bAxD6nzq

1CREATE OR REPLACE PACKAGE BONUS_KARYAWAN AS
2 PROCEDURE CALC_BONUS(employee_salary IN employees.salary%type, date_hired IN employees.hire_date%type, result OUT VARCHAR2);
3 FUNCTION VALUE_BONUS(p_id IN NUMBER) RETURN VARCHAR2; 
4END;
5/
6
7
8CREATE OR REPLACE PACKAGE BODY BONUS_KARYAWAN AS
9 PROCEDURE CALC_BONUS(employee_salary IN employees.salary%type, date_hired IN employees.hire_date%type, result OUT VARCHAR2) IS
10 BEGIN
11  RESULT := 'Employees hired on '|| date_hired || ' get bonus '|| employee_salary;
12 END;
13 FUNCTION VALUE_BONUS(p_id IN NUMBER) RETURN VARCHAR2
14 AS
15  v_hasil NUMBER;
16  v_tanggal DATE;
17  v_result VARCHAR2(500);
18 BEGIN
19  SELECT SALARY*10, HIRE_DATE INTO v_hasil, v_tanggal FROM EMPLOYEES
20  WHERE EMPLOYEE_ID=p_id;
21  CALC_BONUS(v_hasil, v_tanggal, v_result);
22  RETURN v_result;
23 END;
24END;
25/
26
27CREATE OR REPLACE TRIGGER DISPLAY_SALARY_CHANGES
28BEFORE DELETE OR INSERT OR UPDATE ON EMPLOYEES
29FOR EACH ROW
30WHEN (new.employee_id > 0)
31DECLARE
32  salary_diff NUMBER;
33BEGIN
34  salary_diff := :new.salary - :old.salary;
35  DBMS_OUTPUT.PUT_LINE('Old salary: '||:old.salary);
36  DBMS_OUTPUT.PUT_LINE('New salary: '||:new.salary);
37  DBMS_OUTPUT.PUT_LINE('Difference salary: '||salary_diff);
38END;
39/
40
41CREATE OR REPLACE TRIGGER TEST_VALID
42BEFORE INSERT ON EMPLOYEES
43FOR EACH ROW
44WHEN (new.employee_id IS NOT NULL)
45  BEGIN
46    DECLARE EMP_ID NUMBER := 0;
47    BEGIN SELECT MAX(employee_id) INTO emp_id FROM employees;
48    IF emp_id > 0 and emp_id < :new.employee_id THEN
49      RAISE_APPLICATION_ERROR(-20634, 'employee_id already exists');
50    END IF;
51  END;
52END;
53/
54
55INSERT INTO EMPLOYEES(employee_id, first_name, last_name, email, hire_date, job_id)
56VALUES(207, 'Fikry', 'Hazmi', 'hzm@gmail.com', sysdate, 35)
57/
58
59CREATE TABLE HR_AUDIT(
60  EVENT_TYPE VARCHAR2(30),
61  LOGON_DATE DATE,
62  LOGON_TIME VARCHAR2(15),
63  LOGOFF_DATE DATE,
64  LOGOFF_TIME VARCHAR(15)
65);
66/
67
68CREATE OR REPLACE TRIGGER HR_LOGON_AUDIT
69AFTER LOGON ON SCHEMA
70BEGIN
71 INSERT INTO HR_AUDIT VALUES(
72  ORA_SYSEVENT,
73  SYSDATE,
74  TO_CHAR(SYSDATE, 'HH24:MI:SS'),
75  NULL,
76  NULL
77 );
78 COMMIT;
79END;
80/
81
82DECLARE
83 c_id employees.employee_id%type;
84 c_name employees.last_name%type;
85 c_sal employees.salary%type;
86 v_pajak NUMBER;
87 CURSOR c_employees IS
88   SELECT employee_id, last_name, salary FROM employees;
89BEGIN
90 OPEN c_employees;
91 LOOP
92  FETCH c_employees INTO c_id, c_name, c_sal;
93   EXIT WHEN c_employees%NOTFOUND;
94   IF c_sal > 7000 THEN
95    v_pajak := c_sal * 10/100;
96   ELSIF c_sal <= 7000 THEN
97    v_pajak := c_sal * 5/100;
98   ELSE
99    v_pajak := 0;
100   END IF;
101   DBMS_OUTPUT.PUT_LINE('ID Karyawan: ' || c_id || ' dengan nama ' || c_name || ' mempunyai gaji sebesar ' || c_sal || ' dan harus membayar pajak sebesar ' || v_pajak);
102  END LOOP;
103  CLOSE c_employees;
104END;
105/