MYSQL-存储过程

存储过程

存储过程是在大型的数据库中，一组为了完成特定功能的SQL语句集经过第一编辑后调用不需要再次编译，用户通过指定的存储过程的名字并给出来执行存储过程。
为了以后使用而保存一条或多条SQL语句集

1. 为了处理封装在容易使用的单元中，简化复杂咋做
2. 为了防止错误 保证了数据的一致性
3. 简化对变动管理
4. 提高性能
5. 灵活

缺点 1、编写复杂SQL语句 2、权限问题，可能无权限使用，一般都是使用存储过程
– mysql 5.0 以后版本支持存储过程
语法
CREATE PROCEDURE 名称(参数)
BEGIN
SQL 语句
END;

CREATE PROCEDURE test1()
BEGIN
  SELECT student.studentNo ,
        student.studentName,
        grade.gradeName,
        `subject`.subjectName
  FROM student 
  JOIN grade 
  ON grade.gradeID = student.gradeId
  JOIN `subject` 
  ON `subject`.gradeID = grade.gradeID;
END;

== 调用存储过程 CALL 名称(…);==
CALL test1();

在sql语句中定义变量

CREATE PROCEDURE test2()
BEGIN
-- 使用DECLARE 声明一个变量  保存sql中的内容 或 产生的结果
-- DECLARE stuName VARCHAR(32) DEFAULT '李';

DECLARE stuName VARCHAR(32) ;
-- 通过 SET 变量名=值;
SET stuName='张三';

-- 通过 INTO 将值 赋值给变量
SELECT student.studentName INTO stuName 
 FROM student WHERE studentNo = 10001;
-- 显示变量中值
SELECT stuName;
END;

-- 删除存储过程 
DROP PROCEDURE test2;

CALL test2();

在sql语句中定义全局变量

CREATE PROCEDURE test3()
BEGIN
  DECLARE stuName VARCHAR(32);
  DECLARE gName VARCHAR(32);
  BEGIN
--     DECLARE stuName VARCHAR(32); -- 局部变量 只能在 BEGIN END 之间使用
    SELECT student.studentName INTO stuName FROM student WHERE studentNo = 10001;
  END;
  BEGIN
--     DECLARE gName VARCHAR(32);
    SELECT gradeName INTO  gName FROM grade WHERE gradeID=1;
    SELECT gName,stuName;
  END;
END;

DROP PROCEDURE test3;
CALL test3();

存储过程中的传参

CREATE PROCEDURE 存储过程名称 (IN OUT INOUT)

1. IN (默认值) 输入型参数 表示该参数 ，必须在调用存储过程时 传入存储过程 并 在存储过程中该值不能被返回
2. OUT 输出型参数 该值表示在存储过程中内部可以改变并可以返回
3. INOUT 输入输出型参数 调用时指定 并且可以被修改和返回

存储过程中的输入型参数 in

CREATE PROCEDURE test4(in stuNo int)
BEGIN
  DECLARE stuName VARCHAR(32);
  BEGIN
    SELECT student.studentName INTO stuName FROM student WHERE studentNo = stuNo;
    SELECT stuName;
  END;
END;

DROP PROCEDURE test4;

CALL test4(10000);

存储过程中的输出型参数 OUT

-- 在调用存储过程时 传入studentNo返回学生name
CREATE PROCEDURE test5(in stuNo int,out stuName VARCHAR(32))
BEGIN
  BEGIN
    SELECT studentName INTO stuName FROM student WHERE studentNo=stuNo;
  END;
END;

-- 声明变量  接收test5返回的学生姓名 
SET @studentName='';
CALL test5(10000,@studentName);
SELECT @studentName;

inout 可以传入 传出

CREATE PROCEDURE test6(INOUT stuNo int,INOUT stuName VARCHAR(32))
BEGIN
  SELECT studentNo,studentName INTO stuNo,stuName
  FROM student WHERE studentNo = stuNo;
END;

SET @studentNo =10001;
set @studentName='';
CALL test6(@studentNo,@studentName);
SELECT @studentNo,@studentName;

案例演示

– 通过学生编号 为学生的成绩加分 如果 小于60分加5分，大于60 小于80 加2 分 大于80 小于90加1分

CREATE PROCEDURE test7(in stuNo int,in subNo int,out mgs VARCHAR(32))
BEGIN
  -- 声明学生成绩 变量
  DECLARE stuResult int;
  SELECT studentResult INTO stuResult FROM result WHERE studentNo=stuNo AND subjectNo=subNo;  

  if(stuResult<60)
  THEN
    UPDATE result set studentResult=studentResult+5 WHERE studentNo=stuNo AND subjectNo=subNo;  
    SET mgs='1';
  ELSEIF(stuResult>60 AND stuResult<80)
  THEN
    UPDATE result set studentResult=studentResult+2 WHERE studentNo=stuNo AND subjectNo=subNo;  
    SET mgs='1';
  ELSEIF(stuResult>80 AND stuResult<90)
  THEN
    UPDATE result set studentResult=studentResult+1 WHERE studentNo=stuNo AND subjectNo=subNo;
    SET mgs='1';
  ELSE
    SET mgs='0';
  END if;
END;

SET @msg='';
CALL test7(10007,3,@msg);
SELECT @msg;

CREATE PROCEDURE test10(in gradeId int)
BEGIN
  DECLARE gradeName VARCHAR(32);
  CASE gradeId
  WHEN 1  THEN 
    SET gradeName='一年级';
  WHEN 2  THEN 
    SET gradeName='二年级';
  WHEN 3  THEN 
      SET gradeName='三年级';
  ELSE
      SET gradeName='else块';
  END CASE;
SELECT gradeName;
END;

CALL test10(2);

存储过程中的循环

CREATE PROCEDURE test8()
BEGIN
  DECLARE i int DEFAULT 0;
  WHILE(i<10) 
  do 
  SELECT i;
  SET i=i+1;
  INSERT INTO tTable(count)values(i);
  END WHILE;
END;

CALL test8();

 -- 如果i>=10则跳出循环
CREATE PROCEDURE  test9()
BEGIN
    DECLARE i int DEFAULT 0;  
    repeat --循环关键字
    BEGIN
      SELECT i;
      SET i=i+1;
      INSERT INTO tTable(count)values(i);
    END;
    UNTIL i>=10
    END repeat;
END;

CALL test9();

触发器

• 触发器与函数，存储过程一样，触发器是一种对象，根据对表的操作时间，或者触发动作

• 动作可以使 新增 删除 修改等操作

• 创建触发器

简单意思为 对一个表的增删改查会影响另一个表的状态

CREATE TRIGGER tr_account_insert AFTER INSERT ON account
FOR EACH ROW
BEGIN
  UPDATE tTable SET count=count+1;
END;
INSERT INTO `myschool`.`account` (`name`, `cash`) VALUES ('D', '1500.00');

– 通过CREATE TRIGGER 触发器名 创建触发器
– 什么时间触发？ AFTER INSERT ON account
– AFTER / BEFORE
– 对什么事件触发 INSERT UPDATE DELETE
– 影响范围 FOR EACH ROW