MySQL存储过程

MySQL存储过程

概念

存储过程是一组为了完成特定功能的SQL语句级集合，也就是说存储过程中可以有多条SQL语句。

存储过程经过编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

说白了，就类似于php中的函数。

优点

1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用if/while/case等控制语句编写，可以完成复杂的判断和运算

2. 存储过程类似于变成语言的函数，可以在程序中多次调用。

3. 存储过程能实现较快的执行速度。因为存储过程编写完成，编译一次后，之后调用，不再编译。

语法

create procedure 名字(参数1[,参数2])
begin
    sql语句
end

存储过程参加完整步骤

1. 选中某个数据库

   mysql> use demo;

2. 修改mysql默认结束符号

   mysql> \d $

   或 delimiter $

3. 创建存储过程

   mysql> create procedure hello()
   mysql> begin
   mysql> show tables;
   mysql> end
   mysql> $

4. 将结束符号改为;

   mysql> \d ;

5. 调用存储过程

   mysql> call hello();

存储过程中的局部变量定义

局部变量只会在存储过程中生效，其他地方调用不了

• 局部变量定义

  declare 变量名 数据类型 default 默认值

• 局部变量赋值

  set 变量名 = 值 [, 变量名1 = 值]

• 存储过程的三种参数

  存储过程的参数有三种，分别是输入输出类型(in)，输入类型(inout)，输出类型(out)。

  参数形式：

  [in | out | inout] 参数名 type

  其中，in表示输入参数；out表示输出参数； inout表示既可以是输入，也可以是输出；type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型

  in 类型的参数：
  表示该参数的值必须在调用存储过程之前指定，在存储过程中修改的值不能被返回。

  out类型的参数：
  out的值可以在存储过程内部改变，并可以返回

  inout类型的参数：
  inout的值可以在调用时指定，并可以在存储过程中修改和返回

• 局部变量定义例子

  mysql> \d $

  mysql> create procedure test_var()
  mysql> begin

  // 定义一个局部变量
  mysql> declare name varchar(20) default ‘jack’;

  //输出局部变量
  mysql> select name;
  mysql> end
  mysql> $

  //调用存储过程
  mysql> \d ;
  mysql> call test_var();

• 局部变量赋值例子

  mysql> \d $

  mysql> create procedure test_var_val()
  mysql> begin
  mysql> declare num int;

  //给局部变量num赋值
  mysql> set num = 10;
  mysql> select num;
  mysql> end
  mysql> $

  mysql> \d ;

• 存储过程的三种参数in类型的例子

  mysql> \d $

  mysql> create procedure test_three(in age int)

  mysql> begin

  mysql> select age;

  //在存储过程中改变值
  mysql> set age = age + 1;

  mysql> select age;
  mysql> end
  mysql> $

  mysql> \d ;

  //声明一个变量
  mysql> set @i=3;

  //调用存储过程，并且将@i这个变量传递给存储过程的age局部变量
  mysql> call test_three(@i);

  //查询@i的值
  mysql> select @i;
  结论： @i的值不变，还是3，说明in类型的参数是不会影响传递@i的，也就是类似于php中的值传递。

• 存储过程的三种参数out类型的例子

  mysql> \d $

  mysql> create procedure test_out(out age int)

  mysql> begin

  mysql> select age;

  //在存储过程中改变值
  mysql> set age = 22;

  mysql> select age;
  mysql> end
  mysql> $

  mysql> \d ;

  //声明一个变量
  mysql> set @out=0;

  //调用存储过程，并且将@i这个变量传递给存储过程的age局部变量
  mysql> call test_three(@out);

  //查询@i的值
  mysql> select @out;

  总结：@out的值也变成了22,说明改变了age这个局部变量的值，也会改变@out的值

  所以使用out类型参数来接受到存储过程中的需要返回的结果。

总结in、out区别：

in:表示输入一个值，你需要一个值，我给你一个值
out:你往外输出一个值，你输出的那个值我就拿一个变量来接收你给我输出的那个值

• 存储过程的三种参数inout类型的例子

  mysql> \d $

  mysql> create procedure test_five(inout num int)

  mysql> begin

  mysql> select num;

  //在存储过程中改变值
  mysql> set num = 33;

  mysql> select num;
  mysql> end
  mysql> $

  mysql> \d ;

  //声明一个变量
  mysql> set @i=3;

  //调用存储过程，并且将@i这个变量传递给存储过程的age局部变量
  mysql> call test_five(@i);

  //查询@i的值
  mysql> select @i;

  inout类型参数： @i可以传递进去给num,并且num的值也返回给@i

存储过程之选择语句(if)

语法：

 if  条件1  then   if内语句

 [elseif 条件2 then 语句2]

 [else 语句3]

 end if;

例子1：

//如果age> 20 就输出’成年了’，小于20就输出未成年
mysql> \d $mysql> create procedure test_if(in age int) mysql> begin mysql> if age > 20 then mysql> select ‘成年了’; mysql> else mysql> select ‘未成年’; mysql> end if; mysql> end mysql>$

例子2：

//如果60>age> 20 就输出'成年了'，小于20就输出未成年,大于60小于200就是老年人

mysql> \d $mysql> create procedure test_ifelse(in age int) mysql> begin mysql> if age > 20 && age < 60 then mysql> select ‘成年人’; mysql> elseif age <= 20 then mysql> select ‘未成年’; mysql> elseif age > 60 && age < 200 then mysql> select ‘老年人’; mysql> else mysql> select ‘人妖’; mysql> end if; mysql> end mysql>$

存储过程之选择语句(case)

case语句用来进行条件判断，类似php中的switch case

语法：

   case case_value

   when when_value  then 语句

   [when when_value then 语句]

   [else 语句]

   end case;

例子：如果用户=2，就将工资加1000，如果用户id=3，工资加2000

create table saraly(

id int not null primary key auto_increment,

uid int unsigned not null ,

saraly decimal(18,2) not null

)engine=innodb default charset=utf8;

//插入测试数据

insert into saraly(uid, saraly) values(2,200000);
insert into saraly(uid, saraly) values(3,400000);
insert into saraly(uid, saraly) values(3,500000);

\d $
create procedure test_case(in userid int)
begin
case userid
when 1 then
update saraly set saraly=saraly+1000 where uid =1;
when 2 then
update saraly set saraly=saraly+2000 where uid=2;
else
select ‘工资没有涨’;
end case;
end

$

\d ;

• case 另外常用例子

  //将用户详情表中的性别为1，变成男，0=>女

  select id,uid,city,(case sex when 1 then ‘男’ when 0 then ‘女’ else ‘人妖’ end) as sex from user_detail;

存储过程之循环语句(while)

语法：

   while 条件 do
        语句

   end while;

 例子： 利用while循环计算1+2+3+..+100的值

 \d $
mysql> create procedure addsum(out sum int)
    -> begin
    -> declare i int default 1;
    -> declare result int default 0;
    -> while i<=100 do
    ->   set result = i + result;
    ->   set i = i + 1;
    -> end while;
    -> set sum = result;
    -> end
    -> $

mysql>\d ;

//定义一个变量接受addsum返回的值
mysql> set @res=0;
mysql> call addsum(@res);
mysql> select @res;
+--------+
| @resul |
+--------+
|   5050 |
+--------+

存储过程管理

• 查看某个数据库下的所有存储过程

  show procedure status where db =’数据库名’;

• 查看当前数据库下面的存储过程列表

  use demo;//先要进入到某个数据库
  select specific_name from mysql.proc;

• 查看存储过程的内容

  use demo;//先要进入到某个数据库
  show create procedure 存储过程名字；

• 删除存储过程

  drop procedure 存储过程名

触发器

• 触发器概念

触发器是一种特殊类型的存储过程，在插入，删除和修改表中数据时被触发执行。

• 触发时机

  Before和After两种

• 触发条件

  Insert ,delete , update

总结：所以在一个表上最多可以建立6个触发器，即：1. before insert型(插入数据之前触发) 2. before update型(更新数据之前) 3.before delete型

1. after insert 型 5. after update型 6. after delete型。

并且不能在同一个表中建立两个类型完全相同的触发器。

• 触发器语法

  create trigger 触发器名称

  after/before insert/update/delete on 表名

  for each row

  begin

  sql语句

  end

• 在触发器中引用行值

触发事件 引用对象 表示方法

insert 新增的行 new.列名
delete 删除的行 old.列名
update 更新前的行 old.列名
update 更新后的行 new.列名

• 触发器例子

  1. 插入触发器例子

  例子说明： 因为每位员工入职后都应该有对应的薪资,所以适合使用触发器来完成

  //员工表建表语句
  CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(150) NOT NULL,
  pass varchar(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY name (name)
  ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

//薪水表
CREATE TABLE saraly (
id int(11) NOT NULL AUTO_INCREMENT,
uid int(10) unsigned NOT NULL,
saraly decimal(18,2) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

//建立触发器

mysql> \d $mysql> create trigger user_add_saraly after insert -> on user for each row -> begin -> insert into saraly(uid,saraly) values(new.id,50000); -> end ->$

//当给user表插入一条的数据时，saraly表也会多一条数据
mysql> insert into user(name,pass) values(‘rest’,md5(‘234111’));

例子2： 员工离职后，需要删除员工信息与薪资信息。这个时候就可以使用到触发器

mysql> \d $mysql> create trigger user_del_saraly after delete -> on user for each row -> begin -> delete from saraly where uid = old.id; -> end ->$

//当删除user表数据，saraly表数据也会删除
mysql> delte from user where id = 6;

触发器管理

• 删除触发器

  不再需要的触发器必须删除，否则触发器可能会在符合条件时被执行。

  drop trigger 触发器名字

• 查看所有触发器

  show trigger

---

视图

• 概念
  

  视图是一张虚拟表，并不表示任何物理数据，只是用来查看数据而已。

select语句的结果集构成视图所返回的虚拟表，当表中的数据发生变化时，从视图中查询出来的数据也随之改变。

视图中的行和列都来自数据表，这些数据表称之为视图的基表，视图数据是在视图被使用时动态生成的。

• 视图的作用

  1. 保护一些数据，可以将数据表的一些数据放到视图中，然后只给查询者查看视图的权限

  2. 简化SQL语句.为复杂的查询建立一个视图，用户不必输入复杂的查询语句，只需要查询视图就行

• 注意

  1. 视图并不能加快查询速度，因为视图的数据是在使用视图时从基表中拿出来的

• 视图的创建

语法：

create view 视图名 as 查询类型的sql语句

• 实例

  1. 从用户表和用户详情表中查询出所有的数据形成视图

  create view v_all_user as select u.id,u.name,d.sex,d.city from user u left join user_detail d on u.id=d.uid;

  //以后需要所有用户的所有数据，就只需要查询视图 v_all_user

  select * from v_all_user 的数据等同于 select u.id,u.name,d.sex,d.city from user u left join user_detail d on u.id=d.uid 的数据

视图管理

• 查看某个视图的定义

  show create view 视图名

• 视图删除

  drop view 视图名