1. 游标: 容器,存储SQL语句影响行数。
2. 游标类型: 隐式游标,显示游标,REF游标。其中,隐式游标和显示游标属于静态游标(运行前将游标与SQL语句关联),REF游标属于动态游标(运行时将游标与SQL语句关联)。
3. 隐式游标: DML语句对应的游标,由Oracle自动管理,也称SQL游标。(所有的DML操作都被Oracle内部解析为一个cursor名为SQL的隐式游标)
q 隐式游标的属性有:
q %FOUND – SQL 语句影响了一行或多行时为 TRUE
q %NOTFOUND – SQL 语句没有影响任何行时为TRUE
q %ROWCOUNT – SQL 语句影响的行数
q %ISOPEN - 游标是否打开,始终为FALSE
举例说明:
Sql代码
1. declare
2.
3. v_empno emp.empno %type:=7000;
4.
5. begin
6.
7. update emp set ename='fxe' where empno=v_empno;
8.
9. if SQl%found then
10.
11. dbms_output.put_line(SQL%rowcount||'Delete Ok!');
12.
13. end if;
14.
15. if SQL%notfound then -- 一条SQL就表示是一个隐式游标
16.
17. dbms_output.put_line('雇员编号'||v_empno||'不存在');
18.
19. end if;
20.
21. end;
4. 显示游标操作:
(1)声明游标(关联SQL语句) cursor+游标名 is/as sql语句
(2)打开游标(执行SQL语句,填充游标) open+游标名
(3)提取游标的行 fetch 游标名 into 行类型变量
(4)关闭游标 close+游标名
举例说明:
Sql代码
1. declare
2.
3. cursor emp_cur is select * from emp; --声明无参显示游标 关联SQL
4.
5. empRecord emp%rowtype; --表示类型为一条记录
6.
7. begin
8.
9. open emp_cur; --打开游标
10.
11. loop
12.
13. fetch emp_cur into empRecord; --取出emp_cur 这个游标所对应的记录放入empRecord变量里
14.
15. exit when emp_cur%notfound; --当没记录时,退出循环
16.
17. dbms_output.put_line(empRecord.ename); --打印ename
18.
19. end loop;
20.
21. close emp_cur;
22.
23. end;
5. 带参数的显示游标
例:
Sql代码
1. declare
2.
3. destination varchar2(20);
4.
5. cursor emp_cur(dest varchar2) --声明带参显示游标
6.
7. is select * from emp where empno=dest; --关联到SQL
8.
9. empRecord emp%rowtype; -- 行类弄的变量empRecord
10.
11. begin
12.
13. destination:=&empno; --弹出用户输入框
14.
15. open emp_cur(destination); --打开游标
16.
17. loop
18.
19. fetch emp_cur into empRecord; -- 循环每一游标所对应的记录放入empRecord
20.
21. exit when emp_cur%notfound;
22.
23. dbms_output.put_line(empRecord.ename);
24.
25. end loop;
26.
27. close emp_cur;
28.
29. end;
----输入参数:7369
6. 使用显式游标更新行
q 允许使用游标删除或更新活动集中的行
q 声明游标时必须使用 SELECT … FOR UPDATE语句
例:
Sql代码
1. declare
2.
3. old_sal number(4);
4.
5. emp_name varchar2(20);
6.
7. cursor emp_cur is select ename,sal from emp --声明显示游标,关联SQL
8.
9. where sal<1000
10.
11. for update of sal; -- 定义的游标会让数据库对涉及的行(对应的列)加锁,别的会话如果要访问该游标中的行便会进入等待状态。
12. --如果别的会话一直不解锁,那么你的select就会一直等待下去,如果你不想等,只需在for update后面加上nowait就可以解决这个问题了,这样你的选择会立即返回。
13. begin
14.
15. open emp_cur;
16.
17. loop
18.
19. fetch emp_cur into emp_name,old_sal;
20.
21. exit when emp_cur%notfound;
22.
23. update emp set sal=1.1*old_sal --更新数据
24.
25. where current of emp_cur; --作用范围就只在你循环的当前行的范围中了
26.
27. dbms_output.put_line('更新成功!');
28.
29. end loop;
30.
31. end;
7.循环游标
q 循环游标用于简化游标处理代码
q 当用户需要从游标中提取所有记录时使用
q 循环游标的语法如下:
Sql代码
1. FOR <record_index> IN <cursor_name>
2.
3. LOOP
4.
5. <executable statements>
6.
7. END LOOP;
FOR <record_index> IN <cursor_name>
LOOP
<executable statements>
END LOOP;
例:
Java代码
1. declare
2.
3. cursor emp_cur is select empno,ename,sal from emp; --显示游标
4.
5. begin
6.
7.
8. for empRecord in emp_cur --empRecord 表示每行,emp_cur 所有行
9.
10. loop
11.
12. dbms_output.put_line(empRecord.empno
13.
14. ||empRecord.ename||empRecord.sal);
15.
16. end loop;
17.
18. end;
8.REF游标和游标变量
q REF 游标和游标变量用于处理运行时动态执行的 SQL 查询
q 创建游标变量需要两个步骤:
q 声明 REF 游标类型
q 声明 REF 游标类型的变量
q 用于声明 REF 游标类型的语法为:
TYPE <ref_cursor_name> IS REF CURSOR
[RETURN <return_type>];
q 打开游标变量的语法如下:
OPEN cursor_name FOR select_statement;
声明强类型的REF游标
type emp_cur is ref cursor return emp%rowtype;
empRecord emp_cur;
声明弱类型的REF游标
type emp_cur is ref cursor;
empRecord emp_cur;
例:
Sql代码
1. DECLARE
2.
3. TYPE emp_cur IS REF CURSOR
4.
5. RETURN emp%ROWTYPE; -- 声明强类型的REF游标(只能返回emp结构类弄的游标)
6.
7. empObj emp_cur; --声明 REF 游标类型的变量
8.
9. empRecord emp%ROWTYPE; --行数据类开的变量
10.
11. BEGIN
12.
13. OPEN empObj FOR --打开ref类型的游标
14.
15. SELECT * FROM emp;
16.
17. loop
18.
19. FETCH empObj INTO empRecord; --一行一行取出
20.
21. exit when empObj%notfound;
22.
23. dbms_output.put_line(empRecord.ename);
24.
25. end loop;
26.
27. CLOSE empObj;
28.
29. END;
9.游标变量的优点和限制
q 游标变量的功能强大,可以简化数据处理。
q 游标变量的优点有:
q 可从不同的 SELECT 语句中提取结果集
q 可以作为过程的参数进行传递
q 可以进行赋值运算
q 使用游标变量的限制:
q 不能在程序包中声明游标变量(这儿指的不是声明游标)
q FOR UPDATE子句不能与游标变量一起使用
q 不能使用比较运算符
今天遇到的问题:
当使用带参数的显示游标时,比如说使用的参数为输入参数,不能在SQL语句中直接写输入参数名字,否则解析不到,视为无效。
正确的写法:DECLARE
CURSOR cSafeID(report_year2 NUMBER) IS
SELECT t.accident_remark
FROM nssrf_v_s_monthly_report t
WHERE t.report_year =report_year2;
BEGIN
OPEN cSafeID(report_year);
------------------其中report_year为输入参数
错误的写法:
DECLARE
CURSOR cSafeID IS
SELECT t.accident_remark
FROM nssrf_v_s_monthly_report t
WHERE t.report_year =report_year;
BEGIN
OPEN cSafeID;
-----------------语句中解析不到report_year的值
ORACLE游标管理
最新推荐文章于 2024-09-13 16:52:38 发布