What is a stored procedure ?
A stored procedure is a PL/SQL block which performs a specific task or a set of tasks.
A procedure has a name, contains SQL queries and is able to receive parameters and return results.
A procedure is similar to functions(or methods) in programming languages.

From Oracle docs,

A procedure is a group of PL/SQL statements that you can call by name.

When should I use a stored procedure ?
When you want to perform a particular task many times, then in place of repeating the queries every time, embed it in a procedure and just call it where ever required.
For Example,
given the date of birth of an Employee you want to calculate his age and this is required at the time of Employee registration, when applying for insurance of Employee or when transferring him to other departments etc.

Now either you write the logic of age calculation at all the places separately or create a stored procedure with the logic and call it where required.

Benefits of stored procedure

  1. Reusability : Create a procedure once and use it any number of times at any number of places. You just need to call it and your task is done.
  2. Easy maintenance : If instead of using a procedure, you repeat the SQL everywhere and if there is a change in logic, then you need to update it at all the places.
    With stored procedure, the change needs to be done at only one place.

Stored Procedure Syntax

CREATE OR REPLACE PROCEDURE [PROCEDURE_NAME] ( PARAMETERS) AS
  [LOCAL VARIABLES DECLARATION]
  BEGIN
    [SQL STATEMENTS]
  END;

Description

  1. CREATE PROCEDURE 
    Keywords to create procedure. OR REPLACE keywords are optional and may be removed.
  2. [PROCEDURE_NAME] Name of procedure. A procedure can be called using this name only.
  3. PARAMETERS
    Values that a stored procedure receives or returns are declared as parameters while declaring a procedure.
    Parameters are just like arguments in functions in a programming language.
    There are following types of parameters:
    a. IN: Parameters suffixed by IN are the input parameters which the stored procedure receives.
    These parameters are read only and can not be altered by the procedure.
    Example, employeeid IN number.
    b. OUT: Values which can be modified by the procedure are represented as OUT parameters.
    A procedure can update these values and the update will also be visible at the places from where the procedure is called.
    A procedure can have 0 or more parameters.
  4. AS
    Marks the start of procedure
  5. [LOCAL VARIABLES DECLARATION] The values which are selected from a database table inside a procedure needs to be stored inside some variables.
    These variables are declared inside this section.
    This section does not have any start or end markers, it lies in between AS and BEGIN keywords.
  6. BEGIN
    Marks the beginning of actual logic statements of a procedure.
  7. [SQL STATEMENTS] These statements constitute the logic of a stored procedure. These are SQL queries which are normally used outside a procedure also.
  8. END
    Marks the end of stored procedure.
A procedure can be removed using DROP PROCEDURE [PROCEDURE NAME] query.

Stored Procedure Usage Example
A vehicle is rented on a daily charge basis. Suppose total rent needs to be calculated based on its rented and return dates using a procedure.
The database table with required fields is as given below.

CREATE TABLE VEHICLERENT (
   VEHICLE_ID NUMBER(8,0) PRIMARY KEY,
   MANUFACTURER VARCHAR2(75) NOT NULL,
   MODELNAME VARCHAR2(20),
   TYPE VARCHAR2(10),
   RENT_DATE DATE,
   RETURN_DATE DATE
);

Now, the procedure should receive 1 input parameter which is the id of the vehicle for which the rent needs to be calculated and have one output parameter which will contain the total rent amount.

The procedure will fetch the record with provided id, extract the rented and return dates from it, calculate the number of rented days and hence the total rent amount.

Let us insert a couple of records into the table using the following insert queries

INSERT INTO VEHICLERENT VALUES(1, 'Honda', 'City', 'Car',TO_DATE('2018-02-20','YYYY-MM-DD'), TO_DATE('2018-02-23','YYYY-MM-DD'));
INSERT INTO VEHICLERENT VALUES(2, 'Hyundai', 'i20', 'Car',TO_DATE('2018-03-10','YYYY-MM-DD'), TO_DATE('2018-03-13','YYYY-MM-DD'));

The stored procedure would be as given below:

CREATE PROCEDURE calculateRent(vehicleId IN NUMBER, rentAmount OUT NUMBER) AS
  -- variable to hold the number of days
  rentDuration NUMBER;
  BEGIN
    -- fetch dates, calculate the difference in days and assign it to a variable
    SELECT (return_date - rent_date) into rentDuration FROM vehiclerent
    WHERE vehicle_id = vehicleId;
    -- calculate the rent amount assuming the daily rate to be 50 and assign it to output parameter
    rentAmount := rentDuration * 50;
  END;

The above procedure is named as calculateRent. It has :

an input parameter which has name vehicleId and data type NUMBER.
an output parameter which is named rentAmount and NUMBER data type.

The procedure declares a variable named rentDuration to hold the number of days between rented date and return date of vehicle, multiplies it with the daily rate and assigns the result to the out parameter rentAmount.

There are a few things to note about this procedure which will be applicable to all the procedures:

  1. When assigning a value from SELECT query to a variable, INTO keyword is used.
    If there are multiple values which are selected, then each of them are assigned using a single INTO keyword.
  2. When assigning a value to an OUT parameter, := operator is used.

How do I execute stored procedure?
The above procedure can be called using the below syntax

-- declare is required only when there are some variables to be passed to the procedure
DECLARE
  -- variable to be passed to the procedure 
  rent NUMBER
-- marks the start of procedure call 
BEGIN
  -- call procedure with rent as OUT parameter
  calculateRent(1, rent)
  --print the value of rent parameter
  DBMS_OUTPUT.PUT_LINE('Rent = ' || rent)
END;

If the procedure does not require any variables then directly call it using syntax
EXECUTE [PROCEDURE NAME]

If in above example, if only id was to be passed to the procedure then it could be called as EXECUTE calculateRent(1)

Let’s tweak in

  1. In order to create a procedure in a schema, the schema user should have the permission(or privilege) to create Stored procedure.
    This privilege can be given by the administrator using GRANT CREATE PROCEDURE TO [USERNAME] query.
  2. OR REPLACE keywords are used to recompile the procedure when there are some changes and the procedure already exists.
    In the absence of these keywords, if the create procedure is executed then ORA-00955: name is already used by an existing object is raised.
  3. A procedure cannot return a value.
    It can update the values in OUT parameters and the updated values are reflected in the calling block.
    In this way, a procedure can update multiple values.
  4. IN parameters cannot be written by the procedure.
Hope the article was useful.

Leave a Reply