PL/SQL Cursor常见用法模板 .
来源:互联网 发布:php好书推荐 编辑:程序博客网 时间:2024/05/16 05:19
1.Loop
- DECLARE
- emp_rec emp%rowtype;
- CURSOR emp_cur IS
- SELECT *
- FROM emp;
- BEGIN
- OPEN emp_cur;
- LOOP
- FETCH emp_cur INTO emp_rec;
- EXIT WHEN emp_cur%NOTFOUND;--这个必须有,否则buffer overflow
- dbms_output.put_line (emp_rec.ename || ' ' ||emp_rec.sal );
- END LOOP;
- CLOSE emp_cur;
- END;
DECLARE emp_rec emp%rowtype; CURSOR emp_cur IS SELECT * FROM emp;BEGIN OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND;--这个必须有,否则buffer overflow dbms_output.put_line (emp_rec.ename || ' ' ||emp_rec.sal ); END LOOP; CLOSE emp_cur;END;
注:emp_rec必须和emp_cur保持结构一致
2.For Loop
- DECLARE
- CURSOR emp_cur IS
- SELECT * FROM emp;
- BEGIN
- FOR emp_rec in emp_cur LOOP
- dbms_output.put_line(emp_rec.empno || ' ' ||emp_rec.ename || ' ' ||emp_rec.sal);
- END LOOP;
- END;
DECLARE CURSOR emp_cur IS SELECT * FROM emp;BEGIN FOR emp_rec in emp_cur LOOP dbms_output.put_line(emp_rec.empno || ' ' ||emp_rec.ename || ' ' ||emp_rec.sal); END LOOP;END;对于FOR LOOP,无需声明一个record或者一个变量来存储Cursor的值,并且无需显示的Open,Fetch,Close游标
3.For Loop
- DECLARE
- BEGIN
- FOR emp_rec in (SELECT * FROM emp) LOOP
- dbms_output.put_line( emp_rec.empno || ' ' ||emp_rec.ename || ' ' ||emp_rec.sal);
- END LOOP;
- END;
DECLARE BEGIN FOR emp_rec in (SELECT * FROM emp) LOOP dbms_output.put_line( emp_rec.empno || ' ' ||emp_rec.ename || ' ' ||emp_rec.sal); END LOOP; END;
4.FETCH cursor BULK COLLECT INTO a collection
- DECLARE
- TYPE NameList IS TABLE OF emp.ename%TYPE;
- TYPE SalList IS TABLE OF emp.sal%TYPE;
- names NameList;
- sals SalList;
- CURSOR c1 IS SELECT ename,sal FROM emp WHERE job = 'CLERK';
- l_index NUMBER;
- BEGIN
- OPEN c1;
- FETCH c1 BULK COLLECT INTO names,sals;
- l_index := names.FIRST;
- LOOP
- EXIT WHEN l_index IS NULL;
- dbms_output.put_line(names(l_index)||','||sals(l_index));
- EXIT WHEN l_index = names.LAST;
- l_index := names.NEXT(l_index);
- END LOOP;
- CLOSE c1;
- END;
DECLARE TYPE NameList IS TABLE OF emp.ename%TYPE; TYPE SalList IS TABLE OF emp.sal%TYPE; names NameList; sals SalList; CURSOR c1 IS SELECT ename,sal FROM emp WHERE job = 'CLERK'; l_index NUMBER;BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO names,sals; l_index := names.FIRST; LOOP EXIT WHEN l_index IS NULL; dbms_output.put_line(names(l_index)||','||sals(l_index)); EXIT WHEN l_index = names.LAST; l_index := names.NEXT(l_index); END LOOP; CLOSE c1;END;The BULK COLLECT clause lets you fetch entire columns from the result set, or the entire result set at once.
- PL/SQL Cursor常见用法模板
- PL/SQL Cursor常见用法模板 .
- pl/sql cursor example
- PL/SQL--Cursor
- PL/SQL中cursor(光标/游标)的用法
- PL/SQL中cursor(光标/游标)的用法
- pl/sql cursor within a cursor 例子
- PL/SQL 动态CURSOR运用
- Oracle PL/SQL cursor(游标)
- PL/SQL中的游标(cursor)
- SQL Cursor 基本用法
- SQL Cursor 基本用法
- SQL Cursor 基本用法
- SQL Cursor 基本用法
- SQL(一) cursor用法
- SQL Cursor 基本用法
- SQL Cursor 基本用法
- SQL Cursor 基本用法
- oracle获取当年第一天和最后一天
- 07-09 03:16:08.655: E/Database(426): android.database.sqlite.DatabaseObjectNotClosedException: Appli
- 使用Visual Studio Profiler分析程序性能
- UIScrollView详解
- JDBC 增改查
- PL/SQL Cursor常见用法模板 .
- DWRProxy.js中o is null 问题
- FreeMarker生成word文档
- spring security插件--获取 登录前被拦截的URL
- Ajax响应中文乱码 [SpringMVC使用@ResponseBody处理Ajax请求]
- 在Ubuntu 10.10下安装JDK配置Eclipse及Tomcat
- 【转载】jpa 注解2
- SpringMVC注解说明
- cocos2d-x学习笔记(20)-- box2d入门