oracle 游标cursor-pl

yxsailing

　　游标
　　声明    不占内存
　　打开    申请内存    多行多列
　　获取    每次取一行，
　　关闭
　　隐式游标的属性:
　　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 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 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 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);