Write a procedure - Oracle

Procedures are written in Oracle's PL/SQL programming language.

Let’s write a harmless procedure that does nothing, so we can compile and run it with no fear of damage to your database.

In Notepad, type:

CREATE OR REPLACE PROCEDURE skeleton
IS
BEGIN
  NULL;
END;

Save your file as skeleton.sql.

Let’s go through the procedure line by line. The lines are numbered for your convenience:

1 CREATE OR REPLACE PROCEDURE skeleton
2 IS
3 BEGIN
4   NULL;
5 END;

Line 1:

CREATE OR REPLACE PROCEDURE is an SQL statement that instructs Oracle to create a procedure called skeleton, and to overwrite it if it exists.

Line 2:

The IS keyword signals that a PL/SQL body will follow.

Line 3:

The BEGIN keyword signals the start of a PL/SQL body.

Line 4:

The NULL PL/SQL statement indicates that no action should be performed. We cannot just leave it out, because at least one statement is required in a PL/SQL body.

Line 5:

The END keyword signals the end of the PL/SQL block.

Now let's move on to creating a procedure.