初学存储过程

php调用存储过程

Db::query('call 名称([条件])')

1、一个简单的存储过程

delimiter $$//创建分隔符
create procedure testa()  //类似创建一个方法【testa(参数)类似一个方法名】
begin // 表示开始
//要做的事，比如查询一张表，也可以查询多张表
select * from test;
end // 表示结束
$$ //结束
delimiter ;  //还原；的分隔符

2、存储过程的机构组成：

  1. 创建格式：create procedure 存储过程名
  2. 包含一个以上代码块，代码块使用begin和end之间
  3. 在命令行中创建需要定义分隔符 delimiter $$

3、存储过程的特点

1.能完成较复杂的判断和运算
2.可编辑性强，灵活
3.SQL编程的代码可以重复使用
4.执行速度相对快一些
5.减少网络之间数据传输，节省开销

4、存储过程变量

需求：编写存储过程，使用变量取id=2的名字

delimiter $$//创建分隔符
create procedure testa()//创建存储过程
begin //开始
declare my_name varchar(32) default ‘’;//创建变量，必须有字符类型
set  my_name=’wang’;//给变量赋值
select `name` into `my_name` from `test` where `id`=2;//查询表中的数据给变量赋值
select my_name;//查询变量所对应的值
end;//结束
$$
delimiter ;

特点：

  1. 变量的声明使用declare，一句declare只能声明一个变量，变量必须先声明后使用
  2. 变量具有数据类型和长度，与mysql的SQL数据类型保持一致，因此甚至还能指定默认值、字符集和排序规则等
  3. 变量可以通过set来赋值，也可以通过select into的方式赋值
  4. 变量需要返回，可以使用select语句，如：select 变量名

5、存储过程变量应用示例

需求：统计表hxf，users的行数量和users表中最早，最晚的注册时间

delimiter $$
create procedure stats_user()
begin
begin
declare hxf_sum int default 0;
declare users_sum int default 0;
select count(*) int hxf_sum from hxf;
select count(*) int users_sum from users;
end;
begin
declare max_regtime timestamp;
declare min_regtime timestamp;
select max(regtime),min(regtime) into max_timestamp,min_timestamp from users;
select max_regtime,min_regtime;
end;
end;
$$
delimiter ;
  1. 变量是有作用域的，作用范围在begin与end块之间，end结束变量的作用范围即结束。
  2. 需要多个块之间传递值，可以使用全局变量，即放在所有代码块之前。
  3. 传参变量是全局的，可以在多个块之间起作用

⦁ 存储过程参数
⦁ 存储过程的传入参数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 ;

a. 传入参数：类型为IN，表示该参数的值必须在调用存储过程时指定，如果不显示指定为IN，那么默认就是IN类型。
b. IN类型参数一般只用于传入，在调用存储过程中一般不作修改和返回
c. 如果调用存储过程中需要修改和返回值，可以使用OUT类型参数
⦁ 存储过程的传出参数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;
end;
$$
delimit ;

⦁ 传入参数：在调用存储过程中，可以改变其值，并可返回
⦁ OUT是传出参数，不能用于传入参数值
⦁ 调用存储过程时，OUT参数也需要制定，但必须是变量，不能是常量
⦁ 如果即需要传入，同时又需要传出，则可以使用INOUT类型参数

6、存储过程的可变参数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 ;
  1. 可变变量INOUT：调用时可以传入值，在调用过程中，可以修改其值，同时也可以返回值
  2. INOUT参数集合了IN和OUT类型的参数功能
  3. INOUT调用时传入的是变量，而不是常量

7、存储过程条件语句

需求1：编写存储过程，如果用户uid是偶数则就给出uname，其他情况只返回uid

delimit $$
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 ;
  1. 条件语句最基本的结构：if(表达式) then...else...end if;
  2. if条件判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

需求2:根据用户传入的uid参数判断
(1) 如果用户状态status为1，则给用户score加10分
(2) 如果用户状态status为2，则给用户score加20分
(3) 其他情况加30分

delimiter $$
create procedure addscore(OUT my_uid int)
begin
declare my_status int default 0;
select status into my_status from user where uid=my_uid;
if(my_status=1)
then
update user set score=score+10 where uid=my_uid;
elseif(my_status=2)
then
update user set score=score+20 where uid=my_uid;
else
update user set score=score+30 where uid=my_uid;
end if;
end;
$$
delimiter ;

8、存储过程的循环语句

1.while循环

需求：使用循环语句，向表testa(uid)中插入10条uid连续的记录

Delimiter $$
create procedure insertdata1()
begin
declare i int default 0;
while(i<10) do  //循环开始
begin
select i;
set i=i+1;
insert into testa(uid) values (i);
end;
end while;//循环结束
$$
delimiter ;

a. while语句嘴基本的结构：while(表达式) do...end while；
b. while判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式

2.repeat循环语句

需求：使用repeat循环向表testa插入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;//结束循环
$$
delimiter ;
  1. repeat语句最基本的结构：repeat....until ...end repeat
  2. until判断返回逻辑真或者假，表达式可以是任意返回真或者假的表达式，
    只有当until语句为真时，循环结束。

9、什么是游标

需求：编写存储过程，使用游标，把uid为偶数的记录逐一更新用户名

delimiter $$
create procedure testcursor()
begin
declare stopflag int default 0;
declare my_uname varchar(32) default ‘’;
declare uname_cur cursor for select uname from users where uid%2=0;

1.游标是保存查询结果的临时内存区域

2.游标变量usname_cur保存了查询的临时结果，实际上就是查询结果集

declare continue handler for not fount set stopflag = 1;

3.当游标变量中保存的结果都查询一遍（遍历），到达结尾，把遍历stopflag设置为1

用于循环中判断是否结束

open uname_cur;#打开游标
fetch uname_cur into my_uname;#游标向前走一步，取出一条记录放到变量my_uname中。
while(stopflag = 0) do#如果游标还没有到结尾，就继续
begin
update users set uname=concat(my_uname,’_cur’) where uname=my_uname;
fetch uname_cur into my_uname;
end;
end while;
close uname_cur;#从内存中释放游标
end;
$$
delimiter ;

10、简单的自定义函数

需求：编写函数，传入一个用户的uid，返回用户的uname

delimiter $$
create function getuname(my_uid int) returns 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 ;

a. 创建函数使用create function 函数名（参数）returns 返回类型
b. 函数体放在begin和end之间
c. return指定函数的返回值
d. 函数调用：select getuname() [select 函数名]

11、触发器（常用于新增、修改、删除的时候去触发一个语句）

create trigger 触发器名 after insert on 表A for each row
begin
update 表B set 字段=XXX where 条件;
end
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 217,406评论 6赞 503
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,732评论 3赞 393
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 163,711评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,380评论 1赞 293
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,432评论 6赞 392
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,301评论 1赞 301
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,145评论 3赞 418
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 39,008评论 0赞 276
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,443评论 1赞 314
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,649评论 3赞 334
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,795评论 1赞 347
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,501评论 5赞 345
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,119评论 3赞 328
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,731评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,865评论 1赞 269
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,899评论 2赞 370
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,724评论 2赞 354