PL/SQLÂ function:
The PL/SQL function is a named PL/SQL block that performs one or more specific tasks and must return a value.
How to pass a parameter in a function?
We can use the below modes to pass the parameters in a function:
IN-parameters: These parameters are the read-only parameters. The function cannot change the value of IN parameters.
OUT-parameters: These parameters are the write-only parameters and are used to return values to the calling program. The function can change the value of OUT parameters.
IN OUT parameters: These parameters are read and write parameters i.e. a function can read and change the IN OUT parameter value and return it to the calling program.
Syntax of PL/SQL function:
CREATE [OR REPLACE] FUNCTION function_name [parameters] RETURN return_datatype; IS|AS //Declaration block BEGIN //Execution_block Return return_variable; EXCEPTION //Exception block Return return_variable; END; /
How to create a function?
create or replace function getMultiple(num1 in number, num2 in number) return number is num3 number(8); begin num3 :=num1*num2; return num3; end; /
How to execute a function?
A function return value can be assigned to a variable.
result := getMultiple(4, 5);
As a part of a SELECT statement:
SELECT getMultiple(4, 5) FROM dual;
In a PL/SQL Statement:
dbms_output.put_line(getMultiple(4, 5));
How to drop a function?
DROP FUNCTION function_name;