How to create a function to return some data
Few steps as simple as this:
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)
BEGINSELECT 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);
EXCEPTIONWHEN OTHERS THEN
local_dept_id:= 0; RETURN(local_dept_id);
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.