PSQL module definitions are really a single SQL statement that begins with a CREATE clause and ends with a terminator. Within the module definition are a number of elements: clauses, keywords, blocks of multipl statements, branches, loops, and others. Some elements are mandatory; others are optional.
Although the complex definition of a PSQL module is a DDL statement, the SQL extensions within it are elements of a structured, high-level language that has certain distinctive rules. An important one to know about before you begin is the statement terminator.
The CREATE Statement
Source code for procedures and triggers is constructed inside a “super-statement” that begins with the keyword CREATE PROCEDURE or CREATE TRIGGER and ends with a terminator symbol following the final END statement, for example:CREATE PROCEDURE Name...
With both stored procedures and triggers, all statements following the keyword AS comprise the local variables (if any) and the logic of the program module. The main difference between triggers and stored procedures is in the header portion of the CREATE statement.
Each statement inside a stored procedure or trigger body—other than BEGIN and END—must be terminated b a semicolon. No other symbol is valid for terminating statements in PSQL. In DSQL, for both DML and DDL, the semicolon also happens to be the default statement terminator in Firebird and it is also the SQL standard for terminating statements.
This situation is going to present a logical problem for the parser that pre-compiles our PSQL modules: Which semicolons terminate statements inside the module and which one terminates the CREATE definition?
To get around this problem, Firebird has a switching SET TERM syntax that allows you to set a different external terminator, to be in effect for external statements while PSQL definitions are being parsed. In scripts, experienced developers often use a single SET TERM statement at the beginning of all scripts, to have their favorit alternative terminator in effect at all times during scripting. Some database admin tools support configuring their editors and metadata extraction programs with an alternative terminator.
SET TERM statements are used in isql and in scripts.
Isql pre-parses every statement and sends any terminated statement directly to the server as a single command. SET TERM is one of its own ISQL statements that it responds to not by sending a request to the server but by preparing its parser to interpret terminators differently. (Other ISQL SET statements also invoke special activity in the Isql program that is not meaningful outside isql.)
The DSQL layer does not recognize terminators for statements at all. Most of the other utilities that process scripts actually dispatch the DDL statements off to the server one by one without terminators. They provide parsing of their own to recognize the beginning and end of CREATE PROCEDURE statements and pass the internal semicolon terminators simply as regular symbols within the compound statement syntax.
When you use such a utility for creating PSQL modules interactively, it will usually throw exceptions at SET TERM since, as an SQL statement, it has no meaning outside isql. However, in scripts, these utilities usually parse for and expect a SET TERM statement and use the alternative terminator internally, in a manner equivalent to the way isql handles it.
So, use SET TERM in isql if you are using that tool to process your CREATE PROCEDURE statements interactively, and use it in scripts.
The alternative terminator can be any string symbol you like, except a semicolon, a space character, or a single -quote character. If you use an ordinary character, it will be case sensitive. It can be multiple characters if you prefer, including embedded spaces, and it must not be a reserved keyword. Both of the following statements are valid:SET TERM ^; SET TERM boing! ;
In PSQL definitions, use semicolons for all internal statements except BEGIN and END, and use the alternative terminator for the final END statement:... END ^
To return to “normal” statement termination, issue a second SET TERM statement that is the reverse of the first:... END ^ COMMIT ^ SET TERM ;^
In Figure, the main elements of a PSQL module definition are split to illustrate the required elements of the module’s header and body sections. The mandatory parts are shaded.
Required elements of a PSQL module definition
The name of the procedure or trigger must be unique in the database.
For a trigger:
For a stored procedure:
For stored procedures and triggers:
Table shows the PSQL language elements available in Firebird.
PSQL Extensions for Stored Procedures and Triggers
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.