TRIGGERS
A database trigger is a stored procedure that is fired when an INSERT, UPDATE, or DELETE statements is issued against the associate table. A trigger defines an action the database should take when some database related event occurs. A trigger can include SQL and PL/SQL statements to executor as a unit and can invoke other stored procedures.
USES OF TRIGGERS
1) Prevent invalid transactions
2) Enforce complex security authorizations
3) Enforce complex business rules
4) Provide auditing
5) Maintain synchronous table replicates
6) Modify table data when DML statements are issued against views
7) Publish information about database events, user events, and SQL statements to subscribing applications
PARTS OF A TRIGGER
A database trigger has three parts:
1) Triggering event or Statement.
2) Trigger constraint (Optional)
3) Trigger action
1) Triggering Event or Statement:
A triggering event or statement is the SQL statement that causes a trigger to be fired. Atriggering event can be an INSERT, UPDATE, or DELETE statement for a specific table.
2) Trigger Constraint or Restriction
A trigger restriction specifies a Boolean (logical) expression that must be TRUE for thetrigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE. Atrigger restriction is an option available for triggers that are fired for each row. Its functionis to conditionally control the execution of a trigger. A trigger restriction is specified using aWHEN clause. It is an optional part of trigger.
3) Trigger Action
A trigger action is the procedure (PL/SQL block) that contains the SQL statements andPL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Types of Triggers
A trigger’s type is defined by the type of triggering transaction and by the level atwhich the trigger is executed. Oracle has the following types of triggers depending on thedifferent applications:
1)Row Level Triggers
2)Statement Level Triggers
3)Before Triggers
4) After Triggers
Row Level Triggers
Row level triggers execute once for each row in a transaction. The commands of row leveltriggers are executed on all rows that are affected by the command that enables thetrigger. For example, if an UPDATE statement updates multiple rows of a table, a row triggeris fired once for each row affected by the UPDATE statement. If the triggering statementaffects no rows, the trigger is not executed at all. Row level triggers are created using theFOR EACH ROW clause in the CREATE TRIGGER command.
Statement Level Triggers
Statement level triggers are triggered only once for each transaction. Statement level trigger are thedefault type of trigger created via the CREATE TRIGGER command. Consider a case where therequirement is to prevent the DELETE operation during Sunday. For this whenever DELETEstatement deletes records, there must be PL/SQL block that will be fired only once by DELETEstatement to check that day must not be Sunday by referencing system date.
Before and After Trigger
Since triggers are executed by events, they may be set to occur immediately before or after those events. When a trigger is defined, you can specify whether the trigger must occur before or after the triggering event i.e. INSERT, UPDATE, or DELETE commands. BEFORE triggers are used, the trigger action should determine whether or not the triggering statement should be allowed to complete. By
using a BEFORE trigger, you can eliminate unnecessary processing of the triggering statement. For example: To prevent deletion on Sunday,Statement level before trigger is used on DELETEstatement. BEFORE triggers are used to derive specific column values before completing a triggering INSERT Or UPDATE statement. AFTER trigger executes the trigger action after the triggering statement is executed. AFTER triggers are used, when the triggering statement is set to completebefore executing the trigger action. For example: To perform cascade delete operation, it means that user deletes the record from one table, the corresponding records in other tables are also deleted automatically by a trigger which fired after the execution of delete statement issued by the user.
Statement Level
BEFORE option
Oracle fires the trigger only once, before executing the triggering statement.
AFTER option
Oracle fires the trigger only once, after executing the trigger statement.
Row Level
BEFORE option
Oracle fires the trigger before modifying each row affected by the triggering statement.
AFTER option
Oracle fires the trigger after modifying each row affected by the triggering statement.
Instead of triggers
Instead of triggers was first featured in Oracle 8. This was something new in the world of triggers. These are triggers that are defined on a view rather than on a table. Can use INSTEAD OF trigger to tell Oracle what to do instead of performing the actions that invoked the trigger.
Syntax of creating trigger
CREATE OR REPLACE TRIGGER trigger_name
BEFOREAFTER
DELETEINSERTUPDATE [OF column_name, ]
ON table_name
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN condition]
DECLARE
variable declaration;
Constant declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTON exception PL/SQL BLOCK;
END;
REFERENCING
specified correlation names. The user could use the Correlation names in the PL/SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If the row is associated with a table named OLD or NEW, this clause can be used to specify different names to avoid confusion between the table name and the
correlation name. WHEN Clausespecifies the trigger restriction. This condition has to be satisfied to fire the trigger. This condition can be specified for the ROW TRIGGER.
Example
To Create a trigger on the emp table, which store the empno and operation in tableauditor for each operation i.e. Insert, Update and Delete.
CREATE OR REPLACE TRIGGER EMP_AUDIT
AFTER INSERT OR UPDATE OR DELETE ON EMP FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'INSERT');
ELSIF UPDATING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'UPDATE');
ELSIF DELETING THEN
INSERT INTO AUDITOR VALUES(:OLD.EMPNO,'DELETE');
END IF;
END;
Note: The same functionality can be achieved with the BEFORE type trigger also.
Enabling and Disabling Triggers
When a trigger is created it is automatically enabled and is triggered whenever the triggering command and the execution command is true. An enabled trigger executes the trigger body if the triggering statement is issued. To disable the execution use the ALTER TRIGGER Command with the DISABLE clause. A disable trigger does not execute the trigger body even if the triggeringstatement is issued. We can disable / enable the trigger by the following
syntax:ALTER TRIGGER DISABLE / ENABLE
Dropping Triggers
A database trigger is a stored procedure that is fired when an INSERT, UPDATE, or DELETE statements is issued against the associate table. A trigger defines an action the database should take when some database related event occurs. A trigger can include SQL and PL/SQL statements to executor as a unit and can invoke other stored procedures.
USES OF TRIGGERS
1) Prevent invalid transactions
2) Enforce complex security authorizations
3) Enforce complex business rules
4) Provide auditing
5) Maintain synchronous table replicates
6) Modify table data when DML statements are issued against views
7) Publish information about database events, user events, and SQL statements to subscribing applications
PARTS OF A TRIGGER
A database trigger has three parts:
1) Triggering event or Statement.
2) Trigger constraint (Optional)
3) Trigger action
1) Triggering Event or Statement:
A triggering event or statement is the SQL statement that causes a trigger to be fired. Atriggering event can be an INSERT, UPDATE, or DELETE statement for a specific table.
2) Trigger Constraint or Restriction
A trigger restriction specifies a Boolean (logical) expression that must be TRUE for thetrigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE. Atrigger restriction is an option available for triggers that are fired for each row. Its functionis to conditionally control the execution of a trigger. A trigger restriction is specified using aWHEN clause. It is an optional part of trigger.
3) Trigger Action
A trigger action is the procedure (PL/SQL block) that contains the SQL statements andPL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Types of Triggers
A trigger’s type is defined by the type of triggering transaction and by the level atwhich the trigger is executed. Oracle has the following types of triggers depending on thedifferent applications:
1)Row Level Triggers
2)Statement Level Triggers
3)Before Triggers
4) After Triggers
Row Level Triggers
Row level triggers execute once for each row in a transaction. The commands of row leveltriggers are executed on all rows that are affected by the command that enables thetrigger. For example, if an UPDATE statement updates multiple rows of a table, a row triggeris fired once for each row affected by the UPDATE statement. If the triggering statementaffects no rows, the trigger is not executed at all. Row level triggers are created using theFOR EACH ROW clause in the CREATE TRIGGER command.
Statement Level Triggers
Statement level triggers are triggered only once for each transaction. Statement level trigger are thedefault type of trigger created via the CREATE TRIGGER command. Consider a case where therequirement is to prevent the DELETE operation during Sunday. For this whenever DELETEstatement deletes records, there must be PL/SQL block that will be fired only once by DELETEstatement to check that day must not be Sunday by referencing system date.
Before and After Trigger
Since triggers are executed by events, they may be set to occur immediately before or after those events. When a trigger is defined, you can specify whether the trigger must occur before or after the triggering event i.e. INSERT, UPDATE, or DELETE commands. BEFORE triggers are used, the trigger action should determine whether or not the triggering statement should be allowed to complete. By
using a BEFORE trigger, you can eliminate unnecessary processing of the triggering statement. For example: To prevent deletion on Sunday,Statement level before trigger is used on DELETEstatement. BEFORE triggers are used to derive specific column values before completing a triggering INSERT Or UPDATE statement. AFTER trigger executes the trigger action after the triggering statement is executed. AFTER triggers are used, when the triggering statement is set to completebefore executing the trigger action. For example: To perform cascade delete operation, it means that user deletes the record from one table, the corresponding records in other tables are also deleted automatically by a trigger which fired after the execution of delete statement issued by the user.
Statement Level
BEFORE option
Oracle fires the trigger only once, before executing the triggering statement.
AFTER option
Oracle fires the trigger only once, after executing the trigger statement.
Row Level
BEFORE option
Oracle fires the trigger before modifying each row affected by the triggering statement.
AFTER option
Oracle fires the trigger after modifying each row affected by the triggering statement.
Instead of triggers
Instead of triggers was first featured in Oracle 8. This was something new in the world of triggers. These are triggers that are defined on a view rather than on a table. Can use INSTEAD OF trigger to tell Oracle what to do instead of performing the actions that invoked the trigger.
Syntax of creating trigger
CREATE OR REPLACE TRIGGER trigger_name
BEFOREAFTER
DELETEINSERTUPDATE [OF column_name, ]
ON table_name
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN condition]
DECLARE
variable declaration;
Constant declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTON exception PL/SQL BLOCK;
END;
REFERENCING
specified correlation names. The user could use the Correlation names in the PL/SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If the row is associated with a table named OLD or NEW, this clause can be used to specify different names to avoid confusion between the table name and the
correlation name. WHEN Clausespecifies the trigger restriction. This condition has to be satisfied to fire the trigger. This condition can be specified for the ROW TRIGGER.
Example
To Create a trigger on the emp table, which store the empno and operation in tableauditor for each operation i.e. Insert, Update and Delete.
CREATE OR REPLACE TRIGGER EMP_AUDIT
AFTER INSERT OR UPDATE OR DELETE ON EMP FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'INSERT');
ELSIF UPDATING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'UPDATE');
ELSIF DELETING THEN
INSERT INTO AUDITOR VALUES(:OLD.EMPNO,'DELETE');
END IF;
END;
Note: The same functionality can be achieved with the BEFORE type trigger also.
Enabling and Disabling Triggers
When a trigger is created it is automatically enabled and is triggered whenever the triggering command and the execution command is true. An enabled trigger executes the trigger body if the triggering statement is issued. To disable the execution use the ALTER TRIGGER Command with the DISABLE clause. A disable trigger does not execute the trigger body even if the triggeringstatement is issued. We can disable / enable the trigger by the following
syntax:ALTER TRIGGER DISABLE / ENABLE
Dropping Triggers
Triggers may be dropped via the drop trigger command. In order to drop a trigger, you musteither own the trigger or have the DROP ANY TRIGGER system privilege.
Syntax: DROP TRIGGER trigger_name;
Example:DROP TRIGGER rec_check;
Database Trigger V/s Procedure
Triggers don't accept parameters but
procedures can accept parameters.
A trigger is automatically executed without any action required by the user. A store procedure on the other hand needs to be explicitly invoked.
A trigger is automatically executed without any action required by the user. A store procedure on the other hand needs to be explicitly invoked.
PL/SQL - CURSOR
CURSOR
A cursor is a private SQL work area. Whenever we issue a SQL statement the Oracle server opens an area of memory in which the command is parsed and executed. This area is called a cursor.
When the executable part of a block issues a SQL statement PL/SQL creates an implicit cursor which PL/SQL manages automatically.
There are two types of cursors.
- Implicit cursors.
- Explicit cursors.
The Oracle server uses implicit cursors to parse and execute SQL statements. Implicit cursor is declared for all DML and PL/SQL select statements., including queries that return only one row.
Explicit cursors are explicitly declared by the programmer. It is created for the queries that return more than one row and are declared and named by the programmer and manipulated through specific statements in the block’s executable section.
SQL Cursor Attributes :
SQL cursor attributes allow us to evaluate what happened when an implicit cursor was last used. We can use these attributes in the exception section of a block to gather information about the execution of a DML statement.
SQL%ROWCOUNT :
Number of rows affected by the most recent SQL statement.
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 rows.
SQL%ISOPEN :
Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.
Note : PL/SQL does not return an error if a DML statement does not affect any rows in the underlying table. However, if a select statement does not retrieve any rows PL/SQL returns an exception.
Eg: Deleting rows that have the specified empno and print the no of rows deleted.
Variable rows_deleted varchar2(30)
Declare
V_empno emp.empno%type :=7900;
Begin
Delete from emp where empno = v_empno;
:rows_deleted := (SQL%ROWCOUNT ‘rows deleted.’);
End;
/
Print rows_deleted;
Explicit cursor :
Use explicit cursor to individually process each row returned by a multiple row select statement. The set or rows returned by a multiple row query is called the active set.
Declaring the cursor:
CURSOR cursor_name IS select_statement;
Eg:
Declare
Cursor emp_cursor is
Select ename, empno from emp;
Begin
----
Opening the cursor:
Open cursor_name;
--> Open the cursor to execute the query and identify the active set.
If the query returns no rows no exception is raised.
--> Open is an executable statement that performs the following operations.
--> Dynamically allocates memory for a context area that eventually contains crucial processing information.
--> Parses the select statement.
--> Binds the input variables – sets the value for the input variable.
--> Identifies the active set – the set of rows that satisfy the search criteria. Rows in the active set are not retrieved into variables when the open statement is executed. Rather fetch statement retrieve the rows.
--> Positions the pointer just before the first row in the active set.
Fetching data from the cursor :
Fetch cursor_name into [variable,…..] / record_name ] ;
--> Retrieve the current row values into variables.
--> Match each variable to correspond to the columns positionally.
--> Test to see whether cursor contains rows.
Eg:
Loop
Fetch emp_cursor into v_empno, v_ename;
Exit when ….;
----
Process the retrieved data.
---
End loop;
Retrieve the first 10 employees one by one
Set serveroutput on
Declare
V_empno emp.empno%type;
V_ename emp.ename%type;
Cursor emp_cursor is
Select empno, ename from emp;
Begin
Open emp_cursor;
For I in 1..10 loop
Fetch emp_cursor into v_empno, v_ename;
Dbms_output.put_line(to_char(v_empno) ’ –‘ v_ename);
End loop;
End;
Closing the cursor:
Close cursor_name;
--> The close statement disables the cursor, and the active set becomes undefined.
--> It releases the context area.
Note: The maximum limit to the number of open cursors per user is 50.
Explicit cursor attributes :
%ISOPEN : Evaluates to TRUE if the cursor is open.
Eg:
If not emp_cursor%ISOPEN then
Open emp_cursor;
End if;
Loop
Fetch emp_cursor ….
%NOTFOUND : Evaluates to TRUE if the most recent fetch does not return a row.
Eg:
Loop
Fetch c1 into my_ename, my_sal;
Exit when c1%NOTFOUND; (or c1%NOTFOUND is NULL)
-----
End loop;
%FOUND : Evaluates to TRUE if the most recent fetch returns a row.
%ROWCOUNT : Evaluates to the total number of rows returned so far.
Eg:
Loop
Fetch c1 into my_ename, my_sal;
If c1%ROWCOUNT >10 then
----
End if;
----
End loop;
Declare
V_empno emp.empno%TYPE;
V_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename from emp;
Begin
OPEN emp_cursor;
Loop
Fetch emp_cursor into v_empno, v_ename;
Exit when emp_cursor%ROWCOUNT > 10 or
Emp_cursor%NOTFOUND;
Dbms_ouput.put_line(to_char(v_empno’-‘ to_char(v_ename) ;
End loop;
Close emp_cursor;
End;
Cursors and Records :
Process rows of the active set by fetching values into PL/SQL RECORD.
Declare
Cursor emp_cursor is
Select empno, ename from emp;
Emp_record emp_cursor%ROWTYPE;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor into emp_record;
----
declare
cursor emp_cursor is
select empno, ename from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
inser into temp_emp (empid, empname) values (emp_record.empno, emp_record.ename);
end loop;
commit;
close emp_cursor;
end;
Cursor FOR loops :
A cursor FOR loop processes rows in an explicit cursor. It is a shortcut because the cursor is opened and rows are fetched once for each iteration in the loop the loop exists when the last row is processed and the cursor is closed automatically.
FOR record_name IN cursor_name LOOP
Statement1;
Statement2;
-----
End loop;
--> The cursor FOR loop is a shortcut to process explicit cursors.
--> Implicit open, fetch, exit and close cursor,
--> The record is implicitly declared.
Eg: Print a list of the employees who work for the sales department.
set serveroutput on
declare
cursor emp_cursor is
select empno, deptno from emp;
begin
for emp_record in emp_cursor
loop
if emp_record.deptno = 10 then
dbms_output.put_line(to_char(emp_record.empno));
end if;
end loop;
end;
/
Cursor FOR loops using subqueries :
No need to declare a cursor.
Set serveroutput on
Begin
For emp_record in (select empno, deptno from emp)
Loop
If emp_record.deptno = 10 then
Dbms_output.put_line(to_char(emp_record.empno));
End if;
End loop;
End;
/
Cursors with parameters :
We can pass parameters to the cursor in a cursor FOR loop. It means that we can open and close an explicit cursor several times in a block; returning a active set on each occasion. For each execution the previous cursor is closed and reopened with a new set of Each
parameters.formal parameter in the declaration section must have a corresponding actual parameter in the OPEN statement.
Cursor cursor_name
[(parameter_name datatype, …)]
Is
Select statement;
Open cursor_name(parameter, ----);
Eg:
Pass the deptno, job to the where clause in the cursor select statement.
A cursor is a private SQL work area. Whenever we issue a SQL statement the Oracle server opens an area of memory in which the command is parsed and executed. This area is called a cursor.
When the executable part of a block issues a SQL statement PL/SQL creates an implicit cursor which PL/SQL manages automatically.
There are two types of cursors.
- Implicit cursors.
- Explicit cursors.
The Oracle server uses implicit cursors to parse and execute SQL statements. Implicit cursor is declared for all DML and PL/SQL select statements., including queries that return only one row.
Explicit cursors are explicitly declared by the programmer. It is created for the queries that return more than one row and are declared and named by the programmer and manipulated through specific statements in the block’s executable section.
SQL Cursor Attributes :
SQL cursor attributes allow us to evaluate what happened when an implicit cursor was last used. We can use these attributes in the exception section of a block to gather information about the execution of a DML statement.
SQL%ROWCOUNT :
Number of rows affected by the most recent SQL statement.
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 rows.
SQL%ISOPEN :
Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.
Note : PL/SQL does not return an error if a DML statement does not affect any rows in the underlying table. However, if a select statement does not retrieve any rows PL/SQL returns an exception.
Eg: Deleting rows that have the specified empno and print the no of rows deleted.
Variable rows_deleted varchar2(30)
Declare
V_empno emp.empno%type :=7900;
Begin
Delete from emp where empno = v_empno;
:rows_deleted := (SQL%ROWCOUNT ‘rows deleted.’);
End;
/
Print rows_deleted;
Explicit cursor :
Use explicit cursor to individually process each row returned by a multiple row select statement. The set or rows returned by a multiple row query is called the active set.
Declaring the cursor:
CURSOR cursor_name IS select_statement;
Eg:
Declare
Cursor emp_cursor is
Select ename, empno from emp;
Begin
----
Opening the cursor:
Open cursor_name;
--> Open the cursor to execute the query and identify the active set.
If the query returns no rows no exception is raised.
--> Open is an executable statement that performs the following operations.
--> Dynamically allocates memory for a context area that eventually contains crucial processing information.
--> Parses the select statement.
--> Binds the input variables – sets the value for the input variable.
--> Identifies the active set – the set of rows that satisfy the search criteria. Rows in the active set are not retrieved into variables when the open statement is executed. Rather fetch statement retrieve the rows.
--> Positions the pointer just before the first row in the active set.
Fetching data from the cursor :
Fetch cursor_name into [variable,…..] / record_name ] ;
--> Retrieve the current row values into variables.
--> Match each variable to correspond to the columns positionally.
--> Test to see whether cursor contains rows.
Eg:
Loop
Fetch emp_cursor into v_empno, v_ename;
Exit when ….;
----
Process the retrieved data.
---
End loop;
Retrieve the first 10 employees one by one
Set serveroutput on
Declare
V_empno emp.empno%type;
V_ename emp.ename%type;
Cursor emp_cursor is
Select empno, ename from emp;
Begin
Open emp_cursor;
For I in 1..10 loop
Fetch emp_cursor into v_empno, v_ename;
Dbms_output.put_line(to_char(v_empno) ’ –‘ v_ename);
End loop;
End;
Closing the cursor:
Close cursor_name;
--> The close statement disables the cursor, and the active set becomes undefined.
--> It releases the context area.
Note: The maximum limit to the number of open cursors per user is 50.
Explicit cursor attributes :
%ISOPEN : Evaluates to TRUE if the cursor is open.
Eg:
If not emp_cursor%ISOPEN then
Open emp_cursor;
End if;
Loop
Fetch emp_cursor ….
%NOTFOUND : Evaluates to TRUE if the most recent fetch does not return a row.
Eg:
Loop
Fetch c1 into my_ename, my_sal;
Exit when c1%NOTFOUND; (or c1%NOTFOUND is NULL)
-----
End loop;
%FOUND : Evaluates to TRUE if the most recent fetch returns a row.
%ROWCOUNT : Evaluates to the total number of rows returned so far.
Eg:
Loop
Fetch c1 into my_ename, my_sal;
If c1%ROWCOUNT >10 then
----
End if;
----
End loop;
Declare
V_empno emp.empno%TYPE;
V_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename from emp;
Begin
OPEN emp_cursor;
Loop
Fetch emp_cursor into v_empno, v_ename;
Exit when emp_cursor%ROWCOUNT > 10 or
Emp_cursor%NOTFOUND;
Dbms_ouput.put_line(to_char(v_empno’-‘ to_char(v_ename) ;
End loop;
Close emp_cursor;
End;
Cursors and Records :
Process rows of the active set by fetching values into PL/SQL RECORD.
Declare
Cursor emp_cursor is
Select empno, ename from emp;
Emp_record emp_cursor%ROWTYPE;
Begin
Open emp_cursor;
Loop
Fetch emp_cursor into emp_record;
----
declare
cursor emp_cursor is
select empno, ename from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
inser into temp_emp (empid, empname) values (emp_record.empno, emp_record.ename);
end loop;
commit;
close emp_cursor;
end;
Cursor FOR loops :
A cursor FOR loop processes rows in an explicit cursor. It is a shortcut because the cursor is opened and rows are fetched once for each iteration in the loop the loop exists when the last row is processed and the cursor is closed automatically.
FOR record_name IN cursor_name LOOP
Statement1;
Statement2;
-----
End loop;
--> The cursor FOR loop is a shortcut to process explicit cursors.
--> Implicit open, fetch, exit and close cursor,
--> The record is implicitly declared.
Eg: Print a list of the employees who work for the sales department.
set serveroutput on
declare
cursor emp_cursor is
select empno, deptno from emp;
begin
for emp_record in emp_cursor
loop
if emp_record.deptno = 10 then
dbms_output.put_line(to_char(emp_record.empno));
end if;
end loop;
end;
/
Cursor FOR loops using subqueries :
No need to declare a cursor.
Set serveroutput on
Begin
For emp_record in (select empno, deptno from emp)
Loop
If emp_record.deptno = 10 then
Dbms_output.put_line(to_char(emp_record.empno));
End if;
End loop;
End;
/
Cursors with parameters :
We can pass parameters to the cursor in a cursor FOR loop. It means that we can open and close an explicit cursor several times in a block; returning a active set on each occasion. For each execution the previous cursor is closed and reopened with a new set of Each
parameters.formal parameter in the declaration section must have a corresponding actual parameter in the OPEN statement.
Cursor cursor_name
[(parameter_name datatype, …)]
Is
Select statement;
Open cursor_name(parameter, ----);
Eg:
Pass the deptno, job to the where clause in the cursor select statement.
Declare
Cursor emp_cursor
(p_deptno number, p_job varchar2)
Is
Select empno, ename from emp where deptno = p_deptno and job = p_job;
Begin
Open emp_cursor(10, ‘ACCOUNT’);
----
Close emp_cursor;
Open emp_cursor(20, ‘CLERK’);
--
End;
--------
Declare
Cursor emp_cursor( p_deptno number, p_job varchar2) is
Select empno, ename from emp where deptno = p_deptno and job = p_job;
Begin
For emp_record in emp_cursor(10, ‘SOFTWARE’)
Loop
--
The
FOR UPDATE clause :
Locks the rows before update or delete.
Add the FOR UPDATE clause in the cursor query to lock the affected rows when the cursor is opened. Because the Oracle server releases locks at the end of the transaction we should not commit across fetches from an explicit cursor if FOR UPDATE is used.
Select --- from ---
FOR UPDATE [OF column_reference] [NOWAIT];
Column_reference : is a column in the table against which the query is performed.
NOWAIT : Returns an oracle error if the rows are locked by another session.
Eg:
Retrieve the employees who work in dept 10 and update their salary.
Declare
Cursor emp_cursor is
Select empno, ename, deptname from emp, dept where emp.deptno = dept.deptno
And emp.deptno = 10 FOR UPDATE OF sal NOWAIT;
Cursor with subqueries :
Declare
Cursor my_cursor is
Select t1.deptno, t1.deptname, t2.staff from dept t1, (select deptno, count(*) as staff from emp
Group by deptno) t2
Where t1.deptno = t2.deptno and t2.staff >=3;
-----
Subquery : A subquery is a query that appears within another SQL DML statement. When evaluated the subquery provides a value or set of values to the outer query. Subqueries are often used in the WHERE clause of a select statement. They can also used in the FROM clause creating a temporary data source for that query.
Locks the rows before update or delete.
Add the FOR UPDATE clause in the cursor query to lock the affected rows when the cursor is opened. Because the Oracle server releases locks at the end of the transaction we should not commit across fetches from an explicit cursor if FOR UPDATE is used.
Select --- from ---
FOR UPDATE [OF column_reference] [NOWAIT];
Column_reference : is a column in the table against which the query is performed.
NOWAIT : Returns an oracle error if the rows are locked by another session.
Eg:
Retrieve the employees who work in dept 10 and update their salary.
Declare
Cursor emp_cursor is
Select empno, ename, deptname from emp, dept where emp.deptno = dept.deptno
And emp.deptno = 10 FOR UPDATE OF sal NOWAIT;
Cursor with subqueries :
Declare
Cursor my_cursor is
Select t1.deptno, t1.deptname, t2.staff from dept t1, (select deptno, count(*) as staff from emp
Group by deptno) t2
Where t1.deptno = t2.deptno and t2.staff >=3;
-----
Subquery : A subquery is a query that appears within another SQL DML statement. When evaluated the subquery provides a value or set of values to the outer query. Subqueries are often used in the WHERE clause of a select statement. They can also used in the FROM clause creating a temporary data source for that query.
PL/SQL FUNCTIONS :
A function is a
named pl/sql block that returns a value.
A function can be
stored in the database as a schema object for repeated execution.
Syntax :
Create or replace
function function_name
Parameter mode
datatype
----
Return datatype
Is | as
Pl/sql block
Advantages:
--> Increase
efficiency of queries by performing functions in the query rather than in the
application.
--> Permits calculations
that are too complex.
Eg:
Create or replace
function get_sal
(p_id in emp.empno%type)
Return number
Is
V_sal emp.sal%type
:= 0;
Begin
Select sal into
v_sal from emp where empno = p_id;
Return v_sal;
End;
Variable g_sal
number;
Execute :g_sal :=
get_sal(117);
Print g_sal;
A function may
accept one or many parameters but must return a single value.
Executing
functions:
--> Invoke a
function as part of a pl/sql expression.
--> Create a variable
to hold the return value.
Invoking functions
in SQL expressions :
Create or replace
function tax (p_value in number)
Return number
Is
Begin
Return (p_value *
0.08);
End;
Select empno,
ename, sal, tax(sal) from emp where empno=7900;
Locations to call
user defined functions
--> Select list
of select command.
--> Condition of
the where and having clause.
--> Connect by,
start with, order by and group by clauses.
--> values
clauses of insert command.
--> set clause
of the update command.
PL/SQL user defined
functions can be called from any where, where built in functions can be called.
Restrictions on
calling functions from SQL expressions :
--> Must be a
stored function.
--> Accept only
IN parameters.
--> Accept only
valid SQL data types, not pl/sql specific types, as parameters.
--> Return data
types that are valid sql data types not pl/sql specific types.
--> Functions
called from sql expressions cannot contain DML statements.
--> Functions
called from update or delete statements on a table T cannot contain a DML on
the same table T.
--> Functions
called from a DML statement on a table T cannot query the same table.
-->We must own
or have the execute privilege.
Notes:
--> Only stored
functions are callable from sql statements. Stored procedures cannot be called.
Removing functions
--> Drop
function function_name;
Procedures Vs
Functions
Procedure :
--> Execute as a
PL/SQL statement.
--> No return
clause in the header.
--> Can return
none, one or many values.
--> Can contain
a return statement.
Function :
--> Invokes as
part of an expression.
--> Must contain
a return clause in the header.
--> Must return
a single value.
--> Must contain
at least one return statement.
No comments:
Post a Comment