PLSQL P10

/*
Nama    : Akbar Basyarudin
NIM     : 41519010046
*/

SET SERVEROUTPUT ON;
SET ECHO ON;

CREATE OR REPLACE PROCEDURE test_proc
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('This is my first procedure');
END;
/

EXECUTE test_proc;

EXEC test_proc;

BEGIN
    test_proc;
END;
/

SHOW ERROR;

SELECT object_name FROM user_objects WHERE object_type='PROCEDURE';

SELECT line, text FROM user_source WHERE name='TEST_PROC';

SET PAGESIZE 1000;
COL TEXT FORMAT A55;
COL LINE FORMAT 99;
SELECT line, text FROM user_source WHERE name='TEST_PROC';

/* Menambahkan parameter */
CREATE OR REPLACE PROCEDURE cetak_data
(nip NUMBER, nama VARCHAR, gaji NUMBER)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Nip   : '||nip);
    DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
    DBMS_OUTPUT.PUT_LINE('Gaji  : '||gaji);
END;
/

EXEC cetak_data(111, 'Amir', 10000);

EXEC cetak_data(gaji=>2000, nip=>333, nama=>'Amir');

/* Melihat daftar parameter dari procedure */
DESCRIBE cetak_data;

CREATE OR REPLACE PROCEDURE test_in(x IN NUMBER)
IS
BEGIN
    x:=0;
END;
/

CREATE OR REPLACE PROCEDURE test_in(x IN NUMBER)
IS
    hasil NUMBER;
BEGIN
    hasil :=x;
END;
/

VARIABLE kode NUMBER;
PRINT kode;

BEGIN
    :kode := 100;
END;
/

SELECT employee_id, first_name, salary FROM employees WHERE employee_id=:kode;

EXEC :kode:=101;

PRINT kode;

SELECT employee_id, first_name, salary FROM employees WHERE employee_id=:kode;

CREATE OR REPLACE PROCEDURE kalikan
(a IN NUMBER, b IN NUMBER, c OUT NUMBER)
IS
BEGIN
    c:=a*b;
END;
/

DESC kalikan;

VARIABLE hasil NUMBER;
PRINT hasil;

EXEC kalikan(9, 8, :hasil);
PRINT hasil;

/* CONTOH IN OUT */
CREATE OR REPLACE PROCEDURE reset_var(x IN OUT NUMBER)
IS
BEGIN
    x:=0;
END;
/

DESC reset_var;

PRINT hasil;

EXEC reset_var(:hasil);

PRINT hasil;

/* MENGGUNAKAN TABEL DALAM PROCEDURE (1 DATA) */
CREATE OR REPLACE PROCEDURE cari_peg(nip NUMBER)
IS
    nama VARCHAR(25);
    gaji NUMBER;
    posisi VARCHAR(25);
BEGIN
    SELECT first_name, salary, job_id INTO nama, gaji, posisi
    FROM employees WHERE employee_id = nip;
    DBMS_OUTPUT.PUT_LINE('----------------------');
    DBMS_OUTPUT.PUT_LINE('Nip   : '||nip);
    DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
    DBMS_OUTPUT.PUT_LINE('Gaji  : '||gaji);
    DBMS_OUTPUT.PUT_LINE('Posisi: '||posisi);
    DBMS_OUTPUT.PUT_LINE('----------------------');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Pegawai Nip   : '||nip||' tidak ada');
END;
/

EXEC cari_peg(100);

EXEC cari_peg(1000);

/* Menggunakan Tabel Dalam Procedure (banyak data): */
CREATE OR REPLACE PROCEDURE emp_report(kode_dept NUMBER)
IS
    TYPE emp_rec IS RECORD
    (
        nip NUMBER, nama VARCHAR(25), posisi VARCHAR(25)
    );
    CURSOR cur_emp(kode NUMBER) IS SELECT employee_id, first_name, job_id
    FROM employees WHERE department_id = kode;
    emp emp_rec;
BEGIN
    OPEN cur_emp(kode_dept);
    LOOP
        FETCH cur_emp INTO emp;
        EXIT WHEN cur_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp.nip||'|'||emp.nama||'|'||emp.posisi);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Department ID '||kode_dept||' tidak ada!!');
END;
/

EXEC emp_report(90);

/* FUNCTION */
CREATE OR REPLACE FUNCTION kali(a NUMBER, b NUMBER) RETURN NUMBER
IS
    hasil NUMBER;
BEGIN
    hasil:=a*b;
    RETURN hasil;
END;
/

SELECT kali(9,2) FROM dual;

SELECT first_name, salary, kali(salary, 22) FROM employees WHERE employee_id = 100;

/* MENAMPILKAN FUNCTION TANPA SELECT */
DECLARE
    x NUMBER;
BEGIN
    x:= kali(9,8);
    DBMS_OUTPUT.PUT_LINE(x);
END;
/

BEGIN
    DBMS_OUTPUT.PUT_LINE(kali(8,6));
END;
/

DESC kali;

/* PACKAGE */
CREATE OR REPLACE PACKAGE test_pack
IS
    PROCEDURE myproc;
    FUNCTION myfunc RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY test_pack
IS
    PROCEDURE myproc
    IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('myproc...');
    END;

    FUNCTION myfunc RETURN NUMBER
    IS
    BEGIN
        RETURN 10;
    END;
END;
/

EXEC test_pack.myproc;

SELECT test_pack.myfunc FROM dual;

/* TRIGGER */
CREATE TABLE myreg AS SELECT * FROM regions;

CREATE OR REPLACE TRIGGER mytrig
AFTER INSERT OR UPDATE OR DELETE ON myreg
BEGIN
    DBMS_OUTPUT.PUT_LINE('Trigger di eksekusi...');
END;
/

DELETE myreg WHERE region_id=1;

UPDATE myreg SET region_name='xxx' WHERE region_id=2;

/* TRIGGER YANG LEBIH USEABLE */
CREATE OR REPLACE TRIGGER aman
BEFORE INSERT OR UPDATE OR DELETE ON myreg
BEGIN
    IF TO_CHAR(SYSDATE, 'DY')='KAM' THEN
        IF INSERTING THEN
            RAISE_APPLICATION_ERROR (-20001, 'Maaf tidak boleh INSERT hari kamis!!!');
        ELSIF UPDATING THEN
            RAISE_APPLICATION_ERROR (-20001, 'Maaf tidak boleh UPDATE hari kamis!!!');
        ELSIF DELETING THEN
            RAISE_APPLICATION_ERROR (-20001, 'Maaf tidak boleh DELETE hari kamis!!!');
        END IF;
    END IF;
END;
/

UPDATE myreg SET region_name='xxx' WHERE region_id = 2;

SELECT TO_CHAR(SYSDATE, 'DY') FROM dual;