CURSOR

• A cursor is the Private Memory area which is created by an Oracle

server for manipulating the data.
• A cursor is a pointer to this context area. PL/SQL controls the context
area through a cursor.
• A cursor holds the rows (one or more) returned by a SQL statement.
The set of rows the cursor holds is referred to as the active set.
• In other words, a cursor is used to process the result set returned by a SQL
query. Cursors are particularly useful when you want to iterate through
multiple rows in a result set and perform actions for each row.

You can name a cursor so that it could be referred to in a program to fetch

and process the rows returned by the SQL statement, one at a time. There
are two types of cursors −

• Implicit cursors

• Explicit cursors

Implicit Cursors
• Implicit cursors in PL/SQL are automatically created and managed by
the PL/SQL engine for SQL statements like SELECT, INSERT, UPDATE,
DELETE, and others. You don't need to explicitly declare, open, fetch,
or close these cursors. They simplify the code by handling these
operations implicitly.

• Programmers cannot control the implicit cursors and the information in

it.
• The most common use of implicit cursors is in the FOR...IN loop, where
the loop iterates through the rows returned by a SELECT statement.
• Whenever a DML statement (INSERT, UPDATE and DELETE) is issued,
an implicit cursor is associated with this statement. For INSERT
operations, the cursor holds the data that needs to be inserted. For
 UPDATE and DELETE operations, the cursor identifies the rows that
would be affected.

In PL/SQL, you can refer to the most recent implicit cursor as the SQL
cursor, which always has attributes such as %FOUND, %ISOPEN,
%NOTFOUND, and %ROWCOUNT. The SQL cursor has additional
attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed
for use with the FORALL statement. The following table provides the
description of the most used attributes −

S.No Attribute & Description

%FOUND

1 Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or

more rows or a SELECT INTO statement returned one or more rows.
Otherwise, it returns FALSE.

%NOTFOUND

The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or

2
DELETE statement affected no rows, or a SELECT INTO statement returned
no rows. Otherwise, it returns FALSE.

%ISOPEN

3 Always returns FALSE for implicit cursors, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.

%ROWCOUNT

4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE

statement, or returned by a SELECT INTO statement.

Any SQL cursor attribute will be accessed as sql%attribute_name as

shown below in the example.

Example
To Print all the students details
declare

v_student_id varchar2(10);

v_student_name VARCHAR2(20);

v_dcontactnumber NUMBER(12);

v_dno number(5);

v_marks number(5);

Begin

FOR student_rec IN (SELECT * FROM student)

LOOP

DBMS_OUTPUT.PUT_LINE('Student ID: ' || student_rec.regno);

DBMS_OUTPUT.PUT_LINE('Student Name: ' || student_rec.sname);

DBMS_OUTPUT.PUT_LINE('Department ID: ' || student_rec.contact_number);

DBMS_OUTPUT.PUT_LINE('Department ID: ' || student_rec.dno);

DBMS_OUTPUT.PUT_LINE('Department ID: ' || student_rec.marks);

DBMS_OUTPUT.PUT_LINE('------------------------');

END LOOP;

End;

************************************************************************

We will be using the CUSTOMERS table we had created and used in the
previous chapters.
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+

The following program will update the table and increase the salary of each
customer by 500 and use the SQL%ROWCOUNT attribute to determine the
number of rows affected −

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 500;

IF sql%notfound THEN

dbms_output.put_line('no customers selected');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || ' customers selected ');

END IF;

END;

When the above code is executed at the SQL prompt, it produces the following
result −
6 customers selected

PL/SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have
been updated −
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+

Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control
over the context area. An explicit cursor should be defined in the declaration
section of the PL/SQL Block. It is created on a SELECT Statement which
returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

Working with an explicit cursor includes the following steps −

• Declaring the cursor for initializing the memory

• Opening the cursor for allocating the memory

• Fetching the cursor for retrieving the data

• Closing the cursor to release the allocated memory

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated
SELECT statement. For example −

CURSOR c_customers IS
 SELECT id, name, address FROM customers;

Opening the Cursor

Opening the cursor allocates the memory for the cursor and makes it ready
for fetching the rows returned by the SQL statement into it. For example, we
will open the above defined cursor as follows −

OPEN c_customers;

Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example, we
will fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we
will close the above-opened cursor as follows −

CLOSE c_customers;

Example
Following is a complete example to illustrate the concepts of explicit cursors

DECLARE

c_id customers.id%type;

c_name customerS.No.ame%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id, c_name, c_addr;

 EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

When the above code is executed at the SQL prompt, it produces the following
result −
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP

PL/SQL procedure successfully completed.

TRIGGERS
Triggers are stored programs, which are automatically executed or fired when
some events occur. Triggers are, in fact, written to be executed in response
to any of the following events −

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

• A database definition (DDL) statement (CREATE, ALTER, or DROP).

• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or

SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which
the event is associated.

Benefits of Triggers
Triggers can be written for the following purposes −
 • Generating some derived column values automatically

• Keeping track of table access

• Preventing invalid entries

• Performing validity checks

• Maintaining security

• Preventing invalid transactions

Creating Triggers
The syntax for creating a trigger is −

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

Where,
 • CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing
trigger with the trigger_name.

• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be
executed. The INSTEAD OF clause is used for creating trigger on a view.

• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

• [OF col_name] − This specifies the column name that will be updated.

• [ON table_name] − This specifies the name of the table associated with the
trigger.

• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values
for various DML statements, such as INSERT, UPDATE, and DELETE.

• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be
executed for each row being affected. Otherwise the trigger will execute just once
when the SQL statement is executed, which is called a table level trigger.

• WHEN (condition) − This provides a condition for rows for which the trigger would
fire. This clause is valid only for row-level triggers.

Example
To start with, we will be using the CUSTOMERS table we had created and
used in the previous chapters −
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+

The following program creates a row-level trigger for the customers table
that would fire for INSERT or UPDATE or DELETE operations performed on
the CUSTOMERS table. This trigger will display the salary difference between
the old values and new values −

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.salary - :OLD.salary;

dbms_output.put_line('Old salary: ' || :OLD.salary);

dbms_output.put_line('New salary: ' || :NEW.salary);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

When the above code is executed at the SQL prompt, it produces the following
result −
Trigger created.

The following points need to be considered here −

• OLD and NEW references are not available for table-level triggers, rather you can
use them for record-level triggers.

• If you want to query the table in the same trigger, then you should use the AFTER
keyword, because triggers can query the table or change it again only after the
initial changes are applied and the table is back in a consistent state.

• The above trigger has been written in such a way that it will fire before any DELETE
or INSERT or UPDATE operation on the table, but you can write your trigger on a
single or multiple operations, for example BEFORE DELETE, which will fire
whenever a record will be deleted using the DELETE operation on the table.

Triggering a Trigger
Let us perform some DML operations on the CUSTOMERS table. Here is one
INSERT statement, which will create a new record in the table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in the CUSTOMERS table, the above create

trigger, display_salary_changes will be fired and it will display the
following result −
Old salary:
New salary: 7500
Salary difference:

Because this is a new record, old salary is not available and the above result
comes as null. Let us now perform one more DML operation on the
CUSTOMERS table. The UPDATE statement will update an existing record in
the table −

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

When a record is updated in the CUSTOMERS table, the above create

trigger, display_salary_changes will be fired and it will display the
following result −
Old salary: 1500
New salary: 2000
Salary difference: 500
 EXCEPTION
An exception is an error condition during a program execution. PL/SQL
supports programmers to catch such conditions using EXCEPTION block in
the program and an appropriate action is taken against the error condition.
There are two types of exceptions −

• System-defined exceptions

• User-defined exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list
down as many exceptions as you can handle. The default exception will be
handled using WHEN others THEN −

DECLARE

<declarations section>
BEGIN

<executable command(s)>

EXCEPTION

<exception handling goes here >

WHEN exception1 THEN

exception1-handling-statements

WHEN exception2 THEN

exception2-handling-statements

WHEN exception3 THEN

exception3-handling-statements

........

WHEN others THEN

exception3-handling-statements

END;

Example
Let us write a code to illustrate the concept. We will be using the CUSTOMERS
table we had created and used in the previous chapters −

DECLARE

c_id customers.id%type := 8;

c_name customerS.Name%type;

c_addr customers.address%type;

BEGIN

SELECT name, address INTO c_name, c_addr

FROM customers

WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

When the above code is executed at the SQL prompt, it produces the following
result −
No such customer!

PL/SQL procedure successfully completed.

The above program displays the name and address of a customer whose ID
is given. Since there is no customer with ID value 8 in our database, the
program raises the run-time exception NO_DATA_FOUND, which is
captured in the EXCEPTION block.

Raising Exceptions
Exceptions are raised by the database server automatically whenever there
is any internal database error, but exceptions can be raised explicitly by the
programmer by using the command RAISE. Following is the simple syntax
for raising an exception −

DECLARE

exception_name EXCEPTION;

BEGIN

IF condition THEN

RAISE exception_name;

END IF;

EXCEPTION

WHEN exception_name THEN

statement;
END;

You can use the above syntax in raising the Oracle standard exception or any
user-defined exception. In the next section, we will give you an example on
raising a user-defined exception. You can raise the Oracle standard
exceptions in a similar way.

User-defined Exceptions
PL/SQL allows you to define your own exceptions according to the need of
your program. A user-defined exception must be declared and then raised
explicitly, using either a RAISE statement or the
procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception is −

DECLARE
my-exception EXCEPTION;

Example
The following example illustrates the concept. This program asks for a
customer ID, when the user enters an invalid ID, the exception invalid_id is
raised.

DECLARE

c_id customers.id%type := &cc_id;

c_name customerS.Name%type;

c_addr customers.address%type;

-- user defined exception

ex_invalid_id EXCEPTION;

BEGIN

IF c_id <= 0 THEN

RAISE ex_invalid_id;

ELSE

SELECT name, address INTO c_name, c_addr

FROM customers
 WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

END IF;

EXCEPTION

WHEN ex_invalid_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

When the above code is executed at the SQL prompt, it produces the following
result −
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!

PL/SQL procedure successfully completed.

Pre-defined Exceptions
PL/SQL provides many pre-defined exceptions, which are executed when any
database rule is violated by a program. For example, the predefined
exception NO_DATA_FOUND is raised when a SELECT INTO statement
returns no rows. The following table lists few of the important pre-defined
exceptions −

Oracle
Exception SQLCODE Description
Error
 It is raised when a null object is
ACCESS_INTO_NULL 06530 -6530
automatically assigned a value.

It is raised when none of the

choices in the WHEN clause of a
CASE_NOT_FOUND 06592 -6592
CASE statement is selected, and
there is no ELSE clause.

It is raised when a program

attempts to apply collection
methods other than EXISTS to an
uninitialized nested table or
COLLECTION_IS_NULL 06531 -6531
varray, or the program attempts to
assign values to the elements of
an uninitialized nested table or
varray.

It is raised when duplicate values

DUP_VAL_ON_INDEX 00001 -1 are attempted to be stored in a
column with unique index.

It is raised when attempts are

made to make a cursor operation
INVALID_CURSOR 01001 -1001
that is not allowed, such as closing
an unopened cursor.

It is raised when the conversion of

a character string into a number
INVALID_NUMBER 01722 -1722
fails because the string does not
represent a valid number.

It is raised when a program

attempts to log on to the database
LOGIN_DENIED 01017 -1017
with an invalid username or
password.

It is raised when a SELECT INTO

NO_DATA_FOUND 01403 +100
statement returns no rows.
 It is raised when a database call is
NOT_LOGGED_ON 01012 -1012 issued without being connected to
the database.

It is raised when PL/SQL has an

PROGRAM_ERROR 06501 -6501
internal problem.

It is raised when a cursor fetches

ROWTYPE_MISMATCH 06504 -6504 value in a variable having
incompatible data type.

It is raised when a member

method is invoked, but the
SELF_IS_NULL 30625 -30625
instance of the object type was not
initialized.

It is raised when PL/SQL ran out of

STORAGE_ERROR 06500 -6500 memory or memory was
corrupted.

It is raised when a SELECT INTO

TOO_MANY_ROWS 01422 -1422 statement returns more than one
row.

It is raised when an arithmetic,

VALUE_ERROR 06502 -6502 conversion, truncation, or
sizeconstraint error occurs.

It is raised when an attempt is

ZERO_DIVIDE 01476 1476
made to divide a number by zero.
 PACKAGES
Packages are schema objects that groups logically related PL/SQL types,
variables, and subprograms.

A package will have two mandatory parts −

• Package specification

• Package body or definition

Package Specification
The specification is the interface to the package. It just DECLARES the types,
variables, constants, exceptions, cursors, and subprograms that can be
referenced from outside the package. In other words, it contains all
information about the content of the package, but excludes the code for the
subprograms.

All objects placed in the specification are called public objects. Any
subprogram not in the package specification but coded in the package body
is called a private object.

The following code snippet shows a package specification having a single

procedure. You can have many global variables defined and multiple
procedures or functions inside a package.

CREATE PACKAGE cust_sal AS

PROCEDURE find_sal(c_id customers.id%type);

END cust_sal;

When the above code is executed at the SQL prompt, it produces the following
result −
Package created.

Package Body
The package body has the codes for various methods declared in the package
specification and other private declarations, which are hidden from the code
outside the package.

The CREATE PACKAGE BODY Statement is used for creating the package
body. The following code snippet shows the package body declaration for
the cust_sal package created above. I assumed that we already have
CUSTOMERS table created in our database as mentioned in the PL/SQL -
Variables chapter.

CREATE OR REPLACE PACKAGE BODY cust_sal AS

PROCEDURE find_sal(c_id customers.id%TYPE) IS

c_sal customers.salary%TYPE;

BEGIN

SELECT salary INTO c_sal

FROM customers

WHERE id = c_id;

dbms_output.put_line('Salary: '|| c_sal);

END find_sal;

END cust_sal;

When the above code is executed at the SQL prompt, it produces the following
result −
Package body created.

Using the Package Elements

The package elements (variables, procedures or functions) are accessed with
the following syntax −
package_name.element_name;
Consider, we already have created the above package in our database
schema, the following program uses the find_sal method of
the cust_salpackage −

DECLARE

code customers.id%type := &cc_id;

BEGIN

cust_sal.find_sal(code);

END;

When the above code is executed at the SQL prompt, it prompts to enter the
customer ID and when you enter an ID, it displays the corresponding salary
as follows −
Enter value for cc_id: 1
Salary: 3000

PL/SQL procedure successfully completed.

Example
The following program provides a more complete package. We will use the
CUSTOMERS table stored in our database with the following records −
Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 3000.00 |
| 2 | Khilan | 25 | Delhi | 3000.00 |
| 3 | kaushik | 23 | Kota | 3000.00 |
| 4 | Chaitali | 25 | Mumbai | 7500.00 |
| 5 | Hardik | 27 | Bhopal | 9500.00 |
| 6 | Komal | 22 | MP | 5500.00 |
+----+----------+-----+-----------+----------+

The Package Specification

CREATE OR REPLACE PACKAGE c_package AS

-- Adds a customer

PROCEDURE addCustomer(c_id customers.id%type,

c_name customerS.No.ame%type,
 c_age customers.age%type,

c_addr customers.address%type,

c_sal customers.salary%type);

-- Removes a customer

PROCEDURE delCustomer(c_id customers.id%TYPE);

--Lists all customers

PROCEDURE listCustomer;

END c_package;

When the above code is executed at the SQL prompt, it creates the above
package and displays the following result −
Package created.

Creating the Package Body

CREATE OR REPLACE PACKAGE BODY c_package AS

PROCEDURE addCustomer(c_id customers.id%type,

c_name customerS.No.ame%type,

c_age customers.age%type,

c_addr customers.address%type,

c_sal customers.salary%type)

IS

BEGIN

INSERT INTO customers (id,name,age,address,salary)

VALUES(c_id, c_name, c_age, c_addr, c_sal);

END addCustomer;
 PROCEDURE delCustomer(c_id customers.id%type) IS

BEGIN

DELETE FROM customers

WHERE id = c_id;

END delCustomer;

PROCEDURE listCustomer IS

CURSOR c_customers is

SELECT name FROM customers;

TYPE c_list is TABLE OF customerS.No.ame%type;

name_list c_list := c_list();

counter integer :=0;

BEGIN

FOR n IN c_customers LOOP

counter := counter +1;

name_list.extend;

name_list(counter) := n.name;

dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));

END LOOP;

END listCustomer;

END c_package;

The above example makes use of the nested table. We will discuss the
concept of nested table in the next chapter.

When the above code is executed at the SQL prompt, it produces the following
result −
Package body created.
Using The Package
The following program uses the methods declared and defined in the
package c_package.

DECLARE

code customers.id%type:= 8;

BEGIN

c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);

c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);

c_package.listcustomer;

c_package.delcustomer(code);

c_package.listcustomer;

END;

When the above code is executed at the SQL prompt, it produces the following
result −
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish

PL/SQL procedure successfully completed