mysql 存储过程 compile_存储过程 | iMySQL | 老叶茶馆

A.4. MySQL 5.0 FAQ — 存储过程

Questions

26.4.1:

MySQL 5.0是否支持存储过程?

Does MySQL 5.0 support stored procedures?

26.4.2:

在哪可以找到MySQL的存储过程和函数的文档?

Where can I find documentation for MySQL stored procedures

and stored functions?

26.4.3:

哪里有关于MySQL存储过程讨论的地方呢?

Is there a discussion forum for MySQL stored procedures?

26.4.4:

在哪可以找到ANSI SQL 2003规范中关于存储过程的那部分?

Where can I find the ANSI SQL 2003 specification for stored

procedures?

26.4.5:

如何管理存储过程呢?

How do you manage stored routines?

26.4.6:

怎么查看一个指定数据库中的存储过程和函数?

Is there a way to view all stored procedures and stored

functions in a given database?

26.4.7:

存储过程保存在哪里呢?

Where are stored procedures stored?

26.4.8:

可否将存储过程分组或者把函数保存在包中?

Is it possible to group stored procedures or stored

functions into packages?

26.4.9:

存储过程中可否调用其他存储过程呢?

Can a stored procedure call another stored procedure?

26.4.10:

存储过程中能否调用触发器?

Can a stored procedure call a trigger?

26.4.11:

存储过程中能否访问数据表?

Can a stored procedure access tables?

26.4.12:

存储过程中是否有产生应用程序错误的语句呢?

Do stored procedures have a statement for raising

application errors?

26.4.13:

存储过程是否支持溢出处理?

Do stored procedures provide exception handling?

26.4.14:

MySQL 5.0能否返回存储过程的结果集?

Can MySQL 5.0 stored routines return result

sets?

26.4.15:

存储过程支持 WITH RECOMPILE 吗?

Is WITH RECOMPILE supported for stored

procedures?

26.4.16:

MySQL是否有类似 mod_plsql 的网关,使得Apache能直接调用数据库的存储过程?

Is there a MySQL equivalent to using

mod_plsql as a gateway on Apache to talk

directly to a stored procedure in the database?

26.4.17:

我能否向存储过程传递数组参数?

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

26.4.18:

我能否把一个游标作为存储过程的 IN (传入)参数?

Can I pass a cursor as an IN parameter to

a stored procedure?

26.4.19:

我能否把一个游标作为存储过程的 OUT (传出)参数?

Can I return a cursor as an OUT parameter

from a stored procedure?

26.4.20:

能否在存储过程中为了调试打印出某个变量的值?

Can I print out a variable's value within a stored procedure

for debugging purposes?

26.4.21:

能否在存储过程中提交一个的回滚事务?

Can I commit or roll back transactions inside a stored

procedure?

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?

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

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';

存储例程的主体部分可以用语句 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';

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”.

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.