Oracle存储过程使用动态SQL获得Cursor

[buaawjh]

CREATE OR REPLACE PACKAGE PKG_AAA
AS
TYPE CUR_FLIGHT IS REF CURSOR;

PROCEDURE UP_AAA(PSRLN_LIST varchar2,PLOGCODE varchar2,pdesp varchar2);
END PKG_AAA;
/

CREATE OR REPLACE PACKAGE BODY PKG_AAA
AS
procedure UP_AAA
(
PSRLN_LIST VARCHAR2,
PLOGCODE VARCHAR2,
PDESP VARCHAR2
)
is
v_log_id varchar2(20); --Log唯一标号
sqlstr varchar2(2000); --sql语句
cur_model CUR_FLIGHT;----------??????????????
begin
sqlstr:='select * from aaa where srln in ('||PSRLN_LIST||')';
open cur_model for sqlstr;-------???????????
For r_model in cur_model Loop
select SQ_LOG.NEXTVAL into v_log_id from dual;
Insert Into t_log_flight(...)
values(...);
End Loop;
Commit;
end UP_AAA;
END PKG_AAA;
/

请问错在哪里

[vivianfdlpw]

你是想取值还是想insert数据？

[wxmaizi]

注意引号问题

[wxmaizi]

你把sqlstr print出来看看，是不是你想要的sql

[vivianfdlpw]

CREATE OR REPLACE PACKAGE BODY PKG_AAA
AS
procedure UP_AAA
(
PSRLN_LIST VARCHAR2,
PLOGCODE VARCHAR2,
PDESP VARCHAR2
)
is
v_log_id varchar2(20);
sqlstr varchar2(2000);
cur_model CUR_FLIGHT;
v_row aaaA%rowtype;
begin
sqlstr:='select * from aaa where srln in ('||PSRLN_LIST||')';
open cur_model for sqlstr;
Loop
fetch cur_model into v_row;
exit when cur_model%notfound;
select SQ_LOG.NEXTVAL into v_log_id from dual;
Insert Into t_log_flight(...)
values(...);
End Loop;
Commit;
close cur_model;
end UP_AAA;
END PKG_AAA;

[vivianfdlpw]

多了个A


CREATE OR REPLACE PACKAGE BODY PKG_AAA
AS
procedure UP_AAA
(
PSRLN_LIST VARCHAR2,
PLOGCODE VARCHAR2,
PDESP VARCHAR2
)
is
v_log_id varchar2(20);
sqlstr varchar2(2000);
cur_model CUR_FLIGHT;
v_row aaa%rowtype;
begin
sqlstr:='select * from aaa where srln in ('||PSRLN_LIST||')';
open cur_model for sqlstr;
Loop
fetch cur_model into v_row;
exit when cur_model%notfound;
select SQ_LOG.NEXTVAL into v_log_id from dual;
Insert Into t_log_flight(...)
values(...);
End Loop;
Commit;
close cur_model;
end UP_AAA;
END PKG_AAA;