Oracle 游标的学习与实例

[size=medium] 游标(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;[/size]