What is a Function in PL/SQL?
A function is a named PL/SQL Block which is similar to a
procedure. The major difference between a procedure and a function is, a
function must always return a value, but a procedure may or may not return a
value.
General Syntax to
create a function is
CREATE [OR REPLACE] FUNCTION function_name
[parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
|
1) Return Type: The
header section defines the return type of the function. The return datatype can
be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
For
example, let’s create a frunction called ''employer_details_func' similar to
the one created in stored proc
1>
CREATE OR REPLACE FUNCTION employer_details_func
2> RETURN VARCHAR(20);
3>
IS
5> emp_name VARCHAR(20);
6>
BEGIN
7> SELECT first_name INTO emp_name
8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10>
END;
In
the example we are retrieving the ‘first_name’ of employee with empID 100 to
variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.
How
to execute a 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
:= employer_details_func;
If
‘employee_name’ is of datatype varchar we can store the name of the employee by
assigning the return type of the function to it.
2)
As a part of a SELECT statement
SELECT
employer_details_func FROM dual;
3)
In a PL/SQL Statements like,
dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.
No comments:
Post a Comment