Theory Practice Methodology of Relational Database Design and

Theory, Practice & Methodology of Relational Database Design and Programming Copyright © Ellis Cohen 2002 -2008 Embedded Database Programming Using PL/SQL These slides are licensed under a Creative Commons Attribution-Non. Commercial-Share. Alike 2. 5 License. For more information on how you may use them, please see http: //www. openlineconsult. com/db © Ellis Cohen 2001 -2008

Overview of Lecture Basic PL/SQL Conditional Statements CASE Expressions & Statements Loops Stored Functions Stored Procedures Stored Packages Sequential Values Exceptions Record Types Query-Based FOR Loops Dynamic SQL © Ellis Cohen 2001 -2008 2

Basic PL/SQL © Ellis Cohen 2001 -2008 3

PL/SQL Block DECLARE <declaration statements> BEGIN <executable statements> EXCEPTION <exception handling code> END; © Ellis Cohen 2001 -2008 4

Simplest Block BEGIN Do Nothing NULL; END; This block is anonymous (or unnamed) © Ellis Cohen 2001 -2008 5

Hello World Anonymous (unnamed) PL/SQL block invoked directly from SQL*Plus Built-in package Procedure in package SQL> BEGIN dbms_output. put_line( 'Hello World' ); END; / SQL*Plus knows how to parse pure SQL, but not PL/SQL, so it doesn't know when a PL/SQL block actually ends. Use / to tell it. © Ellis Cohen 2001 -2008 6

Hello World via Stored Procedure SQL> CREATE PROCEDURE pl( str varchar ) IS BEGIN dbms_output. put_line( str ); END; / SQL> BEGIN pl( 'Hello World' ); END; / © Ellis Cohen 2001 -2008 7

SQL*Plus EXECUTE SQL> EXECUTE pl('Hello World') automatically translated into SQL> BEGIN pl( 'Hello World' ); END; / © Ellis Cohen 2001 -2008 8

Declaring & Using Variables Adding 100 to salary of employee 7876 SQL> PL/SQL variable DECLARE declaration & mgrno int : = 7876; initialization BEGIN UPDATE Emps SET sal = sal + 100 WHERE empno = mgrno; END; / Using PL/SQL variable in SQL command PL/SQL does not use a : prefix to identify variables (e. g. : mgrno) so there is no obvious way to distinguish attributes and variables © Ellis Cohen 2001 -2008 9

Naming Conflicts Adding 100 to salary of employee 7876 DECLARE mgrno int : = 7876; BEGIN UPDATE Emps SET sal = sal + 100 WHERE empno = mgrno; END; Adding 100 to salary of all employees who are their own manager DECLARE mgr int : = 7876; BEGIN UPDATE Emps SET sal = sal + 100 WHERE empno = mgr; END; The mgr attribute of Emps overrides the mgr variable declaration © Ellis Cohen 2001 -2008 10

Named/Labelled Blocks Adding 100 to salary of employee 7876 This block is named my. Block <<my. Block>> DECLARE mgr int : = 7876; BEGIN UPDATE Emps SET sal = sal + 100 WHERE empno = my. Block. mgr; END; Indicates that the variable mgr declared in my. Block should be used instead of emp's mgr attribute. © Ellis Cohen 2001 -2008 11

Using PL/SQL Variables PL/SQL variable UPDATE Emps SET sal = (sal + max_sal) / 2 WHERE empno = 7876; PL/SQL variable DELETE FROM Projs WHERE pname = bad. Pname; INSERT INTO Projs( pno, pname, pmgr ) VALUES( a. Pno, a. Pname, a. Pmgr ); You can use a PL/SQL variable wherever you can use a constant in SQL PL/SQL variable © Ellis Cohen 2001 -2008 12

PL/SQL is a Typed Language SQL Data Types: – CHAR(n), VARCHAR(n), LONG – NUMBER(n 1, n 2), INT/INTEGER, DEC/DECIMAL, SMALLINT, FLOAT/REAL – DATE Added PL/SQL-only Data Types – BOOLEAN – PLS_INTEGER -- fast internal integer (can't be NULL) – structured types (defined later) © Ellis Cohen 2001 -2008 13

Variable Declarations job_title varchar(80) : = 'Salesman'; the_date : = NULL; is_cool boolean; /* init to NULL */; counter int : = 7; Anchored incr CONSTANT int : = 1; declaration: Whatever the type is of the an. Ename varchar(30) attribute NOT NULL : = 'NONE'; Emps. empno a. Deptno number(5); an. Empno Emps. empno%TYPE; x. Empno an. Empno%TYPE; © Ellis Cohen 2001 -2008 14

Assignment Statements a. Deptno : = 10; counter : = counter + 1; Raises exception • if > 1 row selected, or • if 0 rows selected SELECT ename, deptno INTO an. Ename, a. Deptno FROM Emps WHERE empno = 7876; SELECT count(*) INTO counter FROM Emps WHERE deptno = 40; Useful Idiom! Succeeds no matter how many rows match the WHERE clause © Ellis Cohen 2001 -2008 15

Using Single Row Results DECLARE an. Ename varchar(30); a. Deptno int; BEGIN SELECT ename, deptno INTO an. Ename, a. Deptno FROM Emps WHERE empno = 7876; pl( an. Ename || ' ' || a. Deptno ); END; © Ellis Cohen 2001 -2008 16

Variables vs Simple Scalar Subqueries UPDATE Emps SET sal = sal + 100 WHERE job = (SELECT job FROM Emps WHERE ename = 'BLAKE') Only slight performance penalty Choice is primarily aesthetic DECLARE blakejob varchar(20); BEGIN SELECT job INTO blakejob FROM Emps WHERE ename = 'BLAKE'; UPDATE Emps SET sal = sal + 100 WHERE job = blakejob; END; © Ellis Cohen 2001 -2008 17

RETURNING INTO Delete & Update DELETE FROM Copy. Emps WHERE empno = 7876 RETURNING ename, deptno INTO an. Ename, a. Deptno; Returns information about deleted row • Raises exception if > 1 rows deleted • No effect if no rows are deleted UPDATE Copy. Emps SET sal = sal + 100 WHERE empno = 7876 Return value of sal RETURNING ename, sal after update INTO an. Ename, a. Sal; Returns information about updated row • Raises exception if > 1 rows updated • No effect if no rows are updated © Ellis Cohen 2001 -2008 18

RETURNING INTO Insert INSERT INTO Copy. Emps( empno, ename, deptno, sal ) VALUES( 6144, 'SONI', 40, get. Dept. Sal( 40 ) ) RETURNING sal INTO the. Sal; Returns information about inserted values INSERT INTO Copy. Emps SELECT * FROM Emps WHERE empno = 7876 RETURNING ename, deptno INTO an. Ename, a. Deptno; Not legal, sigh …. © Ellis Cohen 2001 -2008 19

Conditional Statements © Ellis Cohen 2001 -2008 20

Conditional Statements IF profit > 50000 THEN UPDATE Emps SET sal = sal + 200; UPDATE Bonus SET amt =. 004 WHERE job = 'DEPTMGR'; END IF; IF profit > 50000 THEN UPDATE Emps SET sal = sal + 200; UPDATE Bonus SET amt =. 004 WHERE job = 'DEPTMGR'; ELSE UPDATE Emps SET sal = sal + 50 WHERE job = 'DEPTMGR'; END IF; © Ellis Cohen 2001 -2008 This is a PL/SQL variable. It does not identify data in a DB table 21

Using NULLs in Conditional Statements IF profit > 50000 THEN UPDATE Emps SET sal = sal + 200; UPDATE Bonus SET amt =. 004 WHERE job = 'DEPTMGR'; END IF; IF profit <= 50000 THEN NULL; ELSE UPDATE Emps SET sal = sal + 200; UPDATE Bonus SET amt =. 004 WHERE job = 'DEPTMGR'; END IF; Equivalent if profit is not NULL © Ellis Cohen 2001 -2008 22

Using NULL Statements to Avoid NULL Checks IF a = b THEN NULL; ELSE Do. Something(); END IF; Suppose both a and b can be NULL Rewrite the above statement with only a THEN clause. © Ellis Cohen 2001 -2008 23

Doing NULL Checks IF a = b THEN NULL; ELSE Do. Something(); END IF; Equivalent IF (a != b) OR (a IS NULL) OR (b IS NULL) THEN Do. Something(); END IF; © Ellis Cohen 2001 -2008 24

Conditionals with ELSIF IF profit > 50000 THEN UPDATE Emps SET sal = sal + 200; UPDATE Bonus SET pct =. 004 WHERE job = 'DEPTMGR'; ELSIF profit > 20000 THEN UPDATE Emps SET sal = sal + 50 WHERE job = 'DEPTMGR'; ELSIF profit > 0 THEN NULL; ELSE DELETE * FROM Emps WHERE job = 'CLERK'; END IF; © Ellis Cohen 2001 -2008 25

Insert/Update Exercise Write an anonymous PL/SQL block that does the following to the Projs table: if project #30420 is not in the table, INSERT project #30420 with pname: 'My Project', and pmgr: 2020 else if it already exists, UPDATE project #30420 with pname: 'Your Project', and pmgr: 2020 © Ellis Cohen 2001 -2008 26

Can’t Coerce Scalar Result Sets DECLARE the. Pno int : = (SELECT pno FROM Projs WHERE pno = 30420); BEGIN IF the. Pno IS NULL THEN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); ELSE UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; END IF; END; DOESN'T WORK! The SELECT statement produces a result set, not an integer © Ellis Cohen 2001 -2008 27

Scalar Coercion Doesn’t Work BEGIN IF (SELECT count(*) FROM Projs WHERE pno = 30420) = 0 THEN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); ELSE UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; END IF; END; DOESN'T WORK! Coercion of Scalar Result Sets to scalar values only works in a SQL Query © Ellis Cohen 2001 -2008 28

Exists Doesn't Work BEGIN IF exists( SELECT * FROM Projs WHERE pno = 30420 ) THEN UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; ELSE INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); END IF; END; DOESN'T WORK! exists can ONLY be used in a SQL QUERY © Ellis Cohen 2001 -2008 29

Empty SELECTs Fail DECLARE the. Pno int; BEGIN SELECT pno INTO the. Pno FROM Projs WHERE pno = 30420; IF the. Pno IS NULL THEN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); ELSE UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; END IF; END; DOESN'T WORK! The SELECT statement will cause an error if Projs doesn't have project 30420 © Ellis Cohen 2001 -2008 30

Insert/Update Exercise Answer DECLARE knt number(5); BEGIN SELECT count(*) INTO knt FROM Projs WHERE pno = 30420; IF knt = 0 THEN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); ELSE UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; END IF; END; © Ellis Cohen 2001 -2008 31

Using RETURNING INTO DECLARE the. Pno int; BEGIN UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420 RETURNING pno INTO the. Pno; IF the. Pno IS NULL THEN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); END IF; END; the. Pno will remain NULL if nothing is updated © Ellis Cohen 2001 -2008 32

Using SQL%ROWCOUNT BEGIN UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; IF SQL%ROWCOUNT = 0 THEN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ); END IF; END; Better approach! SQL%ROWCOUNT returns the number of rows affected by the previous SQL command – in this case, the number of rows updated. © Ellis Cohen 2001 -2008 33

Other Insert/Update Approaches Oracle 10 g: MERGE INTO Projs p USING DUAL ON (p. pno = 30420) WHEN MATCHED THEN UPDATE SET pname = 'Your Project', pmgr = 2020 WHEN NOT MATCHED THEN INSERT ( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ) My. SQL 5. 0: INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ) ON DUPLICATE KEY UPDATE SET pname = 'Your Project', pmgr = 2020 © Ellis Cohen 2001 -2008 34

Case Expressions & Statements © Ellis Cohen 2001 -2008 35

Simple CASE Expression salincr : = CASE WHEN profit > 50000 THEN 200 WHEN profit > 20000 THEN 50 WHEN profit > 0 THEN 0 ELSE –profit/200 END; Returns the value associated with the first expression which evaluates to TRUE Similar to SQL case expressions © Ellis Cohen 2001 -2008 36

ELSE NULL Implied salincr : = CASE WHEN profit > 50000 THEN 200 WHEN profit > 20000 THEN 50 WHEN profit > 0 THEN 0 END; Equivalent ELSE NULL implied if no explicit ELSE clause salincr : = CASE WHEN profit > 50000 THEN 200 WHEN profit > 20000 THEN 50 WHEN profit > 0 THEN 0 ELSE NULL END; © Ellis Cohen 2001 -2008 37

Searched CASE Expression salincr : = CASE cooljob WHEN 'DEPTMGR' THEN 500 WHEN 'ANALYST' THEN 300 ELSE 150 END; Equivalent salincr : = CASE WHEN cooljob = 'DEPTMGR' THEN 500 WHEN cooljob = 'ANALYST' THEN 300 ELSE 150 END; © Ellis Cohen 2001 -2008 38

Simple CASE Statement SELECT job INTO cooljob FROM Emps WHERE empno = coolemp; CASE WHEN cooljob = 'DEPTMGR' THEN bonuspct : =. 004; salincr : = 500; WHEN cooljob = 'ANALYST' THEN salincr : = 300; Only the statements ELSE matching the first expression which salincr : = 150; evaluates to TRUE END CASE; are executed ELSE NULL implied here as well if no explicit ELSE clause © Ellis Cohen 2001 -2008 39

Searched CASE Statement SELECT job INTO cooljob FROM Emps WHERE empno = coolemp; CASE cooljob WHEN 'DEPTMGR' THEN bonuspct : =. 004; salincr : = 500; WHEN 'ANALYST' THEN salincr : = 300; ELSE Only the statements matching the first value salincr : = 150; found are executed END CASE; © Ellis Cohen 2001 -2008 40

Conditional vs. Simple CASE Statements CASE WHEN profit > 50000 THEN UPDATE Emps SET sal = sal + 200; UPDATE Bonus SET pct =. 004 WHERE job = 'DEPTMGR'; WHEN profit > 20000 THEN UPDATE Emps SET sal = sal + 50 WHERE job = 'DEPTMGR'; WHEN profit > 0 NULL; ELSE DELETE * FROM Emps WHERE job = 'CLERK'; END CASE; © Ellis Cohen 2001 -2008 41

Loops © Ellis Cohen 2001 -2008 42

For Loops FOR LOOP variables are automatically declared These don't need to be constants; they can be arbitrary expression BEGIN FOR i IN 1. . 10 LOOP INSERT INTO Projs ( pno, pname, pmgr ) VALUES( i, 'Base Project ' || i, 7789 ); END LOOP; END; ALWAYS incr by 1, except can also write FOR i IN REVERSE 1. . 10 © Ellis Cohen 2001 -2008 43

While Loops Get the first employee tracked by mypkg DECLARE an. Empno NUMBER(5) : = mypkg. get. First. Emp(); BEGIN WHILE an. Empno IS NOT NULL LOOP DELETE FROM Asns WHERE empno = an. Empno; an. Empno : = mypkg. get. Next. Emp(); END LOOP; END; Get the next employee tracked by mypkg, return NULL when none left © Ellis Cohen 2001 -2008 44

Loops with Exit LOOP Elided code that computes credit rating . . . IF credit_rating < 3 THEN EXIT; END IF; . . . END LOOP; LOOP EXITs can also be used with FOR & WHILE loops, though some authors frown on this practice . . . EXIT WHEN credit_rating < 3; . . . END LOOP; © Ellis Cohen 2001 -2008 45

Labelled Loops with Exit outer loop, labelled this_one <<this_one>> LOOP . . . LOOP inner loop . . . EXIT this_one WHEN credit_rating < 3; . . . END LOOP; . . . END LOOP this_one; Optional, but recommended © Ellis Cohen 2001 -2008 46

Loop Exercise lst is a list of employee numbers (all of which are guaranteed to identify employees in the Emps table) [don't be concerned, for now, with how lst is represented] Int. Lists. length( lst ) returns the length of lst (i. e. how many employee's numbers are in lst) Int. Lists. get( lst, nth ) returns the nth employee number in lst Find the first employee listed in lst whose commission is larger than 2000, and add that employee's number and name to the Winners table © Ellis Cohen 2001 -2008 47

Loop Exercise Answer DECLARE an. Empno Emps. empno%type; an. Ename Emps. ename%type; a. Comm Emps. comm%type; BEGIN FOR knt in 1. . Int. Lists. length(lst) LOOP an. Empno : = Int. Lists. nth( lst, knt ); SELECT ename, comm INTO an. Ename, a. Comm FROM Emps WHERE empno = an. Empno; IF (a. Comm > 2000) THEN INSERT INTO winners VALUES( an. Empno, an. Ename ); EXIT; END IF; END LOOP; END; © Ellis Cohen 2001 -2008 48

Stored Functions © Ellis Cohen 2001 -2008 49

Defining & Using Stored Functions SQL> CREATE FUNCTION sqr( num int ) RETURN int IS BEGIN RETURN num * num; END; / Functions always SQL> EXECUTE pl( sqr( 7 ) ) return a value. -- displays 49 SQL> DECLARE sqrval int; BEGIN sqrval : = sqr( 7 ); pl( sqrval ); END; / The RETURN clause declares the type of value returned © Ellis Cohen 2001 -2008 50

CREATE or REPLACE optional SQL> CREATE OR REPLACE FUNCTION sqr( num int ) RETURN int IS BEGIN RETURN num * num; END; / First drops the function before redefining it. If just CREATE is used, and the function is already defined, the CREATE will fail. © Ellis Cohen 2001 -2008 51

Using Stored Functions from SQL> CREATE FUNCTION sqr( num int ) RETURN int IS BEGIN RETURN num * num; END; / SQL> SELECT empno, sal, sqr( sal ) FROM Emps; © Ellis Cohen 2001 -2008 52

The DUAL Table SELECT ename, sysdate FROM Emps Lists the employee name of all 14 employees, along with today's date SELECT sysdate FROM Emps Lists today's date 14 times sysdate is a built-in parameterless function which returns the current date SELECT sysdate FROM DUAL is a built-in unmodifiable table which has one column (DUMMY) and one row Lists today's date ONCE! © Ellis Cohen 2001 -2008 53

Parameterless Functions SQL> CREATE OR REPLACE FUNCTION get. Date RETURN date IS date; Parameterless BEGIN SELECT sysdate INTO dat FROM DUAL; RETURN dat; END; / Built-in functions can ONLY be used SQL> execute pl( get. Date ) in SQL> execute pl( get. Date() ) commands! User-defined parameterless functions can be called with or without parentheses from PL/SQL © Ellis Cohen 2001 -2008 54

Parameterless Functions from SQL> CREATE OR REPLACE FUNCTION get. Date RETURN date IS date; Parameterless BEGIN SELECT sysdate INTO dat FROM DUAL; RETURN dat; END; / SQL> SELECT * FROM Projs WHERE pstart > get. Date User-defined parameterless functions MUST be called without parentheses from SQL © Ellis Cohen 2001 -2008 55

Stored Procedures © Ellis Cohen 2001 -2008 56

Functions & Procedures Functions – can return a value – should not have side effects (this allows calls using them to be optimized) Procedures – do not have a return value, but can return results through OUT parameters – may have arbitrary side effects © Ellis Cohen 2001 -2008 57

Defining & Using Stored Procedures SQL> CREATE OR REPLACE PROCEDURE Delete. Employee( an. Empno int ) IS BEGIN DELETE FROM Emps WHERE empno = an. Empno; END; / SQL> EXECUTE Delete. Employee( 3142 ) © Ellis Cohen 2001 -2008 58

OUT Parameters SQL> CREATE OR REPLACE PROCEDURE Delete. Employee( an. Empno int, the. Deptno OUT int ) IS BEGIN DELETE FROM Emps WHERE empno = an. Empno RETURNING deptno INTO the. Deptno; END; An OUT parameter can be used / like a variable, but also passes a value back to the caller when the procedure finishes SQL> DECLARE deldept int; BEGIN Delete. Employee( 3142, deldept ); pl( 'Employee deleted from dept ' || deldept ); END; / There can be any number of OUT parameters © Ellis Cohen 2001 -2008 59

Named Parameter Notation SQL> CREATE OR REPLACE PROCEDURE Delete. Employee( an. Empno int, the. Deptno OUT int ) IS BEGIN DELETE FROM Emps WHERE empno = an. Empno RETURNING deptno INTO the. Deptno; END; / SQL> DECLARE deldept int; BEGIN Delete. Employee( the. Deptno => deldept, an. Empno => 3142 ); pl( 'Employee deleted from dept ' || deldept ); END; / © Ellis Cohen 2001 -2008 60

IN OUT Parameters SQL> CREATE OR REPLACE PROCEDURE Swap. Sal( an. Empno int, the. Sal IN OUT number ) IS oldsal number; BEGIN IN OUT SELECT sal INTO oldsal FROM Emps parameters WHERE empno = an. Empno; UPDATE Emps SET sal = the. Sal • pass values WHERE empno = an. Empno; to the procedure the. Sal : = oldsal; when it is END; called / • pass values back to the SQL> DECLARE caller when mysal number : = 1000; the procedure BEGIN finishes Swap. Sal( 3142, mysal ); pl( 'Previous sal was ' || mysal ); END; / © Ellis Cohen 2001 -2008 61

Stored Packages © Ellis Cohen 2001 -2008 62

Role of Packages A package is used to group together a cohesive set of stored database operations (procedures and functions) • They all provide related functionality, and/or • They use the same set of variables/tables/views © Ellis Cohen 2001 -2008 63

Package Descriptions SQL> CREATE OR REPLACE PACKAGE My. Util AS FUNCTION get. Date RETURN date; FUNCTION get. Num. Columns( tblnam varchar ) RETURN int; END My. Util; This only describes the operations in the package; not their implementation. That's done separately in the package body. Invoking packaged functions SQL> execute pl( My. Util. get. Date() ) SQL> execute pl( My. Util. get. Num. Columns( 'Emps' ) ) SQL> SELECT * FROM Projs WHERE pstart > My. Util. get. Date; © Ellis Cohen 2001 -2008 64

Package Body SQL> CREATE OR REPLACE PACKAGE BODY My. Util AS FUNCTION get. Date RETURN date IS date; BEGIN SELECT sysdate INTO dat FROM dual; RETURN dat; END; FUNCTION get. Num. Columns( tblnam varchar ) RETURN int IS numcols int; BEGIN SELECT count(*) INTO numcols FROM user_tab_columns WHERE table_name = upper(tblnam); RETURN numcols; END My. Util; © Ellis Cohen 2001 -2008 65

Table-Based Packages A package of operations dealing with the Emps table PACKAGE Emp. Pkg AS PROCEDURE Change. Sal( an. Empno int, a. Sal number ); -- Changes the salary of an employee PROCEDURE Change. Job( an. Empno int, a. Job varchar ); -- Changes the job of some other employee PROCEDURE Change. Mgr( an. Empno int, a. Mgr int ); -- Changes mgr of an employee PROCEDURE Change. Position( an. Empno int, a. Mgr int, a. Deptno int, a. Job varchar ); -- Changes one or more of job/dept/mgr of employee PROCEDURE Add. Emp( an. Empno int, an. Ename varchar, a. Sal number, a. Job varchar, a. Mgr int, a. Deptno int ); -- Adds an employee to a department PROCEDURE Terminate. Emp( an. Empno int ); -- Terminates an employee END Emp. Pkg; It can also be useful for a package to encapsulate the procedures & functions for a group of related tables © Ellis Cohen 2001 -2008 66

Table-Based Package Body PACKAGE BODY Emp. Pkg AS … PROCEDURE Change. Position( an. Empno int, a. Mgr int, a. Deptno int, a. Job varchar ) IS BEGIN UPDATE Emps SET mgr = a. Mgr, deptno = a. Deptno, job = a. Job WHERE empno = an. Empno; END; ---------- PROCEDURE Add. Emp( an. Empno int, an. Ename varchar, a. Sal number, a. Job varchar, a. Mgr int, a. Deptno int ) IS BEGIN INSERT INTO Emps( empno, ename, sal, job, mgr, deptno ) VALUES ( an. Empno, an. Ename, a. Sal, a. Job, a. Mgr, a. Deptno ); END; ---------- PROCEDURE Terminate. Emp( an. Empno int ) IS BEGIN DELETE FROM Emps WHERE empno = an. Empno; END Emp. Pkg; © Ellis Cohen 2001 -2008 67

Sequential Values © Ellis Cohen 2001 -2008 68

Hiding Implementation Details Given a relational model with Asns asnid empno hrs int primary key int references Emps int references Projs int We would like to write a procedure Add. Assignment( empno, hrs ) which would add a new tuple to Asns but automatically fill in asnid © Ellis Cohen 2001 -2008 69

Hiding Id Assignment CREATE SEQUENCE asnseq START WITH 1000; -- Defines a sequence used to generate -- sequential values. -- Can specify start value and increment CREATE OR REPLACE PROCEDURE Add. Assignment( an. Empno int, a. Pno int, the. Hrs number ) IS BEGIN The first value generated will be 1001 INSERT INTO Asns VALUES( asnseq. nextval, an. Empno, a. Pno, the. Hrs ); END; nextval is a parameterless function which gets the next value from the sequence Suppose you want to return the value as well as insert it? © Ellis Cohen 2001 -2008 70

Returning Assigned Id's CREATE SEQUENCE asnseq START WITH 1001; -- Defines a sequence used to generate -- sequential values. Can specify start value and increment CREATE OR REPLACE PROCEDURE Add. Assignment( an. Empno int, a. Pno int, the. Hrs number, OUT the. Id int ) IS BEGIN SELECT asnseq. nextval INTO the. Id FROM DUAL; -- nextval is an Oracle built-in parameter-less -- function which gets the next value from sequence, -- but can ONLY be called from SQL code -- DUAL is a convenient, -- built-in single row/column table INSERT INTO Asns VALUES( the. Id, an. Empno, a. Pno, the. Hrs ); END; the. Id : = asnseq. nextval DOESN'T WORK! asnseq. nextval must be used in a SQL statement © Ellis Cohen 2001 -2008 71

Using RETURNING INTO CREATE SEQUENCE asnseq START WITH 1001; CREATE OR REPLACE PROCEDURE Add. Assignment( an. Empno int, a. Pno int, the. Hrs number, OUT the. Id int ) IS Associates this value with the asnid attribute BEGIN INSERT INTO Asns VALUES( asnseq. nextval, an. Empno, a. Pno, the. Hrs ) RETURNING asnid INTO the. Id; END; Also, asnseq. currval gets the current value of the sequence, without incrementing it! © Ellis Cohen 2001 -2008 72

Data-Specific Sequences Given a relational model with Entries invid int references Invoices linenum int prodid int qty int primary key ( invid, linenum ) We would like to write a procedure Add. Entry which would add a new tuple to Entries but automatically fill in the next linenum © Ellis Cohen 2001 -2008 73

Computing Next Sequence Number SELECT 1 + nvl(max(linenum), 0) INTO nxtlin FROM Entries WHERE invid = an. Invid; nxtlin will get 1 more than the highest linenum for an. Invid Because this uses an aggregate function (MAX), this SELECT always succeeds and returns a result, even if there are no current entries for an. Invid In that case, MAX returns NULL, and because of nvl, nxtlin will get 1 © Ellis Cohen 2001 -2008 74

Inserting Next Sequence Number CREATE OR REPLACE PROCEDURE Add. Entry( an. Invid int, a. Prodid int, a. Qty int ) IS nxtlin int; BEGIN SELECT 1 + nvl(max(linenum), 0) INTO nxtlin FROM Entries WHERE invid = an. Invid; INSERT INTO Entries VALUES( an. Invid, nxtlin, a. Prodid, a. Qty); END; Later, we'll see how to use Dynamic SQL to define a sequence for each invoice ID. But assuming the number of invoices is large, and the number of lines per invoice is small, it's reasonable to just compute the next linenum © Ellis Cohen 2001 -2008 75

Exceptions © Ellis Cohen 2001 -2008 76

Exceptions An anonymous block ZERO_DIVIDE exception raised if earnings is 0 DECLARE pe_ratio number(5, 1); BEGIN SELECT price / earnings INTO pe_ratio FROM Stocks WHERE symbol = : sym; INSERT INTO Stats( symbol, ratio ) VALUES( : sym, pe_ratio ); END; If an exception is raised • the INSERT is not executed • the exception is reported to the user © Ellis Cohen 2001 -2008 : sym is a middle-tier variable, not a declared local variable 77

Catching ZERO_DIVIDE DECLARE pe_ratio number(5, 1); BEGIN SELECT price / earnings INTO pe_ratio FROM Stocks WHERE symbol = : sym; INSERT INTO Stats( symbol, ratio ) VALUES( : sym, pe_ratio ); EXCEPTION WHEN ZERO_DIVIDE THEN INSERT INTO Stats( symbol, ratio ) VALUES( : sym, NULL ); END; If the exception is raised, control jumps to the EXCEPTION clause, where an exception handler for ZERO_DIVIDE has code for an alternative INSERT statement which is executed instead © Ellis Cohen 2001 -2008 78

Discarding All Other Exceptions Suppose some other kind of error is raised while executing this code DECLARE pe_ratio number(3, 1); BEGIN SELECT price / earnings INTO pe_ratio FROM Stocks WHERE symbol = : sym; INSERT INTO Stats( symbol, ratio ) VALUES( : sym, pe_ratio ); EXCEPTION WHEN ZERO_DIVIDE THEN INSERT INTO Stats( symbol, ratio ) VALUES( : sym, NULL ); WHEN OTHERS THEN NULL; END; WHEN OTHERS catches all other exceptions NULL does nothing (in response) © Ellis Cohen 2001 -2008 79

Raise Application Error DECLARE pe_ratio number(3, 1); BEGIN SELECT price / earnings INTO pe_ratio FROM Stocks WHERE symbol = : sym; INSERT INTO Stats( symbol, ratio ) VALUES( : sym, pe_ratio ); EXCEPTION WHEN ZERO_DIVIDE THEN INSERT INTO Stats( symbol, ratio ) VALUES( : sym, NULL ); WHEN OTHERS THEN RAISE_APPLICATION_ERROR( -20069, 'Couldn''t Calculate PE for ' || : sym ); END; Reports a more descriptive error Note: the error number must be in the range -20000 to -20999 © Ellis Cohen 2001 -2008 80

Propagating Exceptions BEGIN Do. Something(); EXCEPTION WHEN OTHERS THEN pl( 'Problem doing something' ); END; PROCEDURE Do. Something() IS BEGIN … RAISE_APPLICATION_ERROR( -20069, 'Some problem encountered' ); … END Exceptions not caught within a procedure/function are propagated to the site where it was called © Ellis Cohen 2001 -2008 81

Exceptional Insert/Update Attempting to insert a row into a table will raise an exception if another tuple in that table has the same primary key value Use that information to write an anonymous PL/SQL block that does the following without using SELECT, MERGE, or SQL%ROWCOUNT or RETURNING: if a project does not exist with project # 30420, INSERT it with pname: 'My Project', and pmgr: 2020 else if it already exists, UPDATE it with pname: 'Your Project', and pmgr: 2020 © Ellis Cohen 2001 -2008 82

Exceptional Insert/Update Answer BEGIN INSERT INTO Projs( pno, pname, pmgr ) VALUES( 30420, 'My Project', 2020 ) EXCEPTION WHEN OTHERS THEN UPDATE Projs SET pname = 'Your Project', pmgr = 2020 WHERE pno = 30420; END; Although using SQL%ROWCOUNT is generally more efficient © Ellis Cohen 2001 -2008 83

Reraising Exceptions PROCEDURE Do. Something IS BEGIN -- Do something complicated -- which could raise an exception EXCEPTION WHEN OTHERS THEN IF … THEN -- under some circumstances, -- we can simply -- do something else ELSE -- but in other circumstances -- we propagate the exception -- back to the caller RAISE; END IF; END; © Ellis Cohen 2001 -2008 84

Non-Existence Exceptions If there is no employee with empno an. Empno, raise an exception BEGIN SELECT count(*) INTO knt FROM Emps WHERE empno = an. Empno; IF knt = 0 THEN RAISE_APPLICATION_ERROR( … ); END IF; Equivalent result; END; same performance (since at most one such employee) BEGIN SELECT empno INTO the. Empno FROM Emps WHERE empno = an. Empno; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR( … ); END; © Ellis Cohen 2001 -2008 85

Record Types © Ellis Cohen 2001 -2008 86

Record Types DECLARE TYPE Name. Dept. Rec IS RECORD ( ename varchar(30), deptno int ); a. Rec Name. Dept. Rec; BEGIN SELECT ename, deptno INTO a. Rec. ename, a. Rec. deptno FROM Emps WHERE empno = 7876; INSERT INTO Cool. Emps( ename, deptno ) VALUES( a. Rec. ename, a. Rec. deptno ); END; © Ellis Cohen 2001 -2008 87

Record Based Select/Insert DECLARE TYPE Name. Dept. Rec IS RECORD ( ename varchar(30), deptno int ); a. Rec Name. Dept. Rec; BEGIN SELECT ename, deptno INTO a. Rec FROM Emps WHERE empno = 7876; INSERT INTO Cool. Emps( ename, deptno ) VALUES a. Rec; END; match based on position, not name © Ellis Cohen 2001 -2008 88

Using ROWTYPE You can declare a record whose type corresponds to a row of a table or view DECLARE a. Rec Cool. Emps%ROWTYPE; BEGIN SELECT ename, deptno INTO a. Rec FROM Emps WHERE empno = 7876; INSERT INTO Cool. Emps( ename, deptno ) VALUES a. Rec; END; © Ellis Cohen 2001 -2008 89

Pretty Printing Tuples SELECT ename, street, city, state, zip FROM Emps WHERE empno = 7876; DECLARE a. Rec Emps%ROWTYPE; BEGIN SELECT * INTO a. Rec FROM Emps WHERE empno = 7876; pl( a. Rec. ename ); pl( a. Rec. street ); pl( a. Rec. city || ', ' || a. Rec. state || ', ' || a. Rec. zip ); pl( '' ); END; © Ellis Cohen 2001 -2008 90

Record Based Update PROCEDURE Include. Project( p. Rec Projs%ROWTYPE ) IS BEGIN INSERT INTO Projs VALUES p. Rec; EXCEPTION Built-in; a record with that WHEN DUP_VAL_ON_INDEX index is already in the table THEN UPDATE Projs SET ROW = p. Rec WHERE pno = p. Rec. pno; END; © Ellis Cohen 2001 -2008 91

Query-Based FOR Loops © Ellis Cohen 2001 -2008 92

Query-Based FOR Loops FOR i IN 1. . 10 LOOP … END LOOP loops through the numbers from 1. . 10 FOR rec IN (SELECT …) LOOP … END LOOP loops through the tuples resulting from the SELECT BEGIN pl( ' Name Dept#'); Query-Based FOR pl('---------------'); Loops automatically declare the loop FOR erec IN variable to be (SELECT ename, deptno consistent with the FROM Emps tuples in the result set WHERE job = 'ANALYST') LOOP pl( rpad( erec. ename, 15) || erec. deptno ); END LOOP; END; Very similar to SQL*Plus display of the bare SELECT © Ellis Cohen 2001 -2008 93

Queries as Loops CREATE TABLE Results AS SELECT empno, ename FROM Emps e WHERE sal > 1500 CREATE TABLE Results (empno int, ename varchar(30)); BEGIN FOR erec IN (SELECT * FROM Emps WHERE sal > 1500) LOOP INSERT INTO Results VALUES ( erec. empno, erec. ename ); END LOOP; END; Don't fill a table this way! We're just using PL/SQL as a way of describing how queries work © Ellis Cohen 2001 -2008 94

Joins as Nested Loops CREATE TABLE Results AS SELECT ename, dname FROM Emps e, Depts d WHERE e. deptno = d. deptno AND e. sal > 1500 CREATE TABLE Results (ename varchar(30), dname varchar(16)); BEGIN FOR drec IN (SELECT * FROM Depts) LOOP FOR erec IN (SELECT * FROM Emps WHERE deptno = drec. deptno AND sal > 1500) LOOP INSERT INTO Results VALUES( erec. ename, drec. dname ); END LOOP; Don't fill a table this way! END; We're just using PL/SQL as a way of describing how queries work © Ellis Cohen 2001 -2008 95

Bare SELECT Problem BEGIN Do. Something(); SELECT empno, ename FROM Emps WHERE job = 'CLERK'; END; This anonymous block is illegal Why? Write code that works, still written as a single anonymous block © Ellis Cohen 2001 -2008 96

Eliminate Bare SELECTs BEGIN Do. Something(); FOR erec IN (SELECT empno, ename FROM Emps WHERE job = 'CLERK') LOOP pl( erec. empno || ': ' || erec. ename ); END LOOP; END; Can't use a bare SELECT as a PL/SQL statement. Can use a Query For Loop instead © Ellis Cohen 2001 -2008 97

List Employees in Each Dept ACCOUNTING: CLARK, KING, MILLER OPERATIONS RESEARCH: SMITH, JONES, SCOTT, ADAMS, FORD SALES: ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES DECLARE fstr varchar(200); sep varchar(5); BEGIN FOR drec IN (SELECT deptno, dname FROM Depts ORDER BY dname) LOOP fstr : = drec. dname; sep : = ': '; FOR erec IN (SELECT ename FROM Emps WHERE deptno = drec. deptno) LOOP fstr : = fstr || sep || erec. ename; sep : = ', '; END LOOP; pl( fstr ); END LOOP; END; © Ellis Cohen 2001 -2008 98

Existence Exceptions Raise an error if dept a. Deptno has any employees BEGIN SELECT count(*) INTO knt FROM Emps WHERE deptno = a. Deptno; IF knt > 0 THEN RAISE_APPLICATION_ERROR( … ); END IF; END; FOR erec IN ( SELECT empno FROM Emps WHERE deptno = a. Deptno) LOOP RAISE_APPLICATION_ERROR( … ); END LOOP; This code is more efficient if there are many such employees, and there is no index on deptno, since it can quit when the first such employee is found, rather than after counting all of them © Ellis Cohen 2001 -2008 99

Unnecessary Looped UPDATEs Increase salary of all project managers FOR prec IN (SELECT DISTINCT pmgr FROM Projects) LOOP UPDATE Emps SET sal = sal + 100 WHERE empno = prec. pmgr; END LOOP; UPDATE Emps SET sal = sal + 100 WHERE empno IN (SELECT pmgr FROM Projects) This code is much more efficient In general, avoid loops when equivalent code can be written without loops © Ellis Cohen 2001 -2008 100

Unnecessary Looped SELECTs Raise an error if some dept has no employees DECLARE knt int; BEGIN FOR drec IN ( SELECT deptno FROM Depts) LOOP SELECT count(*) INTO knt FROM Emps WHERE deptno = drec. deptno; IF knt = 0 THEN RAISE_APPLICATION_ERROR( -20023, 'There is a dept with no employees' ); END LOOP; END; The inner SELECT statement can be avoided by using a more complicated outer SELECT © Ellis Cohen 2001 -2008 101

Handling Multiple Errors Raise an error if some dept has no employees CREATE VIEW Dept. Knts. View AS SELECT deptno, count(empno) AS eknt FROM Depts NATURAL LEFT JOIN Emps GROUP BY deptno; BEGIN FOR drec IN ( SELECT deptno FROM Dept. Knts. View WHERE eknt = 0) LOOP RAISE_APPLICATION_ERROR( -20023, 'There is a dept with no employees' ); END LOOP; END; This code works, but suppose we want to indicate exactly which departments don't have employees. What can we do? © Ellis Cohen 2001 -2008 102

Identifying the First Error Raise an error if some dept has no employees CREATE VIEW Dept. Knts. View AS SELECT deptno, count(empno) AS eknt FROM Depts NATURAL LEFT JOIN Emps GROUP BY deptno; BEGIN FOR drec IN ( SELECT deptno FROM Dept. Knts. View WHERE eknt = 0) LOOP RAISE_APPLICATION_ERROR( -20023, 'Dept ' || drec. deptno || ' has no employees' ); END LOOP; END; This only identifies the first such department. Suppose we want to identify all of them? © Ellis Cohen 2001 -2008 103

Building Informative Error Messages Identify all depts which have no employees DECLARE empties varchar(200) : = ''; sep varchar(2) : = ''; BEGIN FOR drec IN ( SELECT deptno FROM Dept. Knts. View WHERE eknt = 0) LOOP empties : = empties || sep || drec. deptno; sep : = ', '; END LOOP; IF length(empties) > 0 THEN RAISE_APPLICATION_ERROR( -20023, 'Depts without employees: ' || empties ); END IF; END; © Ellis Cohen 2001 -2008 104

Dynamic SQL © Ellis Cohen 2001 -2008 105

Dynamic SQL PROCEDURE Insert. Project( a. Pno number, a. Pname varchar, a. Pmgr number ) IS BEGIN INSERT INTO Projs( pno, pname, pmgr ) VALUES( a. Pno, a. Pname, a. Pmgr ); END; PROCEDURE Insert. Project( a. Pno number, a. Pname varchar, a. Pmgr number ) IS Dynamically construct & sqlstr varchar(100); execute a SQL command! BEGIN sqlstr : = 'INSERT INTO Projs( pno, pname, pmgr ) ' || ' VALUES( ' || a. Pno || ', ' || quote(a. Pname) || ', ' || a. Pmgr || ' )'; EXECUTE IMMEDIATE sqlstr; END; © Ellis Cohen 2001 -2008 106

Runtime Table Names & Conditions PROCEDURE Delete. Rows( table_name varchar, condition varchar ) IS where_clause varchar(100); BEGIN IF condition IS NOT NULL THEN where_clause : = ' WHERE ' || condition; END IF; EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause; END; EXECUTE IMMEDIATE works with PL/SQL blocks as well as SQL statements! © Ellis Cohen 2001 -2008 107

Runtime DDL PROCEDURE Drop. Table( tbl varchar ) IS sqlstr varchar(99) : = 'drop table ' || tbl; BEGIN EXECUTE IMMEDIATE sqlstr; EXCEPTION WHEN OTHERS THEN NULL; END; Prevents error message if table doesn't exist. DDL statements (CREATE, DROP, …) cannot be executed inside of PL/SQL except via Dynamic SQL © Ellis Cohen 2001 -2008 108

Creating Sequences Dynamically CREATE OR REPLACE PROCEDURE Add. Entry( an. Invid int, a. Prodid int, a. Qty int ) IS seqstr varchar(20) : = 'Inv. Seq' + an. Invid; insstr varchar(100) : = 'INSERT INTO Entries VALUES (' || 'an. Invid, ' || seqstr || '. nextval, a. Prodid, a. Qty)'; BEGIN EXECUTE IMMEDIATE insstr; EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || seqstr; EXECUTE IMMEDIATE insstr; END; Dynamically creates a sequence for an invoice if the sequence doesn’t already exist (causing an exception). For invoice 347, the sequence will be named Inv. Seq 347 © Ellis Cohen 2001 -2008 109

Execute Immediate INTO FUNCTION Select. Employee( condition in varchar ) RETURN Emps. empno%TYPE IS sqlstr varchar(200); an. Emps. empno%TYPE; BEGIN sqlstr : = 'SELECT FROM ' || table_name || ' WHERE ' || condition; EXECUTE IMMEDIATE sqlstr INTO an. Emp; RETURN an. Emp; END; Allows SQL query to be reused with different INTO clauses © Ellis Cohen 2001 -2008 110

Parameterized Dynamic SQL PROCEDURE Insert. Project( a. Pno number, a. Pname varchar, a. Pmgr number ) IS BEGIN INSERT INTO Projs( pno, pname, pmgr ) VALUES( a. Pno, a. Pname, a. Pmgr ); END; PROCEDURE Insert. Project( a. Pno number, a. Pname varchar, a. Pmgr number ) IS sqlstr varchar(100); BEGIN Using numbered bind parameters sqlstr : = 'INSERT INTO Projs( pno, pname, pmgr ) ' || ' VALUES( : 1, : 2, : 3 )' EXECUTE IMMEDIATE sqlstr USING a. Pno, a. Pname, a. Pmgr; END; © Ellis Cohen 2001 -2008 111