10g plsql slide
Post on 18-May-2015
2.903 Views
Preview:
TRANSCRIPT
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;
top related