PL_SQL筆記

show all
show errors procedure wordcount
spool reprot01.txt
spool off
set serveroutput on
呼叫function
exec dbms_output.put_line(booktitle('0-596-00180-0'))

一.區塊結構
(1).
begin
.
.
end;
(2).
declare
.
begin
.
end;
(3).
declare
.
begin
.
exception
.
end;

(4)
BEGIN
<>
LOOP
LOOP
EXIT outer_loop;
END LOOP;
END LOOP;
END;

(5)
if
.
then
.
elsif
.
end if

二. raise
用法 RAISE_APPLICATION_ERROR(-20001,'總帳系統已關帳，最新關帳年月為：' || LS_CLOSE_YM1 || '，故不可刪除傳票：' || :OLD.IN_N);
或 raise l_old_time

三.EXCEPTION_INIT 的建議使用
CREATE OR REPLACE PACKAGE dynsql
IS
invalid_table_name EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_table_name,-903);
invalid_column_name EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_column_name,-904);
現在可於任何一個程式中, 用下列方式捕捉錯誤;
WHEN dynsql.invalid_column_name THEN.....

四.EXCEPTION_INIT 的建議使用之二
PACKAGE errnums
IS
en_too_young CONSTANT NUMBER := -20001;
exc_too_young EXCEPTION;
PRAGMA EXCEPTION_INIT
(exc_too_young,-20001);

en_sal_too_low CONSTANT NUMBER := -20002;
exc_sal_too_low EXCEPTION;
PRAGMA EXCEPTION INIT
(exc_sal_too_low,-20002);
END errnums;
現在可在一個程式中, 用下列方式捕捉錯誤;
PROCEDURE validate_emp (birthdate_in IN DATE)
IS
min_years CONSTANT PLS_INTEGER := 18
BEGIN
IF ADD_MONTHS(SYSDATE,min_years * 12 * -1 )< birthdate_in
THEN
RAISE_APPLICATION_ERROR
(errnums.en_too_young,'Employee must be at least' || min_years || 'old.');
END IF;
END;