This shows you the differences between two versions of the page.
— |
kurs:salary [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ==== Spec ==== | ||
+ | |||
+ | <code> | ||
+ | PACKAGE PCK_SALARY | ||
+ | IS | ||
+ | -- | ||
+ | -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE | ||
+ | -- directory of SQL Navigator | ||
+ | -- | ||
+ | -- Purpose: Briefly explain the functionality of the package | ||
+ | -- | ||
+ | -- MODIFICATION HISTORY | ||
+ | -- Person Date Comments | ||
+ | -- --------- ------ ------------------------------------------ | ||
+ | -- Enter package declarations as shown below | ||
+ | |||
+ | tax_percent number; | ||
+ | euro2dollar number; | ||
+ | | ||
+ | called_init boolean := false; | ||
+ | |||
+ | PROCEDURE init; | ||
+ | |||
+ | PROCEDURE calc_tax; | ||
+ | |||
+ | PROCEDURE calc_tax(v_employee_id IN number); | ||
+ | | ||
+ | FUNCTION tax | ||
+ | ( salary IN number) | ||
+ | RETURN number; | ||
+ | |||
+ | END; -- Package spec | ||
+ | </code> | ||
+ | ==== Body ==== | ||
+ | <code> | ||
+ | CREATE OR REPLACE PACKAGE BODY PCK_SALARY | ||
+ | IS | ||
+ | -- | ||
+ | -- To modify this template, edit file PKGBODY.TXT in TEMPLATE | ||
+ | -- directory of SQL Navigator | ||
+ | -- | ||
+ | -- Purpose: Briefly explain the functionality of the package body | ||
+ | -- | ||
+ | -- MODIFICATION HISTORY | ||
+ | -- Person Date Comments | ||
+ | -- --------- ------ ------------------------------------------ | ||
+ | -- Enter procedure, function bodies as shown below | ||
+ | |||
+ | PROCEDURE init | ||
+ | IS | ||
+ | BEGIN | ||
+ | select isnumeric(par_value) into tax_percent from parameter | ||
+ | where par_key='tax_percent'; | ||
+ | called_init := true; | ||
+ | END; | ||
+ | |||
+ | PROCEDURE calc_tax(v_employee_id IN number) | ||
+ | is | ||
+ | BEGIN | ||
+ | update emp_bonus | ||
+ | set tax = tax(salary) | ||
+ | where emp_id = v_employee_id; | ||
+ | end; | ||
+ | |||
+ | |||
+ | PROCEDURE calc_tax | ||
+ | IS | ||
+ | cursor cur_emp_bonus is | ||
+ | select * from emp_bonus | ||
+ | for update of tax; | ||
+ | | ||
+ | rec_emp_bonus emp_bonus%rowtype; | ||
+ | BEGIN | ||
+ | | ||
+ | for rec_emp_bonus in cur_emp_bonus loop | ||
+ | calc_tax(rec_emp_bonus.emp_id); | ||
+ | end loop; | ||
+ | -- dbms_output.put_line(isnumeric(tax_percent)); | ||
+ | END; | ||
+ | |||
+ | |||
+ | FUNCTION tax | ||
+ | ( salary IN number) | ||
+ | RETURN number | ||
+ | is | ||
+ | begin | ||
+ | if called_init=false then | ||
+ | init; | ||
+ | end if; | ||
+ | |||
+ | return salary*isnumeric(tax_percent); | ||
+ | end; | ||
+ | |||
+ | -- Enter further code below as specified in the Package spec. | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | </code> | ||
+ | |||