MySQL的存储过程（stored procedure）和函数（stored function）统称为stored routines。

1. MySQL存储过程和函数的区别

函数只能通过return语句返回单个值或者表对象。而存储过程不允许执行return，但是通过out参数返回多个值。函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行。

函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少

一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。

Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

2.创建存储过程和函数语法

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语法块是需要注意的地方，先用一个例子来介绍。

3.实例

#创建数据库
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,'张',101),(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;

在存储过程中使用循环、变量

写一个存储过程，能够给TStudent表插入指定数量的学生记录。身份证号随机产生，姓名随机产生，性别随机，班级随机产生。这其中用到了随机函数，以及上面创建的产生姓名的函数。生日有随机函数产生，范围在1980-1989年，用户的邮箱由用户的姓名首写字母组合而成。

如果已有存储过程，必须先删除

drop procedure addStudent

创建的存储过程

create procedure addStudent(in num int)

begin

declare i int;

set i=1;

delete from TStudent;

while num>=i do

insert TStudent values (

LPAD(convert(i,char(5)),5,'0'),

create_name(),

if(ceil(rand()*10)%2=0,'男','女'),

RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,'0'),

Concat('198',convert(ceil(rand()*10),char(1)),'-',LPAD(convert(ceil(rand()*12),char(2)),2,'0'),'-',LPAD(convert(ceil(rand()*30),char(2)),2,'0')),

Concat(PINYIN(sname),'@hotmail.com'),

case ceil(rand()*3) when 1 then '网络与网站开发' when 2 then 'JAVA' ELSE 'NET' END,

NOW());

set i=i+1;

end while;

select * from TStudent;

end

调用存储过程

call addStudent(100)

创建使用case的函数

该函数根据学生的分数，给出评价。

create FUNCTION getGrad3(score int)

RETURNS varchar(50)

BEGIN

declare grad varchar(50);

declare mark int;

set mark=ceil(score/10);

case mark

when 9 then set grad='成绩优秀';

when 8 then set grad='成绩良好';

when 7 then set grad='成绩一般';

else set grad='刚刚及格';

end case;

return grad;

END

测试函数

select b.sname 姓名,mark 分数,getGrad3(mark) 成绩级别

from `TScore` a join `TStudent` b on a.`StudentID`=b.`StudentID` limit 5

数字转汉字

以下函数能够把阿拉伯数字转化成财务中用到的汉字

create FUNCTION tohanzi (n_LowerMoney DECIMAL)

RETURNS VARCHAR(120)

BEGIN

Declare v_LowerStr VARCHAR(200) ;

Declare v_UpperPart VARCHAR(200) ;

Declare v_UpperStr VARCHAR(200) ;

Declare i_I int ;

set v_LowerStr = LTRIM(RTRIM(ROUND(n_LowerMoney,2 ) ) ) ;

set i_I = 1 ;

set v_UpperStr = '' ;

while ( i_I <=char_length(v_LowerStr ) ) do

set v_UpperPart = CONCAT( case substring(v_LowerStr,char_length(v_LowerStr) - i_I + 1,1 )

WHEN '.' THEN '元'

WHEN '0' THEN '零'

WHEN '1' THEN '壹'

WHEN '2' THEN '贰'

WHEN '3' THEN '叁'

WHEN '4' THEN '肆'

WHEN '5' THEN '伍'

WHEN '6' THEN '陆'

WHEN '7' THEN '柒'

WHEN '8' THEN '捌'

WHEN '9' THEN '玖'

END,

case i_I

WHEN 1 THEN '分'

WHEN 2 THEN '角'

WHEN 3 THEN ''

WHEN 4 THEN ''

WHEN 5 THEN '拾'

WHEN 6 THEN '佰'

WHEN 7 THEN '仟'

WHEN 8 THEN '万'

WHEN 9 THEN '拾'

WHEN 10 THEN '佰'

WHEN 11 THEN '仟'

WHEN 12 THEN '亿'

WHEN 13 THEN '拾'

WHEN 14 THEN '佰'

WHEN 15 THEN '仟'

WHEN 16 THEN '万'

ELSE ''

END );

set v_UpperStr =CONCAT( v_UpperPart , v_UpperStr) ;

set i_I = i_I + 1 ;

end while;

set v_UpperStr = REPLACE(v_UpperStr,'零拾','零') ;

set v_UpperStr = REPLACE(v_UpperStr,'零佰','零') ;

set v_UpperStr = REPLACE(v_UpperStr,'零仟','零') ;

set v_UpperStr = REPLACE(v_UpperStr,'零零零','零') ;

set v_UpperStr = REPLACE(v_UpperStr,'零零','零') ;

set v_UpperStr = REPLACE(v_UpperStr,'零角零分','整') ;

set v_UpperStr = REPLACE(v_UpperStr,'零分','整') ;

set v_UpperStr = REPLACE(v_UpperStr,'零角','零') ;

set v_UpperStr = REPLACE(v_UpperStr,'零亿零万零元','亿元') ;

set v_UpperStr = REPLACE(v_UpperStr,'亿零万零元','亿元') ;

set v_UpperStr = REPLACE(v_UpperStr,'零亿零万','亿') ;

set v_UpperStr = REPLACE(v_UpperStr,'零万零元','万元') ;

set v_UpperStr = REPLACE(v_UpperStr,'万零元','万元') ;

set v_UpperStr = REPLACE(v_UpperStr,'零亿','亿') ;

set v_UpperStr = REPLACE(v_UpperStr,'零万','万') ;

set v_UpperStr = REPLACE(v_UpperStr,'零元','元') ;

set v_UpperStr = REPLACE(v_UpperStr,'零零','零') ;

if ( '元' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1));

end if;

if ( '零' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ( '角' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ( '分' = substring(v_UpperStr,1,1)) then

set v_UpperStr = substring(v_UpperStr,2,(char_length(v_UpperStr) - 1)) ;

end if;

if ('整' = substring(v_UpperStr,1,1)) then

set v_UpperStr = '零元整' ;

end if;

return v_UpperStr;

END

select tohanzi(20321)

随机产生姓名的函数

该函数，使用三个字符串，存放用户的姓名，使用随机函数从姓名中随机排列组合成人名。

create function create_name()

RETURNS varchar(3)

begin

DECLARE LN VARCHAR(300);

DECLARE MN VARCHAR(200);

DECLARE FN VARCHAR(200);

DECLARE LN_N INT;

DECLARE MN_N INT;

DECLARE FN_N INT;

SET LN='李王张刘陈杨黄赵周吴徐孙朱马胡郭林何高梁郑罗宋谢唐韩曹许邓萧冯曾程蔡彭潘袁于董余苏叶吕魏蒋田杜丁沈姜范江傅钟卢汪戴崔任陆廖姚方金邱夏谭韦贾邹石熊孟秦阎薛侯雷白龙段郝孔邵史毛常万顾赖武康贺严尹钱施牛洪龚';

SET MN='伟刚勇春菊毅俊峰强军平保东文辉力明永健世广志瑗琰韵融园艺咏卿聪澜纯毓悦昭冰爽琬茗羽希宁欣飘育滢馥新利筠柔竹霭凝晓欢霄枫芸菲寒伊亚宜可姬舒义兴良海山仁波宁贵福生龙元全国胜学祥亮政谦亨奇固之岚苑富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪清飞彬娜静淑惠珠翠雅芝妍茜秋珊莎锦黛青倩婷姣婉娴瑾颖露瑶怡婵雁蓓纨仪荷丹蓉眉君琴蕊薇菁梦素伟刚勇毅俊峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘';

SET FN='伟刚勇毅俊云莲真环雪荣爱妹霞香月莺媛艳瑞凡佳嘉琼勤珍贞莉桂娣叶璧才发武丽琳轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德河哲江超浩璐娅琦晶裕华慧巧美婕馨影荔枝思心邦承乐绍功松善厚庆磊民友玉萍红娥玲芬芳燕彩兰凤洁梅秀娟英行时泰盛雄琛钧冠策腾楠榕风航弘峰强军平保东文辉力明永健世广志义兴良海山仁波宁贵福生龙元全国胜学祥才发武新利清飞彬富顺信子杰涛昌成康星光天达安岩中茂进林有坚和彪博诚先敬震振壮会思群豪心邦承乐绍功松善厚庆磊民友裕河哲江超浩亮政谦亨奇固之轮翰朗伯宏言若鸣朋斌梁栋维启克伦翔旭鹏泽晨辰士以建家致树炎德行时泰盛雄琛钧冠策腾楠榕风航弘';

SET LN_N=CHAR_LENGTH(LN);

SET MN_N=CHAR_LENGTH(MN);

SET FN_N=CHAR_LENGTH(FN);

return Concat(substring(LN,ceil(rand()*LN_N),1),substring(MN,ceil(rand()*MN_N),1),substring(FN,ceil(rand()*FN_N),1));

end

调用函数产生姓名

select create_name() 随机姓名 union

秒客网

MySQL 存储过程和函数

1. MySQL存储过程和函数的区别

2.创建存储过程和函数语法

3.实例

相关文章