4/12/23 4/12/23 1

PL/SQL

PL / SQL

4/12/23 4/12/23 2

PL/SQL

Overview of PL/SQL

Declaring Variables

Writing Executable Statements

Control Structures

Composite Data Types

Explicit Cursors

Handling Exceptions

Creating Procedures

Creating Functions

Creating Packages

Oracle Supplied Packages

Large OBjects

Database Triggers

Managing Dependencies

4/12/23 4/12/23 3

PL/SQL

Overview of PL/SQL

4/12/23 4/12/23 4

PL/SQL

PL/SQL is the procedural extension to SQL with design feature of programming languages.

Data Manipulation and Query statements of SQL are included within procedural units of code.

Place reusable PL/SQL code in libraries to be shared between application to all users.

PL / SQL

4/12/23 4/12/23 5

PL/SQL

Year Oracle PL/SQL Characteristics

1991 6 1.0 Programming Language

1992 7 2.0 Stored procedure, functions, packages, PL/SQL tables, DBMS_OUTPUT, DBMS_PIPE

1994 7.1 2.1 Programmer defined subtypes, DBMS_SQL, Stored functions inside SQL statement.

1995 7.3 2.3 UTL_FILE, Cursor variables, remote dependency

1997 8 8.0 LOB, Object-Oriented design, Advanced Queuing, Collections (VARRAY , nested table)

1998 8i 8.1 Native Dynamic SQL (EXECUTE IMMEDIATE), Support for Java, Autonomous Transaction

2000 9i R1 9.0 Native Compilation as C code, Inheritance in object types, multi-level collections, CASE expression

2002 9i R2 9.2 Oracle Text CTXXPATH, XML, index-by tables indexed by VARCHAR2, record-based DML,

UTL_FILE (allows to copy, remove, and rename files).

2004 10g 10.0 Regular Expression, DBMS_WARNING package, BINARY_FLOAT, BINARY_DOUBLE, DBMS_LOB (128 TB), String

literal.

PL/SQL Versions

4/12/23 4/12/23 6

PL/SQL

DECLARE (Optional)Variables, cursors, user-defined exception

BEGIN (Mandatory)SQL & PL/SQL statements

EXCEPTION (Optional) Actions to perform when error occur

END; (Mandatory)/

* Block - The basic program unit in PL/SQL * Blocks contain sets of instructions to perform. (must contain one command)* You can declare variables.* You can program with Control Structures, IF-THEN-ELSE, LOOP...* Error handling functionality.* Nest sub-block inside large block to build powerful program.* Single line Comment --* Multiple line Comments /* */* A slash ( / ) runs the PL/SQL block.

4/12/23 4/12/23 7

PL/SQL

FUNCTION Name

RETURN Datatype

IS

BEGIN -- statements

RETURN

[EXCEPTION]

END;

PROCEDURE Name

IS

BEGIN -- statements

[EXCEPTION]

END;

[DECLARE]

BEGIN -- statements

[EXCEPTION]

END;

Anonymous Procedure Function

4/12/23 4/12/23 8

PL/SQL

PL/SQL block

Procedural statement executor

PL / SQL enginePL/SQL

block

SQL statement executor

Oracle Server

Oracle tools, like Developer, Oracle Forms have their own PL/SQL engine, which is independent of the engine present in the Oracle Server.

The engine filters out SQL statement and sends them individually to the SQL statement executor in the Oracle Server.

PL/SQL engine, processes remaining Procedural statements.

4/12/23 4/12/23 9

PL/SQL

Summary

PL/SQL

PL/SQL Block Structure

Block Types

PL/SQL Environment

4/12/23 4/12/23 10

PL/SQL

Declaring Variables

4/12/23 4/12/23 11

PL/SQL

Use of Variables

Temporary storage of data

Manipulation of stored values

Reusability

Handling Variables

Declare and initialize variable in the declaration section.

Assign new values to variables in the executable section.

View results through output variables

4/12/23 4/12/23 12

PL/SQL

Types of Variables

PL / SQL

Scalar (hold a single value) - number, date, binary_integer, boolean (true, false, null), timestamp…

Composite (group of value) - records, cursors…

Reference (other program) - pointers

LOB (Large objects) - graphics, movies

Non - PL / SQL Bind and host variables - global values

4/12/23 4/12/23 13

PL/SQL

identifier [CONSTANT] datatype [NOT NULL][ := | DEFAULT expr];

* Follow naming conventions.* Declare one identifier per line.* Initialize identifiers by using the assignment operator ( := )

DECLARE v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(12) := ‘Atlanta’; c_comm CONSTANT NUMBER := 1400; v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_orderdate DATE := SYSDATE + 7; v_valid BOOLEAN NOT NULL := TRUE;

variables

4/12/23 4/12/23 14

PL/SQL

DECLARE v_name employees.last_name%TYPE ; v_balance NUMBER(7,2); v_min_balance v_balance%TYPE := 10;

identifier table.column_name%TYPE ;

* Declare variable according to :-- A database column definition-- Another previously declared variable

v_sal1 := 50000;v_sal2 := 60000;

* Boolean (TRUE - FALSE - NULL)

v_sal1 < v_sal2 this evaluates to TRUE

DECLARE v_flag BOOLEAN := FALSE;BEGIN v_flag := TRUE;END;

4/12/23 4/12/23 15

PL/SQL

• Large OBjects 4GB -- to store unstructured data (graphics, video, soundwave)

-- CLOB Character Large OBject lengthy text

-- BLOB Binary Large OBject Graphics, Photos

-- BFILE Binary FILE Movie

-- NCLOB National Language Other Language Character Large OBject

4/12/23 4/12/23 16

PL/SQL

Summary

Variables / Identifiers - declared at declarative section

Declaring Variables

Types of Variables – SQL PL/SQL

%TYPE

LOB

4/12/23 4/12/23 17

PL/SQL

Writing Executable Statements

4/12/23 4/12/23 18

PL/SQL

DECLAREx NUMBER;y NUMBER;

BEGIN.......DECLARE

z NUMBER;BEGIN

z := x ;END;.......y := z ;

END;

Scope of z

Scope of x , y

A block can look up to the enclosing block. A block cannot look down to the enclosed block.

4/12/23 4/12/23 19

PL/SQL

The qualifier can be the label of the enclosing block.Qualify an identifier by using the block label prefix.

<<abc>> DECLARE birthdate DATE; BEGIN

DECLARE birthdate DATE; .. abc.birthdate := TO_DATE(‘03-AUG-2004’,’DD-MON-YYYY’); END; .. END;

4/12/23 4/12/23 20

PL/SQL

VARIABLE g_monthly_sal NUMBERDEFINE p_annual_sal = 5000SET VERIFY OFF

DECLARE v_sal NUMBER(9,2) := &p_annual_sal;BEGIN :g_monthly_sal := v_sal / 12;END;/

PRINT g_monthly_sal

To reference a Bind Variable, prefix with colon ( : )

4/12/23 4/12/23 21

PL/SQL

SELECT select_listINTO {variable_name[, variable_name]...

| record_name}FROM table[WHERE condition];

* Retrieve data from the database with a SELECT statement.* Queries must return only one row.

DECLARE v_deptno NUMBER(4); v_locid NUMBER(4);BEGIN SELECT department_id, location_id INTO v_deptno, v_locid FROM departments WHERE department_name = ‘Sales’;END;

* Retrieve the department no and location no, for department Sales.

4/12/23 4/12/23 22

PL/SQL

SET SERVEROUTPUT ONDEFINE p_annual_sal = 6000

DECLARE v_sal NUMBER(9,2) := p_annual_sal;BEGIN v_sal := v_sal/12; DBMS_OUTPUT.PUT_LINE (‘The monthly salary is’ ||

TO_CHAR(v_sal));END;/

An Oracle-supplied packaged procedure

To display data to screen

Enable with SET SERVEROUTPUT ON

4/12/23 4/12/23 23

PL/SQL

* Display the sum of the salaries for all employees in the specified department.

SET SERVEROUTPUT ONDECLARE v_sum_sal NUMBER(10,2);BEGIN SELECT sum(salary) INTO v_sum_sal FROM employees WHERE department_id = 30; DBMS_OUTPUT.PUT_LINE(‘The sum salary is ‘

|| TO_CHAR(v_sum_sal));END;/

.. ..BEGIN SELECT salary INTO v_sum_sal FROM employees WHERE department_id = 30;

Analyze the result

Why ?

4/12/23 4/12/23 24

PL/SQL

* The following DELETE statement removes all employees, where last name is not just ‘King’, because Oracle assumes that both last_name(s) in the WHERE clause refer to the database column.

DECLARE last_name VARCHAR2(25) := ‘King’;

BEGIN DELETE FROM emp_copy WHERE last_name = last_name ;

END;

4/12/23 4/12/23 25

PL/SQL

SET SERVEROUTPUT ONDECLARE v_name VARCHAR2(10) := ‘Reynardo’; v_address VARCHAR2(12) := ‘#9, MG Road’; v_city VARCHAR2(10) := ‘Bangalore’; v_zip VARCHAR2(7) := ‘560 006’;

BEGIN

DBMS_OUTPUT.PUT_LINE(v_name || CHR(10) || v_address || CHR(10) || v_city || CHR(10) || v_zip);

END;

v_ename := LOWER(v_ename);v_date := TO_DATE(‘January 12, 2005’, ‘Month DD, YYYY’);

4/12/23 4/12/23 26

PL/SQL

BEGIN INSERT INTO employees

(employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES ( 27 , ‘Ruth’, ‘Cores’, ‘RCORES’, sysdate, ‘AD_ASST’ 9000); END;

DECLARE v_sal_inc employees.salary%TYPE := 800;BEGIN UPDATE employees SET salary = salary + v_sal_inc WHERE job_id = ‘ST_CLERK’; COMMIT; END;

DECLARE v_deptno employees.department_id%TYPE := 80;BEGIN DELETE FROM employees WHERE department_id = v_deptno ;END;

4/12/23 4/12/23 27

PL/SQL

* Cursor is a private SQL work area for each user.* Two Types : Implicit Cursor - Automatically created if any SQL statement is executed. Explicit Cursor - Can Create by programmer.

SQL%ROWCOUNT Number of rows affected by the recent SQL statement

SQL%FOUND Boolean attribute that evaluates TRUE if the recent SQL statement affects one or more rows.

SQL%NOTFOUND Boolean attribute that evaluates TRUE if the recent SQL statement does not affects any rows.

SQL%ISOPEN Always evaluates FALSE because, implicit cursors are closed immediately after they are executed.

Attributes are used to test the outcome of the result.

4/12/23 4/12/23 28

PL/SQL

VARIABLE rows_deleted VARCHAR2(30)

DECLARE v_eid employees.employee_id%TYPE := 176;BEGIN DELETE FROM employees WHERE employee_id = v_eid;

:rows_deleted := (SQL%ROWCOUNT || ‘ rows deleted.’);

END;

/

PRINT rows_deleted

4/12/23 4/12/23 29

PL/SQL

DECLAREv_weight NUMBER(3) := 600;v_message VARCHAR2(99) := ‘ Product 10012’;

BEGIN

DECLAREv_weight NUMBER(3) := 1;v_message VARCHAR2(90) := ‘Product 11001’;v_new_locn VARCHAR2(50) := ‘Europe’;

BEGINv_weight := v_weight + 1;v_new_locn := ‘Western ‘ || v_new_locn;

END;v_weight := v_weight + 1;v_message := v_message || ‘ is in stock’; v_new_locn := ‘Western ‘ || v_new_locn;

END;

The value of V_WEIGHT at position 1 -> 2The value of V_NEW_LOCN at position 1 -> Western EuropeThe value of V_WEIGHT at position 2 -> 601The value of V_MESSAGE at position 2 -> Product 10012 is in stock The value of V_NEW_LOCN at position 2 -> Illegal because, not visible outside the sub-block

1

2

4/12/23 4/12/23 30

PL/SQL

Class Exercise 1<<abc>> DECLARE

v_sal NUMBER(7,2) := 60000;v_comm NUMBER(7,2) := v_sal * 0.20;v_message VARCHAR2(90) := ‘ eligible for commission ’;

BEGIN

DECLAREv_sal NUMBER(7,2) := 50000;v_comm NUMBER(7,2) := 0;v_total_comp NUMBER(7,2) := v_sal * v_comm;

BEGINv_message := ‘CLERK not ’|| v_message;abc.v_comm := v_sal * 0.30;

END; v_message := ‘SALESMAN’ || v_message;

END;

The value of V_MESSAGE at position 1The value of V_COMM at position 1The value of ABC.V_COMM at position 1

The value of V_TOTAL_COMP at position 2The value of V_COMM at position 2The value of V_MESSAGE at position 2

1

2

4/12/23 4/12/23 31

PL/SQL

Summary

Nested Blocks and Scope Variables Qualify and Identifier SELECT statement in PL/SQL Using Bind Variable DBMS_OUTPUT.PUT_LINE Retrieve Data Naming Convention Functions in PL/SQL Insert / Update / Delete Cursors

4/12/23 4/12/23 32

PL/SQL

Control Structures

4/12/23 4/12/23 33

PL/SQL

IF condition THENstatements;

[ELSIF condition THENstatements;]

[ELSEstatements;]

END IF;

* You can change the logical execution of statements using IF statements and LOOP control structures

IF UPPER(v_last_name) = ‘VARGAS’ THENv_job := ‘SA_REP’v_deptno := 80;

END IF;

Eg., If the employee name is Vargas, set job id to SA_REP and set department number to 80

4/12/23 4/12/23 34

PL/SQL

IF v_ename = ‘Vargas’ AND salary > 6500 THEN v_deptno := 60; END IF;..

Eg., If the last name is Vargas and the salary is more than 6500, Set the department number to 60.

DECLAREv_hire_date DATE := ’12-DEC-1990’;v_five_years BOOLEAN;

BEGIN IF MONTHS_BETWEEN(sysdate,v_hire_date)/12 > 5 THEN

v_five_years := TRUE; ELSE

v_five_years := FALSE; END IF;END;/

Set a Boolean flag to TRUE if the hire date is greater than Five years; other wise, set the Boolean flag to FALSE.

4/12/23 4/12/23 35

PL/SQL

...

IF v_deptno = 10 THEN v_bonus := 5000;

ELSIF v_deptno = 80 THEN v_bonus := 7500;

ELSE v_bonus := 2000;

... END IF;

Determine an employee’s bonus based upon the employee’s department.

IF condition1 THENstatements1;

ELSIF condition2 THENstatements2;

ELSEstatements3;

END IF;

4/12/23 4/12/23 36

PL/SQL

SET SERVEROUTPUT ON

DECLARE v_grade CHAR(1) := ‘A’; v_appraisal VARCHAR2(20);

BEGIN v_appraisal := CASE v_grade WHEN ‘A’ THEN ‘Excellent’

WHEN ‘B’ THEN ‘Very Good’ WHEN ‘C’ THEN ‘Good’ ELSE ‘No such grade’

END; DBMS_OUTPUT.PUT_LINE (‘Grade: ’ || v_grade ||

‘ Appraisal’ || v_appraisal); END;

/

CASE expression selects a result and returns it.

4/12/23 4/12/23 37

PL/SQL

4/12/23 4/12/23 38

PL/SQL

• LOOPs repeat a statement or sequence of statements multiple times.

• Types of LOOP

Basic LOOP - Perform repetitive actions without overall conditions.

FOR loop - Perform iterative control of actions based on count.

WHILE loop - perform iterative control based on a condition.

4/12/23 4/12/23 39

PL/SQL

• A basic LOOP allows execution of its statements atleast once, even if the condition already met upon entering the loop.

LOOP statement1; EXIT [WHEN condition];END LOOP;

DECLARE v_country_id locations.country_id%TYPE := ‘CA’; v_location_id locations.location_id%TYPE; v_counter NUMBER(2) := 1; v_city locations.city%TYPE := ‘Montreal’;BEGIN SELECT MAX(location_id) INTO v_location_id FROM locations WHERE country_id = v_country_id; LOOP INSERT INTO locations(location_id, city, country_id) VALUES((v_location_id + v_counter),v_city,v_country_id); v_counter := v_counter + 1; EXIT WHEN v_counter > 3; END LOOP;END;

4/12/23 4/12/23 40

PL/SQL

• A WHILE Loop repeats sequence of statements until the controlling condition is no longer TRUE.

WHILE condition LOOP statement1..;END LOOP;

DECLARE v_country_id locations.country_id%TYPE := ‘CA’; v_location_id locations.location_id%TYPE; v_counter NUMBER(2) := 1; v_city locations.city%TYPE := ‘Washington’;BEGIN SELECT MAX(location_id) INTO v_location_id FROM locations WHERE country_id = v_country_id; WHILE v_counter <= 3 LOOP INSERT INTO locations(location_id, country_id) VALUES((v_location_id + v_counter),v_city,v_country_id); v_counter := v_counter + 1; END LOOP;END;

4/12/23 4/12/23 41

PL/SQL

• A FOR Loop is used to test for the number of iterations.

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1..; statement2..;END LOOP;

DECLARE v_country_id locations.country_id%TYPE := ‘CA’; v_location_id locations.location_id%TYPE; v_city locations.city%TYPE := ‘Paris’;

BEGIN SELECT MAX(location_id) INTO v_location_id FROM locations WHERE country_id = v_country_id; FOR i IN 1..3 LOOP INSERT INTO locations(location_id, country_id) VALUES((v_location_id + i), v_city, v_country_id); v_counter := v_counter + 1; END LOOP;END;

Implicitly declared

j IN -5..5k IN REVERSE first..laststep IN 0..TRUNC(high/low) * 2

4/12/23 4/12/23 42

PL/SQL

Specify a different increment (5 instead of 1 for example). Inside the FOR loop, simply multiply each reference to the loop counter by the new increment. In the following example, you assign today's date to elements 5, 10, and 15 of an index-by table:DECLARE TYPE DateList IS TABLE OF DATE INDEX BY BINARY_INTEGER; dates DateList; k CONSTANT INTEGER := 5; -- set new incrementBEGIN FOR j IN 1..3 LOOP dates(j*k) := SYSDATE; -- multiply loop counter by incrementEND LOOP;...END; FOR ctr IN 1..10 LOOP

IF NOT finished THENINSERT INTO ... VALUES (ctr, ...); -- OKfactor := ctr * 2; -- OK

ELSEctr := 10; -- not allowed

END IF;END LOOP;

4/12/23 4/12/23 43

PL/SQL

• Nest loops to multiple levels.

• Use labels to distinguish between blocks and loops.

• Exit the outer loop with the EXIT statement that references the label... .. ..BEGIN <<Outer_loop>> LOOP

v_counter := v_counter + 1; EXIT WHEN v_counter > 10; -- leaves both loops

<<Inner_loop>> LOOP ... EXIT Outer_loop WHEN total_done = ‘YES’;

-- leaves both loops EXIT WHEN inner_done = ‘YES’;

-- leaves inner loop only .. .. END LOOP Inner_loop;.. ..

END LOOP Outer_loop;END;

4/12/23 4/12/23 44

PL/SQL

BEGIN FOR i IN 1..10 LOOP

IF i = 6 or i = 8 THENnull;

ELSEINSERT INTO messages(results)VALUES (i);

END IF;

COMMIT;

END LOOP;END;/

• Create the MESSAGES table with column results varchar2(60)

• Insert the numbers 1 to 10 excluding 6 and 8

• Commit before the end of the block

4/12/23 4/12/23 45

PL/SQL

SummaryIF condition1 THEN

statements1;ELSIF condition2 THEN

statements2;ELSE

statements3;END IF;

LOOP statement1; EXIT [WHEN condition];END LOOP;

CASE

BOOLEAN

WHILE condition LOOP statement1..;END LOOP;

FOR counter IN [REVERSE] lower_bound..upper_bound LOOP statement1..; statement2..;END LOOP;

4/12/23 4/12/23 46

PL/SQL

Composite Data Types

4/12/23 4/12/23 47

PL/SQL

* Declare a variable according to the collection of columns in a database table or view.* Prefix %ROWTYPE with the database table.* Fields in the record take their names and data types from the columns of the table or view

DEFINE employee_no = 124DECLARE

emp_rec employees%ROWTYPE;BEGIN

SELECT * INTO emp_rec FROM employeesWHERE employee_id = &employee_no;

INSERT INTO retired_emps(empno, ename, job, mgr, hiredate, leavedate, sal, comm, deptno)VALUES (emp_rec.employee_id, emp_rec.last_name,

emp_rec.job_id, emp_rec.manager_id, emp_rec.hire_date, SYSDATE, emp_rec.salary, emp_rec.commission_pct, emp_rec.department_id);

COMMIT;END;/

4/12/23 4/12/23 48

PL/SQL

Write a PL/SQL block to print information about a given country

DEFINE p_countryid = CA

DECLAREcountry_record countries%ROWTYPE;

BEGIN SELECT * INTO country_record FROM countries WHERE country_id = UPPER(‘&p_countryid’); DBMS_OUTPUT.PUT_LINE ( ‘ Country Id: ‘|| country_record.country_id || ‘ Country Name: ‘|| country_record.country_name|| ‘ Region: ‘|| country_record.region_id);END;/

4/12/23 4/12/23 49

PL/SQL

Explicit Cursors

4/12/23 4/12/23 50

PL/SQL

Every SQL statement executed by the Oracle Server has an individual cursor associated with it. Implicit Cursor : Declared for all DML and PL/SQL SELECT statements. Explicit Cursor : Declared and named by the programmer.

Use CURSOR to individually process each row returned by a multiple-row SELECT Statement.

The set of rows returned by a multiple-row query is called active set.

Create a named SQL area

Identify the active set

Lead the current row into variables

Test for existing rows

Return to FETCH if rows are found

Release the active set.

DECLARE OPEN FETCH CLOSEEMPTY?

No

Yes

4/12/23 4/12/23 51

PL/SQL

* Retrieve the first 10 employees one by one.

SET SERVEROUTPUT ONDECLARE v_empno employees.employee_id%TYPE; v_ename employees.last_name%TYPE;

CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees;

BEGIN OPEN emp_cursor;

FOR i IN 1..10 LOOP

FETCH emp_cursor INTO v_empno, v_ename; DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno)||’ ‘||v_ename);

END LOOP;

CLOSE emp_cursor;END;

4/12/23 4/12/23 52

PL/SQL

cname%ROWCOUNT Number Evaluates to the total number of rows returned so forcname%FOUND Boolean Evaluates to TRUE if the most recent fetch returns a row.

cname%NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row.

cname%ISOPEN Boolean Evaluates TRUE if the cursor is open

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cusor;END IF;LOOP FETCH emp_cursor.. ..

LOOP FETCH c1 INTO my_ename, my_sal; EXIT WHEN c1%NOTFOUND;.. ..

END LOOP;

LOOP FETCH c1 INTO my_deptno; IF c1%ROWCOUNT > 10 THEN

.. .. END IF; .. ..END LOOP;

4/12/23 4/12/23 53

PL/SQL

SET SERVEROUTPUT ONDECLARE v_empno employees.employee_id%TYPE; v_ename employees.last_name%TYPE;

CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees;

BEGIN OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_empno, v_ename; EXIT WHEN emp_cursor%ROWCOUNT > 10 OR

emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno)||’ ‘||v_ename);

END LOOP;

CLOSE emp_cursor;END;

* Retrieve the first 10 employees one by one by using attributes.

4/12/23 4/12/23 54

PL/SQL

DECLARE CURSOR emp_cursor IS

SELECT employee_id, last_name FROM employees; emp_record emp_cursor%ROWTYPE;

BEGIN OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; INSERT INTO temp_list (emp_id, ename) VALUES (emp_record.employee_id,

emp_record.last_name);

END LOOP;COMMIT;

CLOSE emp_cursor;END;

* Process the rows of the active set by fetching values into PL/SQL RECORD.

Populate to the table temp_list.

4/12/23 4/12/23 55

PL/SQL

SET SERVEROUTPUT ONDECLARE CURSOR emp_cursor IS

SELECT last_name, department_id FROM employees;BEGIN

FOR emp_record IN emp_cursor LOOP -- implicit open fetch occur

IF emp_record.department_id = 80 THEN DBMS_OUTPUT.PUT_LINE(‘Employee ‘ ||

emp_record.last_name || ‘ works in the Sales Dept.‘);

END IF;

END LOOP; -- implicit closeEND;

* Implicit Open, Fetch, and Close occurs The record is implicitly declared.

Retrieve employee one by one who is working in department 80

4/12/23 4/12/23 56

PL/SQL

SET SERVEROUTPUT ONBEGIN

FOR emp_record IN (SELECT last_name, department_id FROM employees) LOOP

-- implicit open fetch occur

IF emp_record.department_id = 80 THEN DBMS_OUTPUT.PUT_LINE(‘Employee ‘ ||

emp_record.last_name || ‘ works in the Sales Dept.‘);

END IF;

END LOOP; -- implicit closeEND;

* No need to declare the cursor, if FOR loop is usedSame result as previous Slide : Retrieve employee one by one who is working in department 80

4/12/23 4/12/23 57

PL/SQL

SET SERVEROUTPUT ONDECLARE CURSOR emp_cursor (p_dno NUMBER) IS

SELECT employee_id, last_name FROM employeesWHERE department_id = p_dno;

BEGINFOR emp_record IN emp_cursor(50) LOOP DBMS_OUTPUT.PUT_LINE(‘Employee ‘ || emp_record.employee_id||’ ‘|| emp_record.last_name || ‘ works in 50‘); END LOOP;

FOR emp_record IN emp_cursor(60) LOOP DBMS_OUTPUT.PUT_LINE(‘Employee ‘ || emp_record.employee_id||’ ‘|| emp_record.last_name || ‘ works in 60‘); END LOOP;

END;

/

* Pass parameter values to a cursor using WHERE, and Open an explicit cursor

several times with a different times with the different active set each time.

4/12/23 4/12/23 58

PL/SQL

DECLARE CURSOR sal_cursor IS SELECT e.department_id, employee_id, last_name, salary FROM employees e, departments d WHERE d.department_id = e.department_id AND d.department_id=60 FOR UPDATE OF salary NOWAIT;BEGIN FOR emp_record IN sal_cursor LOOP

IF emp_record.salary < 5000 THEN UPDATE employees SET salary = emp_record.salary * 1.10 WHERE CURRENT OF sal_cursor ;END IF;

END LOOP; END;

FOR UPDATE Clause -- Use explicit locking to deny access for the duration of a transaction. -- Lock the rows before the update or delete. -- NOWAIT keyword tells not to wait if requested rows have been locked by another user.WHERE CURRENT OF cursor; To reference the current row from an explicit cursor.

4/12/23 4/12/23 59

PL/SQL

Summary Use CURSOR to individually process each row returned by a multiple-row SELECT Statement.

Implicit Cursor : Declared for all DML and PL/SQL SELECT statements. Explicit Cursor : Declared and named by the programmer.

Using Attributes

Fetching values into PL/SQL RECORD

FOR loop - Implicit Open, Fetch, and Close occurs The record is implicitly declared.

Cursor with Parameters.

FOR UPDATE OF . . NOWAIT WHERE CURRENT OF

4/12/23 4/12/23 60

PL/SQL

Handling Exceptions

4/12/23 4/12/23 61

PL/SQL

An exception is an identifier in the PL/SQL that is raised during execution.

How it is raised ?- An Oracle error occurs.- You raise it explicitly.

How do you handle it ?- Trap it with handler.- Propagate it to the calling environment.

[DECLARE]

BEGIN Exception/Error is Raised

EXCEPTION Error is Trapped

END; Types of Exceptions

Predefined apprx. 20 errors ( < -20000 ) Declared Raised eg. ORA-01422 TOO_MANY_ROWS NO

NO

Non-Predefined Any other standard ( < -20000 ) YES NO Oracle Server Error

User Defined A condition that the (-20000 and -20999) YES YES developer determine is abnormal

User activity

4/12/23 4/12/23 62

PL/SQL

DEFINE p_dept_desc = ‘Information Technology’DEFINE p_dept_no = 300SET SERVEROUTPUT ON

DECLAREe_invalid_department EXCEPTION;

BEGINUPDATE departmentsSET department_name = ‘&p_dept_desc’WHERE department_id = &p_dept_no ;IF SQL%NOTFOUND THEN

RAISE e_invalid_department;

END IF;COMMIT;

EXCEPTIONWHEN e_invalid_department THENDBMS_OUTPUT.PUT_LINE(‘No such department id.’);

END;

4/12/23 4/12/23 63

PL/SQL

Reference the standard name in the exception handling routine.

Sample Pre-defined exceptionsNO_DATA_FOUND TOO_MANY_ROWSINVALID_CURSOR ZERO_DIVIDE

SET SERVEROUTPUT ONDECLARE v_deptno NUMBER(4); v_loc_id NUMBER(4);BEGIN SELECT department_id, location_id INTO v_deptno, v_loc_id FROM departments;

EXCEPTION WHEN TOO_MANY_ROWS THEN --traps pre-defined error DBMS_OUTPUT.PUT_LINE(‘fetches more than one row, use cursor…’); WHEN OTHERS THEN --traps errors which is not handled by above exception DBMS_OUTPUT.PUT_LINE(‘this error not trapped by exception..’);

END;

* The following block produces error with the execution part, the error is trapped in the exception part.

4/12/23 4/12/23 64

PL/SQL

Trap for Oracle Server error number -2292, an integrity constraint violation

DEFINE p_deptno = 10SET SERVEROUTPUT ON

DECLARE e_emps_remaining EXCEPTION; PRAGMA EXCEPTION_INIT (e_emps_remaining, -2292);

BEGIN DELETE FROM departments WHERE department_id = &p_deptno; COMMIT;

EXCEPTION WHEN e_emps_remaining THEN --traps non-predefined error DBMS_OUTPUT.PUT_LINE(‘Cannot remove dept ‘|| TO_CHAR(&p_deptno)

|| ‘. Employees exists. ‘);

END;

PRAGMA (pseudo-instructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the PL/SQL block is executed.

PRAGMA EXCEPTION_INIT(exception,oracle_error_number);

4/12/23 4/12/23 65

PL/SQL

SQLCODE Returns the numeric value for the error code.

SQLERRM Returns the message associated with the error number.

SET SERVEROUTPUT ONDECLARE v_deptno NUMBER(4); v_loc_id NUMBER(4); err_num NUMBER; err_msg VARCHAR2(100);BEGIN SELECT department_id, location_id INTO v_deptno, v_loc_id FROM departments;

EXCEPTION WHEN ZERO_DIVIDE THEN --traps pre-defined error DBMS_OUTPUT.PUT_LINE(‘number is divided by zero…’); WHEN OTHERS THEN --traps errors with Error Message err_num := SQLCODE; err_msg := SUBSTR(SQLERRM,1,100); DBMS_OUTPUT.PUT_LINE(‘Trapped Error: ‘|| err_msg );END;

4/12/23 4/12/23 66

PL/SQL

DECLARE.. ..e_no_rows EXCEPTION;e_integrity EXCEPTION;PRAGMA EXCEPTION_INIT(e_integrity, -2292);

BEGINFOR c_record IN emp_cursor LOOP BEGIN

SELECT .. .. ;UPDATE .. .. ;IF SQL%NOTFOUND THEN

RAISE e_no_rows;END IF;

END;END LOOP;

EXCEPTIONWHEN e_integrity THEN .. ..WHEN e_no_rows THEN .. ..

END;

Example

Block without the exception.

Sub-block can handle an exception or pass the exception to the enclosing block.

Error Trapped.

4/12/23 4/12/23 67

PL/SQL

Summary Exception Types

Predefined Oracle Server error

Non-predefined Oracle Server error

User-defined error

Exception Trapping

Exception Handling

Trap the exception within the PL/SQL block

Propagate the exception

4/12/23 4/12/23 68

PL/SQL

Creating Procedures

4/12/23 4/12/23 69

PL/SQL

A Subprogram :

Is a named PL/SQL block that can accept parameters and be invoked from a calling environment.

Procedure - that performs an actionFunction - that computes a value

Provides easy maintenance , improved data security, improved performance.

<header> IS | AS

Declaration section .. ..

BEGIN .. ..

EXCEPTION.. ..

END;

4/12/23 4/12/23 70

PL/SQL

A procedure is a type of named subprogram that performs an action.

Procedure accepts parameters / arguments for further calls.

A procedure can be stored in the database, as a schema object, for repeated execution.

<header> IS | AS .. ..

BEGIN .. ..EXCEPTION .. .. END;

Procedure

IN parameter (default)

OUT parameter

IN OUT parameter

Calling environment

4/12/23 4/12/23 71

PL/SQL

176 p_id

CREATE OR REPLACE PROCEDURE raise_salary(p_id IN employees.employee_id%TYPE)

ISBEGIN

UPDATE employeesSET salary = salary * 1.20WHERE employee_id = p_id;

END raise_salary;/

EXECUTE raise_salary(176);

Formal parameters : Variables declared in the parameter list of a subprogram specification.CREATE PROCEDURE raise_sal (p_id NUMBER, p_amount NUMBER)

.. ..END raise_sal;

Actual parameters : Variables or expressions referenced in the parameter list of a subprogram call.

raise_sal(v_id,200)

Formal parametersActual parameters

4/12/23 4/12/23 72

PL/SQL

CREATE OR REPLACE PROCEDURE query_emp (p_id IN employees.employee_id%TYPE, p_name OUT employees.last_name%TYPE, p_sal OUT employees.salary%TYPE)ISBEGIN SELECT last_name, salary INTO p_name, p_sal FROM employees WHERE employee_id = p_id;END query_emp;/

VARIABLE g_name VARCHAR2(20)VARIABLE g_sal NUMBER

EXECUTE query_emp(171, :g_name, :g_sal)PRINT g_name g_sal

171 p_id

SMITH p_name

7400 p_sal

4/12/23 4/12/23 73

PL/SQL

CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) ISBEGIN p_phone_no := ‘(‘ || SUBSTR(p_phone_no,1,3) ||

‘)’ || SUBSTR(p_phone_no,4,3) || ‘-’ || SUBSTR(p_phone_no,7) ;

END format_phone;/

VARIABLE g_ph_no VARCHAR2(15)BEGIN

:g_ph_no := ‘8006330575’;END;/PRINT g_ph_noEXECUTE format_phone(:g_ph_no)PRINT g_ph_no

‘8006330575’ p_phone_no‘(800)633-0575’

4/12/23 4/12/23 74

PL/SQL

CREATE OR REPLACE PROCEDURE add_dept(p_name IN departments.department_name%TYPE DEFAULT ‘unknown’, p_loc IN departments.location_id%TYPE DEFAULT 1700 )ISBEGIN INSERT INTO departments(department_id, department_name,

location_id) VALUES(dept_seq.NEXTVAL, p_name, p_loc);END add_dept;/

Note : OUT and IN OUT parameters are not permitted to have DEFAULT values.

4/12/23 4/12/23 75

PL/SQL

PositionalNamedCombination

BEGIN add_dept; add_dept(‘TRAINING’, 2500); add_dept(p_loc =>2400, p_name =>‘EDUCATION’); add_dept(p_loc =>1200);END;/SELECT department_id, department_name, location_id FROM departments;

Consider dept_seq.NEXTVAL 280

DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID280 unknown 1700290 TRAINING 2500300 EDUCATION 2400310 unknown 1200

4/12/23 4/12/23 76

PL/SQL

DECLARE v_id NUMBER := 163;

BEGIN

raise_salary(v_id); -- invoke procedure

COMMIT;

END;

4/12/23 4/12/23 77

PL/SQL

CREATE OR REPLACE PROCEDURE process_empsIS CURSOR emp_cursor IS SELECT employee_id FROM employees;BEGIN

FOR emp_rec IN emp_cursor LOOP raise_salary(emp_rec.employee_id); -- invoke procedure

END LOOP; COMMIT;

END process_emps;/

DROP PROCEDURE raise_sal;

4/12/23 4/12/23 78

PL/SQL

CREATE OR REPLACE PROCEDURE leave_emp2(p_id IN employees.employee_id%TYPE)

IS PROCEDURE log_exec IS BEGIN

INSERT INTO log_table (user_id, log_date)VALUES (user, sysdate);

END log_exec;

BEGIN DELETE FROM employees WHERE employee_id = p_id;

log_exec;

END leave_emp2;

/

4/12/23 4/12/23 79

PL/SQL

PROCEDURE p1IS ..BEGIN .. P2(arg1); ..EXCEPTION ..END p1;

Control returns to the calling procedure

Calling Procedure

Called Procedure

PROCEDURE p2IS ..BEGIN .. -- error ..EXCEPTION -- handledEND p2; PROCEDURE p1

IS ..BEGIN .. P2(arg1); ..EXCEPTION ..END p1;

PROCEDURE p2IS ..BEGIN .. -- error ..EXCEPTION -- unhandledEND p2;

Control returns to the exception section of calling procedure

Calling Procedure

Called Procedure

4/12/23 4/12/23 80

PL/SQL

Summary Procedure – performs an action

IN OUT IN OUT

DEFAULT option

Passing Parameters

Invoking Procedure from Anonymous and another Procedure

Standalone Procedure

Handled / Un-Handled Exception

Removing Procedure

4/12/23 4/12/23 81

PL/SQL

Creating Functions

4/12/23 4/12/23 82

PL/SQL

A function is a named PL/SQL Block that returns a Value.

A function can be stored in the database as a schema object for repeated execution.

A function is called as part of an expression.

The function must have at least one RETURN clause in the header.

at least one RETURN statement in the executable.

Accept only valid SQL data-types, not PL/SQL types

Return only valid SQL data-types, not PL/SQL types

FUNCTION RETURN dataype IS | AS .. ..

BEGIN .. RETURN v_s ..EXCEPTION .. .. END;

Function

IN parameter (default)

4/12/23 4/12/23 83

PL/SQL

CREATE OR REPLACE FUNCTION get_sal (p_id IN employees.employee_id%TYPE) RETURN NUMBERIS

v_salary employees.salary%TYPE := 0;

BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_id;

RETURN v_salary;END get_sal;/

VARIABLE g_sal NUMBEREXECUTE :g_sal := get_sal(117)PRINT g_sal

117 p_id

RETURN v_salary

4/12/23 4/12/23 84

PL/SQL

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER) RETURN NUMBER IS

BEGIN

RETURN (p_value * 0.08);

END tax;

/

SELECT employee_id, last_name, salary, tax(salary)FROM employeesWHERE department_id = 60;

SELECT employee_id, tax(salary)FROM employeesWHERE tax(salary) > (SELECT MAX(tax(salary))

FROM employees WHERE department_id = 30)

ORDER BY tax(salary) DESC;

4/12/23 4/12/23 85

PL/SQL

CREATE OR REPLACE FUNCTION dml_call_sql(p_sal NUMBER) RETURN NUMBER

IS

BEGIN

INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary)

VALUES (999, ‘employee1’, ’emp1@cpm.com’, sysdate, ‘SA_MAN’, 1000);

RETURN (p_sal + 100);END;

/UPDATE employees SET salary = dml_call_sql(2000)WHERE employee_id = 170;

4/12/23 4/12/23 86

PL/SQL

Direct access

hr.employees

GRANT SELECTON employeesTO scott;

GRANT EXECUTEON query_empTO green;

In-Direct access

GreenScott

SELECT

EXECUTE scott.query_emp

EXECUTE privilege for subprograms

PROCEDUREquery_emp

4/12/23 4/12/23 87

PL/SQL

Default AUTHID DEFINER

4/12/23 4/12/23 88

PL/SQL

USER_OBJECTS Provides general information about the object

USER_SOURCE Provides the text of the object

USER_ERRORS Shows compilation errors

SHOW ERRORS PROCEDURE log_execution

DESCRIBE tax

4/12/23 4/12/23 89

PL/SQL

Creating Packages

4/12/23 4/12/23 90

PL/SQL

Group logically related PL/SQL types, items and subprograms.

Allow the Oracle Server to read multiple objects into memory at once.

Package SPECIFICATION

Procedure A declaration

Package BODY

Procedure B definition

Procedure A definition

4/12/23 4/12/23 91

PL/SQL

Procedure A declaration

Procedure B definition

Procedure A definition

Package SPECIFICATION

Package BODY

Public Variable

Public Procedure

Private Variable

Private Procedure

Public procedure

Local Variable

4/12/23 4/12/23 92

PL/SQL

CREATE PACKAGE keyword for package specification.

CREATE PACKAGE BODY keyword for package body.

A package specification can exist without a package body, but the package body cannot exist without a package specification.

Package XYZ Package UVW

4/12/23 4/12/23 93

PL/SQL

CREATE OR REPLACE PACKAGE comm_package ISg_comm NUMBER := 0.10;

PROCEDURE reset_comm (p_comm IN NUMBER);END comm_package;

CREATE OR REPLACE PACKAGE BODY comm_package ISFUNCTION validate_comm (p_comm IN NUMBER)RETURN BOOLEAN IS

v_max_comm NUMBER;BEGIN SELECT MAX(commission_pct) INTO v_max_comm FROM employees;

IF p_comm > v_max_comm THEN RETURN(FALSE);

ELSE RETURN (TRUE);

END IF;END validate_comm;

PROCEDURE reset_comm (p_comm IN NUMBER) ISBEGIN

IF validate_comm(p_comm)THEN g_comm := p_comm --reset the global variableELSE RAISE_APPLICATION_ERROR(-20210,’Invalid Commission’);END IF;

END reset_comm;END comm_package;

Invoke a function within the

same package

4/12/23 4/12/23 94

PL/SQL

EXECUTE comm_package.rest_comm(0.15)

Invoke a package procedure from SQL Worksheet

EXECUTE scott.comm_package.rest_comm(0.15)

Invoke a package procedure in a different schema

EXECUTE scott.comm_package.rest_comm@db1(0.15)

Invoke a package procedure in a remote database

4/12/23 4/12/23 95

PL/SQL

CREATE OR REPLACE PACKAGE global_consts ISmile_2_kilo CONSTANT NUMBER := 1.6093;kilo_2_mile CONSTANT NUMBER := 0.6214;yard_2_meter CONSTANT NUMBER := 0.9144;meter_2_yard CONSTANT NUMBER := 1.0936;

END global_consts;/

EXECUTE DBMS_OUTPUT.PUT_LINE(’20 miles = ‘ || 20 * global_consts.mile_2_kilo|| ‘ km’)

CREATE OR REPLACE PROCEDURE me_to_yard( p_meter IN NUMBER,

p_yard OUT NUMBER)ISBEGIN

p_yard := p_meter * global_consts.meter_2_yard;END me_to_yard; /

VARIABLE yard NUMBEREXECUTE me_to_yard (1, :yard)PRINT yard

4/12/23 4/12/23 96

PL/SQL

Enables you to use the same name for different subprograms inside a PL/SQL block, a subprogram, or a package Requires the formal parameters of the subprograms to differ in number, order, or data type family.

CREATE OR REPLACE PACKAGE over_pack IS PROCEDURE add_dept (p_deptno IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT ‘unknown’, p_loc IN departments.location_id%TYPE DEFAULT 0);

PROCEDURE add_dept (p_dno IN dept.dno%TYPE DEFAULT 0, p_dname IN dept.dname%TYPE DEFAULT ‘unknown’);

PROCEDURE add_dept (p_dname IN col_dept.dname%TYPE DEFAULT ‘unknown’, p_hod IN col_dept.hod%TYPE DEFAULT ‘unknown’);

END over_pack;

/

4/12/23 4/12/23 97

PL/SQLCREATE OR REPLACE PACKAGE BODY over_pack IS PROCEDURE add_dept (p_deptno IN departments.department_id%TYPE, p_name IN departments.department_name%TYPE DEFAULT ‘unknown’, p_loc IN departments.location_id%TYPE DEFAULT 0) IS BEGIN INSERT INTO departments(department_id, department_name, location_id) VALUES (p_deptno, p_name, p_loc); END add_dept;

PROCEDURE add_dept (p_dno IN dept.dno%TYPE DEFAULT 0 , p_dname IN dept.dname%TYPE DEFAULT ‘unknown’) IS BEGIN INSERT INTO dept(dno, dname) VALUES (p_dno, p_dname); END add_dept; PROCEDURE add_dept (p_dname IN college_dept.dname%TYPE DEFAULT ‘unknown’, p_hod IN college_dept.hod%TYPE DEFAULT ‘unknown’) IS BEGIN INSERT INTO college_dept(dname, hod) VALUES (p_dname, p_hod); END add_dept;END over_pack;

EXECUTE over_pack.add_dept(980 , ‘Education’, 2500)EXECUTE over_pack.add_dept(90 , ‘Training’)EXECUTE over_pack.add_dept(‘Database’, ‘DeCarl’)

4/12/23 4/12/23 98

PL/SQL

CREATE OR REPLACE PACKAGE taxes_packIS

FUNCTION tax(p_value IN NUMBER) RETURN NUMBER;

END taxes_pack;/

CREATE OR REPLACE PACKAGE BODY taxes_packIS

FUNCTION tax(p_value IN NUMBER) RETURN NUMBER

IS v_rate NUMBER := 0.08;BEGIN

RETURN (p_value * v_rate);END tax;

END taxes_pack;/

SELECT last_name, salary, taxes_pack.tax(salary)FROM employees;

4/12/23 4/12/23 99

PL/SQL

DROP PACKAGE package_name;

DROP PACKAGE BODY package_name;

Better Performance The entire package is loaded into memory when the package is first referenced. There is only one copy in memory for all users. The dependency hierarchy is simplified.

Over-loading : Multiple subprograms of the same name.

4/12/23 4/12/23 100

PL/SQL

SummaryPackage SPECIFICATION

Package BODY

Invoking Package – EXECUTE

Bodiless Package

Overloading

User Defined Package

4/12/23 4/12/23 101

PL/SQL

Oracle Supplied Packages

4/12/23 4/12/23 102

PL/SQL

ParseCheck for the statement syntaxEnsure the referenced object existEnsure the relevant privilege to those object.

BindJoin the fetched value to the variable / column / column alias

ExecuteAll necessary information and resources are met

Fetch (only SELECT)Rows requested are selected and ordered.

Are provided with the Oracle server.

Extend the functionality of the database.

4/12/23 4/12/23 103

PL/SQL

Use OPEN_CURSOR to establish an area memory to process a SQL statement.

Use PARSE to establish the validity of the SQL statement.

Use EXECUTE function to run the SQL statement. The function returns the number of row processed.

Use CLOSE_CURSOR to close the cursor.

CREATE OR REPLACE PROCEDURE delete_all_rows(p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)

IScursor_name INTEGER;

BEGINcursor_name := DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cursor_name, ‘DELETE FROM ‘ || p_tab_name,

DBMS_SQL.NATIVE);p_rows_del := DBMS_SQL.EXECUTE (cursor_name);DBMS_SQL.CLOSE_CURSOR(cursor_name);

END;/VARIABLE deleted NUMBEREXECUTE delete_all_rows(‘employees’, :deleted)PRINT deleted

4/12/23 4/12/23 104

PL/SQL

CREATE OR REPLACE PROCEDURE del_rows(p_table_name IN VARCHAR2, p_rows_deld OUT NUMBER)

ISBEGIN

EXECUTE IMMEDIATE ‘delete from ‘ || p_table_name;p_rows_deld := SQL%ROWCOUNT;

END;/

VARIABLE deleted NUMBEREXECUTE del_rows(‘test_employees’, :deleted)PRINT deleted

4/12/23 4/12/23 105

PL/SQL

Summary

Oracle Supplied Packages Are provided with the Oracle server.

Extend the functionality of the database.

4/12/23 4/12/23 106

PL/SQL

LOB - Large OBjects

4/12/23 4/12/23 107

PL/SQL

Used to store large unstructured data such as text, graphic, images, films and sound waveforms

Stores locator to the LOB’s value

CLOBBLOB BFILE

4/12/23 4/12/23 108

PL/SQL

ALTER TABLE employees ADD emp_video BFILE;

CREATE OR REPLACE DIRECTORY log_files AS ‘c:\pict’;GRANT READ ON DIRECTORY log_files TO PUBLIC;

CREATE OR REPLACE PROCEDURE load_emp_bfile(p_file_loc IN VARCHAR2) IS

v_file BFILE; v_filename VARCHAR2(16);

CURSOR emp_cursor ISSELECT first_name FROM employeesWHERE department_id = 60 FOR UPDATE;

BEGIN FOR emp_record IN emp_cursor LOOP v_filename := emp_record.first_name || ‘.bmp’;

v_file := BFILENAME(p_file_loc, v_filename);DBMS_LOB.FILEOPEN(v_file); UPDATE employees SET emp_video = v_file WHERE CURRENT OF emp_cursor;DBMS_OUTPUT.PUT_LINE(‘LOADED FILE: ‘||v_filename||‘Size:‘

|| DBMS_LOB.GETLENGTH(v_file)); DBMS_LOB.FILECLOSE(v_file);END LOOP;END load_emp_bfile; EXECUTE load_emp_bfile(‘LOG_FILES’)

4/12/23 4/12/23 109

PL/SQL

Database Triggers

4/12/23 4/12/23 110

PL/SQL

Is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database.

Executes implicitly whenever a particular event takes place.

Two types of Triggers

Application Trigger Fires whenever an event occurs with a particular application.

Database Trigger Fires whenever a data event (such as DML) or system event (such as logon or shutdown on a schema or database)

4/12/23 4/12/23 111

PL/SQL

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

INSTEAD OF Used for views.

4/12/23 4/12/23 112

PL/SQL

DML statement

INSERT INTO departments (department_id,department_name, location_id)VALUES (400, ‘COUNSULTING’, 2400);

Triggering

action

BEFORE statement trigger

AFTER statement trigger

AFTER row trigger

BEFORE row trigger

UPDATE employees SET salary = salary * 1.5 WHERE department_id = 30;

Triggering

action

BEFORE statement trigger

AFTER statement trigger

AFTER row triggerBEFORE row trigger

... ...

AFTER row triggerBEFORE row trigger

303030303030

4/12/23 4/12/23 113

PL/SQL

Create a trigger to restrict inserts into the EMPLOYEES table to certain business hours, i.e. 8.00 am to 6.00 pm, Monday through Friday.

CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT ON employees

BEGIN IF (TO_CHAR(SYSDATE,’DY’) IN (‘SAT’,’SUN’)

ORTO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN

’08:00’ AND ’18:00’) THEN RAISE_APPLICATION_ERROR(-20500,’You many insert

into Employees table only during business hours.’); END IF;END secure_emp;

4/12/23 4/12/23 114

PL/SQL

Combine several triggering events into one trigger body.

CREATE OR REPLACE TRIGGER secure_emp1BEFORE INSERT OR UPDATE OR DELETE ON employees

BEGIN IF (TO_CHAR(SYSDATE,’DY’) IN (‘SAT’,’SUN’) OR

TO_CHAR(SYSDATE,’HH24:MI’) NOT BETWEEN ‘08:00’ AND ’18:00’) THEN IF DELETING THEN

RAISE_APPLICATION_ERROR(-20502,’Delete during office hours...’); ELSIF INSERTING THEN

RAISE_APPLICATION_ERROR(-20503,’Insert during office hours...’); ELSIF UPDATING(‘SALARY’) THEN RAISE_APPLICATION_ERROR(-20504,’Update SALARY during office hours’); ELSE RAISE_APPLICATION_ERROR(-20506,’Update during office hours’); END IF; END IF;END;

4/12/23 4/12/23 115

PL/SQL

Create a trigger to allow job id other than AD_PRES , VD_VP cannot earn more than 15000

CREATE OR REPLACE TRIGGER restrict_salary BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROWBEGIN IF NOT (:NEW.job_id IN (‘AD_PRES’, ‘AD_VP’)) AND

:NEW.salary > 15000 THENRAISE_APPLICATION_ERROR(-20202,’Employees cannot earn

more than this amount’); END IF;END;/

Data Operation : OLD Value : NEW Value

INSERT NULL Inserted valueUPDATE Value before update Value after updateDELETE Value before delete NULL

4/12/23 4/12/23 116

PL/SQL

Only Available for ROW Triggers

SELECT * FROM aud_emp;

CREATE OR REPLACE TRIGGER audit_emp_values AFTER DELETE OR INSERT OR UPDATE ON employees FOR EACH ROWBEGIN INSERT INTO aud_emp(user_name, timestampid, old_lname,

new_lname, old_title, new_title, old_salary, new_salary)

VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);

END;/

4/12/23 4/12/23 117

PL/SQL

To restrict the trigger action for those rows satisfy a certain condition, provide WHEN clause.

CREATE OR REPLACE TRIGGER derive_commpct BEFORE INSERT OR UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.job_id = ‘SA_REP’)BEGIN IF INSERTING THEN :NEW.commission_pct := 0; ELSIF :OLD.commission_pct IS NULL THEN :NEW.commission_pct := 0; ELSE :NEW.commission_pct := OLD.commission_pct + 0.05; END IF;END;/

4/12/23 4/12/23 118

PL/SQL

TRIGGERS

Source Code USER_TRIGGERS

Implicitly Invoked

COMMIT, SAVEPOINT, ROLLBACK are not allowed

PROCEDURES

Source Code USER_SOURCE

Explicitly Invoked

COMMIT, SAVEPOINT, ROLLBACK are allowed

DROP TRIGGER trigger_name;

ALTER TRIGGER trigger_name DISABLE|ENABLE;

ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS;

4/12/23 4/12/23 119

PL/SQL

CREATE, ALTER or DROP

Logging ON / OFF

Shutting down or starting up the database.

CREATE OR REPLACE TRIGGER logon_trigAFTER LOGON ON SCHEMA | DATABASE

BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES (USER, SYSDATE, ‘Logging on’);END logon_trig;/

CREATE OR REPLACE TRIGGER logoff_trigBEFORE LOGOFF ON SCHEMA | DATABASE

BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES (USER, SYSDATE, ‘Logging Off’);END logoff_trig;/

4/12/23 4/12/23 120

PL/SQL

CREATE OR REPLACE TRIGGER log_empBEFORE INSERT ON EMPLOYEES

CALL log_execution;END log_emp;/

CREATE OR REPLACE TRIGGER check_salaryBEFORE UPDATE OF salary ON EMPLOYEES

FOR EACH ROWWHEN (NEW.salary < OLD.salary)

BEGINRAISE_APPLICATION_ERROR(-20509,

‘Do not decrease salary..’); END check_salary;/

4/12/23 4/12/23 121

PL/SQL

Summary

Application Trigger

Database Trigger

Row Trigger :OLD :NEW

Restrict Row Trigger - WHEN

Calling a Procedure - CALL proc_name

4/12/23 4/12/23 122

PL/SQL

Managing Dependencies

4/12/23 4/12/23 123

PL/SQL

Procedure TableView orProcedure

Referenced Dependent

Dependent ReferencedIndirect

dependency

Direct dependency

Direct dependency

4/12/23 4/12/23 124

PL/SQL

Procedure TableProcedure

Direct Local Dependency

View

Procedure TableProcedure View

Definition changed

INVALIDINVALIDINVALID

SELECT name, type, referenced_name, referenced_typeFROM user_dependenciesWHERE referenced_name IN (‘EMPLOYEES’,’EMP_VW’);

EXECUTE deptree_fill(‘TABLE’,’HR’,’EMPLOYEES’);SELECT nested_level, type, name FROM deptree ORDER BY seq#;

utldtree.sql

ideptreeIndirect dependency

4/12/23 4/12/23 125

PL/SQL

ALTER PROCEDURE proc_name COMPILE;

ALTER FUNCTION func_name COMPILE;

ALTER PACKAGE pack_name COMPILE [PACKAGE];ALTER PACKAGE pack_name COMPILE BODY;

ALTER TRIGGER trig_name COMPILE;