存储过程点滴

最新推荐文章于 2022-02-24 15:37:38 发布

原创最新推荐文章于 2022-02-24 15:37:38 发布 · 979 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#存储过程点滴

Oracle Pl/SQL 专栏收录该内容

30 篇文章

订阅专栏

使用存储过程来执行DDL语句。

CREATE OR REPLACE
PROCEDURE CNTL_DATA.alt_tbl(altsql IN varchar2)
   IS
StoO_error  INTEGER;
StoO_errmsg VARCHAR2(255);
BEGIN
    execute immediate altsql;
EXCEPTION
    WHEN others THEN
            StoO_error := SQLCODE;
            StoO_errmsg := SQLERRM;
            RAISE_APPLICATION_ERROR(SQLCODE, SQLERRM,TRUE);
END alt_tbl;

使用循环

FOR rciKorMaps IN (select regexp_substr(rciKorMapId,'[^,]+',1, level) as ids from dual
                      connect by regexp_substr(rciKorMapId,'[^,]+',1, level) is not null)
LOOP

    Query

End loop

输出行

dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM   , 1 , 255));

事务例子

IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
       INSERT INTO CNTL_DATA.cntl_log values (seq_cntl_log.nextval, in_job_id, proc_name|| msg, sysdate) ;
       COMMIT;
       EXCEPTION
        WHEN OTHERS THEN
                DBMS_OUTPUT.put_line('error:-->' || SQLCODE || ', ERR:-->' || SQLERRM );
                ROLLBACK;
END;