0xCor3

PLSQL P8

Oct 20th, 2020 (edited)
1,042
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /* SET OUTPUT ON */
  2. SET SERVEROUTPUT ON;
  3. SET ECHO ON;
  4. /* PENGGUNAAN IF */
  5. DECLARE
  6.     nama EMPLOYEES.FIRST_NAME%TYPE;
  7.     gaji EMPLOYEES.SALARY%TYPE;
  8.     nip EMPLOYEES.EMPLOYEE_ID%TYPE;
  9. BEGIN
  10.     nip:=100;
  11.     SELECT first_name, salary INTO nama, gaji FROM employees WHERE employee_id = nip;
  12.     DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
  13.     DBMS_OUTPUT.PUT_LINE('Gaji  : '||gaji);
  14.     IF (gaji <= 2000) THEN
  15.         DBMS_OUTPUT.PUT_LINE('Kategori  : Low...');
  16.     ELSIF (gaji > 2000) AND (gaji <= 5000) THEN
  17.         DBMS_OUTPUT.PUT_LINE('Kategori  : Medium...');
  18.     ELSE
  19.         DBMS_OUTPUT.PUT_LINE('Kategori  : High...');
  20.     END IF;
  21. END;
  22. /
  23. /* PENGGUNAAN CASE */
  24. DECLARE
  25.     nilai CHAR(1):='&nilai';
  26.     predikat VARCHAR(20);
  27. BEGIN
  28.     predikat :=
  29.     CASE nilai
  30.             WHEN 'A' THEN 'Excellent'
  31.             WHEN 'B' THEN 'Good'
  32.             WHEN 'C' THEN 'Fair'
  33.             WHEN 'D' THEN 'Poor'
  34.             WHEN 'E' THEN 'Failed'
  35.             ELSE 'No Such Grade'
  36.     END;
  37.     DBMS_OUTPUT.PUT_LINE('Nilai     : '||nilai);
  38.     DBMS_OUTPUT.PUT_LINE('Predikat  : '||predikat);
  39. END;
  40. /
  41. /* SET VERIFY OFF AND SHOW IT */
  42. SET VERIFY OFF;
  43. SHOW VERIFY;
  44. /* PENGGUNAAN LOOP */
  45. DECLARE
  46.     counter NUMBER:=0;
  47. BEGIN
  48.     LOOP
  49.         DBMS_OUTPUT.PUT_LINE('Loop ke-'||counter);
  50.         counter:=counter+1;
  51.         EXIT WHEN counter=10;
  52.     END LOOP;
  53. END;
  54. /
  55. /* PENGGUNAAN WHILE */
  56. DECLARE
  57.     penghitung NUMBER:=0;
  58. BEGIN
  59.     WHILE penghitung < 10 LOOP
  60.         DBMS_OUTPUT.PUT_LINE('While ke-'||penghitung);
  61.         penghitung:=penghitung+1;
  62.     END LOOP;
  63. END;
  64. /
  65. /* PENGGUNAAN FOR */
  66. BEGIN
  67.     FOR X IN 1..10 LOOP
  68.         DBMS_OUTPUT.PUT_LINE('For ke-'||X);
  69.     END LOOP;
  70. END;
  71. /
  72. BEGIN
  73.     FOR X IN 1..10 LOOP
  74.         IF MOD(X,2) = 0 THEN
  75.             DBMS_OUTPUT.PUT_LINE('For ke-'||X);
  76.         END IF;
  77.     END LOOP;
  78. END;
  79. /
  80. /* PENGGUNAAN CURSOR */
  81. DECLARE
  82.     CURSOR curreg IS SELECT * FROM regions;
  83.     kode REGIONS.REGION_ID%TYPE;
  84.     nama REGIONS.REGION_NAME%TYPE;
  85. BEGIN
  86.     OPEN curreg;
  87.         LOOP
  88.             FETCH curreg INTO kode, nama;
  89.             EXIT WHEN curreg%NOTFOUND;
  90.             DBMS_OUTPUT.PUT_LINE('Kode  : '||kode);
  91.             DBMS_OUTPUT.PUT_LINE('Nama  : '||nama);
  92.         END LOOP;
  93.     CLOSE curreg;
  94. END;
  95. /
  96.     /* MEMBUAT CURSOR FOR LOOP */
  97. DECLARE
  98.     CURSOR curreg IS SELECT * FROM regions;
  99. BEGIN
  100.     --OPEN curreg;
  101.         FOR x IN curreg LOOP
  102.             --FETCH curreg INTO kode, nama;
  103.             --EXIT WHEN curreg%NOTFOUND;
  104.             DBMS_OUTPUT.PUT_LINE('Kode  : '||x.region_id);
  105.             DBMS_OUTPUT.PUT_LINE('Nama  : '||x.region_name);
  106.         END LOOP;
  107.     --CLOSE curreg;
  108. END;
  109. /
  110.     /* MEMBUAT CURSOR TANPA DECLARE */
  111. BEGIN
  112.     --OPEN curreg;
  113.         FOR x IN (SELECT * FROM regions) LOOP
  114.             --FETCH curreg INTO kode, nama;
  115.             --EXIT WHEN curreg%NOTFOUND;
  116.             DBMS_OUTPUT.PUT_LINE('Kode  : '||x.region_id);
  117.             DBMS_OUTPUT.PUT_LINE('Nama  : '||x.region_name);
  118.         END LOOP;
  119.     --CLOSE curreg;
  120. END;
  121. /
  122.     /* MEMBUAT CURSOR YANG MENGGUNAKAN PARAMETER */
  123. DECLARE
  124.     CURSOR curemp (nip NUMBER) IS SELECT first_name, last_name, salary FROM employees WHERE employee_id<=nip;
  125.     TYPE emp_type IS RECORD
  126.     (
  127.         v_nama_depan VARCHAR(25),
  128.         v_nama_belakang VARCHAR(25),
  129.         v_gaji NUMBER
  130.     );
  131.     emp emp_type;
  132. BEGIN
  133.     OPEN curemp(105);
  134.     LOOP
  135.             FETCH curemp INTO emp;
  136.             EXIT WHEN curemp%NOTFOUND;
  137.             DBMS_OUTPUT.PUT_LINE('Nama Depan    : '||emp.v_nama_depan);
  138.             DBMS_OUTPUT.PUT_LINE('Nama Belakang : '||emp.v_nama_belakang);
  139.             DBMS_OUTPUT.PUT_LINE('Gaji          : '||emp.v_gaji);
  140.     END LOOP;
  141.     CLOSE curemp;
  142. END;
  143. /
  144.  
  145. DECLARE
  146.     CURSOR curemp (nip NUMBER) IS SELECT employee_id, first_name, last_name, salary FROM employees WHERE employee_id<=nip;
  147.     TYPE emp_type IS RECORD
  148.     (
  149.         v_nip NUMBER,
  150.         v_nama_depan VARCHAR(25),
  151.         v_nama_belakang VARCHAR(25),
  152.         v_gaji NUMBER
  153.     );
  154.     emp emp_type;
  155. BEGIN
  156.     OPEN curemp(105);
  157.     LOOP
  158.             FETCH curemp INTO emp;
  159.             EXIT WHEN curemp%NOTFOUND;
  160.             DBMS_OUTPUT.PUT_LINE('Nip           : '||emp.v_nip);
  161.             DBMS_OUTPUT.PUT_LINE('Nama Depan    : '||emp.v_nama_depan);
  162.             DBMS_OUTPUT.PUT_LINE('Nama Belakang : '||emp.v_nama_belakang);
  163.             DBMS_OUTPUT.PUT_LINE('Gaji          : '||emp.v_gaji);
  164.     END LOOP;
  165.     CLOSE curemp;
  166. END;
  167. /
  168.  
RAW Paste Data