Oracle Lesson 6 游标管理
来源：互联网 发布：javascript !== 编辑：程序博客网 时间：2024/05/01 09:44
Oracle Lesson 6 游标管理
Made by Zhangxu 2008.3.3
游标简介
游标的类型
游标主要分为如下几种
如图：
当select语句从数据库中返回的记录多与一条时，我们可以考虑使用游标来解决！！
隐式游标
在PL/SQL中使用DML语句时系统自动创建隐式游标
隐式游标自动声明、打开和关闭，其名为 SQL
通过检查隐式游标的属性可以获得最近执行的DML 语句的信息
隐式游标的属性有：
– %FOUND – SQL 语句影响了一行或多行时为 TRUE
– %NOTFOUND – SQL 语句没有影响任何行时为TRUE
– %ROWCOUNT – SQL 语句影响的行数
– %ISOPEN - 游标是否打开，始终为FALSE
代码示例：
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
UPDATE toys SET toyprice=270
WHERE toyid= 'P005';
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘表已更新');
END IF;
END;
/
注：只有在DML语句影响一行或者多行时。才返回true
SQL> SET SERVEROUTPUT ON
SQL> 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
DBMS_OUTPUT.PUT_LINE('编号未找到。');
ELSE
DBMS_OUTPUT.PUT_LINE(‘表已更新');
END IF;
END;
/
注：如果 DML 语句不影响任何行，则返回 True
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
UPDATE vendor_master
SET venname= 'Rob Mathew'
WHERE vencode='V004';
DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);
END;
/
注：返回DML语句影响的行数
SELECT INTO 语句
（1）注意：如果没有与SELECT INTO语句中的条件匹配的行，将引发NO_DATA_FOUND异常
代码示例：
SQL> SET SERVEROUTPUT ON
SQL> 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;
/
（2）注意：如果 SELECT INTO 语句返回多个值，
将引发TOO_MANY_ROWS异常
代码示例
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
empid VARCHAR2(10);
BEGIN
SELECT empno INTO empid FROM employee;
EXCEPTION
WHEN TOO_MANY_ROWS THEN--（2）注意：如果 SELECT INTO 语句返回多个值，
将引发TOO_MANY_ROWS异常！！
DBMS_OUTPUT.PUT_LINE('该查询提取多行');
END;
/
显示游标
显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行
显式游标的操作过程：
1.声明一些变量，用于保存SELECT语句返回的例的值！
2.声明游标，并指定SELECT语句
3.打开游标
4.从游标中提取数据
5.关闭游标
代码示例：
SQL>SET SERVEROUTPUT ON –-能看到输出的数据
SQL>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;
带参数的显示游标
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
desig VARCHAR2(20);
emp_code VARCHAR2(5);
empnm VARCHAR2(20);
CURSOR emp_cur(desigparam VARCHAR2) IS
SELECT empno, ename FROM employee
WHERE designation=desig;
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语句
CURSOR <cursor_name> IS
SELECT statement FOR UPDATE;
UPDATE <table_name>
SET <set_clause>
WHERE CURRENT OF <cursor_name>
代码示例：
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
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;
代码示例：
SQL> SET SERVER OUTPUT ON
SQL> 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 查询的结果
• REF 游标和游标变量用于处理运行时动态执行的 SQL 查询
• 创建游标变量需要两个步骤：
– 声明 REF 游标类型
– 声明 REF 游标类型的变量
• 用于声明 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;
示例：
SQL> DECLARE
TYPE toys_curtype IS REF CURSOR—声明REF游标类型
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;
游标变量的优点和限制
游标变量的优点有：
可从不同的 SELECT 语句中提取结果集
可以作为过程的参数进行传递
可以引用游标的所有属性
可以进行赋值运算
使用游标变量的限制：
不能在程序包中声明游标变量
FOR UPDATE子句不能与游标变量一起使用
不能使用比较运算符
使用游标变量执行动态 SQL
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;
总结
游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标
在声明 REF 游标时，不需要将 SELECT 语句与 其关联
附录：部分源码
---------------------------隐式游标：-------------------------------------------------------------------
---隐式游标的名称为SQL。不能为sql游标显示执行open,fetch,close语句
---所有操作都是隐式完成
declare
v_rows number;
begin
update student set name='roses' where id=2;
v_rows:=SQL%rowcount;
dbms_output.put_line('把所有编号为2的学生名字修改为roses');
end;
---隐式游标的属性
--SQL%ROWCOUNT 返回的行数只为1 修改影响的行数
--SQL%NOTFOUND 修改没有结果
-------------------------------------------------------------
--------------游标的使用--------------------------------------
declare
type sturecord_type is record
(
id student.id%type,
name student.name%type,
age student.age%type
);
sturecord sturecord_type;
name student.name%type;
age student.age%type;
---声明游标
cursor c1
is
select name,age from student where id=1;
cursor c2(stu_no int)--有参数的游标
is
select name,age from student where id=stu_no;
cursor c3(stu_no int) -- 有返回值的游标
return sturecord
is
select id,name,age from student where id=stu_no;
cursor c4(stu_no int)---基于游标定义记录变量
is
select name,age from student where id=stu_no;
sturecord_2 c4%rowtype;
begin
open c3(2);--打开游标
loop
fetch c3 into sturecord;--提取游标
if c3%found then
dbms_output.put_line(sturecord.name);
else
exit;
end if;
end loop;
end;
-------------------------------------------------------------
------------------显式游标属性--------------------------------
cursor_name%isopen 如果游标打开为真否则为假
cursor_name%found 如果最近一次提取游标操作fetch成功则为真，否则为假
在游标打开之后，提取之前访问%found为null，当游标打开之前访问%found将引起异常
cursor_name%notfound 如果最近一次提取游标操作fetch失败为true,否则为false;
cursor_name%rowcount 返回最近一次提取到数据行的序号
-------------------------------------------------------------
-------------使用游标更新数据----------------------------------
declare
stu_rec student%rowtype;
cursor c1
is
select * from student for update;
begin
open c1;
loop
fetch c1 into stu_rec;
exit when c1%notfound;
if stu_rec.age>=30 then
update student set id=10 where current of c1;
end if;
end loop;
commit;
close c1;
end;
-----------------------------------------------------------------------------游标for循环-----------------------------------
declare
type sturecord_type is record
(
id student.id%type,
name student.name%type,
age student.age%type
);
sturecord sturecord_type;
cursor c1(num_no int default 1)
is
select * from student where id=num_no;
begin
dbms_output.put_line('给ID传递参数为2');
for sturecord in c1(2) loop
dbms_output.put_line(sturecord.name);
end loop;
end;
------------------------------------------------------------------------作用域---------------------------------------------
<<block>>
declare
v_1 varchar2(20):='global v1';
begin
dbms_output.put_line(v_1);
<<subblock_1>>
declare
v_1 varchar2(20):='subblock_1 local v1';
begin
dbms_output.put_line(block.v_1);
dbms_output.put_line(v_1);
end;
end;
-------------------------------------------------------------
--------------------record-----------------------------------
declare
type stu_record_type is record
(
id student.id%type,
name student.name%type,
age student.age%type
);
stu_record stu_record_type;
begin
select id,name,age into stu_record from student
where id=&id;
dbms_output.put_line('编号'||stu_record.id);
dbms_output.put_line('姓名'||stu_record.name);
dbms_output.put_line('年龄'||stu_record.age);
end;
-------------------------------------------------------------
- Oracle Lesson 6 游标管理
- ORACLE游标管理
- Oracle游标管理
- Oracle游标管理
- ORACLE数据库 游标管理基础
- ORACLE数据库 游标管理基础
- Oracle 游标的使用与管理
- oracle调优 浅析有效的游标管理
- 游标管理
- Oracle游标
- Oracle游标
- ORACLE 游标
- Oracle 游标
- oracle 游标
- oracle 游标
- oracle 游标
- Oracle游标
- ORACLE游标
- SQLite语法 ATTACH DATABASE
- SQLite语法 BEGIN TRANSACTION
- 关于C语言中的结构体对齐问题
- Oracle Lesson 5 PL/SQL 编程
- Byte Alignment
- Oracle Lesson 6 游标管理
- 从PDF说开去
- spring中以dataSource方式使用proxool连接池
- Ever-Flowers IN Never-Dream(19)
- 今天又上课了
- 正式火热登录CSDN
- Z 半角和全角互换
- 建立QQ2440的交叉工具链(cygwin环境)
- 失恋成就事业
