Debian Codegraph @

doc/sql.extensions/README.execute_block

raw
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