InterBase Quick Start: Part V - Stored Procedures

From InterBase
Jump to: navigation, search

Go Up to InterBase Quick Start: Part V - Advanced Topics

Stored procedures are programs stored with a metadata of a database that run on the server. Applications can call stored procedures to perform tasks, and you can also use stored procedures in IBConsole. See the Programmer’s Guide for more information on calling stored procedures from applications.

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 result. Since select procedures can return more than one row, they appear as a table or view to a calling program.
  • Executable procedures that an application can call directly with the EXECUTE PROCEDURE statement. Executable procedures can perform a variety of tasks; they might or might not 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.

Stored Procedure Syntax

A CREATE PROCEDURE statement is composed of a header and a body. The header contains:

  • The name of the stored procedure, which must be unique among procedure, view, and table names in the database.
  • An optional list of input parameters and their data types that a procedure receives from the calling program.
  • If the procedure returns values to the calling program, the next item is the RETURNS keyword, followed by a list of output parameters and their data types.

The procedure body contains:

    • An optional list of local variables and their data types.
    • A block of statements in InterBase procedure and trigger language, bracketed by BEGIN and END. A block can itself include other blocks, so that there might be many levels of nesting.
The simplified syntax of a procedure looks like this:
CREATE PROCEDURE procedure_name 
 [(input_var1 data type[, input_var2 data type ])] 
 [RETURNS (output_var1 data type[, output_var2 data type ])] 
 AS 
 BEGIN 
 statements in InterBase procedure and trigger language 
 END

Like trigger definitions, procedure definitions in SQL scripts, embedded SQL, and command-line isql must be preceded by a SET TERM statement that sets the terminator to something other than a semicolon. When all procedure statements have been entered, the SET TERM statement must be used again to set the terminator back to a semicolon. See Procs.sql for an example. The Language Reference, Data Definition Guide, and Programmer’s Guide all contain more information on stored procedures.

Advance To:

Image 025.jpg Creating a Simple SELECT Procedures