你的位置:首页 > 数据库

[数据库]Oracle学习笔记十一 游标


游标的简介

游标的概念

游标是从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行各种操作,然后将操作结果写回数据表中。

 隐式游标

在PL/SQL中使用DML语句时自动创建隐式游标,即:所有的SQL 语句在上下文区内部都是可执行的,因此都有一个游标指向上下文区,此游标就是所谓的SQL游标(SQL cursor),与显式游标不同,SQL游标不被程序打开和关闭,通过检查隐式游标的属性可以获得最近执行的DML 语句的信息。

 

隐式游标的属性有:SQL+属性
  1.%FOUND – SQL 语句影响了一行或多行时为 TRUE
  2.%NOTFOUND – SQL 语句没有影响任何行时为TRUE
  3.%ROWCOUNT – SQL 语句影响的行数
  4.%ISOPEN  - 游标是否打开,始终为FALSE

 

SET SERVEROUTPUT ONBEGIN  UPDATE toys SET toyprice=270  WHERE toyid= 'P005';  IF SQL%FOUND THEN  --只有在 DML 语句影响一行或多行时,才返回 True    DBMS_OUTPUT.PUT_LINE('表已更新');  END IF;END;

 

 

 

 

SET SERVEROUTPUT ONDECLARE   v_TOYID TOYS.ID%type := '&TOYID';   v_TOYNAME TOYS.NAME%Type := '&TOYNAME';   BEGIN   UPDATE TOYS SET NAME = v_TOYNAME   WHERE toyid=v_TOYID;   IF SQL%NOTFOUND THEN   --如果 DML 语句不影响任何行,则返回 True       DBMS_OUTPUT.PUT_LINE('编号未找到。');   ELSE    DBMS_OUTPUT.PUT_LINE('表已更新');  END IF;END;

 

 

SET SERVEROUTPUT ON BEGIN  UPDATE vendor_master  SET venname= 'Rob Mathew'  WHERE vencode='V004';  DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);  --返回 DML 语句影响的行数END;

 

 

BEGIN  UPDATE rooms SET number_seats = 100   WHERE room_id = 99980;    -- 如果更新没有匹配则插入一新行  IF SQL%ROWCOUNT = 0 THEN      INSERT INTO rooms ( room_id, number_seats )      VALUES ( 99980, 100 ) ;  END IF;END;

 

 

SELECT INTO 语句

SET SERVEROUTPUT ONDECLARE   empid VARCHAR2(10);  desig VARCHAR2(10);BEGIN  empid:= '&Employeeid';  SELECT designation INTO desig     FROM employee WHERE empno=empid;  EXCEPTION    WHEN NO_DATA_FOUND THEN  --如果没有与SELECT INTO语句中的条件匹配的行,将引发NO_DATA_FOUND异常      DBMS_OUTPUT.PUT_LINE('职员未找到');END;

 

 

 

SET SERVEROUTPUT ONDECLARE   empid VARCHAR2(10);BEGIN  SELECT empno INTO empid FROM employee;--给变量多个值  EXCEPTION    WHEN TOO_MANY_ROWS THEN   --如果 SELECT INTO 语句返回多个值,将引发TOO_MANY_ROWS异常      DBMS_OUTPUT.PUT_LINE('该查询提取多行');END;

 

 

 

显式游标

显式游标在 PL/SQL 块的声明部分定义查询,该查询可以返回多行。
 
显式游标的操作过程:

如何使用?

游标的打开操作

  如果要使用创建好的游标,需要先打开游标,语法结构如下:

  open 游标名;

  打开游标的过程有以下两个步骤:

    (1)将符合条件的记录送入内存。

    (2)将指针指向第一条记录的前面。

游标打开了要关闭:CLOSE 游标名;

 

游标提取数据的操作(每次只能提取一行);

  如果要提取游标中的数据,需要使用fetch命令,语法形式如下。

  fetch 游标名 into 变量名1, 变量名2,……;

  或fetch 游标名 into 记录型变量名;

 

示例代码:

set serveroutput on declare   tempsal scott.emp.sal%type;  --定义cursorrecord变量是游标mycursor的记录行变量   cursor mycursor is   select * from scott.emp   where sal>tempsal;   --在游标mycursor的结果中找到sal字段大于800的第一个记录  cursorrecord mycursor%rowtype;  begin   tempsal:=800;  open mycursor;   fetch mycursor into cursorrecord;   dbms_output.put_line(cursorrecord.deptno);   --显示deptno字段的内容 end;

 

 

set serveroutput on declare   tempsal scott.emp.sal%type;   cursor mycursor is   select * from scott.emp   where sal>tempsal;   cursorrecord mycursor%rowtype; begin   tempsal:=800;   if mycursor%isopen then     fetch mycursor into cursorrecord; dbms_output.put_line(to_char(cursorrecord.deptno));   else   dbms_output.put_line('游标没有打开!');   end if; end;

 

 

SET SERVER OUTPUT ONDECLARE  my_toy_price toys.toyprice%TYPE;    CURSOR toy_cur IS    SELECT toyprice FROM toys      WHERE toyprice<250;  --声明游标BEGIN  OPEN toy_cur;   --打开游标  LOOP    FETCH toy_cur INTO my_toy_price;  --提取行    EXIT WHEN toy_cur%NOTFOUND;    DBMS_OUTPUT.PUT_LINE('TOYPRICE=:玩具单价=:'||my_toy_price);  END LOOP;  CLOSE toy_cur;  --关闭游标END;

 

 

带参数的显式游标

声明显式游标时可以带参数以提高灵活性。
声明带参数的显式游标的语法如下:

  CURSOR <cursor_name>(<param_name> <param_type>)

     IS select_statement;

要注意的是:参数定义时,数据类型只能写名字,而不能定义长度!还有,当定义了参数游标后一定要在游标子查询的where子句中引用参数不然就没有意义。

SET SERVEROUTPUT ONDECLARE  desig  VARCHAR2(20);  emp_code VARCHAR2(5);  empnm  VARCHAR2(20);  CURSOR emp_cur(desigparam VARCHAR2) IS    SELECT empno, ename FROM employee      WHERE designation=desigparam;BEGIN  desig:= '&desig';  OPEN emp_cur(desig);  LOOP    FETCH emp_cur INTO emp_code,empnm;    EXIT WHEN emp_cur%NOTFOUND;      DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm);  END LOOP;  CLOSE emp_cur;END;

 

使用显式游标更新行

允许使用游标删除或更新活动集中的行。
声明游标时必须使用 SELECT … FOR UPDATE OF 列名 [nowait]语句. For update用于结果集上加锁,nowait 不等待锁。
  CURSOR <cursor_name> IS    SELECT statement FOR UPDATE;

更新的语法

  UPDATE <table_name>    SET <set_clause>      WHERE CURRENT OF <cursor_name>

删除的语法

DELETE FROM <table_name>  WHERE CURRENT OF <cursor_name>

 

SET SERVEROUTPUT ONDECLARE  new_price NUMBER;  CURSOR cur_toy IS    SELECT toyprice FROM toys WHERE toyprice<100      FOR UPDATE OF toyprice;BEGIN  OPEN cur_toy;  LOOP    FETCH cur_toy INTO new_price;    EXIT WHEN cur_toy%NOTFOUND;    UPDATE toys      SET toyprice = 1.1*new_price        WHERE CURRENT OF cur_toy;  END LOOP;  CLOSE cur_toy;  COMMIT;END;

 

循环游标

循环游标用于简化游标处理代码,当用户需要从游标中提取所有记录时使用,不需要显示打开和关闭游标。
 
循环游标的语法如下:
FOR <record_index> IN <cursor_name>LOOP  <executable statements>END LOOP;

注意: <record_index> 名字可以不需要定义,直接使用,因为是Oracle隐含定义的变量名

 

SET SERVER OUTPUT ONDECLARE   CURSOR mytoy_cur IS    SELECT toyid, toyname, toyprice      FROM toys;BEGIN  FOR toy_rec IN mytoy_cur  LOOP    DBMS_OUTPUT.PUT_LINE('玩具编号:'||' ' ||toy_rec.toyid||' '            ||'玩具名称:'||' '||toy_rec.toyname||' '      ||'玩具单价:'||' '||toy_rec.toyprice);  END LOOP;END;

 

 

REF 游标和游标变量

REF 游标和游标变量用于处理运行时动态执行的 SQL 查询。
创建游标变量需要两个步骤:
  1.声明 REF 游标类型
Type newer_cur is ref cursor; --定义了一个newer_cur的游标类型(弱类型)Type newer_cur is ref cursor return emp%rowtype; --强游标类型,表示这个游标的结果返回的一定是empty类型


  2.声明 REF 游标类型的变量
Mycur newer_cur; --定义变量mycur,它是引用游标类型

 


 
用于声明 REF 游标类型的语法为:
TYPE <ref_cursor_name> IS REF CURSOR [RETURN <return_type>];

 

打开游标变量的语法如下:
OPEN cursor_name FOR select_statement;

 

声明强类型的 REF 游标


TYPE my_curtype IS REF CURSOR RETURN stud_det%ROWTYPE;order_cur my_curtype; 

 

 

声明弱类型的 REF 游标

TYPE my_ctype IS REF CURSOR;stud_cur my_ctype;

 

 

DECLARE   TYPE toys_curtype IS REF CURSOR    RETURN toys%ROWTYPE;  toys_curvar toys_curtype;  toys_rec toys%ROWTYPE;BEGIN  OPEN toys_curvar FOR    SELECT * FROM toys;  FETCH toys_curvar INTO toys_rec;  ...  CLOSE toys_curvar;END;

 

游标变量的优点和限制

游标变量的功能强大,可以简化数据处理。
游标变量的优点有:
  1.可从不同的 SELECT 语句中提取结果集
  2.可以作为过程的参数进行传递
  3.可以引用游标的所有属性
  4.可以进行赋值运算
使用游标变量的限制:
  1.不能在程序包中声明游标变量
  2.FOR UPDATE子句不能与游标变量一起使用
  3.不能使用比较运算符
 
可以使用游标变量执行动态构造的 SQL 语句。
打开执行动态 SQL 的游标变量的语如下:
OPEN cursor_name FOR dynamic_sqlstring [USING bind_argument_list];

 

DECLARE  r_emp emp%ROWTYPE;  TYPE c_type IS REF CURSOR;  cur c_type;  p_salary NUMBER;BEGIN  p_salary := 2500;  OPEN cur FOR 'select * from emp where sal>:1 order by sal desc' USING p_salary;  DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有:');  LOOP    FETCH cur INTO r_emp;  EXIT WHEN cur%NOTFOUND;    DBMS_OUTPUT.PUT_LINE('编号:'|| r_emp.empno      || ' 姓名:' || r_emp.ename|| ' 薪水:' || r_emp.sal );  END LOOP;  CLOSE cur; END;

 

 

其他笔记

显示游标

--更新员工的奖金,如果成功了,则提示成功的信息,失败了则提示失败的信息。

begin  update emp set comm = 1 where empno = 8499; --DML语句  if SQL%found then    dbms_output.put_line('数据已经成功更新了');  else    dbms_output.put_line('数据更新失败');  end if;end;

 

--记录用户登录情况的信息(更新用户的登录信息)

Begin  Update login set ltime = sysdate where name = 'zs';  If sql%notfound then    Insert into login values('zs', sysdate);  End if;End;

--在PL/SQL中显示所有工资大于2000的员工信息

Declare  --定义一个游标,里面保存的是工资大于2000的数据  Cursor mycursor is select * from emp where sal > 2000;  mydata emp%rowtype;Begin  Open mycursor;  --打开游标  Fetch mycursor into mydata; --提取一行数据  Dbms_output.put_line(mydata.empno || ' ' || mydata.ename);  Close mycursor; --关闭游标End;

 

--循环输出

Declare  --定义一个游标,里面保存的是工资大于2000的数据  Cursor mycursor is select * from emp where sal > 2000;  mydata emp%rowtype;Begin  Open mycursor;  --打开游标  loop    Fetch mycursor into mydata; --提取一行数据    If mycursor%notfound then      Exit;    End if;    Dbms_output.put_line(mydata.empno || ' ' || mydata.ename || ' ' || mydate.sal);  End loop;  Close mycursor; --关闭游标End;

 

--使用for循环来操作

Declare  --定义一个游标,里面保存的是工资大于2000的数据  Cursor mycursor is select * from emp where sal > 2000;  mydata emp%rowtype;Begin  For myname in mycursor loop --无需打开关闭和,表示提取一行到myname里面    Dbms_ouptup.put_line(myname.empno || ' ' || myname.ename);  End loop;End;

--使用游标2:列数和顺序要一致

Declare  Cursor mycur is select ename, sal from emp;  Var_row emp%rowtype;  Var_name emp.ename%type;  Var_sal emp.sal%type;Begin  Open mycur;  Loop    --fetch mycur into var_row; --错误 PLS-00394: 在FETCH语句的INTO列表中值数量出现错误    Fetch mycur into var_name, var_sal; -- 使用变量来接收结果    Exit when mycur%notfound;    Dbms_output.put_line(var_name || ' ' || var_sal);  End loop;  Close mycur;End;

--参数游标:在定义游标的时候通过定义参数提高灵活性。

--指定工资参数的游标:

Declare  Cursor cur_sal(vsal number) is select * from emp where sal > vsal;Begin  For row in cur_sal(2000) loop    Dbms_output.put_line(row.ename || ' ' || row.sal);  End loop;End;

 

--可更新游标

--更新Declare  Cursor cur is select * from emp where sal > 2500 for update;Begin  For row in cur loop    Dbms_output.put_line(row.ename || ' ' || row.sal || ' ' || row.comm);    --update emp set comm = 9 where empno = row.empno;    Update emp set comm = 9 where current of cur;    --delete emp where current of cur; --删除游标数据  End loop;End;

REF游标

--使用

Declare  Type newer_cur is ref cursor;  Var_row emp%rowtype;  Var_cur newer_cur;Begin  Open var_cur for select * from emp where sal > 2500;  Loop    Fetch var_cur into var_row;    Exit when var_cur%notfound;    Dbms_output.put_line(var_row.empno || ' ' || var_row.ename || ' ' || var_row.sal);  End loop;End;

--根据员工所在的部门信息来查询数据,如果员工是10部门则查询前5个员工,如果是20部门则查询6-10号,如果是30部门则查询10号以后的员工

Declare  Type mycur is ref cursor;  Cur mycur;  Var_dept int;  Var_n varchar2(20);  Var_sal int;  Var_rn int;Begin  Var_dept := &n;  If var_dept = 10 then    Open cur for select * from (select rownum rn, d.* from (select ename,sal from emp order by sal desc) d ) where rn <=5;  Else if var_dept = 20 then    Open cur for select * from (select rownum rn, d.* from (select ename,sal from emp order by sal desc) d ) where rn > 5 and rn <=10;  Else    Open cur for select * from (select rownum rn, d.* from (select ename,sal from emp order by sal desc) d ) where rn > 10;  End if;  End if;    Loop    Fetch cur into var_rn, var_n, var_sal;    Exit when cur%notfound;    Dbms_output.put_line(var_n || ' ' || var_sal || ' ' || var_rn);  End loop;End;