1、存储过程和函数存储过程和函数2011年05月26 日概念：存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。存储过程和函数的区别：函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL,那么就可能需要将函数改造成存储过程。 意义：调用存储过程和函数可以减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 操作：首先确认权限，如有权限，则可进行响应操作。 Example 1 : 创建新的过程 mysql select * from emp; /显示表emp
2、+-+-+-+-+ | ename | hiredate | sal | deptno | +-+-+-+-+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 8000.00 | 2 | | bjguan | 0000-00-00 | 5000.00 | 1 | | wwj | 2003-08-06 | 3000.00 | 4 | | monica | 2006-03-02 | NULL | 3 | +-+-+-+-+ 5 rows in set (0.01 sec) mysql select hiredate - from e
3、mp - where deptno /手工执行条件选择结果显示，从表emp中选择符合deptno2500的hiredate列 +-+ | hiredate | +-+ | 2003-02-01 | | 0000-00-00 | +-+ 2 rows in set (0.00 sec) mysql delimiter * /通常在执行创建过程和函数之前，会通过“DELIMITER ?”命令将语句的结束符从“;”变成其他符号，这里设定的是*，创建完毕后会通过“DELIMITER ;”命令再将结束符改回成“;” mysql create procedure ch_hiredate_pro(in p_
4、deptno int(2),in p_sal decimal(10,2),out p_count int) /创建一个存储过程ch_hiredate_pro，3个参数分别是IN类型的p_deptno，数据类型int(2). - reads sql data /表示子程序包含读数据的语句，这个 特征值 提供子程序使用数据的内在信息，目前只提供给服务器，并没有根据这些 特征值 来约束过程实际使用数据的情况，如果没有明确给定，默认使用的是CONTAINS SQL（表示子程序不包含读或写数据的语句），此外还有NO SQL(表示子程序不包含SQL语句) - begin /过程开始 - select hi
5、redate - from emp - where deptno and salp_sal; - - select found_rows() into p_count; /将函数found_rows()的结果即满足要求的记录数赋值给P_COUNT - end * /过程结束 Query OK, 0 rows affected (0.00 sec) mysql delimiter ; /再将语句结束符改回; mysql call ch_hiredate_pro (4,2500,c); /调用过程，参数分别是4，2500，c +-+ | hiredate | +-+ | 2003-02-01 |
6、| 0000-00-00 | +-+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql select c; +-+ | c | +-+ | 2 | +-+ 1 row in set (0.00 sec) SQL SECURITYDEFINER | INVOKER : 用来指定子程序该用创建子程序者的权限来执行，还是使用调用者的权限来执行，默认值是DEFINER。示例如下： mysql delimiter $ mysql mysql create procedure ch_hiredate_pro_invoke
7、r(in p_deptno int(2),in p_sal decimal(10,2),out p_count int) - sql security invoker /指定过程ch_hiredate_pro_invoker是以调用者的权限执行的 - begin - select hiredate - from emp - where deptno and salp_sal; - - select found_rows() into p_count; - end $ Query OK, 0 rows affected (0.01 sec) mysql delimiter ; mysql mys
8、ql grant execute on mysql.* to lisalocalhost; /赋予用户lisa执行存储过程的权限，但不能查询emp表 Query OK, 0 rows affected (0.00 sec) mysql exit; Bye rootlocalhost # mysql -ulisa Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 to server version: 5.0.45 Type help; or h for help. Type
9、c to clear the buffer. mysql use mysql; Database changed mysql call ch_hiredate_pro(4,2500,c); /上一个示例中创建的过程ch_hiredate_pro,SQL SECURITY的 特征值 默认为DEFINER，即以创建者的权限来执行 +-+ | hiredate | +-+ | 2003-02-01 | | 0000-00-00 | +-+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql call ch_hiredate_pro_invoker(4,2500,c); /过程ch_hiredate_pro,SQL SECURITY的 特征值 为INVOKER，即以调用者的权限来执行，但用户lisa没有SELECT的权限 ERROR 1142 (42000): SELECT command denied to user lisalocalhost for table emp
