Creating Oracle procedure

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.

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, — SQL> CREATE OR REPLACE PROCEDURE CALC_TOTAL (I_SALES_ID IN NUMBER, PERCENT_OF_RETAIL OUT NUMBER, AVG_ITEM_SALE OUT NUMBER) AS |

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 BEGIN DECLARE TOTAL_ITEM_SALE NUMBER; TOTAL_RETAIL_PRICE NUMBER;
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

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

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

5) The final portion of the procedure is the exception handler. — 4) 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 — 5) 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

Ad PL/SQL Programming