Friday, April 13, 2012

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.

Explicit Cursor:

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;
Related Posts Plugin for WordPress, Blogger...