Oracle学习笔记十一游标

游标的简介

游标的概念

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

逐行处理查询结果，以编程的方式访问数据。

游标的定义

游标作为一种数据类型，首先必须进行定义，其语法如下：

cursor 游标名 is select 语句;

cursor是定义游标的关键词，select是建立游标的数据表查询命令。

declare
　　cursor  c1  is 
　　　　select  ename, sal  from emp  where rownum<11;  --定义游标
　　v_ename varchar2(10);
　　v_sal number(7,2);
begin
  open  c1;  --打开游标
  fetch  c1  into  v_ename, v_sal;  --fetch游标，读取数据
  while  c1%found 
 loop
  dbms_output.put_line(v_ename||to_char(v_sal) );
  fetch  c1  into v_ename, v_sal;
end loop;
close c1;  --关闭游标
end;

游标的类型

隐式游标

在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 ON
BEGIN
    UPDATE toys SET toyprice=270
    WHERE toyid= 'P005';
    IF SQL%FOUND THEN    --只有在 DML 语句影响一行或多行时，才返回 True
        DBMS_OUTPUT.PUT_LINE('表已更新');
    END IF;
END;

SET SERVEROUTPUT ON
DECLARE
      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 ON
DECLARE 
    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 ON
DECLARE 
　　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 ON
DECLARE
　　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 ON
DECLARE
　　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 ON
DECLARE 
　　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;

posted @ 2015-11-28 22:59 平平平平平阅读(638) 评论(0) 编辑收藏举报

刷新页面返回顶部

平步青云

纸上得来终觉浅，绝知此事要躬行。

Oracle学习笔记十一游标

游标的简介

游标的概念

游标的定义

游标的类型

隐式游标

显式游标

如何使用？

带参数的显式游标

使用显式游标更新行

循环游标

REF 游标和游标变量

游标变量的优点和限制

其他笔记

显示游标

REF游标

公告

平步青云

纸上得来终觉浅，绝知此事要躬行。

Oracle学习笔记十一 游标

游标的简介

游标的概念

游标的定义

游标的类型

隐式游标

显式游标

如何使用？

带参数的显式游标

使用显式游标更新行

循环游标

REF 游标和游标变量

游标变量的优点和限制

其他笔记

显示游标

REF游标

公告

Oracle学习笔记十一游标