- 浏览: 1130930 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (411)
- Java Foundation (41)
- AI/机器学习/数据挖掘/模式识别/自然语言处理/信息检索 (2)
- 云计算/NoSQL/数据分析 (11)
- Linux (13)
- Open Source (12)
- J2EE (52)
- Data Structures (4)
- other (10)
- Dev Error (41)
- Ajax/JS/JSP/HTML5 (47)
- Oracle (68)
- FLEX (19)
- Tools (19)
- 设计模式 (4)
- Database (12)
- SQL Server (9)
- 例子程序 (4)
- mysql (2)
- Web Services (4)
- 面试 (8)
- 嵌入式/移动开发 (18)
- 软件工程/UML (15)
- C/C++ (7)
- 架构Architecture/分布式Distributed (1)
最新评论
-
a535114641:
LZ你好, 用了这个方法后子页面里的JS方法就全不能用了呀
页面局部刷新的两种方式:form+iframe 和 ajax -
di1984HIT:
学习了,真不错,做个记号啊
Machine Learning -
赵师傅临死前:
我一台老机器,myeclipse9 + FB3.5 可以正常使 ...
myeclipse 10 安装 flash builder 4.6 -
Wu_Jiang:
触发时间在将来的某个时间 但是第一次触发的时间超出了失效时间, ...
Based on configured schedule, the given trigger will never fire. -
cylove007:
找了好久,顶你
Editable Select 可编辑select
Oracle中Cursor介绍:
http://www.iteye.com/topic/649874
游标小记(收集汇总):
http://www.itpub.net/viewthread.php?tid=897079&highlight=%D3%CE%B1%EA
REF CURSOR:
Oracle REF CURSOR:
http://www.oradev.com/ref_cursor.jsp
Using Ref Cursors To Return Recordsets:
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
REF CURSOR 小结:
http://www.itpub.net/viewthread.php?tid=443352&highlight=ref%2Bcursor
IS CURSOR DIFFERENT FROM REF CURSOR?
http://forums.oracle.com/forums/thread.jspa?threadID=1086826
REF CURSOR需要显式(explicitly)关闭吗?
http://forums.oracle.com/forums/thread.jspa?threadID=627238
http://searchoracle.techtarget.com/answer/Closing-ref-cursor-after-it-s-used-in-a-Java-program
使用oracle REF CURSOR的例子(scott用户):
关于上面代码中的SYS_REFCURSOR:
查看数据库中打开的游标数:http://www.iteye.com/topic/337796
将 %ROWTYPE 与游标配合使用:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.plsql.doc/doc/c0053880.html
Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor:
http://www.techonthenet.com/oracle/questions/cursor1.php
http://www.iteye.com/topic/649874
游标小记(收集汇总):
http://www.itpub.net/viewthread.php?tid=897079&highlight=%D3%CE%B1%EA
REF CURSOR:
Oracle REF CURSOR:
http://www.oradev.com/ref_cursor.jsp
引用
With the REF_CURSOR you can return a recordset/cursor from a stored procedure.
Using Ref Cursors To Return Recordsets:
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
引用
Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages.
REF CURSOR 小结:
http://www.itpub.net/viewthread.php?tid=443352&highlight=ref%2Bcursor
IS CURSOR DIFFERENT FROM REF CURSOR?
http://forums.oracle.com/forums/thread.jspa?threadID=1086826
REF CURSOR需要显式(explicitly)关闭吗?
http://forums.oracle.com/forums/thread.jspa?threadID=627238
引用
Ref cursors otoh are special - as they have no local scope. Only session scope. So as long as that Oracle session exist, that ref cursor created in that session will exist, until explicitly closed. Why? Because ref cursors are intended to be use by the client of that session - which means that irrespective of what happens scope wise on the PL/SQL engine side, that ref cursor handle must "survive" as it can (and often is) used by the client.
This is also the primary cause for cursor leakage. Clients (especially Java apps in my experience) use ref cursors - but forget to close them after use. The open cursor handle count quickly runs up and an ORA error results.. with the Java developers then thinking there is something wrong with Oracle and that the max number of cursor handles per session should be increased.
The oracle's garbage collector does not clean up ref cursor handles - those are only closed either explicitly (you need to code the close) or when the session terminates.
Also, ref cursors are intended for clients to use - not for PL/SQL. So whenever you use a ref cursor in PL/SQL only, you need to ask why. The java/Delphi/C client's ref cursor equivalent in PL/SQL is a DBMS_SQL cursor - that supports a proper call interface, including a describe interface.
Ask The Oracle Expert: Questions & Answers->Closing ref cursor after it's used in a Java program:
This is also the primary cause for cursor leakage. Clients (especially Java apps in my experience) use ref cursors - but forget to close them after use. The open cursor handle count quickly runs up and an ORA error results.. with the Java developers then thinking there is something wrong with Oracle and that the max number of cursor handles per session should be increased.
The oracle's garbage collector does not clean up ref cursor handles - those are only closed either explicitly (you need to code the close) or when the session terminates.
Also, ref cursors are intended for clients to use - not for PL/SQL. So whenever you use a ref cursor in PL/SQL only, you need to ask why. The java/Delphi/C client's ref cursor equivalent in PL/SQL is a DBMS_SQL cursor - that supports a proper call interface, including a describe interface.
http://searchoracle.techtarget.com/answer/Closing-ref-cursor-after-it-s-used-in-a-Java-program
引用
Q:I am sending a variable of ref cursor type to a Java program. Now, how do I close the ref cursor? Does the conn.close in Java close the ref cursors? Is there any way I can explicitly close the same after it is used in Java?
A:With the ref cursor, you're doing everything you need to. That is the way it is done. The caller (your Java program) is responsible for the "close" call -- you don't do that in PL/SQL when the client fetches from the cursor. There's nothing for you to explicitly do any different than you mention.
A:With the ref cursor, you're doing everything you need to. That is the way it is done. The caller (your Java program) is responsible for the "close" call -- you don't do that in PL/SQL when the client fetches from the cursor. There's nothing for you to explicitly do any different than you mention.
使用oracle REF CURSOR的例子(scott用户):
CREATE OR REPLACE PROCEDURE p_refcursor_test ( in_field IN VARCHAR2, in_value IN VARCHAR2, rslt_cur OUT SYS_REFCURSOR ) IS str_sql VARCHAR2(300); TYPE t_EmpRecord IS RECORD( ename emp.ename%TYPE, job emp.job%TYPE, sal emp.sal%TYPE ); v_EmpRecord t_EmpRecord; BEGIN str_sql := 'SELECT ename,job,sal FROM emp '; IF(length(in_field)>0) and (length(in_value)>0) THEN str_sql := str_sql || 'WHERE ' || in_field || ' like ''%' || in_value || '%'''; END IF; dbms_output.put_line(str_sql); --execute immediate str_sql; --动态执行 OPEN rslt_cur FOR str_sql; --如果这个procedure是被java程序调用的(如ibatis中的<procedure>),则这里不可以fetch该rslt_cur,要将下面这个loop循环给注掉!!!否则返回给java ResultSet的结果集将会是空的 LOOP FETCH rslt_cur INTO v_EmpRecord; EXIT WHEN rslt_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('名字: ' || v_EmpRecord.ename || ' 工作: ' || v_EmpRecord.job || ' 薪水: ' || v_EmpRecord.sal ); END LOOP; --With the ref cursor, you're doing everything you need to. That is the way it is done. The caller (your Java program) is responsible for the "close" call -- you don't do that in PL/SQL when the client fetches from the cursor EXCEPTION when others then DBMS_OUTPUT.PUT_LINE('------>' ||SQLERRM); END p_refcursor_test;
关于上面代码中的SYS_REFCURSOR:
引用
sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。 9i之前想要使用ref cursor是要自己定义REF CURSOR类型游标变量的
查看数据库中打开的游标数:http://www.iteye.com/topic/337796
引用
查看游标使用情况:
查看游标执行的sql情况:
select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'user ' and o.sid=s.sid group by o.sid, osuser, machine order by num_curs desc;
查看游标执行的sql情况:
select q.sql_text from v$open_cursor o, v$sql q where q.hashvalue=o.hash_value and o.sid = 123;
将 %ROWTYPE 与游标配合使用:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.apdv.plsql.doc/doc/c0053880.html
Oracle/PLSQL: Procedure that outputs a dynamic PLSQL cursor:
http://www.techonthenet.com/oracle/questions/cursor1.php
发表评论
-
Oracle: minus | in | exists
2012-09-05 13:49 1401解释及例子: MINUS Query: http://www. ... -
一个奇怪的Oracle sql问题
2011-01-13 16:13 1309select A.M,B.N from Table1 A ... -
Oracle Analytic Functions:RANK, DENSE_RANK, FIRST and LAST;PARTITION BY
2010-12-13 17:02 1280Oracle/PLSQL: Rank Function: ht ... -
Oracle Analytic Functions:RANK, DENSE_RANK, FIRST and LAST
2010-12-13 17:02 1222Oracle/PLSQL: Rank Function: ht ... -
Oracle:Collections Records Type %TYPE %ROWTYPE
2010-11-09 22:27 1232PL/SQL Collections and Records: ... -
Oracle 锁机制
2010-09-19 20:12 3682Oracle多粒度封锁机制研究: http://www.itp ... -
Oracle Data Dictionary 数据字典
2010-09-19 16:44 1484Oracle数据字典查阅: http://download.o ... -
Oracle Sign Function
2010-09-17 14:52 1436Oracle/PLSQL: Sign Function: ht ... -
Oracle Built-In Functions: Next_Day and Last_Day
2010-09-16 17:09 1490next_day(date,char): 它用来返回从第一个 ... -
Oracle Procedure 存储过程
2010-09-16 08:36 1316Oracle/PLSQL: Creating Procedur ... -
Oracle Exception Handle 异常处理
2010-09-15 13:00 2043Handling PL/SQL Errors: http:// ... -
Oracle 性能工具 : Explain plan、Autotrace、Tkprof
2010-09-14 18:07 2193Oracle: 三个内置的性能工具包 Explain plan ... -
关于Oracle数据和对象的导入导出 [转]
2010-09-14 10:25 1226关于Oracle数据和对象的导入导出 [转]: http:// ... -
Oracle jobs(DBMS_JOB and DBMS_SCHEDULER)
2010-07-21 14:14 7678写PL/SQL procedure的时候,一定要写的够健壮、够 ... -
Oracle 各种注释
2010-07-20 14:19 3576为SQL语句添加注释: http://do ... -
Oracle 监听 本地Net服务名 配置
2010-07-20 10:32 1288Oracle数据库配置: http://shupili1410 ... -
[Oracle]Difference between a database and an instance(数据库 实例 区别)
2010-07-20 09:31 1457Difference between a database a ... -
Oracle Bulk Collect
2010-07-16 10:03 1345On BULK COLLECT: http://www.ora ... -
Oracle/PLSQL: FOR Loop 循环语句
2010-07-15 16:43 9279Oracle/PLSQL: FOR Loop: http:// ... -
Oracle Trigger 触发器
2010-06-09 16:37 1780备忘速查: oracle的update insert dele ...
相关推荐
ORACLE技术文档\oracle cursor 游标.docORACLE技术文档\oracle cursor 游标.docORACLE技术文档\oracle cursor 游标.doc
oracle笔记游标的使用,游标的详细代码案例,游标知识点笔记!
详细介绍了 oracle的游标使用 及 实例
修改后的oracle游标总结,比较的全面 修改后的oracle游标总结,比较的全面 修改后的oracle游标总结,比较的全面
oracle数据库忽然连不上了,一查是游标数量超了。 1.查看游标数 show parameter open_cursors 2.修改游标数 alter system set open_cursors = 10000; 3.获取打开的游标数 select o.sid, osuser, machine, count...
隐式游标(Implicit Cursor):是指非PL/SQL程序中定义的、而且是在PL/SQL中使用UPDATE/DELETE语句时,Oracle系统自动分配的游标。 一.显示游标 1.使用步骤 (1)定义 (2)打开 (3)使用 (4)关闭 2.使用演示 首先创建...
oracle数据库中的游标cursor在应用中具有极高的实用性。
你也在为LatchFree、Librarycache争用那些事头疼吗,快来看看本文作者是怎么解决的。
2. 显示游标是用cursor...is命令定义的游标,它可以对查询语句(select)返回的多条记录进行处理;隐式游标是在执行插入 (insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的...
ORACLE 游标的相关操作,轻轻松松学会ORACLE cursor
在开发过程中可能会声明一个含有某张表不具备字段的游标,来解决特殊问题,本文将详细介绍这类问题,需要了解更多的朋友可以参考下其中,data.*是一张表,然后把其他表中的字段也加到sal_data的游标中 Sql代码 代码...
1.情景展示 一共有22w条数据, 需要将A表的主键更新至B表的指定字段,如何快速完成更新?... FOR TEMP_CURSOR IN (SELECT T2.ID, T2.ID_CARD FROM VIRTUAL_CARD10 T1, PRIMARY_INDEX10 T2 WHERE
在Oracle 9i及其以后的版本中,还增加了使用BULK COLLECT子句批量绑定数据和使用CURSOR表达式实现嵌套游标的功能。本章将主要介绍如何使用显式游标进行多行数据的查询、游标FOR循环以及游标变量的使用,另外还将介绍...
本文实例讲述了Oracle出现超出打开游标最大数的解决方法。分享给大家供大家参考,具体如下: Java代码在执行conn.createStatement()和conn.prepareStatement()的时候,实际上都是相当与在数据库中打开了一个cursor。...
Oracle 遍历游标的四种方式(for、fetch、while、bulk collect+forall) 2.问题分析 我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据? 3.解决方案 ...
Oracle 在模块中经常会使用游标进行数据相关操作,循环使用颇多。。。
oracle中游标的使用方法,便于初学者参考,精通人员欢迎指正在。
CURSOR cur_emp IS SELECT * FROM emp; row_emp cur_emp%ROWTYPE; BEGIN OPEN cur_emp; FETCH cur_emp INTO row_emp; WHILE cur_emp%FOUND LOOP dbms_output.put_line(row_emp.empno||'----'||row_emp.ename); FETCH...
本文实例讲述了Oracle存储过程游标用法。分享给大家供大家参考,具体如下: 使用游标的5个步骤 1、声明一些变量用于保存select语句返回的指 2、声明游标,并指定select 语句 3、打开游标 4、从游标中获取记录 5、...
Cursor2LOB 是一组 Oracle PL/SQL 例程,它们创建(或附加到)Oracle CLOB 或 BLOB,这些数据包含来自 Cursor(即来自表或查询)的数据,这些数据采用 CSV 或固定列格式。 此外,还有能够将 CSV 数据写入(或附加)...