UNIT – IV

ADVANCED PL/SQL

1.CURSORS

Cursors are a private working area or a temporary working area. “It is mainly used for
processing multiple records with in the PL/SQL.

A cursor is a temporary work area created in the system memory when a SQL
statement is executed. This temporary work area is used to store the data retrieved
from the database, and manipulate this data. A cursor can hold more than one row,
but can process only one row at a time.

Cursors are mainly divided into two types in SQL. There are

 Implicit Cursor (System defined)

 Explicit Cursor (User defined)
Implicit Cursor: The Implicit Cursor is declared by PL/SQL implicitly when DML
statements like INSERT, UPDATE, and DELETE statements are executed. These are
automatically executed by SQL Engine Internally.

Ex: Create Table, Alter Table, Create View, Update etc..,

Implicit Cursor Attributes: These attributes are associated with the implicit SQL cursor and
can be accessed by appending the attribute name to the implicit cursor name (SQL).
Syntax: SQL%<attribute name>;

In PL/SQL implicit cursor has 4 attributes there are:

1. SQL%ISOPEN
2. SQL%FOUND
3. SQL%NOTFOUND
4. SQL%ROWCOUNT
II.SQL%ISOPEN: It is used to determine if a cursor is already open. It is always „False‟
because Oracle automatically closes implicit cursor after executing its associated SQL
statements.
III. SQL%FOUND: It is always „True‟ if the most recently executed DML statement as
successful.
IV. SQL%NOTFOUND: It is „True‟ if the most recently executed DML statement was not
successful.
V. SQL%ROWCOUNT: This attribute is used to determine the number of rows that are
processed by SQL statements.
Example: Consider a PL/SQL code to display message to check whether the record is deleted
or not.
declare
begin
delete from stud_info where
stud_id = &stud_id;
if SQL%FOUND then
dbms_output.put_line ('Record Deleted');
else
dbms_output.put_line ('Record Not Deleted');
end if;
end;
/
Explicit Cursor: The Explicit cursor is declared explicitly by the user, along with other
identifiers to be used in a PL/SQL block. These are also known as „User defined‟
cursors, defined in the „Declare‟section of the PL/SQL block. They must be declared
when you are executing a „Select‟ statement that returns more than one row. Even
through the cursor stores multiple records, only one record can be processed at a time,
which is called as „Current row‟ or „active set‟.

A PL/SQL program opens a cursor to process rows returned by a query and then closes the
cursor.

Steps to create a cursor:

1. Declare the cursor
2. Open the cursor
3. Fetch data from the cursor
4. Close the cursor
Declare the cursor: Before using any cursor that should be declared first. In one PL/SQL
Program we may use many cursors; all cursors must be defined under declarative part of the
PL/SQL block. A cursor is defined in the declarative part by naming it and specifying a
„SELECT‟ statement

Syntax: CURSOR <cursor_name> IS <SELECT – statements>;

 cursor_name: A suitable name for the cursor.
 select – statement: A select query which returns multiple rows.
Example: declare
cursor cur is select * from emp where sal > 2000;
In the above example, we are declaring a cursor „cur‟ on a query which returns the records of all
the employees with salary greater than 2000. Here emp‟ is the table which contains records of
all the employees
Open the cursor: After declaration, the cursor is opened with an „Open‟ statement for
processing rows in the cursor. The SELECT statement associated with the cursor is executed
when the cursor is opened.

Syntax: OPEN <cursor_name>;

Example: OPEN cur;

Fetch / Retrieve data from the cursor: After the cursor is opened, the current row is
loading into variables. The current row is the row at which the cursor is currently pointing.
The transfer of data into PL/SQL variables is done through „FETCH‟ statement.

Syntax: FETCH <cursor_name> INTO <variables>; (or) FETCH <cursor_name> INTO

<record_named>
declare
enoemp.empno%type;
nameemp.ename
%type; salary emp.sal
%type;
cursor cur is select empno, ename, sal from emp where sal > 5000;
begin
open
cur;
loop
fetch cur into eno, name, salary;
exit when cur%notfound;
dbms_output.put_line (ENO || '' || NAME || '' ||
SALARY);
end loop;
close cur;
end;
/
Close the cursor: After working successfully with a cursor, it must be closed when we close a
cursor. It will release all recourse
Syntax: CLOSE <cursor_name>;

Example: CLOSE cur;

Explicit cursor attributes: We use these attributes to avoid errors while accessing
cursors through Open, Fetch and Close statements. There are the attributes available to
check the status of an explicit cursor.
In PL/SQL explicit cursor has 4 attributes there are:
1. %ISOPEN
2. %FOUND
3. %NOTFOUND
4.%ROWCOUNT
%ISOPEN: „True‟, if the cursor is already open in the program otherwise „False‟ the
cursor is not opened.

Syntax: <cursor_name>%ISOPEN;

Example: cur%ISOPEN;

%FOUND: „True‟, if fetch statement returns at least one row otherwise „False‟, if fetch
statement doesn‟t return a row.

Syntax: <cursor_name>%FOUND;

Example: fetch cur into eno, name,

salary;
if cur%found then
%NOTFOUND: „True‟, if fetch statement doesn‟t return a row otherwise „False‟, if fetch
statement returns at least one row.
Syntax: <cursor_name>%NOTFOUND;

Example: fetch cur into

eno,name, salary;
exit when cur
%notfound;
%ROWCOUNT: Return the number of rows fetched by the fetch statement. If no row is
returned, the PL/SQL statement returns an error.

Syntax: <cursor_name>%ROWCOUNT;

Example: fetch cur into eno,

name, salary;
if cur%found then

2.TRIGGERS:
* A trigger is a procedural SQL code that is automatically invocated by the RDBMS upon the
occurrence of a given data manipulation event.

* A Trigger is a stored procedure that implicitly executed, when an Insert, Update or

Delete is issued against the associated table.

* We can make a Trigger to Fire (Executed) only for DML statements (Insert, Update, and
Deleted).

*The Trigger code is stored in the database and runs automatically whenever the Triggering
event occurs.

* We can delete the Trigger by using Drop command.

Parts of Triggers: There

are mainly 3 parts.

1. Trigger Statement (Event)

2. Trigger Restriction (Conditions)
3. Trigger Body (Action)

1. Trigger Statement: The Trigger statement specified DML statement like Insert,
Update, Delete and that causes a trigger to be fired.

2. Trigger Restriction: We can specify the condition inside trigger to when trigger is fire.

3. Trigger Body: When the trigger SQL statement is execute, trigger automatically
call and PL/SQL trigger block execute.
Syntax for Creating Trigger:

CREATE [OR REPLACE] TRIGGER <TriggerName> {BEFORE/AFTER/INSTEAD OF}

{INSERT / DELETE/ UPDATE} ON <Table_Name>

[FOR EACH ROW/ STATEMENT [WHERE CONDITION]

DECLARE
Declaration of Variable/ constants;
BEGIN
Body of the Statement;
EXCEPTION
Exception of PL/SQL Block;
END;
Types of Trigger: Triggers are mainly divided into two parts based on usage. There are

1. Before / After
2. For each Row / Statement.
Before / After: The Before / After options can be used to specify when the trigger body
should be fire with respect to trigger statement.

 If Before option is used then Oracle Engine Fires the Trigger before executing the
statement.
 If After option is used then Oracle Engine Fires the Trigger After executing the
Trigger statement.
for each Row/Statement:

Statement Level Trigger: A Trigger which is created only for one record are called “Statement
Level Trigger”. In practice these are not used frequently by default. Every Trigger is statement
level Trigger.

Row Level Trigger: The Trigger which is created on the total table is known as “Row Level
Trigger” by using Row Level Trigger. We can make a Trigger to fire on selected columns for
more than once. When working with Row Level Triggers. We can use two qualifiers called
“Old” & “New”. The most commonly used Triggers are Row Level Triggers.

Example: You may use a Database Trigger to log the fact that an account receivable clerk
has lowered the amount owing on an outstanding account.
Ex:
Create or replace trigger T1
before update on student
for each row
Begin
dbms_output.put_line(“record
is updated”);
End;

Enabling & Disabling Triggers: Triggers don‟t affect all rows in the table. They
affect only Transactions of the specified type. When the Triggers Enable. The Triggers will
not affect any transactions created before to a Trigger by default. Triggers are enabled
when it is created, but sometimes we may need to disable Triggers disabling Triggers
during data loads improve the performances of the data load. In such a way the data load
partially succeeded and the Triggers was executed for a portion of the data load records. It
is also possible to since a trigger twice for the same transaction. To enable a trigger “Alter
Trigger” command is used with the “enable” keyword.

Syntax for Enable / Disable Trigger:

SQL>Alter table <table name> enable / disable all triggers;

Example: SQL> Alter table bank disable all trigger;

Dropping Trigger: If there are any unwanted Triggers in the data base. They must be
removed from that the database, it is also called Dropping a Trigger. To drop any Trigger
the following syntax can be used.
Syntax: SQL> drop trigger <trigger name>;

Example: SQL> drop trigger bank_person;

Trigger dropped.

Advantages of Triggers: Triggers can be used to makes the RDBMS to take some action.
When a database related event has occurred the following advantages of Triggers.
 The Business rules can be stored in the database consistently with each and
every updated operation.
 The reduce complexity of the application program that the database.
3.Exception Handling:

 PL/SQL makes it easy to detect and process error conditions called „Exception‟.
An exception is an error condition is raised. That is, normal execution stops and
control transfers to the exception-handling part of PL/SQL block.
 For example if try to division a number by zero, the pre defined execution ZREO-
DIVIDE is raised automatically. User-define exceptions must be raised explicitly by
RASIE statement.

Exceptions are two types:

 Internally / Pre-defined Exception.
 User-defined Exception.

Internally /Pre-define Exception: An internal exception is raised implicitly whenever a

PL/SQL program violates an Oracle rule or exceeds a system dependent limit. Every Oracle
 error has a number, but exceptions must be handled by named.

Error Number Exception Name Reason

ORA-06511 CURSOR_ALREADY_OPEN When you open a cursor that is already open.
ORA-01001 INVALID_CURSOR When you perform an invalid operation on a cursor like
closing a cursor, fetch data from a cursor that is not opened.
ORA-01403 NO_DATA_FOUND When a SELECT...INTO clause does not return any row from
a table.
ORA-01422 TOO_MANY_ROWS When you SELECT or fetch more than one row into a record
or variable.
ORA-01476 ZERO_DIVIDE When you attempt to divide a number by zero.

EXAMPLE:

Create or replace procedure pname(desig varchar2(20))

Is
Vname varchar2(20);
Begin
Select ename into value from emp where job=desig;
Dbms_output.put_line(‘vname:’||vname);
Exception
When NO_DATA_IS_FOUND then
Dbms_output.put_line(‘No Person is found’);
End Exception;
End;
/

User-define Exception: PL/SQL user define exception to make own exception. User-define
exception must be declare yourself and RAISE statement to raise explicitly.

Declare Exception: You must have to declare user define exception name in „Declare‟ block.

RAISE Exception: RAISE statement to raised defined exception name and control transfer to
an exception block

Implement Exception: In PL/SQL Exception block add When condition to implement user
define action.
Example:

Create or replace procedure pname(veid number)

Is comm number;
Vcomm exception;// declaration of exception
Begin

Select commission into comm. From emp where eid=veid;

If vcomm is found then

Dbms_output.put_line(‘Commission:’,comm);

Else

Raise Vcomm;

Exception

When Vcomm IS_NOT_FOUND then

Dbms_output.put_line(‘commision is not provided to particular employee’);

End exception;

End;