游标的简介
游标的概念
游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，在游标中有一个数据指针，在初始状态下指向的是首记录，利用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 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 不等待锁。
CURSORIS SELECT statement FOR UPDATE;
更新的语法
UPDATESET WHERE CURRENT OF
删除的语法
DELETE FROMWHERE CURRENT OF
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;
循环游标
循环游标用于简化游标处理代码，当用户需要从游标中提取所有记录时使用，不需要显示打开和关闭游标。
循环游标的语法如下：
FORIN LOOP 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 游标类型的语法为：
TYPEIS REF CURSOR [RETURN ];
打开游标变量的语法如下：
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;