存储过程
一、一个简单的存储过程
#delimiter 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";"
delimiter$$
create procedure testa()
begin
select uid from hxf where uid=2;
end;
$$
delimiter;
存储过程的结果组成：
1、创建格式：create procedure 存储过程名
2、包含一个以上的代码块，代码块使用begin和end之间
3、在命令行中创建需要定义分隔符 delimiter$$
二、存储过程的特点
1、能完成较复杂的判断和原酸
2、可编程性强、灵活
3、SQL编码的代码可重复使用
4、执行速度相对快一些
5、减少网路之间的数据传输，节省开销
三、存储过程的变量
1、需求：编写存储过程，使用变量取uid=2的用户名
delimiter$$
create procedure testa()
begin
declare my_uname varchar(32) default '';#变量的声明使用declare，一句declare只声明一个变量，变量必须先声明后使用；变量具有数据类型和长度，与mysql的SQL数据类型保存一致，因此甚至还能指定默认值、字符集和排序规则等
set my_uname='hxf';#变量可以通过set来赋值，也可以通过select into的方式赋值
select uname into my_uname from users where uid=2;
select my_uname;#变量需要返回，可以使用select语句
end;
$$
delimiter;
call testa#调用存储过程
2、需求：统计表hxf,users的行数量和users表中最早，最晚的注册时间。
delimiter$$
create procedure stas_user()
begin
begin
declare hxf_sum int dafault 0;
declare users_sum int dafault 0;
select count(*) into hxf_sum from hxf;
select count(*) into users_sum from users;
select hxf_sum,users_sum;
end;
begin
declare max_regtime timestamp;
declare min_regtime timestamp;
select max(regtime),min(regtime) into max_regtime,min_regtime from users;
select hxf_sum,users_sum,max_regtime,min_regtime;
end;
end;
$$
delimiter;
①变量是有作用域的，作用域范围在begin和end块之间，end结束变量的作用范围即结束
②需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前
③传递变量是全局的，可以在多个块之间起作用
四、存储过程的参数
1、存储过程的传入参数IN
需求：编写存储过程，传入uid，返回用户的uname
delimiter$$
create procedure testa(my_uid int)
begin
declare my_uname varchar(32) default '';
select uname into my_uname from users where uid=my_uid;
select my_uname;
end;
$$
delimiter;
call testa(2);2为传入参数
①传入参数：类型为IN，表示该参数的值必须在调用存储过程时指定，如果不显示指定为IN，那么默认就是IN类型
②IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回
③如果调用存储过程中需要修改和返回值，可以使用OUT类型参数
2、存储过程的传出参数OUT
需求：编写存储过程，传入uid，返回用户的uname
delimiter$$
create procedure testa(IN my_uid int,OUT my_uname varchar(32) )
begin
select uname into my_uname from users where uid=my_uid;
select my_uname;
end;
$$
delimiter;
#调用存储过程
set @uname:='';
call testa(2,@uname);2为传入参数,@uname为传出的接收变量
select @uname as myunameAA
①传出参数：在调用存储过程中，可以改变其值，并可返回
②OUT是传出参数，不能用于传入参数值
③调用存储过程时，OUT参数也需要指定，但必须是变量，不能是常量
④如果既需要传入，同时又需要传出，则可以使用INOUT类型参数
3、存储过程的可变参数INOUT
需求：调用存储过程，参数my_uid，my_uname,既是传入，也是传出参数
delimiter$$
create procedure testa(INOUT my_uid int,INOUT my_uname varchar(32) )
begin
set my_uid=2;
set my_uname='hxf3';
select uid,uname into my_uid,my_uname from users where uid=my_uid;
select my_uid,my_uname;
end;
$$
delimiter;
#调用存储过程
set @uname:='';
set @id:=0;
call testa(@uid,@uname);
select @uid as myuidAA,@uname as myunameAA
①可变变量INOUT：调用时可传入值，在调用过程中，可修改其值，同时也可返回值
②INOUT参数集合了IN和OUT类型的参数功能
③INOUT调用时传入的是变量，不是常量
五、存储过程的条件语句
需求：编写存储过程，如果用户uid是偶数则给出uname，其他情况只返回uid
delimiter$$
create procedure testa(IN my_uid int )
begin
declare my_uname varchar(32) default '';
if(my_uid%2=0)
then
select uname into my_uname from users where uid=my_uid;
select my_uname;
else
select my_uid;
end if;
end;
$$
delimiter;
call testa(2)
①条件语句最基本的结构：if() then...else...end if;
②if语句返回逻辑真或假，表达式可以是任意返回真或假的表达式
需求：根据用户传入的uid参数判断：
（1）如果用户状态status为1，则给用户score加10分
（2）如果用户状态status为2，则给用户score加20分
（3）其他情况加30分
delimiter$$
create procedure testa(IN my_uid int )
begin
declare my_status int default 0;
select status into my_status from users where uid=my_uid;
if( my_status=1)
then
update users set score=score+10 where uid=my_uid;
elseif( my_status=2)
then
update users set score=score+20 where uid=my_uid;
else
update users set score=score+30 where uid=my_uid;
end if;
end;
$$
delimiter;
call testa(2)
①多条件判断结构：if() then...elseif then...else...end if
六、存储过程循环语句
1.while循环语句
需求：使用循环语句，向表testa(uid)中插入10条uid连续的记录
delimiter$$
create procedure insertdata1()
begin
declarei int default 0;
while(i<10) do
begin
select i;
set i=i+1;
insert into testa(uid) values(i);
end;
end while;
end;
$$
delimiter;
call insertdata1()
①while语句最基本的结构：while() do...end while；
②while判断返回逻辑真还是假，表达式可以是任意返回真或者假的表达式
2、repeat语句
需求：使用repeat语句向表testa(uid)中插入10条uid连续的记录
delimiter$$
create procedure insertdata2()
begin
declare i int default 100;
repeat
begin
select i;
set i=i+1;
insert into testa(uid) values(i);
end;
until i>=110
end repeat;
end;
$$
delimiter;
call insertdata2()
①repeat语句最基本的结构：repeat...until...end repeat;
②until判断返回逻辑真或假，表达式可以是任意返回真或假的表达式，只有当until语句为真时，循环结束
七、存储过程游标的使用
1、什么是游标
需求：编写存储过程，使用游标，把uid为偶数的记录逐一更新用户名
delimiter$$
create procedure testcursor()
begin
declare stopflag int default 0;
declare my_uname varchar(20);
declare uname_cur cursor for select uname from users where uid%=0;
#1.定义游标变量，游标是保存查询结果的临时内存区域
#2.游标变量uname_cur保存了查询的临时结果，实际上就是查询结果集
declare continue handler for not found set stopflag=1;
#3.当游标变量中保存的结果都查询一遍（遍历），到达结尾，把变量stopflag设置为1
#用于循环中判断是否结束
open uname_cur;#打开游标
fetch uname_cur into myname;#游标向前走一步，取出一条记录放到变量my_uname中。
while(stopflag=0) do
begin
update testa set uname=concat(my_uname,'_cur') where uname=my_uname;
fetch uname_cur into my_uname;
end;
end while;
close uname_cur;#关闭游标
end;
$$
delimiter;
call testcursor()
八、简单的自定义函数
1、一个简单的函数
需求：编写函数，传入一个用户uid，返回用户的uname
函数和存储过程的区别：函数必须有返回值
delimiter$$
create function getuname(my_uid int ) return varchar(32)
reads sql data
begin
declare my_uname varchar(32) default '';
select uname into my_uname from users where uid=my_uid;
return my_uname;
end;
$$
delimiter;
#调用自定义函数
select getuname(2);
①创建函数使用create function 函数名（参数） returns 返回类型
②函数体放在begin和end之间
③return指定函数的返回值
④函数调用：select getuname()
九、自定义函数综合应用示例
1、需求：输入用户ID，获得accountid，uid,uname组合的UUID值，在全区游戏中作为用户的唯一标识
delimiter$$
create function getuuid(my_uid int ) return varchar(32) charset utf-8
reads sql data#表示子程序包含读数据的语句，但不包含写数据的语句
begin
declare uuid varchar(32) default '';
select concat(accountid,'_',uid,'_’,uname) into uuid from users where uid=my_uid;
return uuid;
end;
$$
delimiter;
2、需求：输入参数uid，计算该uid所在游戏账号下所有角色的金币总量
delimiter$$
create function statgold(my_uid int ) return int charset utf-8
reads sql data#表示子程序包含读数据的语句，但不包含写数据的语句
begin
declare my_accountid int default 0;
declare gold_sum int default 0;
select accountid into my_accountid from users where uid=my_uid;
select sum(gold) into gold_sum from users where accountid=my_accountid;
return gold_sum;
end;
$$
delimiter;
十、触发器
1、什么是触发器
需求：出于审计目的，当有人往表users插入一条记录时，把插入的uid，uname，插入动作和操作时间记录下来。
delimiter$$
create trigger tr_users_insert after insert on users#在insert on users后触发触发器，除了after还有before，是在对表操作之前（before）或之后（after）触发动作的，操作事件包括insert,update,delete
for each now#影响的范围
begin
insert into oplog(uid,uname,action,optime)
values(NEW.uid,NEW.uname,'insert',now());#NEW.uid表示更新之后的值
end;
$$
delimiter;
①触发器：与函数，存储过程一样，触发器是一种对象，它能根据对表的操作时间，触发一些动作，这些动作可以是insert,update,delete等修改操作
需求：出于审计目的，当删除users表时，记录删除前该记录的主要字段值
delimiter$$
create trigger tr_users_delete before delete from users
for each now#影响的范围
begin
insert into oplog(uid,uname,action,optime,old_value,new_value)
values(OLD.uid,OLD.uname,'delete',now(),OLD.regtime,OLD.regtime);#OLD.uid表示删除之前的值
end;
$$
delimiter;
十一、子程序的高级特性
1、DETERMINISTIC特性
delimiter$$
create procedure testa()
NOT DETERMINISTIC
begin
declare my_uid int;
select uid into my_uid from my_uid where salary<3000 limit 1;
update user set regtime=now() where uid=my_uid;
end;
$$
delimiter;
①DETERMINISTIC的涵义是什么？
NOT DETERMINISTIC说明子程序包含了不确定的函数等，如now()，每次使用函数返回的结果都不一样，代表不确定。
②类似的存储过程喊函数高级特性还有：
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
③存储过程与DETERMINISTIC特性对复制的影响：存储过程执行的语句在二进制日志中体现，复制保证精确性。使用NOT DETERMINISTIC和DETERMINISTIC都一样，没影响
④{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}与存储过程也无关，对复制也无影响。
2、函数的只读高级特性
delimiter$$
create function testa() returns int
READS SQL DATA#创建存储过程函数时，必须使用高级特性，不然创建报错
begin
declare my_uid int;
select uid into my_uid from hxf where salary<3000 limit 1;
return my_uid;
end;
$$
delimiter;
①提供子程序使用数据的内在信息：
READS SQL DATA只包含读的SQL信息，说明只有读，没有更新，对复制精确性是安全的，则可以放行
3、函数包含可更新语句的高级特性
delimiter$$
create function testa()
CONTAINS SQL（错）-》READS SQL DATA（对）
begin
declare my_uid int;
select uid into my_uid from hxf where salary<3000 limit 1;
return my_uid;
end;
$$
delimiter;
①如果函数包含了更新语句，指定为CONTAINS SQL，MODIFIES SQL DATA等写的高级特性是不允许创建的
②包含更新语句的函数可以指定为NO SQL,或者READS SQL DATA只读特性，才可以放行创建，复制可以正常进行
4、函数包含可更新语句与复制的影响
delimiter$$
create function testa() return int
modifies sql data
begin
update user set level=50 where uid=5;
return 5;
end;
$$
delimiter;
①如果函数包含了更新语句，指定为CONTAINS SQL，MODIFIES SQL DATA，并且允许创建，有两个方案：修改log_bin_trust_function_creators=1，或者关闭binlog日志。对复制的影响：这种更新记录二进制日志，也能正常复制
②CONTAINS SQL包含读写语句
NO SQL无SQL语句（只读）
READS SQL DATA 只读的SQL语句
MODIFIES SQL DATA 只写的SQL语句
③函数设计原则上不允许更新，历史上函数更新对复制可能是不安全的。二进制日志不会记录select语句，但只有函数中有更新语句时是例外
5、SQL SQCURITY特性
浙公网安备 33010602011771号