Oracle Procedure

In Oracle, FUNCTION is simply a group of PL/SQL statements. The Oracle procedure are similar to any other language routine. It accept three types of parameters IN, OUT and IN OUT. you can create your own procedures in Oracle.

A function is a subprogram that is used to return a single value. You must declare and define a function before invoking it. It can be declared and defined at a same time or can be declared first and defined later in the same block. Just as you can in other languages, you can create your own functions in Oracle.

Oracle Create Procedure Syntax

To create a new Procedure, use the following syntax:

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

Parameters:

In Oracle Procedure, there are three types of parameters that can be declared:

IN: This is a default parameter. This parameter can be referenced, but value can not be overwritten by the procedure or function.

OUT: This must be specified. This parameter can not be referenced, but the value of the parameter can be overwritten by the procedure or function.

IN OUT: This must be specified. This parameter can be referenced and the value of the parameter can be overwritten by the procedure or function.

Oracle Create Procedure Examples

The following Oracle statement to create new Procedure called "userInsert" which accept one parameter "name_in":

Example

CREATE OR REPLACE Procedure userInsert ( name_in IN varchar2 )
IS
   str_user varchar2;
   
   cursor c1 is
   SELECT firstName
    FROM tblCustomerLoan
    WHERE userName = name_in;
	
BEGIN
	open c1;
    fetch c1 into str_user;
   
   INSERT INTO tblUser
   ( userName,
     firstName )
   VALUES
   ( name_in,
     str_user );

   commit;

   close c1;
   
EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Note:

In the above example, we have created a "userInsert" procedure, accept one parameter called "name_in". It has one parameter called name_in. The procedure will lookup the course_number based on course name. It then inserts a new record into the "tblUser" table.