存储过程

Questions

Questions and Answers

26.4.1:
Does MySQL 5.0 support stored procedures?

是的.MySQL 5.0支持2中类型的存储例程 - 存储过程和存储函数.

Yes. MySQL 5.0 supports two types of stored
routines — stored procedures and stored functions.

26.4.2:
Where can I find documentation for MySQL stored procedures
and stored functions?

详情请看 Chapter 17, Stored Procedures and Functions.

See Chapter 17, Stored Procedures and Functions.

26.4.3:
Is there a discussion forum for MySQL stored procedures?

是的.详情请看 http://forums.mysql.com/list.php?98.

Yes. See
http://forums.mysql.com/list.php?98.

26.4.4:
Where can I find the ANSI SQL 2003 specification for stored
procedures?

很抱歉,官方的规范还不是免费的(ANSI 对此是收费的).尽管如此,市面上有些书 - 如 Peter Gulutzan 和 Trudy Pelzer 所著的 SQL-99 Complete, Really,全面讲述了该标准,也包括存储过程.

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.

26.4.5:
How do you manage stored routines?

实践证明,存储例程中使用清晰的模式名很有用.管理存储过程可以使用语句 CREATE [FUNCTION|PROCEDURE], ALTER [FUNCTION|PROCEDURE, DROP [FUNCTION|PROCEDURE], 和 SHOW CREATE [FUNCTION|PROCEDURE].可以从数据库 INFORMATION_SCHEMA 的 ROUTINES 表中查看已经存在的存储过程的信息.详情请看 Section 20.14, “The INFORMATION_SCHEMA ROUTINES Table”.

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 20.14, “The INFORMATION_SCHEMA ROUTINES Table”).

26.4.6:
Is there a way to view all stored procedures and stored
functions in a given database?

是的.在INFORMATION_SCHEMA.ROUTINES 中使用以下语句就能查看 dbname 下所有的存储过程了.

SELECT ROUTINE_TYPE, ROUTINE_NAME 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_SCHEMA='dbname';

详情请看 Section 20.14, “The INFORMATION_SCHEMA ROUTINES Table”.

存储例程的主体部分可以用语句 SHOW CREATE FUNCTION(适用于存储函数) 和 SHOW CREATE PROCEDURE(适用于存储过程) 来查看,详情请看 Section 13.5.4.5, “SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION Syntax”.

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 20.14, “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.5.4.5, “SHOW CREATE PROCEDURE and SHOW CREATE
FUNCTION Syntax”, for
more information.

26.4.7:
Where are stored procedures stored?

在 mysql 系统数据库的 proc 表中.不过,不要直接访问系统数据库.相反地,使用语句 SHOW CREATE FUNCTION(适用于存储函数) 和 SHOW CREATE PROCEDURE(适用于存储过程) 来查看,详情请看 Section 13.5.4.5, “SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION Syntax”.

也可以查询 INFORMATION_SCHEMA 数据库下的 ROUTINES 表来取得相关信息,详情请看 Section 20.14, “The INFORMATION_SCHEMA ROUTINES Table”.

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.5.4.5, “SHOW CREATE PROCEDURE and SHOW CREATE
FUNCTION Syntax”, for
more information about these statements.

You can also query the ROUTINES table in
the INFORMATION_SCHEMA database —
see Section 20.14, “The INFORMATION_SCHEMA ROUTINES Table”, for information about
this table.

26.4.8:
Is it possible to group stored procedures or stored
functions into packages?

不.MySQL 5.0不支持

No. This is not supported in MySQL 5.0.

26.4.9:
Can a stored procedure call another stored procedure?

是的

Yes.

26.4.10:
Can a stored procedure call a trigger?

存储过程中可以执行一条SQL语句,例如 UPDATE 语句,这就会导致触发器起作用.

A stored procedure can execute an SQL statement, such as an
UPDATE, that causes a trigger to fire.

26.4.11:
Can a stored procedure access tables?

是的.存储过程可根据需要访问一个或多个表.

Yes. A stored procedure can access one or more tables as
required.

26.4.12:
Do stored procedures have a statement for raising
application errors?

MySQL 5.0还不行,我们正打算在将来的MySQL发行版中实现标准SQL中的 SIGNAL 和 RESIGNAL 语句.

Not in MySQL 5.0. We intend to implement the
SQL standard SIGNAL and
RESIGNAL statements in a future MySQL
release.

26.4.13:
Do stored procedures provide exception handling?

MySQL 根据SQL标准实现了 HANDLER 定义.详情请看 Section 17.2.8.2, “DECLARE Handlers”, for.

MySQL implements HANDLER definitions
according to the SQL standard. See
Section 17.2.8.2, “DECLARE Handlers”, for
details.

26.4.14:
Can MySQL 5.0 stored routines return result
sets?

是的.如果你在存储过程或存储函数中执行一条普通的 SELECT 语句,那么结果集会直接返回给客户端.需要使用MySQL 4.1的客户端-服务器协议来支持它,这意味着 - 例如在PHP中,就需要用 mysqli 扩展而非 mysql 扩展才能实现.

Yes. If you perform an ordinary SELECT
inside a stored procedure or stored function, the result set
is returned directly to the client. You need to use the
MySQL 4.1 client-server protocol for this to work. This
means that — for instance — in PHP, you need to
use the mysqli extension rather than the
old mysql extension.

26.4.15:
Is WITH RECOMPILE supported for stored
procedures?

MySQL 5.0还不支持.

Not in MySQL 5.0.

26.4.16:
Is there a MySQL equivalent to using
mod_plsql as a gateway on Apache to talk
directly to a stored procedure in the database?

MySQL 5.0还不没有.

There is no equivalent in MySQL 5.0.

26.4.17:
Can I pass an array as input to a stored procedure?

MySQL 5.0还不支持.

Not in MySQL 5.0.

26.4.18:
Can I pass a cursor as an IN parameter to
a stored procedure?

在MySQL 5.0中,游标只能使用在存储过程中.

In MySQL 5.0, cursors are available inside
stored procedures only.

26.4.19:
Can I return a cursor as an OUT parameter
from a stored procedure?

在MySQL 5.0中,游标只能使用在存储过程中.不过,如果你在 SELECT 语句中如果没有打开游标的话,那么结果集会被直接发送给客户端,也可以 SELECT INTO 到变量中.详情请看 Section 13.2.7, “SELECT Syntax”.

In MySQL 5.0, cursors are available inside
stored procedures only. However, if you do not open a cursor
on a SELECT, the result will be sent
directly to the client. You can also SELECT
INTO variables. See Section 13.2.7, “SELECT Syntax”.

26.4.20:
Can I print out a variable's value within a stored procedure
for debugging purposes?

是的.如果你在存储过程或存储函数中执行一条普通的 SELECT 语句,那么结果集会直接返回给客户端.需要使用MySQL 4.1的客户端-服务器协议来支持它,这意味着 - 例如在PHP中,就需要用 mysqli 扩展而非 mysql 扩展才能实现.

Yes. If you perform an ordinary SELECT
inside a stored procedure or stored function, the result set
is returned directly to the client. You will need to use the
MySQL 4.1 client-server protocol for this to work. This
means that — for instance — in PHP, you need to
use the mysqli extension rather than the
old mysql extension.

26.4.21:
Can I commit or roll back transactions inside a stored
procedure?

是的.不过不能在存储函数中执行事务性操作.

Yes. However, you cannot perform transactional operations
within a stored function.