This shows you the differences between two versions of the page.
kurs:interface_package_fuer_working_salary_mit_nds [2014/09/10 21:22] |
kurs:interface_package_fuer_working_salary_mit_nds [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | <code> | ||
+ | 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; | ||
+ | / | ||
+ | </code> |