download
Post on 12-Nov-2014
1.000 Views
Preview:
DESCRIPTION
TRANSCRIPT
1
Enhanced Guide to Enhanced Guide to Oracle8iOracle8i
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
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
Parameters
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 declarationsBEGIN program statementsEXCEPTION exception handlersEND;
header
body
10
Executing a Procedure EXECUTE procedure_name (parameter1_value, parameter2_value, …);
11
Formal parameters: declared in procedure header
Actual parameters: values placed in parameter list when procedure is called
Values correspond based on orderProcedure Header:
PROCEDURE calc_gpa (s_id IN NUMBER, term_id IN NUMBER, gpa OUT NUMBER);
Procedure Call:calc_gpa (current_s_id, 4, current_gpa);
Parameter Types
Formal Parameters
Actual Parameters
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 FunctionCREATE OR REPLACE FUNCTION function_name
(parameter1 mode datatype, parameter2 mode datatype, … ) RETURN function_return_data_typeIS local variable declarationsBEGIN 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
ObjectNavigatorWindow
PL/SQLInterpreterWindow
commandpromptpane
sourcecodepane
21
Program Unit Editor Interface
Button barProcedurelistProcedure
template
Status line
Sourcecode pane
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
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
Using the PL/SQL Interpreterto Find Runtime Errors
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
Variablevalues
Executionarrow
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
Direct Dependency
Indirect Dependency
CREATE_NEW_ORDER
CUST_ORDER
CREATE_NEW_
ORDER_LINE
ORDER_LINE
ORDER_ID_SEQUENCE
ORDER_ID_SEQUENCE
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 blocksEND;
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
51
Creating a Trigger in SQL*PlusCREATE OR REPLACE TRIGGER trigger_name[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ONtable_name [FOR EACH ROW] [WHEN (condition)]BEGIN trigger bodyEND;
52
Creating a Statement-Level Trigger in SQL*Plus
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 SQL*Plus
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
TimingStatementtype
Triggername
Field
WHENconditionTriggerbody
Level
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 SQL*Plus
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 SQL*Plus 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 Commands1. Open the cursor that defines the memory
area where processing is performed2. Define the SQL command as a text string,
using placeholders for dynamic values3. Parse the SQL command4. Bind input variables to placeholders5. Execute the SQL command6. Close the cursor
71
Creating Dynamic SQL Programs that Contain DDL Commands1. Open the cursor that defines the
memory area where processing is performed
2. Define the SQL command as a text string
You cannot define placeholders and dynamically bind values
3. Parse the SQL command4. Close the cursor
72
Creating Dynamic SQL Programs that Contain SELECT Commands1. Open the cursor2. Define the SQL command as a text string using
placeholders for dynamic values3. Parse the SQL command4. Bind input values to placeholders5. Define output variables6. Execute the query7. Fetch the rows8. Associate the fetched rows with the output
variables9. Close the cursor
73
Using Dynamic SQL to Create an Anonymous PL/SQL Block1. Open the cursor2. Define the SQL command as a text string
using placeholders for dynamic values3. Parse the SQL command4. Bind input and output values to
placeholders5. Execute the query6. Retrieve values of output variables7. Close the cursor
top related