01-存储过程---mysql

1.查看存储过程：show procedure status;

2.查看存储过程或函数的创建代码

 show create procedure proc_name;

 show create function func_name;

一个简单的存储过程。

delimiter $$     create   procedure  testa()     begin     select * from users;     end     $$

存储过程的特点

1.能完成较复杂的判断和运算。

2.可编程性强，灵活

3.SQL编程的代码可重复使用

4.执行速度相对快一些

5.减少网络之间数据传输，节省开销

  

存储过程的变量

实例：

编写存储过程，使用变量取uid=2的用户名

BEGINDECLARE   my_uname VARCHAR(32) default '';  SET  my_uname='hxf';  SELECT uname into my_uname from users where uid=2;SELECT my_uname;END

call testb();

声明变量：

DECLARE my_uname VARCHAR(32) DEFAULT '';

给变量赋值：  

 ① set  name='aaa'

 ②SELECT uname into my_uname from users where uid=2;

实例：

查看hxf表中和users表中的数量，和users表中最大的和最小的注册时间。

BEGINBEGIN DECLARE  hxf_sum int default 0;#声明一个变量，默认0    DECLARE users_sum int DEFAULT 0;SELECT  COUNT(*)  into hxf_sum from hxf;SELECT COUNT(*) into users_sum from users;SELECT hxf_sum,users_sum;   END;BEGINDECLARE  max_regtime timestamp;DECLARE  min_regtime  timestamp;SELECT MAX(regtime),min(regtime) into max_regtime,min_regtime from users;select max_regtime,min_regtime;END;END

注意：

BEGIN和END之间的变量是局部变量。其他的BEGIN和END块是不能访问的。需要声明为全局变量，放到所有BEGIN和END之外。

存储过程的参数

需求：编写一个存储过程，传入uid获取uname.

BEGIN#需求：编写一个存储过程，传入uid获取uname.DECLARE my_uname VARCHAR(32) DEFAULT '';  select uname into my_uname from  users  where  uid=my_uid;select my_uname;END

调用：call testd(1);

set  @uname:='';call testd(1,@uname);select @uname as aa;

存储过程的条件语句

if then else end if;

BEGIN#编写一个存储过程，如果uid是偶数则给出uname,其他情况返回uidDECLARE  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;   ELSESELECT my_uid; end if;END

if then elseif then else end if;

BEGIN#根据用户传入的uid进行判断，如果用户状态status为1，则给用户score加10分  #如果用户状态status为2，则给用户score加20分#其他情况加30分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)THENupdate users set score=score+20 where uid=my_uid;ELSEupdate users set score=score+30 where uid=my_uid;END IF;END

循环语句

while

BEGIN#使用循环语句，想表testa(uid)中插入10条id连续的记录。   DECLARE i int default 0;   WHILE (i<10) DO    BEGIN         SELECT i;set i=i+1;        insert into testa(id) VALUES(i);END;   END WHILE;END

repeat

BEGIN#使用repeat循环语句，想表testa(id)中插入10条id连续的记录。   DECLARE i int default 100;  REPEAT    BEGIN         SELECT i;set i=i+1;        insert into testa(id) VALUES(i);END;    UNTIL i>=110   END REPEAT;END

游标

BEGIN#编写存储过程，使用游标，把uid为偶数的记录逐一更新用户名    DECLARE   stopFlag int default 0;    DECLARE   my_uname varchar(32) default '';    DECLARE   uname_cur CURSOR for  SELECT uname from users where uid%2=0;DECLARE CONTINUE  HANDLER for NOT found  set stopFlag=1;#声明一个结束句柄OPEN  uname_cur; #打开游标    FETCH uname_cur   INTO  my_uname;WHILE(stopFlag=0) DOBEGINUPDATE users set  uname=CONCAT(my_uname,'_cur') where  uname=my_uname;FETCH uname_cur INTO  my_uname;END;END WHILE;    CLOSE uname_cur;#关闭游标END

简单的自定义函数

BEGIN#编写一个函数，传入一个用户uid，返回用户的uname  DECLARE  my_uname VARCHAR(32) default '';  SELECT uname into my_uname from  users where uid=my_uid;RETURN my_uname;END

函数与存储过程的区别是，函数必须要有返回值类型

函数的调用使用select  func1(5);

CONTAINS SQL    包含查询的sql语句。   

NO SQL    没有sql，只是一些逻辑

READS SQL DATA     只允许读

MODIFIES   SQL DATA 允许修改

自定义函数综合应用

BEGIN#输入用户的id,获得accountid,uid,uname组合的uuid值，在全区游戏中作为用户的唯一标识  DECLARE  uuid VARCHAR(32) default '';SELECT concat(accountid,'_',uid,'_',uname) into uuid from users where uid=my_uid;  return uuid;END

数据访问：READS SQL DATA 

BEGIN#输入参数uid,计算该uid所在账号下的所有游戏角色的金币总量。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

数据访问：READS SQL DATA 

触发器

需求：出于审计的目的，当有人往表users插入一条记录的时候，把插入的uid,uname,插入动作和操作时间记录下来。

添加审计

create TRIGGER tr_users_insert after insert on usersfor EACH ROWBEGIN   INSERT into alog(id,log,time)  VALUES  (New.uid,New.uname,now());END

删除审计

create trigger tr_user_delete before DELETE on users for each ROWBEGIN   insert into alog (id,log,time)   values(OLD.uid,OLD.uname,now());enddelete from users where uid=2;