Working with Procedures
Go Up to Working with Stored Procedures
Contents
With isql, you can create, alter, and drop procedures and exceptions. Each of these operations is explained in the corresponding sections in this chapter.
There are two ways to create, alter, and drop procedures with isql:
- Interactively
- With an input file containing data definition statements
It is usually preferable to use data definition files, because they are easier to modify and provide separate documentation of the procedure. For simple changes to existing procedures or exceptions, the interactive interface can be convenient.
The user who creates a procedure is the owner of the procedure, and can grant the privilege to execute the procedure to other users, triggers, and stored
procedures.
Working with Procedures Using a Data Definition File
To create or alter a procedure through a data definition file, follow these steps:
- Use a text editor to write the data definition file.
- Save the file.
- Process the file with
isql
. Use this command:isql -input filename database_name
where <filename> is the name of the data definition file and <database_name> is the name of the database to use. Alternatively, from within isql, you can process the file using the command:
SQL> input filename;
If you do not specify the database on the command line or interactively, the data definition file must include a statement to create or open a database.
The data definition file can include:
- Statements to create, alter, or drop procedures. The file can also include statements to create, alter, or drop exceptions. Exceptions must be created before they can be referenced in procedures.
- Any other
isql
statements.
Calling Stored Procedures
Applications can call stored procedures from SQL and DSQL. You can also use stored procedures in isql. For more information on calling stored procedures from applications, see the Embedded SQL Guide.
There are two types of stored procedures:
- SELECT procedures that an application can use in place of a table or view in a
SELECT
statement. A select procedure must be defined to return one or more values (output parameters), or an error results. - Executable procedures that an application can call directly with the
EXECUTE PROCEDURE
statement. An executable procedure can optionally return values to the calling program.
Both kinds of procedures are defined with CREATE PROCEDURE
and have essentially the same syntax. The difference is in how the procedure is written and how it is intended to be used. Select procedures can return more than one row, so that to the calling program they appear as a table or view. Executable procedures are routines invoked by the calling program, which can optionally return values.
In fact, a single procedure conceivably can be used as a select procedure or as an executable procedure, but in general a procedure is written specifically to be used in a SELECT
statement (a select procedure) or to be used in an EXECUTE PROCEDURE
statement (an executable procedure).
Privileges for Stored Procedures
To use a stored procedure, a user must be the creator of the procedure or must be given EXECUTE
privilege for it. An extension to the GRANT
statement assigns the EXECUTE
privilege, and an extension to the REVOKE
statement eliminates the privilege.
Stored procedures themselves sometimes need access to tables or views for which a user does not—or should not—have privileges. For more information about granting privileges to users and procedures, see Planning Security.