oracle动态SQL

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;