Oracle Basics

PL/SQL Introduction

PL/SQL is Oracle corporation's procedural language extension to SQL, the standard data access language for RDBMS and ORDBMS It brings state-of-the-art programming to the Oracle server and Tool set Contains all SQL DML functionality

PL/SQL Architecture
PL/SQL run time system is a technology,not an independent product. This technology is an engine that executes PL/SQL blocks and subprograms PL/SQL resides in two environments:

Oracle server Oracle tools

PL/SQL engine accepts PL/SQL block or subprograms as input Engine executes procedural statements but sends SQL statements to SQL statement executor

PL/SQL Architecture

PL/SQL block issued from:

Forms(Triggers) SQL*Plus Oracle Pre-compilers Oracle Call Interface (OCI)

PL/SQL engine located in:

Forms RDBMS

Oracle Basics
PL/SQL Execution Environments The PL/SQL Engine

PL/SQL BLOCK DECLARE Procedural Procedural BEGIN Procedural SQL Procedural SQL END;

PL/SQL BLOCK DECLARE Procedural Procedural BEGIN Procedural SQL Procedural SQL END; PROCEDURAL STATEMENT EXECUTOR

To the statement Executors in the ORACLE RDBMS

Oracle Basics
PL/SQL BLOCK STRUCTURE

DECLARE

BEGIN

EXCEPTION

Oracle Basics
PL/SQL BLOCK STRUCTURE Declaration Section Executable Section Exception Handler Section
Quick Note - Block structuring 1. Any block may contain sub-block.Sub-blocks may appear anywhere an executable statement may legally appear. 2.Statement end with a; 3.comments are preceded by -- or surrounded by /* */ Declared objects exist within a certain scope (addressed later in this course).

Oracle Basics

Oracle Basics
Variable Declarations Overview
Syntax of Declaration identifier [constant ] datatype [not null ] [:= plsql_expression ] ; Quick Notes- variable Declaration 1. The rules for identifiers are same as for SQL objects.
2. NOT NULL may be used to constrain a variable so that it cannot have NULL value

3. Only one identifier per line is allowed . For ex. DECLARE fristname lastname CHAR(20) ; - illegal DECLARE fristname CHAR(20) ; -legal lastname CHAR(20) ; - legal

Oracle Basics
Variable Declaration over
NUMBER Count revenue second_per_day running _total CHAR mid_initial last_name company_name
DATE anniversary DATE ;= 05-NOV-78; project_complexion DATE; next_checkup DATE NOT NULL ;= 28-JUN-90; BOOLEAN over_budget available BOOLEAN NOT NULL := FALSE; BOOLEAN := NULL ; NUMBER; NUMBER (9,2); CONSTANT NUMBER := 60 * 60* 24 ; NUMBER (10,0) ;= 0; CHAR; CHAR(10) NOT NULL := PEEBLES; CONSTANT CHAR(12) := ORACLE;

Oracle Basics
Attribute Declaration
PL/SQL objects (such as variables and constants) and database objects(such as col. and tables )are associated with certain attributes. %TYPE attribute Ex. DECLARE books_printed NUMBER (6); books_sold books_printed%TYPE ; maiden_name emp.ename%TYPE ; %ROWTYPE attribute DECLARE dept_row dept%ROWTYPE ;

Ex.

Oracle Basics
Variable Assignment
PL/SQL Expressions consist of Variables, Constants, Literals, and Function Calls.
Operation ASSIGNMENT Syntax plsql_variable := plsql_expression; Quick notes -Assignment 1. := (ASSIGNMENT )= (VALUE EQUALITY) 2. The datatype of the left and right hand side of an assignment must be the same or implicitly convertible to each other.

For ex. , N:=7 is legal because number may be implicitly converted to char.

3.Column or table reference are not allowed or either side of an either side of an assignment operator( : = ).
SCOTT.EMP.EMPNO := 1234; location := dept.loc.; These are illegal

Oracle Basics
Scoping Variables and Constants.
SCOPE refers to the visibility of identifiers at different points in the PL /SQL block. SCOPING RULES:

1. An identifier is visible in the block in which it is declared and all its sub-blocks unless rule #2 applies.
2. If an identifier in an enclosing block is redeclared in a sub-block, the original identifier declared in the enclosing block is no longer visible in the sub-block .However, the newly declared identifier has the rules of scope defined in rule #1.

Oracle Basics
Scoping Variables and Constants.
DECLARE credit_limit CONSTANT NUMBER (6,2) : =2000; account NUMBER;
BEGIN DECLARE account CHAR(10); new_balance NUMBER (9,2); BEGIN new_balance account END; DECLARE account CHAR(10); new_balance NUMBER (9,2); BEGIN old_balance account END; END; account

credit_limit

credit_limit

credit_limit

Oracle Basics

Oracle Basics
SQL & PL/SQL Overview
SQL Data Manipulation Language statement support 1. INSERT 2. UPDATE 3. DELETE 4. SELECT
QuickNotes - SQL DML Support 1. The full ORACLE syntax is supported for these statements 2. A PL/SQL variable may be placed anywhere a constant may be legally placed. 3. An identifier is first checked to see if it is a column in the database . If not , it is assumed to be a PL/SQL identifier.

4.These statements may not appear as part of an expression.

Oracle Basics
SQL & PL/SQL Overview
INSERT DECLARE
my_sal NUMBER(7,2) := 3040.22; my_ename CHAR(25) := WANDA; my_hiredate DATE := 08-SEP-01; BEGIN INSERT INTO emp (empno,ename,job,hiredate,sal ,deptno) VALUES (2345,my_ename,cab Driver,my_hiredate,my_sal,20); END;

E M P N OE N A M ES A L 7 6 4 4 T U R N E R1 5 0 0

E M P N O E N A M ES A L 7 6 4 4 T U R N E R1 5 0 0 7 4 0 0 A L L E N1 6 0 0

INSERT 7400 ALLEN 1600

Oracle Basics
SQL & PL/SQL Overview
UPDATE DECLARE
max_allowed CONSTANT N UMBER := 5000; good_cust CHAR(8) := VIP; BEGIN UPDATE ACCOUNT SET CREDIT_LIMIT = MAX_ALLOWED WHERE TYPE = EMPOLEE OR TYPE =good_cust ;

END;
E M P N OE N A M ES A L 7 6 4 4 T U R N E R1 5 0 0 7 4 0 0 A L L E N1 6 0 0

E M P N O E N A M ES A L 7 6 4 4 T U R N E R1 5 0 0 7 4 0 0 A L L E N1 4 0 0

UPDATE

Oracle Basics
SQL & PL/SQL Overview
DELETE DECLARE
bad_child_type CHAR(8) := NAUGHTY; BEGIN DELETE FROM santas_gift_list WHERE kid_rating =bad_child_type ; END;

E M P N OE N A M ES A L 7 6 4 4 T U R N E R1 5 0 0 7 4 0 0 A L L E N1 6 0 0

E M P N OE N A M ES A L 7 6 4 4 T U R N E R1 5 0 0

DELETE

Oracle Basics
SQL & PL/SQL Overview
APPLICATION
E M P N OE N A M ES A L 7 6 4 4 T U R N E R1 5 0 0 7 4 0 0 A L L E N1 6 0 0

VAR1
VAR2 VAR3

QuickNotes - SELECT INTO 1 A SELECT statement is the only DML that returns data .You must provide location for this data to be stored via the INTO clause. 2. A SELECT..INTO statement must return exactly one row .Zero or multiple returned rows result in an error. 3. For multi-row SELECTs use cursors (discussed later).

Oracle Basics
SQL & PL/SQL Overview
SELECT Syntax
SELECT col1,col2INTO var1,var2.. FROM table_name WHERE ...

SELECT Ex.
DECLARE part_name num_in_stock BEGIN SELECT name, num INTO part_name, num_in_stock FROM PARTS WHERE part_id = 234; parts.name%TYPE; parts.num%TYPE;

----manipulate the retrieved data here

Oracle Basics
Transaction processing
SAVEPOINT Syntax
SAVEPOINT < marker_name >;

ROLLBACK TO Syntax
ROLLBACK [WORK] TO SAVEPOINT < marker_name >;

SAVEPOINT and ROLLBACK TO Ex.

BEGIN INSERT INTO temp VALUES (1,1 ROW 1); SAVEPOINT A; INSERT INTO temp VALUES (2,2 ROW 2); SAVEPOINT B ; . ROLLBACK TO SAVEPOINT B; COMMIT ; END;

Oracle Basics
SQL Functions
SQL Functional support(within a SQL Statement): 1. Numeric (e.g. SQRT,ROUND,POWER) 2. Character (e.g. LENGTH,UPPER) 3. Date (e.g. ADD_MONTHS,MONTH_BETWEEN); 4. Group(e.g. AVG,MAX,COUNT)
INSERT INTO phonebook (lastname) VALUES (UPPER(my_lastname)); OTHER SQL Functional support (outside of a SQL Statement):

1.MOST ORACLE SQL functional are available (except for group functions). X := SQRT(y); lastname := UPPER (lastname);

age_diff := MONTHS_BETWEEN(birthday1,birthday2)/12;

Oracle Basics

Oracle Basics
Logical Comparisons
Logical Comparisons form the basis of conditional control in PL/SQL; the result of these comparisons are always either TRUE ,FALSE or NULL.
1. Anything compared with NULL results in a NULL value.

2. A NULL in an expression evaluates to NULL (except concatenation)

Ex. 5 + NULL -evaluate to NULL PL/ || NULL || SQL evaluate to PL/SQL PL /SQL Datatypes NUMBER CHAR DATE BOOLEAN Operators <,> =, !=

<=, >=

Oracle Basics
Logical Comparisons

Boolean Operators: AND ,OR,NOT

AND TRUE FALSE NULL TRUE FALSE NULL OR TRUE TRUE FALSE NULL

T F N

F F F

N F N
NOT TRUE FALSE NULL

T F N

F F F

N F N

FALSE
NULL

F T N

Oracle Basics
If Statements
If statements are used to conditionally execute the statement or sequence of statements.
IF Statements syntax IF <condition> THEN <sequence of statement > [ELSEIF <condition> THEN <sequence of statement > ] ---ELSEIFs may be repeated [ELSE <sequence of statements>] END IF; QuickNotes -- IF Statements

1. <condition> must evaluate to a Boolean datatype (I.e. TRUE ,FALSE,or NULL) 2. If <condition is TRUE ,then the associated <sequence of statement is executed ;otherwise, it is not. 3. At most one <sequence of statement > gets executed.

Oracle Basics
If Statements Ex.
DECLARE num_jobs NUMBER(7); BEGIN SELECT COUNT(*) INTO num_jobs FROM auditions WHERE actorid=&&actor_id AND called_back =YES; IF num_jobs> 90 THEN UPDATE actor SET actor_rating = OSCAR time WHERE actorid = &&actor_id; ELSE IF num_jobs> 75 THEN UPDATE actor SET actor_rating = DAY time soaps WHERE actorid = &&actor_id; ELSE UPDATE actor SET actor_rating = Waiter WHERE actorid = &&actor_id; END IF; COMMIT; END;

Oracle Basics
If Statements The NULL Trap
BLOCK 1
. IF a >= b THEN do this ..; BLOCK 2

.
IF b > a THEN do that ..; ELSE do_this.; END IF;

ELSE do_this.;
END IF;

Given any pair of non-NULL values for a andb, will Block 1 and Block 2 do the same thing?

What if either a orb (or both) is NULL ?

Oracle Basics Loop Statement Overview Loops repeat a statement or sequence of statements multiple times.
Four types of loop:

1. Simple Loops.
2. Numeric For Loops. 3. While Loops. 4. Cursor FOR Loops.

Oracle Basics
Loop Statements

Simple Loops repeat sequence of statements multiple times.

Simple Loop syntax
Loop <sequence of statement> END LOOP ;---sometimes called an infinite loop

Exit statements exit any type of loop immediately.

Exit Syntax
EXIT [WHEN <condition >]; -- infinite loop insurance

Oracle Basics Loop Statements Example

DECLARE ctr NUMBER(3) := 0; BEGIN LOOP INSERT INTO LOG VALUES (ctr,ITERATION COMPLETE); ctr := ctr +1; IF ctr = 1500 THEN EXIT; END IF; END LOOP; END; DECLARE ctr NUMBER(3) := 0; BEGIN LOOP UPDATE TABLE 1 SET COMMIT = UPDATES WHERE COUNT_COL = ctr;; ctr := ctr +1; IF ctr = 1500 THEN EXIT; END IF; END LOOP; END;

Oracle Basics
Loop Statements
Numeric FOR Loops repeat sequence of statements fixed number of times. Numeric FOR Loop Syntax
FOR <index> IN [REVERSE ] <integer>..<integer> LOOP <sequence of statements>

The Loop Index takes on each value in range , one of a time , either in forward or reverse order.
Example. BEGIN FOR i IN 1..500 LOOP INSERT INTO temp(message)VALUES (I will not sleep in class.); END LOOP; END;

Oracle Basics
Loop Statement
QuickNotes - Index :

1. It is implicitly of type NUMBER. 2. It is only defined within the loop . 3. Value may be referenced in an expression,but a new value may not be assigned to it within the loop
Example
DECLARE my_index CHAR(20) := Fettuccini Alfredo; BEGIN FOR my index IN REVERSE 2130 LOOP /* redeclare s my_index*/ INSERT INTO temp(coll.)VALUES (my_index); /* insert the numbers 30 through 21*/ END LOOP; END;

FOR

i I N 1256 LOOP x := x + i ;----legal i := I + 5;----illegal END LOOP;

Oracle Basics
Loop Statements
WHILE Loops repeat a sequence of statements until a specific condition is no longer TRUE. While Loop Syntax WHILE <condition > LOOP <sequence of statements > END LOOP; QuickNotes - WHILE Loops 1. The term <condition> may be any legal PL/SQL condition (I.e. it must return a Boolean value of TRUE,FALSE,or NULL). 2. The sequence of statements will be repeated as long as <condition> evaluates to TRUE. Ex. DECLARE ctr NUMBER (3) := 0; BEGIN WHILE ctr < 500 LOOP INSERT INTO temp (message) VALUES (Well,I might sleep just a little); ctr := ctr +1 ; END LOOP; END;

Oracle Basics GO TO Statement Overview GO TO Statements jump to a different place in the PL/SQL block.
GO TO Statements have parts

1. The GOTO statement itself.

2. A statement label GO TO Statement Syntax <<label_name >> X :=X+1 ; - - statement label GOTO LABEL_NAME - - JUMPS TO x := x +1

Oracle Basics
GO TO Statements
NOT ALL GOTOs are Legal !
You can legally a GOTO a statement that is either: 1.in the same sequence of statements as the GOTO STATEMENT 2. In the sequence of statements that encloses the GOTO statement (I.e. an outer block)

<<dinner>>
x := x + 1 ; y := y + 1; IF a >= b THEN

GOTO tour_brothers; IF a > b THEN b := b - a; <<your_brothers>> x := x - 1;

b : = b + c;
GOTO dinner; END IF;

END IF;

Oracle Basics Other Uses for Statement Labels

Labels may label any statement.

In addition to their use as targets for GOTO statements, labels may be used for :
1. Blocks 2. Loops Labeling a block allows referencing of DECLARED objects that would otherwise not be visible because of Scoping rules. Syntax << label_name>> [ DECLARE -- declarations go here ] BEGIN -- executable statements go here [ EXCEPTION -- exception handlers go here ] END label_name ; -- must include the label_name

Oracle Basics Other Uses for Statement Labels

Example << outer_block >> DECLARE n NUMBER; BEGIN n := 5; /* start a sub_block */ DECLARE x NUMBER := 10; n CHAR (10) := Fifteen; BEGIN INSERT INTO TEMP VALUES (outer_block.n ,x ,n ); COMMIT; END ; /* end of the sub_block */ END outer_block;

Oracle Basics Other Uses for Statement Labels

Labeling a Block allows you to reference a variable that might be hidden by a column name.
Example << sample >> DECLARE deptno NUMBER := 20; BEGIN UPDATE emp SET sal = sal * 1.1 WHERE deptno = sample.deptno; COMMIT; END sample;

Oracle Basics Other Uses for Statement Labels

Labeling LOOPS allows you to reference objects that would otherwise not be visible because of scoping rules. EXAMPLE << compute_loop >> For i IN 110 LOOP < statements . > DECLARE i NUMBER := 0 ; BEGIN INSERT INTO temp VALUES (i, compute_loop.I, COMPLETE ); END; END LOOP compute_loop; - must include loop name here

Oracle Basics Other Uses for Statement Labels

Labeling EXITs is a convenient way to specify exits from
outer loops. EXAMPLE << outer_loop >> WHILE a > b LOOP b := b + 1; <<inner_loop >> WHILE b > c LOOP c := c + 2 ; EXIT outer_loop WHEN c > 200 ; END LOOP inner_loop; END LOOP outer_loop;

Oracle Basics

Oracle Basics
Cursor Overview
Every SQL DML statement processed by PL/SQL has an associated CURSOR. Two Types of CORSORS 1. EXPLICIT

. Multiple row SELECT STATEMENTS

2. IMPLICIT All INSERT statements

All UPDATE statements

All DELETE statements Single row SELECT.INTO Statements

Oracle Basics
Using explicit cursors
STEP 1 . Declare the cursor DECLARE CURSOR <cursor name>

IS <regular select statement> ;

QuickNotes - CURSOR Declaration

1. The < regular select statement > must NOT include the INTO clause required in a single-row SELECT.INTO statement. 2. Declared cursors are scoped just like variables.

Cursor Declaration Example

DECLARE X NUMBER ( 7, 2 ) ; total NUMBER ( 5 ) lower_sal_limit CONSTANT NUMBER ( 4 ) := 1200 ; CURSOR c1 IS SELECT ename FROM emp WHERE sal > lower_sal_limit ; BEGIN ...

Oracle Basics
Using Explicit Cursors
STEP 2 . Open the cursor OPEN < cursor name > ; STEP 3 . Fetch data from the cursor

FETCH < cursor name > INTO < var1 ,var2 >> > ;
Quick Notes - FETCH 1. Retrieves one row of data from the cursor , and stores it in the specified variables ( similar to how a single-row select works ) . 2. There must be exactly one INTO variable for each column selected by the SELECT statement . 3. The first column gets assigned to var1 , the second to var2 , etc . STEP 4 . Close the cursor CLOSE < cursor name > ;

Oracle Basics Explicit Cursors Attributes

%NOTFOUND

%NOTFOUND Example
LOOP FETCH my_cursor INTO my_ename , my_sal ; EXIT WHEN my_cursor%NOTFOUND ; -- process data here END LOOP ; %FOUND

%FOUND Example
FETCH my_cursor INTO my_ename ,my_sal ; WHILE my_cursor INTO my_ename , my_sal ; -- process data here FETCH my_cursor INTO my_ename ,my_sal ; END LOOP ;

Oracle Basics Explicit Cursor Attributes

%ROWCOUNT

%ROWCOUNT Example LOOP FETCH my_cursor INTO my_ename , my_sal ; EXIT WHEN ( my_cursor%NOTFOUND ) OR ( my_cursor%ROWCOUNT > 10 ) ; -- process data here END LOOP
%ISOPEN %ISOPEN Example IF my_cursor%ISOPEN THEN FETCH my_cursor INTO my_ename , my_sal ; ELSE OPEN my_cursor ; END IF ;

Oracle Basics Using Explicit Cursors

Example
DECLARE sal_limit NUMBER ( 4 ) := 0 ; my_ename emp.ename%TYPE ; my_sal emp.sal%TYPE ; CURSOR my_cursor IS SELECT ename , sal FROM emp WHERE sal > sal_limit ; BEGIN sal_limit := 1200 ; OPEN my_cursor INTO my_ename , my_sal ; LOOP FETCH my_cursor INTO my_ename , my_sal ; EXIT WHEN my_cursor%NOTFOUND ; -nothing returned INSERT INTO new_table VALUES ( my_ename , my_sal ) ; END LOOP ; CLOSE my_cursor ; COMMIT ; END ;

Oracle Basics Explicit Cursors -FOR Loops

Cursor FOR Loops specify a sequence of statement to be repeated once for each row that is returned by the cursor. Cursor FOR Loop Syntax FOR <record _name> IN <cursor_name> LOOP ---statements to be repeated go here END LOOP; Numeric FOR Loop Similarities 1. Specify a set of rows from a table by using the cursors name vs. specifying a set of integers (i.e. 110) 2. Index takes on the values of each row vs. index taking on integer values (I.e. 1 through 10) Implicitly Declared <record_name> record_name cursor _name%ROWTYPE; To reference an element of the record, use the record_name.column_name notation.

Oracle Basics Explicit Cursors -FOR Loops

Conceptual Cursor Loop Model

Loops

When a cursor loop is initiated, an implicit OPEN cursor_name is executed. For each row that satisfies the query associated with the cursor an, implicit FETCH is executed into the components of record_name.

Loops

Loops

When there are no more rows left to FETCH, an implicit CLOSE cursor_name is executed and the loop is exited.

Oracle Basics Explicit Cursors - FOR Loops

Example DECLARE sal_limit NUMBER ( 4 ) := 0 ; total_sal NUMBER (9,2 ) := 0; CURSOR my_cursor IS SELECT ename , sal FROM emp WHERE sa l > sal_limit ; BEGIN sal_limit := 1200 ; -- implicitly OPEN done next FOR cursor_row IN my_cursor LOOP -- an implicit fetch done here INSERT INTO new_table VALUES (cursor_row.ename ,cursor.sal ) ; total_sal := total_sal + cursor_row.sal END LOOP ; --an implicit close done here. COMMIT ; END ;

Oracle Basics Implicit Cursors - FOR Loops

An Implicit Cursor is automatically associated with any SQL DML statement that does not have an explicit cursor associated with it.
This includes : 1. ALL INSERT statements 2. ALL UPDATE statements 3. ALL DELETE statements 4. ALL SELECTINTO statements QuickNotes - Implicit Cursors 1. Implicit cursor is called the SQL cursor --it stores information concerning the processing of the last SQL statement not associated with an explicit cursor.

2.OPEN, FETCH, AND CLOSE dont apply.

3. All cursor attributes apply.

Oracle Basics
Implicit Cursors
SQL %NOTFOUND

SQL %NOTFOUND Example

UPDATE emp SET sal = sal * 10.0 WHERE ename =WARD ; IF SQL %NOTFOUND THEN ---WARD wasnt found INSERT INTO emp (empno, ename ,sal) VALUES ( 1234,WARD 99999 ); END IF ; SQL %FOUND

Oracle Basics
Implicit Cursors
SQL%ROWCOUNT SQL%ROWCOUNT Example DELETE FROM baseball_team WHERE batting _avg. < .100; IF SQL%ROWCOUNT > 5 THEN INSERT INTO temp(message) VALUES(Your team needs helps .); END IF; SQL %ISOPEN always evaluate to FALSE.

Oracle Basics

Oracle Basics
Exception Overview

In PL/SQL error are called exceptions.

When an exception is raised, processing jumps to the exception handlers.

An exception handler is a sequence of statements to be processed when a certain exception occurs.

When an exception handler is complete processing of the block terminates.

Oracle Basics
Exception Overview Two Types of Exceptions 1. PREDEFINED INTERNAL EXCEPTIONS 2. USER-DEFINED EXCEPTIONS PL/SQLs Exception Handlers vs. Conventional Error Handling

Oracle Basics Predefined Internal Exceptions

Any ORACLE error raises an exception automatically ; some of the more common ones have names.

Example
TOO_MANY_ROWS ORA-(01427) - a single row SELECT returned more than one row NO_DATA_FOUND ORA-(01403) - a single row SELECT returned no data INVALID_CURSOR ORA-(01001) - invalid cursor was specified VALUES_ERROR ORA-(06502) - arithmetic ,numeric, string , conversion,or constraint error occurred. ZERO_DIVIDE ORA-(01476) - attempted to divide by zero DUP_VAL_ON_INDEX ORA-(00001) - attempted to insert a duplicate value into a column that has a unique index specified.

Oracle Basics
Exception Handlers
Syntax WHEN <exception_name [OR <exception_name] then <sequence of statements> OR WHEN OTHERS THEN -- if used , must be last handler < sequence of statements> Example DECLARE employee_num emp.empno%TYPE; BEGIN SELECT empno INTO employee_num FROM emp WHERE ename = BLAKE; INSERT INTO temp VALUES(NULL, empno,Blake's employee_num); DELETE FROM emp WHERE ename =BLAKE; EXCEPTION WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN ROLLBACL; INSERT INTO temp VALUES (NULL,NULL,Blake not found, or more than one Blake); COMMIT; WHEN OTHERS THEN ROLLBACK; END;

Oracle Basics
User - Defined Exceptions
User - defined Exceptions must be defined and explicitly raised by the user. EX. DECLARE x NUMBER; my_exception E XCEPTION; -- a new object type.. Raise your_exception; RAISE my_exception;

Quick Notes -RAISE <exception_name>

1. Once an exception is RAISED manually, it is treated exactly the same as if it were a predefined internal exception. 2. Declared exceptions are scoped just like variables. 3. A user-defined exception is checked for manually and then RAISED , if appropriate.

Oracle Basics
User - Defined Exceptions
Example

DECLARE my_ename emp.ename%TYPE :=BLAKE; assigned_projects NUMBER; too_few_projects EXCEPTION BEGIN ---- get no of projects assigned to BLAKE IF assigned_project < 3 THEN RAISE too_few_projects; END IF; EXCEPTION --begin the exception handlers WHEN too_few_projects THEN INSERT INTO temp VALUES(my_ename,assigned_projects,LESS THAN 3 PROJECTS!) COMMIT; END;

Oracle Basics
Exceptions Propagation Propagation
Step #1 The current block is searched for a handler .If not found, go to step 2.
Step#2 If an enclosing block is found, it is searched for it handler. Step#3 Step #1 and#2 are repeated until either there are no more enclosing blocks, or a handler is found . - If there are no more enclosing blocks, the exception is passed back to the calling environment (SQL *Plus,SQL *Forms, a precompiled program,etc.) - If the handler is found ,it is executed .when done the block in which the handler was found is terminated, and control is passed to thee enclosing block (if one exists), or to environment (if there is enclosing block) Quick notes 1. Only one handler per block may be active at a time.

2. If an exception is raised in a handler, the search for a handler for the new exception begins in the enclosing block of the current block.

Oracle Basics
Exceptions Propagation Example 1
BEGIN ...
BEGIN IF X=1 THEN RAISE A: ELSEIF X=2 THEN RAISE B; ELSE RAISE C; EXCEPTION
Exception A is handled locally and execution resumes in the outer block

WHEN A THEN
... END;

Oracle Basics
Exceptions Propagation Example 2
BEGIN ...
BEGIN IF X=1 THEN RAISE A: ELSEIF X=2 THEN RAISE B; ELSE RAISE C; EXCEPTION
Exception B PROPAGATES to the first outer block with an appropriate handler

WHEN A THEN ... END;

Exception B handled and control is passed back to the calling environment

Oracle Basics
Exceptions Propagation Example 3
BEGIN ...
BEGIN IF X=1 THEN RAISE A: ELSEIF X=2 THEN RAISE B; ELSE RAISE C; EXCEPTION

WHEN A THEN .. END;

Exception C has no handler and will result in runtime unhandled exception

Oracle Basics
Other uses of RAISE By itself ,the RAISE statement simply re-raise the current exception (as if it were being propagated). Syntax

RAISE

Quick Notes -RAISE 1. RAISE may only be used in an exception handler

Oracle Basics
EXCEPTION_INIT
Exceptions may only be handled by name (not ORACLE error number).
EXCEPTION_INIT allows naming of nay ORACLE error. SYNTAX PRAGMA EXCEPTION_INIT(<user defined exception_name > , <ORACLE_error_number>); Example DECLARE

deadlock_detected

exception;

PRAGMA EXCEPTION_INIT (deadlock_detected, -60);

Oracle Basics
Error Reporting Functions
SQLCODE and SQLERRM

1 . Provided information on the exception currently being handled.

2. Especially useful in the OTHERS handler. SQLCODE 1. Returns the ORACLE error number of the exceptio0n or 1 if it was user-defined exception . SQLERRM 1 .Return the ORACLE error message currently associated with the current value of SQLCODE

2. May also use any ORACLE error number as an argument.

QuickNotes - Error Reporting
1. If no exception is active SQLCODE = 0 SQLERRM = normal , successful completion 2. SQLCODE and SQLERRM cannot be used within a SQL statement.

Oracle Basics
Error Reporting Functions
Ex. DECLARE sqlcode_val NUMBER;

sqlcode_val
BEGIN EXCEPTION

CHAR(70);

WHEN OTHERS THEN sqlcode _val := SQLCODE - - - cant insert - - - directly. sqlerrm_val := SQLERRM ; - - ditto INSERT INTO temp VALUES(sqlcode_val, NULL,sqlerrm_val); END;

Oracle Basics

Oracle Basics
Stored procedures and Functions Collections of SQL and PL/SQL statements. Stored in complied from in the database.

Can call others and self.

Can be called from all client environments Procedures and function (including remote) are the same, except a function returns a values and a procedure does not.

Oracle Basics
Uses for procedures

Define central well-known business functions.

- Create an order - Delete a customer

Store batch job in the database

-Weekly account rollups Encapsulate a transaction - Gather and process information from remote nodes Funnel activity on a table through a single path - all changes to employee salaries should change department budgets.

Oracle Basics
Creating a procedure
Argument Modes

IN O U T

D a tav a lu ec o m e sinfro mth ec a llin g p ro c e s sa n disn o tc h a n g e d N od a tav a lu ec o m e sinfro mth ec a llin g p ro c e s s ;o nn o rm a le x it ,v a lu eo fa rg u m e n t isp a s s e db a c ktoc a lle r D a tav a lu ec o m e sinfro mth ec a llin g p ro c e s s ,a n da n o th e rv a lu eisre tu rn e do n n o rm a le x it

INO U T

Oracle Basics
Creating a procedure
Example
CREATE PROCEDURE fire_employee (empid NUMBER) AS BEGIN DELETE FROM emp WHERE empno= fire_employee.empid; END

Tip:Write each procedure in a text file, and save(both P-code and source
code is saved in the database)

Oracle Basics
Creating and changing a function
Example
CREATE FUNCTION get_bal (acc_no NUMBER(4)) RETURN IS acc_bal NUMBER(11,2); BEGIN SELECT balance NUMBER(11,2);

INTO

acc_bal

FROM accounts WHERE account_id_no=acc_no; RETURN (acc_bal); END;

Oracle Basics
Statements in procedures
Valid statements in a procedure or function
SQL DML or PL/SQL statements Calls to other procedures and functions stored in the database Calls to other procedures and functions in a remote database Restricted statements DDL Dynamic SQL In trigger, COMMIT,SAVEPOINT, and ROLLBACK

Oracle Basics
Executing a stored procedure
From within a PL/SQL block fire_employee (empno); scott.fire_employee (empno);

On a remote node scott.fire_employee@ny (empno);

From SQL*DBA and some ORACLE tools EXECUTE fire_employee (1043)

Within an anonymous block (when EXECUTE not available) SQLPLUS>BEGIN

FIRE_EMPLOYEE(1043);END; From a precompiler application EXEC SQL

fire_employee (:empno);

Oracle Basics
Executing a stored procedure
From OCI

CALL OSQL3 (cursor, /*parse*/ BEGIN scott.fire_employee(:empno); END;); CALL OBNDRV (..); /*bind vars*/ CALL OEXEC (..); /*EXECUTE*/

Oracle Basics
Specifying procedure arguments
Example
CREATE PROCEDURE update_sal (empno NUMBER, bonus NUMBER, sal_incr NUMBER) .;

Positional method
List values in the order they are declared update_sal (7000,20,500);

Named method

List argument names and values in any order, using special syntax update_sal (bonus=>20, sal_incr=>500, empno=>7000);

Oracle Basics
Specifying procedure arguments
Combination method
Use both positional and named methods Once you specify a named parameter, must use named method for all remaining update_sal (7000,sal_incr=>500,bonus=>20); update_sal (empno=>7000, sal_incr=>500,20); Illegal Legal

Oracle Basics
How procedures are entered into the database
PL/SQL engine compiles the source code ORACLE stores a procedure in a database: Object name Source code Parse tree Pseudo code(P-code) Syntax errors in dictionary table Dependency information SQL in procedure not stored in parsed form Uses shared and cached SQL Allows SQL to be optimized dynamically (without recompiling referencing procedures)

Oracle Basics
PL/SQL Compilation Errors
Compile done by PL/SQL engine in RDBMS
Error stored in the database To view errors:
Use SQL*DBA command SHOW ERRORS

Select from errors views

USER_ERRORS ALL_ERRORS DBA_ERRORS

Oracle Basics
PL/SQL Compilation Errors Executing SHOW ERRORS
SQLDBA>create procedure test1 is begin test2;end; DBA-00072: Warning: PROCEDURE TEST1 created with compilation errors. SQLDBA>show errors ERRORS FOR PROCEDURE TEST1: LINE/COL ERROR

--------------------------------------------------------------------------------------------1/0 1/33 PL/SQL: Compilation unit PL/SQL-00219:test2 is not defined analysis terminated

2 rows selected

Oracle Basics
PL/SQL Compilation Errors
Fields in ERRORS views
NAME:name of the object TYPE: one of the following: PROCEDURE FUNCTION PACKAGE

PACKAGE BODY
LINE:line number where error occurs TEXT:text of error

Oracle Basics
USER-DEFINED System Errors
Any procedure can raise an error and return a user defined error message
and error number Error number range is -20000 to -20999 Range always reserved for user defined errors Oracle does not check if user defined error numbers are used uniquely Raise error within PL/SQL block with procedure raise application_error (error_number,text of the message) Full pathname of procedure may be needed in early releases sys.standard_utilities. Raise_application_error

Oracle Basics
USER-DEFINED System Errors
Example
CREATE PROCEDURE fire_employee (empid NUMBER) AS BEGIN IF empid <=0 THEN raise_application_error (-20100,Employee number must be> 0); ELSE DELETE FROM emp WHERE EMPNO =EMPID; END IF; END; SQLDBA> EXECUTE FIRE_EMPLOYEE(-1); ORA=-20100: Employee number must be >0

Oracle Basics
Debugging methods
Version 6
User INSERTS information into a user defined table, and examines data

Version 7
PL/SQL will have methods of I/O to system defined table(TIO$LINES)
TEXT_IO package Rolls back or commits with transaction DEBUG_IO package

Writes despite error, rollback,commit

Can use Version6 method

Future
A PL/SQL debugger

Oracle Basics
Dependencies and Procedures
A procedure is dependent on:
every database object to which it refers (direct dependency) procedures,functions,packages,tables,views,synony ms,sequences the database objects those objects depend on(indirect dependency)

Two types of dependencies

local: objects are on the same node remote: objects are on separate nodes

ORACLE automatically checks dependencies

Oracle Basics
Recompilation of Dependent procedures
When an object changes, its dependent objects are marked for recompilation
Any change to definition of referenced object implies new version of reference object Dependent objects must be recompiled if referenced object changes Recompilation of dependent objects takes place automatically at runtime Reasons recompilation could fail
Changing parameter list in called procedure Changing definition of or removing referenced column from referenced table Dropping referenced table

Oracle Basics
Recompilation
Procedure/function can be recompiled be either
RDBMS automatically, when next accessed(only if marked for recompilation) Manually by the user, using ALTER PROCEDURE command

If recompilation fails, error information is put to error table

Oracle Basics
Manual Recompilation

ALTER PROCEDURE

Procedure COMPILE

schema
Example
ALTER PROCEDURE add_department COMPILE

Oracle Basics
Changing a procedure
To modify a procedure, replace it:
CREATE OR REPLACE PROCEDURE fire_employee AS . . . END;

OR REPLACE option:
Recreates the procedure even if it already exists Retains existing grants (need not reissue) Creates procedure even if there are syntax errors

Marks dependent objects for recompilation

Users executing old procedure finish that call: next invocation gets new procedure Facilitates development (one step)

CREATE without OR REPLACE on existing procedure fails

Oracle Basics
Dropping a procedure

DROP PROCEDURE

Procedure

schema
Example
DROP PROCEDURE fire_employee;

Marks dependent objects for recompilation

Oracle Basics
Privileges for procedures
Example
GRANT EXECUTE ON scott.hire_fire TO mkennedy

WITH GRANT OPTION;

Procedure executes under the authority of owner, not user executing procedure User of procedure need not have access to objects inside procedure Can only GRANT privileges on an entire package, not a procedure, function,or variable defined in the package

Oracle Basics
Privileges for procedures
PROCEDURE system privileges apply to procedures, functions, and packages
T od oth is C R E A T E N e e de ith e r
C R E A T E P R O C E D U R Eo r C R E T A EA N Y P R O C E D U R E s y s te mp riv ile g e O w nth ep ro c e d u re o rA L T E RA N Y P R O C E D U R E s y s te mp riv ile g e O w nth ep ro c e d u re o rD R O PA N Y P R O C E D U R E s y s te mp riv ile g e

A n d
O w n e rm u s th a v e a c c e s stoa ll o b je c ts re fe re n c e dinth e p ro c e d u re

A L T E R

D R O P

Oracle Basics
Privileges for procedures

T o d o t h i s

N e e d e i t h e r

A n d
P r o c e d u r e o w n e r m u s tb e e x p l i c i t l y g r a n t e d a c c e s s t o a l l d a t a b a s e o b j e c t s i n t h e p r o c e d u r e ( n o t t h r o u g h r o l e s )

E x e c u t e a O w n t h e p r o c e d u r e p r o c e d u r e o ra c c e s so rb e g r a n t e d a p a c k a g e c o n s t r u c t E X E C U T E P R I V I L E G E o r E X E C U T E A N Y P R O C E D U R E s y s t e m p r i v i l e g e

Oracle Basics
Benefits of Procedures
Security
Executes under security domain of procedures owner Provides controlled indirect access to database objects to nonprivileged users

Integrity
Defines allowed operations on data Ensures related actions are performed together

Performance
Reduces number of calls to thedatabase Decreases network traffic Pre-parses PL/SQL statements

Oracle Basics
Benefits of Procedures
Memory savings
Takes advantages of shared SQL Requires only one copy of the code for multiple users

Productivity
Avoids redundant code for common procedures in multiple applications Reduces coding errors: no redundant code written

Oracle Basics
Benefits of Procedures
Maintainability
Enables system wide changes with one update Makes testing easier: duplicate testing not needed Dependency tracked by ORACLE

High availability
Allows changing procedured on-line while users execute previous version

Oracle Basics
Package
A database object that groups related package constructs
Procedures functions cursor definitions variables and constants exception definitions

Package variables and cursors have persistent state Variables retain values and cursors retain contexts and positions for the duration of a user session State persists across a users calls in one session (not multiple sessions or users)

Oracle Basics
Parts of a package
Package specification
Declares (specifies) package constructs, including names and parameters publicly available procedures and functions

Package body
May declare additional, private package constructs that are not publicly available Defines all package constructs (public and private) May be replaced without affecting package specification (Breaks dependency chain) Each session has own version of state

Oracle Basics
Public and Private Package Constructs
Public package constructs
Declared in the package specification Available to anyone who can run the package

Private package constructs

Only declared in the package body Available to other package constructs within the package body Allows hiding procedures from programmers referencing the public constructs

Oracle Basics
Public and Private Package Constructs
Public declarations
PACKAGE hire_fire IS PROCEDURE hire_employee (. .); PROCEDURE fire_employee (. .); valid CHAR(1); END; PACKAGE BODY hire_fire IS PROCEDURE hire_employee (. .); IS BEGIN. . . END; PROCEDURE fire_employee ( . .) IS BEGIN . . . END;

Definition of public constructs

Definition of private function

FUNCTION check_num (. . ) RETURN. . IS BEGIN . . END;

END;

Oracle Basics
Public and Private Package Constructs

Package PK Variable C Package specification

Procedure A x :=pk.c;

Variable D Package body

Procedure B y :=d;
z :=c;

Oracle Basics
Uses of Packages
Group related constructs
Declare globally accessible variables Declare variables with persistent state Organize development activity

Define modules, collections of procedures known to on team Minimize name conflicts within a schema
Personnel.audit inventory.audit Simplify security

GRANT EXECUTE on entire package limit recompilation

Change body of procedure or function without changing specification Limit indirect dependencies

Oracle Basics
Creating a Package Specification

Example
/* Package specification declaring procedures and variables for hiring and firing employees */ CERATE PACKEAGE hire_fire AS /*Declare procedures */ PROCEDURE hire_employee (empno NUMBER, ename CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (empid NUMBER); /*Global variable declaration*/ valid CHAR(1); END hire_fire;

Oracle Basics Creating a package body

Example
/*package body containing procedures to hire and ire employee */ CREATE PACKAGE BODY hire_fire AS /*Procedure to hire a employee*/ PROCEDURE hire_employee (empno NUMBER, ename CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); IS BEGIN /*VALID is declared in the package definition*/ valid :=check_sum(empno); /*IF valid empno then add user*/ IF valid =T THEN INSERT INTO EMP VALUES (empno,ename,mgr,sal,comm,deptno); END IF; END;

(continued)

Oracle Basics
Creating a package body Example(continued)
/* Procedure to fire an employee number */ PROCEDURE fire_employee (empid NUMBER) IS BEGIN DELETE FROM emp WHERE empno =empid; END; /*function to check that the employee number>0.Local to the package */ FUNCTION check_sum (empno NUMBER) RETURN CHAR(1) IS answer CHAR(1); BEGIN answer :=T; IF empno <0 THEN answer := F; END IF; RETURN (answer); END; END hire_fire; /*End of package*/

Oracle Basics
Accessing package constructs
PROCEDURE employee_admin /* The action to perform and the employee ID have been entered previously*/ IF action =HIRETHEN scott.hire_fire.hire_employee ( employee,ename,mgr,sal,comm,deptno);

IF scott.hire_fire.valid =T THEN
/*sports_club is another package that handles membership to the company sports club*/ sports_club.add (employee) END IF; ELSIF action =FIRE THEN scott.hire_fire.fire_employee (employee); sports_club.remove (employee); END IF;

Oracle Basics
Dropping a Package

Procedure

schema

Oracle Basics
Benefit Of Package Performance Reduces disk I/o for subsequent calls - First call to package loads whole package into memory Persistence state Retain values of package constructs for an entire session

Security

Access to the package allows access to public constructs in the package only. No need to issue GRANT for every procedure in package.

Oracle Basics
Benefit Of Package

Productivity Stores related procedures and function together

Easier to manger changing the specification or definition of a construct. Reduces cascading dependencies

Dependencies are at package ,not procedure level

Can change package body with changing or affecting package specification

Oracle Basics

Oracle Basics
Triggers

What a trigger is. Creating triggers

Restrictions on triggers
Dropping and recompiling Privileges for creating Applications Benefits

Oracle Basics
What a Trigger is Database
UPDATE(trigger)

Application UPDATE t
SET .;

Table T INSERT INTO DELETE t..;

INSERT(trigger)

FROM t;

DELETE(trigger)

Oracle Basics
What a Triggers is
A user-defined PL/SQL block associated with a specific table, and implicitly fired (executed) when a triggering statement is issued against the table
Made up of parts

- Triggering event (INSERT/UPDATE/DELETE)

- Trigger type (BEFORE/AFTER, per statement or per row) - Trigger restriction (optional)

* WHEN clause
- Trigger action * PL/SQL BLOCK Not the same as a SQL * Forms trigger

Oracle Basics Ex. of a Triggers ----- Keeping sal. In range for a job
CREATE TRIGGER scott.salary_check

BEFORE INSERT OR UPDATE OR UPDATE OF sal,job ON scott.emp

FOR EACH ROW WHEN (NEW .job <> PRESIDENT) DECLARE

minsal
BEGIN

NUMBER;
/* get min and max salaries for the employees job from the SAL_GUIDE*/

maxsal NUMBER; SELECT minsal,maxsal INTO minsal,maxsal FROM sal_guide WHERE job = :NEW.job; /* If salary below min or above max,generate an error*/ IF (:NEW.sal < minsal.OR :NEW.sal > maxsal) THEN raise_application_error ( -20500,salary || :NEW.sal|| out of range for job|| :NEW.job|| for employee|| :NEW.ENAME); ENDIF; END; /* End of Trigger*/

Oracle Basics Triggers and stored procedures

similarities Made up of PL/SQL and SQL statements Use shared SQL areas Cannot be changed (must be dropped and recreated ) Dependencies tracked automatically by ORACLE

Oracle Basics Triggers and stored procedures

Differences Triggers invoked implicitly ;procedures invoked explicitly

Triggers and procedures crated with different SQL statements

No CREATE OR REPLACE TRIGGER statement

Triggers are associated with tables

COMMIT ,ROLLBACK,SAVEPOINT not allowed in triggers (nor in procedures called by triggers)

Oracle Basics Triggers vs. SQL * Forms triggers

Database trigger Fires while statement executes Fires independently of and in addition to SQL *From triggers Fired by SQL statement from any tool or application Can prevent completion of SQL statement Fire as each statement is executed SQL *From trigger Associated with particular from Only executes when from is run Can fire after each field is entered

pre/post INSERT/UPDATE /DELETE triggers execute when COMMIT key is pressed

Oracle Basics
Types of triggers
Type of a trigger determines
The time when the trigger fires BEFORE trigger: before the triggering action AFTER trigger: after the triggering action The item the trigger fires on Row trigger:once for each row affected by the triggering statement

Statement trigger:once for the triggering statement,regardless of the number rows affected

Each table have up to 12 triggers in all:

INSERT/UPDATE/DELETE)* BEFORE/AFTER)* STATE/ROW)

Oracle Basics
Types of triggers
How to use each type BEFORE statement trigger
To initialize global variables used in triggers To prevent update before it occurs

BEFORE row trigger

To compute derived fields in the new row To prevent updates before they occur

AFTER row trigger

For auditing (by value,by row)

Used by ORACLE snapshot mechanism)

AFTER statement trigger

For auditing

Oracle Basics
Triggers firing sequence
INSERT,UPDATE or DELETE is applied to table statement to execute

Execute BEFORE statement trigger

For each row affected by the SQL statement: Execute BEFORE row trigger

Change row and perform integrity constraint checking

Execute AFTER row trigger Execute AFTER statement trigger Complete deferred integrity constraint checking

Returns to application

Oracle Basics
Expressions in triggers
Referring to values in row triggers
To refer to the old and new values of a column in row triggers, use the:OLD and :NEW prefixes: IF :NEW.sal< :OLD.sal. . . Notes: Values available in row triggers only New and old both available for UPDATE The old value in an INSERT is NULL

T he new value in a DELETE is NULL

BEFORE row trigger can assign values to :NEW if it is not set by UPDATE SET clause or INSERT VALUES list Can replace: NEW and :old with other correlation names if desired

Colon dropped in when clauses

Oracle Basics
Expressions in triggers
conditional predicates
If a trigger can fire on more than one type of DML operation use pre defined PL/SQL boolean variables to determine which caused the trigger to fire:

IF INSERTING . . .
IF UPDATING . . . IF DELETING . . . To detect which column is being updated: IF UPDATING (columnname)

Oracle Basics
Expressions in triggers

CREATE TRIGGER total_salary AFTER DELETE OR INSERT OR UPDATE OF deptno,sal ON emp

FOR EACH ROW

BEGIN IF(DELETING) OR (UPDATING AND: OLD deptno <> :NEW deptno.) THEN UPDATE dept SET total_sal=total_sal -:OLD.sal WHERE deptno. = :OLD.deptno; END IF; IF (INSERT

Oracle Basics
Restrictions on triggers
Maximum number of 12 triggers for a table
Up to three(INSERT/UPDATE/DELETE)triggers of each type

Prohibited statements in triggers:

ROLLBACK

COMMIT
SAVEPOINT Note:Also applies to procedures called by triggers(including remote procedures

Cascading trigger limit

The action of a trigger may cause another trigger to fire and so on (called cascading triggers)

Limit the depth of cascading with an INIT.ORA parameter

Oracle Basics
Restrictions on triggers
Mutating tables

Original EMP
E N A M E
S M I T H J O N E S

S A L
1 0 0 0 1 0 0 0

UPDATE emp SET sal = sal *1.1; UPDATE(trigger)

SELECT sal FROM emp WHERE

mutating EMP
E N A M E
S M I T H J O N E S

S A L
1 1 0 0 1 0 0 0

A table that is being modified by an UPDATE,DELETE, or INSERT in a single user statement A trigger cannot SELECT from or change a mutating table(except current row, using :NEW and :OLD)

Oracle Basics Restrictions on triggers

Changes to updated/inserted values

E M P N O
0 4 5 0 0 4 0 7

D E P T N O
2 0 1 0

UPDATE emp SET deptno=10 WHERE empno =0405;

mutating EMP
E M P N O
0 4 5 0 0 4 0 7

D E P T N O
1 0 1 0

UPDATE (trigger)

GRATE TRIGGER bad BEGIN NEW.deptno:=30 END;

A trigger cannot change values explicitly referenced in the UPDATE statement SET clause or INSERT statement

Oracle Basics
Enabling and disabling triggers
Possible states for a trigger
Enabled Executes its triggered action if both:
an appropriate statement is issued. trigger WHEN clause evaluates to TRUE(if present).

Disabled Does not execute its triggered action

Triggers are automatically enabled on creation

Oracle Basics
Enabling and disabling triggers
Reasons for disabling the trigger
Have to load a large amount of data, and want to proceed quickly without firing triggers
Example: SQL *loader using direct path automatically disables triggers

Want to INSERT, UPDATE or DELETE on a table whose trigger references a database object that is not available

Oracle Basics
Enabling and disabling triggers
With ALTER TRIGGER

ALTER TRIGGER
schema

trigger

ENABLE DISABLE

Examples
ALTER TRIGGER reorder DISABLE; ALTER TRIGGER reorder ENABLE;

Oracle Basics
Enabling and disabling triggers
With ALTER TABLE

ALTER TABLE

table

schema
ENABLE DISABLE

trigger schema

trigger

Examples
ALTER TABLE INVENTORY DISABLE TRIGGER REORDER;

ALTER TABLE INVENTORY

ENABLE TRIGGER REORDER;

Oracle Basics
Dropping Triggers

DROP TRIGGER schema

trigger

Example
DROP TRIGGER reorder;

Triggers cannot be altered; they must be dropped and recreated

Oracle Basics
Recompiling a trigger
COMPILE

ALTER TRIGGER schema

trigger

Manually recompile to resolve dependencies (same as procedures) Example

ALTER TRIGGER reorder COMPILE;

Oracle Basics
Applications of triggers
Maintaining derived fields Implementing complex security rules Enforcing complex business rules Performing value-based auditing Making implied changes

Maintaining table replication

Oracle Basics
Examples of using triggers
Deriving column values Derive column values for a row based upon a value provided by an INSERT or UPDATE statement. Must use BEFORE ROW trigger
Value must be derived first so the INSERT or UPDATE statement can use it. Trigger must fire for each row affected.

Cannot assign to new values set by triggering INSERT or UPDATE.

Oracle Basics
Examples of using triggers
Deriving column values
E m p n o E n a m eU p p e r n a m eS o u n d e x n a m e J o b 7 3 2 9 S m i t h 7 4 9 9 A l l e n 7 5 6 6 J o n e s S m i t h A l l e n J o n e s S 5 3 0 A 4 5 0 J 5 2 0 C l e r k S a l e s m a n M a n a g e r

CREATE TRIGGER upper_soundex BEFORE INSERT OR UPDATE OF ename, uppername, soundexname ON emp ; FOR EACH ROW BEGIN :NEW.uppername := UPPER (:NEW.ename); :NEW.soundexname := SOUNDEX(:NEW.ename); END;

Oracle Basics
Examples of using triggers
Complex security authorization Allows more complex security checking than provided by ORACLE

Examples
- Check for time of day,day of week
- Check for terminal being used

- Permit updates of certain amounts by specific users

Oracle Basics
Examples of using triggers
Complex security authorization
CREATE TRIGGER emp_permit_changes BEFORE INSERT OR DELETE OR UPDATE ON emp DECLARE dummy INTEGER; BEGIN IF(TO_CHAR (sysdate,DY) IN(SAT,SUN)) THEN raise_application_error(-20504,cannot change emp table during weekend); END IF; SELECT COUNT(* ) INTO dummy FROM company_holidays WHERE day = TRUNC(sysdate); IF dummy>0 THEN raise_application_error(-20505,cannot change emp table during holiday); END IF; IF (TO_NUMBER(sysdate,HH24) NOT BETWEEN 8 AND 18) THEN raise_application_error (-20506,cannot change emp table in of_hours); END IF; END;

Oracle Basics
Examples of using triggers
Enforcing complex business rules
Complex check constraints that are not definable using declarative constraints Can be used to maintain data integrity across a distributed database (declarative integrity cannot span distributed database) Note: simple constraints are best handled by declarative constraints features.

Oracle Basics
Examples of using triggers Enforcing complex business rules CREATE TRIGGER increase_chk
BEFORE UPDATING OF sal ON emp

FOR EACH ROW

WHEN (NEW.sal <OLD.sal OR NEW.sal >1.1 * OLD. Sal) BEGIN

raise_application_error(-20502,
may not decreased salary. Increase must be <10%) END;

Oracle Basics
Examples of using triggers Enforcing complex business rules
CREATE TRIGGER scott.salary_check BEFORE INSERT OR UPDATE OR UPDATE OF sal, ON scott.emp FOR EACH ROW WHEN (NEW.job <>PRESIDENT) DECLARE minsal NUMBER; maxsal NUMBER; BEGIN SELECT minsal,maxsal INTO minsal,maxsal FROM sal_guide WHERE job= :NEW.job ; IF (:NEW.sal <minsal OR :NEW.sal > maxsal) THEN raise_application_error(-20503,salary || :NEW.job || for employee || :NEW.ename); END IF; END;

Oracle Basics
Examples of using triggers
Value based auditing Auditing that cannot be accomplished with standard RDBMS auditing features Allows
Exclusively DML auditing Per row auditing Storage of update details

Notes:many basic conditional auditing functions accomplished best by standard RDBMS auditing Triggers cannot audit:
DDL SELECTs

Logons

Oracle Basics Examples of using triggers

Value based auditing
CREATE TRIGGER audit_employee AFTER INSERT OR DELETE OR UPDATE ON emp FOR EACH ROW BEGIN IF auditpage. Reason IS NULL THEN raise_application_error(-20501,MUST specify reason for update before performing update;use auditpackage.set_reason()); END IF; INSERT IF; INSERT INTO audit_employee VALUES ( :OLD.ssn, :OLD.name; :OLD.classification, :OLD.sal, :NEW.ssn; :NEW.name, :NEW.classification, :NEW.sal, auditpackage.reason,user,sysdate); END;

Oracle Basics Examples of using triggers

Implied data changes

PENNDING_ORDERS
P A R T _ N O O R D _ Q T Y O R D _ D A T E
0 0 2 3 4 0 0 3 4 2 1 5 2 5 1 5 J A N 9 2 1 5 J A N 9 2

INVENTORY
P A R T _ N O O N _ H A N D R E O R D _ P R E O R D _ Q T Y T
0 0 2 3 4 0 0 3 4 2 3 4 5 2 3 0 5 0 1 5 2 5

Transparently perform an action when a triggering is executed

Example: Inventory check generates a new order

Oracle Basics
Examples of using triggers
Implied data changes
CREATE TRIGGER inv_check AFTER UPDATE of on_hand ON inventory FOR EACH ROW WHEN (NEW.ON_HAND NEW.reord_pt) DECLARE x NUMBER; BEGIN SELECT COUNT(*) INTO x FROM pending_orders WHERE pending_orders.part_no= :NEW.part_no; IF x=0 THEN INSERT INTO pending_orders VALUES (:NEW.part_no, :NEW.reord_qty, SYSDATE); END IF; END;

Oracle Basics
Examples of using triggers synchronous table replication
Link identical tables(replicas) on different nodes so when replica is altered, changes are synchronously reflected in other replicas Replicas must contain a flag field that is set when they are updated to stop trigger cascading

EMP_REP1

EMP_REP2

UPDATE...

UPDATE...

Oracle Basics
Benefits of triggers
Security
Allows sophisticated security checking Enables customized auditing mechanism to be built

Integrity
Ensures related operations on data are performed together Can be used to enforce complex business rules

Oracle Basics
Benefits of triggers
Performance
Reduces number of calls to the RDBMS Decreases network traffic

Memory savings
Takes advantage of shared SQL Requires only one copy of the code for multiple users

Productivity
Requires only a single copy of the code be written and maintained(not multiple copies in client applications)

Oracle Basics
PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:

support for SQL support for object-oriented programming. Better performance portability higher productivity integration with ORACLE

Oracle Basics

Thanks