上海龙凤1314 shlf    Sybase存储过程点滴

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

Sybase存储过程点滴

CREATE OR REPLACEPROCEDURE CNTL_DATA.alt_tbl(altsql IN varchar2)   ISStoO_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    QueryEnd loop输出行dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM   , 1 , 255));事务例子ISPRAGMA 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;