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;