Sunday, November 1, 2009

Cursor and Trigger in Pl/Sql

Cursor: cursor is a private sql work area. Every sql statement executed by oracle server has an individual cursor associated with it.
Cursor are two types
1.Implicit cursor
2.Explicit cursor
Implicit cursor: Implicit cursors are declared by pl/sql implicitly at the time of DML statement and select statement in pl/sql including queries that returns single row.

Cursor have four attributes
1. SQL%ROWCOUNT
2. SQL%ISOPEN
3. SQL%NOTFOUND
4. SQL%FOUND

SQL%ROWCOUNT-Basically it returns  number. means number of rows affected by present sql statement.
SQL%ISOPEN-Always evalutes false because implicit cursor automatically closed after execution of sql statement.
SQL%FOUND-Always evalutes true because one or more rows are affected by recent sql statement
SQL%NOTFOUND-Always evalutes true when no rows are affected by present sql statement.

Example of explicit cursor
DECLARE
v_empno employees.employee_id%TYPE;
v_name employees.last_name%TYPE;
CURSOR emp_cur IS
SELECT employee_id,last_name FROM employees
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno,v_name ;
EXIT WHEN emp_cur%ROWCOUNT>10 OR
emp_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('employee_id:'||TO_CHAR(v_empno) || 'employee_name:'||'v_name');

END LOOP;
CLOSE emp_cur;
END;

Trigger Trigger is pl/sql block or procedure that is associated with table,view,schema and database. Execute immediately when particular event take place.
There are two types of trigger
1.Application trigger: Fires automatically when event occurs with particular application.
2.Database trigger: Fires when data such as DML oparation occured at that time.
DML triggers are two types
   1.Statementlevel trigger
   2.Rowlevel trigger

Statement level trigger
-Statement level trigger means trigger body execute once for the triggering event.this is default.A statement level trigger fire once even no rows are affected at all.
Row level- Trigger body execute once for each row affected by triggering event.if no rows are affected in that case trigger body not executed.

Trigger example:
CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON employees
BEGIN
TO_CHAR(SYSDATE,'DY')IN('SUN','SAT') OR TO_CHAR((SYSDATE,'HH24:MI')NOT BETWEEN '08:00' AND '18:00')THEN
RAISE_APPLICATION_ERROR(-20253,'u may insert employee information at business hrs');
END;

3 comments:

Tani said...

Thanks for explaining Cursor and Trigger in Pl/Sql in details. It gives a clear insite where they can be implemented.


sap project system

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.