使用游标

使用显式游标以及游标属性;显式游标更新或删除数据;参数游标;游标FOR循环;游标变量;FETCH ...BULK  COLLECT   INTO语句和CURSOR表达式。

 

 

为了处理SELECT语句返回的多行数据,可以使用显式游标,使用显示游标包括定义游标、打开游标、提取数据和关闭游标四个阶段。

1)定义游标。在使用显式游标之前,必须首先在定义部分定义游标。定义游标用于指定游标所对应的SELECT语句。

CURSOR  cursor_name  IS  select_statement;

2)打开游标。当打开游标时,Oracle会执行游标所对应的SELECT语句,并且将SELECT语句的结果暂时存放到结果集中。

OPEN cursor_name

3)提取数据。为了处理结果集中的数据,需要使用FETCH语句提取游标数据。使用FETCH语句每次只能提取一行数据,通过使用FETCH...BULK  COLLECT  INTO语句,每次可以提取多行数据。

语法一:FETCH   cursor_name  INTO  variable1,variable2...

语法二:FETCH   cursor_name  BULK  COLLECT  INTO  collect1,collect2,...[LIMIT rows];

variable用于指定接收游标数据的变量;collect用于指定接收游标结果的集合变量。当使用语法一时,必须要使用循环语句处理结果集的所有数据。

4)关闭游标。

CLOSE  cursor_name;

 

 

显示游标属性用于返回显式游标的执行信息,这些属性包括%ISOPEN、%FOUND、%NOTFOUND和%ROWCOUNT。

 

 

--在显式游标中使用FETCH...INTO语句

 

DECLARE

  CURSOR emp_cursor IS

    SELECT ename,sal FROM emp WHERE deptno=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;

 

 

 

--在显示游标中,使用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;

 

 

 

--在显示游标中,使用FETCH...BULK  COLLECT  INTO...LIMIT语句提取部分数据

 

DECLARE

  TYPE name_array_type IS VARRAY(5) OF VARCHAR2(10);

  name_array name_array_type;

  CURSOR emp_cursor IS SELECT ename FROM emp;

  rows INT:=5;

  v_count INT:=0;

BEGIN

  OPEN emp_cursor;

  LOOP

    FETCH emp_cursor BULK COLLECT INTO name_array LIMIT rows;

    dbms_output.put('雇员名:');

    FOR i IN 1..(emp_cursor%ROWCOUNT-v_count) LOOP

      dbms_output.put(name_array(i)||' ');

    END LOOP;

    dbms_output.new_line;

    v_count:=emp_cursor%ROWCOUNT;

    EXIT WHEN emp_cursor%NOTFOUND;

  END LOOP;

  CLOSE emp_cursor;

END;

 

 

 

--使用游标属性

 

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_output.put_line(emp_cursor%ROWCOUNT);

  CLOSE emp_cursor;

END;

 

 

 

--基于游标定义记录变量

 

DECLARE

  CURSOR emp_cursor IS

    SELECT ename, sal FROM emp;

  emp_record emp_cursor%ROWTYPE;

BEGIN

  IF NOT emp_cursor%ISOPEN THEN

    OPEN emp_cursor;

  END IF;

  LOOP

    FETCH emp_cursor

      INTO emp_record;

    EXIT WHEN emp_cursor%NOTFOUND;

    dbms_output.put_line('雇员名:' || emp_record.ename || ' ,雇员工资:' ||

                         emp_record.sal);

  END LOOP;

END;

 

 

 

--参数游标

 

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;

 

 

 

使用游标更新或删除数据

CURSOR  cursor_name(parameter_name  datatype)  IS  select_statement  FOR  UPDATE [OF  column_reference][NOWAIT];

FOR  UPDATE子句用于在游标结果集数据上加行共享锁,以防止其他用户在相应行上执行DML操作,当SELECT语句引用到多张表时,使用OF子句可以确定哪些表要加锁,如果没有OF子句,则会在SELECT语句所引用的全部表上加锁;NOWAIT子句用于指定不等待锁。在提取了游标数据之后,为了更新或删除当前游标行数据,必须在UPDATE或DELETE语句中引用WHERE  CURRENT  OF子句。

 

--使用游标更新数据

 

DECLARE

  CURSOR emp_cursor IS

    SELECT ename,sal FROM emp FOR UPDATE;

  v_ename emp.ename%TYPE;

  v_oldsal emp.sal%TYPE;

BEGIN

  OPEN emp_cursor;

  LOOP

    FETCH emp_cursor INTO v_ename,v_oldsal;

    EXIT WHEN emp_cursor%NOTFOUND;

    IF v_oldsal<2000 THEN

      UPDATE emp set sal=sal+100 WHERE CURRENT OF emp_cursor;

    END IF;

    dbms_output.put_line(v_ename);

  END LOOP;

  CLOSE emp_cursor;

END;

 

 

 

--使用OF子句在特定表上加行共享锁

 

DECLARE

  CURSOR emp_cursor IS

    SELECT ename, sal, dname, emp.deptno

      FROM emp, dept

     WHERE emp.deptno = dept.deptno

       FOR UPDATE OF emp.deptno;

  emp_record emp_cursor%ROWTYPE;

BEGIN

  OPEN emp_cursor;

  LOOP

    FETCH emp_cursor

      INTO emp_record;

    EXIT WHEN emp_cursor%NOTFOUND;

    IF emp_record.deptno = 30 THEN

      UPDATE emp set sal = sal + 100 WHERE CURRENT OF emp_cursor;

    END IF;

    dbms_output.put_line('雇员名:' || emp_record.ename || ' ,工资:'

                         ||emp_record.sal);

  END LOOP;

  CLOSE emp_cursor;

END;

 

 

 

--使用NOWAIT子句

 

DECLARE

  CURSOR emp_cursor IS

    SELECT ename,sal FROM emp FOR UPDATE NOWAIT;

  v_ename emp.ename%TYPE;

  v_oldsal emp.sal%TYPE;

BEGIN

  OPEN emp_cursor;

  LOOP

    FETCH emp_cursor INTO v_ename,v_oldsal;

    EXIT WHEN emp_cursor%NOTFOUND;

    IF v_oldsal<2000 THEN

      UPDATE emp set sal=sal+100 WHERE CURRENT OF emp_cursor;

    END IF;

    dbms_output.put_line(v_ename);

  END LOOP;

  CLOSE emp_cursor;

END;

 

 

 

游标FOR循环。Oracle会隐含地打开游标、提取游标数据并关闭游标。

 

DECLARE

  CURSOR emp_cursor IS

    SELECT ename, sal FROM emp;

BEGIN

  FOR emp_record IN emp_cursor LOOP

    dbms_output.put_line('第' || emp_cursor%ROWCOUNT || '个雇员:' ||

                         emp_record.ename);

  END LOOP;

END;

 

 

 

--在游标FOR循环中直接使用子查询

 

BEGIN

  FOR emp_record IN (SELECT ename, sal FROM emp) LOOP

    dbms_output.put_line(emp_record.ename);

  END LOOP;

END;

 

 

 

使用游标变量。其使用步骤:定义游标变量、打开游标、提取游标数据、关闭游标。

1)首先必须定义REF  CURSOR类型,然后才能定义游标变量。

TYPE  ref_type_name  IS  REF  CURSOR[RETURN  return_type];

cursor_variable  ref_type_name;

下面的步骤同显式游标...

 

--在定义REF  CURSOR类型时不指定RETURN子句

 

DECLARE

   TYPE emp_cursor_type IS REF CURSOR;

   emp_cursor emp_cursor_type;

   emp_record emp%ROWTYPE;

BEGIN

   OPEN emp_cursor FOR SELECT * FROM emp where deptno=10;

   LOOP

     FETCH emp_cursor INTO emp_record;

     EXIT WHEN emp_cursor%NOTFOUND;

     dbms_output.put_line(emp_record.ename);

   END LOOP;

   CLOSE emp_cursor;

END;

 

 

 

--在定义REF  CURSOR类型时指定RETURN子句

 

DECLARE

   TYPE emp_record_type IS RECORD(name VARCHAR(10),salary NUMBER(6,2));

   TYPE emp_cursor_type IS REF CURSOR RETURN emp_record_type;

   emp_cursor emp_cursor_type;

   emp_record emp_record_type;

BEGIN

   OPEN emp_cursor FOR SELECT ename,sal FROM emp where deptno=20;

   LOOP

     FETCH emp_cursor INTO emp_record;

     EXIT WHEN emp_cursor%NOTFOUND;

     dbms_output.put_line(emp_record.name||' '||emp_record.salary);

   END LOOP;

   CLOSE emp_cursor;

END;

 

 

 

使用CURSOR表达式,用于返回嵌套游标

 

DECLARE

  TYPE refcursor IS REF CURSOR;

  CURSOR dept_cursor(no NUMBER) IS

    SELECT a.dname,

           CURSOR (SELECT ename, sal FROM emp WHERE deptno = a.deptno)

      FROM dept a

     WHERE a.deptno = no;

  empcur refcursor;

  v_dname dept.dname%TYPE;

  v_ename emp.ename%TYPE;

  v_sal emp.sal%TYPE;

BEGIN

  OPEN dept_cursor(&no);

  LOOP

    FETCH dept_cursor INTO v_dname,empcur;

    EXIT WHEN dept_cursor%NOTFOUND;

    dbms_output.put_line('部门名:'||v_dname);

    LOOP

      FETCH empcur INTO v_ename,v_sal;

      EXIT WHEN empcur%NOTFOUND;

      dbms_output.put_line('雇员名:'||v_ename||', 工资:'||v_sal);

    END LOOP;

  END LOOP;

  CLOSE dept_cursor;

END;

 

 

 

 

 

 

 

 

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值