background image
<< Developing and Using Stored Procedures | Creating a Procedure with GUI Menu >>
<< Developing and Using Stored Procedures | Creating a Procedure with GUI Menu >>

Creating Standalone Procedures and Functions

Creating and Using Standalone Procedures and Functions
4-2 Oracle Database 2 Day Developer's Guide
This next section of this chapter is
"Creating and Using Standalone Procedures and
Functions"
on page 4-2, shows you how to create and use standalone procedures and
functions. You may wish to skip it and move directly to
"Creating and Using Packages"
on page 4-9.
Creating and Using Standalone Procedures and Functions
With Oracle Database, you can store programs in the database, so commonly used
code can be written and tested once and then accessed by any application that requires
it. Program units that reside in the database also ensure that when the code is invoked
the data is processed consistently, which leads to ease and consistency of the
application development process.
Schema-level, or standalone subprograms such as functions (which return a value) and
procedures (which do not return a value) are compiled and stored in an Oracle
Database. Once compiled, they become stored procedure or stored function schema
objects, and can be referenced or called by any applications connected to Oracle
Database. At invocation, both stored procedures and functions can accept parameters.
Procedures and functions follow the basic PL/SQL block structure, which consists of
the following elements:
A declarative part, sometimes starting with the keyword
DECLARE
, identifies
variables and constants used in the application logic. This part is optional.
An executable part, starting with
BEGIN
and ending with
END
, contains the
application logic. This part is mandatory.
An exception-handling part, starting with
EXCEPTION
, handles error conditions
that may be raised in the executable part of the block. This part is optional.
The general form of a PL/SQL block follows. Note also that each stored program unit
has a header that names the unit and identifies it as either a function, procedure, or a
package.
Header
AS
[declaration statements
...]
BEGIN
...
[EXCEPTION
...]
END;
Creating Procedures and Functions
The SQL statements for creating procedures and functions are
CREATE PROCEDURE
and
CREATE FUNCTION
, respectively. In practice, it is best to use a
CREATE OR
REPLACE
statement. The general form of these statements follows.
CREATE OR REPLACE
procedure_name
(
arg1
data_type,
...) AS
BEGIN
....
END
procedure_name
;
See Also:
Oracle Database PL/SQL Language Reference for information on the
syntax for declaring procedures