Advanced SQL And PLSQL Topics

1
Advanced SQL And PL/SQL Topics

• Chapter 9

2
Lesson A Objectives

• Learn how to create and use indexes
• Become familiar with PL/SQL stored program units
• Learn how to create server-side stored program
  units in SQLPlus
• Learn how to use Forms Builder to create stored
  program units

3
Database Indexes

• Similar to an index in a book
• Table with list of sorted data values and
  corresponding physical location
• Used to speed searches
• Uses ROWID column to represent physical location
• Primary key indexed automatically
• Unlimited number allowed, but more indexes means
  more processing time for action queries (insert,
  update, delete)

4
Creating an Index

• Create index after table data is loaded
• CREATE INDEX index_name ON tablename
  (index_fieldname)
• Convention for naming index tablename_fieldname.

5
Composite Index

• Contains multiple (up to 16) sorted columns
• Used for queries with multiple search conditions
• CREATE INDEX index_name ON tablename(index_fieldn
  ame1, index_fieldname2, )

6
Viewing Index Information

• Use data dictionary view USER_INDEXES

7
Dropping an Index

• If an index is no longer needed or does not
  improve performance, delete it
• DROP INDEX index_name

8
Use an Index When

• Table contains a large number of records (a rule
  of thumb is that a large table contains over
  100,000 records)
• The field contains a wide range of values
• The field contains a large number of NULL values
• Application queries frequently use the field in a
  search condition or join condition
• Most queries retrieve less than 2 to 4 of the
  table rows

9
Do Not Use an Index When

• The table does not contain a large number of
  records
• Applications do not use the proposed index field
  in a query search condition
• Most queries retrieve more than 2 to 4 of the
  table records
• Applications frequently insert or modify table
  data

10
Overview of PL/SQL Stored Program Units

• Self-contained group of program statements that
  can be used within a larger program.
• Easier to conceptualize, design, and debug
• Save valuable programming time because you can
  reuse them in multiple database applications
• Other PL/SQL programs can reference them

11
Overview of PL/SQL Stored Program Units

• Server-side program units stored in the
  database as database objects and execute on the
  database server
• Client-side program units stored in the file
  system of the client workstation and execute on
  the client workstation

12
Types of Program Units
13
Creating Stored Program Units

• Procedure a program unit that can receive
  multiple input parameters and return multiple
  output values or return no output values
• Function a program unit that can receive
  multiple input parameters, and always returns a
  single output value.

14
Parameter Declarations List

• Defines the parameters and declares their
  associated data types
• Enclosed in parentheses
• Separated by commas

15
Parameter Declarations List

• Parameter mode describes how the program unit can
  change the parameter value
• IN - specifies a parameter that is passed to the
  program unit as a read-only value that the
  program unit cannot change.
• OUT - specifies a parameter that is a write-only
  value that can appear only on the left side of an
  assignment statement in the program unit
• IN OUT - specifies a parameter that is passed to
  the program unit, and whose value can also be
  changed within the program unit

16
Creating a Stored Procedure in SQLPlus
17
Debugging Stored Program Units in SQLPlus
18
Debugging Stored Program Units in SQLPlus
19
Calling a Stored Procedure

• From SQLPlus command line
• EXECUTE procedure_name (parameter1_value,
  parameter2_value, ...)
• From PL/SQL program
• Omit execute command
• Passing parameters (see Figure 9-13)

20
Creating a Stored Program Unit Function
21
Creating a Stored Program Unit Function

• Last command in function must be RETURN

22
Calling a Function

• variable_name function_name(parameter1,
  parameter2, ...)

23
Using Forms Builder to Create Stored Procedures
and Functions

• Create and test the program unit within a form
• Save it as a stored program unit in your database
  schema
• Provides an enhanced development and debugging
  environment
• Color-coded editor for entering and debugging
  program unit commands
• Displays compile error messages immediately
• Use the Forms Debugger to step through program
  unit commands and view how variable values change

24
Using Forms Builder to Create Stored Procedures
and Functions

• Create the procedure or function as a form
  program unit
• Test and debug the form program unit by calling
  it from commands within a form trigger
• Save the form program unit as a stored program
  unit in the database

25
Lesson B Objectives

• Learn how to call stored procedures from other
  stored procedures and pass parameter values
• Create libraries
• Create packages
• Create database triggers

26
Calling Stored Program Units from Other Stored
Program Units

• Decompose applications into logical units of work
  and then write individual program units for each
  logical unit
• Code is in a single location
• Developers do not need to rewrite program units
  that already exist
• References procedures must be declared first

27
PL/SQL Libraries

• Operating system file that contains code for
  multiple related procedures and functions
• Attach a PL/SQL library to a form or report
• Triggers within the form or report reference
  librarys procedures and functions
• Store a PL/SQL library in the file system of the
  client workstation
• .pll extension - stands for PL/SQL Library
• Compile the library into a library executable
  file - .plx extension - stands for PL/SQL
  Library Executable
• Library places the commands for multiple related
  program units in a single location that
  developers can access and use

28
Creating a PL/SQL Library

• Use Forms Builder to create libraries
• Add form program units and stored program units
  to the library.

29
Packages

• Another way to make PL/SQL program units
  available to multiple applications
• A code library that contains related program
  units and variables
• Stored in the database and executes on the
  database server
• Have more functionality than PL/SQL libraries
• Can create variables in packages
• Definitions for explicit cursors
• More convenient to use than PL/SQL libraries
• Available without explicitly attaching them to a
  form or report

30
Package Specification

• Also called package header
• Declares package objects, including variables,
  cursors, procedures, and functions,
• Use to declare public variables
• Remain in memory after the programs that declare
  and reference them terminate
• Declared in the DECLARE section of a package
• Referenced same as private variables

31
Package Specification
32
Package Header

• Package_name identifies the package
• Must adhere to the Oracle Naming Standard
• Declare the package objects in any order
• Package can consist of just variable
  declarations, or it can consist of just procedure
  or function declarations

33
Procedure and Function Declarations

• Declare a procedure
• PROCEDURE procedure_name
• (parameter1 parameter1_data_type,
• parameter2 parameter2_data_type, ...)
• Declare a function
• FUNCTION function_name
• (parameter1 parameter1_data_type,
• parameter2 parameter2_data_type, ...)
• RETURN return_datatype

34
Package Body

• Contains the implementation of declared
  procedures and functions
• Specification comes before body
• Optional sometimes a package contains only
  variable or cursor declarations, and no procedure
  or function declarations
• See Figure 9-35 for general syntax

35
Package Body

• Package_name in the package body must be the same
  as package_name in the package specification
• Variables that you declare at the beginning of
  the package body are private to the package
• Each package program unit has its own declaration
  section and BEGIN and END statements
• Each program unit declared in the package body
  must have a matching program unit forward
  declaration in the package specification, with an
  identical parameter list

36
Creating a Package Header in SQLPlus
37
Creating a Package Body in SQLPlus
38
Using Package Objects

• Must preface the item with the package name
• package_name.item_name.
• To grant other users the privilege to execute a
  package
• GRANT EXECUTE ON package_name TO username

39
Creating a Package in Forms Builder

• Create a program unit of type Package Spec
• Type the package specification in the PL/SQL
  editor
• Create a program unit of type Package Body
• Type package body in the PL/SQL editor
• Compile package body and test using a form
  trigger
• Save the package in the database for future use

40
Database Triggers

• Program units that execute in response to the
  database events of inserting, updating, or
  deleting a record
• Different from form triggers
• Useful for maintaining integrity constraints and
  audit information
• Cannot accept input parameters
• Executes only when its triggering event occurs

41
Trigger Properties

• Trigger timing
• Defines whether a trigger fires before or after
  the SQL statement executes
• Can have the values BEFORE or AFTER
• Trigger statement
• Defines the type of SQL statement that causes a
  trigger to fire
• Can be INSERT, UPDATE, or DELETE

42
Trigger Properties

• Trigger level
• Defines whether a trigger fires once for each
  triggering statement or once for each row
  affected by the triggering statement
• Can have the values ROW or STATEMENT
• Statement-level triggers fire once, either before
  or after the SQL triggering statement executes.
• Row-level triggers fire once for each row
  affected by the triggering statement
• Use OLD.fieldname to reference previous value
• Use NEW.fieldname to reference changed value

43
Creating Database Triggers
44
Database Trigger Header

• Trigger_name must follow Oracle Naming Standard
• Join statement types using the OR operator to
  fire for multiple statement types (INSERT OR
  UPDATE)
• WHEN (condition) clause
• Trigger will fire only for rows that satisfy a
  specific search condition
• WHEN OLD.grade IS NOT NULL

45
Database Trigger Body

• Contains the commands that execute when the
  trigger fires
• PL/SQL code block that contains the usual
  declaration, body, and exception sections
• Cannot contain transaction control statements
• Reference the NEW and OLD field values only in a
  row-level trigger

46
Trigger Use Audit Trail
47
Creating Audit Trigger in SQLPlus
48
Creating a Database Triggerin Forms Builder

• Use the Database Trigger Dialog Box to specify
  trigger properties
• Type trigger body into Trigger Body entry field

49
Disabling and Dropping Triggers

• To remove a trigger
• DROP TRIGGER trigger_name
• To disable/enable a trigger
• ALTER TRIGGER trigger_name ENABLE DISABLE

50
Viewing Trigger Information
51
Summary

• Database indexes store an ordered list of field
  values with corresponding ROWID
• Indexes are used to speed query performance
• Stored program units are named PL/SQL blocks that
  are saved
• Procedures accept parameters and return 0,1, or
  many values
• Functions accept parameters and return exactly
  one value

52
Summary

• PL/SQL Library is a client-side file containing
  procedures and functions
• PL/SQL Package is a collection of public
  variables, cursors, procedures and functions
  stored in the DBMS
• Database triggers are PL/SQL blocks that are run
  in response to table changes
• Database triggers are used to enforce integrity
  constraints and track changes
• Forms Builder may be used as an IDE to develop
  functions, procedures, libraries, packages and
  triggers