oracle11的用存储过程实现ddl维护

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE  "ZZQJ"."ALL_EXTERNAL_AND_USER"
                       (    "ID" NUMBER(16,0),
                            "EXTERNAL_USER_ID" VARCHAR2(128),
                            "USER_ID" VARCHAR2(128),
                            "USERNAME" VARCHAR2(128),
                            "REMARK" VARCHAR2(128),
                            "CREATE_BY" VARCHAR2(128),
                            "CREATE_TIME" TIMESTAMP (6),
                            "UPDATE_BY" VARCHAR2(128),
                            "UPDATE_TIME" TIMESTAMP (6),
                            "DEL_FLAG" NUMBER(1,0),
                            CONSTRAINT "aeu_id" PRIMARY KEY ("ID")
                       )';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -955 THEN
            -- 表已存在,可以在这里处理
            DBMS_OUTPUT.PUT_LINE('Table  ALL_EXTERNAL_AND_USER already exists.');
        ELSE
            -- 其他错误
            RAISE;
        END IF;
END;$


DECLARE
    v_count NUMBER := 0;
BEGIN  BEGIN
    SELECT COUNT(*)
    INTO v_count
    FROM user_indexes
    WHERE index_name = 'AEAU_USERID';
END;
-- 如果索引不存在,则创建索引
IF v_count = 0 THEN
    EXECUTE IMMEDIATE 'CREATE INDEX "ZZQJ"."AEAU_USERID" ON "ZZQJ"."ALL_EXTERNAL_AND_USER" ("EXTERNAL_USER_ID")';
END IF;
EXCEPTION
    WHEN OTHERS THEN
        -- 处理可能的异常
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
        RAISE;
END;$


COMMENT ON COLUMN ZZQJ.ALL_EXTERNAL_AND_USER.USER_ID IS '用户id'$

 

posted @ 2024-08-23 15:34  24601  阅读(1)  评论(0编辑  收藏  举报