oracle plsql
DESCRIPTION
Oracle Plsql turprialTRANSCRIPT
Siemens GreeceD.I.P.2.2006
Page 1
Oracle PL/SQL Programming
Oracle Training
Instructor
Siemens GreeceD.I.P.2.2006
Page 2
Oracle PL/SQL Programming
• Variables• Executable Statements• Interacting with the Server• Control Structures• Composite Datatypes• Explicit Cursors• Handling Exceptions• Procedures• Functions• Packages• Triggers• Managing Subprograms and Triggers• Managing Dependencies• Oracle Supplied Packages
Training Index
Siemens GreeceD.I.P.2.2006
Page 3
Oracle PL/SQL Programming
Introduction – About PL/SQL
• PL/SQL is an extension to SQL.• Blocks of PL/SQL code are passed to and processed by a PL/SQL engine.• Benefits of PL/SQL:
– Integration– Improved performance– Portability– Modularity of program development
Siemens GreeceD.I.P.2.2006
Page 4
Oracle PL/SQL Programming
Anonymousblock
Applicationtrigger
Stored procedure/
function
Databasetrigger
Applicationprocedure/
function
Packagedprocedure/
function
DECLARE
BEGIN
EXCEPTION
END;
Introduction – PL/SQL Program Constructs
Siemens GreeceD.I.P.2.2006
Page 5
Oracle PL/SQL Programming
Introduction – PL/SQL Block Structure
• DECLARE (optional)– Declare PL/SQL objects to be used within this block
• BEGIN (mandatory)– Define the executable statements
• EXCEPTION (optional)– Define the actions that take place if an error arises
• END; (mandatory)
Siemens GreeceD.I.P.2.2006
Page 6
Oracle PL/SQL Programming
• Temporary storage of data• Manipulation of stored values• Reusability• Ease of maintenance
Variables – Use of Variables
Siemens GreeceD.I.P.2.2006
Page 7
Oracle PL/SQL Programming
• Declare and initialize variables in the declaration section.• Assign new values to variables in the executable section.• Pass values into PL/SQL blocks through parameters.• View results through output variables.
Variables – Handling Variables
Siemens GreeceD.I.P.2.2006
Page 8
Oracle PL/SQL Programming
• PL/SQL variables– Scalar
• VARCHAR2 (maximum_length)• NUMBER [(precision, scale)]• DATE• CHAR [(maximum_length)]• LONG• LONG RAW• BOOLEAN• BINARY_INTEGER• PLS_INTEGER
– Composite• TABLE• RECORD• NESTED TABLE• VARRAY
– Reference– LOB (large objects)
• CLOB • BLOB • BFILE• NCLOB
• Non-PL/SQL variables– Bind – Host
Variables – Types of Variables
Siemens GreeceD.I.P.2.2006
Page 9
Oracle PL/SQL Programming
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
• Follow naming conventions.• Initialize variables designated as NOT NULL and CONSTANT.• Initialize identifiers by using the assignment operator (:=) or the DEFAULT
reserved word.• Declare at most one identifier per line.• Two variables can have the same name, provided they are in different
blocks.• The variable name (identifier) should not be the same as the name of table
columns used in the block.
Variables – Declaring Variables
Siemens GreeceD.I.P.2.2006
Page 10
Oracle PL/SQL Programming
• Declare a variable according to: – A database column definition– Another previously declared variable
• Prefix %TYPE with:– The database table and column– The previously declared variable name
Variables – %TYPE attribute
Siemens GreeceD.I.P.2.2006
Page 11
Oracle PL/SQL Programming
• Reference non-PL/SQL variables as host variables.• Prefix the references with a colon (:).
Variables – Non-PL/SQL Variables
Siemens GreeceD.I.P.2.2006
Page 12
Oracle PL/SQL Programming
• An Oracle-supplied packaged procedure• An alternative for displaying data from a PL/SQL block• Must be enabled in SQL*Plus with SET SERVEROUTPUT ON.
Variables – DBMS_OUTPUT.PUT_LINE
Siemens GreeceD.I.P.2.2006
Page 13
Oracle PL/SQL Programming
• Statements can continue over several lines• Lexical units can be separated by
– Spaces– Delimiters– Identifiers– Literals
• Character and date literals must be enclosed in single quotation marks.• Numbers can be simple values or scientific notation.
– Comments• Prefix single-line comments with two dashes (--).• Place multi-line comments between the symbols /* and */.
• Identifiers– Can contain up to 30 characters– Cannot contain reserved words unless enclosed in double quotation marks– Must begin with an alphabetic character– Should not have the same name as a database table column name
• A PL/SQL block is terminated by a slash ( / ) on a line by itself.
Executable Statements – PL/SQL Block Syntax
Siemens GreeceD.I.P.2.2006
Page 14
Oracle PL/SQL Programming
• Available in procedural statements:– Single-row number – Single-row character– Datatype conversion– Date
• Not available in procedural statements:– DECODE– Group functions
Executable Statements – SQL Functions in PL/SQL
Siemens GreeceD.I.P.2.2006
Page 15
Oracle PL/SQL Programming
• Convert data to comparable datatypes.• Mixed datatypes can result in an error and affect performance.• Conversion functions:
– TO_CHAR– TO_DATE– TO_NUMBER
Executable Statements – Datatype Conversions
Siemens GreeceD.I.P.2.2006
Page 16
Oracle PL/SQL Programming
• Statements can be nested wherever an executable statement is allowed.• A nested block becomes a statement.• An exception section can contain nested blocks.• The scope of an object is the region of the program that can refer to the
object.• An identifier is visible in the regions in which you can reference the
unqualified identifier– A block can look up to the enclosing block.– A block cannot look down to enclosed blocks.
Executable Statements – Nested Blocks and Variable Scope
Siemens GreeceD.I.P.2.2006
Page 17
Oracle PL/SQL Programming
• Logical• Arithmetic• Concatenation • Parentheses to control order of operations• Exponential operator (**)
Executable Statements – Operators in PL/SQL
Siemens GreeceD.I.P.2.2006
Page 18
Oracle PL/SQL Programming
• Make code maintenance easier by:– Documenting code with comments– Developing a case convention for the code– Developing naming conventions for identifiers and other objects– Enhancing readability by indenting
Executable Statements – Programming Guidelines
Siemens GreeceD.I.P.2.2006
Page 19
Oracle PL/SQL Programming
• Avoid ambiguity:– The names of local variables and formal parameters take precedence over the
names of database tables.– The names of columns take precedence over the names of local variables.
Executable Statements – Code Naming Conventions
Siemens GreeceD.I.P.2.2006
Page 20
Oracle PL/SQL Programming
• For clarity, indent each level of code.
Executable Statements – Indenting Code
Siemens GreeceD.I.P.2.2006
Page 21
Oracle PL/SQL Programming
• Extract a row of data from the database by using the SELECT command. Only a single set of values can be returned.
• Make changes to rows in the database by using DML commands.• Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT
command.• Determine DML outcome with implicit cursors.
Interacting with the Server – SQL Statements in PL/SQL
Siemens GreeceD.I.P.2.2006
Page 22
Oracle PL/SQL Programming
SELECT select_listINTO {variable_name[, variable_name]...| record_name} FROM tableWHERE condition;
• Retrieve data from the database with SELECT.• The INTO clause is required.
Interacting with the Server – SELECT Statements in PL/SQL
Siemens GreeceD.I.P.2.2006
Page 23
Oracle PL/SQL Programming
• Make changes to database tables by using DML commands:– INSERT– UPDATE– DELETE
Interacting with the Server – Manipulating Data Using PL/SQL
Siemens GreeceD.I.P.2.2006
Page 24
Oracle PL/SQL Programming
• Use a naming convention to avoid ambiguity in the WHERE clause.• Database columns and identifiers should have distinct names.• Syntax errors can arise because PL/SQL checks the database first for a
column in the table.
Interacting with the Server – Naming Conventions
Siemens GreeceD.I.P.2.2006
Page 25
Oracle PL/SQL Programming
• Initiate a transaction with the first DML command to follow a COMMIT or ROLLBACK.
• Use COMMIT and ROLLBACK SQL statements to terminate a transaction explicitly.
Interacting with the Server – COMMIT and ROLLBACK Statements
Siemens GreeceD.I.P.2.2006
Page 26
Oracle PL/SQL Programming
• A cursor is a private SQL work area.• There are two types of cursors:
– Implicit cursors– Explicit cursors
• The Oracle Server uses implicit cursors to parse and execute your SQL statements.
• Explicit cursors are explicitly declared by the programmer.
Interacting with the Server – SQL Cursor
Siemens GreeceD.I.P.2.2006
Page 27
Oracle PL/SQL Programming
• Using SQL cursor attributes, you can test the outcome of your SQL statements.
Interacting with the Server – SQL Cursor
SQL%ROWCOUNT Number of rows affected by the most recent SQL statement (an integer value)
SQL%FOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows
SQL%NOTFOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows
SQL%ISOPEN Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed
Siemens GreeceD.I.P.2.2006
Page 28
Oracle PL/SQL Programming
IF condition THEN statements;[ELSIF condition THEN statements;][ELSE statements;]END IF;
• You can change the logical flow of statements using conditional IF statements and loop control structures.
Control Structures – IF Statement
Siemens GreeceD.I.P.2.2006
Page 29
Oracle PL/SQL Programming
• You can handle null values with the IS NULL operator.• Any arithmetic expression containing a null value evaluates to NULL.• Concatenated expressions with null values treat null values as an empty
string.
Control Structures – Logical Conditions
NOT
TRUE
FALSE
NULL
OR
TRUE
FALSE
NULL
TRUE FALSE NULL
FALSE
TRUE
NULL
AND
TRUE
FALSE
NULL
TRUE FALSE NULL
TRUE
NULL NULL
NULL
FALSE FALSE
FALSE
FALSE
FALSE
TRUE
TRUE
TRUE
TRUETRUE
FALSE
NULL NULL
NULL
Siemens GreeceD.I.P.2.2006
Page 30
Oracle PL/SQL Programming
• Loops repeat a statement or sequence of statements multiple times.• There are three loop types:
– Basic loop– FOR loop– WHILE loop
Control Structures – LOOP Statements
Siemens GreeceD.I.P.2.2006
Page 31
Oracle PL/SQL Programming
LOOP statement1; . . . EXIT [WHEN condition];END LOOP;
Control Structures – Basic Loop
Siemens GreeceD.I.P.2.2006
Page 32
Oracle PL/SQL Programming
FOR counter in [REVERSE] lower_bound..upper_bound LOOP statement1; statement2; . . .END LOOP;
• Use a FOR loop to shortcut the test for the number of iterations.• Do not declare the counter; it is declared implicitly.• Reference the counter within the loop only; it is undefined outside the loop.• Use an expression to reference the existing value of a counter.• Do not reference the counter as the target of an assignment.
Control Structures – FOR Loop
Siemens GreeceD.I.P.2.2006
Page 33
Oracle PL/SQL Programming
WHILE condition LOOP statement1; statement2; . . .END LOOP;
• Use the WHILE loop to repeat statements while a condition is TRUE.
Control Structures – WHILE Loop
Siemens GreeceD.I.P.2.2006
Page 34
Oracle PL/SQL Programming
BEGIN <<Outer_loop>> LOOP v_counter := v_counter+1; EXIT WHEN v_counter>10; <<Inner_loop>> LOOP ... EXIT Outer_loop WHEN total_done = 'YES'; -- Leave both loops … EXIT WHEN inner_done = 'YES'; -- Leave inner loop only ... END LOOP Inner_loop; ... END LOOP Outer_loop;END;
• Nest loops to multiple levels.• Use labels to distinguish between blocks and loops.• Exit the outer loop with the EXIT statement referencing the label.
Control Structures – Nested Loops and Labels
Siemens GreeceD.I.P.2.2006
Page 35
Oracle PL/SQL Programming
TYPE type_name IS RECORD (field_declaration[, field_declaration]…);identifier type_name;
• Must contain one or more components of any scalar, RECORD, or PL/SQL TABLE datatype, called fields
• Are similar in structure to records in a 3GL• Are not the same as rows in a database table• Treat a collection of fields as a logical unit• Are convenient for fetching a row of data from a table for processing
Composite Datatypes – PL/SQL Records
Siemens GreeceD.I.P.2.2006
Page 36
Oracle PL/SQL Programming
Field1 (datatype) Field2 (datatype) Field3 (datatype)
Composite Datatypes – PL/SQL Record Structure
Siemens GreeceD.I.P.2.2006
Page 37
Oracle PL/SQL Programming
• Declare a variable according to a collection of columns in a database table or view.
• Prefix %ROWTYPE with the database table.• Fields in the record take their names and datatypes from the columns of the
table or view.• The number and datatypes of the underlying database columns may not be
known.• The number and datatypes of the underlying database column may change
at runtime.• The attribute is useful when retrieving a row with the SELECT statement.
Composite Datatypes – %ROWTYPE Attribute
Siemens GreeceD.I.P.2.2006
Page 38
Oracle PL/SQL Programming
TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE} [NOT NULL] [INDEX BY BINARY_INTEGER];identifier type_name;
• Are composed of two components:– Primary key of datatype BINARY_INTEGER– Column of scalar or record datatype
• Increase dynamically because they are unconstrained
Composite Datatypes – PL/SQL Tables
Siemens GreeceD.I.P.2.2006
Page 39
Oracle PL/SQL Programming
Composite Datatypes – PL/SQL Table Structure
Primary key Column... ...
1 Athens2 Thessaloniki3 Volos
... ...
BINARY_INTEGER Scalar
Siemens GreeceD.I.P.2.2006
Page 40
Oracle PL/SQL Programming
• EXISTS• COUNT• FIRST and LAST• PRIOR • NEXT• EXTEND• TRIM• DELETE
Composite Datatypes – PL/SQL Table Methods
Siemens GreeceD.I.P.2.2006
Page 41
Oracle PL/SQL Programming
• Every SQL statement executed by the Oracle Server has an individual cursor associated with it:
– Implicit cursors: Declared for all DML and PL/SQL SELECT statements– Explicit cursors: Declared and named by the programmer
Explicit Cursors – About Cursors
Siemens GreeceD.I.P.2.2006
Page 42
Oracle PL/SQL Programming
• Create a named SQL area
DECLAREDECLARE
• Identify the active set
OPENOPEN
• Load the current row into variables
FETCHFETCH
• Test for existing rows
EMPTY?
• Return to FETCH if rows found
No
• Release the active set
CLOSECLOSEYes
Explicit Cursors – Controlling Explicit Cursors
Siemens GreeceD.I.P.2.2006
Page 43
Oracle PL/SQL Programming
Explicit Cursors – Declare Cursor
CURSOR cursor_name IS select_statement;
• Do not include the INTO clause in the cursor declaration.• If processing rows in a specific sequence is required, use the ORDER BY
clause in the query.
Siemens GreeceD.I.P.2.2006
Page 44
Oracle PL/SQL Programming
Explicit Cursors – Open Cursor
OPEN cursor_name;
• Open the cursor to execute the query and identify the active set.• If the query returns no rows, no exception is raised.• Use cursor attributes to test the outcome after a fetch.
Siemens GreeceD.I.P.2.2006
Page 45
Oracle PL/SQL Programming
Explicit Cursors – Fetching Data from the Cursor
FETCH cursor_name INTO [variable1, variable2, ...] | record_name];
• Retrieve the current row values into variables.• Include the same number of variables.• Match each variable to correspond to the columns positionally.• Test to see if the cursor contains rows.
Siemens GreeceD.I.P.2.2006
Page 46
Oracle PL/SQL Programming
Explicit Cursors – Closing Cursor
CLOSE cursor_name;
• Close the cursor after completing the processing of the rows.• Reopen the cursor, if required.• Do not attempt to fetch data from a cursor once it has been closed.
Siemens GreeceD.I.P.2.2006
Page 47
Oracle PL/SQL Programming
Explicit Cursors – Explicit Cursor Attributes
Attribute Type Description
%ISOPEN Boolean Evaluates to TRUE if the cursor is open
%NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row
%FOUND Boolean Evaluates to TRUE if the mostrecent fetch returns a row; complement of %NOTFOUND
%ROWCOUNT Number Evaluates to the total number of rows returned so far
Siemens GreeceD.I.P.2.2006
Page 48
Oracle PL/SQL Programming
Explicit Cursors – Cursor FOR Loops
FOR record_name IN cursor_name LOOP statement1; statement2; . . .END LOOP;
• The cursor FOR loop is a shortcut to process explicit cursors.• Implicit open, fetch, and close occur.• The record is implicitly declared.
Siemens GreeceD.I.P.2.2006
Page 49
Oracle PL/SQL Programming
Explicit Cursors – Cursor FOR Loops Using Subqueries
FOR record_name IN (Subquery) LOOP statement1; statement2; . . .END LOOP;
• No need to declare the cursor.• The record is implicitly declared.
Siemens GreeceD.I.P.2.2006
Page 50
Oracle PL/SQL Programming
Explicit Cursors – Cursors With Parameters
CURSOR cursor_name [(parameter_name datatype, ...)]IS select_statement;
• Pass parameter values to a cursor when the cursor is opened and the query is executed.
• Open an explicit cursor several times with a different active set each time.
Siemens GreeceD.I.P.2.2006
Page 51
Oracle PL/SQL Programming
Explicit Cursors – FOR UPDATE Clause
SELECT ... FROM ...FOR UPDATE [OF column_reference] [NOWAIT];
• Explicit locking lets you deny access for the duration of a transaction.• Lock the rows before the update or delete.
Siemens GreeceD.I.P.2.2006
Page 52
Oracle PL/SQL Programming
Explicit Cursors – WHERE CURRENT OF Clause
WHERE CURRENT OF cursor ;
• Use cursors to update or delete the current row.• Include the FOR UPDATE clause in the cursor query to lock the rows first.• Use the WHERE CURRENT OF clause to reference the current row from an
explicit cursor.
Siemens GreeceD.I.P.2.2006
Page 53
Oracle PL/SQL Programming
Handling Exceptions – Handling Exceptions in PL/SQL
• What is an exception?Identifier in PL/SQL that is raised during execution
• How is it raised?– An Oracle error occurs.– You raise it explicitly.
• How do you handle it?– Trap it with a handler.– Propagate it to the calling environment.
Siemens GreeceD.I.P.2.2006
Page 54
Oracle PL/SQL Programming
Trap the exception
DECLARE
BEGIN
END;
Exception is raised
EXCEPTIONException is
trapped
Propagate the exception
DECLARE
BEGIN
END;
Exception is raised
EXCEPTION
Exception isnot trapped
Exception propagates to calling environment
Handling Exceptions – Handling Exceptions in PL/SQL
Siemens GreeceD.I.P.2.2006
Page 55
Oracle PL/SQL Programming
Handling Exceptions – Exception Types
• Implicitly raised– Predefined Oracle Server– Non-predefined Oracle Server
• Explicitly raised– User-defined
Siemens GreeceD.I.P.2.2006
Page 56
Oracle PL/SQL Programming
Handling Exceptions – Trapping Exceptions
EXCEPTION WHEN exception1 [OR exception2 . . .] THEN statement1; statement2; . . . [WHEN exception3 [OR exception4 . . .] THEN statement1; statement2; . . .] [WHEN OTHERS THEN statement1; statement2; . . .]
Siemens GreeceD.I.P.2.2006
Page 57
Oracle PL/SQL Programming
Handling Exceptions – Trapping Exceptions
• WHEN OTHERS is the last clause.• EXCEPTION keyword starts exception-handling section.• Several exception handlers are allowed.• Only one handler is processed before leaving the block.
Siemens GreeceD.I.P.2.2006
Page 58
Oracle PL/SQL Programming
Handling Exceptions – Predefined Oracle Exceptions
• Reference the standard name in the exception-handling routine.• Sample predefined exceptions:
– NO_DATA_FOUND– TOO_MANY_ROWS– INVALID_CURSOR– ZERO_DIVIDE– DUP_VAL_ON_INDEX– CURSOR_ALREADY_OPEN– INVALID_CURSOR– STORAGE_ERROR
Siemens GreeceD.I.P.2.2006
Page 59
Oracle PL/SQL Programming
Handling Exceptions – Non Predefined Oracle Exceptions
Declare
• Name the exception
Associate
• Code thePRAGMA EXCEPTION_INIT
Declarative section
Reference
• Handle the raised exception
Exception-handlingsection
Siemens GreeceD.I.P.2.2006
Page 60
Oracle PL/SQL Programming
Handling Exceptions – User Defined Exceptions
• Name the exception
Declare
Declarativesection
Raise
• Explicitly raise the Explicitly raise the exception by using the exception by using the RAISE statementRAISE statement
Executablesection
Reference
• Handle the raised exception
Exception-handlingsection
Siemens GreeceD.I.P.2.2006
Page 61
Oracle PL/SQL Programming
Handling Exceptions – RAISE_APPLICATION_ERROR Procedure
RAISE_APPLICATION_ERROR (error_number,message [, {TRUE | FALSE}]);
• A procedure that lets you issue user-defined error messages from stored subprograms (-20000 - -20999)
• Called only from an executing stored subprogram• Used in two different places:
– Executable section– Exception section
• Returns error conditions to the user in a manner consistent with other Oracle Server errors
Siemens GreeceD.I.P.2.2006
Page 62
Oracle PL/SQL Programming
Procedures – Overview
• A procedure is a named PL/SQL block that performs an action.• A procedure can be stored in the database, as a database object, for
repeated execution.
Siemens GreeceD.I.P.2.2006
Page 63
Oracle PL/SQL Programming
Procedures – Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)IS|ASPL/SQL Block;
Siemens GreeceD.I.P.2.2006
Page 64
Oracle PL/SQL Programming
Procedures – Creating Procedures Using SQL*PLUS
• Enter the text of the CREATE PROCEDURE statement in an editor.• Run the script file to compile the procedure.• Use SHOW ERRORS to see compilation
errors.• When successfully compiled, the procedure is ready for execution.
Siemens GreeceD.I.P.2.2006
Page 65
Oracle PL/SQL Programming
Procedures – Parameters
Callingenvironment
Procedure
(DECLARE)
BEGIN
EXCEPTION
END;
IN parameter
OUT parameter
IN OUT parameter
Siemens GreeceD.I.P.2.2006
Page 66
Oracle PL/SQL Programming
IN OUTMust be specified
Passed into subprogram; returned to calling environment
Initialized variable
Must be a variable
OUTMust be specified
Returned to calling environment
Uninitialized variable
Must be a variable
INDefault
Value ispassed into subprogram
Formal parameter acts as a constant
Actual parametercan be a literal, expression, constant, orinitialized variable
Procedures – Parameters
Siemens GreeceD.I.P.2.2006
Page 67
Oracle PL/SQL Programming
Procedures – Methods for Passing Parameters
• Positional• Named• Combination
Siemens GreeceD.I.P.2.2006
Page 68
Oracle PL/SQL Programming
Procedures – Subprograms
CREATE [OR REPLACE] PROCEDURE procedure_name (parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)IS|ASprocedure_codePL/SQL Block;
Siemens GreeceD.I.P.2.2006
Page 69
Oracle PL/SQL Programming
Procedures – Handled Exceptions
Calling procedure
PROCEDUREPROC1 ...IS ...BEGIN ... PROC2(arg1); ...EXCEPTION ...END PROC1;
PROCEDUREPROC2 ...IS ...BEGIN ...EXCEPTION ...END PROC2;
Called procedure
Exception raised
Exception handled
Control returns to calling procedure
Siemens GreeceD.I.P.2.2006
Page 70
Oracle PL/SQL Programming
Procedures – Unhandled Exceptions
Calling procedure
PROCEDUREPROC1 ...IS ...BEGIN ... PROC2(arg1); ...EXCEPTION ...END PROC1;
PROCEDUREPROC2 ...IS ...BEGIN ...EXCEPTION ...END PROC2;
Called procedure
Exception raised
Exception unhandled
Implicitrollback
Control returns to exception section of calling procedure
Siemens GreeceD.I.P.2.2006
Page 71
Oracle PL/SQL Programming
Procedures – Remove Procedure
DROP PROCEDURE procedure_name;
Siemens GreeceD.I.P.2.2006
Page 72
Oracle PL/SQL Programming
Functions – Overview
• A function is a named PL/SQL block that returns a value.• A function can be stored in the database, as a database object, for repeated
execution.• A function can be called as part of an expression.
Siemens GreeceD.I.P.2.2006
Page 73
Oracle PL/SQL Programming
Functions – Syntax
CREATE [OR REPLACE] FUNCTION function_name (parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)RETURN datatypeIS|ASPL/SQL Block;
• The PL/SQL block must have at least one RETURN statement.
Siemens GreeceD.I.P.2.2006
Page 74
Oracle PL/SQL Programming
Functions – Create Functions Using SQL*PLUS
• Enter the text of the CREATE FUNCTION statement in an editor.• Run the script file to compile the function.• Use SHOW ERRORS to see compilation errors.• When successfully compiled, the function is ready for execution.
Siemens GreeceD.I.P.2.2006
Page 75
Oracle PL/SQL Programming
Functions – Executing Functions
• Invoke a function as part of a PL/SQL expression.• Create a host variable to hold the returned value.• Execute the function. The host variable will be populated by the RETURN
value.
Siemens GreeceD.I.P.2.2006
Page 76
Oracle PL/SQL Programming
Functions – User-Defined Functions is SQL Expressions
• Advantages– Extend SQL where activities are too complex, too awkward, or unavailable with SQL– Can increase efficiency, by using them in the WHERE clause to filter data, as opposed to filtering the
data in the application– Can manipulate character strings
• Locations to call user-defined functions– Select list of a SELECT command– Condition of the WHERE and HAVING clauses– CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses– VALUES clauses of the INSERT command– SET clause of the UPDATE command
• Restrictions– A user-defined function must be a stored function.– A user-defined function must be a SINGLE-ROW function, not a GROUP function.– A user-defined function only takes IN parameters, not OUT or IN OUT.– Datatypes must be valid SQL datatypes, CHAR, DATE, or NUMBER.– Datatypes cannot be PL/SQL types such as BOOLEAN, RECORD, or TABLE.– INSERT, UPDATE, or DELETE commands are not allowed.– Calls to subprograms that break the above restriction are not allowed.
Siemens GreeceD.I.P.2.2006
Page 77
Oracle PL/SQL Programming
Functions – Removing Functions
DROP FUNCTION function_name
Siemens GreeceD.I.P.2.2006
Page 78
Oracle PL/SQL Programming
Functions – Compare Procedures and Functions
ProcedureExecute as a PL/SQL statement
No RETURN datatype
Can return none, one or many values
FunctionInvoke as part of an expression
Must contain a RETURN datatype
Must return a single value
Siemens GreeceD.I.P.2.2006
Page 79
Oracle PL/SQL Programming
Packages – Overview
• Group logically related PL/SQL types, items, and subprograms• Consist of two parts:
– Specification– Body
• Cannot be called, parameterized, or nested• Allow Oracle to read multiple objects into memory at once
Siemens GreeceD.I.P.2.2006
Page 80
Oracle PL/SQL Programming
Packages – Components of a Package
Procedure Adeclaration
Packagespecification
Packagebody
1
2
3
2Procedure Adefinition
Procedure Bdefinition
4
5
Siemens GreeceD.I.P.2.2006
Page 81
Oracle PL/SQL Programming
Packages – Overview
• Group logically related PL/SQL types, items, and subprograms• Consist of two parts:
– Specification– Body
• Cannot be called, parameterized, or nested• Allow Oracle to read multiple objects into memory at once
Siemens GreeceD.I.P.2.2006
Page 82
Oracle PL/SQL Programming
Packages – Develop a Package
• Saving the text of the CREATE PACKAGE statement in two different text files facilitates later modifications to the package.
• A package specification can exist without a package body, but a package body cannot exist without a package specification.
• If you have incorporated a stand-alone procedure into a package, you should drop your stand-alone procedure.
• Keep packages as general as possible.• Define the package specification before the body.• The package specification should only contain public constructs.• The package specification should contain as few constructs as possible.
Siemens GreeceD.I.P.2.2006
Page 83
Oracle PL/SQL Programming
Packages – Create Package Specification/Body
CREATE [OR REPLACE] PACKAGE package_nameIS|AS public type and item declarations subprogram specificationsEND package_name;
CREATE [OR REPLACE] PACKAGE BODY package_nameIS|AS private type and item declarations subprogram bodiesEND package_name;
Siemens GreeceD.I.P.2.2006
Page 84
Oracle PL/SQL Programming
Packages – Advantages of Create Packages
• Modularity: Encapsulate related constructs.• Easier application design: Code and compile specification and body
separately.• Information hiding:
– Private constructs are hidden and inaccessible.
– All coding is hidden in the body. • Added functionality: Persistency of variables and cursors• Better performance:
– Entire package loaded into memory when the package is first referenced– Only one copy in memory for all users– Dependency hierarchy simplified
• Overloading: Multiple subprograms of the same name
Siemens GreeceD.I.P.2.2006
Page 85
Oracle PL/SQL Programming
Packages – Overloading
• Allows you to use the same name for different subprograms inside a package
• Requires the formal parameters of the subprograms to differ in number, order, or datatype family
• Allows you to build more flexibility. A user or application is not restricted by the specific datatype or number of formal parameters
• Restriction: Only local or packaged subprograms can be overloaded
Siemens GreeceD.I.P.2.2006
Page 86
Oracle PL/SQL Programming
Packages – Forward Declaration
• PL/SQL allows for a special subprogram declaration called a forward declaration. It consists of the subprogram specification terminated by a semicolon. You can use forward declarations to do the following:
– Define subprograms in logical or alphabetical order– Define mutually recursive subprograms– Group subprograms in a package
Siemens GreeceD.I.P.2.2006
Page 87
Oracle PL/SQL Programming
Packages – One-Time-Only Procedure
• A one-time-only procedure is executed one time only, when the package is first invoked within the user session.
– Initialize public or private variables with an automatic, one-time-only procedure when the derivation is too complex to embed within the variable declaration.
Siemens GreeceD.I.P.2.2006
Page 88
Oracle PL/SQL Programming
Packages – Restrictions on Package Functions
• INSERT, UPDATE, or DELETE are not allowed.
• Only local functions can update package variables.
• Remote functions cannot read or write remote package variables.
• Functions that read or write package variables cannot use the parallel query option.
• Calls to subprograms that break the above restrictions are not allowed.
Siemens GreeceD.I.P.2.2006
Page 89
Oracle PL/SQL Programming
Packages – Remove Package Specification/Body
DROP PACKAGE package_name
DROP PACKAGE BODY package_name
Siemens GreeceD.I.P.2.2006
Page 90
Oracle PL/SQL Programming
Triggers – Overview
• A trigger is a PL/SQL block that executes implicitly whenever a particular event takes place.
• A trigger can be either a database trigger or an application trigger.
Siemens GreeceD.I.P.2.2006
Page 91
Oracle PL/SQL Programming
Triggers – Designing Triggers
• Design triggers to:– Perform related actions– Centralize global operations
• Do not design triggers:– Where functionality already exists– Which duplicate other triggers
Siemens GreeceD.I.P.2.2006
Page 92
Oracle PL/SQL Programming
Triggers – Creating Triggers
• Trigger timing– For table
• BEFORE: Execute the trigger body before the triggering DML event on a table.• AFTER: Execute the trigger body after the triggering DML event on a table.
– For view• INSTEAD OF: Execute the trigger body instead of the triggering statement. Used for VIEWS that are not otherwise
modifiable.
• Triggering event– INSERT, UPDATE or DELETE
• Table name– On table or view
• Trigger type: – Row
• The trigger body executes once for each row affected by the triggering event.– Statement
• The trigger body executes once for the triggering event. This is the default.
• When clause– Restricting condition
• Trigger body– The trigger body is a PL/SQL block or a call to a procedure .
Siemens GreeceD.I.P.2.2006
Page 93
Oracle PL/SQL Programming
Triggers – Syntax (Statement Trigger)
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_nametrigger_body
Siemens GreeceD.I.P.2.2006
Page 94
Oracle PL/SQL Programming
Triggers – Conditional Predicates
• You can combine several triggering events into one by taking advantage of the special conditional predicates INSERTING, UPDATING, and DELETING within the trigger body.
Siemens GreeceD.I.P.2.2006
Page 95
Oracle PL/SQL Programming
Triggers – Syntax (Row Trigger)
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW [WHEN condition]trigger_body
Siemens GreeceD.I.P.2.2006
Page 96
Oracle PL/SQL Programming
Triggers – Syntax (INSTEAD OF Trigger)
CREATE [OR REPLACE] TRIGGER trigger_name INSTEAD OF event1 [OR event2 OR event3] ON view_name [REFERENCING OLD AS old | NEW AS new][FOR EACH ROW]trigger_body
Siemens GreeceD.I.P.2.2006
Page 97
Oracle PL/SQL Programming
Triggers – Difference Between Procedures & Triggers
Triggers
Use CREATE TRIGGER
Data dictionary contains source and p-code
Implicitly invoked
COMMIT, SAVEPOINT, ROLLBACK not allowed
Procedure
Use CREATE PROCEDURE
Data dictionary contains source and p-code
Explicitly invoked
COMMIT, SAVEPOINT, ROLLBACK allowed
Siemens GreeceD.I.P.2.2006
Page 98
Oracle PL/SQL Programming
Triggers – Triggers Execution Model
• Execute all BEFORE STATEMENT triggers• Loop for each row affected
– Execute all BEFORE ROW triggers– Execute the DML statement and perform integrity constraint checking– Execute all AFTER ROW triggers
• Complete deferred integrity constraint checking• Execute all AFTER STATEMENT triggers
Siemens GreeceD.I.P.2.2006
Page 99
Oracle PL/SQL Programming
Triggers – Database Triggers
• Triggering user event: – CREATE, ALTER, or DROP– Logging on or off
• Triggering database or system event:– Database shutting down or starting up
• A specific error or any error being raised
Siemens GreeceD.I.P.2.2006
Page 100
Oracle PL/SQL Programming
Triggers – Create DDLTriggers
CREATE [OR REPLACE] TRIGGER trigger_name timing [ddl_event1 [OR ddl_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body
Siemens GreeceD.I.P.2.2006
Page 101
Oracle PL/SQL Programming
Triggers – System Events Triggers
CREATE [OR REPLACE] TRIGGER trigger_name timing [database_event1 [OR database_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body
Siemens GreeceD.I.P.2.2006
Page 102
Oracle PL/SQL Programming
Triggers – CALL Statement
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new][FOR EACH ROW] [WHEN condition]CALL procedure_name
Siemens GreeceD.I.P.2.2006
Page 103
Oracle PL/SQL Programming
Triggers – Mutating Table
• A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity action. A table is not considered mutating for STATEMENT triggers.
• The triggered table itself is a mutating table, as well as any table referencing it with the FOREIGN KEY constraint. This restriction prevents a row trigger from seeing an inconsistent set of data.
Siemens GreeceD.I.P.2.2006
Page 104
Oracle PL/SQL Programming
Triggers – Implementation of Triggers
• SecuritySecurity• Auditing• Data integrity• Referential integrity• Table replication• Derived data• Event logging
Siemens GreeceD.I.P.2.2006
Page 105
Oracle PL/SQL Programming
Triggers – Benefits of Triggers
• Improved data security:– Provide value-based security checks– Provide value-based auditing
• Improved data integrity:– Enforce dynamic data integrity constraints– Enforce complex referential integrity constraints– Ensure related operations are performed together implicitly
Siemens GreeceD.I.P.2.2006
Page 106
Oracle PL/SQL Programming
Triggers – Removing Triggers
DROP TRIGGER trigger_name
Siemens GreeceD.I.P.2.2006
Page 107
Oracle PL/SQL Programming
Managing Subprograms & Triggers – Privileges
CREATE (ANY) PROCEDURE TRIGGER
ALTER ANY PROCEDURE TRIGGER
DROP ANY PROCEDURE TRIGGER
EXECUTE ANY PROCEDURE
DBA grants
System privileges
EXECUTE
Object privilegeOwner grants
Siemens GreeceD.I.P.2.2006
Page 108
Oracle PL/SQL Programming
Column
OBJECT_NAME
OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
Column Description
Name of the object
Internal identifier for the object
Type of object, for example, TABLE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER
Date when the object was created
Date when the object was last modified
Date and time when the object was last recompiled
VALID or INVALID
Managing Subprograms & Triggers – USER_OBJECTS
Siemens GreeceD.I.P.2.2006
Page 109
Oracle PL/SQL Programming
Column
NAME
TYPE
LINE
TEXT
Column Description
Name of the object
Type of object, for example, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY
Line number of the source code
Text of the source code line
Managing Subprograms & Triggers – USER_SOURCE
Siemens GreeceD.I.P.2.2006
Page 110
Oracle PL/SQL Programming
Managing Subprograms & Triggers – USER_ERROSR
Column
NAME
TYPE
SEQUENCE
LINE
POSITION
TEXT
Column Description
Name of the object
Type of object, for example, PROCEDURE,
FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER
Sequence number, for ordering
Line number of the source code at which the error occurs
Position in the line at which the error occurs
Text of the error message
Siemens GreeceD.I.P.2.2006
Page 111
Oracle PL/SQL Programming
Managing Subprograms & Triggers – USER_TRIGGERS
Column
TRIGGER_NAME
TRIGGER_TYPE
TRIGGERING_EVENT
TABLE_NAME
REFERENCING_NAMES
WHEN_CLAUSE
STATUS
TRIGGER_BODY
Column Description
Name of the trigger
The type is BEFORE, AFTER, INSTEAD OF
The DML operation firing the trigger
Name of the database table
Name used for :OLD and :NEW
The when_clause used
The status of the trigger
The action to take
Siemens GreeceD.I.P.2.2006
Page 112
Oracle PL/SQL Programming
Managing Subprograms & Triggers – SHOW ERRORS/DESCRIBE
• Use SHOW ERRORS without any arguments at the SQL prompt to obtain compilation errors for the last object you compiled.
• To display a procedure or function and its parameter list, use the DESCRIBE SQL*Plus command.
Siemens GreeceD.I.P.2.2006
Page 113
Oracle PL/SQL Programming
Dependent
xxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvv
ProcedureView/
procedure
Directdependency
Referenced
Referenced
Table
Directdependency
dependent
Indirectdependency
Dependencies – Direct/Indirect
Siemens GreeceD.I.P.2.2006
Page 114
Oracle PL/SQL Programming
xxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvvvvvvvvvvv
Procedure View
Local references
Procedure Tablevvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvvxxxxxxxxxxxxxxvvvvvvvvvvvvvv
Direct localdependency
INVALIDINVALIDINVALID
Definitionchange
The Oracle Server implicitly recompiles any INVALID object when the object is next called.
Dependencies – Local Dependencies
Siemens GreeceD.I.P.2.2006
Page 115
Oracle PL/SQL Programming
Dependencies – Show Dependencies
• Execute Script UTLDTREE • EXECUTE procedure deptree_fill.• Query DEPTREE and IDEPTREE viewsDEPTREE and IDEPTREE views
Siemens GreeceD.I.P.2.2006
Page 116
Oracle PL/SQL Programming
Oracle Supplied Packages – Overview
• Provided with the Oracle Server– DBMS_PIPE
• It allows two or more sessions connected to the same instance to communicate through a pipe just like pipes used in UNIX.
– DBMS_DDL• Provides access to some SQL DDL statements
– DBMS_JOB• Allows tasks to be scheduled and altered once in the queue.
– DBMS_OUTPUT • Allows you to output messages from PL/SQL blocks
• Extend the functionality of the database• Allow access to certain SQL features normally restricted for PL/SQL
Siemens GreeceD.I.P.2.2006
Page 117
Oracle PL/SQL Programming
Oracle Supplied Packages – DBMS_PIPE
• PACK_MESSAGE• SEND_MESSAGE• RECEIVE_MESSAGE• UNPACK_MESSAGE
Siemens GreeceD.I.P.2.2006
Page 118
Oracle PL/SQL Programming
Oracle Supplied Packages – DBMS_SQL /EXECUTE IMMEDIATE
• OPEN_CURSOR• PARSE• BIND_VARIABLE• EXECUTE• FETCH_ROWS• CLOSE_CURSOR
Siemens GreeceD.I.P.2.2006
Page 119
Oracle PL/SQL Programming
Oracle Supplied Packages – DBMS_DDL
• ALTER_COMPILE• ANALYZE_OBJECT
Siemens GreeceD.I.P.2.2006
Page 120
Oracle PL/SQL Programming
Oracle Supplied Packages – DBMS_JOB
• SUBMIT • RUN• USER_JOBS
Siemens GreeceD.I.P.2.2006
Page 121
Oracle PL/SQL Programming
Oracle Supplied Packages – DBMS_OUTPUT
• PUT• NEW_LINE• PUT_LINE• GET_LINE• GET_LINES• ENABLE/DISABLE
Siemens GreeceD.I.P.2.2006
Page 122
Oracle PL/SQL Programming
Oracle Supplied Packages – DBMS_LOB
• Modify LOB values:– APPEND, COPY, ERASE, TRIM, WRITE, LOADFROMFILE
• Read or examine LOB values:– GETLENGTH, INSTR, READ, SUBSTR
• Specific to BFILEs:– FILECLOSE, FILECLOSEALL, FILEEXISTS, FILEGETNAME, FILEISOPEN,
FILEOPEN