Return Types : Function Return « Function Procedure Packages « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Function Procedure Packages
  3. Function Return

In a function, you must declare a return datatype.

The return datatype can be any datatype allowed by Oracle, such as

  1. CHAR
  2. VARCHAR2
  3. NUMBER
  4. INTEGER
  5. DATE
  6. BOOLEAN (true/false values)
  7. TABLE
  8. RECORD
SQL>
SQL> CREATE OR REPLACE PACKAGE valerr
  2  IS
  3     FUNCTION get RETURN VARCHAR2;
  4  END valerr;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY valerr
  2  IS
  3     v VARCHAR2(1) := 'abc';
  4
  5     FUNCTION get RETURN VARCHAR2
  6     IS
  7     BEGIN
  8        RETURN v;
  9     END;
 10  BEGIN
 11     dbms_output.put_line ('Before I show you v...');
 12
 13  EXCEPTION
 14    WHEN OTHERS
 15    THEN
 16      dbms_output.put_line ('Trapped the error!');
 17
 18  END valerr;
 19  /

Package body created.

SQL>
SQL>
SQL>








27.3.Function Return
27.3.1.Return Types
27.3.2.Returning values with functions
27.3.3.Return number from a function
27.3.4.Return value from a function
27.3.5.Multiple RETURN Statements
27.3.6.Returning a list based on parameters
27.3.7.Return date value from a function
27.3.8.A pipelined Table Function that returns a PL/SQL type
27.3.9.Return column type
27.3.10.Statements after Return will not be executed
27.3.11.create a function to return a employee record. accept employee numner return all fields.
27.3.12.Return a table collection
27.3.13.Demonstrate returning a record.
27.3.14.Return cursor from function
27.3.15.Return user-defined type