---游标:类似于C语言中的指针
游标:分为隐式游标和显式游标
隐式游标:系统自动定义的游标,记录集只有单行数据,用于处理select into 和DML语句
显示游标:即用户自定义游标,专门用于处理select语句返回的多行数据
游标使用的一般过程:
显示游标:声明, 打开, 读取, 关闭
隐式游标:直接使用读取,声明、打开、关闭都是系统自动进行的
----显式游标:包括声明, 打开, 读取, 关闭
declare
cursor cur is
select * from emp;
my_cur cur%rowtype;
begin
open cur;
loop
fetch cur
into my_cur;
exit when cur%notfound;
dbms_output.put_line(my_cur.job);
end loop;
close cur;
end;
---------------
DECLARE
v_deptno emp.deptno%type;
type ename_table_type is table of varchar2(10); --定义PL/SQL表类型
ename_table ename_table_type; --定义PL/SQL表变量存放游标数据
cursor emp_cur is
select ename from emp where deptno = v_deptno;
BEGIN
v_deptno := &inputno;
open emp_cur;
fetch emp_cur bulk collect
into ename_table; --使用bulk collect into提取所有数据
for i in 1 .. ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cur;
END;
---游标的属性:
cursor_name%ISOPEN 游标是否打开
cursor_name%FOUND 最近的FETCH是否提取到数据
cursor_name%NOTFOUND 最近的FETCH是否没有提取到数据
cursor_name%ROWCOUNT 返回到目前为止,已经从游标缓冲区中提取到数据的行数
DECLARE
v_deptno emp.deptno%type type ename_table_type is table of varchar2(10);
ename_table ename_table_type;
cursor emp_cur is
select ename from emp where deptno = v_deptno;
BEGIN
v_deptno := &inputno;
if not emp_cur%isopen then
--判断游标是否打开,如未打开,则打开游标
open emp_cur;
end if;
fetch emp_cur bulk collect
into ename_table;
dbms_output.put_line('All record counts from cursor is : ' ||
emp_cur%rowcount); --使用cursor_name%rowcount 统计游标的记录数
close emp_cur;
END;
----使用游标更新记录
通过游标既可以逐行检索结果集中的记录,又可以更新或删除当前游标行的数据
如果要通过游标更新和删除数据,在定义游标时必须要带有FOR UPDATE子句格式:
CURSOR cursor_name IS select_statement FOR UPDATE [ OF column_reference ][NOWAIT]
FOR UPDATE :子句用于在游标结果集数据上加行共享锁,以防止其它用户在相应行上执行DML操作
OF :子句用于游标子查询到多张表时来确定哪些表要加锁,如未指定,则select语句所引用的全部表将被加锁
NOWAIT :子句指定不等待锁
使用DML语句操作游标中的当前行时,需要在update或delete语句中引用where current of子句
UPDATE tbname set col1=.. WHERE CURRENT OF cursor_name;
DELETE tbname WHERE CURRENT OF cursor_name;
--例:使用游标修改所有记录的工资,根据JOB来作不同的修改。
create table tb_emp as select * from emp;
DECLARE
v_job tb_emp.job%TYPE;
CURSOR emp_cur IS
SELECT job FROM tb_emp FOR UPDATE; --定义时,使用FOR UPDATE
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur
INTO v_job;
EXIT WHEN emp_cur%NOTFOUND;
CASE
WHEN v_job = 'CLERK' THEN
UPDATE tb_emp
SET sal = sal * 1.1
WHERE CURRENT OF emp_cur; --注意,需要使用WHERE CURRENT OF
WHEN v_job = 'SALESMAN' THEN
UPDATE tb_emp
SET sal = sal * 1.08
WHERE CURRENT OF emp_cur;
ELSE
UPDATE tb_emp
SET sal = sal * 1.05
WHERE CURRENT OF emp_cur;
END CASE;
END LOOP;
CLOSE emp_cur;
END;
--例:利用游标删除数据
DECLARE
v_job tb_emp.job%type;
v_sal tb_emp.sal%type;
cursor emp_cur is
select job, sal from tb_emp for update;
BEGIN
open emp_cur;
fetch emp_cur
into v_job, v_sal;
while emp_cur%found loop
if v_sal > 3000 then
delete from tb_emp where current of emp_cur;--注意,需要使用WHERE CURRENT OF
end if;
fetch emp_cur
into v_job, v_sal;
end loop;
close emp_cur;
END;
/
--例:使用OF子句对特定的表加共享锁
DECLARE
cursor emp_cur is
select ename, sal, dname, e.deptno
from emp e
join dept d
on e.deptno = d.deptno
for update of e.deptno;---使用OF子句对特定的表加共享锁
emp_record emp_cur%rowtype;
BEGIN
open emp_cur;
loop
fetch emp_cur
into emp_record;
exit when emp_cur%notfound;
if emp_record.deptno = 20 then
update emp
set sal = sal + 100
where current of emp_cur;
end if;
dbms_output.put_line('Ename: ' || emp_record.ename ||
',Sal: ' || emp_record.sal ||
',Deptname:' || emp_record.dname);
end loop;
close emp_cur;
END;
--例:NOWAIT子句的使用
DECLARE
v_ename emp.ename%type;
v_oldsal emp.sal%type;
cursor emp_cur is
select ename, sal from emp for update nowait; --使用nowait子句指定不等待锁,会给出错误提示
BEGIN
open emp_cur;
loop
fetch emp_cur
into v_ename, v_oldsal;
exit when emp_cur%notfound;
if v_oldsal < 2000 then
update emp
set sal = sal + 200
where current of emp_cur;-----注意,需要使用WHERE CURRENT OF
end if;
end loop;
close emp_cur;
END;
---游标FOR循环
游标FOR循环是为了简化游标使用过程而设计的。
使用游标FOR循环检索游标时,游标的打开、数据提取、数据是否检索到的判断与游标
的关闭都是ORACLE系统自动进行的。
游标FOR循环两种语句格式:
格式一:
先在定义部分定义游标,然后在游标FOR循环中引用该游标
FOR record_name IN cursor_name LOOP
statement1;
statement2;
END LOOP;
格式二:
在FOR循环中直接引用子查询,隐式定义游标
FOR record_name IN subquery LOOP
statement;
END LOOP;
--例:定义游标并使用for循环逐个显示记录(显式游标)
DECLARE
v_job emp.job%TYPE;
CURSOR emp_cur IS
SELECT ename, sal FROM emp WHERE job = v_job;
BEGIN
v_job := '&inputjob';
DBMS_OUTPUT.PUT_LINE('NO. Name Sal');
FOR emp_record IN emp_cur LOOP ----记录名称
DBMS_OUTPUT.PUT_LINE(emp_cur%ROWCOUNT || ' ' ||
emp_record.ename || ' ' ||
emp_record.sal);
END LOOP;
END;
/
--例:直接在游标for循环中使用子查询来逐个显示记录(隐式游标)
DECLARE
v_job emp.job%TYPE;
BEGIN
v_job := '&inputjob';
DBMS_OUTPUT.PUT_LINE('Name Sal');
FOR emp_record IN (SELECT ename, sal FROM emp WHERE job = v_job) LOOP
DBMS_OUTPUT.PUT_LINE(emp_record.ename || ' ' ||
emp_record.sal);
END LOOP;
END;
datatype 只指定数据类型即可,不能指定参数的长度、精度、刻度
打开参数游标:
OPEN cursor_name [(vlaues)]
参数个数、类型必须与定义时的形参相匹配。
对于定义的参数游标,一定要在游标子查询的where子句中指定定义的参数,否则将使得参数游标失去意义
例:用部门编号deptno作形参,显示每个人的姓名和工资
游标变量
简言之,其一是一个游标,其次则是一个变量,因此称之为游标变量,可以用来存储不同的游标
对于游标变量的使用,在打开游标变量时指定其对应的select语句
1.游标变量的使用步骤
a.定义REF CURSOR 类型和游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type]; --必须先定义REF CURSOR类型
cursor_variable ref_type_name; --接下来再定义游标变量
ref_type_name: 指定自定义的类型名
RETURN: 指定REF CURSOR返回结果的数据类型
cursor_variable: 定义游标变量的名字
注:若指定RETURN子句,其数据类型必须是记录类型,此外,不能在包内定义游标变量
b.打开游标
在打开游标时必须指定其对应的select语句,一旦打开游标变量则对应的select结果集将存放到游标变量中
OPEN cursor_variable FOR select_statement;
c.提取数据
提取数据与普通的显示游标提取数据的方法类似
FETCH cursor_variable INTO variable1,...variable2 ; --提取单行数据,需要配合循环语句来使用
FETCH cursor_variable BULK COLLECT INTO collect1,collect2,...[LIMIT rows]; --提取多行数据,collect为集合变量
d.关闭游标变量
CLOSE cursor_vairable;
2.游标变量使用的例子
--例.根据部门名称显示该部门的所有雇员(定义REF CURSOR时不指定RETURN子句)
DECLARE
type emp_cur_type is ref cursor; --定义游标类型为ref cursor
emp_cur emp_cur_type; --定义游标变量为emp_cur
emp_record emp%rowtype; --定义游标变量记录类型为emp_record
v_deptno emp.deptno%type;
BEGIN
v_deptno := &inputno;
open emp_cur for
select * from emp where deptno = v_deptno;
dbms_output.put_line('No Name');
loop
fetch emp_cur
into emp_record;
exit when emp_cur%notfound;
dbms_output.put_line(emp_cur%rowcount || ' ' || emp_record.ename);
end loop;
close emp_cur;
END;
--例:根据部门名称显示该部门的所有雇员名字及薪水(定义REF CURSOR时指定RETURN子句)
DECLARE
type emp_record_type is record(
name varchar2(10),
salary number(6, 2)); --定义PL/SQL记录变量类型
type emp_cur_type is ref cursor return emp_record_type; --定义游标类型为ref cursor,且具有返回类型
emp_cur emp_cur_type; --定义游标变量为emp_cur
emp_record emp_record_type; --定义类型为emp_record_type记录变量emp_record
v_deptno emp.deptno%type;
BEGIN
v_deptno := &inputno;
open emp_cur for
select ename, sal from emp where deptno = v_deptno;
dbms_output.put_line('Name Salary');
loop
fetch emp_cur
into emp_record;
exit when emp_cur%notfound;
dbms_output.put_line(emp_record.name || ' ' || emp_record.salary);
end loop;
close emp_cur;
END;
--例:游标变量的多次使用
DECLARE
type cur_type is ref cursor;
scott_cur cur_type;
v_emp emp%rowtype;
v_dept dept%rowtype;
BEGIN
open scott_cur for
select * from emp where deptno = 10; --使用for select首次打开游标
dbms_output.put_line('No, Name');
loop
fetch scott_cur
into v_emp;
exit when scott_cur%notfound;
dbms_output.put_line(scott_cur%rowcount || ',' || v_emp.ename);
end loop;
open scott_cur for
select * from dept where deptno = 10; --使用for select 再此打开游标,此次加载了不同数据
dbms_output.put_line('Deptno, Name');
loop
fetch scott_cur
into v_dept;
exit when scott_cur%notfound;
dbms_output.put_line(v_dept.deptno || ',' || v_dept.dname);
end loop;
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30018455/viewspace-1399224/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30018455/viewspace-1399224/