create or replace procedure aaa(v_num int)
is
v_string varchar2(200);
v_string2 varchar2(200);
begin
v_string := 'drop table wjt_'||(to_char(sysdate-interval '6' minute,'yyyymmddhhmi'));
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string;
execute immediate v_string2;
end;我想把参数v_num把6替换,请问应该怎么连接呢?
is
v_string varchar2(200);
v_string2 varchar2(200);
begin
v_string := 'drop table wjt_'||(to_char(sysdate-interval '6' minute,'yyyymmddhhmi'));
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string;
execute immediate v_string2;
end;我想把参数v_num把6替换,请问应该怎么连接呢?
is
v_string varchar2(200);
v_string2 varchar2(200);
begin
v_string := 'drop table wjt_'||(to_char(sysdate-interval :v_num minute,'yyyymmddhhmi'));
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string using v_mum;
execute immediate v_string2;
end;
/
你这个好像是给它赋值、、v_num是我当参数传进来的
is
v_string varchar2(200);
v_string2 varchar2(200);
begin v_string := 'drop table wjt_'||(to_char(sysdate-v_num/60,'yyyymmddhhmi'));
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string;
execute immediate v_string2;
end;
2 authid current_user
3 is
4 v_string varchar2(200);
5 v_string2 varchar2(200);
6 v_cnt number(18,0);
7 begin
8 EXECUTE IMMEDIATE 'SELECT to_char(sysdate-interval '''||to_char(v_num)||''' minute,''yyyymmddhhmi'') FROM dual' INTO v_string;
9 v_string := 'WJT_'||v_string;
10 SELECT count(1) INTO v_cnt from user_tables where table_name=v_string;
11 IF v_cnt=1 then
12 EXECUTE immediate 'DROP TABLE '||v_string;
13 END IF;
14 v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
15 execute immediate v_string2;
16 end;
17 /过程已创建。scott@SZTYORA> exec aaa(10);
create public synonym WJT_201102151200 for scott.WJT_201102151200PL/SQL 过程已成功完成。scott@SZTYORA> select table_name from user_tables where table_name like 'WJT%';TABLE_NAME
------------------------------------------------------------
WJT_201102151200scott@SZTYORA>
create or replace procedure insert_rs(v_num int)
is
v_string varchar2(200);
v_string2 varchar2(200);
begin
v_string := 'drop table wjt_'||(to_char(sysdate-v_num/1440,'yyyymmddhhmi'));
v_string2 := 'create table wjt_'||to_char(sysdate,'yyyymmddhhmi')||'(id number,name varchar2(10))';
execute immediate v_string;
execute immediate v_string2;
end;