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;

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的oracle表没有数据判断,Oracle中判断有表DROP无表CREATE的存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。