| 27. 5. 1. Procedures |
|
|
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 |
- OR REPLACE specifies the procedure is to replace an existing procedure if present.
- You can use this option when you want to modify a procedure.
- A procedure may be passed multiple parameters.
- IN | OUT | IN OUT specifies the mode of the parameter.
- type specifies the type of the parameter.
- 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: |
- IN is the default mode for a parameter.
- IN parameters already have a value when the procedure is run.
- The value of IN parameters may not be changed in the body.
- OUT is specified for parameters whose values are only set in the body.
- 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>
|
|