PL/SQL Procedures and Functions.

Creating a procedure

The basic syntax for create statement to create a procedure is:

create [or replace] procedure procedure_name (
 argument declarations
) {is | as}
  /* declarative section */
begin
  /* executable section */
exception
  /* exception section */
end procedure_name;

Creating a function

The basic syntax for create statement to create a function is:

create [or replace] function function_name (
 argument declarations
) return return_type {is | as}
  /* declarative section */
begin
  /* executable section */
exception
  /* exception section */
end function_name;

Subprogram parameters

To declare a parameter for a procedure/function the following syntax is used:

parameter_name [in | out | in out] [nocopy] datatype [:= | default] value

Mode	Description
`in`	The value of the actual parameter is passed into the procedure when the procedure is invoked. Inside the procedure, the formal parameter acts like a PL/SQL constant - it is considered read-only and cannot be changed. When the procedure finishes and control returns to the calling environment, the actual parameter is not changed. By default PL/SQL passes this type of parameters by reference.
`out`	Any value the actual parameter has when the procedure is called is ignored. Inside the procedure, the formal parameter acts like an uninitialized PL/SQL variable, and thus has a value of `NULL`. It can be read from and written to. When a procedure finishes and control returns to the calling environment, the contents of the formal parameter are assigned to the actual parameters. Parameters of this type are passes by value by default. This can be altered by using `nocopy` modifier (Oracle 8i and higher).
`in out`	This mode is a combination of `in` and `out`. The value of the actual parameter is passed into the procedure when the procedure is invoked. Inside the procedure, the formal parameters acts like an initialized variable, and can be read from and written to. When the procedure finishes and control returns to the calling environment, the contents of the formal parameter are assigned to the actual argument. By default this type of parameters are passed by value. To pass it by reference use `nocopy` modifier.

Take a look at this example to see the difference in speed when using nocopy for big arguments. You may also need this file to create tables needed in the previous example.

When declaring a parameter it is illegal to constrain char and varchar2 parameters with a length, and number parameters with a precision and/or scale. However they can be constrained by using %type. If a formal parameter is declared using %type, and the underlying type is constrained, the constraint will be on the formal parameter rather than the actual parameter; that is, fir example, if a precision of actual argument is larger than a precision of a table field used in %type, then Oracle will generate an error message.