create or replace procedure testprodurce is cursor c_row is select * from fpkj; ref_c_row sys_refcursor; -- ref_c_row kplsh varchar2(200); kplx number; begin for tmp in c_row loop dbms_output.put_line(tmp.kplsh); end loop; /* execute immediate 'select kplsh,kplx from fpkj where kplx=:1' into ref_c_row using 2; 不能into到cursor,只能类似count(*)into到number类型中 */ open ref_c_row for 'select kplsh,kplx from fpkj where kplx=:1' using 2; loop fetch ref_c_row into kplsh, kplx; exit when ref_c_row%notfound; dbms_output.put_line(kplsh || '---->' || kplx); end loop; close ref_c_row; declare ct_fpkj number; begin execute immediate 'select count(*) from fpkj where kplx=:1' into ct_fpkj using 2; dbms_output.put_line(ct_fpkj); end; end testprodurce;
相关推荐
在数据开发中,有时你需要合并两个动态游标sys_refcursor。 开发一个存储过程PROC_A,这个过程业务逻辑相当复杂,代码篇幅较长。一段时间后要开发一个PROC_B,要用PROC_A同样的逻辑,而且在这个过程中,还要循环调用...
有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为 in out 或out (1)声明个人系统游标.(推荐) 代码如下: create or replace p_temp_procedure ( cur_arg out sys_refcursor;...
第一种情况是返回的游标是某个具体的表或视图的数据,如: SQL-Code: 代码如下: CREATE OR REPLACE PROCEDURE P_TESTA ( PRESULT OUT SYS_REFCURSOR ) AS BEGIN OPEN PRESULT FOR SELECT * FROM USERS; END P_TESTA;...
CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR,MYCS2 OUT SYS_REFCURSOR,a out varchar)as BEGIN a:='test'; OPEN MYCS1 FOR SELECT 1 from dual; OPEN MYCS2 FOR SELECT 2 from dual; END; ...
文章目录游标1.1 显式游标1.1.1 创建游标1.1.2 打开游标1.1.3 获取数据1.1.4 关闭游标1.1.5 使用实例1.2 隐式游标1.2.2 游标属性1.3 ...详情参考oracle中cursor与refcursor及sys_refcursor的区别 1.1 显式游标 1.1.1 创
* cursor_var:游标变量,必须是SYS_REFCURSOR类型,已经定义并打开,符合特定于语言的名称规则 CLOSE语句的作用是关闭游标(或游标变量),使其对于除OPEN或FREE(或OPEN FOR)之外的任何语句无用,并释放数据库...
procedure Pro_GetBillNO(TypeTable in varchar2,cur_mycursor out sys_refcursor) as DReceiptCode varchar2(40); DReceiptName varchar2(50); DPrefix1 varchar2(50); DISO varchar2(50); DIsAutoCreate varchar2...
主要介绍了oracle常用sql查询语句部分,并用图文并茂的方式为大家进程实例说明,需要的朋友可以参考下
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中经过第一次编译后再次调用不需要再次编译,...CREATE OR REPLACE PROCEDURE gd_CURSOR(MYCS1 OUT SYS_REFCURSOR
RESULTLIST OUT SYS_REFCURSOR, V_ID IN NUMBER, V_NAME IN VARCHAR2 ) AS ISSQL_STR VARCHAR2(500); BEGIN SQL_STR := 'SELECT * FROM EMP WHERE 1=1 '; DBMS_OUTPUT.put_line('V_ID=' || V_ID || ' V_NAME='...
my_cur sys_refcursor; val varchar2(1000); v_year varchar2(50); v_max_part_name varchar2(256); v_maxdate_part_name varchar2(256); v_part_tablespace varchar2(256); v_part_sums int; begin for v_...
Type DynamicCursor is ref Cursor; --动态游标 --Purpose : 获得汉字拼音编码 Type ut_PYIndex_191_List is Varray( 191 ) OF Number; Type ut_PYIndex_List is Varray( 10 ) OF ut_PYIndex_191_...
#pragma extref _floatconvert This will force inclusion of floating point formats, which may not be linked to reduce executable size. COMPILER - The default extension for source files to the ...
#pragma extref _floatconvert This will force inclusion of floating point formats, which may not be linked to reduce executable size. COMPILER - The default extension for source files to the ...