Procedure in Database Management System(DBMS).

PROCEDURE:

Procedure are defined as sub program which can be  store by finding their name for future use. These also known as Name Block.

Syntax:

CREATE[REPLACE]  PROCEDURE procedure_name 

(varname IN | OUT | INOUT datatype,......)

IS | AS

DECLARE

--------BLOCK-----

BEGIN

--------BLOCK-----

EXCEPTION

--------BLOCK-----

END;

In the above syntax CREATE PROCEDURE is keyword followed by procedure_name . The procedure consist of different parameter which will have three mode and datatype.

The mode of parameter can be as follows:

1. In :In mode of parameter specify that only  read variables which means they are are used to                         define constraints. 

2. Out:Such variables can over written and can be used to return control of the procedure execution the     block which is called Procedure.

3.  Inout: Such varibles are read and write both, if so mode is specify than by default mode will be             inout.

Example:

Write a block to read an employee number from user, pass this employee number to Procedure where procedure calculate order amounts of that employee and print the result.

Syntax:

CREATE PROCEDURE total_amount 

(e_id  salesrep.empid %TYPE)

IS

orderTotalAmount orders.amount %TYPE;

BEGIN

    SELECT SUM(amount) INTO orderTotalAmount 

    FROM salesrep, orders

    WHERE rep=emp_id, emp_id=e_id;

    DBMS_OUTPUT.PUT_LINE('Total amount'||orderTotalAmount );

DECLARE

    e_id salesrep.emp_id%TYPE=&e_id;

    BEGIN

        total_amount (e_id);

END;

The above Procedure created can also be executed directly as:

    exec total_amount (101);