Working with Procedures

Go Up to Working with Stored Procedures

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:

1. Use a text editor to write the data definition file.
2. Save the file.
3. 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.

Advance To:

• Creating Procedures