Oracle中判斷有表DROP無表CREATE的預存程序

CREATE OR REPLACE PROCEDURE PD_GENERATE_HELP_LAST_TABLES IS
--把從sqlserver中產生的vw_last_xxx所對應的檢視窗產生本地的資料表
  tabNocount NUMBER ;
  v_sql      VARCHAR2(200);

BEGIN
  --判斷表dat_help_last_scqy1(成品油生產旬報）
  SELECT count(*) INTO tabNocount FROM user_tables WHERE table_name = 'DAT_HELP_LAST_SCQY1';
  IF tabNocount>0 THEN
     dbms_output.put_line('drop table dat_help_last_scqy1');
     v_sql := 'DROP TABLE DAT_HELP_LAST_SCQY1';
     EXECUTE IMMEDIATE v_sql;
  END IF;
     dbms_output.put_line('create table dat_help_last_scqy1');
     v_sql := 'CREATE TABLE DAT_HELP_LAST_SCQY1 AS SELECT * FROM VW_LAST_SCQY1';
     EXECUTE IMMEDIATE v_sql;
  --判斷表dat_help_last_scqy2(成品油生產月報）
 
  SELECT count(*) INTO tabNocount FROM user_tables WHERE table_name = 'DAT_HELP_LAST_SCQY2';
  IF tabNocount>0 THEN
     dbms_output.put_line('drop table dat_help_last_scqy2');
     v_sql := 'DROP TABLE DAT_HELP_LAST_SCQY2';
     EXECUTE IMMEDIATE v_sql;
  END IF;
     dbms_output.put_line('create talbe dat_help_last_scqy2');
     v_sql := 'CREATE TABLE DAT_HELP_LAST_SCQY2 AS SELECT * FROM VW_LAST_SCQY2';
     EXECUTE IMMEDIATE v_sql;
  --判斷表dat_help_last_shsndybb(能源調度月報表）
  SELECT count(*) INTO tabNocount FROM user_tables WHERE table_name = 'DAT_HELP_LAST_SHSNDYBB';
  IF tabNocount>0 THEN
     dbms_output.put_line('drop table dat_help_last_shsndybb');
     v_sql := 'DROP TABLE DAT_HELP_LAST_SHSNDYBB';
     EXECUTE IMMEDIATE v_sql;
  END IF;
     dbms_output.put_line('create table dat_help_last_shsndybb');
     v_sql := 'CREATE TABLE dat_help_last_shsndybb AS SELECT * FROM vw_last_shsndybb';
     EXECUTE IMMEDIATE v_sql;
END PD_GENERATE_HELP_LAST_TABLES;

 

測試

begin
  -- Call the procedure
  pd_generate_help_last_tables;
end;

 

如果出現 ORA-01031: 許可權不足
ORA-06512: 在"SYSTEM.CCCCCC", line 6
ORA-06512: 在line 1

的錯誤，則執行以下授權語句

grant create table to jwdc;
grant resource to jwdc;
grant create procedure to jwdc;