Oracle中判断有表DROP无表CREATE的存储过程

CREATE OR REPLACE PROCEDURE PD_GENERATE_HELP_LAST_TABLES IS–把从sqlserver中生成的vw_last_xxx所对应的视图生成本地的数据表

首页 → 数据库技术

背景：

阅读新闻

Oracle中判断有表DROP无表CREATE的存储过程

[日期：2011-05-11]来源：Linux社区作者：Linux[字体：]

CREATE OR REPLACE PROCEDURE PD_GENERATE_HELP_LAST_TABLES IS
–把从sqls

本文来源gaodai.ma#com搞##代!^码@网3

erver中生成的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;