Tips Dan Trik Exception Handling Pada Pl/Sql

Sintak Dasar

DECLARE     BEGIN     EXCEPTION     WHEN exception1 THEN    exception1-handling-statements WHEN exception2 THEN    exception2-handling-statements WHEN exception3 THEN    exception3-handling-statements    ........ WHEN others THEN    exception3-handling-statements END; 

Contoh Program

DECLARE    v_npm     mahasiswa.npm%TYPE := '100';    v_nama   mahasiswa.nama%TYPE;    v_jurusan   mahasiswa.jurusan%TYPE; BEGIN    SELECT npm, nama, jurusan      INTO v_npm, v_nama, v_jurusan      FROM mahasiswa     WHERE npm = v_npm;     DBMS_OUTPUT.PUT_LINE ('NPM: ' || v_npm);    DBMS_OUTPUT.PUT_LINE ('Nama: ' || v_nama);    DBMS_OUTPUT.PUT_LINE ('Jurusan: ' || v_jurusan); EXCEPTION    WHEN NO_DATA_FOUND    THEN       DBMS_OUTPUT.put_line ('Data Mahasiswa tidak ditemukan!');    WHEN OTHERS    THEN       DBMS_OUTPUT.put_line ('Error!'); END; / 

Output

Data Mahasiswa tidak ditemukan!  PL/SQL procedure successfully completed. 

Membuat User Defined Exception

Sintak Dasar

DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END; 

Contoh:

DECLARE    v_npm            mahasiswa.npm%TYPE := '1001';    v_nama          mahasiswa.nama%TYPE;    v_jurusan          mahasiswa.jurusan%TYPE;        -- user defined exception    invalid_npm_ex  EXCEPTION; BEGIN    IF length(v_npm) <= 7    THEN       RAISE invalid_npm_ex;    ELSE       SELECT nama, jurusan         INTO v_nama, v_jurusan         FROM mahasiswa        WHERE npm = v_npm;                DBMS_OUTPUT.PUT_LINE ('NPM: ' || v_npm);        DBMS_OUTPUT.PUT_LINE ('Nama: ' || v_nama);       DBMS_OUTPUT.PUT_LINE ('Jurusan: ' || v_jurusan);    END IF; EXCEPTION    WHEN invalid_npm_ex    THEN       DBMS_OUTPUT.put_line ('Panjang NPM harus 7 digit');    WHEN NO_DATA_FOUND    THEN       DBMS_OUTPUT.put_line ('Mahasiswa tidak ditemukan!');    WHEN OTHERS    THEN       DBMS_OUTPUT.put_line ('Error Teuing..!'); END; / 

Output

Panjang NPM harus 7 digit