enhanced guide to oracle8i n.
Skip this Video
Loading SlideShow in 5 Seconds..
Enhanced Guide to Oracle8i PowerPoint Presentation
Download Presentation
Enhanced Guide to Oracle8i

Enhanced Guide to Oracle8i

128 Views Download Presentation
Download Presentation

Enhanced Guide to Oracle8i

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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