Complete Tutorial
Procedures:
1. What is a Procedure?
A Procedure is a
PL/SQL sub program used to perform an action (transaction) which takes
parameters as input and returns output.
2. What does a procedure
consists?
Procedure
consists is PL/SQL block (code) which consists a declarative part, an
executable part, and an optional exception-handling part. A
procedure is compiled and stored in the database as a schema object. Procedures
promote reusability and maintainability. When validated, they can be used in
any number of applications. If the requirements change, only the procedure
needs to be updated.
Oracle Procedures has the following
- Declarative part,
- Executable part,
- Exception-handling part.
The declarative
part contains the following and these items are local and cease to exist when
you exit the subprogram.
·
Declarations of types,
·
Cursors ( Names Control
Structures)
·
Constants,
·
Variables
·
Exceptions
·
Nested subprograms.
The executable
part contains
·
Statements that assign values
·
Control execution
·
Manipulate Oracle data.
Exception-handling
part
The exception-handling part contains exception
handlers, which deal with exceptions raised during execution.
3. What is the Syntax of
the PROCEDURE?
PROCEDURE name [
( parameter [, parameter ... ] ) ]
IS
[declaration statements]
BEGIN
executable-statements
[ EXCEPTION
exception handler statements]
END [ name ];
where each
component is used in the following ways:
name
The name of the
procedure comes directly after the keyword PROCEDURE.
parameters
An optional list
of parameters that you define to both pass information into the procedure and send
information out of the procedure, back to the calling program.
declaration
statements
The declarations
of local identifiers for that procedure. If you do not have any declarations,
then there will not be any statements between the IS and BEGIN statements.
executable
statements
The statements
that the procedure executes when it is called. You must have at least one
executable statement after the BEGIN and before the END or EXCEPTION keywords.
exception
handler statements
The optional
exception handlers for the procedure. If you do not explicitly handle any
exceptions, then you can leave out the EXCEPTION keyword and simply terminate
the execution section with the END keyword.
Below figure
shows the apply_discount procedure, which contains all four sections of the
named PL/SQL block, as well as a parameter list.
The
apply_discount procedure
Calling
a Procedure
A procedure is
called as an executable PL/SQL statement. In other words, a call to a procedure
must end with a semicolon (;) and be executed before and after other SQL or
PL/SQL statements.
The following
executable statement runs the apply_discount procedure:
apply_discount(
new_company_id, 0.15 ); -- 15% discount
If the procedure
does not have any parameters, then you must call the procedure without any
parentheses:
display_store_summary;
Procedure
Header
The portion of the procedure definition
that comes before the IS keyword is called the procedure header. The header
provides all the information a programmer needs to call that procedure, namely:
- The procedure name
- The parameter list, if any
A programmer
does not need to know about the inside of the procedure in order to be able to
call it properly from another program.
The header for
the apply_discount procedure discussed above is:
PROCEDURE
apply_discount
(company_id_in IN company.company_id%TYPE,
discount_in IN NUMBER)
It consists of
the module type, the name, and a list of two parameters.
Procedure
Body
The body of the
procedure is the code required to implement the procedure. It consists of the
declaration, execution, and exception sections of the function. Everything
after the IS keyword in the procedure makes up that procedure's body.
Once again, the
declaration and exception sections are optional. If you have no exception
handlers, you will leave off the EXCEPTION keyword and simply enter the END
statement to terminate the procedure. If you do not
have any declarations, the BEGIN statement simply follows immediately after the
IS keyword (see the do_nothing procedure below for an example of this
structure.). You must supply
at least one executable statement in a procedure. Here is my candidate for the
procedure in PL/SQL with the smallest possible body:
PROCEDURE
do_nothing IS
BEGIN
NULL;
END;
Does the
do_nothing procedure seem silly? A procedure that doesn't do anything can, in
fact, be very useful when you are creating stubs for modules in a top-down
design effort. I have also used this kind of procedure when building templates.
My do_nothing procedure acts initially as a placeholder in my code, but then
also provides a mechanism for customization of the templates.
The
END Label
You can append the name of the procedure directly after the END
keyword when you complete your procedure, as shown below:
PROCEDURE
display_stores (region_in IN VARCHAR2) IS
BEGIN
...
END
display_stores;
This name serves
as a label that explicitly links up the end of the program with its beginning.
You should as a matter of habit use an END label. It is especially important to
do so when you have a procedure that spans more than a single page, or is one
in a series of procedures and functions in a package body.
4. What is meant by Forward Declaration?
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. In Forward declaration an identifier is declared before it is used. Therefore a subprogram is declared before calling it. Although the formal parameter list appears in the forward declaration, it must also appear in the subprogram body. You can place the subprogram body anywhere after the forward declaration, but they must appear in the same program unit. For example, the following declaration of procedure calc_rating is illegal becauseaward_bonuscalls procedurecalc_rating.
DECLARE
PROCEDURE calc_rating (...); -- forward declaration
...
/* Define subprograms in alphabetical order. */
PROCEDURE award_bonus (...) IS
BEGIN
calc_rating( ... );
...
END;
PROCEDURE calc_rating (...) IS
BEGIN
...
END;
5. What are the Parameter Modes in Procedure?
·
IN (the default),
·
OUT Parameter
IN Mode
An
IN parameter
lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter
acts like a constant. Therefore, it cannot be assigned a value.
PROCEDURE
validate_input(l_input_rec IN INPUT_REC_TYPE,
v_return_status OUT VARCHAR2 ,
v_return_message OUT
VARCHAR2
)
IS
v_source_system VARCHAR2(150);
v_source_system_identifier VARCHAR2(150);
v_notes VARCHAR2(2000);
v_status VARCHAR2(30) ;
v_service_request_number VARCHAR2(64) ;
v_input_type VARCHAR2(30) ;
v_attribute_name VARCHAR2(30) ;
OUT Mode
An
OUT
parameter lets you return values to the caller of a subprogram. Inside the
subprogram, an OUT parameter acts like a variable. That means you can use an OUT formal
parameter as if it were a local variable. You can change its value or reference
the value in any way, as the following example shows:
v_return_status OUT VARCHAR2 ,
v_return_message OUT
VARCHAR2
)
IS
v_source_system VARCHAR2(150);
v_source_system_identifier VARCHAR2(150);
v_notes
VARCHAR2(2000);
v_status
VARCHAR2(30) ;
v_service_request_number VARCHAR2(64) ;
IN OUT Mode
OUT
|
IN OUT
|
|
Default
|
Must be Specified
|
Must be Specified
|
6.How to ALTER a PROCEDURE
Using the ALTER PROCEDURE statement we can explicitly recompile a standalone stored procedure. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead. The pre-requisite is the Procedure must be in your own schema or one must have the ALTER ANY PROCEDURE system Privilege.
Using the ALTER PROCEDURE statement we can explicitly recompile a standalone stored procedure. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead. The pre-requisite is the Procedure must be in your own schema or one must have the ALTER ANY PROCEDURE system Privilege.
