存储过程
存储过程是在大型的数据库中,一组为了完成特定功能的SQL语句集经过第一编辑后调用不需要再次编译,用户通过指定的存储过程的名字并给出来执行存储过程。
为了以后使用而保存一条或多条SQL语句集
- 为了处理封装在容易使用的单元中,简化复杂咋做
- 为了防止错误 保证了数据的一致性
- 简化对变动管理
- 提高性能
- 灵活
缺点 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)
- IN (默认值) 输入型参数 表示该参数 ,必须在调用存储过程时 传入存储过程 并 在存储过程中该值不能被返回
- OUT 输出型参数 该值表示在存储过程中内部可以改变并可以返回
- 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