create or replace procedure CREATE_TABLE ( v_table_name in varchar2) is v_sql varchar2(1000); begin begin v_sql:='create table '||v_table_name; v_sql:=v_sql||' ( APP_ID NUMBER(3) not null,'; v_sql:=v_sql||' DOMAIN VARCHAR2(20) not null, VALUE VARCHAR2(200))'; v_sql:=v_sql||' tablespace GWTICKETDATA'; v_sql:=v_sql||' pctfree 10 pctused 40 initrans 1 maxtrans 255'; v_sql:=v_sql||' storage ( initial 40K next 40K minextents 1'; v_sql:=v_sql||' maxextents 505 pctincrease 50 );'; execute immedidate v_sql; end ; end CREATE_TABLE;
查询复杂点: 这是得到该表AA的记录总数my_count nSQL := DBMS_SQL.OPEN_CURSOR; aa := table_name; sqlcmd := 'SELECT COUNT(*) FROM tabs WHERE TABLE_NAME=:TBNAME'; DBMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ) ; DBMS_SQL.BIND_VARIABLE( nSQL, ':TBNAME', aa) ; DBMS_SQL.DEFINE_COLUMN( nSQL, 1, my_count ) ; ret := DBMS_SQL.EXECUTE( nSQL ) ; LOOP IF DBMS_SQL.FETCH_ROWS( nSQL ) = 0 THEN EXIT; END IF; DBMS_SQL.COLUMN_VALUE( nSQL, 1, my_count ) ; END LOOP;
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; /
用动态SQL语句+ Execute Immediate 命令
各位老大,我的问题在查询中如何使用,虽说异曲同工,可是我调试几次,都不成功。再次请教。 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; 结果错误更多了,请指教。
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;--此处只能返加一个条记录,零条与多条也会出错若想返回多条,不能用execute immediate,只能用游标变量
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; /
is
v_sql varchar2(1000);
begin
begin
v_sql:='create table '||v_table_name;
v_sql:=v_sql||' ( APP_ID NUMBER(3) not null,';
v_sql:=v_sql||' DOMAIN VARCHAR2(20) not null, VALUE VARCHAR2(200))';
v_sql:=v_sql||' tablespace GWTICKETDATA';
v_sql:=v_sql||' pctfree 10 pctused 40 initrans 1 maxtrans 255';
v_sql:=v_sql||' storage ( initial 40K next 40K minextents 1';
v_sql:=v_sql||' maxextents 505 pctincrease 50 );';
execute immedidate v_sql;
end ;
end CREATE_TABLE;
这是得到该表AA的记录总数my_count
nSQL := DBMS_SQL.OPEN_CURSOR;
aa := table_name;
sqlcmd := 'SELECT COUNT(*) FROM tabs WHERE TABLE_NAME=:TBNAME';
DBMS_SQL.PARSE( nSQL, sqlcmd, DBMS_SQL.v7 ) ;
DBMS_SQL.BIND_VARIABLE( nSQL, ':TBNAME', aa) ;
DBMS_SQL.DEFINE_COLUMN( nSQL, 1, my_count ) ;
ret := DBMS_SQL.EXECUTE( nSQL ) ;
LOOP
IF DBMS_SQL.FETCH_ROWS( nSQL ) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE( nSQL, 1, my_count ) ;
END LOOP;
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;
/
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;
结果错误更多了,请指教。
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;--此处只能返加一个条记录,零条与多条也会出错若想返回多条,不能用execute immediate,只能用游标变量
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;
/