PROCEDURE dalam ORACLE

Beberapa perintah atau sintaks procedure dalam oracle.

Melihat daftar prosedur, function, dan triger yang kita punya.
select distinct name, type from user_source;

Perintah yang kita gunakan untuk melihat bagian yang error saat procedure dijalankan.
show error;

Untuk melihat source code atau bagian yang ada dalam sebuah procedure, function, maupun trigger.
contoh pengecekan sourcode atau bagian yang ada dalam sebuah procedure
select text from user_source where name = 'SECURE_EMPLOYEES'
Keterangan
SECURE_EMPLOYEES : nama prosedur yang mau kita cek sourcode nya.

Contoh procedure yang digunakan untuk menghitung jumlah procedure, function, dan trigger dengan menggunakan cursor explicit.


CREATE OR REPLACE PROCEDURE BONUS IS
a user_source.type%type;
b number(10);

cursor coba is
select type, count(distinct name)
from user_source group by type;

begin
open coba;
loop
fetch coba into a, b;
exit when coba%notfound;
dbms_output.put_line(a|| ' : ' ||b|| ' buah ');

end loop;
close coba;
end;
Cara memanggil atau menjalankan procedure diatas
exec BONUS();

Contoh procedure yang digunakan untuk menghitung jumlah procedure, function, dan trigger.
CREATE OR REPLACE PROCEDURE bonus IS
p number(5);
f number(5);
t number(5);
BEGIN
select count(distinct name) into p from user_source where type = 'PROCEDURE';
select count(distinct name) into f from user_source where type = 'FUNCTION';
select count(distinct name) into t from user_source where type = 'TRIGGER';

dbms_output.put_line('Procedure :'|| p ||' biji');
dbms_output.put_line('Function :'|| f ||' biji');
dbms_output.put_line('Trigger :'|| t ||' biji');
END;
/
Cara memanggil atau menjalankan procedure diatas
exec bonus();

cara membuat prosedure insert tanpa parameter

create or replace procedure add_dept IS
dept_id departments.department_id%type;
dept_name departments.department_name%type;

BEGIN
dept_id:=280;
dept_name:='ST-Currriculum';

INSERT INTO departments(department_id, department_name)
VALUES(dept_id, dept_name);

DBMS_OUTPUT.PUT_LINE('Berhasil Menginsert'||SQL%ROWCOUNT||'baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH ADA');
END;

Keterangan

script diatas adalah sebuah contoh untuk membuat procedure insert tanpa parameter inputan dengan nama procedurenya adalah add_dept. dan isinya yaitu memasukkan data kedalam tabel departments dengan dept_id:=280; dan dept_name:='ST-Currriculum';

Ada beberapa cara memanggil prosedur, diantaranya :
1. exec add_dept();
2. call add_dept();
3. begin
add_dept();
end;

Cara membuat prosedure insert dengan parameter

CREATE OR REPLACE PROCEDURE add_dept_pake_param
(dept_id IN departments.department_id%type,
dept_name departments.department_name%type)
IS

BEGIN
INSERT INTO departments (department_id,department_name)
VALUES(dept_id,dept_name);
DBMS_OUTPUT.PUT_LINE('Berhasil Menginsert ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH ADA');
END;

Keterangan

script diatas adalah sebuah contoh untuk membuat procedure insert dengan menggunakan parameter inputan dengan nama procedurenya adalah add_dept_pake_param.

Cara Memanggil procedure ini yaitu

exec add_dept_pake_param(09,'contoh procedure dengan meminta parameter');


cara membuat prosedure delete dengan parameter

CREATE OR REPLACE PROCEDURE add_dept_del
(dept_id IN departments.department_id%type)
IS
BEGIN
DELETE FROM departments where department_id = dept_id;
DBMS_OUTPUT.PUT_LINE('Berhasil Menghapus ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH TERHAPUS');
END;

script diatas adalah sebuah contoh untuk membuat procedure delete dengan menggunakan parameter inputan dengan nama procedurenya adalah add_dept_del.


cara membuat prosedure update dengan parameter

CREATE OR REPLACE PROCEDURE add_dept_update
(dept_id IN departments.department_id%type,
dept_name departments.department_name%type)
IS

BEGIN
UPDATE departments
SET department_name = dept_name
where department_id = dept_id;

DBMS_OUTPUT.PUT_LINE('Berhasil Mengupdate ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH TERUPDATE');
END;

Keterangan

script diatas adalah sebuah contoh untuk membuat procedure update dengan menggunakan parameter inputan dengan nama procedurenya adalah add_dept_update.

CREATE OR REPLACE PROCEDURE upadate
(gaji IN employees.salary%type,
emp_id IN employees.employee_id%type)
IS

BEGIN
UPDATE employees
SET salary = gaji
where employee_id = emp_id;

DBMS_OUTPUT.PUT_LINE('Berhasil Mengupdate ' ||SQL%ROWCOUNT|| ' baris');
END;

keterangan
procedure diatas adalah sebuah procedure update dengan hasil setelah di eksekusi yaitu memberitahukan berapa banyak jumlah baris yang diupdate dalam tabel employees.

Membuat procedure dengan fungsi akar

create or replace procedure proc_akar
(a number)
IS
b number(8,3);
begin
b:=sqrt(a);
dbms_output.put_line('Hasil Akar dari ' ||a|| ' adalah ' ||b );
end;

Procedure diatas adalah contoh procedure untuk menghitung hasil akar dari sebuah nilai. nama procedure diatas adalah proc_akar.
cara memanggil procedure ini yaitu

exec proc_akar(5);

CONTOH SOAL UNTUK PROCEDURE

1. Buatlah prosedur dengan parameter input X = employee_id.
Untuk menampilkan first_name, last_name, email, salary

Jawaban:
CREATE OR REPLACE PROCEDURE emp
(x employees.employee_id%type)
is
first employees.first_name%type;
last employees.last_name%type;
mail employees.email%type;
gaji employees.salary%type;

BEGIN
select first_name, last_name, email, salary
into first, last, mail, gaji
from employees
where employee_id = x;
DBMS_OUTPUT.PUT_LINE('First Name : '||first);
DBMS_OUTPUT.PUT_LINE('Last Name : '||last);
DBMS_OUTPUT.PUT_LINE('Email : '||mail);
DBMS_OUTPUT.PUT_LINE('Salary : '||gaji);
END;

Cara memanggil atau menjalankan procedurenya
exec emp(100);

2. buatlah prosedur untuk memasukkan pada tabel region dengan value yang dimasukkan id_region dan region_name yang dimasukkan dengan value id_region dan region_name, region_name dan region_id dijadikan parameter.

Jawaban:

CREATE OR REPLACE PROCEDURE reg
(id IN regions.region_id%type,
reg_name regions.region_name%type)
IS

BEGIN
INSERT INTO regions (region_id,region_name)
VALUES(id,reg_name);
DBMS_OUTPUT.PUT_LINE('Berhasil Menginsert ' ||SQL%ROWCOUNT|| ' baris');

exception
when dup_val_on_index then
DBMS_OUTPUT.PUT_LINE('DATANYA SUDAH ADA');
END;

Cara Memanggilnya:
exec reg();

3. buatlah prosedur dengan 1 parameter IN x dan 1 parameter OUT y.
x = jari-jari lingkaran
y = hasil untuk menghitung luas lingkaran.

Jawaban:

create or replace procedure kali
(x IN number)
IS
y number(10,2);
begin
y:=3.14*(x*x);
dbms_output.put_line('Luas Lingkarannya yaitu : ' ||y);
end;

cara memanggil atau menjalankan procedure diatas
exec kali(10);

atau

create or replace procedure kali_param
(x IN number, y OUT number)
IS
begin
y:=3.14*(x*x);
end;

cara memanggil atau menjalankan procedurenya

declare
x number(10);
y number(10,2);
begin
x:=&nilai_x;
kali_param(x,y);
dbms_output.put_line('Luas Lingkarannya dengan jari-jari ' ||x|| ' yaitu : ' ||y);
end;