Creating an Oracle Procedure

Create a procedure to calculate a percentage and an average for a given sales transaction.
A procedure is created by using the CREATE PROCEDURE command and blocks of PL/SQL statements. Use the SlideShow below to see how a procedure is built. The following series of images walks you through creating a procedure that accepts a primary key field as an incoming parameter and sends two calculated values back to two outgoing parameters.

SQL> create or replace function calc_area
   2    (n_length in number,
   3     n_width  in number)
   4    return number
   5  as
   6  begin
   7    return n_length*n_width;
   8  end;/
 Function created.

In the PL/SQL code shown above, the function calc_area is defined so that it returns a number. In the function body there must be a RETURN statement defining what is returned. In the example above, the RETURN statement is at line 4. A function name can be very descriptive with up to 32 characters and the function is always created in the schema of the user that creates the function. As with the procedure, a function can declare any number of values in the declaration section, limited only by the usability of the function. However, unlike a procedure, a function can not be passed variables in mode OUT or INOUT.
A function can only return a datatype and if a function is defined with an

1. OUT or
2. INOUT

variable, the function will compile but will throw an exception when executed.
ORA-06572: Function <name> has out arguments
An example used earlier in the book converted a temperature in Fahrenheit to Celsius. This is a perfect example of a function, which takes a value and returns a number.

SQL> create or replace function f2c
   2    (n_faren IN number)
   3    return number
   4  as
   5    n_cel number := 0;
   6  begin
   7    n_cel := (5/9)*(n_faren -32);
   8    return n_cel;
   9  end;
  10  /

Function created.

The next lesson examines the capabilities and restrictions of calling other procedures.
Click the link below to create a procedure for the course project.

Steps to create a "Replace Procedure".

1. You began your procedure by typing CREATE OR REPLACE PROCEDURE SHIPPING_FEE and pressing Enter.
2. To define the incoming parameter, you typed (I_SALES_ID IN NUMBER) AS and approved the entry by pressing Enter.
3. You began typing in the declaration section to define the first variable. You typed V_SHIPPING_FEE NUMBER; and approved the entry by pressing Enter.
4. Then you typed BEGIN and pressed Enter to start the main block of the procedure.
5. Next, you typed SELECT TOTAL_SALE_AMOUNT, and pressed Enter. This was the first line of a query.
6. You continued to define another part of the query by typing V_TOTAL_SALE, V_TOTAL_ITEM, V_TAX and pressing Enter.
7. You finished the query by typing SALES_ID = I_SALES_ID; and pressing Enter.
8. Next, you created a calculation for the shipping fee by typing V_SHIPPING_FEE := V_TOTAL_ITEM * .10; and pressing Enter.
9. You finished the second calculation for the total sales by typing V_TOTAL_ITEM + V_SHIPPING_FEE + V_TAX; and pressing Enter.
10. You create the beginning of an update command by typing UPDATE CUSTOMER_SALE and pressing Enter.
11. Then you continue by defining the first column to be updated by typing SET TOTAL_SALE_AMOUNT = V_TOTAL_SALE, and pressing Enter.
12. The rest of the UPDATE command was added for you. Now, you type END; and press Enter to complete the body of the procedure.
13. To execute your block, you typed / at the SQL4 prompt and pressed Enter.
14. SQL*Plus then displayed the result of the compilation of your block.
15. Next, you executed the procedure by typing EXECUTE SHIPPING_FEE(107); and pressing Enter. You see the results of the command in the final screen.

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

-- query the table now.

SELECT TOTAL_SALE_AMOUNT,
TOTAL_ITEM_AMOUNT, TAX_AMOUNT
INTO
V_TOTAL_SALE,V_TOTAL_ITEM, V_TAX
FROM CUSTOMER_SALE
WHERE SALES_ID = I_SALES_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
WHERE SALES_ID = I_SALES_ID;
END;
END;

Question: How do you write your own built-in SQL functions?
Answer: A function is a PL/SQL named block that returns a value. It is commonly used to convert or assign values.
Note the following: 1) A procedure is executed, 2) a function is called, as in the example below:

Begin
   get_area(11,22,n_area);
   n_area := calc_area(11,22);
 End;

In the code fragment above, the area is calculated using a procedure named get_area and a function named calc_area.
The procedure was passed three values and it copied the calculated area into the n_area variable when the procedure exited. The next line uses a function that is 1) passed two values, 2) calculates the area, and 3) returns that value, which is assigned to the n_area variable. Notice that the function is used directly in the assignment operation. A function is defined in the format below.

create or replace function <Name>
   (<variable list>) return <datatype>
 as (or is) 

  local variable declaration
 begin
   code section
 exceptions
 end;