, , ,

RETURN statement in PL SQL

The use of the RETURN statement is unique to functions. The RETURN statement is used to return some value. In fact, the primary reason for storing a PL/SQL block as a function is to return this value—this is the purpose of the function. For example, if a function is meant to compute the total payments received so far from guest reservations booked on a cruise, then the function will do whatever it needs to do to arrive at this final value and use the RETURN statement at the end to send the result back to the function call.
If you attempt to compile a function that has no RETURN statement, you will succeed, and the function will be stored in the data dictionary with a status of VALID. However, when you attempt to execute the function, you will receive a message like this:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at “[schema.function_name]”, line 6
ORA-06512: at line 1
Therefore, it is the developer’s responsibility to remember the RETURN statement. The compilation process won’t remind you that it’s required.
The function processes its statements until the RETURN statement is reached. Once the RETURN statement is processed, the execution of the function will stop. Any statements that follow will be ignored, and control is returned to the calling source. Therefore, it is considered good design to make the RETURN statement the last executable statement. However, the parser does not require this. Your function will compile without any RETURN statement or with a RETURN statement that precedes other valid PL/SQL statements.
/by
, , ,

Invoking Procedures/Functions

Once a procedure has been created and stored in the database, it can be invoked from

  • An executable statement of a PL/SQL block
  • A command entered in the SQL*Plus command-line interface

Executing a Procedure from a PL/SQL Block
To invoke a procedure from another PL/SQL block, use a single statement that names the procedure. For example, suppose you’ve created a procedure called PROC_UPDATE_CRUISE_STATUS. The following PL/SQL block will execute the procedure:
BEGIN
  PROC_UPDATE_CRUISE_STATUS;
END;
Executing a Procedure from the SQL*Plus Command Line
You can execute a PL/SQL procedure from within the SQL*Plus command line without having to write another PL/SQL block to do it. The SQL command EXECUTE, or EXEC for short, must be used.
For example, if you have already stored a procedure called PROC_RUN_BATCH with no parameters, then the following statement, entered in the SQL*Plus window at the SQL prompt, will invoke the procedure:
EXECUTE PROC_RUN_BATCH;

Invoking Functions
Functions are never called in a stand-alone statement as procedures are. Instead, a function call is always part of some other expression. Valid PL/SQL expressions can incorporate functions anywhere that a variable would be accepted. Valid SQL expressions may also invoke functions, but with a few limitations—only certain types of functions can be invoked from SQL.
The following is a sample of a block that might call our sample FUNC_COUNT_GUESTS function:
PROCEDURE PROC_ORDER_FOOD (p_cruise_number NUMBER)
IS
  v_guest_count NUMBER(10);
BEGIN
  — Get the total number of guests
— for the given cruise
v_guest_count := FUNC_COUNT_GUESTS(p_cruise_number);
— Issue a purchase order
  INSERT INTO PURCHASE_ORDERS
    (PURCHASE_ORDER_ID, SUPPLIER_ID, PRODUCT_ID, QUANTITY)
    VALUES
    (SEQ_PURCHASE_ORDER_ID.NEXTVAL, 524, 1, v_guest_count)
  COMMIT;
END;
Functions Called from PL/SQL Expressions
Any PL/SQL function can be called from a PL/SQL expression of another program unit. Remember that expressions can be found in many places within PL/SQL:

  • The right side of an assignment statement
  • The Boolean expression of an IF … THEN … END IF statement
  • The Boolean expression of a WHILE loop
  • The calculation of a variable’s default value

In short, anywhere you might use a PL/SQL variable, you can issue a function call.
Examples:
1. DECLARE
  v_official_statement VARCHAR2(1000);
BEGIN
  v_official_statement := ‘The leading customer is ‘ ||
                          leading_customer;
END;
2. Functions can even be used as parameter values to other functions. For example,
BEGIN
  IF (leading_customer(get_largest_department) = ‘Iglesias’)
  THEN
    DBMS_OUTPUT.PUT_LINE(‘Found the leading customer’);
  END IF;
END;
3. SELECT     COUNT(SHIP_ID) NUMBER_OF_SHIPS,
           leading_customer
FROM       SHIPS;

/by
, , ,

Create, Alter and Drop

Creating Procedures
The following is a code sample that will create a stored procedure named PROC_RESET_ERROR_LOG:
CREATE PROCEDURE PROC_RESET_ERROR_LOG IS
BEGIN
  — Clean out the ERRORS table
  DELETE FROM ERRORS;
  COMMIT;
END;
The syntax to create a function is similar to the syntax used to create a procedure, with one addition: the RETURN declaration. The following is a sample CREATE FUNCTION statement.
CREATE OR REPLACE FUNCTION FUNC_COUNT_GUESTS
  (p_cruise_id NUMBER)
RETURN NUMBER
IS
  v_count NUMBER(10)
BEGIN
  SELECT COUNT(G.GUEST_ID)
  INTO   v_count
FROM     GUESTS G,
         GUEST_BOOKINGS GB
WHERE    G.GUEST_ID = GB.GUEST_BOOKING_ID
  AND    GB.CRUISE_ID = p_cruise_id;
RETURN   v_count;
END;

This function will take a single parameter, p_cruise_id. This parameter could include the parameter type declaration, such as IN, OUT, or IN OUT, but this example leaves it out, so this parameter is assumed to be the default IN parameter type, just as it would be assumed in a procedure. This function will use the p_cruise_id parameter to query the database and count the total number of guests for a single cruise. The result of the query is then returned to the calling block, using the RETURN statement at the end of the function.
If you think of the entire function as a variable, then think of the RETURN datatype as the function’s datatype.

Altering Procedures
Once a procedure has been created, you can use two methods to “alter” the procedure. If you are replacing the original source code with a new set of source code, use the OR REPLACE option discussed in the previous section. This is true for any code modification at all. If, however, you are recompiling the procedure without changing the code, then use the ALTER PROCEDURE command.
The ALTER PROCEDURE command is required when your stored procedure has not been changed in and of itself, but another database object referenced from within your procedure, such as a table, has been changed. This automatically causes your procedure to be flagged as INVALID.
CREATE OR REPLACE PROCEDURE PROC_RESET_ERROR_LOG IS
BEGIN
  — Clean out the ERRORS table
  DELETE FROM ERRORS;
  COMMIT;
END;
ALTER PROCEDURE PROC_RESET_ERROR_LOG COMPILE;
As with a procedure, a function may reference database objects from within its code. As with a procedure, if those database objects are changed, then the function must be recompiled. To perform this recompilation, use the ALTER FUNCTION …COMPILE command.
ALTER FUNCTION FUNC_COUNT_GUESTS COMPILE;
CREATE OR REPLACE FUNCTION FUNC_COUNT_GUESTS
  (p_cruise_id NUMBER)
RETURN NUMBER
IS
  v_count NUMBER(10)
BEGIN
  Statements;
END;
Dropping Procedures
An example of a command that drops a procedure is shown in the following code listing:
DROP PROCEDURE PROC_RESET_ERROR_LOG;
Once this command is successfully executed, the database response “Procedure dropped” will be displayed.
To drop a function, use the DROP … FUNCTION statement. The following is a sample command that will drop our sample function:
DROP FUNCTION FUNC_COUNT_GUESTS;

/by
, , ,

Subprograms

Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.


 

Uses of Procedures/Functions
Procedures are excellent for defining a PL/SQL code block that you know you will need to call more than once, and whose work may produce results largely seen in the database or perhaps some module, like an Oracle Form, or a client-side form, as opposed to work whose result is some single answer; that would probably be more appropriate for a function.

In addition, an anonymous PL/SQL block is parsed each time it is submitted for execution. But if that same anonymous block is assigned a name and created as a procedure, then Oracle will parse the procedure once, at the time it is created. Each subsequent call to that procedure will not require reparsing; it will simply execute, saving time over an anonymous block.
A PL/SQL procedure can be invoked from a single executable statement in another PL/SQL statement. These other PL/SQL statements could be in an anonymous PL/SQL block or in a named program unit, such as another procedure. A PL/SQL procedure can also be invoked from a single command-line executable statement in a SQL*Plus session.

A function‘s main purpose is to return a single value of some sort, as opposed to a procedure, whose main purpose is to perform some particular business process. Like a procedure, a function is a PL/SQL block that’s been assigned a name; but unlike a procedure, the function will always return one—and only one—value of some sort. This returned value is embodied in the function call in such a way that the function becomes, in essence, a variable.
When you create a function, you must consider how you intend to use the function. There are two major categories of functions you can create:

  • Functions that are called from expressions in other PL/SQL program units. Any function can be used this way.
  • Functions that are called from within SQL statements, whether the SQL statement is part of a PL/SQL program unit or not. Some functions you create in PL/SQL can be used in this way.

It’s possible to create functions that can be invoked in both manners. However, if you intend to make a function that can be called from a valid SQL statement, there are some restrictions you have to consider. For example, a function that returns a BOOLEAN datatype, which is perfectly acceptable in PL/SQL, cannot be invoked from a SQL statement, where BOOLEAN datatypes are not recognized.

Functions versus Procedures
Functions can be used in places where procedures cannot be used. Whereas a procedure call is a statement unto itself, a call to a function is not; a function call is part of an expression. This means that functions can be used as a part, or all, of the right side of the assignment statement. Functions can be used as part, or perhaps all, of the Boolean expression in an IF statement. In short, wherever you might use a variable, you can use a function. Functions always return a single value, embodied in the function call itself. In other words, contrary to the optional OUT parameter feature in procedures, which you may or may not use to return multiple values from a procedure, a function must always return one—and only one—value through the very call to the function itself. This value is not returned in the form of an OUT parameter, but instead it is returned in the body of the function itself, so that the function call behaves like a variable. Technically, functions can use IN, OUT, and IN OUT parameters. In practice, functions are generally only given IN parameters.
Where Can You Store Procedures?
Procedures can be stored in the database, alongside tables and other database objects. Once a procedure is stored in the database, it can be invoked from any process with database access. If a process, such as a SQL*Plus window, Java program, Oracle Form, or another PL/SQL procedure, has access to the database, it can execute the procedure, provided that the proper privileges have been granted on the procedure (more on this later) to the schema under which the process is running.

/by
, , ,

Invoking Packaged Constructs

Packages themselves are never directly invoked or executed. Instead, the constructs contained within the package are invoked. For example, procedures and functions within the package are executed. Other constructs in the package, such as constants and other declared constructs, can be referenced from within other PL/SQL programs, in the same manner that those program units could refer to their own locally declared PL/SQL program constructs.
Referencing Packaged Constructs
To call upon any construct of a package, simply use the package name as a prefix to the construct name. For example, to reference a procedure with no parameters called increase_wages that is stored in a package called hr, use this reference:
hr.increase_wages;
All packaged constructs are invoked in the same fashion: by including the package name as a prefix, with a period separating the package name and the construct name. This notation is often referred to as dot notation. It’s the same format used to refer to database objects that are owned by a schema.
For example, if you have a package called assumptions that defines a public constant called tax_rate, you could use it this way in an expression:
v_full_price := v_pre_tax * (1 + assumptions.tax_rate);
Dot notation is required for references to any packaged constructs from outside of the package. However, although references to constructs from within the same package will accept dot notation, they do not require it.
Using Packaged Constructs
For the purpose of reviewing the rules of using packaged constructs, let’s consider all constructs to be in either of two categories: packaged program units, meaning procedures and functions, and global constructs, meaning packaged variables, constants, cursors, exceptions, and types.
Packaged Program Units
The same rules apply to the use of packaged procedures and functions that apply to stand-alone procedures and functions. In other words, packaged procedures must be called from a single statement, whereas packaged functions must be called from within an expression, just like stand-alone PL/SQL stored functions. Parameters may be passed by positional notation or by named notation.
For example, the following PL/SQL block executes a packaged procedure:
BEGIN
  pack_finance.proc_reconcile_invoice(p_status => ‘ACTIVE’);
END;
Packaged Global Constructs
Packaged global constructs generally behave the same as their locally defined counterparts, with one dramatic difference: Their declaration is global to the user sessions.
Consider the following code sample:
PACKAGE rates AS
  bonus NUMBER(3,2);
END rates;
/by