0xCor3

PLSQL P9

Oct 29th, 2020 (edited)
1,037
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. NAMA    : IZZELDIN ADDARDA
  3. NIM     : 41519010045
  4. */
  5.  
  6.  
  7. SET SERVEROUTPUT ON;
  8. SET ECHO ON;
  9. SET VERIFY OFF;
  10.  
  11. /* TIPE DATA */
  12. DECLARE
  13.     TYPE kar_rec IS RECORD
  14.     (
  15.         nip CHAR(3), nama VARCHAR(10), gaji NUMBER
  16.     );
  17.     TYPE daftar_kar IS TABLE OF kar_rec INDEX BY PLS_INTEGER;
  18.     all_kar daftar_kar;
  19. BEGIN
  20.     all_kar(0).nip  := '111';
  21.     all_kar(0).nama := 'Amir';
  22.     all_kar(0).gaji := 1000;
  23.  
  24.     all_kar(1).nip  := '222';
  25.     all_kar(1).nama := 'Budi';
  26.     all_kar(1).gaji := 2000;
  27.  
  28.     FOR x IN all_kar.FIRST..all_kar.LAST LOOP
  29.         DBMS_OUTPUT.put_line(all_kar(x).nip||'  '||all_kar(x).nama||' '||all_kar(x).gaji);
  30.         DBMS_OUTPUT.put_line(all_kar(x).gaji);
  31.     END LOOP;
  32. END;
  33. /
  34.  
  35. /* BULK PROCESSING */
  36. DECLARE
  37.     TYPE emp_rec IS RECORD
  38.     (
  39.         nip EMPLOYEES.EMPLOYEE_ID%TYPE,
  40.         nama EMPLOYEES.FIRST_NAME%TYPE,
  41.         gaji EMPLOYEES.SALARY%TYPE
  42.     );
  43.     TYPE emp_list IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
  44.     all_emp emp_list;
  45. BEGIN
  46.     SELECT employee_id, first_name, salary BULK COLLECT INTO all_emp FROM employees;
  47.     FOR x IN all_emp.FIRST..all_emp.LAST LOOP
  48.         DBMS_OUTPUT.put_line(all_emp(x).nip||'  '||all_emp(x).nama||'  '||all_emp(x).gaji);
  49.     END LOOP;
  50. END;
  51. /
  52.  
  53. /* BULK COLLECT FOR ALL */
  54.     /* CREATE TABLE */
  55. CREATE TABLE emp2
  56. (
  57.     nip NUMBER,
  58.     nama VARCHAR2(25),
  59.     gaji NUMBER
  60. );
  61.     /* BULK */
  62. DECLARE
  63.     TYPE emp_rec IS RECORD
  64.     (
  65.         nip EMPLOYEES.EMPLOYEE_ID%TYPE,
  66.         nama EMPLOYEES.FIRST_NAME%TYPE,
  67.         gaji EMPLOYEES.SALARY%TYPE
  68.     );
  69.     TYPE emp_list IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
  70.     all_emp emp_list;
  71. BEGIN
  72.     SELECT employee_id, first_name, salary BULK COLLECT INTO all_emp FROM employees;
  73.     FOR x IN all_emp.FIRST..all_emp.LAST LOOP
  74.         DBMS_OUTPUT.put_line(all_emp(x).nip||'  '||all_emp(x).nama||'  '||all_emp(x).gaji);
  75.     END LOOP;
  76.     DBMS_OUTPUT.put_line('Total records : '||all_emp.COUNT);
  77.     FORALL i IN all_emp.FIRST..all_emp.LAST INSERT INTO emp2 VALUES all_emp(i);
  78.     COMMIT;
  79. END;
  80. /
  81.     /* DROP TABLE AGAR TIDAK BENTROK */
  82. DROP TABLE emp2;
  83.  
  84.     /* Creating Table emp2 & emp3 */
  85. CREATE TABLE emp2
  86. (
  87.     nip NUMBER,
  88.     nama VARCHAR2(25),
  89.     gaji NUMBER
  90. );
  91.  
  92. CREATE TABLE emp3
  93. (
  94.     nip NUMBER,
  95.     nama VARCHAR2(25),
  96.     gaji NUMBER
  97. );
  98.  
  99. /* BENCHMARKING NORMAL INSERT & BULK INSERT */
  100. DECLARE
  101.     TYPE emp_rec IS RECORD
  102.     (
  103.         nip EMPLOYEES.EMPLOYEE_ID%TYPE,
  104.         nama EMPLOYEES.FIRST_NAME%TYPE,
  105.         gaji EMPLOYEES.SALARY%TYPE
  106.     );
  107.     TYPE emp_list IS TABLE OF emp_rec INDEX BY BINARY_INTEGER;
  108.     all_emp emp_list;
  109.     lstart INTEGER;
  110. BEGIN
  111.     SELECT employee_id, first_name, salary BULK COLLECT INTO all_emp FROM employees;
  112.     lstart := DBMS_UTILITY.GET_TIME;
  113.     --INSERT BIASA
  114.     FOR x IN all_emp.FIRST..all_emp.LAST LOOP
  115.         INSERT INTO emp3 VALUES (all_emp(x).nip, all_emp(x).nama, all_emp(x).gaji);
  116.     END LOOP;
  117.     DBMS_OUTPUT.put_line('Normal Insert : '||(DBMS_UTILITY.GET_TIME() - lstart));
  118.  
  119.     --BULK INSERT
  120.     lstart := DBMS_UTILITY.GET_TIME;
  121.     FORALL i IN all_emp.FIRST..all_emp.LAST INSERT INTO emp2 VALUES all_emp(i);
  122.     DBMS_OUTPUT.put_line('Bulk Insert   : '||(DBMS_UTILITY.GET_TIME - lstart));
  123.     COMMIT;
  124. END;
  125. /
  126.  
  127. /* EXCEPTION HANDLING */
  128. DECLARE
  129.     hasil NUMBER;
  130. BEGIN
  131.     hasil := 10/0;
  132.     DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
  133. END;
  134. /
  135.  
  136. DECLARE
  137.     hasil NUMBER;
  138. BEGIN
  139.     hasil := 10/0;
  140.     DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
  141. EXCEPTION
  142.     WHEN OTHERS THEN
  143.         DBMS_OUTPUT.put_line('Ada error : '||SQLERRM);
  144. END;
  145. /
  146.  
  147. DECLARE
  148.     hasil NUMBER;
  149.     nama VARCHAR(25);
  150. BEGIN
  151.     hasil := 10/1;
  152.     DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
  153.    
  154.     SELECT first_name INTO nama FROM employees WHERE employee_id = 300;
  155.     DBMS_OUTPUT.put_line('Nama  : '||nama);
  156. EXCEPTION
  157.     WHEN OTHERS THEN
  158.         DBMS_OUTPUT.put_line('Ada error : '||SQLERRM);
  159. END;
  160. /
  161.  
  162. DECLARE
  163.     hasil NUMBER;
  164.     nama VARCHAR(25);
  165. BEGIN
  166.     hasil := 10/1;
  167.     DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
  168.    
  169.     SELECT first_name INTO nama FROM employees WHERE employee_id = 300;
  170.     DBMS_OUTPUT.put_line('Nama  : '||nama);
  171. EXCEPTION
  172.     WHEN ZERO_DIVIDE THEN
  173.         DBMS_OUTPUT.put_line('Terjadi Bagi 0');
  174.     WHEN NO_DATA_FOUND THEN
  175.         DBMS_OUTPUT.put_line('Data tidak ditemukan');
  176.     WHEN OTHERS THEN
  177.         DBMS_OUTPUT.put_line('Ada Error : '||SQLERRM);
  178. END;
  179. /
  180. /* mengasosiasikan variabel exception buatan Anda sendiri dengan error number built in */
  181. DECLARE
  182.     hasil NUMBER;
  183.     nama VARCHAR(25);
  184.     bagi_nol EXCEPTION;
  185.     kagak_nemu EXCEPTION;
  186.     PRAGMA EXCEPTION_INIT(bagi_nol, -01476);
  187.     PRAGMA EXCEPTION_INIT(kagak_nemu, -100);
  188. BEGIN
  189.     hasil := 10/1;
  190.     DBMS_OUTPUT.put_line('Hasil Bagi    : '||hasil);
  191.    
  192.     SELECT first_name INTO nama FROM employees WHERE employee_id = 100;
  193.     DBMS_OUTPUT.put_line('Nama  : '||nama);
  194. EXCEPTION
  195.     WHEN bagi_nol THEN
  196.         DBMS_OUTPUT.put_line('Terjadi Bagi 0');
  197.     WHEN kagak_nemu THEN
  198.         DBMS_OUTPUT.put_line('Data tidak ditemukan');
  199.     WHEN OTHERS THEN
  200.         DBMS_OUTPUT.put_line('Ada Error : '||SQLERRM);
  201. END;
  202. /
  203. /* Membuat Error Number Sendiri (Custom Error) */
  204. DECLARE
  205.     pembilang NUMBER := 10;
  206.     penyebut NUMBER := &pembagi;
  207.     hasil NUMBER;
  208.     salah EXCEPTION;
  209.     PRAGMA EXCEPTION_INIT(salah, -20001);
  210. BEGIN
  211.     IF penyebut = 0 THEN
  212.         RAISE_APPLICATION_ERROR (-20001, 'Pembagi tidak boleh 0!!!');
  213.     ELSE
  214.         hasil := pembilang/penyebut;
  215.         DBMS_OUTPUT.put_line('Hasil : '||hasil);
  216.     END IF;
  217. EXCEPTION
  218.     WHEN salah THEN
  219.         DBMS_OUTPUT.put_line('Ada Error : '||SQLERRM);
  220. END;
  221. /
  222.  
  223. DECLARE
  224.     pembilang NUMBER := 10;
  225.     penyebut NUMBER := &pembagi;
  226.     hasil NUMBER;
  227.     satu EXCEPTION;
  228.     dua EXCEPTION;
  229.     tiga EXCEPTION;
  230.  
  231.     PRAGMA EXCEPTION_INIT(satu, -20001);
  232.     PRAGMA EXCEPTION_INIT(dua, -20002);
  233.     PRAGMA EXCEPTION_INIT(tiga, -20003);
  234. BEGIN
  235.     IF penyebut = 1 THEN
  236.         RAISE_APPLICATION_ERROR (-20001, 'Pembagi tidak boleh 1 !!!');
  237.     ELSIF penyebut = 2 THEN
  238.         RAISE_APPLICATION_ERROR (-20002, 'Pembagi tidak boleh 2 !!!');
  239.     ELSIF penyebut = 3 THEN
  240.         RAISE_APPLICATION_ERROR (-20003, 'Pembagi tidak boleh 3 !!!');
  241.     ELSE
  242.         hasil := pembilang/penyebut;
  243.         DBMS_OUTPUT.put_line('Hasil : '||hasil);
  244.     END IF;
  245. EXCEPTION
  246.     WHEN satu THEN
  247.         DBMS_OUTPUT.put_line('Ada Error 1   : '||SQLERRM);
  248.     WHEN dua THEN
  249.         DBMS_OUTPUT.put_line('Ada Error 2   : '||SQLERRM);
  250.     WHEN tiga THEN
  251.         DBMS_OUTPUT.put_line('Ada Error 3   : '||SQLERRM);
  252.     WHEN OTHERS THEN
  253.         DBMS_OUTPUT.put_line('Ada Error     : '||SQLERRM);
  254. END;
  255. /
  256.  
  257. /* MERGE TABLE */
  258. CREATE TABLE copy_reg AS SELECT * FROM regions WHERE 1=3;
  259.  
  260. SELECT * FROM copy_reg;
  261.  
  262. INSERT INTO copy_reg VALUES (5, 'JOGJA');
  263.  
  264. INSERT INTO copy_reg VALUES (4, 'SEMARANG');
  265.  
  266. SELECT * FROM copy_reg;
  267.  
  268. SELECT * FROM regions;
  269.  
  270. BEGIN
  271.     MERGE INTO copy_reg c USING regions r ON(r.region_id=c.region_id)
  272.     WHEN MATCHED THEN
  273.         UPDATE SET
  274.             c.region_name = r.region_name
  275.     WHEN NOT MATCHED THEN
  276.         INSERT VALUES (r.region_id, r.region_name);
  277. END;
  278. /
  279.  
  280. SELECT * FROM copy_reg;
RAW Paste Data