Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL RETURN Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: RETURN

Definition:
In Oracle PL/SQL, the term RETURN can be used either as a clause or a statement. It shows different behavior in each context.

1. As a Clause

As a clause, RETURN is a mandatory element in a function definition. It defines the data type of the value returned by the function. It can be a primitive data type like NUMBER, VARCHAR2, DATE or UDT (database collection types). A function can logically return only one value from the body. (To work around this limitation, multiple values are often concatenated and returned as a single value, which are then split back apart for further use.)

Example Syntax:

CREATE OR REPLACE FUNCTION [FUNCTION NAME]
RETURN [RETURN TYPE]
IS
BEGIN
...
END;


Example Usage:

The function "F_RET_VAL" is created which returns a NUMBER value, i.e. 1 from the function body.

CREATE OR REPLACE FUNCTION F_RET_VAL 
RETURN NUMBER
IS
BEGIN
RETURN 1;
END;


2. As a PL/SQL Statement

In PL/SQL code the RETURN keyword can appear in any subprogram as a statement. As soon as it is encountered in the subprogram body, Oracle server immediately sends the execution control back to the immediate calling code or host environment. No further statements are processed in the block following the RETURN statement.

Note that a function can contain RETURN both as a clause and a statement.

Example Syntax:

CREATE OR REPLACE [SUBPROGRAM] [NAME]
IS
BEGIN
statement 1;
...
RETURN;
statement 2;
END;


In the above syntax, when the execution pointer comes to the RETURN statement, Oracle skips all subsequent statements and returns to the calling environment.

Example Usage:

In the example below, a procedure named "P_RET_VAL" is created which returns the control to the calling environment based on the value of P_EMPNO. It returns if it is NULL.

CREATE OR REPLACE PROCEDURE P_RET_VAL (P_EMPNO NUMBER DEFAULT NULL)
IS
BEGIN
IF P_EMPNO IS NULL THEN
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE(P_EMPNO);
END IF;
END;



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 69 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?