Oracle Procedures by examples

A procedure is a module that performs one or more actions; it does not need to return any values.

The syntax for creating a procedure is as follows:

CREATE OR REPLACE PROCEDURE name
[(parameter[, parameter, ...])]
AS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

A procedure may have zero to many parameters.

Parameters are the means to pass values to and from the calling environment to the server. There are three types of modes for procedures:-

IN: data passed to the procedure.
OUT: data returned by the procedure.
INOUT: data passed to the procedure that is, during procedure execution, replaced by data to be returned from the procedure.

IN Mode Example

I simply created a procedure using IN mode that insert data into the table by using TOAD for Oracle.


Use F5 key to compile procedure. Now passing data from calling envirnment to the procedure.


Data insert successfully into the table by using procedure.

OUT Mode Example

This procedure is simply selecting data from table and pass the selected data to the calling environment.


Use F5 key to compile procedure. Now passing data from the procedure to the calling envirnment.


INOUT Mode Example




Calling procedure by using Oracle 10g Form Developer

I create a simple form for data insertion and i am calling my stored procedure from the form. For this purpose i create a Save Button on the form and put the procedure calling code on button's WHEN_BUTTON_PRESSED trigger.


Comments

Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle