数据库开发---常用对象-游标

 

---游标:类似于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语句操作游标中的当前行时,需要在updatedelete语句中引用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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值