Managing Stored Procedures and Functions

Previous

Next

You can create, modify, run, and drop stored procedures and functions with the SQL Commands page, the Object Browser page, the Script Editor page, or SQL Command Line (SQL*Plus). You can view existing functions and procedures in Object Browser.

The SQL CREATE PROCEDURE statement is used to create stored procedures that are stored in the database. The SQL CREATE FUNCTION statement is used to create stored functions that are stored in an Oracle database.

A procedure or function is similar to a miniature program. It has an optional declarative part, an executable part, and an optional exception-handling part. A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions. A function is a subprogram that computes and returns a value. Functions and procedures are structured alike, except that functions return a value. See "Using the PL/SQL Block Structure".

When passing parameters to functions and procedures, the parameters can be declared as IN or OUT or IN OUT parameters. For a description of these parameter declarations, see "Using Local PL/SQL Procedures and Functions in PL/SQL Blocks".

This section contains the following topics:

• Creating a Procedure or Function With the SQL Commands Page

• Creating a Procedure or Function With the Object Browser Page

• Viewing Procedures or Functions With the Object Browser Page

• Creating Stored Procedures With SQL CREATE PROCEDURE

• Creating a Stored Procedure That Uses Parameters

• Creating a Stored Procedure With the AUTHID Clause

• Creating Stored Functions With the SQL CREATE FUNCTION Statement

• Calling Stored Procedures or Functions

• Editing Procedures or Functions

• Dropping a Procedure or Function