Create Procedure   «Prev  Next»

Lesson 5 Adding a function to a procedure
Objective Add a function to a procedure.

Adding Oracle Function to Procedure

This lesson demonstrates an example of how to add a function to a procedure. The following MouseOver shows how you can use a function within a procedure. Here we create a new procedure called DO_ADDRESS. The function FORMAT_ZIP, which was demonstrated in an earlier lesson, is used within the procedure. Afterwards, we demonstrate the syntax for removing a procedure.

Function in Assignment Statement

Here is the function, and it is used in an assignment statement.
Using Oracle Function inside Procedure
Using a function inside a procedure 
CREATE OR REPLACE PROCEDURE DO_ADDRESS
  (I_CUST_ID IN NUMBER,
    ADDRESS_LINE2 OUT VARCHAR) AS
  BEGIN
  DECLARE
    V_CITY VARCHAR2(20);
    V_STATE VARCHAR2(2);
    V_ZIP VARCHAR(9);
  BEGIN
    SELECT CITY, STATE, ZIP
    INTO V_CITY, V_STATE, V_ZIP
    FROM CUSTOMER
    WHERE CUST_ID = I_CUST_ID;
    ADDRESS_LINE2 := V_CITY || '  ' ||
                     V_STATE ||'  '||
                     FORMAT_ZIP(V_ZIP);
  END;
  END;
  /

Display Area 1: Here is the function and how it is used in an assignment statement. The parameter passed to the function is a local variable from the procedure. The returned value is used to help build the ADDRESS_LINE2 parameter that is returned from the procedure.
Display Area 2: This is the way you remove a procedure. To remove a function, use the same syntax, except replace the word PROCEDURE with the word FUNCTION.


Now that you have seen an example of calling a function from a procedure, you could apply this knowledge to calling just about any kind of sub-program from any other. For example, you could call a procedure from a trigger; you could create a function that calls a procedure or another function; you could call one procedure from another procedure and call a function from the called procedure; and so on. Click the link below to modify a procedure using an evaluative simulation.

Add a function to a procedure in Oracle PL/SQL

  1. First, you call the function within a calculation by typing 
    V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID);
    and pressing Enter.
  2. Next, you add one line to the UPDATE command so that the tax is updated. Type 
    							
TAX_AMOUNT = V_TAX

    and press Enter.

The completed procedure should look like this: 
CREATE OR REPLACE PROCEDURE SHIPPING_FEE
(I_SALES_ID IN NUMBER) AS
BEGIN
DECLARE
V_SHIPPING_FEE NUMBER;
V_TOTAL_ITEM NUMBER;
V_TOTAL_SALE NUMBER;
V_TAX NUMBER;
V_CUST_ID NUMBER;

BEGIN
-- query the table now.

SELECT TOTAL_SALE_AMOUNT,
TOTAL_ITEM_AMOUNT, TAX_AMOUNT, CUST_ID
INTO
V_TOTAL_SALE,V_TOTAL_ITEM, V_TAX, V_CUST_ID
FROM CUSTOMER_SALE
WHERE SALES_ID = I_SALES_ID;
V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID);
V_SHIPPING_FEE := V_TOTAL_ITEM * .10;
V_TOTAL_SALE := 
V_TOTAL_ITEM + V_SHIPPING_FEE + V_TAX;

-- update the table now.
UPDATE CUSTOMER_SALE
SET TOTAL_SALE_AMOUNT = V_TOTAL_SALE,
SHIPPING_HANDLING_FEE = V_SHIPPING_FEE,
TAX_AMOUNT = V_TAX
WHERE SALES_ID = I_SALES_ID;
END;

END;
Here's a breakdown of the PL/SQL block shown above:
Procedure Name: `SHIPPING_FEE`
Purpose: The primary goal of this procedure is to calculate and update shipping fees, taxes, and the total sale amount for a specific order within a system (likely an e-commerce system).
Procedure Breakdown:
  1. Declaration of Variables:
    • `V_SHIPPING_FEE`: Stores calculated shipping fee.
    • `V_TOTAL_ITEM`: Stores total cost of items in the order.
    • `V_TOTAL_SALE`: Stores the final total sale amount (items + shipping + tax).
    • `V_TAX`: Stores calculated tax amount.
    • `V_CUST_ID`: Stores the customer ID associated with the order.
  2. Retrieving Order Data:
    • Query: Fetches existing data from the `CUSTOMER_SALE` table:
      • `TOTAL_SALE_AMOUNT`
      • `TOTAL_ITEM_AMOUNT`
      • `TAX_AMOUNT`
      • `CUST_ID`
    • Filtering: The query uses `WHERE SALES_ID = I_SALES_ID` to select the specific order based on the provided sales ID (input parameter).
    • Storing Data: The fetched results are stored into the corresponding declared variables.
  3. Calculations:
    • Tax: Calculates tax with the `GET_PERCENT` function (assumed to retrieve a tax percentage based on the customer ID). The calculation is `V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID)`.
    • Shipping Fee: Directly calculates the shipping fee as 10% of the total item amount (`V_SHIPPING_FEE := V_TOTAL_ITEM * .10`).
    • Total Sale: Calculates the new total by summing items, shipping, and the calculated tax (`V_TOTAL_SALE := V_TOTAL_ITEM + V_SHIPPING_FEE + V_TAX`).
  4. Updating the Database:
    • Update statement: Updates the `CUSTOMER_SALE` table for the specific order with:
      • The new `TOTAL_SALE_AMOUNT`
      • The calculated `SHIPPING_HANDLING_FEE`
      • The calculated `TAX_AMOUNT`
    • Filtering: The `WHERE` clause ensures only the correct sale record is updated.

In Summary:
This PL/SQL block takes a sales ID as input, fetches relevant order information, recalculates tax and shipping costs, and then updates the corresponding record in the database.
Important Notes:
  • The `GET_PERCENT` function is not a standard PL/SQL function and would need to be custom-defined within the database system where this procedure is used.
  • This code assumes the existence of a `CUSTOMER_SALE` table with the specified columns.


Question: I am working with Oracle database 10g. Is it possible to create a function inside a procedure because I do not want to use package.
Answer: Yes, it is possible. In the declaration section of the procedure, you can declare and define a function. But this function wil be private to this procedure and can not be called from outside. 

SQL> create or replace procedure test
  2  as
  3    l_dt date;
  4    --
  5    function dt
  6    return date
  7    is 
  8    begin
  9      return sysdate;
 10    end;
 11    --
 12  begin
 13    l_dt := dt;
 14    dbms_output.put_line(to_char(l_dt, 'dd-mm-yyyy'));
 15  end; 
 16  /

Procedure created.

SQL> exec test
11-13-2011
PL/SQL procedure successfully completed.

The next lesson concludes this module.
SEMrush Software