Keywords
- mode
Specifies whether the parameter can be read from or written to, with the following acceptable values:
- IN
Read-only variable. The value of the actual parameter can be referenced inside the program, but the parameter cannot be changed. This is the default.
- OUT
Read/write variable.
- IN OUT
Read/write variable.
If an exception is raised during execution of a procedure or function, assignments made to OUT or IN OUT parameters get rolled back, unless NOCOPY is specified.
- NOCOPY
Compiler hint for parameters makes the parameter a call-by-reference instead of a call-by-value parameter. Normally, PL/SQL passes IN OUT parameters by value—a copy of the parameter is created for the subprogram. When parameter items get large (as collections and objects do), the copy can eat memory and slow down processing. NOCOPY directs PL/SQL to pass the parameter by reference, using a pointer to the single copy of the parameter. The disadvantage of NOCOPY is that when an exception is raised during execution of a program that has modified an OUT or IN OUT parameter, the changes to the actual parameters are not rolled back because the parameters were passed by reference instead of being copied.
- datatype
Can be any PL/SQL or programmer-defined datatype but cannot be constrained by a size. The actual size of the parameter is determined from the calling program or via a %TYPE constraint, which is explained later in this chapter.
CREATE OR REPLACE PROCEDURE empid_to_name (in_id emp.emp_id%TYPE -- Compiles OK. ...