Procedure Example in Oracle

Procedure:
Also KNown As “Stored Procedures”
· Named block
· Can process several variables
· Returns no values
· Interacts with application program using
IN, OUT, or INOUT parameters are Valid parameters include all data types such as Integer,Char,Varchar2,date etc.
However, for char, varchar2, and number no length and scale, respectively, can be specified.
For example, the parameter number (6) results in a compile error and must be replaced by number

Procedure Basic Syntax

CREATE [OR REPLACE] PROCEDURE name
[( argument [IN|OUT|IN OUT] datatype
[{, argument [IN|OUT|IN OUT] datatype }] )]
AS
/* declaration section */
BEGIN
/* executable section - required*/
EXCEPTION
/* error handling statements */
END;
/

Parameter direction: The optional clauses IN, OUT, and IN OUT specify the way in which the parameter is used. The default mode is IN
» IN (default) : means that the parameter can be referenced inside the procedure body, but it cannot be changed
» OUT: means that a value can be assigned to the parameter in the body, but the parameter’s value cannot be referenced.
The OUT qualifier signifies that the procedure passes a value back to the caller through this argument
» IN OUT: allows both assigning values to the parameter and referencing the parameter.

Executing procedures
» EXECUTE ()
» EXECUTE ;

A procedure can be deleted using the command:

drop procedure


Procedure Example

CREATE OR REPLACE PROCEDURE
myProcedure(bookname in varchar2,quantity integer,price number) AS
BEGIN
INSERT INTO books Values(bookname,quantity,price);
END;
/

To Execute This Procedure Using this Command:
exec myProcedure('Teach Yourself C++',100,125);