PL/SQL Function

Function


  • Is a named PL/SQL block that returns a value
  • Can be stored in the database as a schema object for repeated execution
  • Is called as part of an expression or is used to provide a parameter value for another subprogram
  • Can be grouped into PL/SQL packages

Creating Functions


Function should have at least one return statement.

Syntax to Create function

CREATE [OR REPLACE] FUNCTION function_name[(parameter1 [mode1]datatype1, . . .)]
RETURN datatype IS|AS
[local_variable_declarations; . . .]
BEGIN
--actions;
RETURN expression;
END [function_name];

Example:

CREATE OR REPLACE FUNCTION get_sal(p_id employees.employee_id%TYPE)
RETURN NUMBER IS
v_sal employees.salary%TYPE := 0;
BEGIN
SELECT salary
INTO v_sal
FROM employees
WHERE employee_id = p_id;
RETURN v_sal;
END get_sal;

Invoke the function as an expression or as a parameter value.

EXECUTE dbms_output.put_line(get_sal(100))

Output:

As a PL/SQL expression, get the results using host variables

VARIABLE b_salary NUMBER;
EXECUTE :b_salary := get_sal(100);
print b_salary;

Script output:

Advantages of User-Defined Functions in SQL Statements


  • Can extend SQL where activities are too complex, too awkward, or unavailable with SQL
  • Can increase efficiency when used in the WHERE clause to filter data, as opposed to filtering the data in the application
  • Can manipulate data values

Using a Function in a SQL Expression:

Example

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (p_value * 0.08);
END tax;

SELECT employee_id, last_name, salary, tax(salary)
FROM employees
WHERE department_id = 100;

Removing Functions:

Using the DROP statement

DROP FUNCTION f;

Leave a Reply

Your email address will not be published. Required fields are marked *