| 1 |
SQL Language Extension: EXECUTE BLOCK |
| 2 |
|
| 3 |
Function: |
| 4 |
Allow execute PL-SQL block as if it is stored procedure. |
| 5 |
Supports input and output parameters |
| 6 |
|
| 7 |
Autor: |
| 8 |
Vlad Khorsun <hvlad at users.sourceforge.net> |
| 9 |
|
| 10 |
Syntax: |
| 11 |
EXECUTE BLOCK [ (param datatype = ?, param datatype = ?, ...) ] |
| 12 |
[ RETURNS (param datatype, param datatype, ...) } |
| 13 |
AS |
| 14 |
[DECLARE VARIABLE var datatype; ...] |
| 15 |
BEGIN |
| 16 |
... |
| 17 |
END |
| 18 |
|
| 19 |
Client-side: |
| 20 |
The call isc_dsql_sql_info with parameter isc_info_sql_stmt_type returns |
| 21 |
- isc_info_sql_stmt_select, if block has output parameters. |
| 22 |
Semantics of a call is similar to SELECT query - client has open cursor, |
| 23 |
can fetch data from it, and must close it after use. |
| 24 |
|
| 25 |
- isc_info_sql_stmt_exec_procedure, if block has no output parameters. |
| 26 |
Semantics of a call is similar to EXECUTE query - client has no cursor, |
| 27 |
execution runs until first SUSPEND or end of block |
| 28 |
|
| 29 |
The client should preprocess only head of the SQL statement or use '?' |
| 30 |
instead of ':' as parameter indicator because in a body of the block may be links |
| 31 |
to local variables and \ or parameters with a colon ahead. |
| 32 |
|
| 33 |
Example: |
| 34 |
User SQL is |
| 35 |
EXECUTE BLOCK (X INTEGER = :X) RETURNS (Y VARCHAR) |
| 36 |
AS |
| 37 |
DECLARE V INTEGER; |
| 38 |
BEGIN |
| 39 |
INSERT INTO T(...) VALUES (... :X ...); |
| 40 |
|
| 41 |
SELECT ... FROM T INTO :Y; |
| 42 |
SUSPEND; |
| 43 |
END |
| 44 |
|
| 45 |
Preprocessed SQL is |
| 46 |
EXECUTE BLOCK (X INTEGER = ?) RETURNS (Y VARCHAR) |
| 47 |
AS |
| 48 |
DECLARE V INTEGER; |
| 49 |
BEGIN |
| 50 |
INSERT INTO T(...) VALUES (... :X ...); |
| 51 |
|
| 52 |
SELECT ... FROM T INTO :Y; |
| 53 |
SUSPEND; |
| 54 |
END |
| 55 |
|