Firebird supports two styles of stored procedures: executable and selectable.
In DSQL, EXECUTE PROCEDURE executes (calls) an executable stored procedure—that is, a stored procedure designed to perform some operations on the server, optionally returning a single line of one or more return values. The statement for executing such a procedure has the following general format:EXECUTE PROCEDURE procedure-name [(<list of input values>)]
The following simple example illustrates calling an executable procedure that accepts two input arguments, performs some operations on the server, and exits:EXECUTE PROCEDURE DO_IT(49, '25-DEC-2004');
In applications, it is more powerful to use parameters (see the section “Using Parameters”) in query statements that execute stored procedures, for example:EXECUTE PROCEDURE DO_IT(:IKEY, :REPORT_DATE);
orEXECUTE PROCEDURE DO_IT(?, ?);
A selectable stored procedure is capable of returning a multi-row set of data in response to a specialized SELECT statement form, as follows:SELECT <list of output columns> FROM procedure-name [(<list of input values>)] [WHERE <search predicates>] [ORDER BY <list drawn from output columns>]
In the following PSQL fragment, a stored procedure is defined to accept a single key as an input argument and return a set of rows. The RETURNS clause defines the output set:CREATE PROCEDURE GET_COFFEE_TABLE (IKEY INTEGER) RETURNS ( BRAND_ID INTEGER, VARIETY_NAME VARCHAR(40), COUNTRY_OF_ORIGIN VARCHAR(30)) AS ..........
The application selects the output set from the stored procedure as follows:SELECT BRAND_ID, VARIETY_NAME, COUNTRY_OF_ORIGIN FROM GET_COFFEE_TABLE(5002);
This is the same example, with the input argument parameterized:SELECT BRAND_ID, VARIETY_NAME, COUNTRY_OF_ORIGIN FROM GET_COFFEE_TABLE(:IKEY);/* Delphi */
orSELECT BRAND_ID, VARIETY_NAME, COUNTRY_OF_ORIGIN FROM GET_COFFEE_TABLE(?);
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.