PLSQL P8

/* SET OUTPUT ON */
SET SERVEROUTPUT ON;
SET ECHO ON;
/* PENGGUNAAN IF */
DECLARE
    nama EMPLOYEES.FIRST_NAME%TYPE;
    gaji EMPLOYEES.SALARY%TYPE;
    nip EMPLOYEES.EMPLOYEE_ID%TYPE;
BEGIN
    nip:=100;
    SELECT first_name, salary INTO nama, gaji FROM employees WHERE employee_id = nip;
    DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
    DBMS_OUTPUT.PUT_LINE('Gaji  : '||gaji);
    IF (gaji <= 2000) THEN
        DBMS_OUTPUT.PUT_LINE('Kategori  : Low...');
    ELSIF (gaji > 2000) AND (gaji <= 5000) THEN
        DBMS_OUTPUT.PUT_LINE('Kategori  : Medium...');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Kategori  : High...');
    END IF;
END;
/
/* PENGGUNAAN CASE */
DECLARE
    nilai CHAR(1):='&nilai';
    predikat VARCHAR(20);
BEGIN
    predikat :=
    CASE nilai
            WHEN 'A' THEN 'Excellent'
            WHEN 'B' THEN 'Good'
            WHEN 'C' THEN 'Fair'
            WHEN 'D' THEN 'Poor'
            WHEN 'E' THEN 'Failed'
            ELSE 'No Such Grade'
    END;
    DBMS_OUTPUT.PUT_LINE('Nilai     : '||nilai);
    DBMS_OUTPUT.PUT_LINE('Predikat  : '||predikat);
END;
/
/* SET VERIFY OFF AND SHOW IT */
SET VERIFY OFF;
SHOW VERIFY;
/* PENGGUNAAN LOOP */
DECLARE
    counter NUMBER:=0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Loop ke-'||counter);
        counter:=counter+1;
        EXIT WHEN counter=10;
    END LOOP;
END;
/
/* PENGGUNAAN WHILE */
DECLARE
    penghitung NUMBER:=0;
BEGIN
    WHILE penghitung < 10 LOOP
        DBMS_OUTPUT.PUT_LINE('While ke-'||penghitung);
        penghitung:=penghitung+1;
    END LOOP;
END;
/
/* PENGGUNAAN FOR */
BEGIN
    FOR X IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('For ke-'||X);
    END LOOP;
END;
/
BEGIN
    FOR X IN 1..10 LOOP
        IF MOD(X,2) = 0 THEN
            DBMS_OUTPUT.PUT_LINE('For ke-'||X);
        END IF;
    END LOOP;
END;
/
/* PENGGUNAAN CURSOR */
DECLARE
    CURSOR curreg IS SELECT * FROM regions;
    kode REGIONS.REGION_ID%TYPE;
    nama REGIONS.REGION_NAME%TYPE;
BEGIN
    OPEN curreg;
        LOOP
            FETCH curreg INTO kode, nama;
            EXIT WHEN curreg%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Kode  : '||kode);
            DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
        END LOOP;
    CLOSE curreg;
END;
/
    /* MEMBUAT CURSOR FOR LOOP */
DECLARE
    CURSOR curreg IS SELECT * FROM regions;
BEGIN
    --OPEN curreg;
        FOR x IN curreg LOOP
            --FETCH curreg INTO kode, nama;
            --EXIT WHEN curreg%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Kode  : '||x.region_id);
            DBMS_OUTPUT.PUT_LINE('Nama  : '||x.region_name);
        END LOOP;
    --CLOSE curreg;
END;
/
    /* MEMBUAT CURSOR TANPA DECLARE */
BEGIN
    --OPEN curreg;
        FOR x IN (SELECT * FROM regions) LOOP
            --FETCH curreg INTO kode, nama;
            --EXIT WHEN curreg%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Kode  : '||x.region_id);
            DBMS_OUTPUT.PUT_LINE('Nama  : '||x.region_name);
        END LOOP;
    --CLOSE curreg;
END;
/
    /* MEMBUAT CURSOR YANG MENGGUNAKAN PARAMETER */
DECLARE
    CURSOR curemp (nip NUMBER) IS SELECT first_name, last_name, salary FROM employees WHERE employee_id<=nip;
    TYPE emp_type IS RECORD
    (
        v_nama_depan VARCHAR(25),
        v_nama_belakang VARCHAR(25),
        v_gaji NUMBER
    );
    emp emp_type;
BEGIN
    OPEN curemp(105);
    LOOP
            FETCH curemp INTO emp;
            EXIT WHEN curemp%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Nama Depan    : '||emp.v_nama_depan);
            DBMS_OUTPUT.PUT_LINE('Nama Belakang : '||emp.v_nama_belakang);
            DBMS_OUTPUT.PUT_LINE('Gaji          : '||emp.v_gaji);
    END LOOP;
    CLOSE curemp;
END;
/

DECLARE
    CURSOR curemp (nip NUMBER) IS SELECT employee_id, first_name, last_name, salary FROM employees WHERE employee_id<=nip;
    TYPE emp_type IS RECORD
    (
        v_nip NUMBER,
        v_nama_depan VARCHAR(25),
        v_nama_belakang VARCHAR(25),
        v_gaji NUMBER
    );
    emp emp_type;
BEGIN
    OPEN curemp(105);
    LOOP
            FETCH curemp INTO emp;
            EXIT WHEN curemp%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('Nip           : '||emp.v_nip);
            DBMS_OUTPUT.PUT_LINE('Nama Depan    : '||emp.v_nama_depan);
            DBMS_OUTPUT.PUT_LINE('Nama Belakang : '||emp.v_nama_belakang);
            DBMS_OUTPUT.PUT_LINE('Gaji          : '||emp.v_gaji);
    END LOOP;
    CLOSE curemp;
END;
/