Oracle Package Exercises

1 /*2 Create a package that implements the following functions3 1: Build a process that calculates the EMP table based on the minimum wage that the user has passed in4 number of persons below the minimum wage and information on these personnel5 2: Build a function that calculates the date of the entry based on the passed-in month6 Number of employees7 */8 9 --Package SpecificationTen Create or ReplacePackage Emp_controller One  as A        --Create a REF CURSOR -Type Emplist isRefcursor returnEmp%RowType; -  the        procedureCheck_sal_less (v_min Number, V_count out Number, v_emplist out emplist); -         -        functionCount_same_year_emp (v_year Number)return  Number; -         + End; -  + --Package Body A Create or ReplacePackage Body Emp_controller at  as -        --implementation Process -        procedureCheck_sal_less (v_min Number, V_count out Number, v_emplist out emplist) -         as -        begin -            --calculate the number of people below the minimum wage in the table, based on the minimum wage passed in in            Select Count(*) intoV_count frommyemp -            whereSal<v_min; to             +            --store people with wages below the minimum wage in a cursor -            --Open Cursor the            OpenV_emplist for Select *  frommyemp *            whereSal<v_min; $        End;Panax Notoginseng         -        --implementation Functions the        functionCount_same_year_emp (v_year Number)return  Number +         as AV_number Number; the        begin +            --Count the number of employees entering the year based on the year passed in -            Select Count(*) intoV_number frommyemp $            whereTo_char (HireDate,'yyyy')=v_year; $             -            returnV_number; -        End; the End; - Wuyi  the --Package Program Testing - Declare WuV_count Number; - v_emp emp_controller.emplist; AboutV_e myemp%RowType; $ begin -     --calculate the number of people below the minimum wage in the table, based on the minimum wage passed in -Emp_controller.check_sal_less ( -, v_count,v_emp); -Dbms_output.put_line ('the number of people below the minimum wage of 2000 is:'||V_count||', respectively, are:'); A Loop +       FetchV_emp intov_e; the       Exit  whenV_emp%NotFound; -Dbms_output.put_line ('Name:'||V_e.ename||', Salary:'||v_e.sal); $Dbms_output.put_line ('---------------------------------------'); the     EndLoop; the End; the  the --Package Test - Declare inV_number Number; the begin the     --output test, call function directly, output return value AboutDbms_output.put_line (Emp_controller.count_same_year_emp ('1981')); the End;

Oracle Package Exercises