Oracle 游标的学习与实例

游标(CURSOR):可以增强SQL语句的功能,它可以对SQL语句的处理进行的控制,方便地帮助我们从数据库中连续撮数据,然后分别对每一条数据进行处理.

游标是一种PL/SQL控制结构,Oracle服务器使用工作区(又称为专用SQL工作区)来执行SQL语句,储存处理信息.

游标分为两种形式:隐式游标与显式游标.

显示游标:
对于返回多条记录的查询语句,可显式游标逐个处理这些数据.

cursor 游标

实例:

  1  declare
  2  cursor c is
  3  select * from emp;
  4  v_emp c%rowtype;
  5  begin
  6  open c;
  7  fetch c into v_emp;
  8  dbms_output.put_line(v_emp.ename);
  9  close c;
10* end;
SQL> /
SMITH

PL/SQL 过程已成功完成。

全部记录循环打印出来:
实例:
  1  declare
  2     cursor c is
  3     select * from emp;
  4     v_emp c%rowtype;
  5  begin
  6     open c;
  7     loop
  8             fetch c into v_emp;
  9             exit when (c%notfound);
10             dbms_output.put_line(v_emp.ename);
11             end loop;
12     close c;
13* end;
SQL> /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL 过程已成功完成。

while循环:

实例:

  1  declare
  2     cursor c is
  3     select * from emp;
  4     v_emp c%rowtype;
  5  begin
  6     open c;
  7     fetch c into v_emp;
  8     while(c%found) loop
  9             dbms_output.put_line(v_emp.ename);
10             fetch c into v_emp;
11             end loop;
12     close c;
13* end;

注意: fetch c into v_emp;在while语句下面的顺序.先打印再fetch

for循环:用的最多的.而且最不容易出错.

游标cursor 不用打开,for循环开始执行时,自动打开并且Open,游标也不用关闭,for循环结束时,自动关闭游标,而且变量v_emp不用声明!

实例:
  1  declare
  2     cursor c is
  3     select * from emp;
  4  begin
  5     for v_emp in c loop
  6             dbms_output.put_line(v_emp.ename);
  7     end loop;
  8* end;
SQL> /
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL 过程已成功完成。

声明游标为可更新的:cursor

实例:

declare
cursor c
is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal <2000) then
--emp2是重emp表复制过来的.
update emp2 set sal = sal*2 where current of c;
elsif(v_temp.sal =5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;


创建存储过程:procedure

create or replace procedure p
is
cursor c
is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal <2000) then
--emp2是重emp表复制过来的.
update emp2 set sal = sal*2 where current of c;
elsif(v_temp.sal =5000) then
delete from emp2 where current of c;
end if;
end loop;
commit;
end;

删除存储过程:

drop procedure p;

函数:function

自定义函数实例:

  1  create or replace function sal_tax
  2  (v_sal number)
  3  return number
  4  is begin
  5  if(v_sal < 2000) then
  6  return 0.10;
  7  elsif(v_sal < 2750) then
  8     return 0.15;
  9  else
10     return 0.20;
11  end if;
12* end;
SQL> /

函数已创建。

触发器:trigger

创建表:

create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);

创建触发器:

create or replace trigger trig
after insert or delete or update on emp2 for each row
begin
if inserting then
insert into emp2_log values(user,'insert',sysdate);
elsif updating then
insert into emp2_log values(user,'update',sysdate);
elsif deleting then
insert into emp2_log values(user,'delete',sysdate);
end if;
end;


关联改变被约束的字段值:(创建触发器trigger的副作用)

create or replace trigger trig
after update on dept
for each row
begin
update emp set deptno =:NEW.deptno where deptno =:OLD.deptno;
end;

update dept set deptno =99 where deptno =10;