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;