السلام عليكم ممكن حد يساعدنى يا جماعة فى حل ال practice ده
Practice 2
1. Create and invoke the GET_JOB function to return a job title.
a. Create and compile a function called GET_JOB to return a job title.
b. Create a VARCHAR2 host variable called TITLE, allowing a length of 35 characters. Invoke the function with SA_REP job ID to return the value in the host variable. Print the host variable to view the result.

2. Create a function called GET_ANNUAL_COMP to return the annual salary computed from an employee’s monthly salary and commission passed as parameters.
a. Develop and store the function GET_ANNUAL_COMP, accepting parameter values for monthly salary and commission. Either or both values passed can be NULL, but the function should still return a non-NULL annual salary. Use the following basic formula to calculate the annual salary: (salary*12) + (commission_pct*salary*12)
b. Use the function in a SELECT statement against the EMPLOYEES table for employees in department 30.

3. Create a procedure, ADD_EMPLOYEE, to insert a new employee into the EMPLOYEES table. The procedure should call a VALID_DEPTID function to check whether the department ID specified for the new employee exists in the DEPARTMENTS table.
a. Create a function VALID_DEPTID to validate a specified department ID and return a BOOLEAN value of TRUE if the department exists.
b. Create the procedure ADD_EMPLOYEE to add an employee to the EMPLOYEES table. The row should be added to the EMPLOYEES table if the VALID_DEPTID function returns TRUE; otherwise, alert the user with an appropriate message. Provide the following parameters (with defaults specified in parentheses): first_name, last_name, email, job (SA_REP), mgr (145), sal (1000), comm (0), and deptid (30). Use the EMPLOYEES_SEQ sequence to set the employee_id column, and set hire_date to TRUNC(SYSDATE).
c. Call ADD_EMPLOYEE for the name Jane Harris in department 15, leaving other parameters with their default values. What is the result?
d. Add another employee named Joe Harris in department 80, leaving remaining parameters with their default values. What is the result?

ارجو الحل فى اسرع وقت انا عندى امتحان وكنت واقف على اسئلة الفصل ده
وشكرا

الحلول
#
1. Create and invoke the GET_JOB function to return a job title.
#

#
a. Create and compile a function called GET_JOB to return a job title.
#

#
CREATE OR REPLACE FUNCTION get_job (jobid IN jobs.job_id%type )
#
RETURN jobs.job_title%type IS
#
title jobs.job_title%type;
#
BEGIN
#
SELECT job_title
#
INTO title
#
FROM jobs
#
WHERE job_id = jobid;
#
RETURN title;
#
END get_job;
#
/
#
Function created

=========================التجربه====================


#
b. Create a VARCHAR2 host variable called TITLE, allowing a length of 35
#
characters. Invoke the function with SA_REP job ID to return the value in the
#
host variable. Print the host variable to view the result.
#
VARIABLE title VARCHAR2(35)
#
EXECUTE :title := get_job ('SA_REP');
#
PRINT title

=============================================



CREATE OR REPLACE FUNCTION get_annual_comp(
sal IN employees.salary%TYPE,
comm IN employees.commission_pct%TYPE)
RETURN NUMBER IS
BEGIN
RETURN (NVL(sal,0) * 12 + (NVL(comm,0) * nvl(sal,0) * 12));
END get_annual_comp;
/


=========================التجربه====================


SELECT employee_id, last_name,
get_annual_comp(salary,commission_pct) "Annual Compensation"
FROM employees
WHERE department_id=90
/

=============================================

تحياتي
محب المعرفه وحل المسائل العمليه