1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
Stored Procedures implemented 2003-09-16:
Summary of Not Yet Implemented:
- SQL queries (like SELECT, INSERT, UPDATE etc) in FUNCTION bodies
- External languages
- Access control
- Routine characteristics (mostly used for external languages)
- SQL-99 COMMIT (related to BEGIN/END)
- FOR-loops
- CASCADE/RESTRICT for ALTER and DROP
- ALTER/DROP METHOD (as it implies User Defined Types)
- SIGNAL and RESIGNAL, and UNDO handlers
Summary of what's implemented:
- SQL PROCEDUREs/FUNCTIONs (CREATE/DROP)
- CALL
- DECLARE of local variables
- BEGIN/END, SET, CASE, IF, LOOP, WHILE, REPEAT, ITERATE, LEAVE
- SELECT INTO local variables
- "Non-query" FUNCTIONs only
- Prepared SP caching
- CONDITIONs and HANDLERs
- Simple read-only CURSORs.
List of what's implemented:
- CREATE PROCEDURE|FUNCTION name ( args ) body
No routine characteristics yet.
- ALTER PROCEDURE|FUNCTION name ...
Is parsed, but a no-op (as there are no characteristics implemented yet).
CASCADE/RESTRICT is not implemented (and CASCADE probably will not be).
- DROP PROCEDURE|FUNCTION [IF EXISTS] name
CASCADE/RESTRICT is not implemented (and CASCADE probably will not be).
- CALL name (args)
OUT and INOUT parameters are only supported for local variables, and
therefore only useful when calling such procedures from within another
procedure.
Note: For the time being, when a procedure with OUT/INOUT parameter is
called, the out values are silently discarded. In the future, this
will either generate an error message, or it might even work to
call all procedures from the top-level.
- Function/Procedure body:
- BEGIN/END
Is parsed, but not the real thing with (optional) transaction
control, it only serves as block syntax for multiple statements (and
local variable binding).
Note: Multiple statements requires a client that can send bodies
containing ";". This is handled in the CLI clients mysql and
mysqltest with the "delimiter" command. Changing the end-of-query
delimiter ";" to for instance "|" allows ";" to be used in the
routine body.
- SET of local variables
Implemented as part of the pre-existing SET syntax. This allows an
extended syntax of "SET a=x, b=y, ..." where different variable types
(SP local and global) can be mixed. This also allows combinations
of local variables and some options that only make sense for
global/system variables; in that case the options are accepted but
ignored.
- The flow control constructs: CASE, IF, LOOP, WHILE, ITERATE and LEAVE
are fully implemented.
- SELECT ... INTO local variables (as well as global session variables)
is implemented. (Note: This is not SQL-99 feature, but common in other
databases.)
- A FUNCTION can have flow control contructs, but must not contain
an SQL query, like SELECT, INSERT, UPDATE, etc. The reason is that it's
hard to allow this is that a FUNCTION is executed as part of another
query (unlike a PROCEDURE, which is called as a statement). The table
locking scheme used makes it difficult to allow "subqueries" during
FUNCTION invokation.
- SPs are cached, but with a separate cache for each thread (THD).
There are still quite a few non-reentrant constructs in the lexical
context which makes sharing prepared SPs impossible. And, even when
this is resolved, it's not necessarily the case that it will be faster
than a cache per thread. A global cache requires locks, which might
become a buttleneck. (It would save memory though.)
- CONDITIONs and HANDLERs are implemented, but not the SIGNAL and
RESIGNAL statements. (It's unclear if these can be implemented.)
The semantics of CONDITIONs is expanded to allow catching MySQL error
codes as well. UNDO handlers are not implemented (since we don't have
SQL-99 style transaction control yet).
- Simple read-only CURSORs are implemented, but not yet any of the
optional arguments to DECLARE (SCROLL, SENSITIVE, etc) or FETCH
(NEXT, PRIOR, etc). Cursors are ASENSITIVE, READ-ONLY, non-SCROLLing.
(The additional syntax will be added for completeness, but for the
most part unsupported with the current underlying cursor mechanism.)
Closed questions:
- What is the expected result when creating a procedure with a name that
already exists? An error or overwrite?
Answer: Error
- Do PROCEDUREs and FUNCTIONs share namespace or not? I think not, but the
we need to flag the type in the mysql.proc table and the name alone is
not a unique key any more, or, we have separate tables.
(Unfortunately, mysql.func is already taken. Use "sfunc" and maybe even
rename "proc" into "sproc" while we still can, for consistency?)
Answer: Same tables, with an additional key-field for the type.
Open questions/issues:
- SQL-99 variables and parameters are typed. For the present we don't do
any type checking, since this is the way MySQL works. I still don't know
if we should keep it this way, or implement type checking. Possibly we
should have optional, uset-settable, type checking.
|
