Procedures : Procedure « Function Procedure Packages « Oracle PL/SQL Tutorial

You can create a procedure that contains a group of SQL and PL/SQL statements.

Procedures allow you to centralize your business logic in the database.

Procedures may be used by any program that accesses the database.

You create a procedure using the CREATE PROCEDURE statement.

The simplified syntax for the CREATE PROCEDURE statement is as follows:

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
END procedure_name;


  1. OR REPLACE specifies the procedure is to replace an existing procedure if present.
  2. You can use this option when you want to modify a procedure.
  3. A procedure may be passed multiple parameters.
  4. IN | OUT | IN OUT specifies the mode of the parameter.
  5. type specifies the type of the parameter.
  6. procedure_body contains the SQL and PL/SQL statements to perform the procedure's task.

You may pick one of the following modes for each parameter:

  1. IN is the default mode for a parameter.
  2. IN parameters already have a value when the procedure is run.
  3. The value of IN parameters may not be changed in the body.
  4. OUT is specified for parameters whose values are only set in the body.
  5. IN OUT parameters may already have a value when the procedure is called, but their value may also be changed in the body.
SQL> create or replace procedure hello_world
  2  as
  3  begin
  4      dbms_output.put_line('Hello World!');
  5  end;
  6  /

Procedure created.

SQL> drop procedure hello_world;

Procedure dropped.


27.5.2.A procedure block.
27.5.3.Wrapping a task into a procedure
27.5.4.Storing PL/SQL procedure in the Database
27.5.5.Creating stored procedures
27.5.6.Execute a procedure
27.5.7.Demonstration of a nested procedure block
27.5.8.Create or replace a procedure
27.5.9.Calling a Procedure
27.5.10.Pass ROWTYPE to a procedure
27.5.11.Insert debug line to the stored procedure with DBMS_OUTPUT.PUT_LINE
27.5.12.Listing Stored Procedure Information
27.5.13.Decrease salary with user procedure
27.5.14.Forward Referencing