Enhanced Guide to Oracle8i

1
Enhanced Guide to Oracle8i
Chapter 5 Advanced PL/SQL Programming
2
Anonymous PL/SQL Programs

• Write code in text editor, execute it in SQLPlus
• Code can be stored as text in file system
• Program cannot be called by other programs, or
  executed by other users
• Cannot accept or pass parameter values

3
Named PL/SQL Programs

• Can be created
• Using text editor executed in SQLPlus
• Using Procedure Builder
• Can be stored
• As compiled objects in database
• As source code libraries in file system
• Can be called by other programs
• Can be executed by other users

4
Named Program Locations

• Server-side
• Stored in database as database objects
• Execute on the database server
• Client-side
• Stored in the client workstation file system
• Execute on the client workstation

5
Named Program Types

• Program Units (client or server-side)
• Procedures
• Functions
• Libraries (client-side)
• Packages (client or server-side)
• Triggers (server-side)

6
Program Units

• Procedures
• Can receive and pass multiple parameter values
• Can call other program units
• Functions
• Like procedures, except they return a single
  value

7
Parameters

• Variables used to pass data values in/out of
  program units
• Declared in the procedure header
• Parameter values are passed when the procedure is
  called from the calling program

8
Parameter Modes

• IN
• Incoming values, read-only
• OUT
• Outgoing values, write-only
• IN OUT
• Can be both incoming and outgoing

9
Creating a Procedure

• CREATE OR REPLACE PROCEDURE procedure_name
• (parameter1 mode datatype,
• parameter2 mode datatype,
• ) IS AS
• local variable declarations
• BEGIN
• program statements
• EXCEPTION
• exception handlers
• END

header
body
10
Executing a Procedure

• EXECUTE procedure_name
• (parameter1_value, parameter2_value, )

11
Parameter Types

• Formal parameters declared in procedure header
• Actual parameters values placed in parameter
  list when procedure is called
• Values correspond based on order

Procedure Header
Formal Parameters
PROCEDURE calc_gpa (s_id IN NUMBER, term_id IN
NUMBER, gpa OUT NUMBER)
Procedure Call
Actual Parameters
calc_gpa (current_s_id, 4, current_gpa)
12
Debugging Named Program Units in SQLPlus

• Isolate program lines causing errors and then fix
  them
• Use SHOW ERRORS command to view compile error
  details
• Use DBMS_OUTPUT.PUT_LINE commands to view
  intermediate values and track program flow

13
Creating a Function

• CREATE OR REPLACE FUNCTION function_name
• (parameter1 mode datatype,
• parameter2 mode datatype,
• )
• RETURN function_return_data_type
• IS local variable declarations
• BEGIN
• program statements
• RETURN return_value
• EXCEPTION
• exception handlers
• RETURN EXCEPTION_NOTICE
• END

header
body
14
Function Syntax Details

• RETURN command in header specifies data type of
  value the function will return
• RETURN command in body specifies actual value
  returned by function
• RETURN EXCEPTION_NOTICE instructs the function to
  display the except notice in the calling program

15
Calling a Function

• Can be called from either named or anonymous
  PL/SQL blocks
• Can be called within SQL queries
• return_value
• function_name(parameter1_value,
  parameter2_value, )

16
Stored Program UnitObject Privileges

• Stored program units exist as objects in your
  database schema
• To allow other users to use your units, you must
  grant them EXECUTE privileges
• GRANT EXECUTE ON unit_name TO username

17
Using Stored Program UnitsThat Belong to Other
Users

• You must have been granted the privilege to use
  it
• You must preface the unit name with the owners
  username
• return_value
• LHOWARD.my_function
• TO_DATE(07/14/1958, MM/DD/YYYY)

18
Using Procedure Builder to Create Named Program
Units

• Procedures and functions created in
  Notepad/SQLPlus are always server-side
• Stored in the database
• Executed on the database server
• Procedure Builder can be used to create
  client-side libraries of procedures and functions
• Stored in the client file system
• Executed on the client

19
Procedure Builder Client-SideFile Types

• .pls
• Single program unit
• Uncompiled source code
• Can only be opened/modified in Procedure Builder
• .pll
• Library of procedures or functions
• Compiled code
• Can be referenced in other Developer applications
  (Forms, Reports)

20
Procedure Builder Interface
Object Navigator Window
PL/SQL Interpreter Window
source code pane
command prompt pane
21
Program Unit Editor Interface
Button bar
Procedure list
Procedure template
Source code pane
Status line
22
Creating Client-Side Objects in Procedure Builder

• Client-side program unit source code
• Create program unit in Program Unit Editor,
  export text to .pls file
• Client-side library
• Click File, click Save As, and specify to save
  library .pll file in file system

23
Executing a Procedure in Procedure Builder

• Load program unit as a top-level Program Unit
  object
• Type the procedure name and parameter list values
  at the command prompt

24
Using the PL/SQL Interpreterto Find Runtime
Errors

• Set a breakpoint on the program line where
  execution will pause
• Single-step through the program lines and examine
  current variable values
• Global variables
• Stack (local) variables
• View program execution path

25
Setting a Breakpoint

• Load program unit in PL/SQL Interpreter window
• Double-click line to create breakpoint

Breakpoint
26
Viewing Program Variable Values During Execution
Execution arrow
Variable values
27
Strategy For Using the PL/SQL Interpreter Debugger

• Run the program, and determine which line is
  causing the run-time error
• Run the program again, and examine variable
  values just before the error occurs to determine
  its cause

28
Calling Procedures From Other Procedures

• Use procedure name followed by parameter list
• procedure_name
• (parameter1_value, parameter2_value, )

29
Creating Server-Side Objects in Procedure Builder

• Stored program units
• Drag program unit from top-level node in
  Procedure Builder to Program Units node under
  Database node
• Libraries
• Click File, click Save As, and specify to save
  library in database
• Regardless of storage location, PL/SQL libraries
  ALWAYS execute on client

30
Program Unit Dependencies

• Object dependencies
• Program units are dependent on the database
  objects they reference (tables, views, sequences,
  )
• Procedure dependencies
• Program units are dependent on other program
  units they call

31
Direct and Indirect Dependencies

• Direct dependency
• Object or program is directly called or
  referenced
• Indirect dependency
• Object or program is called or referenced by a
  subprogram

32
Direct and Indirect Dependencies
CUST_ORDER
Direct Dependency
CREATE_ NEW_ ORDER
ORDER_ID_ SEQUENCE
Indirect Dependency
CREATE_ NEW_ ORDER_LINE
ORDER_ID_ SEQUENCE
ORDER_LINE
33
Invalidation

• If an object or program on which a program has a
  dependency is changed, the program is
  invalidated, and must be recompiled

34
Packages

• Server-side code library
• Can contain
• Global variable declarations
• Cursors
• Procedures
• Functions

35
Differences Between Packages and Libraries

• Libraries have to be explicitly attached to
  applications, while packages are always available
  to be called by applications
• Libraries always execute on client
• Packages always execute on server

36
Package Components

• Specification
• Used to declare all public variables, cursors,
  procedures, functions
• Body
• Contains underlying code for procedures and
  functions
• Rationale
• Specification is visible to users, gives details
  on how to use
• Body is not visible, users dont care about
  details

37
Creating a Package Specification in SQLPlus
CREATE OR REPLACE PACKAGE package_name IS
--public variables variable_name datatype
--program units PROCEDURE procedure_name
(parameter_list) FUNCTION function_name
(parameter_list) END
38
Creating a Package Specification in SQLPlus
39
Creating a Package Body in SQLPlus
CREATE OR REPLACE PACKAGE BODY package_name IS
private variable declarations program unit
blocks END
40
Creating a Package Body in SQLPlus
41
Calling a Program Unit That Is In a Package

• Preface the program unit name with the package
  name
• PACKAGE_NAME.program_unit_name(parameter_list)
• Example
• DBMS_OUTPUT.PUT_LINE(Hello World)

42
Overloading Program Units in Packages

• Overloading
• Multiple program units have the same name, but
  accept different input parameters
• Allows user to use the same command to perform an
  action but pass different parameter values

43
Package Specification With Overloaded Procedures
44
Saving Packages as Database Objects

• Expand the Database Objects node so your username
  appears, and expand your username so the Stored
  Program Units node appears
• Drag the Package Specification and Package Body
  under the Stored Program Units node

45
Database Triggers

• Program units that are attached to a specific
  table
• Execute in response to the following table
  operations
• INSERT
• UPDATE
• DELETE

46
Uses For Database Triggers

• Force related operations to always happen
• Sell an item, update QOH
• Create a table that serves as an audit trail
• Record who changes a student grade and when they
  change it

47
Creating Database Triggers

• Code is similar to all PL/SQL program unit blocks
• Database triggers cannot accept parameters

48
Defining Triggers

• To define a trigger, you must specify
• Statement type that causes trigger to fire
• INSERT, UPDATE, DELETE
• Timing
• BEFORE or AFTER
• Level
• STATEMENT or ROW

49
Trigger Timing

• BEFORE trigger fires before statement executes
• Example for audit trail, records grade value
  before it is updated
• AFTER trigger fires after statement executes
• Example update QOH after item is sold

50
Trigger Levels

• ROW trigger fires once for each row that is
  affected
• Example when adding multiple order lines,
  update multiple inventory QOH values
• STATEMENT trigger fires once, regardless of how
  many rows are updated
• Example for audit trail, you just want to
  record that someone updated a table, but you
  dont care how many rows were updated

51
Creating a Trigger in SQLPlus

• CREATE OR REPLACE TRIGGER trigger_name
• BEFOREAFTER INSERTUPDATEDELETE ON
• table_name
• FOR EACH ROW WHEN (condition)
• BEGIN
• trigger body
• END

52
Creating a Statement-Level Trigger in SQLPlus
53
Trigger Restrictions

• You can only create triggers on tables that you
  own
• You must have the CREATE TRIGGER system privilege
• You cannot execute a COMMIT command in a trigger

54
Row-Level Trigger Syntax

• WHEN (condition)
• Optional
• Specifies to fire only when a row satisfies a
  certain search condition
• Referencing old and new values in the trigger
  body
• OLD.field_name
• NEW.field_name

55
Creating a Row-Level Trigger in SQLPlus
56
Creating a Trigger in Procedure Builder

• In the Object Navigator Pane, expand the Database
  Objects node, expand your username, and select
  the table to which the trigger will be attached
• Create a new trigger
• Specify the trigger properties

57
Specifying Trigger Properties in Procedure Builder
Trigger name
Timing
Field
Statement type
Level
WHEN condition
Trigger body
58
INSTEAD-OF Triggers

• Fire when a user issues a DML command associated
  with a complex view
• Normally, users cannot insert, update, or delete
  records associated with complex views that are
  created by joining multiple tables

59
Creating an INSTEAD-OF Trigger in SQLPlus
60
Disabling and Dropping Triggers

• Syntax to drop a trigger
• DROP TRIGGER trigger_name
• Syntax to enable or disable a trigger
• ALTER TRIGGER trigger_name ENABLE DISABLE

61
Oracle Built-In Packages

• Provide support for basic database functions
• Owned by the SYS database schema

62
Oracle Built-In Package Types

• Transaction processing
• Application development
• Database and application administration
• Internal support

63
Transaction Processing Packages

• Provide procedures to support transaction
  processing
• DBMS_ALERT dynamically sends messages to other
  database sessions
• DBMS_LOCK creates user-defined locks on tables
  and records
• DBMS_SQL implements Dynamic SQL
• DBMS_TRANSACTION provides procedures for
  transaction management

64
Application Development Packages

• Aid developers in creating and debugging PL/SQL
  applications
• DBMS_DESCRIBE returns information about the
  parameters of any stored program unit
• DBMS_JOB schedules PL/SQL named programs to run
  at specific times
• DBMS_OUTPUT provides text output in PL/SQL
  programs in SQLPlus
• DBMS_PIPE sends messages to other database
  sessions
• DBMS_SESSION dynamically changes the properties
  of a database session
• UTL_FILE enables PL/SQL output to be written to
  a binary file

65
DBMS_JOB Package

• Enables PL/SQL named programs to be run at
  certain times
• Creates a job queue
• List of programs to be run, and times they are to
  be run

66
DBMS_PIPE Package

• Implements database pipes
• Used to transfer information among database
  sessions independently of transactions
• Sending a message
• Use the PACK_MESSAGE and SEND_MESSAGE procedures
• Receiving a message
• Use the RECEIVE_MESSAGE and UNPACK_MESSAGE
  procedures

67
Database and Application Administration Packages

• Support database administration tasks
• DBMS_APPLICATION_INFO registers information
  about programs being run by individual user
  sessions
• DBMS_DDL provides procedures for compiling
  program units and analyzing database objects
• DBMS_SHARED_POOL used to manage the shared pool
• DBMS_SPACE provides information for managing
  how data values are physical stored in the
  database
• DBMS_UTILITY provides procedures for compiling
  all program units and analyzing all objects in a
  database schema

68
Internal Support Packages

• Provide underlying functionality of the Oracle
  database
• STANDARD defines all built-in functions and
  procedures, database data types, and PL/SQL data
  types
• DBMS_SNAPSHOT used to manage database snapshots
• DBMS_REFRESH used to refresh snapshots
• DBMS_STANDARD contains common functions of the
  PL/SQL language

69
Dynamic SQL

• Allows you to create SQL commands as text strings
  in PL/SQL programs, and validate the database
  objects at runtime
• Allows user to specify program inputs
• Supporting procedures are provided in the
  DBMS_SQL package

70
Creating Dynamic SQL Programs that Contain DML
Commands

• Open the cursor that defines the memory area
  where processing is performed
• Define the SQL command as a text string, using
  placeholders for dynamic values
• Parse the SQL command
• Bind input variables to placeholders
• Execute the SQL command
• Close the cursor

71
Creating Dynamic SQL Programs that Contain DDL
Commands

• Open the cursor that defines the memory area
  where processing is performed
• Define the SQL command as a text string
• You cannot define placeholders and dynamically
  bind values
• Parse the SQL command
• Close the cursor

72
Creating Dynamic SQL Programs that Contain SELECT
Commands

• Open the cursor
• Define the SQL command as a text string using
  placeholders for dynamic values
• Parse the SQL command
• Bind input values to placeholders
• Define output variables
• Execute the query
• Fetch the rows
• Associate the fetched rows with the output
  variables
• Close the cursor

73
Using Dynamic SQL to Create an Anonymous PL/SQL
Block

• Open the cursor
• Define the SQL command as a text string using
  placeholders for dynamic values
• Parse the SQL command
• Bind input and output values to placeholders
• Execute the query
• Retrieve values of output variables
• Close the cursor