User:Nileshdgawali

Example of Exception

declare temp_sal employees.salary%type; Less_sal exception; Begin select salary into temp_sal from employees where employee_id = 103; if temp_sal < 10000 Then raise less_sal; else update employees set salary = salary - 1; end if; EXCEPTION when no_data_found then raise_application_error('-20001','No data found'); when less_sal then dbms_output.put_line('Salary is very less'); end;

Example of Reference Cursor

REF CURSOR: - A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.). Let us start with a small sub-program as follows: declare type r_cursor is REF CURSOR; c_emp r_cursor; en emp.ename%type; begin open c_emp for select ename from emp; loop fetch c_emp into en; exit when c_emp%notfound; dbms_output.put_line(en); end loop; close c_emp; end; Let me explain step by step. The following is the first statement you need to understand: type r_cursor is REF CURSOR; The above statement simply defines a new data type called "r_cursor," which is of the type REF CURSOR. We declare a cursor variable named "c_emp" based on the type "r_cursor" as follows: c_emp r_cursor; Every cursor variable must be opened with an associated SELECT statement as follows: open c_emp for select ename from emp; To retrieve each row of information from the cursor, I used a loop together with a FETCH statement as follows: Loop fetch c_emp into en; exit when c_emp%notfound; dbms_output.put_line (en); end loop; I finally closed the cursor using the following statement: Close c_emp;

%ROWTYPE with REF CURSOR In the previous section, I retrieved only one column (ename) of information using REF CURSOR. Now I would like to retrieve more than one column (or entire row) of information using the same. Let us consider the following example: Declare type r_cursor is REF CURSOR; c_emp r_cursor; er emp%rowtype; begin open c_emp for select * from emp; loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line (er.ename || ' - ' || er.sal); end loop; close c_emp; end; In the above example, the only crucial declaration is the following: er emp%rowtype; The above declares a variable named "er," which can hold an entire row from the "emp" table. To retrieve the values (of each column) from that variable, we use the dot notation as follows: Dbms_output.put_line (er.ename || ' - ' || er.sal); Let us consider that a table contains forty columns and I would like to retrieve fifteen columns. In such scenarios, it is a bad idea to retrieve all forty columns of information. At the same time, declaring and working with fifteen variables would be bit clumsy. The next section will explain how to solve such issues. Working with REF CURSOR in PL/SQL - Working with RECORD and REF CURSOR (Page 2 of 4) Until now, we have been working either with %TYPE or %ROWTYPE. This means we are working with either one value or one complete record. How do we create our own data type, with our own specified number of values to hold? This is where TYPE and RECORD come in. Let us consider the following example: Declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record (    name  varchar2(20),     sal   number(6)    ); er rec_emp; begin open c_emp for select ename,sal from emp; loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; close c_emp; end; The most confusing aspect from the above program is the following: type rec_emp is record (   name  varchar2(20),    sal   number(6)  ); The above defines a new data type named "rec_emp" (just like %ROWTYPE with limited specified fields) which can hold two fields, namely "name" and "sal." er rec_emp; The above statement declares a variable "er" based on the datatype "rec_emp." This means that "er" internally contains the fields "name" and "job." fetch c_emp into er; The above statement pulls out a row of information (in this case "ename" and "sal") and places the same into the fields "name" and "sal" of the variable "er." Finally, I display both of those values using the following statement: dbms_output.put_line(er.name || ' - ' || er.sal); As defined earlier, a REF CURSOR can be associated with more than one SELECT statement at run-time. Before associating a new SELECT statement, we need to close the CURSOR. Let us have an example as follows: declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record (    name  varchar2(20),     sal   number(6)   ); er rec_emp; begin open c_emp for select ename,sal from emp where deptno = 10; dbms_output.put_line('Department: 10'); dbms_output.put_line('--'); loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; close c_emp; open c_emp for select ename,sal from emp where deptno = 20; dbms_output.put_line('Department: 20'); dbms_output.put_line('--'); loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; close c_emp; end; In the above program, the skeleton looks like the following: declare . . Begin . .  open c_emp for select ename,sal from emp where deptno = 10; . .     fetch c_emp into er; . . close c_emp; . . open c_emp for select ename,sal from emp where deptno = 20; . .     fetch c_emp into er; . . close c_emp; . . end; From the above skeleton, you can easily understand that every CURSOR is opened, used and closed before opening the same with the next SELECT statement. Working with REF CURSOR inside loops Sometimes, it may be necessary for us to work with REF CURSOR within loops. Let us consider the following example: declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record (    name  varchar2(20),     sal   number(6)   ); er rec_emp; begin for i in (select deptno,dname from dept) loop open c_emp for select ename,sal from emp where deptno = i.deptno; dbms_output.put_line(i.dname); dbms_output.put_line('--'); loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; close c_emp; end loop; end; As you can observe from the above program, I implemented a FOR loop as follows: for i in (select deptno,dname from dept) loop .     .  end loop; The above loop iterates continuously for each row of the "dept" table. The details of each row in "dept" (like deptno, dname etc.) will be available in the variable "i." Using that variable (as part of the SELECT statement), I am working with REF CURSOR as follows: open c_emp for select ename,sal from emp where deptno = i.deptno; The rest of the program is quite commonplace. Working with REF CURSOR in PL/SQL - Dealing with REF CURSOR in the sub-programs of a PL/SQL block (Page 4 of 4 ) Sub-programs can also be called sub-routines. These are nothing but the divisions of the main program. These divisions are named and are executed when they are called by name from the main program. They will not get executed unless they are called. The following is an example: declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record (   name  varchar2(20),    sal   number(6)  ); er rec_emp; procedure PrintEmployeeDetails is begin loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; end; begin for i in (select deptno,dname from dept) loop open c_emp for select ename,sal from emp where deptno = i.deptno; dbms_output.put_line(i.dname); dbms_output.put_line('--'); PrintEmployeeDetails; close c_emp; end loop; end; In the above program, the sub-routine is named "PrintEmployeeDetails." You can observe that I am executing (or calling) the sub-routine from within the loop as follows: for i in (select deptno,dname from dept) loop .     .      PrintEmployeeDetails; .     .  end loop; According to the above loop, the sub-routine gets executed for every iteration, which displays the employee information for the respective department. Passing REF CURSOR as parameters to sub-programs In the previous section, we already started working with sub-programs (or sub-routines). In this section, I shall extend the same with the concept of "parameters" (or arguments). Every sub-program (or sub-routine) can accept values passed to it in the form of "parameters" (or arguments). Every parameter is very similar to a variable, but gets declared as part of a sub-program. Let us consider the following program: declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record (   name  varchar2(20),    sal   number(6)  ); procedure PrintEmployeeDetails(p_emp r_cursor) is   er rec_emp; begin loop fetch p_emp into er; exit when p_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; end; begin for i in (select deptno,dname from dept) loop open c_emp for select ename,sal from emp where deptno = i.deptno; dbms_output.put_line(i.dname); dbms_output.put_line('--'); PrintEmployeeDetails(c_emp); close c_emp; end loop; end; From the above program, you can observe the following declaration: procedure PrintEmployeeDetails(p_emp r_cursor) is In the above declaration, "PrintEmployeeDetails" is the name of the sub-routine which accepts "p_emp" as a parameter (of type "r_cursor") and we can use that parameter throughout that sub-routine. I hope you enjoyed the article and any comments, suggestions, feedback, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com

14.8. REF CURSORS and Cursor Variables This section discusses another type of cursor that provides far greater flexibility than the previously discussed static cursors. 14.8.1. REF CURSOR Overview A cursor variable is a cursor that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable. EnterpriseDB currently supports both strongly and weakly typed REF CURSOR's. A cursor variable is not tied to a single particular query like a static cursor. The same cursor variable may be opened a number of times with OPEN FOR statements containing different queries. Each time, a new result set is created from that query and made available via the cursor variable. REF CURSOR types may be passed as parameters to or from stored procedures and functions. The return type of a function may also be a REF CURSOR type. This provides the capability to modularize the operations on a cursor into separate programs by passing a cursor variable between programs. 14.8.2. Declaring a Cursor Variable SPL supports the declaration of a cursor variable using both the SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR. Only the declaration of SYS_REFCURSOR and user defined REF CURSOR variable's is different. The remaining usage like opening the cursor, selecting into the cursor and closing the cursor is the same across both the cursor types. For the rest of this chapter our examples will primarily be making use of the SYS_REFCURSOR cursors. All you need to change in the examples to make them work for user defined REF CURSOR's is the declaration section. Note: Strongly-typed REF CURSOR's require the result set to conform to a declared number and order of fields with compatible data types and can also optionally return a result set. 14.8.2.1. Declaring a SYS_REFCURSOR Cursor Variable The following is the syntax for declaring a SYS_REFCURSOR cursor variable: name SYS_REFCURSOR; name is an identifier assigned to the cursor variable. The following is an example of a SYS_REFCURSOR variable declaration. DECLARE emp_refcur     SYS_REFCURSOR; ... 14.8.2.2. Declaring a User Defined REF CURSOR Type Variable You must perform two distinct declaration steps in order to use a user defined REF CURSOR variable: 1.	Create a referenced cursor TYPE 2.	Declare the actual cursor variable based on that TYPE The syntax for creating a user defined REF CURSOR type is as follows: TYPE cursor_type_name IS REF CURSOR [RETURN return_type]; The following is an example of a cursor variable declaration. DECLARE TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE; my_rec emp_cur_type; ... 14.8.3. Opening a Cursor Variable Once a cursor variable is declared, it must be opened with an associated SELECT command. The OPEN FOR statement specifies the SELECT command to be used to create the result set. OPEN name FOR query; name is the identifier of a previously declared cursor variable. query is a SELECT command that determines the result set when the statement is executed. The value of the cursor variable after the OPEN FOR statement is executed identifies the result set. In the following example, the result set is a list of employee numbers and names from a selected department. Note that a variable or parameter can be used in the SELECT command anywhere an expression can normally appear. In this case a parameter is used in the equality test for department number. CREATE OR REPLACE PROCEDURE emp_by_dept (   p_deptno        emp.deptno%TYPE ) IS   emp_refcur      SYS_REFCURSOR; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; ... 14.8.4. Fetching Rows From a Cursor Variable After a cursor variable is opened, rows may be retrieved from the result set using the FETCH statement. See Section 14.7.3 for details on using the FETCH statement to retrieve rows from a result set. In the example below, a FETCH statement has been added to the previous example so now the result set is returned into two variables and then displayed. Note that the cursor attributes used to determine cursor state of static cursors can also be used with cursor variables. See Section 14.7.7 for details on cursor attributes. CREATE OR REPLACE PROCEDURE emp_by_dept (   p_deptno        emp.deptno%TYPE ) IS   emp_refcur      SYS_REFCURSOR; v_empno        emp.empno%TYPE; v_ename        emp.ename%TYPE; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); LOOP FETCH emp_refcur INTO v_empno, v_ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; ... 14.8.5. Closing a Cursor Variable Use the CLOSE statement described in Section 14.7.5 to release the result set. Note: Unlike static cursors, a cursor variable does not have to be closed before it can be re-opened again. The result set from the previous open will be lost. The example is completed with the addition of the CLOSE statement. CREATE OR REPLACE PROCEDURE emp_by_dept (   p_deptno        emp.deptno%TYPE ) IS   emp_refcur      SYS_REFCURSOR; v_empno        emp.empno%TYPE; v_ename        emp.ename%TYPE; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); LOOP FETCH emp_refcur INTO v_empno, v_ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; CLOSE emp_refcur; END; The following is the output when this procedure is executed. EXEC emp_by_dept(20)

EMPNO   ENAME -   --- 7369     SMITH 7566    JONES 7788    SCOTT 7876    ADAMS 7902    FORD 14.8.6. Usage Restrictions The following are restrictions on cursor variable usage. Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null. Null cannot be assigned to a cursor variable. The value of a cursor variable cannot be stored in a database column. Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement. In addition the following table shows the permitted parameter modes for a cursor variable used as a procedure or function parameter depending upon the operations on the cursor variable within the procedure or function. Table 14-3. Permitted Cursor Variable Parameter Modes Operation	IN	IN OUT	OUT OPEN	No	Yes	No FETCH	Yes	Yes	No CLOSE	Yes	Yes	No So for example, if a procedure performs all three operations, OPEN FOR, FETCH, and CLOSE on a cursor variable declared as the procedure's formal parameter, then that parameter must be declared with IN OUT mode. 14.8.7. Examples The following are examples of cursor variable usage. 14.8.7.1. Returning a REF CURSOR From a Function In the following example the cursor variable is opened with a query that selects employees with a given job. Note also that the cursor variable is specified in this function's RETURN statement so the result set is made available to the caller of the function. CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2) RETURN SYS_REFCURSOR IS   emp_refcur      SYS_REFCURSOR; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job; RETURN emp_refcur; END; This function is invoked in the following anonymous block by assigning the function's return value to a cursor variable declared in the anonymous block's declaration section. The result set is fetched using this cursor variable and then it is closed. DECLARE v_empno        emp.empno%TYPE; v_ename        emp.ename%TYPE; v_job          emp.job%TYPE := 'SALESMAN'; v_emp_refcur   SYS_REFCURSOR; BEGIN DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job); DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); v_emp_refcur := emp_by_job(v_job); LOOP FETCH v_emp_refcur INTO v_empno, v_ename; EXIT WHEN v_emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; CLOSE v_emp_refcur; END; The following is the output when the anonymous block is executed. EMPLOYEES WITH JOB SALESMAN EMPNO   ENAME -   --- 7499     ALLEN 7521    WARD 7654    MARTIN 7844    TURNER 14.8.7.2. Modularizing Cursor Operations The following example illustrates how the various operations on cursor variables can be modularized into separate programs. The following procedure opens the given cursor variable with a SELECT command that retrieves all rows. CREATE OR REPLACE PROCEDURE open_all_emp (   p_emp_refcur    IN OUT SYS_REFCURSOR ) IS BEGIN OPEN p_emp_refcur FOR SELECT empno, ename FROM emp; END; This variation opens the given cursor variable with a SELECT command that retrieves all rows, but of a given department. CREATE OR REPLACE PROCEDURE open_emp_by_dept (   p_emp_refcur    IN OUT SYS_REFCURSOR,    p_deptno        emp.deptno%TYPE ) IS BEGIN OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; END;

This third variation opens the given cursor variable with a SELECT command that retrieves all rows, but from a different table. Also note that the function's return value is the opened cursor variable.

CREATE OR REPLACE FUNCTION open_dept (   p_dept_refcur    IN OUT SYS_REFCURSOR ) RETURN SYS_REFCURSOR IS   v_dept_refcur    SYS_REFCURSOR; BEGIN v_dept_refcur := p_dept_refcur; OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept; RETURN v_dept_refcur; END;

This procedure fetches and displays a cursor variable result set consisting of employee number and name. CREATE OR REPLACE PROCEDURE fetch_emp (   p_emp_refcur    IN OUT SYS_REFCURSOR ) IS   v_empno         emp.empno%TYPE; v_ename        emp.ename%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); LOOP FETCH p_emp_refcur INTO v_empno, v_ename; EXIT WHEN p_emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; END;

This procedure fetches and displays a cursor variable result set consisting of department number and name. CREATE OR REPLACE PROCEDURE fetch_dept (   p_dept_refcur   IN SYS_REFCURSOR ) IS   v_deptno        dept.deptno%TYPE; v_dname        dept.dname%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('DEPT  DNAME'); DBMS_OUTPUT.PUT_LINE('  -'); LOOP FETCH p_dept_refcur INTO v_deptno, v_dname; EXIT WHEN p_dept_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_deptno || '    ' || v_dname); END LOOP; END;

This procedure closes the given cursor variable. CREATE OR REPLACE PROCEDURE close_refcur (   p_refcur        IN OUT SYS_REFCURSOR ) IS BEGIN CLOSE p_refcur; END;

The following anonymous block executes all the previously described programs. DECLARE gen_refcur     SYS_REFCURSOR; BEGIN DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES'); open_all_emp(gen_refcur); fetch_emp(gen_refcur); DBMS_OUTPUT.PUT_LINE('****************');

DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10'); open_emp_by_dept(gen_refcur, 10); fetch_emp(gen_refcur); DBMS_OUTPUT.PUT_LINE('****************');

DBMS_OUTPUT.PUT_LINE('DEPARTMENTS'); fetch_dept(open_dept(gen_refcur)); DBMS_OUTPUT.PUT_LINE('*****************');

close_refcur(gen_refcur); END;

The following is the output from the anonymous block. ALL EMPLOYEES EMPNO   ENAME -   --- 7369     SMITH 7499    ALLEN 7521    WARD 7566    JONES 7654    MARTIN 7698    BLAKE 7782    CLARK 7788    SCOTT 7839    KING 7844    TURNER 7876    ADAMS 7900    JAMES 7902    FORD 7934    MILLER **************** EMPLOYEES IN DEPT #10 EMPNO   ENAME -   --- 7782     CLARK 7839    KING 7934    MILLER **************** DEPARTMENTS DEPT  DNAME - 10    ACCOUNTING 20    RESEARCH 30    SALES 40    OPERATIONS *****************

14.8.8. Dynamic Queries With REF CURSOR's EnterpriseDB also supports dynamic queries via the OPEN FOR USING statement. A string literal or string variable is supplied in the OPEN FOR USING statement to the SELECT command. OPEN name FOR dynamic_string [ USING bind_arg [, bind_arg_2 ]...]; name is the identifier of a previously declared cursor variable. dynamic_string is a string literal or string variable containing a SELECT command (without the terminating semi-colon). bind_arg, bind_arg_2... are bind arguments that are used to pass variables to corresponding placeholders in the SELECT command when the cursor variable is opened. The placeholders are identifiers prefixed by a colon character. The following is an example of a dynamic query using a string literal. CREATE OR REPLACE PROCEDURE dept_query IS   emp_refcur      SYS_REFCURSOR; v_empno        emp.empno%TYPE; v_ename        emp.ename%TYPE; BEGIN OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' || ' AND sal >= 1500'; DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); LOOP FETCH emp_refcur INTO v_empno, v_ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; CLOSE emp_refcur; END;

The following is the output when the procedure is executed. EXEC dept_query;

EMPNO   ENAME -   --- 7499     ALLEN 7698    BLAKE 7844    TURNER In the next example, the previous query is modified to use bind arguments to pass the query parameters. CREATE OR REPLACE PROCEDURE dept_query (   p_deptno        emp.deptno%TYPE,    p_sal           emp.sal%TYPE ) IS   emp_refcur      SYS_REFCURSOR; v_empno        emp.empno%TYPE; v_ename        emp.ename%TYPE; BEGIN OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = :dept' || ' AND sal >= :sal' USING p_deptno, p_sal; DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); LOOP FETCH emp_refcur INTO v_empno, v_ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; CLOSE emp_refcur; END;

The following is the resulting output. EXEC dept_query(30, 1500);

EMPNO   ENAME -   --- 7499     ALLEN 7698    BLAKE 7844    TURNER

Finally, a string variable is used to pass the SELECT providing the most flexibility. CREATE OR REPLACE PROCEDURE dept_query (   p_deptno        emp.deptno%TYPE,    p_sal           emp.sal%TYPE ) IS   emp_refcur      SYS_REFCURSOR; v_empno        emp.empno%TYPE; v_ename        emp.ename%TYPE; p_query_string VARCHAR2(100); BEGIN p_query_string := 'SELECT empno, ename FROM emp WHERE ' || 'deptno = :dept AND sal >= :sal'; OPEN emp_refcur FOR p_query_string USING p_deptno, p_sal; DBMS_OUTPUT.PUT_LINE('EMPNO   ENAME'); DBMS_OUTPUT.PUT_LINE('-   ---'); LOOP FETCH emp_refcur INTO v_empno, v_ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || '    ' || v_ename); END LOOP; CLOSE emp_refcur; END; EXEC dept_query(20, 1500);

EMPNO   ENAME -   --- 7566     JONES 7788    SCOTT 7902    FORD

Example of Triggers

What is a Trigger?

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed. Syntax of Triggers Syntax for Creating a Trigger

CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) BEGIN --- sql statements END;

For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table CREATE TABLE product_price_history (product_id number(5), product_name varchar2(32), supplier_name varchar2(32), unit_price number(7,2) ); CREATE TABLE product  (product_id number(5), product_name varchar2(32), supplier_name varchar2(32), unit_price number(7,2) );

2) Create the price_history_trigger and execute it. CREATE or REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price  ON product  FOR EACH ROW  BEGIN   INSERT INTO product_price_history VALUES (:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price);  END;  /

3) Lets update the price of a product. UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100 Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.

4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back. Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered. 1) Row level trigger - An event is triggered for each row upated, inserted or deleted. 2) Statement level trigger - An event is triggered for each sql statement executed. PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired. 1) BEFORE statement trigger fires first. 2) Next BEFORE row level trigger fires, once for each row affected. 3) Then AFTER row level trigger fires once for each affected row. This events will alternates between BEFORE and AFTER row level triggers. 4) Finally the AFTER statement level trigger fires.

For Example: Let's create a table 'product_check' which we can use to store messages when triggers are fired.

CREATE TABLE product (Message varchar2(50), Current_Date number(32));

Let's create a BEFORE and AFTER statement and row level triggers for the product table.

1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' before a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER Before_Update_Stat_product BEFORE UPDATE ON product Begin INSERT INTO product_check Values('Before update, statement level',sysdate); END; /

2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row is updated.

CREATE or REPLACE TRIGGER Before_Upddate_Row_product BEFORE UPDATE ON product FOR EACH ROW BEGIN INSERT INTO product_check Values('Before update row level',sysdate); END; /

3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' after a sql update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER After_Update_Stat_product AFTER UPDATE ON product BEGIN INSERT INTO product_check Values('After update, statement level', sysdate); End; /

4) AFTER UPDATE, Row Level: This trigger will insert a record into the table 'product_check' after each row is updated.

CREATE or REPLACE TRIGGER After_Update_Row_product AFTER insert On product FOR EACH ROW BEGIN INSERT INTO product_check Values('After update, Row level',sysdate); END; /

Now lets execute a update statement on table product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id in (100,101);

Lets check the data in 'product_check' table to see the order in which the trigger is fired.

SELECT * FROM product_check;

Output:

Message                                     Current_Date Before update, statement level             26-Nov-2008 Before update, row level                   26-Nov-2008 After update, Row level                    26-Nov-2008 Before update, row level                   26-Nov-2008 After update, Row level                    26-Nov-2008 After update, statement level              26-Nov-2008

The above result shows 'before update' and 'after update' row level events have occured twice, since two records were updated. But 'before update' and 'after update' statement level events are fired only once per sql statement.

The above rules apply similarly for INSERT and DELETE statements. How To know Information about Triggers.

We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.

The below statement shows the structure of the view 'USER_TRIGGERS'

DESC USER_TRIGGERS;

NAME                             Type TRIGGER_NAME                 VARCHAR2(30) TRIGGER_TYPE                 VARCHAR2(16) TRIGGER_EVENT                VARCHAR2(75) TABLE_OWNER                  VARCHAR2(30) BASE_OBJECT_TYPE             VARCHAR2(16) TABLE_NAME                   VARCHAR2(30) COLUMN_NAME                  VARCHAR2(4000) REFERENCING_NAMES            VARCHAR2(128) WHEN_CLAUSE                  VARCHAR2(4000) STATUS                       VARCHAR2(8) DESCRIPTION                  VARCHAR2(4000) ACTION_TYPE                  VARCHAR2(11) TRIGGER_BODY                 LONG

This view stores information about header and body of the trigger.

SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product';

The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'.

You can drop a trigger using the following command.

DROP TRIGGER trigger_name;

CYCLIC CASCADING in a TRIGGER This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situtation does not exist.

The below example shows how Trigger's can enter into cyclic cascading. Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created. 1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'. 2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.

In such a situation, when there is a row inserted in table 'abc', triggerA fires and will update table 'xyz'. When the table 'xyz' is updated, triggerB fires and will insert a row in table 'abc'. This cyclic situation continues and will enter into a infinite loop, which will crash the database.

Example of Varray

declare type ABC is varray(10) of number; v1 ABC := ABC(1,2,3,4,5,6); Begin dbms_output.put('After Declaration Stage'); dbms_output.new_line; for i in 1 .. v1.count loop dbms_output.put(v1(i)||' '); end loop; dbms_output.new_line; dbms_output.put_line('Count of array: '||v1.count); dbms_output.put_line('Limit of array: '||v1.limit); dbms_output.put_line('First value of array: '||v1.First); dbms_output.put_line('Last value of array: '||v1.Last); dbms_output.new_line; v1.trim(v1.next(2)); dbms_output.put_line('After triming and next function'); dbms_output.new_line; for i in 1 .. v1.count loop dbms_output.put(v1(i)||' '); end loop; dbms_output.new_line; dbms_output.put_line('Count of array: '||v1.count); dbms_output.put_line('Limit of array: '||v1.limit); dbms_output.put_line('First value of array: '||v1.First); dbms_output.put_line('Last value of array: '||v1.Last); dbms_output.new_line; end;

Output: anonymous block completed After Declaration Stage 1 2 3 4 5 6 Count of array: 6 Limit of array: 10 First value of array: 1 Last value of array: 6 After deletion of second item 1 2 3 Count of array: 3 Limit of array: 10 First value of array: 1 Last value of array: 3

Example of Nested-Table

declare type card_suit is table of varchar(10); card card_suit := card_suit; begin for i in 1..3 loop card.extend; if i = 1 then card(i):='Ace'; elsif i = 2 then card(i):='Two'; elsif i = 3 then card(i):='Three'; end if; end loop; for i in 1..card.count loop dbms_output.put_line(card(i)); end loop; end; Example of Associative Array

declare type number_table is table of number Index by PLS_INTEGER; list1 number_table; begin for i in 1 .. 6 loop list1(i):=i; end loop; list1.delete(2); for i in 1..list1.count loop if list1.exists then dbms_output.put_line('['||list1(i)||']'); end if; end loop; end;