PL/SQL编程之游标使用

 

显示游标的操作步骤：定义游标、打开游标、提取数据、关闭游标

1.定义游标

cursor cursor_name is select_statement;

2.打开游标

open cursor_name;

3.提取数据

语法一：fetch cursor_name into variable1,variable2...;

语法二：fetch cursor_name bulk collect into collect1,collect2,...[limit rows];

4.关闭游标：

close cursor_name;

游标属性：cursor_name%ISOPEN,cursor_name%FOUND

cursor_name%NOTFOUND,cursor_name%ROWCOUNT

Example 1:

declare

cursor emp_cursor is

select ename,sal from emp where dept=10;

v_ename emp.ename%TYPE;

v_sal emp.sal%TYPE;

begin

open emp_cursor;

loop

fetch emp_cursor into v_ename,v_sal;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line(v_ename||': '||v_sal);

end loop;

close emp_cursor;

end;

Example 2(在显示游标中，使用fetch.. bulk collect into语句提取数据):

declare

cursor emp_cursor is

select ename from emp where deptno=10;

TYPE ename_table_type is TABLE OF VARCHAR2(10);

ename_table ename_table_type;

begin

open emp_cursor;

fetch emp_cursor bulk collect into ename_table;

for i in 1.. ename_table.count loop

dbms_output.put_line(ename_table(i));

end loop;

close emp_cursor;

end;

Example 3:(使用游标属性)

declare

cursor emp_cursor is 

select ename from emp where deptno=10;

TYPE ename_table_type is TABLE OF VARCHAR2(10);

ename_table ename_table_type;

begin

if not emp_cursor%ISOPEN then

open emp_cursor;

end if;

fetch emp_cursor bulk collect into ename_table;

dbms_ouptput.put_line('提取的总计行数：'||emp_cursor%ROWCOUNT);

close emp_cursor;

end;

Example 4:(基于游标定义记录变量)

declare

cursor emp_cursor is select ename,sal from emp;

emp_record emp_cursor%ROWTYPE;

begin

open emp_cursor;

loop

fetch emp_cursor into emp_record;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line('雇员名：'||emp_record.ename

||',雇员工资：'||emp_record.sal);

end loop;

close emp_cursor;

end;

Example 5:(参数游标)

定义参数游标时，游标参数只能指定

数据类型，而不能指定长度,定义参数游标语法如下：

cursor cursor_name(parameter_name datatype) is select_statement;

declare

cursor emp_cursor(no number) is 

select ename from emp where deptno=no;

v_ename emp.ename%type;

begin

open emp_cursor(10);

loop

fetch emp_cursor into v_ename;

exit when emp_cursor%NOTFOUND;

dbms_output.put_line(v_ename);

end loop;

close emp_cursor;

end;