存储的程序
========================================
* 数据库中存储的程序，对数据进行运算处理
* 存储过程
* 函数
* 触发器
会话变量
========================================
* 一次会话过程中，可以设置一些变量保存数据
客户端连接DB后，执行命令，数据库返回响应，来回执行的过程就是一次会话过程,本次连接断开后，会话结束，下次再连DB，这些变量就不存在了
* set @name='张三'
* @表示会话变量
* @@表示全局变量，指定是系统变量，@@可省略
set @@sql_mode='STRICT_TRANS_TABLES';
set @@autocommit=OFF
set sql_mode='STRICT_TRANS_TABLES';
set autocommit=OFF
* 显示变量的值
显示全局变量时，@@不可省略
select @name, @a, @b, @c;
存储过程
==================================================
1、mysql中存储过程只能对数据增删改查，不能动表结构
2、创建在某个具体的库中，如db1，本库可以直接调用，其他库可以通过库名.调用
* 创建存储过程
delimiter -- 设置语句的结束符号
create procedure p1()
begin
insert into t values(floor(rand()*10));
select * from t;
end;
delimiter ; -- 语句的结束符重新设置成分号
需求：创建存储过程，随机插入0-9的整数到t表的c字段
* 调用存储过程
call p1();
* 删除存储过程
drop procedure [if exists] p1;
* 查询存储过程
show procedure status;
* 参数
存储过程的参数三种：
in 输入参数（默认）
out 输出参数
inout 即可输入也可输出
定义存储过程：
create procedure p2(in a int, out b int)
begin
set b = a*2;--设置输出变量b的值
end;
调用 p2()：
call p2(1,@a);
call p2(2,@b);
call p2(3,@c);
select @a,@b,@c;
//
* 流程控制
*)选择判断
if 条件 then
...
end if;
-----------------
if 条件 then
...
else
...
end if;
==========================
case
when 条件 then ...;
when 条件 then ...;
when 条件 then ...;
else ...;
end case;
*) 循环
while 条件 do
...
end while;
---------------
loop
...
end loop;
---------------
repeat
...
until 条件 end repeat;
---------------
leave 跳出循环
iterate 直接进入下次迭代
---------------
循环命名
lp: loop
leave lp;
iterate lp;
end loop;
* declare
定义局部变量
*) declare a int; -- null
*) declare a int default 1;
*) 局部变量在 end 结束时销毁
begin
declare a int default 1;
begin
declare b int default 2;
end;
select a; -- ok
select b; -- 错误
end;
选择判断：p3()
drop procedure if exists p3;//
create procedure p3(in a int)
begin
if a=1 then select 10;
end if;
case
when a=2 then select 20;
when a=3 then select 30;
when a=4 then select 40;
else select 100;
end case;
end;//
call p3(1)//
循环：p4()
需求：循环插入1-n的数字到t表
drop table t;
delete from t;
truncate table t;
drop procedure if exists p4;//
create procedure p4(in a int)
begin
declare i int default 1;
while i<=a do
insert into t values(i);
set i=i+1;
end while;
set i=1;
lp:loop
insert into t values(i);
set i=i+1;
if i>a then leave lp;
end if;
end loop;
end;//
call p4(5);
函数
==================================================
* 与存储过程类似，
* 函数有返回值
* 用 select f() 调用函数
* 创建
create function f(参数) returns int
begin
....
return 计算结果;
end;
函数：求a的b次方
r --定义的局部变量保存结果
i --定义的局部变量用来控制循环次数
触发器
=============================================
* 对一行数据进行增删改操作，
可以触发一端代码执行
* 六种：一张表最多就能创建6个触发器
before insert
before update
before delete
after insert
after update
after delete
* 创建触发器
create trigger 名称 before insert
on tb1 for each row
begin
....
end;
* 查询库中所有触发器
SELECT * FROM information_schema.`TRIGGERS`;
* 两个隐含对象
new - 新的数据行
old - 旧的数据行
new.id, new.name, new.price
old.id, old.name, old.price
*)插入操作
new - 新插入的一行数据
old - 没有
*)修改操作
new - 修改后的新数据
old - 修改前的旧数据
*)删除操作
new - 没有
old - 被删掉的数据
需求：修改数据时，用触发器自动填入修改的时间
练习
===================================================
1. 新建库新建表
create database db1 charset=utf8;
use db1;
create table t(
c int
);
2. 创建存储过程
delimiter -- 修改语句结束符
drop procedure if exists p1; -- 删除存储过程
create procedure p1() -- 定义存储过程 p1
begin
insert into t values(floor(rand()*10));
select * from t;
end;
call p1();// -- 调用存储过程
3. 测试输入参数和输出参数
drop procedure if exists p2;
create procedure p2(in a int, out b int)
begin
set b = a*2; -- 设置输出变量 b 的值
end;
call p2(1, @a); -- a变量在p2()存储过程中被赋值
call p2(2, @b);
call p2(3, @c);
select @a, @b, @c;
4. 测试选择判断
drop procedure if exists p3;
create procedure p3(in a int)
begin
if a=1 then -- 如果a是1
select 10;
end if;
--
case
when a=2 then select 20; -- 如果a是2
when a=3 then select 30; -- 如果a是3
when a=4 then select 40; -- 如果a是4
else select 100; -- 如果a是234之外的其他值
end case;
end;
call p3(1);
call p3(2);
call p3(3);
call p3(4);
call p3(5);
5. 循环测试，循环插入 1,2,3,4,5
truncate table t; -- 删除再重建表（DDL）
delete from t; -- 删除数据不会重建表，故自增不会归零（DML）
drop procedure if exists p4;
create procedure p4(in a int)
begin
-- 定义变量1.局部变量 2.会话变量
declare i int default 1; -- 局部变量 i
--
while i<=a do -- 当<=a时重复执行
insert into t values(i);
set i = i+1;
end while;
--
set i=1;
lp: loop
insert into t values(i);
set i = i+1;
if i>a then -- 如果超过a离开循环
leave lp;
end if;
end loop;
--
set i=1;
repeat
insert into t values(i);
set i = i+1;
until i>a end repeat; -- 直到超过a结束循环
end;
call p4(5);
select * from t;
6. 测试函数，求 a 的 b 次方
drop function if exists func_pow;
create function func_pow(a bigint, b bigint) returns bigint
begin
declare r bigint; -- 用来保存结果
declare i bigint default 1; -- 用来控制循环次数
set r = a;
while i<b do -- 重复b-1次
set r = r*a; -- 重复地乘a
set i=i+1; -- 次数递增
end while;
return r; -- 返回计算结果
end;
select func_pow(2,3);
select func_pow(3,2);
select func_pow(2,8);
select c, func_pow(c, 2) from t;
7. 产生随机字符串
可以产生大量随机字符串填入表中，
对查询效率进行测试
drop function if exists rand_str;
-- 产生的随机字符串长度范围 [a, b)
create function rand_str(a int, b int) returns varchar(255)
begin
-- 从这些字符当中随机挑选几个
declare s0 varchar(600) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水化高自二理起小物现实加量都两体制机当使点从业本去把性好应开它合还因由其些然前外天政四日那社义事平形相全表间样与关各重新线内数正心反你明看原又么利比或但质气第向道命此变条只没结解问意建月公无系军很情者最立代想已通并提直题党程展五果料象员革位入常文总次品式活设及管特件长求老头基资边流路级少图山统接知较将组见计别她手角期根论运农指几九区强放决西被干做必战先回则任取据处队南给色光门即保治北造百规热领七海口东导器压志世金增争济阶油思术极交受联什认六共权收证改清己美再采转更单风切打白教速花带安场身车例真务具万每目至达走积示议声报斗完类八离华名确才科张信马节话米整空元况今集温传土许步群广石记需段研界拉林律叫且究观越织装影算低持音众书布复容儿须际商非验连断深难近矿千周委素技备半办青省列习响约支般史感劳便团往酸历市克何除消构府称太准精值号率族维划选标写存候毛亲快效斯院查江型眼王按格养易置派层片始却专状育厂京识适属圆包火住调满县局照参红细引听该铁价严龙飞';
declare len int; -- 保存随机长度值
declare s varchar(255) default ''; -- 保存随机字符串结果
declare i int default 0; -- 用来控制次数
declare j int; -- 随机位置
-- 随机长度，范围 [a, b)
set len = a+floor(rand()*(b-a));
-- 产生 len 个随机字符连接到 s
while i<len do
-- 随机位置 [1, char_length(s0))
set j = 1+floor(rand()*(char_length(s0)-1));
-- 从s0取i位置字符，连接到 s
set s = concat(s, substring(s0, j, 1));
set i = i+1; -- i递增
end while;
return s; -- 返回随机字符串结果
end;
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
select rand_str(3,8);
1.具体长度 len
[3,8)
3+[0,5)
3+floor(rand()*5)
len = a+floor(rand()*(b-a))
0 0
0.1 *5 0.5
0.2 1
0.3 1.5
0.8 4
0.9999 4.xxxx
rand()
2. 需要一个空串 s=''
3. 循环 len 次
4. 产生随机位置 j
1+ [0, char_length(s0)-1)
j = 1+floor(rand()*(char_length(s0)-1))
5. 截取 s0 的 i 位置字符
substring(s0, j, 1)
6. 这个字符连到 s 上
s = concat(s, substring(s0, j, 1))
* 分类表(tb)
修改数据时，不必手动修改 updated 字段，
用触发器自动填入当前时间
update tb set name='新类别'
where id=2;
update tb set name='新类别',updated=now()
where id=2;
create trigger tb_updated_trigger
before update on tb
for each row
begin
set new.updated=now();-- 新数据的更新时间字段填入系统时间
end;
select id,name,updated from tb_item_cat
where id in(3,4,5,6,7);
//
update tb
set name=concat('>>>', name)
where id in(3,4,5,6,7);
//
* 阻止删除数据
*)当 before delete 时，手动产生一个错误，
使删除操作失败
create trigger tb_del_trigger
before delete on tb
for each row
begin
-- 手动暴力产生错误
delete from 不允许表的删除操作;
end;
//
delete from tb where id=1;
//
-- 新建分类表 tb
create table tb(
id bigint primary key auto_increment comment '自增主键',
name varchar(50) comment '分类名称',
created datetime comment '创建时间',
updated datetime comment '修改时间'
)engine=innodb charset=utf8;
insert into tb(name,created,updated) values('AA','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('BB','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('CC','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('DD','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('EE','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('FF','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('GG','2015-09-14 23:59:59','2015-09-14 23:59:59');
insert into tb(name,created,updated) values('HH','2015-09-14 23:59:59','2015-09-14 23:59:59');
文章转载自edge的IT空间，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
