Cursor
Context area:
When processing an SQL statement, Oracle creates a temporary work area in
the system memory which contains all the information needed for processing
the statement known as context area.
Cursor:
A cursor is a pointer to context area i.e. Context area is controlled by the
cursor. It is used to fetch and manipulate the data returned by the SQL
statement.
Note: The set of rows the cursor holds is known as active set.
There are two types of cursor in PL/SQL:
1. Implicit cursors: These are created by default when DML statements like,
INSERT, UPDATE, and DELETE statements are executed. They are also
created when a SELECT statement that returns just one row is executed.
Following are the cursor attributes available:
• • SQL%ROWCOUNT – Number of rows returned/changed in the last
executed query. Applicable for SELECT as well as DML statement
• • SQL%ISOPEN – Boolean TRUE if the cursor is still open, else FALSE.
For implicit cursor it is FALSE only
• • SQL%FOUND – Boolean TRUE, if the cursor fetch points to a record,
else FALSE
• • SQL%NOTFOUND – Inverse of SQL%FOUND. The flag is set as
FALSE when the cursor pointer does not point to a record in the result set.
EXAMPLE:
create table customer1(cust_id varchar2(10), name varchar2(20), salary int,
age int, address varchar2(30));
insert into customer1 values('c123','Alex', 10000, 20, 'NY');
insert into customer1 values('c125','Jhon', 80000, 21, 'TX');
insert into customer1 values('c183','Bob', 30000, 22, 'HX');
insert into customer1 values('c173','Ana', 10500, 20, 'AU');
insert into customer1 values('c158','Emily', 15000, 30, 'HT');
insert into customer1 values('c111','Ron', 20000, 28, 'NY');
DECLARE var_rows number(2);
BEGIN
UPDATE customer1
SET salary = salary + 2000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('No record updated.');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line(var_rows || ' records are updated.');
END IF;
END;
Output:
6 records are updated.
2. Explicit cursors: They must be created when you are executing a SELECT
statement that returns more than one row. Even though the cursor stores multiple
records, only one record can be processed at a time, which is called as current row.
When you fetch a row the current row position moves to next row.
General Syntax for creating an explicit cursor is as given below:
CURSOR cursor_name IS select_statement;
How to access an Explicit Cursor?
1. DECLARE the cursor for initialization in the declaration section.
2. OPEN the cursor for memory allocation in the execution section.
3. FETCH the cursor for retrieving data in the execution section.
4. CLOSE the cursor to release allocated memory in the execution section.
Declaring the cursor:
CURSOR cur_customer IS
SELECT name, salary, age FROM customer;
Fetching the cursor:
FETCH cur_customer INTO c_name, c_salary, c_age;
Closing the cursor:
CLOSE cur_customer;
EXAMPLE:
DECLARE
c_salary customer1.salary%type;
c_name customer1.name%type;
c_age customer1.age%type;
CURSOR cur_customer is
SELECT name, salary, age FROM customer1;
BEGIN
OPEN cur_customer;
LOOP
FETCH cur_customer into c_name, c_salary, c_age;
EXIT WHEN cur_customer%notfound;
dbms_output.put_line(c_name || ' ' || c_salary || ' ' || c_age);
END LOOP;
CLOSE cur_customer;
END;
Output
Statement processed.
Alex 12000 20
Alex 12000 20
Alex 12000 20
Jhon 82000 21
Bob 32000 22
Ana 12500 20
Emily 17000 30
Ron 22000 28
Trigger
A database trigger is a stored program which is automatically fired or executed
when some events occur. A trigger can be executed in response to any of the
following events:
1. A database manipulation (DML) statement like DELETE, INSERT or
UPDATE.
2. A database definition (DDL) statement like CREATE, ALTER or DROP.
3. A database operation like SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN.
create table employee(emp_id int, name varchar2(20), age int, salary int );
insert into employee values(1, 'Alex', 23, 10000);
insert into employee values(2, 'Bob', 24, 20000);
insert into employee values(3, 'Ron', 26, 20000);
insert into employee values(4, 'Rio', 28, 90000);
insert into employee values(7, 'aaa', 28, 100000);
CREATE OR REPLACE TRIGGER show_salary_difference
BEFORE DELETE OR INSERT OR UPDATE ON employee
FOR EACH ROW
WHEN (NEW.EMP_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;
/
update employee set salary = 8523000 where emp_id = 2;
Output
1 row(s) updated.
Old salary: 20000
New salary: 8523000
Salary difference: 8503000
