create or replace procedure proc_rgb_optimation_backup (p_table_name varchar2) is
sql_statement varchar2(500);
i_l_count pls_integer;
sql_count varchar2(200);
new_table varchar2(30);
begin
new_table := 'TBL_BMCODE_RGB_OPT_' || to_char(sysdate,'yyyyMMdd'); --变量赋值用 :=
i_l_count := 0;
sql_count := 'select count(*) from user_tables t where t.TABLE_NAME ='
|| '''' || new_table || ''''; --这里注意 两个单引号表示一个单引号(‘)
execute immediate sql_count into i_l_count; -- 用 execute immediate 来执行sql . 用 into 来接收返回值
if i_l_count<=0 then
sql_statement := 'create table ' || new_table || ' as select * From ' || p_table_name;
else
sql_statement := 'INSERT INTO ' || new_table || '(BM_CODE,SPEED,RGB_OLD,RGB_OPTI,TRAVELTIME,TIMESTAMP,DURATION,SAMPLE,NUM_RED,NUM_YELLOW) select BM_CODE,SPEED,RGB_OLD,RGB_OPTI,TRAVELTIME,TIMESTAMP,DURATION,SAMPLE,NUM_RED,NUM_YELLOW from ' || p_table_name;
end if;
execute immediate sql_statement;
commit;
EXCEPTION
WHEN dup_val_on_index THEN
rollback;
WHEN timeout_on_resource THEN
rollback;
WHEN NO_DATA_FOUND THEN
rollback;
end;