PLSQL P9

/*
NAMA    : Akbar Basyarudin
NIM     : 41519010046
*/


SET SERVEROUTPUT ON;
SET ECHO ON;
SET VERIFY OFF;

/* TIPE DATA */
DECLARE
    TYPE kar_rec IS RECORD
    (
        nip CHAR(3), nama VARCHAR(10), gaji NUMBER
    );
    TYPE daftar_kar IS TABLE OF kar_rec INDEX BY PLS_INTEGER;
    all_kar daftar_kar;
BEGIN
    all_kar(0).nip  := '111';
    all_kar(0).nama := 'Amir';
    all_kar(0).gaji := 1000;

    all_kar(1).nip  := '222';
    all_kar(1).nama := 'Budi';
    all_kar(1).gaji := 2000;

    FOR x IN all_kar.FIRST..all_kar.LAST LOOP
        DBMS_OUTPUT.put_line(all_kar(x).nip||'  '||all_kar(x).nama||' '||all_kar(x).gaji);
        DBMS_OUTPUT.put_line(all_kar(x).gaji);
    END LOOP;
END;
/

/* BULK PROCESSING */
DECLARE
    TYPE emp_rec IS RECORD
    (
        nip EMPLOYEES.EMPLOYEE_ID%TYPE,
        nama EMPLOYEES.FIRST_NAME%TYPE,
        gaji EMPLOYEES.SALARY%TYPE
    );
    TYPE emp_list IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
    all_emp emp_list;
BEGIN
    SELECT employee_id, first_name, salary BULK COLLECT INTO all_emp FROM employees;
    FOR x IN all_emp.FIRST..all_emp.LAST LOOP
        DBMS_OUTPUT.put_line(all_emp(x).nip||'  '||all_emp(x).nama||'  '||all_emp(x).gaji);
    END LOOP;
END;
/

/* BULK COLLECT FOR ALL */
    /* CREATE TABLE */
CREATE TABLE emp2
(
    nip NUMBER,
    nama VARCHAR2(25),
    gaji NUMBER
);
    /* BULK */
DECLARE
    TYPE emp_rec IS RECORD
    (
        nip EMPLOYEES.EMPLOYEE_ID%TYPE,
        nama EMPLOYEES.FIRST_NAME%TYPE,
        gaji EMPLOYEES.SALARY%TYPE
    );
    TYPE emp_list IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
    all_emp emp_list;
BEGIN
    SELECT employee_id, first_name, salary BULK COLLECT INTO all_emp FROM employees;
    FOR x IN all_emp.FIRST..all_emp.LAST LOOP
        DBMS_OUTPUT.put_line(all_emp(x).nip||'  '||all_emp(x).nama||'  '||all_emp(x).gaji);
    END LOOP;
    DBMS_OUTPUT.put_line('Total records : '||all_emp.COUNT);
    FORALL i IN all_emp.FIRST..all_emp.LAST INSERT INTO emp2 VALUES all_emp(i);
    COMMIT;
END;
/
    /* DROP TABLE AGAR TIDAK BENTROK */
DROP TABLE emp2;

    /* Creating Table emp2 & emp3 */
CREATE TABLE emp2
(
    nip NUMBER,
    nama VARCHAR2(25),
    gaji NUMBER
);

CREATE TABLE emp3
(
    nip NUMBER,
    nama VARCHAR2(25),
    gaji NUMBER
);

/* BENCHMARKING NORMAL INSERT & BULK INSERT */
DECLARE
    TYPE emp_rec IS RECORD
    (
        nip EMPLOYEES.EMPLOYEE_ID%TYPE,
        nama EMPLOYEES.FIRST_NAME%TYPE,
        gaji EMPLOYEES.SALARY%TYPE
    );
    TYPE emp_list IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
    all_emp emp_list;
    lstart INTEGER;
BEGIN
    SELECT employee_id, first_name, salary BULK COLLECT INTO all_emp FROM employees;
    lstart := DBMS_UTILITY.GET_TIME;
    --INSERT BIASA
    FOR x IN all_emp.FIRST..all_emp.LAST LOOP
        INSERT INTO emp3 VALUES (all_emp(x).nip, all_emp(x).nama, all_emp(x).gaji);
    END LOOP;
    DBMS_OUTPUT.put_line('Normal Insert : '||(DBMS_UTILITY.GET_TIME() - lstart));

    --BULK INSERT
    lstart := DBMS_UTILITY.GET_TIME;
    FORALL i IN all_emp.FIRST..all_emp.LAST INSERT INTO emp2 VALUES all_emp(i);
    DBMS_OUTPUT.put_line('Bulk Insert   : '||(DBMS_UTILITY.GET_TIME - lstart));
    COMMIT;
END;
/

/* EXCEPTION HANDLING */
DECLARE
    hasil NUMBER;
BEGIN
    hasil := 10/0;
    DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
END;
/

DECLARE
    hasil NUMBER;
BEGIN
    hasil := 10/0;
    DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Ada error : '||SQLERRM);
END;
/

DECLARE
    hasil NUMBER;
    nama VARCHAR(25);
BEGIN
    hasil := 10/1;
    DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);

    SELECT first_name INTO nama FROM employees WHERE employee_id = 300;
    DBMS_OUTPUT.put_line('Nama  : '||nama);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Ada error : '||SQLERRM);
END;
/

DECLARE
    hasil NUMBER;
    nama VARCHAR(25);
BEGIN
    hasil := 10/1;
    DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);

    SELECT first_name INTO nama FROM employees WHERE employee_id = 300;
    DBMS_OUTPUT.put_line('Nama  : '||nama);
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.put_line('Terjadi Bagi 0');
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('Data tidak ditemukan');
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Ada Error : '||SQLERRM);
END;
/
/* mengasosiasikan variabel exception buatan Anda sendiri dengan error number built in */
DECLARE
    hasil NUMBER;
    nama VARCHAR(25);
    bagi_nol EXCEPTION;
    kagak_nemu EXCEPTION;
    PRAGMA EXCEPTION_INIT(bagi_nol, -01476);
    PRAGMA EXCEPTION_INIT(kagak_nemu, -100);
BEGIN
    hasil := 10/1;
    DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);

    SELECT first_name INTO nama FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.put_line('Nama  : '||nama);
EXCEPTION
    WHEN bagi_nol THEN
        DBMS_OUTPUT.put_line('Terjadi Bagi 0');
    WHEN kagak_nemu THEN
        DBMS_OUTPUT.put_line('Data tidak ditemukan');
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Ada Error : '||SQLERRM);
END;
/
/* Membuat Error Number Sendiri (Custom Error) */
DECLARE
    pembilang NUMBER := 10;
    penyebut NUMBER := &pembagi;
    hasil NUMBER;
    salah EXCEPTION;
    PRAGMA EXCEPTION_INIT(salah, -20001);
BEGIN
    IF penyebut = 0 THEN
        RAISE_APPLICATION_ERROR (-20001, 'Pembagi tidak boleh 0!!!');
    ELSE
        hasil := pembilang/penyebut;
        DBMS_OUTPUT.put_line('Hasil : '||hasil);
    END IF;
EXCEPTION
    WHEN salah THEN
        DBMS_OUTPUT.put_line('Ada Error : '||SQLERRM);
END;
/

DECLARE
    pembilang NUMBER := 10;
    penyebut NUMBER := &pembagi;
    hasil NUMBER;
    satu EXCEPTION;
    dua EXCEPTION;
    tiga EXCEPTION;

    PRAGMA EXCEPTION_INIT(satu, -20001);
    PRAGMA EXCEPTION_INIT(dua, -20002);
    PRAGMA EXCEPTION_INIT(tiga, -20003);
BEGIN
    IF penyebut = 1 THEN
        RAISE_APPLICATION_ERROR (-20001, 'Pembagi tidak boleh 1 !!!');
    ELSIF penyebut = 2 THEN
        RAISE_APPLICATION_ERROR (-20002, 'Pembagi tidak boleh 2 !!!');
    ELSIF penyebut = 3 THEN
        RAISE_APPLICATION_ERROR (-20003, 'Pembagi tidak boleh 3 !!!');
    ELSE
        hasil := pembilang/penyebut;
        DBMS_OUTPUT.put_line('Hasil : '||hasil);
    END IF;
EXCEPTION
    WHEN satu THEN
        DBMS_OUTPUT.put_line('Ada Error 1   : '||SQLERRM);
    WHEN dua THEN
        DBMS_OUTPUT.put_line('Ada Error 2   : '||SQLERRM);
    WHEN tiga THEN
        DBMS_OUTPUT.put_line('Ada Error 3   : '||SQLERRM);
    WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('Ada Error     : '||SQLERRM);
END;
/

/* MERGE TABLE */
CREATE TABLE copy_reg AS SELECT * FROM regions WHERE 1=3;

SELECT * FROM copy_reg;

INSERT INTO copy_reg VALUES (5, 'JOGJA');

INSERT INTO copy_reg VALUES (4, 'SEMARANG');

SELECT * FROM copy_reg;

SELECT * FROM regions;

BEGIN
    MERGE INTO copy_reg c USING regions r ON(r.region_id=c.region_id)
    WHEN MATCHED THEN
        UPDATE SET
            c.region_name = r.region_name
    WHEN NOT MATCHED THEN
        INSERT VALUES (r.region_id, r.region_name);
END;
/

SELECT * FROM copy_reg;