, , ,

Parameters in PL SQL

A parameter is a variable whose value can be defined at execution time and can be exchanged between the procedure and the calling PL/SQL block. Parameter values can be passed in to the procedure from the calling PL/SQL block and can optionally have their values passed back out of the procedure to the calling PL/SQL block upon the completion of the procedure’s execution
Parameters are declared at the top of the procedure within a set of parentheses. Each parameter declaration includes the following:

  • A name, defined by the developer, and adhering to the rules of object names (discussed earlier).
  • The type of parameter, which will either be IN, OUT, or IN OUT. The default is IN.
  • The datatype. Note that no specification or precision is allowed in parameter datatype declarations. To declare something as an alphanumeric string, you can use VARCHAR2, but you cannot use, for example, VARCHAR2(30).
  • Optionally, a parameter may be provided with a default value. This can be done by using the reserved word DEFAULT, followed by a value or expression that is consistent with the declared datatype for the parameter. The DEFAULT value identifies the value the parameter will have if the calling PL/SQL block doesn’t assign a value.

After each parameter declaration, you may place a comma and follow it with another parameter declaration.
The following is an example of a procedure header that uses parameters:
PROCEDURE PROC_SCHEDULE_CRUISE
  ( p_start_date IN DATE DEFAULT SYSDATE
   , p_total_days IN NUMBER
   , p_ship_id IN NUMBER
   , p_cruise_name IN VARCHAR2 DEFAULT ‘Island Getaway’)
IS
… code follows …This procedure declares four parameters. Each parameter is an IN parameter. Each parameter is assigned a datatype. The parameter p_cruise_name is given a datatype of VARCHAR2; the length cannot be specified in a parameter datatype declaration.
Two of the parameters are assigned default values. The first, p_start_date, uses the Oracle pseudocolumn SYSDATE, and the second, p_cruise_name, is assigned the string, ‘Island Getaway’.
Functions parameters
Functions take parameters, just like procedures do, and just like procedures, a parameter for a function can be an IN, OUT, or an IN OUT parameter. The default parameter type is an IN parameter.
However, unlike a procedure, a function always returns a value through its unique RETURN statement, and this value replaces the original call to the function in the expression that calls the function. Given this, functions are not generally used to pass OUT or IN OUT parameters. Furthermore, the OUT and IN OUT parameter will not work with function calls that are made from SQL statements. For example, consider the following function:
FUNCTION FUNC_COMPUTE_TAX
(p_order_amount IN OUT NUMBER)
RETURN NUMBER
IS
BEGIN
  p_order_amount := p_order_amount * 1.05;
  RETURN p_order_amount * .05;
END;
This function has an IN OUT parameter. The parameter comes IN as some dollar amount representing an order; it goes OUT with tax added. The function RETURNS the amount of the tax itself, as a NUMBER datatype

/by
, , ,

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