Pl/sql: Procedural Language Extension To Sql

IBM Global Services

IBM Global Services India

PL/SQL Procedural Language Extension to SQL

© Copyright IBM Corporation 2007 2002

16/07/2007 IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Today’s Topics  Oracle Architecture and Overview  Error Handling  Stored Procedures 

Procedure



Function



Package

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL - Introduction  Procedural extension allowing for modularity, variable declaration, loops and logical constructs. Allows for advanced error handling Communicates natively with other oracle database objects. Managed centrally within the Oracle database. PL/SQL Blocks of code can be embedded in host languages Using PL/SQL, you can define and execute PL/SQL procedures, functions and packages PL/SQL engine executes PL/SQL blocks of code

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL – Why Use Manage business rules – through middle layer application logic. Generate code for triggers Generate code for interface Enable database-centric client/server applications Execution privileges can be better managed

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL Engine PL/SQL Engine PL/SQL block

Procedural Procedural Statement Statement Executor

PL/SQL block SQL

SQL Statement Executor

Oracle Server IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL Structure

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL Block Type

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL Block Type DECLARE v_item_code VARCHAR2(6); v_item_descr VARCHAR2(20); BEGIN v_item_code := 'ITM101'; v_item_descr := 'Spare parts'; INSERT INTO items_tab VALUES (v_item_code, v_item_descr); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM); END; To make this procedure work, the SQL*Plus environment variable SERVEROUTPUT should be set to ON. Here's the code to do this in SQL*Plus: SQL> set serveroutput on; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL Data Type

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

PL/SQL Control Structures  Conditional Control: IF and CASE Statements  Iterative Control: LOOP and EXIT Statements - simple or infinite loop - FOR loop - WHILE loop  Sequential Control: GOTO and NULL Statements

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

IF Constructor IF age >= 65 THEN

IF THEN

INSERT INTO retired

....

VALUES(name, age, lodging);

END IF;

ELSIF age <18 THEN

IF THEN

INSERT INTO underage

.... [ELSIF THEN] ......

VALUES(name, age, lodging); ELSE

[ELSE]

INSERT INTO workforce

...

VALUES(name, age, lodging);

END IF;

END IF;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

CASE Statement CASE grade WHEN ’A’ THEN dbms_output.put_line(’Excellent’); WHEN ’B’ THEN dbms_output.put_line(’Very Good’); WHEN ’C’ THEN dbms_output.put_line(’Good’); WHEN ’D’ THEN dbms_output.put_line(’Fair’); WHEN ’F’ THEN dbms_output.put_line(’Poor’); ELSE dbms_output.put_line(’No such grade’); END CASE;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

LOOP … END LOOP LOOP rem_balance := account_balance(accno); IF rem_balance < 100

THEN

EXIT; ELSE decrement_balance(accno,withdraw_amt); END IF; END LOOP;  Can also use EXIT WHEN to conditionally exit from a loop  If you don’t specify an exit condition, it becomes an infinite loop

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

FOR Loop 1. FOR

even_number IN 1..50

LOOP calc_values(even_number * 2); END LOOP; 2.

FOR even_number IN REVERSE 1..10 LOOP calc_values(even_number*2); END LOOP;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

WHILE Loops DECLARE TEN number:=10; i number_table.num%TYPE:=1; BEGIN WHILE i <= TEN LOOP INSERT INTO number_table VALUES(i); i := i + 1; END LOOP; END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

GOTO Statement  To unconditionally transfer control.  GOTO  At least one statement must follow a label  You cannot transfer into an IF construct  You cannot go to the middle of a LOOP  You cannot transfer control from an executable section to an exception and vice versa  You cannot use GOTO to enter a block

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

What are CURSOR ???  Cursors are memory areas that allow you to allocate an area of memory and access the information retrieved from a SQL statement.  A cursor is a symbolic name associated with a select statement.  Cursors are special programming constructs that allow data to be manipulated on a row-by-row basis.  You can think of a cursor as a pointer into a table in the database. IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Types of Cursors  Implicit Cursors  Get created automatically every time you use an INSERT, UPDATE, DELETE or SELECT command  Doesn’t need to be declared.  Symbolic Name is SQL

 Explicit Cursors  Needs to be explicitly created  Symbolic Name is user-given.  Used if query returns more than one record.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Implicit Cursor Example DECLARE v_sname VARCHAR2(10); v_rating NUMBER(3); BEGIN SELECT sname, rating INTO v_sname, v_rating FROM Sailors WHERE sid = '112'; END; / • INTO clause is required. • Query must return exactly one row. • Otherwise, a NO_DATA_FOUND or TOO_MANY_ROWS exception is thrown IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Implicit Cursor Attributes  Return information about the execution of the last INSERT, UPDATE, DELETE, or SELECT INTO statement. It has 4 useful attributes

SQL%ROWCOUNT

Number of rows affected by the most recent SQL statement (an integer value).

SQL%FOUND

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

SQL%NOTFOUND

Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any rows.

SQL%ISOPEN

Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Implementation of %rowcount SQL> begin 2 update emp1 3 set sal = sal + 500 4 where empno=&emp_no; 5 if sql%rowcount = 1 then 6 dbms_output.put_line('Employee Successfully Updated'); 7 end if; 8 end; 9 / Enter value for emp_no: 8003 old 4: where empno=&emp_no; new 4: where empno=8003; Employee Successfully Updated PL/SQL procedure successfully completed.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Another Implementation 1 declare 2 vsal number; 3 begin 4 select sal into vsal 5 from emp1 6 where empno=&emp_no; 7 if sql%found then 8 dbms_output.put_line('Employee Successfully Found'); 9 else 10 dbms_output.put_line('Employee Not Successfully Found'); 11 end if; 12 exception 13 when no_data_found then 14 dbms_output.put_line('Employee Not Found'); 15* end; 16 / Enter value for emp_no: 8009 old 6: where empno=&emp_no; new 6: where empno=8009; Employee Not Found

PL/SQL procedure successfully completed.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Explicit Cursor Manipulation The process of working with an cursor consists of the following steps:  Declaring the cursor. This initializes the cursor into memory.  Opening the cursor. The previously declared cursor can now be opened; memory is allotted.  Fetching the cursor. Previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor.  Closing the cursor. Previously declared, opened, and fetched cursor must now be closed to release memory allocation IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Declaring Cursor  Declaring a cursor defines the name of the cursor and associates it with a SELECT statement. Syntax: CURSOR <cursor_name> IS select statement e.g. Cursor c_myCursor is Select emp.name, emp.dept from employee where salary > 5000; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Opening a Cursor When the Open cursor statement is processed, the following four actions will take place automatically: 1. The variables (including bind variables) in the WHERE clause are examined. 2. Based on the values of the variables, the active set is determined and the PL/SQL engine executes the query for that cursor. Variables are examined at cursor open time only. 3. The PL/SQL engine identifies the active set of data—the rows from all involved tables that meet the WHERE clause criteria. 4. The active set pointer is set to the first row.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Fetching Records After the cursor has been declared and opened, you can then retrieve data from the cursor. The process of getting the data from the cursor is referred to as fetching the cursor. There are two methods of fetching a cursor, done with the following command:

1. FETCH cursor_name INTO PL/SQL variables; OR 3. FETCH cursor_name INTO PL/SQL record;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Explicit Cursor Attributes <cursor_name>

%NOTFOUND

A Boolean attribute that returns TRUE if the previous FETCH did not return a row, and FALSE if it did.

<cursor_name>

%FOUND

A Boolean attribute that returns TRUE if the previous FETCH returned a row, and FALSE if it did not

<cursor_name>

%ROWCOUNT

Number of records fetched from a cursor at that point in time.

<cursor_name>

%ISOPEN

A Boolean attribute that returns TRUE if cursor is open, FALSE if it is not.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Closing Cursor  Once all of the rows in the cursor have been processed (retrieved), the cursor should be closed.  This tells the PL/SQL engine that the program is finished with the cursor, and the resources associated with it can be freed. The syntax for closing the cursor is CLOSE cursor_name;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Putting it all Together… declare --DECLARE CURSOR cursor trial_cursor IS Select emp_name, salary from employee; myname employee.emp_name%type; mysal employee.salary%type; BEGIN if not trial_cursor%isopen then open trial_cursor; --OPEN CURSOR end if; loop --FETCH From CURSOR into VARIALBLES fetch trial_cursor into myname, mysal; dbms_output.put_line(myname || ' ' || mysal); Exit when trial_cursor%NOTFOUND = true; end loop; dbms_output.put_line('No.of rows in Cursor are: ' || trial_cursor%rowcount); close trial_cursor; --CLOSING CURSOR End; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Cursor with For Loop  Automatically opens the cursor, fetches the records, then closes the cursor  FOR variable_name(s) IN cursor_name LOOP processing commands END LOOP;  Cursor variables cannot be used outside loop

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

An Example declare cursor c_emp_cursor IS Select * from employee; BEGIN FOR myrow IN c_emp_cursor LOOP

--NO OPEN

dbms_output.put_line(myrow.Emp_Name || ' ' || myrow.Salary); --NO FETCH

--NO CLOSE

end loop;

dbms_output.put_line('All operations performed Implicitly'); End;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Using Subqueries Instead of Explicit Cursors You need not declare a cursor because PL/SQL lets you substitute a subquery. The following cursor FOR loop calculates a bonus, then inserts the result into a database table: DECLARE bonus REAL; BEGIN FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25); INSERT INTO bonuses VALUES (emp_rec.empno, bonus); END LOOP; COMMIT; END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Passing Parameters to Explicit Cursors To Pull different sets of records in the same cursor, one can pass parameter to the cursor at the time of opening. An explicit cursor can take parameters and return a data set for a specific parameter value. This eliminates the need to define multiple cursors and hard-code a value in each cursor. DECLARE CURSOR emp_cursor(dnum NUMBER) IS SELECT sal, comm FROM emp WHERE deptno = dnum; total_wages NUMBER(11,2) := 0; high_paid NUMBER(4) := 0; higher_comm NUMBER(4) := 0; BEGIN /* the number of iterations will equal the number of rows * * returned by emp_cursor */ FOR emp_record IN emp_cursor(20) LOOP emp_record.comm := NVL(emp_record.comm,0); total_wages := total_wages + emp_record.sal + emp_record.comm; IF emp_record.sal > 2000 THEN high_paid := high_paid + 1; END IF; END LOOP; INSERT INTO temp VALUES (high_paid, higher_comm, 'Total Wages: ' || TO_CHAR(total_wages)); COMMIT; END; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

SELECT FOR UPDATE Cursors You use SELECT FOR UPDATE cursors for updating the rows retrieved by a cursor. This is often required when there’s a need to modify each row retrieved by a cursor without having to re-fetch that row.

Defining a SELECT FOR UPDATE Cursor

DECLARE CURSOR csr_1 IS SELECT * FROM sec_hrc_tab FOR UPDATE OF hrc_descr; BEGIN /* . . . Open the cursor and process the resultset . . . */ END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Using a SELECT FOR UPDATE Cursor

Once you’ve defined a SELECT FOR UPDATE cursor, you use the WHERE CURRENT OF clause to process the rows returned by it. You can use this clause in an UPDATE or DELETE statement. DECLARE CURSOR csr_1 IS SELECT * FROM sec_hrc_tab FOR UPDATE OF hrc_descr; v_hrc_descr VARCHAR2(20); BEGIN FOR idx IN csr_1 LOOP v_hrc_descr := UPPER(idx.hrc_descr); UPDATE sec_hrc_tab SET hrc_descr = v_hrc_descr WHERE CURRENT OF csr_1; END LOOP; COMMIT; END; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Cursor Variables  An explicit cursor once declared is associated with a specific query—only the one specific query that was known at compile time. In this way, the cursor declared was static and couldn’t be changed at runtime.  A cursor variable is a single PL/SQL variable that you can associate with different queries at runtime. Why Use Cursor Variables?

The primary advantage of using cursor variables is their capability to pass resultsets between stored subprograms. Before cursor variables, this wasn’t possible. Now, with cursor variables, the work area that a cursor variable points to remains accessible as long as the variable points to it. Another advantage of cursor variables is their introduction of a sort of dynamism, in that a single cursor variable can be associated with multiple queries at runtime.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Defining a Cursor Variable

 Defining a cursor variable consists of defining a pointer of type REF CURSOR and defining a variable of this type. First it involves the definition of a REF CURSOR first, as shown here: TYPE rc IS REF CURSOR; The next step is to declare a variable of this type. Here’s the code for this: v_rc rc; So the complete declaration of a cursor variable is as follows: TYPE rc IS REF CURSOR;   v_rc rc; This code suggests that rc is a pointer of type CURSOR and v_rc (in fact, any variable) defined of type rc points to a SQL cursor.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

A Complete Example DECLARE TYPE rc is REF CURSOR; v_rc rc; hrc_rec hrc_tab%ROWTYPE; BEGIN OPEN v_rc FOR SELECT * from hrc_tab; dbms_output.put_line(‘Hierarchy Details’); dbms_output.put_line(‘------------------------’); dbms_output.put_line(‘Code’||’ ‘||rpad(‘Description’,20,’ ‘)); dbms_output.put_line(rpad(‘-’,4,’-’)||’ ‘||rpad(‘-’,20,’-’)); LOOP FETCH v_rc INTO hrc_rec; EXIT WHEN v_rc%NOTFOUND; dbms_output.put_line(to_char(hrc_rec.hrc_code)||’ ‘|| rpad(hrc_rec.hrc_descr,20,’ ‘)); END LOOP; CLOSE v_rc; END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Strong and Weak REF CURSOR Types

 The Ref Cursor variable which doesn’t dictate the return type of the cursor is a WEAK Ref Cursor Type. For eg TYPE rc IS REF CURSOR; v_rc rc; Hence, it can point to any SELECT query with any number of columns.

 A strong REF CURSOR type definition specifies a return type. TYPE rc is REF CURSOR RETURN hrc_tab%ROWTYPE; v_rc rc;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Weak REF CURSOR Example DECLARE TYPE tcursor IS REF CURSOR; tcur tcursor; e1 emp%ROWTYPE; d1 dept%ROWTYPE; tname VARCHAR2(20); BEGIN tname := &tablename; IF tname = 'emp' THEN OPEN tcur FOR SELECT * FORM emp; DBMS_OUTPUT.PUT_LINE ('Emp table opened.'); close tcur; DBMS_OUTPUT.PUT_LINE ('Emp table closed.'); ELSE IF tname = 'dept' THEN OPEN tcur FOR SELECT * FROM dept; DBMS_OUTPUT.PUT_LINE ('Dept table opened.'); close tcur; DBMS_OUTPUT.PUT_LINE ('Emp table closed.'); ELSE RAISE_APPLICATION_ERROR (-20004, 'Table name is wrong'); END IF; END; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Strong REF CURSOR Example

DECLARE TYPE ecursor IS REF CURSOR RETURN emp%ROWTYPE; ecur ecursor; e_rec emp%ROWTYPE; dn NUMBER; BEGIN dn := &deptno; OPEN ecur FOR SELECT * FROM emp WHERE deptno = dn; FOR e_rec IN ecur LOOP DBMS_OUTPUT.PUT_LINE ('Employee No : ' || e_rec.empno); DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || e_rec.salary); END LOOP; END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating Procedures 

A procedure is a named PL/SQL block that performs an action.

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

CREATE [OR REPLACE] PROCEDURE procedure_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .) IS [AS] PL/SQL Block;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Developing Stored Procedures Oracle Procedure Builder

Edit

System editor

1

Text file Store in database Source code Compile

2

parsed-code Execute IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating a Stored Procedure Using SQL*Plus 1. Enter the text of the CREATE PROCEDURE statement in a system editor or word processor and save it as a script file (.sql extension). 1. From SQL*Plus, run the script file to compile the source code into parsed-code and store both in the database. 1. Invoke the procedure from an Oracle Server environment to determine whether it executes without error.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Stored Procedures : Example

SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 / Procedure created. SQL> EXECUTE raise_salary (7369) PL/SQL procedure successfully completed.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Invoking a Procedure from an Anonymous PL/SQL Block

DECLARE v_id NUMBER := 7900; BEGIN raise_salary(v_id); COMMIT; ...

--invoke procedure

END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Invoking a Procedure from a Stored Procedure

SQL> CREATE OR REPLACE PROCEDURE process_emps 2 IS 3 CURSOR emp_cursor IS 4 SELECT empno 5 FROM emp; 6 BEGIN 7 FOR emp_rec IN emp_cursor LOOP 8 raise_salary(emp_rec.empno); --invoke procedure 9 END LOOP; 10 COMMIT; 11 END process_emps; 12 /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Procedural Parameter Modes Procedure

Calling environment

IN parameter OUT parameter IN OUT parameter (DECLARE) BEGIN EXCEPTION END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Parameter Modes for Formal Parameters IN

OUT

IN OUT

Default

Must be specified

Must be specified

Value is passed into subprogram

Returned to calling environment

Passed into subprogram; returned to calling environment

Formal parameter acts as a constant

Uninitialized variable Initialized variable

Actual parameter can be a literal, expression, constant, or initialized variable

Must be a variable

IGSI Confidential

|

Wednesday, August 5, 2009

Must be a variable

© Copyright IBM Corporation 2003

IBM Global Services

IN Parameters: Example 7369

v_id

SQL> CREATE OR REPLACE PROCEDURE raise_salary 2 (v_id in emp.empno%TYPE) 3 IS 4 BEGIN 5 UPDATE emp 6 SET sal = sal * 1.10 7 WHERE empno = v_id; 8 END raise_salary; 9 / Procedure created. SQL> EXECUTE raise_salary (7369) PL/SQL procedure successfully completed. IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

OUT Parameters: Example Calling environment

QUERY_EMP procedure

7654

v_id MARTIN v_name

IGSI Confidential

|

Wednesday, August 5, 2009

1250

v_salary

1400

v_ comm

© Copyright IBM Corporation 2003

IBM Global Services

OUT Parameters: Example SQL> 1 2 3 4 5 6 7 8 9 10 11 12

CREATE OR REPLACE PROCEDURE query_emp (v_id IN emp.empno%TYPE, v_name OUT emp.ename%TYPE, v_salary OUT emp.sal%TYPE, v_comm OUT emp.comm%TYPE) IS BEGIN SELECT ename, sal, comm INTO v_name, v_salary, v_comm FROM emp WHERE empno = v_id; END query_emp; /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

OUT Parameters and SQL*Plus SQL> START emp_query.sql Procedure created. SQL> VARIABLE g_name SQL> VARIABLE g_salary SQL> VARIABLE g_comm

varchar2(15) number number

SQL> EXECUTE query_emp (7654, :g_name, :g_salary, 2 :g_comm) PL/SQL procedure successfully completed. SQL> PRINT g_name G_NAME --------------MARTIN

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

IN OUT Parameters Calling environment '(800)633-0575'

'(800)633-0575'

SQL> CREATE OR REPLACE PROCEDURE format_phone 2 (v_phone_no IN OUT VARCHAR2) 3 IS 4 BEGIN 5 v_phone_no := '(' || SUBSTR(v_phone_no,1,3) || 6 ')' || SUBSTR(v_phone_no,4,3) || 7 '-' || SUBSTR(v_phone_no,7); 8 END format_phone; 9 /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Invoking FORMAT_PHONE from SQL*Plus SQL>VARIABLE g_phone_no varchar2(15) SQL> BEGIN :g_phone_no := '8006330575'; END; 2 / PL/SQL procedure successfully completed. SQL> EXECUTE format_phone (:g_phone_no) PL/SQL procedure successfully completed. SQL> PRINT g_phone_no G_PHONE_NO --------------(800)633-0575

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Passing Large Data Structures with the NOCOPY Compiler Hint  When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual parameter is passed to the corresponding formal parameter.  By default, the OUT and IN OUT parameters are passed by value.  When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory.  To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. PROCEDURE getEmployees ( ptab_emp_id

OUT NOCOPY

tab_emp_id,

ptab_firstname

OUT NOCOPY

tab_firstname,

ptab_lastname

OUT NOCOPY

tab_lastname )

IS BEGIN ……………… End IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Methods for Passing Parameters  Positional  Named  Combination

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Passing Parameters: Example Procedure SQL> 1 2 3 4 5 6 7 8

CREATE OR REPLACE PROCEDURE add_dept (v_name IN dept.dname%TYPE DEFAULT 'unknown', v_loc IN dept.loc%TYPE DEFAULT 'unknown') IS BEGIN INSERT INTO dept VALUES (dept_deptno.NEXTVAL, v_name, v_loc); END add_dept; /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Examples of Passing Parameters SQL> 2 3 4

begin add_dept; add_dept ( 'TRAINING', 'NEW YORK'); add_dept ( v_loc => 'DALLAS', v_name => 'EDUCATION') ; 5 add_dept ( v_loc => 'BOSTON') ; 6 end; 7 / PL/SQL procedure successfully completed. SQL>

SELECT * FROM dept;

DEPTNO -----... 41 42 43 44

DNAME -------------... unknown TRAINING EDUCATION unknown

IGSI Confidential

|

Wednesday, August 5, 2009

LOC ------------... unknown NEW YORK DALLAS BOSTON © Copyright IBM Corporation 2003

IBM Global Services

Removing Server-Side Procedures  Using SQL*Plus: -Syntax DROP PROCEDURE procedure_name

-Example SQL> DROP PROCEDURE raise_salary; Procedure dropped.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating Functions - A function is a named PL/SQL block that returns a value. - A function can be stored in the database, as a database object, for repeated execution. - A function can be called as part of an expression.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Syntax for Creating Functions CREATE [OR REPLACE] FUNCTION function_name (argument1 [mode1] datatype1, argument2 [mode2] datatype2, . . .) RETURN datatype IS|AS PL/SQL Block;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating a Function Oracle Procedure Builder

Edit

System editor

1

Text file Store in database Source code Compile

2

p-code Execute IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating a Stored Function Using SQL*Plus 

Enter the text of the CREATE FUNCTION statement in a system editor or word processor and save it as a script file (.sql extension).



From SQL*Plus, run the script file to compile the source code into p-code and store both in the database.

•

Invoke the function from an Oracle Server environment to determine whether it executes without error.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating a Stored Function Using SQL*Plus : Example SQL> CREATE OR REPLACE FUNCTION get_sal 2 (v_id IN emp.empno%TYPE) 3 RETURN NUMBER 4 IS 5 v_salary emp.sal%TYPE :=0; 6 BEGIN 7 SELECT sal 8 INTO v_salary 9 FROM emp 10 WHERE empno = v_id; 11 RETURN (v_salary); 12 END get_sal; 13 /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Executing Functions

 Invoke a function as part of a PL/SQL expression.  Create a host variable to hold the returned value.  Execute the function. The host variable will be populated by the RETURN value.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Executing Functions in SQL*Plus: Example 7934

v_id

SQL> START get_salary.sql Procedure created. created SQL> VARIABLE g_salary number SQL> EXECUTE :g_salary := get_sal(7934) PL/SQL procedure successfully completed. SQL> PRINT g_salary G_SALARY -----------------1300

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Locations to Call User-Defined Functions  Select list of a SELECT command  Condition of the WHERE and HAVING clauses  CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses  VALUES clauses of the INSERT command  SET clause of the UPDATE command

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Removing a Server-Side Function  Using SQL*Plus - Syntax DROP FUNCTION function_name

- Example SQL> DROP FUNCTION get_salary; Function dropped.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Comparing Procedures and Functions Procedure

Function

Execute as a PL/SQL statement

Invoke as part of an expression

No RETURN datatype

Must contain a RETURN datatype

Can return zero, one or more values

Must return a value

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Benefits of Stored Procedures and Functions  Improved performance  Improved maintenance  Improved data security and integrity

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating Packages

 Group logically related PL/SQL types, items, and subprograms  Consist of two parts:  Specification  Body  Cannot be called, parameterized, or nested  Allow Oracle8 to read multiple objects into memory at once

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Advantages of Packages

 Modularity  Easier application design  Information hiding  Added functionality  Better performance  Overloading

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Developing a Package 1 Package specification

Procedure A declaration

2 4

Package body

Procedure B definition

3

Procedure A definition

2

5

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating the Package Specification & Body Syntax CREATE [OR REPLACE] PACKAGE package_name IS | AS public type and item declarations subprogram specifications END package_name; CREATE [OR REPLACE] PACKAGE BODY package_name IS | AS private type and item declarations subprogram bodies END package_name;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Public and Private Constructs COMM_PACKAGE package Package specification

Package body

IGSI Confidential

|

G_COMM

1

RESET_COMM procedure declaration

2

VALIDATE_COMM function definition

3

RESET_COMM procedure definition

2

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating a Package Specification: Example SQL>CREATE OR REPLACE PACKAGE comm_package IS 2 g_comm NUMBER := 10; --initialized to 10 3 PROCEDURE reset_comm 4 (v_comm IN NUMBER); 5 END comm_package; 6 /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Creating a Package Body : Example CREATE OR REPLACE PACKAGE BODY comm_package IS FUNCTION validate_comm (v_comm IN NUMBER) RETURN BOOLEAN IS v_max_comm NUMBER; BEGIN SELECT MAX (comm) INTO v_max_comm FROM emp; IF v_comm > v_max_comm THEN RETURN (FALSE); ELSE RETURN (TRUE); END IF; END validate_comm; PROCEDURE reset_comm (v_comm IN NUMBER) IS v_valid BOOLEAN; BEGIN v_valid := validate_comm (v_comm); IF v_valid = TRUE THEN g_comm := v_comm; ELSE raise_application_error (-20210, 'Invalid commission'); END IF; END reset_comm; END comm_package; /

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Invoking Package Constructs  Example 1: Invoke a function from a procedure within the same package.

CREATE OR REPLACE PACKAGE BODY comm_package IS . . . PROCEDURE reset_comm(v_comm IN NUMBER) IS v_valid BOOLEAN; BEGIN v_valid := validate_comm(v_comm); IF v_valid = TRUE THEN g_comm := v_comm; ELSE RAISE_APPLICATION_ERROR (-20210, 'Invalid comm'); END IF; END reset_comm; END comm_package;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Invoking Package Constructs Example 2: Invoke a package procedure from SQL*Plus. SQL>

EXECUTE comm_package.reset_comm(1500);

Example 3: Invoke a package procedure in a different schema. SQL>

EXECUTE scott.comm_package.reset_comm(1500);

Example 4: Invoke a package procedure in a remote database.

SQL>

EXECUTE comm_package.reset_comm@ny (1500);

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Referencing a Global Variable Within the Package Example 1 CREATE OR REPLACE PACKAGE BODY comm_package IS . . . PROCEDURE reset_comm(v_comm IN NUMBER) IS v_valid BOOLEAN; BEGIN v_valid := validate_comm(v_comm); IF v_valid = TRUE THEN g_comm := v_comm; ELSE RAISE_APPLICATION_ERROR (-20210,'Invalid comm'); END IF; END reset_comm; END comm_package;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Overloading  Allows you to use the same name for different subprograms inside a package  The formal parameters of the subprograms must differ in number, order, or datatype family  Overloaded subprograms can be placed in local or packaged subprograms

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Overloading : Example Initialize the first 50 rows in two PL/SQL tables. PROCEDURE initialize(tab OUT datetabtyp, n INTEGER) IS BEGIN FOR i in 1..n LOOP tab(i) := SYSDATE; END LOOP; END initialize; PROCEDURE initialize(tab OUT realtabtyp, n INTEGER) IS BEGIN FOR i in 1..n LOOP tab(i) := 1000; END LOOP; END initialize; IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Removing Packages To remove the package specification and the body: DROP PACKAGE package_name

To remove the package body: DROP PACKAGE BODY package_name

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Exception Handling  In PL/SQL a warning or error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user-defined.  Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.   With PL/SQL, a mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors.  When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.  To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

A Block without Exception Section SQL> declare 2 emp_sal EMP.SAL%TYPE; 3 emp_no EMP.EMPNO%TYPE; 4 vname varchar2(10) :='&name'; 5 begin 6 select EMPNO, SAL into emp_no, emp_sal 7 from EMP1 where ENAME = vname; 8 9 if emp_sal * 1.05 > 4000 then 10 dbms_output.put_line('High Salary'); 11 else 12 update EMP set sal = sal + sal * .1 13 where ENAME=vname; 14 end if ; 15 16 dbms_output.put_line('Salary Increased'); 17 end; 18 / Enter value for name: KING old 4: vname varchar2(10) :='&name'; new 4: vname varchar2(10) :='KING'; High Salary Salary Increased PL/SQL procedure successfully completed. IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

A Block without Exception Section SQL> declare 2 emp_sal EMP.SAL%TYPE; 3 emp_no EMP.EMPNO%TYPE; 4 vname varchar2(10) :='&name'; 5 begin 6 select EMPNO, SAL into emp_no, emp_sal 7 from EMP1 where ENAME = vname; 8 9 if emp_sal * 1.05 > 4000 then 10 dbms_output.put_line('High Salary'); 11 else 12 update EMP set sal = sal + sal * .1 13 where ENAME=vname; 14 end if ; 15 16 dbms_output.put_line('Salary Increased'); 17 end; 18 / Enter value for name: JOHN old 4: vname varchar2(10) :='&name'; new 4: vname varchar2(10) :='JOHN'; declare * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 6

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

A Block with Exception Section SQL> declare 2 emp_sal EMP.SAL%TYPE; 3 emp_no EMP.EMPNO%TYPE; 4 vname varchar2(10) :='&name'; 5 too_high_sal exception; 6 begin 7 select EMPNO, SAL into emp_no, emp_sal 8 from EMP1 where ENAME = vname; 9 10 if emp_sal * 1.05 > 4000 then 11 raise too_high_sal; 12 else 13 update EMP set sal = sal + sal * .1 14 where ENAME=vname; 15 end if ; 16 17 dbms_output.put_line('Salary Increased'); 18 exception 19 when no_data_found then 20 dbms_output.put_line(vname || ' Not Found'); 21 when too_high_sal then 22 dbms_output.put_line(vname || ' Already High Salary'); 23 end; 24 / Enter value for name: JOHN old 4: vname varchar2(10) :='&name'; new 4: vname varchar2(10) :='JOHN'; JOHN Not Found PL/SQL procedure successfully completed. IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Pre-defined Exception  An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. For example, the predefined exception NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows. The table below lists some of these exceptions with their names and a short description.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

User-defined Exceptions Raising an Exception

User-defined exceptions must be raised explicitly by RAISE statements. You can place RAISE statements for a given exception anywhere within the scope of that exception. You can also raise a predefined exception explicitly. DECLARE out_of_stock EXCEPTION; number_on_hand NUMBER(4); BEGIN ... IF number_on_hand < 1 THEN RAISE out_of_stock; END IF; EXCEPTION WHEN out_of_stock THEN -- handle the error END;

IGSI Confidential

|

DECLARE acct_type INTEGER := 7; BEGIN IF acct_type NOT IN (1, 2, 3) THEN -- raise predefined exception RAISE INVALID_NUMBER; END IF; EXCEPTION WHEN INVALID_NUMBER THEN ROLLBACK; END;

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

How PL/SQL Exceptions Propagate  When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search.

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

How PL/SQL Exceptions Propagate

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

How PL/SQL Exceptions Propagate

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Associating a PL/SQL Exception with a Number  In PL/SQL, the predefined pragma EXCEPTION_INIT tells the compiler to associate an exception name with an ORACLE error number.

Declare

Associate

Declarative section

• Name the

• Code the PRAGMA

exception

IGSI Confidential

EXCEPTION_INIT

|

Wednesday, August 5, 2009

Reference Exception-handling section

• Handle the raised exception © Copyright IBM Corporation 2003

IBM Global Services

Associating a PL/SQL Exception with a Number DECLARE e_emps_remaining EXCEPTION; e_emps_remaining EXCEPTION; PRAGMA PRAGMA EXCEPTION_INIT EXCEPTION_INIT ( ( e_emps_remaining, e_emps_remaining,-2292); -2292); v_deptno dept.deptno%TYPE := &p_deptno; BEGIN DELETE FROM dept WHERE deptno = v_deptno; COMMIT; EXCEPTION WHEN e_emps_remaining e_emps_remaining THEN DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' || TO_CHAR(v_deptno) || '. Employees exist. '); END;

IGSI Confidential

|

Wednesday, August 5, 2009

1 2

3

© Copyright IBM Corporation 2003

IBM Global Services

Functions for Trapping Exceptions SQLCODE Returns the numeric value for the error code SQLERRM Returns the message associated with the error number

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Example DECLARE v_error_code v_error_message BEGIN ... EXCEPTION ... WHEN OTHERS THEN ROLLBACK; v_error_code := v_error_message

NUMBER; VARCHAR2(255);

SQLCODE ; := SQLERRM SQLERRM ;

INSERT INTO errors VALUES(v_error_code, v_error_message); END;

IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

RAISE_APPLICATION_ERROR Procedure Syntax raise_application_error (error_number, message[, {TRUE | FALSE}]);

A procedure that lets you issue user-defined error Messages from stored subprograms Called only from an executing stored subprogram Used in two different places:  Executable section  Exception section Returns error conditions to the user in a manner consistent with other Oracle Server errors IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003

IBM Global Services

Example 1 Declare 2 curr_sal NUMBER; 3 vempno NUMBER := &emp_id; 4 BEGIN 5 SELECT sal INTO curr_sal FROM emp WHERE empno = vempno ; 6 IF curr_sal IS NULL THEN 7 raise_application_error(-20101, 'Salary is missing'); 8 ELSE 9 UPDATE emp SET sal = curr_sal + 500 10 WHERE empno = vempno ; 11 END IF; 12* END; SQL> / Enter value for emp_id: 7900 old 3: vempno NUMBER := &emp_id; new 3: vempno NUMBER := 7900; Declare * ERROR at line 1: ORA-20101: Salary is missing ORA-06512: at line 7 IGSI Confidential

|

Wednesday, August 5, 2009

© Copyright IBM Corporation 2003