Advanced SQL And PL/SQL Topics

1. Advanced SQL And PL/SQL Topics Chapter 9 A Guide to Oracle9i

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 SQL*Plus • Learn how to use Forms Builder to create stored program units A Guide to Oracle9i

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) A Guide to Oracle9i

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. A Guide to Oracle9i

5. Composite Index • Contains multiple (up to 16) sorted columns • Used for queries with multiple search conditions • CREATE INDEX index_name ON tablename(index_fieldname1, index_fieldname2, …); A Guide to Oracle9i

6. Viewing Index Information • Use data dictionary view USER_INDEXES A Guide to Oracle9i

7. Dropping an Index • If an index is no longer needed or does not improve performance, delete it • DROP INDEX index_name; A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

12. Types of Program Units A Guide to Oracle9i

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. A Guide to Oracle9i

14. Parameter Declarations List • Defines the parameters and declares their associated data types • Enclosed in parentheses • Separated by commas A Guide to Oracle9i

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 A Guide to Oracle9i

16. Creating a Stored Procedure in SQL*Plus A Guide to Oracle9i

17. Debugging Stored Program Units in SQL*Plus A Guide to Oracle9i

18. Debugging Stored Program Units in SQL*Plus A Guide to Oracle9i

19. Calling a Stored Procedure • From SQL*Plus command line: • EXECUTE procedure_name (parameter1_value, parameter2_value, ...); • From PL/SQL program: • Omit execute command • Passing parameters (see Figure 9-13) A Guide to Oracle9i

20. Creating a Stored Program Unit Function A Guide to Oracle9i

21. Creating a Stored Program Unit Function • Last command in function must be RETURN A Guide to Oracle9i

22. Calling a Function • variable_name := function_name(parameter1, parameter2, ...); A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 library’s 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 A Guide to Oracle9i

28. Creating a PL/SQL Library • Use Forms Builder to create libraries • Add form program units and stored program units to the library. A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

31. Package Specification A Guide to Oracle9i

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 A Guide to Oracle9i

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; A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

36. Creating a Package Header in SQL*Plus A Guide to Oracle9i

37. Creating a Package Body in SQL*Plus A Guide to Oracle9i

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; A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 A Guide to Oracle9i

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 triggersfire once, either before or after the SQL triggering statement executes. • Row-level triggersfire once for each row affected by the triggering statement • Use :OLD.fieldname to reference previous value • Use :NEW.fieldname to reference changed value A Guide to Oracle9i

43. Creating Database Triggers A Guide to Oracle9i

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; A Guide to Oracle9i

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 A Guide to Oracle9i

46. Trigger Use – Audit Trail A Guide to Oracle9i

47. Creating Audit Trigger in SQL*Plus A Guide to Oracle9i

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 A Guide to Oracle9i

49. Disabling and Dropping Triggers • To remove a trigger: • DROP TRIGGER trigger_name; • To disable/enable a trigger: • ALTER TRIGGER trigger_name [ENABLE | DISABLE]; A Guide to Oracle9i

50. Viewing Trigger Information A Guide to Oracle9i