| 1 |
SQL Language Extension: default paremeters in stored procedures |
| 2 |
|
| 3 |
Function: |
| 4 |
allow input parameters of stored procedures to have optional default values |
| 5 |
|
| 6 |
Autor: |
| 7 |
Vlad Khorsun <hvlad at users.sourceforge.net> |
| 8 |
|
| 9 |
Syntax: |
| 10 |
same as default value definition of column or domain, except that |
| 11 |
you can use '=' in place of 'DEFAULT' keyword. Parameters with default values |
| 12 |
must be last in parameter list i.e. not allowed to define parameter without |
| 13 |
default value after parameter with default value. Caller must set first few |
| 14 |
parameters, i.e. not allowed set param1, param2, miss param3, set param4... |
| 15 |
|
| 16 |
|
| 17 |
Default values substitution occurs at run-time. If you define procedure with |
| 18 |
defaults (say P1), call it from another procedure (say P2) and skip some last |
| 19 |
parameters (with default value) then default values for P1 will be substituted |
| 20 |
by the engine at time of the beginning of execution P1. Then if you change |
| 21 |
default values for P1 it is not need to recompile P2. But it is still necessary |
| 22 |
to disconnect all client connections, for more details see IB6 Beta documentation |
| 23 |
"Data Definition Guide", section "Altering and dropping procedures in use" |
| 24 |
|
| 25 |
|
| 26 |
Examples: |
| 27 |
|
| 28 |
CONNECT ... ; |
| 29 |
|
| 30 |
CREATE PROCEDURE P1 (X INTEGER = 123) |
| 31 |
RETURNS (Y INTEGER) |
| 32 |
AS |
| 33 |
BEGIN |
| 34 |
Y = X; |
| 35 |
SUSPEND; |
| 36 |
END; |
| 37 |
COMMIT; |
| 38 |
|
| 39 |
SELECT * FROM P1; |
| 40 |
|
| 41 |
Y |
| 42 |
============ |
| 43 |
|
| 44 |
123 |
| 45 |
|
| 46 |
EXECUTE PROCEDURE P1; |
| 47 |
|
| 48 |
Y |
| 49 |
============ |
| 50 |
123 |
| 51 |
|
| 52 |
|
| 53 |
CREATE PROCEDURE P2 |
| 54 |
RETURNS (Y INTEGER) |
| 55 |
AS |
| 56 |
BEGIN |
| 57 |
FOR SELECT Y FROM P1 INTO :Y |
| 58 |
DO SUSPEND; |
| 59 |
END; |
| 60 |
COMMIT; |
| 61 |
|
| 62 |
|
| 63 |
SELECT * FROM P2; |
| 64 |
|
| 65 |
Y |
| 66 |
============ |
| 67 |
|
| 68 |
123 |
| 69 |
|
| 70 |
|
| 71 |
ALTER PROCEDURE P1 (X INTEGER = CURRENT_TRANSACTION) |
| 72 |
RETURNS (Y INTEGER) |
| 73 |
AS |
| 74 |
BEGIN |
| 75 |
Y = X; |
| 76 |
SUSPEND; |
| 77 |
END; |
| 78 |
COMMIT; |
| 79 |
|
| 80 |
SELECT * FROM P1; |
| 81 |
|
| 82 |
Y |
| 83 |
============ |
| 84 |
|
| 85 |
5875 |
| 86 |
|
| 87 |
SELECT * FROM P2; |
| 88 |
|
| 89 |
Y |
| 90 |
============ |
| 91 |
|
| 92 |
123 |
| 93 |
|
| 94 |
COMMIT; |
| 95 |
|
| 96 |
CONNECT ... ; |
| 97 |
|
| 98 |
SELECT * FROM P2; |
| 99 |
|
| 100 |
Y |
| 101 |
============ |
| 102 |
|
| 103 |
5880 |
| 104 |
|
| 105 |
|
| 106 |
Notes: |
| 107 |
default sources and BLR's are kept in RDB$FIELDS |
| 108 |
|