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 SQL*Plus • 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 SQL*Plus • 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 SQL*Plus • 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 owner’s 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/SQL*Plus 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 CREATE_ NEW_ ORDER Direct Dependency 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 don’t care about details

37. Creating a Package Specification in SQL*Plus 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 SQL*Plus

39. Creating a Package Body in SQL*Plus CREATE OR REPLACE PACKAGE BODY package_name IS private variable declarations program unit blocks END;

40. Creating a Package Body in SQL*Plus

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 don’t care how many rows were updated