Learn About Different Type Of Triggers In Oracle

Database triggers are specialized stored programs. Oracle engine allow the definition of procedure that are implicitly executed when an insert, update, delete is issued again a table from sql or thought application the trigger automatically associated DML statement is executed.They are not called directly but are triggered by events in the database. They run between the time you issue a commandand the time you perform the database management system action. You can write triggers in PL/SQL.

PL/SQL Type of Triggers based on how they triggered:-

Before Triggers: These triggers are fired before the triggering SQL statement (INSERT, UPDATE, DELETE) is executed. The execution of triggering SQL statement is stopped depending on the various conditions to be fulfilled in BEFORE trigger.

After Triggers: These triggers are fired after the triggering SQL statement  (INSERT, UPDATE, DELETE) is executed. The triggering SQL statement is executed first followed by the code of trigger.

ROW Trigger: The triggers are fired for each and every record which is inserted or updated or deleted from a table.

Statement Trigger: The Trigger are fired for each row of DML operation being performed on a table. we can not access the column values for records being inserted, updated, deleted on the table and not individual records.

PL/SQL Triggers Syntax Description:-

CREATE or REPLACE TRIGGER trigger_name: Creates a trigger with the given name otherwise overwrites an existing trigger with the same name.

{BEFORE , AFTER }: Indicates the where should trigger get fired. BEFORE trigger execute before when statement execute before time or AFTER trigger execute after when statement execute after time.

{INSERT , UPDATE , DELETE}: Determines the performing trigger event. More than one triggering events allow can be used together separated by OR keyword.

ON Table Name: Determine the perform trigger event in selected Table.

[Referencing {old AS old, new AS new}]: Reference the old and new values of the data being changed. :old use to existing row perform and :new use to execute new row to perform. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.

Note:-

Insert have no :OLD value ( before execution) and have :NEW value (After execution)

Delete have no :OLD value but it have :NEW value.

Update have both :OLD and :NEW value.

for each row: Trigger must fire when each row gets Affected (Row Level Trigger) or just once when the entire sql statement is executed(statement level Trigger).

WHEN (condition): Valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

There are various events on which a trigger can be written, such as:

1.System events

.....a. Database startup and shutdown

.....b. Server error message events

2.User events

.....a. User logon and logoff

.....b. DDL statements (CREATE, ALTER, and DROP)

.....c. DML statements (INSERT, DELETE, and UPDATE)

Based on the above condition we can classify the trigger into 5 catogory dml trigger , ddl trigger ,Compound triggers, Instead-of triggers and System or database event triggers. Out of which here i am discussing mainly ddl and dml trigger.

1.DDL Trigger

DDL triggers fire when you create, change or remove objects in a database, they support both before and after event triggers and work at the database or schema level.

DDL event supported :-

alter, analyze, associate statistics, audit, comment, create, ddl, disassociate statistics, drop, grant, noaudit, rename, revoke, truncate .

There are a number of event attribute functions that can be used to get user, client or system information , commonly used are given below.

ORA_CLIENT_IP_ADDRESS	returns the client IP address as varchar2
ORA_DATABASE_NAME	returns database name as varchar2
ORA_DES_ENCRYPTED_PASSWORD	returns DES-encrypted password as varchar2
ORA_DICT_OBJ_NAME	returns object name as varchar2
ORA_DICT_OBJ_NAME_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_OWNER	returns the owner of the object acted upon by the event as a varchar2
ORA_DICT_OBJ_OWNER_LIST	returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_TYPE	returns the datatype of the dictionary object changed by the event as a varchar2
ORA_GRANTEE	returns the number of elements in the list as a pls_integer
ORA_INSTANCE_NUM	returns the current database instance number as a number
ORA_IS_ALTER_COLUMN	returns true or false depending if the column has been altered (true = altered)
ORA_IS_CREATING_NESTED_TABLE	returns a true or false value when you create a table with a nested table
ORA_IS_DROP_COLUMN	returns true or false depending if the column has been dropped (true = dropped)
ORA_IS_SERVERERROR	returns true or false when the error is on the error stack
ORA_LOGIN_USER	returns the current schema name as a varchar2
ORA_PARTITION_POS	returns the numeric position with the SQL text where you can insert a partition clause
ORA_PRIVILEGE_LIST	returns the number of elements in the list as a pls_integer
ORA_REVOKEE	returns the number of elements in the list as a pls_integer
ORA_SERVER_ERROR	returns the error number as a number
ORA_SERVER_ERROR_DEPTH	returns the number of errors on the error stack as a pls_interger
ORA_SERVER_ERROR_MSG	returns an error message text as a varchar2
ORA_SERVER_ERROR_NUM_PARAMS	returns the count of any substituted strings from error messages as a pls_integer
ORA_SERVER_ERROR_PARAM	returns an error message text as a varchar2
ORA_SQL_TXT	returns the number of elements in the list as a pls_integer
ORA_SYSEVENT	returns the system event that was responible for firing the trigger as a varchar2
ORA_WITH_GRANT_OPTION	returns true or false when privileges are granted with grant option (true = with grant option)
SPACE_ERROR_INFO	returns true or false when the triggering event is related to an out-of-space condition.Now for an example

Example.1

The below given ddl trigger prevent truncating table on schema level,

SQL> create or replace trigger prevent_truncates

  2    before truncate on schema

  3    begin

  4    raise_application_error(-20001,'TRUNCATE not permitted');

  5   end;

  6    /

Trigger created.

SQL> create table salary_bk as select * from salary;

Table created.

SQL> select * from salary_bk;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

    ---------- ---------------    ---------------       ----------   ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> truncate table salary_bk;

truncate table salary_bk

               *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: TRUNCATE not permitted

ORA-06512: at line 2

SQL>    

Example.2 

Below given trigger update every create statement that happens in the schema level into 

log_table,

SQL> CREATE TABLE log_table(

  2     user_name       VARCHAR2(100),

  3     event_date      DATE,

  4     detail          VARCHAR2(400));

Table created.

SQL> 

CREATE OR REPLACE TRIGGER log_create_trigg

  2    AFTER CREATE ON SCHEMA

  3  BEGIN

  4    INSERT INTO log_table

  5      (user_name, event_date, detail)

  6    VALUES

  7      (USER, SYSDATE, 'created object is: ' || ora_dict_obj_name);

  8  END;

  9  /

Trigger created.

SQL> select * from log_table;

no rows selected

SQL> create table abc as select * from dba_users;

Table created.

SQL> col user_name for a12

SQL> col detail for a25

SQL> select * from log_table;

USER_NAME    EVENT_DAT DETAIL

------------ --------- -------------------------

MAHI         19-OCT-12 created object is: ABC

2.Database event trigger

These triggers fire when a system activity occurs in the database, like the logon and logoff event triggers. They are useful for auditing information of system access. These triggers let you track system events and map them to users.

Example:-

Below given trigger logs the logging information into log_trigger_table table, 

SQL> CREATE TABLE log_trigger_table (

  2        user_name       VARCHAR2(30),

  3        event_date      DATE,

  4        action     VARCHAR2(300));

Table created.

SQL> CREATE OR REPLACE TRIGGER logon_trigger

  2      AFTER LOGON ON SCHEMA

  3      BEGIN

  4      INSERT INTO log_trigger_table

  5       (user_name, event_date, action )

  6       VALUES

  7       (USER, SYSDATE, 'Logging On');

  8    END;

  9     /

Trigger created.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\DELL\node1>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 19 17:39:19 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: mahi

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from log_trigger_table;

USER_NAME    EVENT_DAT ACTION

------------ --------- -----------------------------------

MAHI         19-OCT-12 Logging On

3.DML Trigger

These triggers fire when you insert, update, or delete data from a table. You can fire them once for all changes on a table, or for each row change, using statement- or row-level trigger types, respectively. DML triggers are useful to control DML statements. You can use these triggers to audit, check, save, and replace values before they are changed.

Example.1

Below given example insert each record that will deleted from salary table into sal_deleted table,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

    ---------- ---------------   ---------------        ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

     10005 Arunkumar.K     Programmer           12000       1440       1320       1080      13800

SQL> create table sal_deleted(

rollno number(5), name varchar(15),

del_date date);

Table created.
SQL>

Now create the trigger,

SQL>ed sal_delete_trig

create or replace trigger sal_delete before delete

on salary for each row

begin 

insert into sal_deleted values

 (:old.rollno, :old.empname,sysdate);

end;

/

SQL> @sal_delete_trig

Trigger created.

SQL> delete from salary where rollno = 10005;

1 row deleted.

SQL> select * from salary;

      ROLLNO  EMPNAME    DESIGN                BPAY         DA         TA         PF     NETSAL

      ---------- --------------- ---------------          ---------- ---------- ---------- ---------- ----------

     10001   S.Krishnan      HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

SQL> select * from sal_deleted;

    ROLLNO NAME            DEL_DATE

---------- --------------- ---------

     10005 Arunkumar.K     19-OCT-12

Example.2

Following trigger will insert the system time automatically into DOJ field while inserting records into student_details table,

SQL> create table student_details

  2  (rollno number(5), name varchar(15),

  3  dob date, doj date, dop date );

Table created.

SQL> ed student_details_trig;

create trigger student_details_trig before insert

on student_details for each row

begin

:new.doj := sysdate;

end;

/

SQL> @student_details_trig

Trigger created.

SQL> select * from student_details;

no rows selected

SQL> select sysdate from dual;

SYSDATE

---------

19-OCT-12

SQL> insert into student_details (rollno,name,dob) values (1001,'MAHESH','30-OCT-86');

1 row created.

SQL> select * from student_details;

    ROLLNO NAME            DOB       DOJ       DOP

---------- --------------- --------- --------- ---------

      1001 MAHESH          30-OCT-86 19-OCT-12

SQL>

Here you can see DOJ is automatically inserted by the trigger..

Example.3

Following trigger will insert each records into salupdated table before update happens in salary table,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- ---------------         --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200       1800      22000

SQL> create table salupdated(

  2  rollno number(5),

  3  empname varchar(15),

  4  design varchar(15),

  5  bpay number(8,2),

  6  da number(6,2),

  7  total number(8,2),

  8  ta number(6,2));

Table created.

SQL> ed salupdate_trig

create or replace trigger salupdate_trig before update

on salary for each row

begin

insert into salupdated values

(:old.rollno, :old.empname, :old.design, :old.bpay, :old.da, :old.netsal, :old.ta);

end;

/

SQL> @salupdate_trig

Trigger created.

SQL> select * from salupdated;

no rows selected

SQL> update salary set BPAY=21000 where DESIGN='Asst.Manager';

3 rows updated.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         21000       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         21000       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         21000       1500       1200       1800      22000

SQL> select * from salupdated;

    ROLLNO EMPNAME         DESIGN                BPAY         DA      TOTAL         TA

---------- --------------- --------------- ---------- ---------- ---------- ----------

     10002 K.K.Omana       Asst.Manager         20000       1500      22000       1200

     10003 Anishkumar.K    Asst.Manager         20000       1500      22000       1200

     10004 Girishkumar.K   Asst.Manager         20000       1500      22000       1200

SQL>

Example.4
Following dml trigger will raise an application error while trying to delete records belonging to Asst.Manager,

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200       2250      27000

     10002 K.K.Omana       Asst.Manager         19500       1500       1200       1800      22000

     10003 Anishkumar.K    Asst.Manager         19500       1500       1200       1800      22000

     10004 Girishkumar.K   Asst.Manager         19500       1500       1200       1800      22000

SQL> CREATE or REPLACE TRIGGER not_del

  2  AFTER

  3  DELETE ON salary

  4  for each row

  5

  6  BEGIN

  7  IF :old.DESIGN = 'Asst.Manager' THEN

  8     raise_application_error(-20015, 'Not Delete this Row');

  9  END IF;

 10  END;

 11  /

Trigger created.

SQL> delete from salary where rollno=10004;

delete from salary where rollno=10004

            *

ERROR at line 1:

ORA-20015: Not Delete this Row

ORA-06512: at "MAHI.NOT_DEL", line 3

ORA-04088: error during execution of trigger 'MAHI.NOT_DEL

Learn about oracle Cursor with example

A cursor is a pointer, which points towards a pre-allocated memory location in th SGA. The memory location to which it points is known as Context area. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into the ORA-01000: maximum number of open cursors exceeded error.

There are two major types of cursors: Implicit cursors and explicit cursors. Again we can classify the cursor as static (normal cursor) and dynamic cursor (Reference cursor)

Implicit cursor

PL/SQL declares an implicit cursor for every DML command, and queries that return a single row. The name of the implicit cursor is SQL. You can directly use this cursor without any declaration. Implicit cursor are used for DML statements and you have no control over them. The cursor is opened at the point in time when the SQL statement is run by Oracle, the data is fetched if the statement is a query, and the cursor is closed as soon as the SQL statement finishes - all automatically.

You can however access the results of implicit cursors (such as how many rows were updated or whether or not any rows were found) as well as the data fetched from Oracle by an implicit cursor for a SELECT statement.

The process of an implicit cursor is as follows:-

• Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
• A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).PL/SQL employs an implicit cursor for each UPDATE, DELETE, or INSERT statement you execute in a program. You cannot, in other words, execute these statements within an explicit cursor, even if you want to. You have a choice between using an implicit or explicit cursor only when you execute a single-row SELECT statement (a SELECT that returns only one row).
• All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.
• An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
• The most recently opened cursor is called the “SQL%” Cursor.

For example:-

In the following UPDATE statement, which gives everyone in the company a 10% raise, PL/SQL creates an implicit cursor to identify the set of rows in the table which would be affected by the update:

UPDATE employee SET salary = salary * 1.1;

The following single-row query calculates and returns the total salary for a department. Once again, PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total FROM employee

 WHERE department_number = 10;

Thus the implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

Common attribute of implicit cursor
%ISOPEN
SQL%ISOPEN always has the value FALSE.
%FOUND
SQL%FOUND has one of these values:

• If no SELECT or DML statement has run, NULL.
• If the most recent SELECT or DML statement returned a row, TRUE.
• If the most recent SELECT or DML statement did not return a row, FALSE.

%NOTFOUND
SQL%NOTFOUND has one of these values:

• If no SELECT or DML statement has run, NULL.
• If the most recent SELECT or DML statement returned a row, FALSE.
• If the most recent SELECT or DML statement did not return a row, TRUE.

%ROWCOUNT
SQL%ROWCOUNT has one of these values:

• If no SELECT or DML statement has run, NULL.
• If a SELECT or DML statement has run, the number of rows fetched so far.

Here's a sample piece of Oracle PL/SQL code that uses an implicit cursor for a  SELECT statement.

Example.1

Note that the only variable declared was a record (gra) to hold the employee data obtained from the database and the only difference between our select statement and one we would issue using SQL*Plus is the INTO clause.

Example.2

If you can used any DML statement in your pl-sql code. At last you use SQL cursor name with its attributes so that you will meaning full information. Like SQL%ROWCOUNT which gives how many rows are affected by this plsql block.
Consider the PL/SQL Block that uses implicit cursor attributes as shown below:-

2.Explicit Cursor

Explicit cursors are defined explicitly (using the keyword CURSOR) and used only for SQL SELECT statements. Implicit cursors are used for both DML statements (UPDATE, INSERT, DELETE) and SELECT statements. PL/SQL’s implicit cursor can handle only single-row queries. If you ever need to select more than one row using SELECT in PL/SQL then you have to use explicit cursor.

The process of working with an explicit cursor consists of the following steps:

•          DECLARING the cursor. This initializes the cursor into memory.

•          OPENING the cursor. The previously declared cursor can now be opened; memory        is allotted.  

•          FETCHING the cursor. The previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor.

•          CLOSING the cursor. The previously declared, opened, and fetched cursor must  now be closed to release memory allocation

Example.1

I created a table named salary and inserted some records as shown.Now i created a procedure to insert records into salary table so that rollno should take accordingly. The procedure utilize the cursor to take the maximum of rollno from salary table .

SQL> create table salary(

rollno number(5),empname varchar(15),

design varchar(15),

bpay number(7,2),

da number(6,2),

ta number(6,2),

pf number(6,2),

netsal number(8,2));

Insert the records by,

SQL> insert into salary values ( &rollno, '&empname', '&design', &bpay, &da, &ta, &pf, &netsal);

SQL> select * from salary;

    ROLLNO EMPNAME     DESIGN                  BPAY         DA         TA         PF     NETSAL

  ---------- --------------- ---------------           ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan        HOD                     25000       1500       1200        700      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200        700      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200        700      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200        700      22000

Now create the procedure as follows,

C:\Users\DELL> ed salinsert

declare  

cursor ins_c is select max(rollno) from salary;---> declare section

prollno salary.rollno%type;

pname salary.empname%type;

pdesign salary.design%type;

pbpay salary.bpay%type;

pda salary.da%type;

pta salary.ta%type;

ppf salary.pf%type;

pnetsal salary.netsal%type;

begin

open ins_c;  ---> opening the cursor

fetch ins_c into prollno; ---> fetching the cursor

close ins_c; ---> closing the cursor

prollno := prollno + 1;

pname := '&pname';

pdesign := '&pdesign';

pbpay := &pbpay;

pda := pbpay * 12/100;

pta := pbpay *11/100;

ppf := pbpay *8/100;

pnetsal := pbpay + pda + pta -ppf;

insert into salary values (prollno , pname , pdesign, pbpay , pda , pta , ppf , pnetsal );

end;

/

Now execute the sql,

SQL> @salinsert

Enter value for pname: Arunkumar.K

old  24: pname := '&pname';

new  24: pname := 'Arunkumar.K';

Enter value for pdesign: Programmer

old  25: pdesign := '&pdesign';

new  25: pdesign := 'Programmer';

Enter value for pbpay: 12000

old  26: pbpay := &pbpay;

new  26: pbpay := 12000;

PL/SQL procedure successfully completed.

SQL> select * from salary;

    ROLLNO EMPNAME         DESIGN                BPAY         DA         TA         PF     NETSAL

---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------

     10001 S.Krishnan      HOD                       25000       1500       1200        700      27000

     10002 K.K.Omana       Asst.Manager         20000       1500       1200        700      22000

     10003 Anishkumar.K    Asst.Manager         20000       1500       1200        700      22000

     10004 Girishkumar.K   Asst.Manager         20000       1500       1200        700      22000

     10005 Arunkumar.K     Programmer          12000       1440       1320        960      13800

SQL>

Example.2

I created a table named grade , Now i want to insert salary table's rollno , name and their

grade accordig to their bpay into grade table.

SQL> create table grade (

rollno number(5),

name varchar(15),

grade char(1));

Now prepare the pl-sql code,

C:\Users\DELL> ed gradeinsert

declare

cursor crr is select rollno,empname,bpay from salary;

prollno salary.rollno%type;

pname salary.empname%type;

pbpay salary.bpay%type;

pgrade grade.grade%type;

begin

open crr;

loop

fetch crr into prollno,pname,pbpay;

exit when crr%notfound;

if pbpay > 20000 then

   pgrade := 'A';

elsif pbpay > 15000 then

   pgrade := 'B';

else

   pgrade := 'C';

end if;

insert into grade values (prollno,pname,pgrade);

end loop;

close crr;

end;

/

SQL> @gradeinsert

PL/SQL procedure successfully completed.

SQL> select * from grade;

    ROLLNO NAME            G

    ---------- --------------- -

     10001 S.Krishnan      A

     10002 K.K.Omana       B

     10003 Anishkumar.K    B

     10004 Girishkumar.K   B

     10005 Arunkumar.K     C

Example.3

This example shows the cursor to count the number of lines

C:\Users\DELL> ed count_sal

declare

        cursor c_countEmps is select count(*) from salary;

        v_out NUMBER;

    begin

        open c_countEmps;

        fetch c_countEmps into v_out;

        close c_countEmps;

       DBMS_OUTPUT.put_line('number of emps is:'||v_out);

   end;

/

SQL> set serveroutput on

SQL> @count_sal

number of emps is:5

PL/SQL procedure successfully completed.
SQL>

Parameterised cursors

PL/SQL Parameterized cursor pass the parameters into a cursor and use them in to query.Parameterizing the cursor makes it more usable and avoids the limitation of hard coding values in where the clause.

C:\Users\DELL>ed para_cursor

declare

cursor crr is select rollno,empname,bpay from salary

where rollno = &prollno; ----->> see the difference

prollno salary.rollno%type;

pname salary.empname%type;

pbpay salary.bpay%type;

pgrade grade.grade%type;

begin

open crr;

loop

fetch crr into prollno,pname,pbpay;

exit when crr%notfound;

if pbpay > 20000 then

   pgrade := 'A';

elsif pbpay > 15000 then

   pgrade := 'B';

else

   pgrade := 'C';

end if;

insert into grade values (prollno,pname,pgrade);

end loop;

close crr;

end;

/

SQL> delete from grade;

5 rows deleted.

SQL> commit;

Commit complete.

\SQL> select * from grade;

no rows selected

Now tun the sql,

SQL> @para_cursor

Enter value for prollno: 10003

old   4: where rollno = &prollno;

new   4: where rollno = 10003;

PL/SQL procedure successfully completed.

SQL> select * from grade;

    ROLLNO   NAME              G

     ---------- ---------------     -

     10003     Anishkumar.K    B

SQL>