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; /

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

First, you call the function within a calculation by typing
```
V_TAX := V_TOTAL_ITEM * GET_PERCENT(V_CUST_ID);
```
and pressing Enter.
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:

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.
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.
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`).
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.