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
|
Stored Procedures implemented 2004-01-29:
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.
- SHOW CREATE PROCEDURE/FUNCTION and SHOW PROCEDURE/FUNCTION STATUS
Summary of Not Yet Implemented:
- SQL statements using tables (like SELECT, INSERT, UPDATE etc) in FUNCTIONs
- External languages
- Access control
- 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
List of what's implemented:
- CREATE PROCEDURE|FUNCTION name ( args ) characteristics body
where characteristics is:
LANGUAGE SQL |
[NOT] DETERMINISTIC |
SQL SECURITY [DEFINER|INVOKER] |
COMMENT string
However the DETERMINISTIC setting is not currently used.
- ALTER PROCEDURE|FUNCTION name characteristics
CASCADE/RESTRICT is not implemented.
characteristics is:
COMMENT string |
SQL SECURITY [DEFINER|INVOKER] |
NAME newname
- DROP PROCEDURE|FUNCTION [IF EXISTS] name
CASCADE/RESTRICT is not implemented.
- CALL name (args)
OUT and INOUT parameters are also works for user variables ("global"
variables) - i.e., if a procedure is defined as:
CREATE PROCEDURE foo(OUT p INT) ...;
a call like:
CALL foo(@x);
will set @x to the output value.
- 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/statement, 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 bottleneck. (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.)
N.B. The current implementation is temporary and only works within a
stored procedure, and may not perform well for very large result sets.
A "real" cursor implementation is under development; this will replace
the current one when it's finished.
- SHOW procedures and functions
SHOW CREATE PROCEDURE|FUNCTION <name>
returns the definition of a routine.
SHOW PROCEDURE|FUNCTION STATUS [LIKE <pattern>]
returns characteristics of routines, like the name, type, creator,
creation and modification dates, etc.
|
