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

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Procedure

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:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  procedure_body
END procedure_name;

where

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>
SQL> create or replace procedure hello_world
  2  as
  3  begin
  4      dbms_output.put_line('Hello World!');
  5  end;
  6  /

Procedure created.

SQL>
SQL> drop procedure hello_world;

Procedure dropped.

SQL>
SQL>