akbarbasya26

PLSQL P10

Nov 5th, 2020
1,113
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. Nama    : Akbar Basyarudin
  3. NIM     : 41519010046
  4. */
  5.  
  6. SET SERVEROUTPUT ON;
  7. SET ECHO ON;
  8.  
  9. CREATE OR REPLACE PROCEDURE test_proc
  10. IS
  11. BEGIN  
  12.     DBMS_OUTPUT.PUT_LINE('This is my first procedure');
  13. END;
  14. /
  15.  
  16. EXECUTE test_proc;
  17.  
  18. EXEC test_proc;
  19.  
  20. BEGIN
  21.     test_proc;
  22. END;
  23. /
  24.  
  25. SHOW ERROR;
  26.  
  27. SELECT object_name FROM user_objects WHERE object_type='PROCEDURE';
  28.  
  29. SELECT line, text FROM user_source WHERE name='TEST_PROC';
  30.  
  31. SET PAGESIZE 1000;
  32. COL TEXT FORMAT A55;
  33. COL LINE FORMAT 99;
  34. SELECT line, text FROM user_source WHERE name='TEST_PROC';
  35.  
  36. /* Menambahkan parameter */
  37. CREATE OR REPLACE PROCEDURE cetak_data
  38. (nip NUMBER, nama VARCHAR, gaji NUMBER)
  39. IS
  40. BEGIN
  41.     DBMS_OUTPUT.PUT_LINE('Nip   : '||nip);
  42.     DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
  43.     DBMS_OUTPUT.PUT_LINE('Gaji  : '||gaji);
  44. END;
  45. /
  46.  
  47. EXEC cetak_data(111, 'Amir', 10000);
  48.  
  49. EXEC cetak_data(gaji=>2000, nip=>333, nama=>'Amir');
  50.  
  51. /* Melihat daftar parameter dari procedure */
  52. DESCRIBE cetak_data;
  53.  
  54. CREATE OR REPLACE PROCEDURE test_in(x IN NUMBER)
  55. IS
  56. BEGIN
  57.     x:=0;
  58. END;
  59. /
  60.  
  61. CREATE OR REPLACE PROCEDURE test_in(x IN NUMBER)
  62. IS
  63.     hasil NUMBER;
  64. BEGIN
  65.     hasil :=x;
  66. END;
  67. /
  68.  
  69. VARIABLE kode NUMBER;
  70. PRINT kode;
  71.  
  72. BEGIN
  73.     :kode := 100;
  74. END;
  75. /
  76.  
  77. SELECT employee_id, first_name, salary FROM employees WHERE employee_id=:kode;
  78.  
  79. EXEC :kode:=101;
  80.  
  81. PRINT kode;
  82.  
  83. SELECT employee_id, first_name, salary FROM employees WHERE employee_id=:kode;
  84.  
  85. CREATE OR REPLACE PROCEDURE kalikan
  86. (a IN NUMBER, b IN NUMBER, c OUT NUMBER)
  87. IS
  88. BEGIN
  89.     c:=a*b;
  90. END;
  91. /
  92.  
  93. DESC kalikan;
  94.  
  95. VARIABLE hasil NUMBER;
  96. PRINT hasil;
  97.  
  98. EXEC kalikan(9, 8, :hasil);
  99. PRINT hasil;
  100.  
  101. /* CONTOH IN OUT */
  102. CREATE OR REPLACE PROCEDURE reset_var(x IN OUT NUMBER)
  103. IS
  104. BEGIN
  105.     x:=0;
  106. END;
  107. /
  108.  
  109. DESC reset_var;
  110.  
  111. PRINT hasil;
  112.  
  113. EXEC reset_var(:hasil);
  114.  
  115. PRINT hasil;
  116.  
  117. /* MENGGUNAKAN TABEL DALAM PROCEDURE (1 DATA) */
  118. CREATE OR REPLACE PROCEDURE cari_peg(nip NUMBER)
  119. IS
  120.     nama VARCHAR(25);
  121.     gaji NUMBER;
  122.     posisi VARCHAR(25);
  123. BEGIN
  124.     SELECT first_name, salary, job_id INTO nama, gaji, posisi
  125.     FROM employees WHERE employee_id = nip;
  126.     DBMS_OUTPUT.PUT_LINE('----------------------');
  127.     DBMS_OUTPUT.PUT_LINE('Nip   : '||nip);
  128.     DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
  129.     DBMS_OUTPUT.PUT_LINE('Gaji  : '||gaji);
  130.     DBMS_OUTPUT.PUT_LINE('Posisi: '||posisi);
  131.     DBMS_OUTPUT.PUT_LINE('----------------------');
  132. EXCEPTION
  133.     WHEN OTHERS THEN
  134.         DBMS_OUTPUT.PUT_LINE('Pegawai Nip   : '||nip||' tidak ada');
  135. END;
  136. /
  137.  
  138. EXEC cari_peg(100);
  139.  
  140. EXEC cari_peg(1000);
  141.  
  142. /* Menggunakan Tabel Dalam Procedure (banyak data): */
  143. CREATE OR REPLACE PROCEDURE emp_report(kode_dept NUMBER)
  144. IS
  145.     TYPE emp_rec IS RECORD
  146.     (
  147.         nip NUMBER, nama VARCHAR(25), posisi VARCHAR(25)
  148.     );
  149.     CURSOR cur_emp(kode NUMBER) IS SELECT employee_id, first_name, job_id
  150.     FROM employees WHERE department_id = kode;
  151.     emp emp_rec;
  152. BEGIN
  153.     OPEN cur_emp(kode_dept);
  154.     LOOP
  155.         FETCH cur_emp INTO emp;
  156.         EXIT WHEN cur_emp%NOTFOUND;
  157.         DBMS_OUTPUT.PUT_LINE(emp.nip||'|'||emp.nama||'|'||emp.posisi);
  158.     END LOOP;
  159. EXCEPTION
  160.     WHEN OTHERS THEN
  161.         DBMS_OUTPUT.PUT_LINE('Department ID '||kode_dept||' tidak ada!!');
  162. END;
  163. /
  164.  
  165. EXEC emp_report(90);
  166.  
  167. /* FUNCTION */
  168. CREATE OR REPLACE FUNCTION kali(a NUMBER, b NUMBER) RETURN NUMBER
  169. IS
  170.     hasil NUMBER;
  171. BEGIN
  172.     hasil:=a*b;
  173.     RETURN hasil;
  174. END;
  175. /
  176.  
  177. SELECT kali(9,2) FROM dual;
  178.  
  179. SELECT first_name, salary, kali(salary, 22) FROM employees WHERE employee_id = 100;
  180.  
  181. /* MENAMPILKAN FUNCTION TANPA SELECT */
  182. DECLARE
  183.     x NUMBER;
  184. BEGIN
  185.     x:= kali(9,8);
  186.     DBMS_OUTPUT.PUT_LINE(x);
  187. END;
  188. /
  189.  
  190. BEGIN
  191.     DBMS_OUTPUT.PUT_LINE(kali(8,6));
  192. END;
  193. /
  194.  
  195. DESC kali;
  196.  
  197. /* PACKAGE */
  198. CREATE OR REPLACE PACKAGE test_pack
  199. IS
  200.     PROCEDURE myproc;
  201.     FUNCTION myfunc RETURN NUMBER;
  202. END;
  203. /
  204.  
  205. CREATE OR REPLACE PACKAGE BODY test_pack
  206. IS
  207.     PROCEDURE myproc
  208.     IS
  209.     BEGIN
  210.         DBMS_OUTPUT.PUT_LINE('myproc...');
  211.     END;
  212.  
  213.     FUNCTION myfunc RETURN NUMBER
  214.     IS
  215.     BEGIN
  216.         RETURN 10;
  217.     END;
  218. END;
  219. /
  220.  
  221. EXEC test_pack.myproc;
  222.  
  223. SELECT test_pack.myfunc FROM dual;
  224.  
  225. /* TRIGGER */
  226. CREATE TABLE myreg AS SELECT * FROM regions;
  227.  
  228. CREATE OR REPLACE TRIGGER mytrig
  229. AFTER INSERT OR UPDATE OR DELETE ON myreg
  230. BEGIN
  231.     DBMS_OUTPUT.PUT_LINE('Trigger di eksekusi...');
  232. END;
  233. /
  234.  
  235. DELETE myreg WHERE region_id=1;
  236.  
  237. UPDATE myreg SET region_name='xxx' WHERE region_id=2;
  238.  
  239. /* TRIGGER YANG LEBIH USEABLE */
  240. CREATE OR REPLACE TRIGGER aman
  241. BEFORE INSERT OR UPDATE OR DELETE ON myreg
  242. BEGIN
  243.     IF TO_CHAR(SYSDATE, 'DY')='KAM' THEN
  244.         IF INSERTING THEN
  245.             RAISE_APPLICATION_ERROR (-20001, 'Maaf tidak boleh INSERT hari kamis!!!');
  246.         ELSIF UPDATING THEN
  247.             RAISE_APPLICATION_ERROR (-20001, 'Maaf tidak boleh UPDATE hari kamis!!!');
  248.         ELSIF DELETING THEN
  249.             RAISE_APPLICATION_ERROR (-20001, 'Maaf tidak boleh DELETE hari kamis!!!');
  250.         END IF;
  251.     END IF;
  252. END;
  253. /
  254.  
  255. UPDATE myreg SET region_name='xxx' WHERE region_id = 2;
  256.  
  257. SELECT TO_CHAR(SYSDATE, 'DY') FROM dual;
RAW Paste Data