MySQL 5.0 FAQ: Stored Procedures and Functions
2872
B.4.2: Where can I find documentation for MySQL stored procedures and stored functions?
See
Section 18.2, “Using Stored Routines (Procedures and Functions)”
.
B.4.3: Is there a discussion forum for MySQL stored procedures?
Yes. See
http://forums.mysql.com/list.php?98
.
B.4.4: Where can I find the ANSI SQL 2003 specification for stored procedures?
Unfortunately, the official specifications are not freely available (ANSI makes them available for
purchase). However, there are books—such as SQL-99 Complete, Really by Peter Gulutzan and
Trudy Pelzer—which give a comprehensive overview of the standard, including coverage of stored
procedures.
B.4.5: How do you manage stored routines?
It is always good practice to use a clear naming scheme for your stored routines. You can manage
stored procedures with
CREATE [FUNCTION|PROCEDURE]
,
ALTER [FUNCTION|PROCEDURE]
,
DROP [FUNCTION|PROCEDURE]
, and
SHOW CREATE [FUNCTION|PROCEDURE]
. You can obtain
information about existing stored procedures using the
ROUTINES
table in the
INFORMATION_SCHEMA
database (see
Section 19.8, “The
INFORMATION_SCHEMA ROUTINES
Table”
).
B.4.6: Is there a way to view all stored procedures and stored functions in a given database?
Yes. For a database named
dbname
, use this query on the
INFORMATION_SCHEMA.ROUTINES
table:
SELECT ROUTINE_TYPE, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='
dbname
';
For more information, see
Section 19.8, “The
INFORMATION_SCHEMA ROUTINES
Table”
.
The body of a stored routine can be viewed using
SHOW CREATE FUNCTION
(for a stored function)
or
SHOW CREATE PROCEDURE
(for a stored procedure). See
Section 13.7.5.8, “
SHOW CREATE
PROCEDURE
Syntax”
, for more information.
B.4.7: Where are stored procedures stored?
In the
proc
table of the
mysql
system database. However, you should not access the tables in
the system database directly. Instead, use
SHOW CREATE FUNCTION
to obtain information about
stored functions, and
SHOW CREATE PROCEDURE
to obtain information about stored procedures. See
Section 13.7.5.8, “
SHOW CREATE PROCEDURE
Syntax”
, for more information about these statements.
You can also query the
ROUTINES
table in the
INFORMATION_SCHEMA
database—see
Section 19.8,
“The
INFORMATION_SCHEMA ROUTINES
Table”
, for information about this table.
B.4.8: Is it possible to group stored procedures or stored functions into packages?
No. This is not supported in MySQL 5.0.
B.4.9: Can a stored procedure call another stored procedure?
Yes.
B.4.10: Can a stored procedure call a trigger?
A stored procedure can execute an SQL statement, such as an
UPDATE
, that causes a trigger to
activate.
B.4.11: Can a stored procedure access tables?
Yes. A stored procedure can access one or more tables as required.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...