MAT https:/ te.edu/bbeswebdav/pid-2793891-dt-content-rid-31295535 1/ 016-M E -16

Question

MAT https:/ te.edu/bbeswebdav/pid-2793891-dt-content-rid-31295535 1/ 016-M E -160-5/MAT Objective Write a code package that can solve identified equations from Ch-5 and Ch-6 based on user input values and data read from external databases (provided). Each equation should be codded as an individual function used by the main code. Your code MUST include: 1) Ask the user to identify a unit system (SI/USCs) 2) Read numeric data from external databases (provided). 3) Ask for the equation/unknown to be solved. 4) Ask for the user input(s) 5) Lookup the needed constant value(s) from the data inputted from external source(s) 6) Conduct calculations. 7) Output answer with numeric values for all components (including 153 answer) properly named with proper units. 8) Include a re-run option

Explanation / Answer

SQL> -- Sequence that packaged function needs:
SQL>
SQL> CREATE SEQUENCE emp_sequence
START WITH 8000
INCREMENT BY 10;

Sequence created.

SQL>
SQL> -- Package specification:
SQL>
SQL> CREATE or REPLACE PACKAGE employee_management IS
  
FUNCTION hire_emp (
firstname VARCHAR2,
lastname VARCHAR2,
email VARCHAR2,
phone VARCHAR2,
hiredate DATE,
job VARCHAR2,
sal NUMBER,
comm NUMBER,
mgr NUMBER,
deptno NUMBER
) RETURN NUMBER;

PROCEDURE fire_emp(
emp_id IN NUMBER
);
  
PROCEDURE sal_raise (
emp_id IN NUMBER,
sal_incr IN NUMBER
);
END employee_management;
/

Package created.

SQL> -- Package body:
SQL>
SQL> CREATE or REPLACE PACKAGE BODY employee_management IS

FUNCTION hire_emp (
firstname VARCHAR2,
lastname VARCHAR2,
email VARCHAR2,
phone VARCHAR2,
hiredate DATE,
job VARCHAR2,
sal NUMBER,
comm NUMBER,
mgr NUMBER,
deptno NUMBER
) RETURN NUMBER
IS
new_empno NUMBER(10);
BEGIN
new_empno := emp_sequence.NEXTVAL;

INSERT INTO EMPLOYEES VALUES (
new_empno,
firstname,
lastname,
email,
phone,
hiredate,
job,
sal,
comm,
mgr,
deptno
);

RETURN (new_empno);
END hire_emp;
PROCEDURE fire_emp (
emp_id IN NUMBER
) IS
BEGIN

WHERE EMPLOYEE_ID = emp_id;

IF SQL%NOTFOUND THEN
raise_application_error(
-20011,
'Invalid Employee Number: ' || TO_CHAR(Emp_id)
);
END IF;
END fire_emp;

PROCEDURE sal_raise (
emp_id IN NUMBER,
sal_incr IN NUMBER
) IS
BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + sal_incr
WHERE EMPLOYEE_ID = emp_id;

IF SQL%NOTFOUND THEN
raise_application_error(
-20011,
'Invalid Employee Number: ' || TO_CHAR(Emp_id)
);
END IF;
END sal_raise;
END employee_management;
/

Package body created.

SQL>
SQL> -- Invoke packaged procedures:
SQL>
SQL> DECLARE
empno NUMBER(6);
sal NUMBER(6);
temp NUMBER(6);
BEGIN
empno := employee_management.hire_emp(
'John',
'Doe',
'john.doe@example.com',
'555-0100',
'20-SEP-07',
'ST_CLERK',
2500,
0,
100,
20);

DBMS_OUTPUT.PUT_LINE('New employee ID is ' || TO_CHAR(empno));
END;
/
New employee ID is 8000

PL/SQL procedure successfully completed.

SQL>

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.