--游标
declare
cursor 游标名字
is
查询语句;
begin
其他语句;
end;
--游标的属性
%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT（当前游标的指针位移量）
--FETCH的两种形式
FETCH cursor_name INTO var1, var2, …;
FETCH cursor_name INTO record_var;
--游标的FETCH循环
LOOP
FETCH cursor INTO…
EXIT WHEN cursor%NOTFOUND;
END LOOP
WHILE cursor%FOUND LOOP
FETCH cursor INTO…
END LOOP
FOR var IN cursor LOOP
FETCH cursor INTO…
END LOOP
--如果使用了PL/SQL变量在select_statement中, 变量的声明必须放在游标前面
v_major students.major%TYPE;
DELCARE
CURSOR c_student IS
SELECT first_name, last_name
FROM students
WHERE major = v_major;
--CURSOR可以带参数
DECLARE
CURSOR c_student(p_major students.major%TYPE) --注意返回类型
SELECT *
FROM students
WHERE major = p_major;
BEGIN
OPEN c_student( 101 );
…
--游标举例
declare
cursor cur_teaname
is
select teaname from t_teacher where teatitle='教授';
teanme t_teacher.teaname%type;
result varchar2(100);
begin
open cur_teaname;
loop
fetch cur_teaname into teaname;
exit when cur_teaname%notfound;
result:=result||teaname||' ';
end loop;
dbms_output.put_line(result);
close cur_teaname;
end;
带参数的游标
IKKI@ test10g> edit
Wrote file ././././afiedt.buf
1 declare
2 cursor cust_cursor(p_cust_id int,p_last_name varchar2)
3 is
4 select cust_id,first_name,last_name,credit_limit
5 from customer
6 where cust_id=p_cust_id
7 and last_name=p_last_name;
8 customer_record customer%rowtype;
9 begin
10 open cust_cursor('&id','&lname');
11 loop
12 fetch cust_cursor into customer_record;
13 exit when cust_cursor%notfound;
14 dbms_output.put_line(customer_record.cust_id||':'||customer_record.last_name);
15 end loop;
16 close cust_cursor;
17* end;
IKKI@ test10g> /
Enter value for id: 1
Enter value for lname: smith
old 10: open cust_cursor('&id','&lname');
new 10: open cust_cursor('1','smith');
1:smith
PL/SQL procedure successfully completed.
使用游标更新数据
IKKI@ test10g> edit
Wrote file ././././afiedt.buf
1 declare
2 cursor test_cursor is
3 select ddh,ydrq,jfrq from test for update;
4 test_record test%rowtype;
5 begin
6 open test_cursor;
7 loop
8 fetch test_cursor into test_record;
9 exit when test_cursor%notfound;
10 dbms_output.put_line('ddh:'||test_record.ddh||',ydrq:'||test_record.ydrq||',jfrq:'||test_record.jfrq);
11 if test_record.jfrq-test_record.ydrq>15 then
12 update test set jfrq=ydrq+15 where current of test_cursor;
13 end if;
14 end loop;
15 close test_cursor;
16* end;
IKKI@ test10g> /
ddh:601,ydrq:01-MAY-90,jfrq:30-MAY-90
ddh:600,ydrq:01-MAY-90,jfrq:29-MAY-90
PL/SQL procedure successfully completed.
IKKI@ test10g> select * from test;
DDH YDRQ JFRQ
---------- ------------ ------------
601 01-MAY-90 16-MAY-90
600 01-MAY-90 16-MAY-90
--用for循环简化的游标
for 记录变量名 in 游标名字 loop
代码;
end loop;
create or replace function fun_get_teaname(title varchar2)
return varchar2
as
cursor cur_teaname
is
select teaname from t_teacher where teatitle=title;
result varchar2(100);
begin
for rec in cur_teaname loop
result:=result||rec.teaname||' ';
end loop;
return(result);
end;
--存储过程和游标配合使用
create or replace procedure test1(j emp.job%type)
as
cursor test
is select empno,ename from emp where job=j;
eno emp.empno%type;
ena emp.ename%type;
begin
open test;
loop
fetch test into eno,ena;
exit when test%notfound;
dbms_output.put_line(eno||' '||ena);
end loop;
close test;
end;