procedure mysql_mysql之procedure() and function()

自行创建一个员工信息表info，里面记录着员工姓名，年龄，地址以及工资，下面的实验要使用。

mysql> create database testdb charset utf8;

mysql> use testdb;

mysql> create table info(ID int not null auto_increment primary key,NAME CHAR(8) NOT NULL,

AGE INT NOT NULL,

ADDRESS VARCHAR(20) NOT NULL,

SALARY decimal(10,2) not null) ENGINE=innodb;

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('ZHANG',32,'Beijing',2000.00);

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('LI',25,'Shanghai',1500.00);

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('PENG',23,'Hangzhou',2000.00);

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('LIN',25,'Henan',6500.00);

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',27,'Hunan',8500.00);

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',22,'Hunan',4500.00);

mysql> INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('GAO',24,'Hebei',10000.00);

存储过程和存储函数

简单地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。

存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值，函数可以从语句外调用(即通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。

存储过程的好处：

1.由于数据库执行动作时，是先编译后执行的，然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。

2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。

3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

4.可在生产环境下执行，修复BUG。

创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下：

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body

CREATE PROCEDURE为用来创建存储函数的关键字；sp_name为存储过程的名称；proc_parameter为指定存储过程的参数列表，可省略，列表形式如下：

[ IN | OUT | INOUT ] param_name type

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。另外如果你传入的参数中文的，那么还可以在“[ IN | OUT | INOUT ] param_name type”加入字符集，如“IN id INT character set utf8”。

Characteristics指定存储过程的特性，有以下取值：

LANGUAGE SQL

说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。

[NOT] DETERMINISTIC

指明存储过程执行的结果是否正确，DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统会指定为CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER}

指明谁有权限来执行，DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。

COMMENT ‘string’

注释信息，可以用来描述存储过程或函数。

routine_body

是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

编写存储过程并不是件简单的事情，可能存储过程中需要复杂的SQL语句，并且要有创建存储过程的权限；但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率，因此存储过程时非常有用的。

下面是一个简单存储过程的实例：

mysql> use testdb;

mysql> delimiter $

mysql> create procedure test()

begin

select * from testdb.info;

end $

mysql> delimiter ;

其中，“delimiter $”语句的作用是将MySQL的结束符设置为“$”符，因为MySQL默认的语句结束符为分号“;”，为了避免与存储过程中SQL语句结束符相冲突，所以就是用delimiter改变存储过程的结束符，并以“END $”结束存储过程，其中存储过程定义完毕之后再使用“delimiter ;”恢复默认结束符。

然后开始定义存储过程，此存储过程名为test，使用CREATE PROCEDURE test()语句定义。此存储过程没有参数，但是后面的()仍然需要。BEGIN和END语句用来限定存储过程体，过程本身仅是一个简单的SELECT语句。每次调用这个存储过程的时候都会执行SELECT语句查看表。当然存储过程也可以是很多语句的复杂的组合，就好像这个例子刚开始给出的那个语句一样，其本身也可以调用其他的函数，来组成更加复杂的操作。

操作存储过程

1)查看存储过程

mysql> SHOW PROCEDURE STATUS\G

*************************** 1. row ***************************

Db: testdb

Name: test

Type: PROCEDURE

Definer: root@localhost

Modified: 2016-12-22 15:09:15

Created: 2016-12-22 15:09:15

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

存储过程的默认安全类型是DEFINER，并且Definer的用户为创建这个存储过程的用户。

另外在mysql.proc中也有存储过程的信息。

mysql> select * from mysql.proc\G

2)查看存储过程创建过程

mysql> show create procedure test\G

*************************** 1. row ***************************

Procedure: test

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()

begin

select * from testdb.info;

end

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

3)调用存储过程

mysql> CALL test();

4)删除存储过程

mysql> DROP PROCEDURE procedure_name;

5)修改存储过程权限

方法一：修改存储过程的definer

mysql> update mysql.proc set definer='root'@'192.168.2.%' where db='db_name';

方法二：修改sql security

sql secuirty的值决定了调用存储过程的方式，取值definer或者invoker(默认是definer)。

definer：在执行存储过程前验证definer对应的用户，如：’root’@’192.168.2.%’是否存在，以及是否具有执行存储过程的权限，若没有则报错。简单来说就是执行这个存储过程的用户不管是谁也不管有没有权限执行，只要’root’@’192.168.2.%’这个用户存在且有执行的权限即可。

invoker：在执行存储过程时判断inovker，即调用该存储过程的用户是否有相应权限，若没有则报错。简单来说也就是根据执行用户本身权限为主。

修改语法：

mysql> alter procedure procedure_name sql security invoker;

也可以在创建存储过程的时候给定definer=’test’@’%’，如下：

mysql> use testdb;

mysql> delimiter $

mysql> create definer='test'@'%' procedure test1()

begin

select * from testdb.info;

end $

mysql> delimiter ;

这个时候由于是definer模式，所以如果你的’test’@’%’用户没有执行这个存储过程的权限或者此用户不存在的话，其他用户执行这个存储过程就会报错。信息如下：

ERROR 1370 (42000): execute command denied to user 'tt'@'%' for routine 'testdb.test1'

下面再创建一个可以传参的存储过程。

mysql> use testdb;

mysql> delimiter $

mysql> create procedure param(n int)

begin

select * from testdb.info where salary > n;

end $

mysql> delimiter ;

mysql> call param(5000);

创建一个param存储过程，定义一个参数n，类型为int；然后执行SQL语句的时候使用参数n作为条件，最后调用存储过程时输入参数值。

创建存储函数

创建存储函数，需要使用CREATE FUNCITON语句，基本语法格式如下：

CREATE FUNCTION fun_name ([fun_parameer])

RETURENS type

[characteristic …] routine_body

CREATE FUNCTION为用来创建存储函数的关键字；fun_name表示存储函数的名称；fun_parameter为存储过程的参数列表，参数列表形式如下：

[ IN | OUT | INOUT ] param_name type

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

RETURNS type语句表示函数返回数据的类型；characteristic：指定存储函数的特性，取值与创建存储过程时相同，详细的看存储过程就行了。

下面创建一个存储函数，名称为fun，参数定义为空，该函数返回SELECT语句的查询结果，数值为字符串型，代码如下：

mysql> DELIMITER $

mysql> CREATE FUNCTION fun() RETURNS char(200)

BEGIN

RETURN (SELECT SALARY from testdb.info WHERE NAME="ZHANG");

end$

mysql> DELIMITER ;

如果在存储函数中的RETURN语句返回一个类型不同于函数的RETURNS字句中指定类型的值，返回值将被强制为恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集的相应的ENUM成员，从函数返回的值时字符串。

MySQL存储过程和函数有什么区别？在本质上他们都是存储程序，函数只能通过return语句返回单个值或者表对象；而存储过程不允许执行return，但是可以通过out参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一个些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在SQL语句中使用，可以在SELECT语句中作为查询语句的一个部分带哦用；而存储过程一般是作为一个独立的部分来执行。

变量的使用

在编写存储过程中，可以定义变量，变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中。

1)定义变量

在存储过程中使用DECLARE语句定义变量，语法格式如下：DECLARE var_name date_type [DEFAULT value];

var_name为局部变量的名称，DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

实例：定义名称为myparam的变量，类型为INT类型，默认值为100，代码如下。

mysql> delimiter $

mysql> create procedure var1()

begin

DECLARE myparam INT DEFAULT 100;

end$

2)输出变量

在MySQL中输出变量的方式为select @@log_bin;，但在存储过程中输出变量的方式为SELECT var_name;即可，具体代码如下：

mysql> create procedure var2()

begin

DECLARE myparam INT DEFAULT 100;

SELECT myparam;

end$

mysql> call p2()\G

*************************** 1. row ***************************

var: 100

3)为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值，MySQL中使用SET语句为变量赋值，语法格式如下：

SET var_name=expr[,var_name=expr]…;

在存储程序中的SET语句时一般SET语句的扩展版本，被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现，这允许SET a=x，b=y，…这样的扩展语法。其中不同的变量类型(局域声明变量即全局变量)可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。下面声明三个变量，然后使用SET为变量赋值。代码如下：

mysql> create procedure var3()

begin

DECLARE var1,var2,var3 INT;

SET var1 = 10, var2 = 20;

SET var3 = var1+var2;

SELECT var3;

end$

mysql> call var3()\G

*************************** 1. row ***************************

Var3: 30

MySQL中可以通过SELECT … INTO为一个或多个变量赋值，语法如下：

SELECT col INTO var_name FROM table_name

代码如下：

mysql> create procedure var4()

begin

DECLARE var INT;

select count(*) into var from testdb.info;

SELECT var;

end$

mysql> delimiter ;

mysql> call var4()\G

*************************** 1. row ***************************

var: 7

定义条件和处理程序

特定条件需要特定处理，这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。如果需要可以通过DECLARE关键字来定义条件和处理程序。

流程控制的使用

在存储过程中可以定义流程控制语句，流程控制语句用来根据条件控制语句的执行，MySQL中的用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句和REPEAT语句。每个流程中可能包含一个单独语句，或者是使用BEGIN…END构造的复合语句，构造可以被嵌套。下面介绍这些流程控制语句

1)IF语句

IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式如下：

IF condition THEN

Statement

ELSELF condition THEN

statement

ELSE

statement

END IF

IF实现了一个基本的条件构造，如果condition求值为真，响应的SQL语句列表被执行；如果没有condition匹配，则ELSE字句里的语句列表被执行。Statement可以包括一个或多个语句。如果非常熟悉Shell变量应该很好理解这些语句，另外MySQL中还有一个IF()函数，它不同于这里描述的IF语句。

示例

mysql> use testdb;

mysql> delimiter $

mysql> create procedure pif(n int,j char(1))

begin

if j='gao' then

select * from testdb.info where salary > n;

else

select * from testdb.info where salary < n;

end if;

end$

该示例中，create procedure pif(n int,j char(1))语句怎么使用在创建函数中有说明，IF主要是判断j参数的值，如果为“gao”那么执行第一条SQL语句，如果j的值不等“gao”那么就执行第二条SQL语句，IF语句都需要使用END IF来结束。

mysql> call pif(5000,'gao');

#调用存储过程pif并输入参数n和j的值

2)CASE语句

CASE是另一个进行条件判断的语句，该语句有2中语句格式，第1种格式如下：

CASE case_expr

WHEN when_value THEN statement

[WHEN when_value THEN statement]

[ELSE statement]

END CASE

其中，case_expr参数表示条件判断的表达式，决定了那一个WHEN子句会被执行；when_value参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement中的语句；statement参数表示不同when_value值的执行语句。

示例

mysql> create procedure caseone()

begin

declare val INT;

set val = 1;

case val

when 1 then select 'val is 1';

when 2 then select 'val is 2';

else select 'val is not 1 or 2';

end case;

end$

mysql> call caseone()\G

*************************** 1. row ***************************

val is 1: val is 1

使用CASE判断当val值为1时，输出字符串“val is 1”；当val值为2时，输出字符串“val is 2”；否则输出字符串“val is not 1 or 2”。

CASE语句的第二种格式如下：

CASE

WHEN condition THEN statement

[WHEN condition THEN statement]

[ELSE statement]

END CASE

其中，condition参数表示条件判断语句；statement参数表示不同条件的执行语句。该语句中，WHEN语句将被逐个执行，直到某个condition表达式为真，则执行对应THEN关键字后面的statement语句。如果没有条件匹配，ELSE字句里的语句被执行。

示例

mysql> create procedure casetwo ()

begin

declare val INT;

case

when val IS NULL then select 'val is null';

when val < 0 then select 'val is less than 0';

when val > 0 then select 'val is greater than 0';

else select 'val is 0';

end case;

end$

mysql> call casetwo()\G

*************************** 1. row ***************************

val is null: val is null

当val值为空，输出字符串“val is NULL”；当val值小于0时，输出字符串“val is less than 0”；当val值大于0时，输出字符串“val is greater than 0”；否则输出字符串“val is 0”。

3)LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程，使用LEAVE子句，LOOP语句的基本格式如下：

[loop_label:] LOOP

Statement

END LOOP [loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略；statement参数表示需要循环执行的语句。

示例

mysql> create procedure loopone()

begin

declare id int default 0;

add_loop:loop

set id = id+1;

if id >= 10 then leave add_loop;

end if;

end loop add_loop;

end$

4)LEAVE语句

LEAVE语句用来退出任何被标注的流程控制构造，LEAVE语句基本格式如下：

LEAVE label

其中，label参数表示循环的标志，LEAVE和BEGIN….END或循环一起使用。使用LEAVE语句退出循环，代码如下：

Add_num:loop

Set @count=@count+1;

If @count=50 then leave add_num;

End loop add_num;

该循环执行count加1的操作，当count的值等于50时，使用LEAVE语句跳出循环。

5)ITERATE语句

ITERATE语句将执行顺序转到语句段开头处，语句基本格式如下：

ITERATE label

Iterate只可以出现在LOOP、REPEAT和WHILE语句内。Iterate的意思为“在此循环”，label参数表示循环的标志。Iterate语句必须跟在循环标志前面。

示例

mysql> create procedure iterateone()

begin

declare var int default 0;

my_loop:loop

set var=var+1;

if var < 10 then iterate my_loop;

elseif var > 20 then leave my_loop;

end if;

select 'var is between 10 and 20';

end loop my_loop;

end$

var等于0，如果var的值小于10时，重复执行var加1操作；当var大于等于10并且小于20时，打印消息“var is between 10 and 20”；当var大于20时，退出循环。

6)REPEAT语句

REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。Repeat语句的基本格式如下：

[repeat_label]:REPEAT

Statement

UNTIL condition

End REPEAT [repeat_label]

示例：id值小于等于0之前，将重复执行循环过程。

mysql> create procedure repeatone()

begin

declare id int default 0;

repeat

set id = id+1;

until id >= 10

end repeat;

select id;

mysql> call repeatone()\G

*************************** 1. row ***************************

id: 10

7)WHILE语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。WHILE语句的基本格式如下：

[while_label:]WHILE condition DO

Statement

END WHILE [while_label]

while_label为WHILE语句的标注名称，condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行，直至condition为假，退出循环。

示例：使用WHILE计算1+n的和

create procedure whileone(n smallint)

begin

declare i int;

declare s int;

set i = 1;

set s = 0;

while i <= n do

set s = s + i;

set i = i + 1;

end while;

select s;

end$

mysql> delimiter ;

mysql> call whileone(10)\G

*************************** 1. row ***************************

s: 55

光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。在存储过程中可以使用光标对结果集进行循环处理，应用程序可以根据需要滚动或浏览其中的数据。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1)声明光标

MySQL中使用DECLARE关键字来声明光标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement

其中，cursor_name参数表示光标的名称；select_statement参数表示SELECT语句的内容，返回一个用于创建光标的结果集。

声明名称为cursor_first的光标，代码如下：

DECLARE cursor_first CURSOR FOR select * from testdb.info;

2)打开光标

OPEN cursor_first;

3)读取光标

FETCH cursor_name INTO var_name [,var_name]…{参数名称}

这个语句用指定的打开光标读取下一行，并且前进光标指针。其中，cursor_name参数表示光标的名称；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中，var_name必须在声明光标之前就定义好。

使用名称为cursor_first的光标，将查询出来的数据存入one_name和two_name这两个变量中，代码如下：

FETCH cursor_first INTO one_name,two_name;

4)关闭光标

CLOSE cursor_name

这个语句关闭先前打开的光标，如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

PS：MySQL中光标只能在存储过程和存储函数中使用。

示例1：把testdb.info中ID字段的值赋给光标mycursor，然后把mycursor读取到变量a上，最后使用LOOP循环显示a变量

mysql> delimiter $

mysql> CREATE PROCEDURE curdemo()

BEGIN

DECLARE a INT;

DECLARE mycursor CURSOR FOR SELECT ID FROM testdb.info;

OPEN mycursor;

LOOP

FETCH mycursor INTO a;

SELECT a;

END LOOP;

CLOSE mycursor;

END $

mysql> call curdemo()$

mysql> delimiter ;

调用存储过程

存储过程是通过CALL语句进行调用的，语法如下：

CALL procedure_name([parameter])

CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程，其中procedure_name为存储过程的名称，[parameter]为存储过程的参数，可选。下面顶一个countproc的存储过程，然后调用这个存储过程，代码执行如下：

mysql> delimiter $

mysql> create procedure countproc(IN sid INT,OUT num INT)

begin

select count(*) into num from testdb.info where salary > 5000;

end$

调用存储过程

mysql> delimiter ;

mysql> call countproc(5000,@num);

查看返回结果

mysql> select @num\G

*************************** 1. row ***************************

@num: 3

该存储过程返回了info表中工资高于5000的人数，把人数返回值存储在num变量中，使用SELECT查看，返回结果为3.

查看存储过程和存储函数

SHOW STATUS语句可以查看存储过程和函数的状态，其基本语法结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

这个语句是一个MySQL的扩展，它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。代码执行如下：

mysql> SHOW PROCEDURE STATUS LIKE 'var%'\G

*************************** 1. row ***************************

Db: testdb

Name: var

Type: PROCEDURE

Definer: root@localhost

Modified: 2015-11-17 22:40:58

Created: 2015-11-17 22:40:58

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

除了SHOW STATUS之外，MySQL还可以使用SHOW CREATE语句查看存储过程和函数的状态。

SHOW CREATE {PROCEDURE | FUNCTION} name

这个语句时一个MySQL的扩展，类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。SHOW CREATE语句示例，代码如下：

mysql> show create procedure var \G

*************************** 1. row ***************************

Procedure: var

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `var`()

begin

DECLARE var INT;

select count(*) into var from testdb.info;

end

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语句形式如下：

select * from information_schema.Routines where ROUTINE_NAME='sp_name'\G

其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name参数表示存储过程或函数的名称。从Routines表中查询名称为var的存储函数的信息，代码如下：

mysql> select * from information_schema.Routines where ROUTINE_NAME='var' AND ROUTINE_TYPE='PROCEDURE'\G

*************************** 1. row ***************************

SPECIFIC_NAME: var

ROUTINE_CATALOG: def

ROUTINE_SCHEMA: testdb

ROUTINE_NAME: var

ROUTINE_TYPE: PROCEDURE

DATA_TYPE:

CHARACTER_MAXIMUM_LENGTH: NULL

CHARACTER_OCTET_LENGTH: NULL

NUMERIC_PRECISION: NULL

NUMERIC_SCALE: NULL

DATETIME_PRECISION: NULL

CHARACTER_SET_NAME: NULL

COLLATION_NAME: NULL

DTD_IDENTIFIER: NULL

ROUTINE_BODY: SQL

ROUTINE_DEFINITION: begin

DECLARE var INT;

select count(*) into var from testdb.info;

end

EXTERNAL_NAME: NULL

EXTERNAL_LANGUAGE: NULL

PARAMETER_STYLE: SQL

IS_DETERMINISTIC: NO

SQL_DATA_ACCESS: MODIFIES SQL DATA

SQL_PATH: NULL

SECURITY_TYPE: INVOKER

CREATED: 2015-11-17 22:40:58

LAST_ALTERED: 2015-11-18 16:42:48

SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

ROUTINE_COMMENT:

DEFINER: root@localhost

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

在information_schema数据库下的Routines表中，存储所有存储过程和函数的定义。使用SELECT语句查询Routines表中的存储过程和函数的定义时，一定要使用ROUTINE_NAME字段指定存储过程或函数的名称。否则，将查询出所有的存储过程或函数的定义。如果有存储过程和存储函数名称相同，则需要同时指定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。

修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性，语法如下：

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic…]

其中，sp_name参数表示存储过程或函数的名称，characteristic参数指定存储函数的特性，可能的取值有：

CONTAINS SQL – 表示子程序包含SQL语句，但不包含读或写数据的语句。

NO SQL – 表示子程序中不包含SQL语句。

READS SQL DATA – 表示子程序中包含读数据的语句。

MODIFIES SQL DATA – 表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER } – 指明谁有权限来执行。

DEFINER – 表示只有定义者自己才能够执行。

INVOKER – 表示调用者可以执行。

COMMENT ‘string’ – 表示注释信息。

下面修改存储过程

mysql> delimiter $

mysql> ALTER PROCEDURE countproc

-> MODIFIES SQL DATA

-> SQL SECURITY INVOKER

-> $

mysql> delimiter ;

存储过程中的代码可以修改吗？MySQL目前还不支持对已存在的存储过程代码的更改。如果必须要更改存储过程，必须使用DROP语句删除之后，再重新写代码。

转自：http://www.ywnds.com/?p=3877