Database Reference
In-Depth Information
The return statement in a function returns control to the calling program and returns the
results of the function
The return statement of a procedure returns control to the calling program and cannot re-
turn a value
Functions can be called from SQL, procedure cannot
Functions are considered expressions, procedure are not
The syntax for creating a function is:
CREATE [OR REPLACE] FUNCTION function_name (parameter list) RETURN data-
type
IS
BEGIN
<body of the function>
RETURN (return value);
END;
Example11:
CREATE OR REPLACE FUNCTION staff_func
RETURN VARCHAR(20);
IS
staff_name VARCHAR(20);
BEGIN
SELECT Name INTO staff_name
FROM Staff WHERE StaffID = '101';
RETURN staff_name ;
END;
Execution of PL/SQL Function
A function can be executed in the following ways. 1) Since a function returns a value we
can assign it to a variable.
employee_name := staff_func ;
If 'employee_name' is of datatype varchar we can store the name of the employee by as-
signing the return type of the function to it.
2) As a part of a SELECT statement
SELECT staff_func FROM dual;
3) In a PL/SQL Statements like,
dbms_output.put_line(staff_func);
