Cursor in PL/SQL (1)
• If a query may result in more than one
tuple, a cursor needs to be used.
• Define a cursor:
cursor cursor_name is
select_statement;
Example: cursor c1 is
select cid, cname, city
from customers;
Working with Cursor (1)
• open cursor_name;
• fetch cursor_name
into record_or_variable-list;
• close cursor_name;
Working with Cursor (2)
declare
cursor c1 is
select cid, cname, city from customers;
c1_rec c1%rowtype;
/* %rowtype is used to define a
PL/SQL record type that corresponds
to the schema of a table.
*/
Working with Cursor (3)
begin
open c1;
fetch c1 into c1_rec;
dbms_output.put_line(c1_rec.cid || ‘, ‘ ||
c1_rec.cname || ‘, ‘ || c1_rec.city);
close c1;
end;
/
Cursor Attributes
• c1%notfound returns true if the most
recent fetch failed to obtain a row.
• c1%found returns true if the most
recent fetch obtained a row.
• c1%rowcount returns the total
number of rows fetched so far.
• c1%isopen returns true if cursor c1 is
open.
Using Cursor Attributes
begin
if (not c1%isopen) then
open c1;
end if;
fetch c1 into c1_rec;
while c1%found loop
dbms_output.put_line(c1_rec.cid || ‘, ‘ ||
c1_rec.cname || ‘, ‘ || c1_rec.city);
fetch c1 into c1_rec;
end loop;
close c1;
end;
/
Cursor For Loop
begin
for c1_record in c1 loop
if (c1_record.city = ‘New York’) then
dbms_output.put_line(c1_record.cid || ‘, ‘ ||
c1_record.cname || ‘, ‘ ||
c1_record.city);
end if;
end loop;
end;
/
• open, fetch and close are all done implicitly.
• No need to declare c1_record.
Cursor for Update (1)
• Use the for update clause to declare cursor:
cursor c1 is
select cid, cname, city
from customers for update;
• Use of the cursor.
update customers
set city = ‘Boston’
where current of c1;
delete from customers
where current of c1;
Cursor for Update (2)
declare
cursor c1 is
select * from customers for update;
begin
for c1_rec in c1 loop
if (c1_rec.city = ‘New York’) then
delete from customers
where current of c1;
end if;
end loop;
end;
Parameterized Cursor
cursor cursor_name (parameter_list) is
select_statement;
Example:
cursor c1(d_name in
Students.dept_name%type) is
select age, avg(GPA) from Students
where dept_name = d_name
group by age;
open c1(‘Computer Science’);
Built-in Package: dbms_output (1)
• dbms_output is primarily used to help
debugging PL/SQL programs.
• dbms_output has the following public
procedures:
– disable (dbms_output.disable;): It disables
the dbms_output package.
– enable: It enables the dbms_output
package. Use dbms_output.enable(20000);
to set the buffer size to be 20,000 bytes. The
default buffer size is 2,000.
Built-in Package: dbms_output (2)
– put_line( ): puts information into the
buffer and starts a new line.
– put( ): puts information into the buffer.
– new_line: starts a new line.
– get_line(line, status): retrieves one line of
information from the buffer and stores
the information in variable line.
• status = 0 if successful
• status = 1 if unsuccessful
Trigger (1)
• A trigger is an event-driven block of
PL/SQL code.
• An event could be an insertion of a tuple
into a table, a deletion of an existing tuple
from a table, and an update of a table.
• A trigger fires (executes, activates) when
an event occurs.
• Triggers are useful for enforcing various
integrity constraints and business rules.
Trigger (2)
ECA Model:
an event yes Is the event yes take
detected? significant? actions
insert need to satisfy execute
delete additional SQL/PL
update condition block
Event Condition Action
Trigger (3)
create or replace trigger raise_sal
before update of salary on employees
for each row
when (new.salary > old.salary * 1.2)
begin
dbms_output.put_line(‘Old salary is ‘ ||
:old.salary || ‘, ‘ || ‘New salary is ‘ ||
:new.salary);
dbms_output.put_line(‘The raise is too high!’);
end;
Trigger (4)
• Trigger event
update of salary on employees
insert on employees
delete or insert on employees
• trigger timing
before: fires the trigger before executing
the triggering statement
after: fires the trigger after executing
the triggering statement
Trigger (5)
• row trigger
– for each row is specified
– fire the trigger once for each row that is
affected by the event and satisfies the
additional condition in the when clause
• statement trigger
– for each row is not specified
– fire the trigger once for the entire
trigger event
Trigger (6)
• trigger restriction for row trigger
when new.salary > old.salary * 1.2
– new is applicable for insert and update
– old is applicable for update and delete
– use :new.salary and :old.salary in
trigger body
• trigger body
– the PL/SQL block containing trigger
action.
Trigger(7)
create or replace trigger raise_sal
after delete on employees
declare
total_sal number;
begin
select sum(salary) into total_sal
from employees;
dbms_output.put_line(‘The new
total salary is ’ || total_sal);
end;
Trigger Applications (1)
• Use trigger for monitoring changes
Example: add a log entry each time the price
of a product is changed
log table for product:
create table products_log
(pid char(4),
username char(10),
update_date date,
old_price number(6, 2),
new_price number(6, 2));
Trigger Applications (2)
create or replace trigger update_p_price
after update of price on products
for each row
begin
insert into products_log values
(:old.pid, user,
sysdate, :old.price, :new.price);
end;
Trigger Applications (3)
• Use trigger to enforce integrity constraints
Example: If a student is removed, delete all
enrollments by the student.
create or replace trigger stud_enroll
after delete on students
for each row
begin
delete from enrollments where sid = :old.sid;
end;
