使用動態語句
存储过程proc
(
p_contid IN VARCHAR2
)
as
sql_str varchar2(500);
begin
sql_str := 'Update PaymentRecord set Flag = 1 where ID in ( '||p_contid||' ) ';
execute immediate sql_str;
commit;
end;
存储过程proc
(
p_contid IN VARCHAR2
)
as
sql_str varchar2(500);
begin
sql_str := 'Update PaymentRecord set Flag = 1 where ID in ( '||p_contid||' ) ';
execute immediate sql_str;
commit;
end;
for A IN (select contid,lastdate,nextdate,paymentrecord.ifend as ifend,
decode(paytype,"月付",1,"双月付",2,1) as intval
from paymentrecord Inner Join Contract on paymentrecord.ContID = Contract.ContractID where paymentrecord.nextdate is not null and paymentrecord.ContID in (p_contid))
LOOP
begin end;
end loop;
当中这段in (p_contid)) 该怎么来表示呢?
以上的問題就是將你的執行語句變成字符串
用動態語句來打開遊標及操作就可以了;
--example
SQL> create or replace procedure pro_test
2 (
3 p_contid in varchar2
4 )
5 as
6 type ref_cursor_type is ref cursor;
7 my_cursor ref_cursor_type;
8 is_name varchar2(20);
9 sql_str varchar2(500);
10 begin
11 sql_str := 'select name from bao_temp where name in ('||p_contid||')' ;
12 dbms_output.put_line(sql_str);
13 open my_cursor for sql_str ;
14 loop
15 fetch my_cursor into is_name;
16 exit when my_cursor%notfound;
17
18 dbms_output.put_line('name is :'||is_name);
19
20 end loop;
21 close my_cursor;
22 end;
23 /程序已被建立SQL> exec pro_test('''A'',''B''');
select name from bao_temp where name in ('A','B')
name is :A
name is :BPL/SQL 程序順利完成目前歷時: 00:00:00.16
type v_rc is cursor ref;
var_sql varchar2(1000);
begin
var_sql:='select contid,lastdate,nextdate,paymentrecord.ifend as ifend,decode(paytype,"月付",1,"双月付",2,1) as intval from paymentrecord Inner Join Contract on paymentrecord.ContID = Contract.ContractID where paymentrecord.nextdate is not null and paymentrecord.ContID in ('||p_contid||')';
open var_sql for var_sql;
LOOP
begin end;
end loop;
....
/
p_contid IN VARCHAR2
)
beginfor A IN (select contid,lastdate,nextdate,paymentrecord.ifend as ifend,
decode(paytype,"月付",1,"双月付",2,1) as intval
from paymentrecord Inner Join Contract on paymentrecord.ContID = Contract.ContractID where paymentrecord.nextdate is not null and paymentrecord.ContID in (p_contid))
LOOP
begin
insert into tab1(contid,curdate) values(A.contid,A.nextdate);
end;
end loop;
end;
这个应该怎么改呢?
type v_rc is ref cursor ;
var_sql varchar2(1000);
v_contid number;
v_lastdate date;
v_nextdate date;
v_ifend varchar2(100);
v_intval varchar2(100);
begin
var_sql:='select contid,lastdate,nextdate,paymentrecord.ifend as ifend,decode(paytype,"月付",1,"双月付",2,1) as intval from paymentrecord Inner Join Contract on paymentrecord.ContID = Contract.ContractID where paymentrecord.nextdate is not null and paymentrecord.ContID in ('||p_contid||')';
open v_rc for var_sql;
LOOP
FETCH v_rc INTO v_contid,v_lastdate,v_nextdate,v_ifend,v_intval;
exit when var_sql%notfound;
insert into tab1(contid,curdate) values(v_contid,v_nextdate);
end loop;
....
close v_rc;
/
这里执行出错,请问如何解决?