Function

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.

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;

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;
/

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;
/

Also see

• Anonymous block
• Procedure
• Package
• Trigger

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	#