dokuwiki

This is an old revision of the document!

---

CREATE OR REPLACE PACKAGE pck_ws
  IS

eur2usd number;
pi constant number := 3.14;

function get_emp_by_index(i number) return varchar2;

function get_next_emp  return varchar2;
function get_next_emp2  return varchar2;

FUNCTION get_number
     ( p_name varchar2)
     RETURN number;

FUNCTION get_char
     ( p_name varchar2)
     RETURN varchar2;

FUNCTION get_date
     ( p_name varchar2)
     return date;

procedure add_emp (
    p_first_name in working_salary.first_name%type,
    p_last_name  in working_salary.last_name%type,
    p_salary     in working_salary.salary%type default null,
    p_bonus      in working_salary.bonus%type  default null,
    p_hire_date  in working_salary.hire_date%type default sysdate);

procedure chg_emp (
    p_id         in working_salary.id%type,
    p_first_name in working_salary.first_name%type default null,
    p_last_name  in working_salary.last_name%type default null,
    p_salary     in working_salary.salary%type default null,
    p_bonus      in working_salary.bonus%type  default null,
    p_hire_date  in working_salary.hire_date%type default null);

procedure get_emp (
    p_first_name in working_salary.first_name%type default null,
    p_last_name  in working_salary.last_name%type default null,
    p_salary     in working_salary.salary%type default null);

/* has to be private anyway
procedure add_emp (
    p_id         in working_salary.id%type,
    p_first_name in working_salary.first_name%type,
    p_last_name  in working_salary.last_name%type,
    p_salary     in working_salary.salary%type default null,
    p_bonus      in working_salary.bonus%type  default null,
    p_hire_date  in working_salary.hire_date%type default sysdate);
*/

END; -- Package spec
/
CREATE OR REPLACE PACKAGE BODY pck_ws
IS

e_uk_violated exception;
e_out_of_bounds exception;
e_no_emp exception;

pragma EXCEPTION_INIT
    (e_uk_violated, -00001);

cursor c_ws is select * from working_salary;

r_ws c_ws%rowtype;

type emp_type is table of working_salary%rowtype;
t_emp emp_type;

function get_next_emp return varchar2 is
begin

    if not c_ws%isopen then
      open c_ws;
    end if;

    fetch c_ws into r_ws;
    if c_ws%notfound then
        close c_ws;
        open c_ws;
        fetch c_ws into r_ws;
    end if;

    return r_ws.first_name||' '||r_ws.last_name;
end;


function get_next_emp2 return varchar2 is
begin

    if not c_ws%isopen then
      open c_ws;
    end if;

    fetch c_ws into r_ws;
    if c_ws%notfound then
        close c_ws;
        open c_ws;
        fetch c_ws into r_ws;
    end if;

    return r_ws.first_name||' '||r_ws.last_name;
end;

FUNCTION get_number( p_name varchar2) return number
as
     ret parameters.n%type;
     begin
     select  n into ret from parameters where name = p_name;
     RETURN ret;
     end;

FUNCTION get_char( p_name varchar2) return varchar2
as
     ret parameters.vc%type;
     begin
     select vc into ret from parameters where name = p_name;
     return ret;
     end;

FUNCTION get_date( p_name varchar2) return date
as
     ret parameters.d%type;
     begin
     select d into ret from parameters where name = p_name;
     return ret;
     end;


procedure add_emp (
    p_id         in working_salary.id%type,
    p_first_name in working_salary.first_name%type,
    p_last_name  in working_salary.last_name%type,
    p_salary     in working_salary.salary%type default null,
    p_bonus      in working_salary.bonus%type  default null,
    p_hire_date  in working_salary.hire_date%type default sysdate)
    is
    begin
    insert into working_salary (
       id,
       first_name,
       last_name,
       salary,
       bonus,
       hire_date
    ) values (
       p_id,
       p_first_name,
       p_last_name,
       p_salary,
       p_bonus,
       p_hire_date
    );
    EXCEPTION
    when e_uk_violated then
      update working_salary set
         bonus = nvl(p_bonus, bonus),
         salary = nvl(p_salary, salary)
      where first_name = p_first_name and
            last_name  = p_last_name;
    end;



procedure chg_emp (
    p_id         in working_salary.id%type,
    p_first_name in working_salary.first_name%type default null,
    p_last_name  in working_salary.last_name%type default null,
    p_salary     in working_salary.salary%type default null,
    p_bonus      in working_salary.bonus%type  default null,
    p_hire_date  in working_salary.hire_date%type default null)
    is
    update_stmt varchar2(1000);
    update_stmt_trim varchar2(1000);
    begin

    update_stmt := 'update working_salary set ';
    if p_first_name is not null then
      update_stmt := update_stmt || ' first_name = '||dbms_assert.enquote_literal(p_first_name)||',';
    end if;

    if p_last_name is not null then
      update_stmt := update_stmt || ' last_name = '||dbms_assert.enquote_literal(p_last_name)||',';
    end if;

    if p_salary is not null then
      update_stmt := update_stmt || ' salary = '||p_salary||',';
    end if;

    if p_bonus is not null then
      update_stmt := update_stmt || ' bonus = '||dbms_assert.enquote_literal(p_bonus)||',';
    end if;

    if p_hire_date is not null then
      update_stmt := update_stmt || ' hire_date = '||dbms_assert.enquote_literal(p_hire_date)||',';
    end if;

    update_stmt := rtrim(update_stmt,',');
    update_stmt := update_stmt || ' where id =' || p_id;
    dbms_output.put_line(update_stmt);
    execute immediate update_stmt;

end;


procedure get_emp (
    p_first_name in working_salary.first_name%type default null,
    p_last_name  in working_salary.last_name%type default null,
    p_salary     in working_salary.salary%type default null) is
    c_emp sys_refcursor;
    stmt varchar2(1000);
    r working_salary%rowtype;

    begin
    stmt := 'select * from working_salary where ';
    if p_first_name is not null then
      stmt:= stmt || 'first_name = '||dbms_assert.enquote_literal(p_first_name)||' and';
    end if;

    if p_last_name is not null then
      stmt:= stmt || 'last_name = '||dbms_assert.enquote_literal(p_last_name)||' and';
    end if;

    if p_salary is not null then
      stmt:= stmt || 'salary = '||p_salary||' and';
    end if;

    stmt := rtrim(stmt,'and');

    dbms_output.put_line(stmt);
    open c_emp for stmt;
    loop
      fetch c_emp into r;
      exit when c_emp%notfound;
      dbms_output.put_line(r.first_name||' '||
                  r.last_name ||' '||
                  r.salary     ||' '||
                  r.bonus     ||' '||
                  r.hire_date);
    end loop;
    end;

procedure add_emp (
    p_first_name in working_salary.first_name%type,
    p_last_name  in working_salary.last_name%type,
    p_salary     in working_salary.salary%type default null,
    p_bonus      in working_salary.bonus%type  default null,
    p_hire_date  in working_salary.hire_date%type default sysdate)
    is
    new_salary number;
    begin
    add_emp(p_id=>null,
            p_first_name=>p_first_name,
            p_last_name=>p_last_name,
            p_salary=>p_salary,
            p_bonus=>p_bonus,
            p_hire_date=>p_hire_date);
    end;

function get_emp_by_index(i number) return varchar2
   is
   begin
   if not t_emp.exists(i) then
     raise e_out_of_bounds;
   end if;
   return t_emp(i).first_name||' '||t_emp(i).last_name;
   end;

begin
  eur2usd := get_number('eur2usd');
  select * bulk collect into t_emp from working_salary;

END;
/