oracle cusor游标,oracle 游标cursor

游标

声明    不占内存

打开    申请内存    多行多列

获取    每次取一行，

关闭

隐式游标的属性:

SQL%ROWCOUNT    成功操作的行的数量

SQL%FOUND       发现复合条件的行返回TRUE

SQL%NOTFOUND    没有发现复合条件的行回TRUE

SQL%ISOPEN      游标打开状态(boolean)

练习 12:打印隐式游标属性

declare

v_count number;

begin

select count(*) into v_count from scott.emp;

dbms_output.put_line(chr(10)||'select return '||sql%rowcount||' rows!');

end;

begin

delete emp;

dbms_output.put_line(chr(10)||sql%rowcount||' rows deleted!');

--rollback;

end;

/

显式游标使用流程:

1.声明 declare

cursor c_name is subquery;

2.打开 open

3.获取 fetch

4.关闭 close

1.简单游标的使用

游标是在open时使用内存的.获取一次,内存中就少一行.

declare

cursor c1 is select ename,sal from scott.emp;

v_ename scott.emp.ename%type;

v_sal   scott.emp.sal%type;

begin

open c1 ;

loop

fetch c1 into v_ename,v_sal;

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

exit when c1%rowcount>5;

end loop;

close c1;

end;

/

declare

cursor c1

is

select employee_id,salary from employees;

v_id employees.employee_id%type;

v_sal employees.salary%type;

begin

open c1;

loop

if c1%notfound then

exit;

end if;

fetch c1 into v_id,v_sal;

dbms_output.put_line('Id is : '||v_id||'  '||'salary is : '||v_sal);

end loop;

close c1;

end;

2.使用游标取表中的所有行所有列

declare

cursor c1 is select * from scott.dept;

v_deptno dept.deptno%type;

v_dname  dept.dname%type;

v_loc  dept.loc%type;

begin

open c1;

loop

fetch c1 into v_deptno,v_dname,v_loc;

exit when C1%NOTFOUND;

dbms_output.put_line(v_deptno||' '||v_dname||' '||v_loc);

end loop;

close c1;

end;

/

3.游标for循环:使用for循环来代替loop循环

for循环中的变量根据in关键字后面的内容而决定变量的类型

如果in后面是数字 则变量是number类型标量变量

如果in后面是游标 则变量是record类型复合变量

declare

cursor c1 is select ename,sal from scott.emp;

begin

for r1 in c1 loop

exit when c1%rowcount>5;

dbms_output.put_line(r1.ename||' '||r1.sal);

end loop;

end;

/

4. 使用游标for循环 可以省略游标的声明

但这种游标for循环不能使用隐式游标属性控制 要自己添加计数器

declare

v_num number := 0;

begin

for r1 in (select ename,sal from scott.emp) loop

-- dbms_output.put_line(SQL%rowcount);

v_num := v_num + 1;

exit when v_num > 5;

dbms_output.put_line(r1.ename||' '||r1.sal);

end loop;

end;

/

set serveroutput on

declare

v_number NUMBER:=1;

begin

for r1 in (select * from hr.employees) loop

--     dbms_output.put_line(sql%rowcount);

if v_number > 5 then

exit;

end if;

dbms_output.put_line(r1.last_name);

v_number := v_number+1;

end loop;

end;

/

5. 参数游标

declare

cursor c1(v_deptno scott.emp.deptno%type)

is

select deptno,ename,sal from scott.emp where deptno=v_deptno;

begin

for r1 in c1 (&deptno) loop

dbms_output.put_line(r1.deptno||' '||r1.ename||' '||r1.sal);

end loop;

end;

/

declare

cursor c1(deptno hr.employees.department_id%type)

is

select last_name,department_id,salary from hr.employees where department_id=deptno;

begin

for emp_rec in c1(&did) loop

dbms_output.put_line('Name: '||emp_rec.last_name||' -- '||'Deptno: '||emp_rec.department_id||' -- '||

'Salary: '||emp_rec.salary);

end loop;

end;

/

练习 1: 基本loop循环+显式游标的使用

DECLARE

v_empno employees.employee_id%TYPE;

v_ename employees.last_name%TYPE;

CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees; --声明

BEGIN

OPEN emp_cursor; --打开

LOOP

FETCH emp_cursor INTO v_empno, v_ename; --获取

exit when emp_cursor%rowcount>20;

DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||' '|| v_ename);

END LOOP;

CLOSE emp_cursor; --关闭

END ;

/

练习 2: for循环+显式游标的使用

DECLARE

v_empno employees.employee_id%TYPE;

v_ename employees.last_name%TYPE;

CURSOR emp_cursor IS SELECT employee_id, last_name FROM employees;

BEGIN

OPEN emp_cursor;

FOR i IN 1..10 LOOP

FETCH emp_cursor INTO v_empno, v_ename;

DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)||' '|| v_ename);

END LOOP;

CLOSE emp_cursor;

END ;

/

游标for循环:

declare

cursor emp_cursor is select rownum,employee_id,last_name from employees; --声明

begin

for emp_record in emp_cursor loop --隐式打开隐式获取

exit when emp_cursor%rowcount>23;

dbms_output.put_line(emp_record.rownum||' '||

emp_record.employee_id||' '||

emp_record.last_name);

end loop; --隐式关闭

end;

/

省略游标定义:

begin

for r in (select last_name from employees) loop

dbms_output.put_line(r.last_name);

end loop;

end;

/

高级显式游标(带参数的游标):

练习 1:通过传入不同的参数使打开游标时取到不同的结果集

declare

cursor c1 (p_deptno number,p_job varchar2)

is

select empno,ename

from emp

where deptno=p_deptno

and job=p_job;

begin

Dbms_output.put_line('first fetch cursor!');

for r_c1 in c1(10,'MANAGER') loop --open cursor时传入不同的实际参数得到不同的游标上下文!

Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);

end loop;

Dbms_output.put_line('second fetch cursor!');

for r_c1 in c1(20,'MANAGER') loop

Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);

end loop;

Dbms_output.put_line('third fetch cursor!');

for r_c1 in c1(30,'MANAGER') loop

Dbms_output.put_line(r_c1.empno||' '||r_c1.ename);

end loop;

end;

/

练习:获取每个部门前两个雇员的信息

获取10部门前两个人的信息

declare

cursor c1 is select * from scott.emp

where deptno=10;

begin

for r1 in c1 loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

end loop;

end;

/

使用替代变量取指定部门的前两个人的信息

declare

cursor c1 is select * from scott.emp

where deptno=&p_deptno;

begin

for r1 in c1 loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

end loop;

end;

/

显示所有部门的工资最高的前两位员工的信息；

declare

cursor dept

is

select distinct department_id from employees order by department_id;

cursor emp(deptno number)

is

select employee_id,department_id,last_name，salary from employees

where department_id = deptno order by salary;

begin

for d in dept loop

for e in emp(d.department_id) loop

exit when emp%rowcount > 2 or emp%notfound;

dbms_output.put_line(e.employee_id||' : ' ||e.department_id ||' : '||e.last_name||' : ' || e.salary);

end loop;

end loop;

end;

/

使用高级游标代替替代变量

declare

cursor c1(p_deptno number) is select * from scott.emp

where deptno=p_deptno;

begin

for r1 in c1(10) loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

end loop;

for r1 in c1(20) loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

end loop;

for r1 in c1(30) loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

end loop;

end;

/

使用循环嵌套简化上面的代码

declare

cursor c2 is select distinct deptno from scott.emp;

cursor c1(p_deptno number) is

select * from scott.emp

where deptno=p_deptno;

begin

for r2 in c2 loop

for r1 in c1(r2.deptno) loop

exit when c1%rowcount=3 or c1%notfound;

dbms_output.put_line(r1.ename||' '||r1.deptno);

end loop;

end loop;

end;

/

练习 2:将每个部门工资小于2000的职员工资涨10%

declare

cursor c1 is select deptno from scott.dept;

cursor c2 (p_deptno number,p_job varchar2)

is

select empno,ename,sal

from emp

where deptno=p_deptno

and job=p_job

for update of sal;

begin

for r_c1 in c1 loop

dbms_output.put_line('第'||c1%rowcount||'次获取游标c1' || '修改'||r_c1.deptno||'部门职员的工资');

for r_c2 in c2(r_c1.deptno,'CLERK') loop  /* 参数游标的好处的就是可以使锁定行更少 更有利于并发 */

if r_c2.sal<2000 then

update scott.emp set sal=sal*1.1

where current of c2;  /* 只修改C2游标所涉及到的行 */

end if;

end loop;

end loop;

end;

/

declare

cursor d1 is select distinct department_id from hr.e;

cursor e1(deptno hr.e.department_id%type)

is

select employee_id,salary from hr.e

where department_id=deptno;

begin

for d_info in d1 loop

for e_info in e1(d_info.department_id) loop

if e_info.salary 

update hr.e set salary=salary*1.1 where employee_id=e_info.employee_id;

end if;

end loop;

end loop;

end;

动态游标：ref游标

强类型的动态游标

弱类型的动态游标

强类型的动态游标的样例：

declare

type emp_record_type is record(

empno employees.employee_id%type,

ename employees.last_name%type);

type emp_cursor is REF CURSOR return emp_record_type;

v_employee emp_record_type;

v_empno employees.salary%type := &no;

v_emp_cursor emp_cursor;

begin

if v_empno > 150 then

open v_emp_cursor for

select employee_id,last_name from employees

where employee_id > 150;

else

open v_emp_cursor for

select employee_id,last_name from employees

where employee_id <= 150;

end if;

fetch v_emp_cursor into v_employee;

while v_emp_cursor%found loop

dbms_output.put_line(v_employee.empno||' : '||v_employee.ename);

fetch v_emp_cursor into v_employee;

end loop;

close v_emp_cursor;

end;

弱类型的动态游标的样例：

declare

type emp_record_type is record(

empno number,

ename varchar2(100));

type emp_cursor is REF CURSOR;

v_employee emp_record_type;

v_no employees.salary%type := &no;

v_emp_cursor emp_cursor;

v_dname varchar2(50);

begin

if v_no > 150 then

open v_emp_cursor for

select employee_id,last_name from employees

where employee_id > 150;

else

open v_emp_cursor for

select department_name from departments

where department_id <= 150;

end if;

if v_no > 150 then

fetch v_emp_cursor into v_employee;

while v_emp_cursor%found loop

dbms_output.put_line(v_employee.empno||' : '||v_employee.ename);

fetch v_emp_cursor into v_employee;

end loop;

close v_emp_cursor;

else

fetch v_emp_cursor into v_dname;

while v_emp_cursor%found loop

dbms_output.put_line(v_dname);

fetch v_emp_cursor into v_dname;

end loop;

close v_emp_cursor;

end if;

end;

有定义游标结构(用return定义)的就是强类型 使用时必须按定义去取值 取值数量与定义匹配

弱类型没有定义游标结构 随SQL返回结果而构建游标的结构 特点是灵活 完全依赖于SQL 容易出错 SQL错游标就错

参照类型(ref cursor 程序间传递结果集)

create or replace package ref_package

as

TYPE emp_record_type IS RECORD

(ename VARCHAR2(25),

job VARCHAR2(10),

sal NUMBER(7,2));

TYPE weak_ref_cursor IS REF CURSOR;--弱类型，不规定返回值

TYPE strong_ref_cursor IS REF CURSOR return emp%rowtype;--强类型，规定返回值

TYPE strong_ref2_cursor IS REF CURSOR return emp_record_type;--强类型，规定返回值

end ref_package;

/

弱类型ref测试: 没有指定游标结构

create or replace procedure test_ref_weak

(p_deptno emp.deptno%type, p_cursor out ref_package.weak_ref_cursor)

is

begin

case p_deptno

when 10 then

open p_cursor for

select empno,ename,sal,deptno

from emp where deptno=p_deptno;

when 20 then

open p_cursor for

select *

from emp where deptno=p_deptno;

end case;

end;

/

var c refcursor

exec test_ref_weak(10,:c); --传入不同形式参数，走不同分支，返回不同结果集！

print c

exec test_ref_weak(20,:c);

print c

*oracle 9i 中定义了系统弱游标类型 sys_refcursor  可以和任何查询相关联

/* Oracle 9i */

create or replace procedure test_p

( p_deptno number, p_cursor out sys_refcursor)

is

begin

open p_cursor for

select *

from   emp

where  deptno = p_deptno;

end test_p;

/

create or replace function getemp

return sys_refcursor

as

emp_cursor sys_refcursor;

begin

open emp_cursor for select * from scott.emp;

return  emp_cursor;

end;

/

select getemp from dual;

强类型ref测试:查询结构必须符合游标返回值结构，否则报错：

PLS-00382: expression is of wrong type

create or replace procedure test_ref_strong

(p_deptno emp.deptno%type, p_cursor out ref_package.strong_ref_cursor)

is

begin

open p_cursor for

select *

from emp where deptno=p_deptno;

end test_ref_strong;

/

var c refcursor

exec test_ref_strong(10,:c);

create or replace procedure test_call

is

c_cursor ref_package.strong_ref_cursor;

r_emp  emp%rowtype;

begin

test_ref_strong(10,c_cursor);

loop

fetch c_cursor into r_emp;

exit when c_cursor%notfound;

dbms_output.put_line(r_emp.ename);

end loop;

close c_cursor;

end test_call;

/

exec test_call;

强类型ref测试:

create or replace procedure test_ref2_strong

(p_deptno emp.deptno%type, p_cursor out ref_package.strong_ref2_cursor)

is

begin

open p_cursor for

select ename,job,sal

from emp where deptno=p_deptno;

end test_ref2_strong;

/

var c refcursor

exec test_ref2_strong(10,:c);