Function

From Oracle FAQ
Jump to: navigation, search

A function is a block of PL/SQL code named and stored within the database. A function always returns a single value to its caller.

Contents

[edit] Creating and dropping functions

Create a function:

CREATE OR REPLACE FUNCTION mult(n1 NUMBER, n2 NUMBER) RETURN NUMBER
AS
BEGIN
  RETURN n1 * n2;
END;
/

Remove the function from the database:

DROP FUNCTION mult;

[edit] Calling functions

Call the above function from SQL:

SQL>  SELECT mult(10, 2) FROM dual;
MULT(10,2)
----------
        20

Call the above function from SQL*Plus:

SQL> VARIABLE val NUMBER
SQL> EXEC :val := mult(10, 3);
PL/SQL procedure successfully completed.
SQL> PRINT :val
       VAL
----------
        30

Calling the function from PL/SQL:

DECLARE
  v_val NUMBER;
BEGIN
  v_val := mult(10, 4);
  Dbms_output.Put_Line('Value is: '|| v_val);
END;
/

[edit] Examples

Simple lookup function (lookup an employee's salary):

CREATE OR REPLACE FUNCTION get_salary (p_empno NUMBER)
   RETURN NUMBER
AS
  v_sal emp.sal%TYPE;
BEGIN
  SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
  RETURN v_sal;
END;
/

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #