Laborbericht - DBI - 5CHIF 16/17

CREATE OR REPLACE PROCEDURE A2_1 AS


 v_FIRST_NAME EMPLOYEES.FIRST_NAME%type;
 v_LAST_NAME EMPLOYEES.LAST_NAME%type;
 v_JOB_TITLE JOBS.JOB_TITLE%type;  

BEGIN
  select FIRST_NAME, LAST_NAME, JOB_TITLE
  into v_FIRST_NAME, v_LAST_NAME, v_JOB_TITLE
  from (select FIRST_NAME, LAST_NAME, JOB_TITLE
    from EMPLOYEES
    inner join JOBS on ( EMPLOYEES.JOB_ID = JOBS.JOB_ID)
    order by hire_date)
  where ROWNUM = 1;

    DBMS_OUTPUT.put_line(v_FIRST_NAME || ' ' || v_LAST_NAME || ', ' || v_JOB_TITLE);
END A2_1;

-- Steven King, President

create or replace
procedure A2_2 as

cursor employee_cur is
  select *
  from (
    select first_name, last_name, rownum as row_number
    from employees)
  where row_number between 2 and 9;

  v_employee employee_cur%ROWTYPE;

begin
open employee_cur;
  loop
    fetch employee_cur into v_employee;
    exit when employee_cur%notfound;

   DBMS_OUTPUT.put_line(v_employee.first_name || ' ' || v_employee.last_name );
  end loop;
  close employee_cur;
end;

CREATE OR REPLACE PROCEDURE A2_3 AS

  cursor employee_cur is
    select *
    from employees;

  v_employee employee_cur%ROWTYPE;

BEGIN
  OPEN employee_cur;

  LOOP
    FETCH employee_cur INTO v_employee;
    EXIT WHEN employee_cur%NOTFOUND;

    if v_employee.DEPARTMENT_ID = 40 then
      SET_SALARY( v_employee.employee_id, v_employee.salary * 1.09);
    elsif v_employee.DEPARTMENT_ID = 70 then
      SET_SALARY( v_employee.employee_id, v_employee.salary * 1.17);
    elsif v_employee.COMMISSION_PCT > 0.35 then
      SET_SALARY( v_employee.employee_id, v_employee.salary * 1.04);
    else
      SET_SALARY( v_employee.employee_id, v_employee.salary * 1.11);
    end if;

  END LOOP;
  CLOSE employee_cur;
  NULL;
END A2_3;