InterBase Quick Start: Part V - Stored Procedures
Stored procedures are programs stored within the metadata of a database. Applications (including IBConsole) can call stored procedures to perform tasks. See Working with Stored Procedures for more information about stored procedures.
There are two types of stored procedures:
- Select procedures: An application can use a select procedure in place of a table or view in a
SELECTstatement. 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: An application can call an executable procedure directly with the
EXECUTE PROCEDUREstatement. Executable procedures can perform a variety of tasks and they can return values to the calling program.
CREATE PROCEDURE syntax applies to both tpes of procedures. The difference is in how you write the procedure and how you intend to use it.
Stored Procedure Syntax
CREATE PROCEDURE statement has 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
RETURNSkeyword, followed by a list of output parameters and their data types.
The body contains:
- An optional list of local variables and their data types.
- A block of statements in InterBase procedure and trigger language, enclosed with
ENDkeywords. You are allowed to nest blocks. The simplified syntax of a procedure is:
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 with trigger definitions, you must precede procedure definitions in SQL scripts, embedded SQL, and command-line
isql by a
SET TERM statement that sets the terminator to something other than a semicolon. At the end of the procedure code, you must use the
SET TERM statement again to set the terminator back to a semicolon.