CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create procedure pro(p_table in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(100);
begin
str:='select A.svcnum from d_rp_jfyh A ,'||p_table||' B where A.start_date=B.start_date';
open p_rc for str;
end;
/
declare
my_tabel_name varchar2(30);
v_rc pkg_test.myrctype;
v_svcnum d_rp_jfyh.svcnum%type;
begin
my_table_nam:='d_dc_call_dtl_rec2'||substr(to_char(sysdate-1,'YYYYMMDD'),7,2);
pro(my_table_nam,v_rc);
loop
fetch v_rc into v_svcnum;
exit when v_rc%notfound;
dbms_output.put_line(v_svcnum);
end loop;
end;
/
declare
my_tabel_name varchar2(30);
sql_str varchar2(1000);
v_svcnum d_rp_jfyh.svcnum%type;
begin
my_table_nam:='d_dc_call_dtl_rec2'||substr(to_char(sysdate-1,'YYYYMMDD'),7,2);
sql_str='select A.svcnum from d_rp_jfyh A ,'||my_table_name||' B where A.start_date=B.start_date';
execute immediate sql_str into v_svcnum;--此处只能返加一个条记录,零条与多条也会出错
各位老大,我的问题在查询中如何使用,虽说异曲同工,可是我调试几次,都不成功。再次请教。
declare
my_tabel_name varchar2(30);
sql_str varchar2(1000);
begin
my_table_nam:='d_dc_call_dtl_rec2'||substr(to_char(sysdate-1,'YYYYMMDD'),7,2);
--取当前日期的后两位。
sql_str='select A.svcnum from d_rp_jfyh A ,'||my_table_name||' B where A.start_date=B.start_date';
execute immediate sql_str;
结果提示出错。在执行的时候。
后来我换成
sql_str='select A.svcnum from d_rp_jfyh A ,:1 B where A.start_date=B.start_date';
execute immediate sql_str using my_table_name;
结果错误更多了,请指教。
create procedure pro(p_table in varchar2)
as
str varchar2(100);
begin
str:='create table '||p_table||' (id varchar2(10),name varchar2(20))';--grant create any table to your_user
execute immediate str;
end;
/