-- Execute statement ini sekali saja :
SET SERVEROUTPUT ON;

-- Source pembuatan Procedure hello (contoh while)
CREATE OR REPLACE PROCEDURE hello (jml IN NUMBER) IS
 teks VARCHAR2(20) := 'Hello World';
 i number(3) := 1;
BEGIN
	WHILE i <= jml LOOP
	 dbms_output.put_line(i || ' ' || teks);
	 i := i+1;
	END LOOP;
END;
-- contoh pemanggilan procedure
EXECUTE hello(5);


-- contoh blok PL/SQL Procedure untuk menaikkan gaji
-- menggunakan kondisional
CREATE OR REPLACE PROCEDURE naikkan_gaji IS
BEGIN 
   dbms_output.put_line  ('Kenaikan Gaji Baru:'); 
   FOR t IN (SELECT * FROM emp) 
   LOOP 
    IF (t.sal > 1500) THEN
      dbms_output.put_line(t.sal+(0.1 * t.sal));
    ELSE
      dbms_output.put_line(t.sal+(0.15 * t.sal));
    END IF;
   END LOOP; 
END; 
-- contoh pemanggilan procedure
EXECUTE naikkan_gaji


-- contoh FUNCTION
CREATE OR REPLACE FUNCTION luas_segitiga(
	alas IN FLOAT,
	tinggi IN FLOAT) 
	RETURN FLOAT 
	IS
BEGIN
	RETURN 0.5 * alas * tinggi;
END;
-- contoh penggunaan function :
SELECT luas_segitiga(4,7) FROM dual;


-- Source PL/SQL Anonymous Block, non Procedure/Function
DECLARE
   gaji emp.sal%TYPE;
BEGIN
   SELECT sal INTO gaji
   FROM emp WHERE empno = &kode_pegawai;
   IF gaji > 1500 THEN
         dbms_output.put_line('Gajinya besar, yaitu '||gaji);
   ELSE
         dbms_output.put_line('Gajinya kecil, yaitu '||gaji);
   END IF;
END;

-- SCHEMA : HR. Bukan SCOTT.
-- Function untuk mendeteksi standar gaji 
CREATE OR REPLACE FUNCTION standargaji(gaji IN NUMBER) 
	RETURN VARCHAR2 IS 
BEGIN
	IF gaji >= 10000 THEN 
		RETURN 'Tinggi';
	ELSE
		RETURN 'Biasa';
	END IF;
END;
-- contoh penggunaan function standargaji
SELECT standargaji(11000) FROM dual;
SELECT 
	employee_id, 
	first_name, 
	last_name, 
	salary, 
	standargaji(salary) AS standar_gaji
FROM employees;