Mysql应用理解MySQL存储过程和函数
《Mysql应用理解MySQL存储过程和函数》要点: 一、概述? MYSQL数据库 一提到存储过程可能就会引出另一个话题就是存储过程的优缺点,这里也不做讨论,一般别人问我我就这样回答你觉得它好你就用它.因为mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值(return),并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型.MYSQL数据库 二、语法? MYSQL数据库 ?创建存储过程和函数语法MYSQL数据库 CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement or statements 语法来自官方自带的参考手册,characteristic语法块是需要注意的地方,先用一个例子来介绍.MYSQL数据库 例子:MYSQL数据库 #创建数据库 DROP DATABASE IF EXISTS Dpro; CREATE DATABASE Dpro CHARACTER SET utf8 ; USE Dpro; #创建部门表 DROP TABLE IF EXISTS Employee; CREATE TABLE Employee (id INT NOT NULL PRIMARY KEY COMMENT '主键',name VARCHAR(20) NOT NULL COMMENT '人名',depid INT NOT NULL COMMENT '部门id' ); #插入测试数据 INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',(4,'李',102),(5,'郭',103); #创建存储过程 DROP PROCEDURE IF EXISTS Pro_Employee; DELIMITER $$ CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT ) READS SQL DATA SQL SECURITY INVOKER BEGIN SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid; END$$ DELIMITER ; #执行存储过程 CALL Pro_Employee(101,@pcount); SELECT @pcount;
语法解释:MYSQL数据库 在创建存储过程的时候一般都会用DELIMITER$$.....END$$ DELIMITER ;放在开头和结束,目的就是避免mysql把存储过程内部的";"解释成结束符号,最后通过“DELIMITER ;”来告知存储过程结束.MYSQL数据库 主要解释characteristic部分:MYSQL数据库 LANGUAGE SQL:用来说明语句部分是SQL语句,未来可能会支持其它类型的语句.MYSQL数据库 [NOT] DETERMINISTIC:如果程序或线程总是对同样的输入参数产生同样的结果,则被认为它是“确定的”,否则就是“非确定”的.如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC,默认的就是NOT DETERMINISTIC(非确定的)CONTAINS SQL:表示子程序不包括读或写数据的语句.MYSQL数据库 NO SQL:表示子程序不包括SQL语句.MYSQL数据库 READS SQL DATA:表示子程序包括读数据的语句,但不包括写数据的语句.MYSQL数据库 MODIFIES SQL DATA:表示子程序包括写数据的语句.MYSQL数据库 SQL SECURITY DEFINER:表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行.MYSQL数据库 SQL SECURITY INVOKER:表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行.(例如上面的存储过程我写的是由调用该存储过程的用户的权限来执行,当前存储过程是用来查询Employee表,如果我当前执行存储过程的用户没有查询Employee表的权限那么就会返回权限不足的错误,如果换成DEFINER如果存储过程是由ROOT用户创建那么任何一个用户登入调用存储过程都可以执行,因为执行存储过程的权限变成了root)MYSQL数据库 COMMENT 'string':备注,和创建表的字段备注一样.MYSQL数据库 注意:在编写存储过程和函数时建议明确指定上面characteristic部分的状态,特别是存在复制的环境中,如果创建函数不明确指定这些状态会报错,从一个非复制环境将带函数的数据库迁移到复制环境的机器上如果没有明确指定DETERMINISTIC,NO SQL,or READS SQL DATA该三个状态也会报错.MYSQL数据库 报错示例MYSQL数据库 Error Code: 1418. This function has none of DETERMINISTIC,or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 解决方法有两种:MYSQL数据库 1.将log_bin_trust_function_creators参数设置为ON,这样一来开启了log-bin的MySQL Server便可以随意创建function.这里存在潜在的数据平安问题,除非明确的知道创建的function在master和slave上的行为完全一致. mysql> show variables like 'log_bin_trust_function_creators'; mysql> set global log_bin_trust_function_creators=1; ? 另外如果是在master上创建函数,想通过主从复制的方式将函数复制到slave上则也需在开启了log-bin的slave中设置上述变量的值为ON(变量的设置不会从master复制到slave上,这点需要注意),否则主从复制会报错.MYSQL数据库 2.明确指明函数的类型 修改存储过程函数语法MYSQL数据库 ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic: { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' 删除存储过程函数语法MYSQL数据库 DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name 查看存储过程和函数MYSQL数据库 1.查看存储过程状态MYSQL数据库 SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern'] show procedure status like 'Pro_Employee' G MYSQL数据库 2.查看存储过程和函数的创建语法MYSQL数据库 SHOW CREATE {PROCEDURE | FUNCTION} sp_name SHOW CREATE PROCEDURE Pro_Employee G; MYSQL数据库 3.查看存储过程和函数详细信息MYSQL数据库 代码如下: SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME='Pro_Employee' G;MYSQL数据库 总结? MYSQL数据库 ?存储过程和函数语法不难理解,但是往往存储过程中不单单只包括这种简单的查询语法,还会嵌套循环语句、变量、报错处理、事务等,下一篇文章会单独讲变量,将变量的知识加入到存储过程,包括变量的声明和报错处理,欢迎关注.MYSQL数据库 欢迎参与《Mysql应用理解MySQL存储过程和函数》讨论,分享您的想法,编程之家PHP学院为您提供专业教程。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |