Debian Codegraph @

doc/sql.extensions/README.default_parameters

raw
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