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
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
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 a 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.
