Requirement:

 Write a function for finding employee's department id by passing employee's id.

 employee_id will be passed as a parameter.

fUNCTION called find_department: (The difference between the procedure and function - function always return a value).

CREATE OR REPLACE FUNCTION find_department(parm_employee_id IN NUMBER)

RETURN NUMBER

IS

local_dept_id NUMBER;

SELECT department_id

INTO local_dept_id

FROM employees

WHERE employee_id = parm_employee_id;

dbms_output.put_line ('Department id for '||parm_employee_id||' is '|| local_dept_id);

RETURN(local_dept_id);

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line (SQLERRM);

local_dept_id := 0;

RETURN(local_dept_id);

END;

Compile the above code in PL/SQL window and function will be created in the database.

Execute this function (With DBMS_ouput turned on) by passing a employee_id (example 100),the result will be displayed.

Note;  parm_employee_id is the parameter passed IN.

       local_dept_id - is a local variable used to SELECT INTO values.

       dbms_output_put_line - prints the output

       EXCEPTION - part that handles error and in the case function will return 0 as the department.