CREATE PROCEDURE

From InterBase

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 IFTHENELSE, WHILEDO, FOR SELECTDO, 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

BEGINEND

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.

See Also

Advance To: