Use The Referencing Clause

If we want we can use the referencing clause to specify a different name [similar to alias name] for :old and :new correlation names.  This is the syntax:

referencing [old as old_name] [new as new_name]

Example:

Create or replace triggergenerateprofID

before insert on professor

referencing new as new_prof

for each row

begin

if :new_prof.pid is null then

select profids.nextval into :new_prof.pid from dual;

end if;

end triggergenerateprofID;

How A Cursor Works?

Open:

• Open the cursor, to execute query and identify active set.
• If the query returns no rows, no exception is raised.
• Use cursor attributes to test the outcome after a fetch.

Fetch:

• To retrieve the current row values into variables.
• Include the same number of variables.
• Match each variable to correspond to columns, by position.
• Test to see if cursor contains rows.

Close:

• Close cursor after completing processing-rows. Session scope.
• Reopen cursor, if required.
• Do not attempt to fetch data from cursor once closed.