CREATE PROCEDURE

Go Up to Statement and Function Reference (Language Reference Guide)

Creates a stored procedure, its input and output parameters, and its actions. Available in DSQL, and isql.

CREATE PROCEDURE'' name
'' [(<param>'' ''data_type [, <param>'' ''data_type'' ''…])] 
[RETURNS param data_type'' [, ''<param>'' ''data_type …])]
AS ''procedure_body '';

procedure_body =

 [variable_declaration_list]
block
variable_declaration_list =

 DECLARE VARIABLE var data_type;
[DECLARE VARIABLE var data_type; …]
block =
BEGIN
compound_statement
[compound_statement …]
END
compound_statement = block | statement;
data_type = { SMALLINT | INTEGER | FLOAT | DOUBLE PRECISION}
| {DECIMAL | NUMERIC} [(precision [, scale])]
| {DATE | TIME | TIMESTAMP)
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR}
[(int)] [CHARACTER SET charname]
| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(int)]
| BOOLEAN

Argument	Description
<name>	Name of the procedure. Must be unique among procedure, table, and view names in the database.
<param data_type>	Input parameters that the calling program uses to pass values to the procedure: <param>: Name of the input parameter, unique for variables in the procedure. <data_type>: An InterBase data type.
`RETURNS` <param data_type>	Output parameters that the procedure uses to return values to the calling program: <param>: Name of the output parameter, unique for variables within the procedure. <data_type>: An InterBase data type. The procedure returns the values of output parameters when it reaches a `SUSPEND` statement in the procedure body.
`AS`	Keyword that separates the procedure header and the procedure body.
`DECLARE VARIABLE`	Declares local variables used only in the procedure; must be preceded by `DECLARE VARIABLE` and followed by a semicolon (;). `is the name of the local variable, unique for variables in the procedure.`
<statement>	Any single statement in InterBase procedure and trigger language; must be followed by a semicolon (;) except for `BEGIN` and `END` statements.

Description: CREATE PROCEDURE defines a new stored procedure to a database. A stored procedure is a self-contained program written in InterBase procedure and trigger language, and stored as part of a metadata of a database. Stored procedures can receive input parameters from and return values to applications.

InterBase procedure and trigger language includes all SQL data manipulation statements and some powerful extensions, including IF … THEN … ELSE, WHILE … DO, FOR SELECT … DO, exceptions, and error handling.

There are two types of 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, or an error will result.
Executable procedures that an application can call directly, with the EXECUTE PROCEDURE statement. An executable procedure need not return values to the calling program.

A stored procedure is composed of a header and a body.

The procedure header contains:

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

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 may be many levels of nesting.

InterBase does not allow database changes that affect the behavior of an existing stored procedure (for example, DROP TABLE or DROP EXCEPTION). To see all procedures defined for the current database or the text and parameters of a named procedure, use the isql internal commands SHOW PROCEDURES or SHOW PROCEDURE procedure.

InterBase procedure and trigger language is a complete programming language for stored procedures and triggers. It includes:

SQL data manipulation statements: INSERT, UPDATE, DELETE, and singleton SELECT.
SQL operators and expressions, including generators and UDFs that are linked with the database.
Extensions to SQL, including assignment statements, control-flow statements, context variables (for triggers), event-posting statements, exceptions, and error-handling statements.

The following table summarizes language extensions for stored procedures. For a complete description of each statement, see Procedures and Triggers.

Language extensions for stored procedures
Statement	Description
`BEGIN` … `END`	Defines a block of statements that executes as one. The `BEGIN` keyword starts the block; the `END` keyword terminates it. Neither should end with a semicolon.
variable = expression	Assignment statement: assigns the value of expression to variable, a local variable, input parameter, or output parameter.
/* comment_text */	Programmer’s comment, where comment_text can be any number of lines of text.
`EXCEPTION` <exception_name>	Raises the named exception: an exception is a user-defined error that returns an error message to the calling application unless handled by a `WHEN` statement.
`EXECUTE PROCEDURE` <proc_name> [`[, …]] [RETURNING_VALUES [, …]]`	Executes stored procedure, <proc_name>, with the listed input arguments, returning values in the listed output arguments following `RETURNING_VALUES`; input and output arguments must be local variables.
`EXIT`	Jumps to the final `END` statement in the procedure.
`FOR` <select_statement> `DO` <compound_statement>	Repeats the statement or block following `DO` for every qualifying row retrieved by <select_statement>. <select_statement> is like a normal `SELECT` statement.
<compound_statement>	Either a single statement in procedure and trigger language or a block of statements bracketed by `BEGIN` and `END`.
`IF` (<condition>) `THEN` <compound_statement> [`ELSE` <compound_statement>]	Tests <condition>, and if it is `TRUE`, performs the statement or block following `THEN`; otherwise, performs the statement or block following `ELSE`, if present. <condition>: a Boolean expression (`TRUE`, `FALSE`, or `UNKNOWN`), generally two expressions as operands of a comparison operator.
`NEW`.<column>	New context variable that indicates a new column value in an `INSERT` or `UPDATE` operation.
`OLD`.<column>	Old context variable that indicates a column value before an `UPDATE` or `DELETE` operation.
`POST_EVENT` <event_name> \| <col>	Posts the event, <event_name>, or uses the value in <col> as an event name.
`SUSPEND`	In a `SELECT` procedure: Suspends execution of procedure until next `FETCH` is issued by the calling application. Returns output values, if any, to the calling application. Not recommended for executable procedures.
`WHILE` (<condition>) `DO` <compound_statement>	While <condition> is `TRUE`, keep performing <compound_statement>: Tests <condition>, and performs <compound_statement> if condition is `TRUE`. Repeats this sequence until <condition> is no longer `TRUE`.
`WHEN` {<error> [, <error> …] \| `ANY`} `DO` <compound_statement>	Error-handling statement: when one of the specified errors occurs, performs <compound_statement>: `WHEN` statements, if present, must come at the end of a block, just before `END`. <error>: `EXCEPTION` <exception_name>, `SQLCODE` <errcode> or `GDSCODE` errcode. `ANY`: Handles any errors.

The stored procedure and trigger language does not include many of the statement types available in DSQL or gpre. The following statement types are not supported in triggers or stored procedures:

Data definition language statements: CREATE, ALTER, DROP, DECLARE EXTERNAL FUNCTION, and DECLARE FILTER
Transaction control statements: SET TRANSACTION, COMMIT, ROLLBACK
Dynamic SQL statements: PREPARE, DESCRIBE, EXECUTE
CONNECT/DISCONNECT, and sending SQL statements to another database
GRANT/REVOKE
SET GENERATOR
EVENT INIT/WAIT
BEGIN/END DECLARE SECTION
BASED ON
WHENEVER
DECLARE CURSOR
OPEN
FETCH

Examples: The following procedure, SUB_TOT_BUDGET, takes a department number as its input parameter, and returns the total, average, smallest, and largest budgets of departments with the specified HEAD_DEPT.

CREATE PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT CHAR(3))
RETURNS (tot_bw1udget DECIMAL(12, 2), avg_budget DECIMAL(12, 2),
min_budget DECIMAL(12, 2), max_budget DECIMAL(12, 2))
AS
BEGIN
SELECT SUM(BUDGET), AVG(BUDGET), MIN(BUDGET), MAX(BUDGET)
FROM DEPARTMENT
WHERE HEAD_DEPT = :head_dept
INTO :tot_budget, :avg_budget, :min_budget, :max_budget;
EXIT;
END ;

The following SELECT procedure, ORG_CHART, displays an organizational chart that shows the department name, the parent department, the department manager, the manager’s job title, and the number of employees in the department:

CREATE PROCEDURE ORG_CHART
RETURNS (HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25),
MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER)
AS
DECLARE VARIABLE mngr_no INTEGER;
DECLARE VARIABLE dno CHAR(3);
BEGIN
FOR SELECT H.DEPARTMENT, D.DEPARTMENT, D.MNGR_NO, D.DEPT_NO
FROM DEPARTMENT D
LEFT OUTER JOIN DEPARTMENT H ON D.HEAD_DEPT = H.DEPT_NO
ORDER BY D.DEPT_NO
INTO :head_dept, :department, :mngr_no, :dno
DO
BEGIN
IF (:mngr_no IS NULL) THEN
BEGIN
MNGR_NAME = '--TBH--';
TITLE = '';
END
ELSE
SELECT FULL_NAME, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NO = :mngr_no
INTO :mngr_name, :title;
SELECT COUNT(EMP_NO)
FROM EMPLOYEE
WHERE DEPT_NO = :dno
INTO :emp_cnt;
SUSPEND;
END
END ;

When ORG_CHART is invoked, for example in the following isql statement:

SELECT * FROM ORG_CHART

it displays the department name for each department, which department it is in, the department manager’s name and title, and the number of employees in the department.

HEAD_DEPT	DEPARTMENT	MGR_NAME	TITLE	EMP_CNT
	Corporate Headquarters	Bender, Oliver H.	CEO	2
Corporate Headquarters	Sales and Marketing	MacDonald, Mary S.	VP	2
Sales and Marketing	Pacific Rim Headquarters	Baldwin, Janet ?	Sales	2
Pacific Rim Headquarters	Field Office: Japan	Yamamoto, Takashi	SRep	2
Pacific Rim Headquarters	Field Office: Singapore	—TBH—		0

ORG_CHART must be used as a select procedure to display the full organization. If called with EXECUTE PROCEDURE, the first time it encounters the SUSPEND statement, it terminates, returning the information for Corporate Headquarters only.

Advance To:

CREATE ROLE

CREATE PROCEDURE

See Also

Advance To: