Lesson 3	Creating a procedure
Objective	Create procedure to calculate sales transaction.

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.

1) Here is the beginning few lines of the procedure. The procedure has the name CALC_TOTAL.
There are three parameters: I_SALES_ID, which is an incoming parameter, — 1) Here is the beginning few lines of the procedure. The procedure has the name CALC_TOTAL. There are three parameters: I_SALES_ID, which is an incoming parameter, PERCENT_OF_RETAIL and AVG_ITEM_SALE, which are both outgoing parameters that are loaded in the procedure.

SQL> CREATE OR REPLACE PROCEDURE CALC_TOTAL (I_SALES_ID IN NUMBER, PERCENT_OF_RETAIL OUT NUMBER, AVG_ITEM_SALE OUT NUMBER) AS |

2) Lines 5 through 8 are the declaration section. This is an optional section where local variables are defined. In the example, two variables are defined which are named TOTAL_ITEM_SALE and TOTAL_RETAIL_PRICE

SQL> CREATE OR REPLACE PROCEDURE CALC_TOTAL (I_SALES_ID IN NUMBER, PERCENT_OF_RETAIL OUT NUMBER, AVG_ITEM_SALE OUT NUMBER) AS BEGIN DECLARE TOTAL_ITEM_SALE NUMBER; TOTAL_RETAIL_PRICE NUMBER;

3) Line 9 marks the beginning of the body of the procedure. The body is a required section and contains all the word done by the procedure. As you can see in this example, the first thing the procedure does is query the database for the sum of the items sold, the sum of the retail prices of each, and the average price of an item.

BEGIN DECLARE TOTAL_ITEM_SALE NUMBER; TOTAL_RETAIL_PRICE NUMBER; BEGIN SELECT SUM(SI.SALE_AMOUNT), SUM(P.SALE_PRICE), ROUND(AVG(SI.SALE_AMOUNT),2) INTO TOTAL_ITEM_SALE, TOTAL_RETAIL_PRICE, AVG_ITEM_SALE FROM SALE_ITEM SI, PRODUCT P WHERE SALES_ID - I_SALES_ID AND P.PRODUCT_ID - SI.PRODUCT_ID;

4) The second outgoing parameter is calculated in the next part of the body

5) The final portion of the procedure is the exception handler. In this example, the outgoing parameters are set to zero if there is no match on the incoming SALES_ID parameter. Asyou can see, the procedure has been created.

6) To test the procedure, a short PL/SQL block is written that calls the procedure and displays the resulting parameter values on the screen. Here you see that for SALES_ID =107, the sale price is 100% of the retail price and the average price of an item sold is $29.88

Oracle Procedure using Return

The following procedure definition uses the return definition.
A function returns a datatype, not a variable.

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.

calc_area returns number

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

OUT or
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.
Create Replace Procedure

Built-in SQL functions

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

passed two values,
calculates the area, and
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;