这个错误是什么原因,out 参数 类型可以OPEN FOR 吗? CREATE OR REPLACE PACKAGE pk_xue_query
is
TYPE gv_debtreturn_rec IS RECORD(
calling_number VARCHAR2(100), calling_name VARCHAR2(100), ct_c_16300 varchar2(100), ct_c_16306 varchar2(100), ct_c_16366 varchar2(100),
ct_c_17200 VARCHAR2(100), ct_c_17201 VARCHAR2(100), ct_c_17202 VARCHAR2(100),ct_c_96663 VARCHAR2(100),
crm_name VARCHAR2(100),crm_address VARCHAR2(100),crm_postcode VARCHAR2(100),crm_billname VARCHAR2(100),
crm_custname VARCHAR2(100)
); TYPE gv_debtreturn_systable IS TABLE OF gv_debtreturn_rec; TYPE gv_debtreturn_syscur IS REF CURSOR RETURN gv_debtreturn_rec;
END pk_xue_query;
--定义过程
create or replace procedure xue_ctip_query
(
v_bill_cycle IN VARCHAR2,
v_area_id IN VARCHAR2,
v_begintime IN VARCHAR2,
v_endtime IN VARCHAR2, v_cur OUT pk_xue_query.gv_debtreturn_syscur, --查询结果集
v_resultcode OUT NUMBER, --错误编号
v_resulterrinfo OUT VARCHAR2 --错误信息
)
is
dyna_sql varchar2(4000) :='';
BEGIN
dyna_sql := 'select a1,a2,a3,a4,a5....from dual';
open v_cur for dyna_sql;
end xue_ctip_query;
报错:cursor 'V_CUR' connot be used in dynamic SQL OPEN statement
is
TYPE gv_debtreturn_rec IS RECORD(
calling_number VARCHAR2(100), calling_name VARCHAR2(100), ct_c_16300 varchar2(100), ct_c_16306 varchar2(100), ct_c_16366 varchar2(100),
ct_c_17200 VARCHAR2(100), ct_c_17201 VARCHAR2(100), ct_c_17202 VARCHAR2(100),ct_c_96663 VARCHAR2(100),
crm_name VARCHAR2(100),crm_address VARCHAR2(100),crm_postcode VARCHAR2(100),crm_billname VARCHAR2(100),
crm_custname VARCHAR2(100)
); TYPE gv_debtreturn_systable IS TABLE OF gv_debtreturn_rec; TYPE gv_debtreturn_syscur IS REF CURSOR RETURN gv_debtreturn_rec;
END pk_xue_query;
--定义过程
create or replace procedure xue_ctip_query
(
v_bill_cycle IN VARCHAR2,
v_area_id IN VARCHAR2,
v_begintime IN VARCHAR2,
v_endtime IN VARCHAR2, v_cur OUT pk_xue_query.gv_debtreturn_syscur, --查询结果集
v_resultcode OUT NUMBER, --错误编号
v_resulterrinfo OUT VARCHAR2 --错误信息
)
is
dyna_sql varchar2(4000) :='';
BEGIN
dyna_sql := 'select a1,a2,a3,a4,a5....from dual';
open v_cur for dyna_sql;
end xue_ctip_query;
报错:cursor 'V_CUR' connot be used in dynamic SQL OPEN statement
改成 TYPE gv_debtreturn_syscur IS REF CURSOR ; 后 与上面的 recode 还有联系吗?why 改后还是编译不过去?
2 (
3 v_bill_cycle IN VARCHAR2,
4 v_area_id IN VARCHAR2,
5 v_begintime IN VARCHAR2,
6 v_endtime IN VARCHAR2,
7
8 v_cur OUT pk_xue_query.gv_debtreturn_syscur, --查询结果集
9 v_resultcode OUT NUMBER, --错误编号
10 v_resulterrinfo OUT VARCHAR2 --错误信息
11 )
12 is
13 dyna_sql varchar2(4000) :='';
14 BEGIN
15 open v_cur for select 1,2,3,4,5,6,7,8,9,10,11,12,13,14 from dual;
16 end xue_ctip_query;
17 /
Procedure created
SQL> 这样是可以的.
动态sql是执行时才检查语法的,你在动态sql对应的串里写的不是sql语句都可以编译通过就可以说明这一点.
dyna_sql := 'select a1,a2,a3,a4,a5....from dual where 参数';
open v_cur for dyna_sql;
需要这样执行 该怎么修改的?
如果 open v_cur for select a1,a2,a3,a4,a5....from dual where 参数 这样直接查询是可以编译成功 也可以出结果 - 但是:这样参数只能写死
需求是 参数是个变量
需要写出 动态sql
open v_cur for 后面 如何写、?
用sys_refcursor来定义吧.
改为 type gv_debtreturn_syscur sys_refcursor RETURN gv_debtreturn_rec ?
(
v_bill_cycle IN VARCHAR2,
v_area_id IN VARCHAR2,
v_begintime IN VARCHAR2,
v_endtime IN VARCHAR2, v_cur OUT Sys_Refcursor, --查询结果集
v_resultcode OUT NUMBER, --错误编号
v_resulterrinfo OUT VARCHAR2 --错误信息
)
is
dyna_sql varchar2(4000) :='';
BEGIN
dyna_sql := 'select a1,a2,a3,a4,a5....from dual';
open v_cur for dyna_sql;
end xue_ctip_query;直接这样就可以了,不需要自己定义
is
begin
open io_cursor for
select * from tab where rownum <= query_id;
end;