Using Procedures in Oracle

This article is written about how to create and drop methods in Oracle/PLSQL with syntax and examples.

Create Procedure

Just as you can in different languages, you can create your very own processes in Oracle.

Syntax

The syntax to create a procedure in Oracle is:

CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]

IS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [procedure_name];

When you create a technique or function, you can also define parameters. There are three kinds of parameters that can be declared:

IN – The parameter can be referenced by means of the manner or function. The cost of the parameter can no longer be overwritten by way of the method or function. OUT – The parameter can not be referenced by means of the manner or function, however the fee of the parameter can be overwritten with the aid of the process or function. IN OUT – The parameter can be referenced by means of the technique or characteristic and the price of the parameter can be overwritten via the method or function.

Example

Let’s look at an example of how to create a system in Oracle.

The following is a simple example of a procedure:

CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )

IS
   cnumber number;

   cursor c1 is
   SELECT course_number
    FROM courses_tbl
    WHERE course_name = name_in;

BEGIN

   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;

   INSERT INTO student_courses
   ( course_name,
     course_number )
   VALUES
   ( name_in,
     cnumber );

   commit;

   close c1;

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

This manner is known as UpdateCourse. It has one parameter referred to as name_in. The system will look up the course_number primarily based on route name. If it does not locate a match, it defaults the path wide variety to 99999. It then inserts a new report into the student_courses table.

Drop Procedure

Once you have created your procedure in Oracle, you may discover that you need to remove it from the database.

Syntax

The syntax to a drop a procedure in Oracle is:

DROP PROCEDURE procedure_name;

procedure_name The title of the process that you desire to drop.

Example

Let’s look at an example of how to drop a method in Oracle.

For example:

DROP PROCEDURE UpdateCourse;

This instance would drop the manner referred to as UpdateCourse.