存储过程和函数,存储过程

函数定义:是一组预先定义好的SQL语句集合,通晓为批管理语句。
①提升代码的重用性
②简化操作
③减去编译次数和压缩和数据库的连接次数,升高功效
④安全性调高
函数和存款和储蓄进度的区分
函数:有且唯有一个重临值,相符营为数据管理后的三个标记。
仓库储存进程:重回值多样(可有可无)做批量陈设和批量翻新。
一、语法定义
Create function 函数名(参数列表) returns 重返类型
Begin
函数体;
End
注意:
①参数列表:函数名 参数类型
②函数体:必需含有return 语句,return的岗位随便,然而提出坐落最终。
③函数体之后一句,能够简轻易单begin—end语句
④在意函数体内要求用”;”结尾,结合delimiter使用
二、调用方式
Select 函数名(参数列表)
留心:参数列表结合变量的运用
区分存款和储蓄进程是使用call来调用的
三、查看函数和删除函数
1.翻看函数
Show create function 函数名;
2.去除函数
Drop function 函数名;
四、例如表明
1.不含参数
归来职员和工人个数count(*)
星彩彩票app下载 1
星彩彩票app下载 2

一、代码推行结构

  • 次第结果
  • 支行协会
  • 巡回结构

1.分支结构

分层组织:实现七个代码块,依据标准选拔性实践某段代码
在MySQL中只有if分支

着力语法:

if 条件判别 then
-- 满足条件要推行的代码
else
-- 不滿足条件要进行的代码
end if;

触发器结合if分支

星彩彩票app下载 3

触发器结合if分支

星彩彩票app下载 4

影响

2.循环布局

星彩彩票app下载,巡回结构:某段代码在钦点条件下施行重复。

while循环

主导语法

while 条件判别 do
-- 满足条件要实践的代码
-- 更换循环条件
end while;

(1)巡回调控

在循环之中开展巡回推断和垄断(monopoly)

iterate:迭代,类似continue,后面代码不实行,循环重复来过
leave:离开,类似break,整个循环接收

动用办法:
iterate/leave 循环名字;

概念循环名字
巡回名字:while 条件 do
-- 循环体
-- 循环调控
leave/iterate 循环名字;
end while;

积累进程和积攒函数

至于MySQL的存款和储蓄函数(自定义函数)的概念和行使办法详解,mysql详解

存款和储蓄函数

怎么是积累函数: 封装一段sql代码,完毕一种特定的效应,再次回到结果。

积累函数的语法:

create function 函数([函数参数[,….]]) Returns 返回类型

  Begin

    If(

      Return (返回的数据)

    Else 

      Return (返回的数据)

    end if;

  end;

例如: create function count_news(hits int) returns int

与存款和储蓄进程重临参数分裂的是积存函数在概念时没用直白评释哪个变量是再次回到参数,而只是利用了returns注脚了回来参数所属的数据类型,重回参数是在函数体中动用return再次回到要回来的数码变量的花样来代表的。这就须要注意的是:
存款和储蓄函数只协理输入参数,何况输入参数前未有IN或INOUT.

仓库储存函数中的限制

流动调查控(Flow-of-control)语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是法定的.

变量注明(DECLARE)以至派出(SET)是法定的.

允许规范注解.

老大管理注脚也是同意的.

唯独在那要记住函数有受限条件:不可能在函数中造访表.由此在函数中使用以下语句是不合规的。

ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE

存储函数与存款和储蓄进度的区分

一、 存储函数有且唯有贰个重临值,而存款和储蓄进程无法有重临值。

二、 函数只好有输入参数,而且不可能带in, 而存款和储蓄进程可以有多少个in,out,inout参数。

三、 存款和储蓄进度中的语句功效更加强有力,存款和储蓄进程能够达成很复杂的事情逻辑,而函数有无数限制,如无法在函数中运用insert,update,delete,create等话语;存款和储蓄函数只实现查询的干活,可接受输入参数并重返三个结果,也正是函数达成的功力针对性比较强。

四、 存款和储蓄进度能够调用存款和储蓄函数。但函数不能调用存款和储蓄过程。

五、 存款和储蓄过程相似是作为一个独门的有个别来举行(call调用)。而函数能够作为查询语句的三个某些来调用。

实例1:

Id

Name 

QQ

phone

秦云 

10102800

13500000

在路上

10378

13600000

LEO

10000

  13900000

Id

Name 

上机时间

管理员

1

秦云 

2004-1-1 

李大伟

2

秦云

2005-1-1

马化腾

在路上 

2005-1-1 

马化腾

秦云

2005-1-1

李大伟

5

在路上

2005-1-1

李大伟

落实指标:

从表1中取全体人士列表,从表第22中学取上机次数和助理馆员.

上机人士名单    上机次数             助理馆员
秦云               3             李大伟,马化腾,李大伟
在路上           2            马化腾,李大伟
LEO              0     

存款和储蓄函数 什么是累积函数: 封装一段sql代码,完毕一种特定的机能,重返...

2.带有参数的
依据顾客名再次来到salary薪水
星彩彩票app下载 5
星彩彩票app下载 6

二、函数

函数:将一段代码块封装到二个结构中,在要求施行代码块的时候,调用结果实践就可以。(代码复用)

  • 系统函数
    系统定义好的函数,直接调用就能够。
    其余函数都有再次回到值,因而函数的调用是透过select调用。

MySQL字符串的基本操作单位

substring:字符串截取(字符为单位)

星彩彩票app下载 7

字符串截取

char_length:字符长度
length:字节长度

星彩彩票app下载 8

字符,字节长度

instr:判定字符串是或不是在有个别具体的字符串中留存,存在再次回到地方

星彩彩票app下载 9

字符串寻觅

lpad:左填充,将字符串根据有个别内定的填充形式,填充到内定长度(字符单位)

星彩彩票app下载 10

字符串填充

insert:替换,找到对象地点,钦赐长度的字符串,替换到指标字符串

星彩彩票app下载 11

字符串替换

strcmp:compere,字符串相比较

星彩彩票app下载 12

字符串比较

  • 自定义函数

函数要素:函数名,参数列表(形参和实参),重临值,函数体(成效域)

(1)创设函数

创建语法
create function 函数名([形参列表])returns 数据类型 -- 规定要重临的数据类型
begin
-- 函数体

-- 再次来到值:return 类型(钦赐数据类型);
end

星彩彩票app下载 13

创建函数

自定义函数和种类函数调用格局是毫无二致的:select 函数名([实参列表])

星彩彩票app下载 14

调用函数

(2)查看函数

翻看全部函数:
show function status;
查看函数的创导语句:
show create function 函数名;

星彩彩票app下载 15

翻看全数函数

星彩彩票app下载 16

查看函数的成立语句

(3)修改函数&&删除函数

函数只可以先删除后新扩张,不能修改

drop function 函数名;

星彩彩票app下载 17

去除函数

函数参数
参数分为两种

  • 形参:定义时的参数
  • 实参:调用时的参数(实参能够是数值也能够是变量)

非得钦定数据类型
function 函数名(形参名 字段类型) returns 数据类型

举个栗子

星彩彩票app下载 18

函数运用

星彩彩票app下载 19

函数查证

作用域

MySQL中全局变量能够在其他地方使用;局地变量只可以在函数内部选拔

全局变量:使用set关键字定义,使用@符号标识
一些变量:使用declare关键字注明,未有@符号。全数的部分变量的注脚,必需在函数体起头在此以前

星彩彩票app下载 20

局地变量的施用

MySQL的蕴藏进度(stored procedure)和函数(stored function)统称为stored routines。

3.传开七个float值,重返两个之和
星彩彩票app下载 21

三、存款和储蓄进度(procedure)

仓库储存进度是一种未有重临值的函数,是一种用来管理数量的措施。

(1)制程

create procedure 过程名([参数列表])
begin
-- 过程体
end

星彩彩票app下载 22

创造进度

(2)查看进度

函数的查阅格局完全适用于经过:关键字换到procedure

查阅全部进度
show procedure status;
或者
show procedure status [like 'pattern'];

星彩彩票app下载 23

翻开所以经过

查阅进度创立语句
show create procedure 过程名;

星彩彩票app下载 24

翻开进程创制语句

(3)调用进度

进度并未有重返值:select是不可能访问的

调用进程关键字:call

星彩彩票app下载 25

调用进度

(4)修改进程&&删除进度

进度不可能改改,只好先删除后新增加

删除进程
drop procedure 过程名;

星彩彩票app下载 26

剔除进度

(5)进程参数

函数的参数须求数据类型钦赐,进程供给类型限定:三种档案的次序

  • in:数据只是从表面传入给个中使用(值传递)。能够是数值也能够是变量
  • out:只同意进程之中选用(不用外界数据),给外界使用的(援用传递:外界的多少会被先清空才回步向到内部),只可以是变量
  • inout:外界能够在其间使用,内部修改也足以给外界使用,规范的引用传递:只好传变量

基本采纳
create procedure 进程名(in 形参名 数据类型,out 形参名 数据类型,inout 形参名 数据类型)

星彩彩票app下载 27

经过参数

调用:out和inout类型的参数必需传入变量,无法是变量。

星彩彩票app下载 28

调用

积存进程对于变量的操作是后退的:是在仓库储存进度调用停止的时候,才会重新将里面的值赋给外界传入全局变量

星彩彩票app下载 29

经过参数

星彩彩票app下载 30

调试

终极,在蕴藏进程调用停止今后,系统会将部分变量重复重返给全局变量(out和inout)

星彩彩票app下载 31

验证

 

  1. MySQL存款和储蓄进程和函数的区分

函数只可以通过return语句再次回到单个值可能表对象。而存款和储蓄进度不容许实施return,可是经过out参数再次来到四个值。 

函数是能够放手在sql中运用的,能够在select中调用,而存款和储蓄进度丰裕。

函数限制很多,举例无法用不经常表,只可以用表变量.还或然有点函数都不可用等等.而存款和储蓄进度的限制相对就少之甚少。

日常的话,存款和储蓄进程完毕的机能要复杂一点,而函数的完毕的效率针对性相比较强。

当存款和储蓄进度和函数被推行的时候,SQL Manager会到procedure cache中去取相应的询问语句,如果在procedure cache里没有对号入座的询问语句,SQL Manager就能对存款和储蓄进程和函数进行编写翻译。

Procedure cache中保存的是实施布置 (execution plan) ,当编写翻译好之后就实践procedure cache中的execution plan,之后SQL SELANDVEEvoque会依照各类execution plan的其真实情情形来思量是或不是要在cache中保留那些plan,评判的正儿八经八个是以此execution plan大概被选用的功能;其次是生成那么些plan的代价,也正是编写翻译的耗费时间。保存在cache中的plan在后一次奉行时就无须再编写翻译了。

积攒进程选用实例

  1. 创设未有参数的积攒进度

创造存款和储蓄进程获得最高等学校统招考试分

create PROCEDURE getMaxMark()
begin
select max(mark) 最高分 from `TScore`;
end
-- 调用存储过程
call getMaxMark();
  1. 制造带输入参数的存款和储蓄进程

以下存款和储蓄进程可以输入的学生号查出该学生的音信参数Sid代表学号,IN代表输入参数。

CREATE PROCEDURE getStudentByID(IN sid varchar(15))
BEGIN
select * from `TStudent` where studentID=sid;
END
-- 调用该函数
call getStudentByID('00009')
  1. 始建带输入和输出参数的蕴藏进度

创设存款和储蓄进程,能够出口钦定课程的参天分

create PROCEDURE getMaxMarkBySubject(IN subName varchar(30),OUT maxMark int)
begin
select MAX(mark) into maxMark from `TScore` a join `TSubject` b on a.`subJectID`=b.`subJectID`
where b.`subJectName`=subName;
end
-- 调用存储过程,将取出的最大值放到变量@maxScore
CALL getMaxMarkBySubject('计算机网络',@maxScore);
select @maxScore 计算机网络最高分;
CALL getMaxMarkBySubject('数据结构',@maxScore);
select @maxScore 数据结构最高分;
  1. 寻思:成立存款和储蓄进度,能够基于内定学号删除学生记录

  2. 删去存款和储蓄进程

    drop PROCEDURE getMaxMarkBySubject

  3. 翻看创造的囤积进程

  4. 依附学生成绩判定是或不是精粹

以下函数能够依据输入值范围输出成绩是不是优良差。

create FUNCTION getGrad1(score int)
RETURNS varchar(50)
BEGIN
return IF(score>90,'成绩优秀',IF(score<90 and score>80,'成绩良好',IF(score<80 and score>70,'成绩一般',IF(score<70 and score>60,'成绩及格','不及格'))));
END
-- 在查询中使用定义的函数
select b.sname 姓名,mark 分数,getGrad1(mark) 成绩级别 from `TScore` a join `TStudent` b on a.`StudentID`=b.`StudentID`
  1. 取汉字拼音首字母的函数

先创立猎取汉字拼音函数要求动用的表

DROP TABLE IF EXISTS `pinyin`;

CREATE TABLE `pinyin` (

`letter` char(1) NOT NULL,

`chinese` char(1) NOT NULL,

PRIMARY KEY (`letter`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

INSERT INTO `pinyin` VALUES ('A','驁'),('B','簿'),('C','錯'),('D','鵽'),('E','樲'),('F','鰒'),('G','腂'),

('H','夻'),('J','攈'),('K','穒'),('L','鱳'),('M','旀'),('N','桛'),('O','漚'),('P','曝'),('Q','囕'),('R','鶸'),

('S','蜶'),('T','籜'),('W','鶩'),('X','鑂'),('Y','韻'),('Z','咗');

创设取得汉字拼音的函数

DROP FUNCTION IF EXISTS `PINYIN`

CREATE FUNCTION PINYIN(str CHAR(255))

RETURNS char(255)

BEGIN

DECLARE hexCode char(4);

DECLARE pinyin varchar(255);

DECLARE firstChar char(1);

DECLARE aChar char(1);

DECLARE pos int;

DECLARE strLength int;

SET pinyin = '';

SET strLength = CHAR_LENGTH(LTRIM(RTRIM(str)));

SET pos = 1;

SET @str = (CONVERT(str USING gbk));

WHILE pos <= strLength DO

SET @aChar = SUBSTRING(@str,pos,1);

SET hexCode = HEX(@aChar);

IF hexCode >= "8140" AND hexCode <= "FEA0" THEN

SELECT letter into firstChar

FROM pinyin

WHERE chinese >= @aChar

LIMIT 1;

ELSE

SET firstChar = @aChar;

END IF;

SET pinyin = CONCAT(pinyin,firstChar);

SET pos = pos   1;

END WHILE;

RETURN UPPER(pinyin);

END

-- 使用函数获取用户的姓名拼音首写字母

select sname 姓名,pinyin(sname) 拼音首字母 from `TStudent`
  1. 想想:改造顾客邮箱,将客商的邮箱地址设置姓名的拼音缩写

  2. 数字转汉字

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

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)
  1. 随机发生姓名的函数

该函数,使用七个字符串,贮存客户的人名,使用随机函数从姓名中随机排列组合中年人名。

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

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名 union

select create_name() 随机姓名
  1. 翻开创设的函数

  2. 删去存款和储蓄的函数

    drop FUNCTION tohanzi

在存款和储蓄进度和函数中采纳变量、决断和巡回

MySQL中变量、判别和循环只好在蕴藏进程和积存函数中采纳。

  1. 在仓库储存进度中动用循环、变量

写多个存款和储蓄进程,能够给TStudent表插入钦赐数量的学生记录。身份ID号随机爆发,姓名随机发生,性别随机,班级随机发生。那其间使用了随便函数,以致地点创造的发生姓名的函数。破壳日有自由函数发生,范围在一九七八-1986年,顾客的邮箱由客户的全名首写字母组合而成。

借使已有囤积进度,必得先删除

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)
  1. 创办使用while的囤积进度插入学生成绩

安插了100名上学的小孩子后,实践以下命令。

创办存款和储蓄进程,能够插入为学员插入分数。存款和储蓄进度选取三个巡回,分数在50-100分之间,使用随机数落成。

drop procedure fillSore

开创存款和储蓄进程

create procedure fillSore()

begin

DECLARE St_Num INT;

DECLARE Sb_Num INT;

DECLARE i1 INT default 1;

DECLARE i2 INT default 1;

delete from TScore;

select count(*) into St_Num from TStudent;

select count(*) into Sb_Num from TSubject;

while St_Num>=i1 do

set i2=1;

while Sb_Num>=i2 do

insert TScore values

(LPAD(convert(i1,char(5)),5,'0'),LPAD(convert(i2,char(4)),4,'0'),ceil(50 rand()*50));

set i2=i2 1;

END WHILE;

set i1=i1 1;

END WHILE;

End

-- 调用存储过程

call fillSore()

-- 查询

select * from TScore
  1. 创办使用if的函数

If函数扶植多层嵌套

create FUNCTION getGrad2(score int)

RETURNS varchar(50)

BEGIN

declare grad varchar(50);

if score>90 then

set grad='成绩优秀';

else if score>80 then

set grad='成绩良好';

else if score>70 then

set grad='成绩一般';

else set grad='刚刚及格';

end if;

end if;

end if;

return grad;

END

-- 使用函数查询数据库

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

from `TScore` a join `TStudent` b on a.`StudentID`=b.`StudentID` limit 5
  1. 创造使用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

查看存款和储蓄进度和仓库储存函数的语句

运转一下下令能够查阅创建fillSore存款和储蓄进度语句

  1. 动用show create查看存款和储蓄进程内容

    show create procedure fillSore

  2. 应用管理工科具产生查看创立存款和储蓄进程的言语

作业:

  1. 总结男子和女子人数

  2. 总计各班人数

  3. 把重姓的学生搜索来

  4. 找寻身份ID号末尾是偶数的上学的儿童。

  5. 查询出生年月在1983-01-00到一九九零-01-00里面的学员。

  6. 总括各班“Computer网络”平均分

  7. 寻找“计算机互连网”不如格的男同学

 

 

MySQL之自定义函数

引言

  MySQL本身提供了安置函数,这一个函数的留存给我们日常的费用和数目操作带来了一点都不小的惠及,比方作者日前提到过的聚合函数SUM()、AVG()以致日期时间函数等等,但是我们总会出现任何的要求:大家须求调用贰个函数,那个函数供给根据大家的须求来贯彻我们友好的成效,可是那些函数式系统无法提供的因为那中必要的不鲜明性。因而我们须要团结来化解这种需要。幸而MySQL设计的扩张性给了作者们那一个机缘,大家得以因而自定义函数的效应消除这一个标题。

 

1.MySQL自定义函数简单介绍

  在MySQL中利用自定义函数也急需相应的渴求,语法如下,

  创造新函数:

    Create function function_name(参数列表)

    returns再次来到值类型

    函数体内容

  相关注解,

  • 函数名:应该合法的标记符,何况不该与已有些根本字冲突。二个函数应该属于某数据库,可以利用db_name.funciton_name的款式进行业前函数所属数据库

    ,不然默认为近年来数据库。

  • 参数列表:能够有贰个要么多个函数参数,乃至是从未有过参数也是能够的。对于每一个参数,由参数名和参数类型组成。
  • 重返值:指明重回值类类型
  • 函数体:自定义函数的函数体由多条可用的MySQL语句,流程调节,变量申明等语句构成。供给指明的是函数体中一定要含有return 重回语句。

2.自定义示例

  a.无参函数定义

mysql> DROP FUNCTION IF EXISTS hello;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION hello()
    -> RETURNS VARCHAR(255)
    -> BEGIN
    -> RETURN 'Hello  world,i am mysql';
    -> END $$
Query OK, 0 rows affected (0.11 sec)

调用自定义函数:

mysql> DELIMITER ;
mysql> SELECT hello();
 ------------------------- 
| hello()                 |
 ------------------------- 
| Hello  world,i am mysql |
 ------------------------- 
1 row in set (0.00 sec)

可以预知曾经调用成功了,须求介意的几点

  之所以接纳DELIMITE福睿斯是因为MySQL中私下认可是利用分号来收场贰个指令的,我们定义的函数体中一条命令写完时会用分号来收尾,而MySQL会误感到函数体已经定义实现,因为我们要求定义二个新的标志符来标记八个下令的截止,因为就足以接纳DELIMITESportage。

  b:含有参数的自定义函数

  在大家格式化日期的时候大家会调用DATE_FORMAT(date,format)函数,第一个参数是日期第贰个参数是亟需格式化的格式,也正是说需求传递三个参数,我们前几天就把那些函数给改下

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS test.formatDate $$
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> CREATE FUNCTION   test.formatDate(fdate datetime)
    -> RETURNS VARCHAR(255)
    -> BEGIN
    -> DECLARE x VARCHAR(255) DEFAULT '';
    -> SET x= date_format(fdate,'%Y年%m月%d日%h时%i分%s秒');
    -> RETURN x;
    -> END $$
Query OK, 0 rows affected (0.11 sec)

mysql> DELIMITER ;
mysql> SELECT   formatDate(now());
 ---------------------------- 
| formatDate(now())          |
 ---------------------------- 
| 2014年11月21日03时41分21秒 |
 ---------------------------- 
1 row in set (0.18 sec)

那般当大家必要定制那样的函数时就能够协和完结了,那正是经过含参的自定义函数。

3.自定义函数相关语法及变量

  a:变量证明

    DECLARE var_name[,...] type [DEFAULT value] ,这一个讲话被用来声称局地变量。要给变量提供二个暗中认可值,请包蕴二个DEFAULT子句。值可以被内定为贰个表明式,无需为贰个常数。若无DEFAULT子句,初叶值为NULL。使用语序使用 set 和 select into语句为变量赋值。

  b:IF  条件语句、

IF search_conditionTHEN

statement_list

[ELSEIF search_conditionTHENstatement_list]

...

[ELSE statement_list]

ENDIF;

c:CASE语句

CASE case_value

WHEN when_valueTHENstatement_list

[WHEN when_value THENstatement_list]

...

[ELSE statement_list]

END CASE;

d:循环语句

While

[begin_label:]WHILEsearch_conditionDO

statement_list

END WHILE [end_label];

退出整个循环leave 相当于break

退出当前循环iterate 相当于 continue

通过退出的标签决定退出哪个循环。

4.别的相关

  删除函数我们得以接纳 DROP FUNCTION IF EXISTS function_name;

mysql>  SELECT hello();
 ------------------------- 
| hello()                 |
 ------------------------- 
| Hello  world,i am mysql |
 ------------------------- 
1 row in set (0.12 sec)

mysql> DROP FUNCTION hello();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right s
mysql> DROP FUNCTION hello;
Query OK, 0 rows affected (0.47 sec)

mysql>  SELECT hello();
ERROR 1305 (42000): FUNCTION test.hello does not exist

透过打印的失实能够开采有些是需求专一的

  查看定义的函数  SHOW FUCNTION STATUS LIKE 'function_name',只怕是使用SHOW  CREATE FUNCTION function_name;

mysql> SHOW FUNCTION STATUS LIKE 'hello';
 ------ ------- ---------- ---------------- --------------------- --------------------- --------------- --------- ---------------------- ---------------------- -------------------- 
| Db   | Name  | Type     | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
 ------ ------- ---------- ---------------- --------------------- --------------------- --------------- --------- ---------------------- ---------------------- -------------------- 
| test | hello | FUNCTION | root@localhost | 2014-11-21 14:48:05 | 2014-11-21 14:48:05 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
 ------ ------- ---------- ---------------- --------------------- --------------------- --------------- --------- ---------------------- ---------------------- -------------------- 
1 row in set (0.18 sec)

mysql> SHOW  CREATE FUNCTION hello;
 ---------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------
----------- ---------------------- ---------------------- -------------------- 
| Function | sql_mode                                                       | Create Function
           | character_set_client | collation_connection | Database Collation |
 ---------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------
----------- ---------------------- ---------------------- -------------------- 
| hello    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `hello`() RETURNS varchar(255) CHARSET latin1
BEGIN
RETURN 'Hello  world,i am mysql';
END | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
 ---------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------
----------- ---------------------- ---------------------- -------------------- 
1 row in set (0.01 sec)

修改函数ALTE奥迪Q3 FUNCTION  function_name  函数选项。

ALTER FUNCTION func_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

上边这些语法结构是MySQL官方给出的,修改的开始和结果可以包涵SQL语句也可以不含有,既可以够是读数据的SQL也得以是修改数据的SQL还会有权力。另外在修改function的时候还供给留意你不可能使用这几个语句来修改函数的参数以至函数体,假设您想改动那些的话你就必要删除掉那几个函数然后再一次创造。

mysql> SELECT hello();
 ------------------------- 
| hello()                 |
 ------------------------- 
| Hello  world,i am mysql |
 ------------------------- 
1 row in set (0.00 sec)

mysql> ALTER FUNCTION hello
    ->
    -> READS SQL DATA
    -> COMMENT 'print  hello';
Query OK, 0 rows affected (0.23 sec)

mysql>  SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,
    -> ROUTINE_COMMENT FROM information_schema.Routines
    -> WHERE ROUTINE_NAME='hello';
 --------------- ----------------- ----------------- 
| SPECIFIC_NAME | SQL_DATA_ACCESS | ROUTINE_COMMENT |
 --------------- ----------------- ----------------- 
| hello         | READS SQL DATA  | print  hello    |
 --------------- ----------------- ----------------- 
1 row in set (0.21 sec)

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:存储过程和函数,存储过程

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。