config_ora.sql

很好用的数据库连接组件

SET  SERVEROUTPUT ON SIZE  100000;

/*==============================================================*/
/* Database name:  zxn10_new                                    */
/* DBMS name:      ORACLE Version 9i                            */
/* Created on:     2005-5-17 9:41:33                            */
/*==============================================================*/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_views WHERE view_name = 'NEW_TKGPDESTCOUNT_TASKVIEW');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop view new_tkgpdestcount_taskview';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_views WHERE view_name = 'NEW_TKGPDEST_TASKVIEW');
    IF cnt!=0  THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop view new_tkgpdest_taskview';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/


BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_views WHERE view_name = 'NEW_TKGPCOUNT_TASKVIEW');
    IF cnt!=0  THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop view new_tkgpcount_taskview';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_views WHERE view_name = 'NEW_TKGP_TASKVIEW');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop view new_tkgp_taskview';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_views WHERE view_name = 'NEW_DESTCOUNT_TASKVIEW');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop view new_destcount_taskview';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_views WHERE view_name = 'NEW_DEST_TASKVIEW');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop view new_dest_taskview';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'NEW_OPREPORT_DAY');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table new_opreport_day cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'NEW_DEST_TASK');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table new_dest_task cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'NEW_REPORTCOLUMNINFO');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table new_reportcolumninfo cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/


BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'USERS');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table users cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/


BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'USERROLERIGHT');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table userroleright cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'USERROLEDEPT');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table userroledept cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'USERROLE');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table userrole cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'TRDTBL_TABLE');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table trdtbl_table cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/


BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'TA24');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table ta24 cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'TA23');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table ta23 cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'TA22');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table ta22 cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'TA21');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table ta21 cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/

BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'SYSPARAM');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table sysparam cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/


BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'SUBSYS');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN
            s := 'drop table subsys cascade constraints';
            DBMS_OUTPUT.PUT_LINE(s);
            EXECUTE IMMEDIATE  s;                
        END;
    END IF;
exception
    WHEN no_data_found  THEN
        DBMS_OUTPUT.PUT_LINE(cnt);
END;
END;
/


BEGIN
DECLARE
cnt integer := 0;
BEGIN
    SELECT 1 INTO cnt FROM dual WHERE exists(SELECT * FROM user_tables WHERE table_name = 'ROUTES_TABLE');
    IF cnt!=0 THEN
        DECLARE s  VARCHAR2(500);
        BEGIN