Oracle

UAS

Mhs_STMIKMJ : Tugas-13.3.0113-HendraSetia

Emailto: nizhamt@gmail.com

NIM : 13.3.0113

Nama : Hendra Setia

SOAL :

Dengan database HR, buatlah stored procedure dengan PL/SQL untuk masalah berikut ini:

1. Direktur sebuah perusahaan ingin menaikkan gaji karyawan bagian keuangan sebesar 10%, namun manager bagian keuangan kenaikannya hanya 5%.

2. Tampilkan nama karyawan bagian SALES, jika ditemukan karyawan dengan gaji dibawah 10000, maka tampilkan sebanyak dua kali.

-- Jawaban Soal No. 1

-- ASUMSI :

-- ID Bagian Keuangan => Department_ID = 100

-- Job_ID = 'FI_ACCOUNT’ => Karyawan Bagian Keuangan

-- Job_ID = 'FI_MGR' => Manager Bagian Keuangan

-- SELECT first_name, last_name,  job_ID,  salary, department_ID FROM employees WHERE department_ID='100'

-- STORED PROCEDURE-NYA

create or replace PROCEDURE soal_pertama AS

   n_salary employees.salary%TYPE;

CURSOR curr_emp

IS

   SELECT employee_ID, first_name, last_name, job_ID, salary, department_ID

   FROM employees

   WHERE department_ID = 100;

   rec_emp curr_emp%ROWTYPE;

BEGIN

   OPEN curr_emp;

   LOOP

      FETCH curr_emp INTO rec_emp;

      EXIT WHEN curr_emp%NOTFOUND;

      IF rec_emp.job_ID = 'FI_MGR' THEN

         n_salary := 0.05;

      ELSE

         n_salary :=0.1;

      END IF;

      UPDATE EMPLOYEES

         SET SALARY = rec_emp.salary + (n_salary * rec_emp.salary)

      WHERE EMPLOYEE_ID = rec_emp.employee_ID;

   END LOOP;

   CLOSE curr_emp;

END;

/

-- Jalankan PL/SQL nya

EXECUTE soal_pertama;

/

-- Jawaban Soal No. 2

-- ASUMSI :

-- ID Bagian Sales => department_ID = 80

-- SELECT first_name, last_name, job_ID, salary, department_ID FROM employees WHERE department_ID='80' AND salary < 10000

-- STORED PROCEDURE-NYA

create or replace PROCEDURE soal_kedua AS

CURSOR curr_emp

IS

   SELECT first_name || ' ' || last_name AS employee_name, department_ID, salary

   FROM employees

   WHERE department_ID=80;

   rec_emp curr_emp%ROWTYPE;

BEGIN

   OPEN curr_emp;

   LOOP

      FETCH curr_emp INTO rec_emp;

      EXIT WHEN curr_emp%NOTFOUND;

      IF rec_emp.salary < 10000 THEN

         DBMS_OUTPUT.PUT_LINE(rec_emp.employee_name);

         DBMS_OUTPUT.PUT_LINE(rec_emp.employee_name);

      ELSE

         DBMS_OUTPUT.PUT_LINE(rec_emp.employee_name);

      END IF;

   END LOOP;

   CLOSE curr_emp;

END;

/

-- Jalankan PL/SQL nya

EXECUTE soal_kedua;

/