Berikut adalah cara penggunaan Procedure Oracle
Format :
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Contoh 1 :
CREATE OR REPLACE PROCEDURE pLooping IS
i INT(3);
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Nilai : ' || i);
END LOOP;
END pLooping;
/
Output :
SQL> set serveroutput on;
SQL> execute pLooping;
Nilai : 1
Nilai : 2
Nilai : 3
Nilai : 4
Nilai : 5
PL/SQL procedure successfully completed.
Contoh 2 :
CREATE OR REPLACE PROCEDURE pPenjumlahan(x IN NUMBER, x IN NUMBER)
IS
jumlah Number;
BEGIN
SELECT x + y INTO jumlah FROM dual;
DBMS_OUTPUT.PUT_LINE('Hasil Penjumlahan : '||jumlah);
END pPenjumlahan;
/
Output:
SQL> SET SERVEROUTPUT ON
SQL> EXEC pPenjumlahan(3,10);
Hasil Penjumlahan : 13
PL/SQL procedure successfully completed.
Catatan
Untuk menghapus procedure
DROP PROCEDURE nama_procedure;
Contoh :
DROP PROCEDURE pLooping;
DROP PROCEDURE nama_procedure;
Untuk melihat daftar Procedure :
SQL> set linesize 1000;
SQL> SELECT object_id, object_name, object_type FROM user_procedures;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- -------------------------------------------------
114664 pLooping PROCEDURE
114665 pPenjumlahan PROCEDURE