alt
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow Oracle 10g arrow Oracle 10g Tutorials - Identifying PL/SQL Objects
Site Search


Oracle 10g Tutorials - Identifying PL/SQL Objects
Article Index
Oracle 10g Tutorials - Identifying PL/SQL Objects
Page 2

.

.

Procedures:

A Procedure is a named PL/SQL block with declarative section, an executable section and an exception handling section. Procedures are stored in the data dictionary and are executed whenever they are invoked from another PL/SQL block. Procedures can be called from any PL/SQL block and arguments can be passed while calling. A procedure call is a PL/SQL statement and cannot be a part of an expression.

The syntax for creating a procedure is as below:

CREATE [OR REPLACE] procedure procedure_name
[(argument_1[{IN/OUT/IN OUT}] data_type,




argument_n[{IN/OUT/IN OUT}] data_type)] {IS/AS}
/*declarative section*/
BEGIN
/*executable section*/
EXCEPTION
/*exception section*/
END [procedure_name];

Below is an example of creating a procedure:

Figure -2 Procedure Creation

Functions:

A Function, just like a procedure, is a named PL/SQL block that has an executable section and an exception handling section. Functions also, are stored in the data dictionary and can be called from other PL/SQL blocks when required. Functions can also have parameters and the arguments can be passed while calling them.

There are some differences between procedures and functions though. Functions can only be called as a part of an expression (as opposed to procedure calls which is a PL/SQL statement by itself). Function body contains a ‘return’ statement that returns the result of function execution to the calling expression. So, the function has a return type that has to be declared in the function header.

The syntax for creating a function is as below:

CREATE [OR REPLACE] FUNCTION function_name
[( argument_1 [{IN/OUT/IN OUT}] data_type,




argument_n [{IN/OUT/IN OUT}] data_type,)]
RETURN return_type {IS/AS}
/*declarative section*/
BEGIN
/*executable section*/
RETURN expression;
EXCEPTION
/*exception section*/
END [function_name];

Below is an example of creating a function:

Figure -3 Function Creation


Trackback(0)
Comments (0)add comment

Write comment

busy

 
< Prev   Next >
Sponsored Links
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape