MySQL存储过程

李木子QQ

已于 2024-04-09 14:38:40 修改

阅读量821

点赞数 12

文章标签： mysql 存储过程

于 2024-04-09 14:34:55 首次发布

本文链接：https://blog.youkuaiyun.com/qq_33715866/article/details/137551371

版权

-- MySQL存储过程(属于数据对象)(Java 方法)(优点:1:编译好存储在服务器
-- (1:执行效率高(存储过程的语句都是先编译(创建存储过程时候就编译好了)))
-- 2:安全性高(把不公开的数据或者业务封装在存储过程中,外部不需要知道内部的实现细节)
-- 3:减少网络流量
-- 4:支持模块化)

SHOW PROCEDURE STATUS;//显示数据库中所有的存储过程

SHOW CREATE PROCEDURE proc;//显示某个存储过程的详细信息(proc为存储过程名称)

DROP PROCEDURE IF EXISTS proc;//删除某个存储过程(proc为存储过程名称)

CALL proc();//调用存储过程(proc为存储过程名称)

结构：
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc()
   BEGIN
   SELECT '存储过程';
   END //
示例：
1）
   DELIMITER //
   CREATE PROCEDURE proc()
   BEGIN
   DECLARE num INT(3);
   SET num=10;
   SELECT num+20;
   END //
调用存储过程：CALL proc(); =>21
2）
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc()
   BEGIN
   DECLARE num INT(3) DEFAULT 1;
   SELECT num+20;
   END //
调用存储过程：CALL proc(); =>21
3）
   -- in 参数（参数输入模式）
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3),j INT(3))
   BEGIN
   SELECT i+j;
   END //
调用存储过程：CALL proc(1, 1); =>2
4）
   -- out 参数（参数输出模式）
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3),j INT(3),OUT result INT(3))
   BEGIN
   SET result=i+j;
   END //
调用存储过程：SET @result=10;//在此处还没有发现此步骤的用处
       CALL proc(1, 1, @result);
SELECT @result; =>2
5）
   -- inout 参数(即为输入参数也是输出参数)
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3),j INT(3),INOUT result INT(3))
   BEGIN
   SELECT result AS result计算之前的值;
   SET result=i+j;
   END //
调用存储过程：SET @result=10;
           CALL proc(1, 1, @result); =>10（result计算之前的值）
           SELECT @result; =>2
6）
   -- IF 语句
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3))
   BEGIN
   IF i<10 THEN SELECT '小于10';
   ELSEIF i>10 THEN SELECT '大于10';
   ELSE SELECT '等于10';
   END IF;
   END //
调用存储过程：CALL proc(8); =>小于10
           CALL proc(10); =>等于10
           CALL proc(18); =>大于10
7）
   -- CASE 语句
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3))
   BEGIN
   CASE i
       WHEN 1 THEN SELECT '星期一';
       WHEN 2 THEN SELECT '星期二';
       WHEN 3 THEN SELECT '星期三';
       WHEN 4 THEN SELECT '星期四';
       WHEN 5 THEN SELECT '星期五';
       WHEN 6 THEN SELECT '星期六';
       WHEN 7 THEN SELECT '星期天';
       ELSE SELECT '错误的数据';
   END CASE;
   END //
调用存储过程：CALL proc(5); =>星期五
8）
   -- LOOP 循环
   8.1）
       DROP PROCEDURE IF EXISTS proc;
       DELIMITER //
       CREATE PROCEDURE proc(i INT(3))
       BEGIN
           DECLARE num INT(3) DEFAULT 10; -- 定义局部变量(循环的次数)
           myloop:LOOP -- 定义循环的块名称
               IF num>i THEN LEAVE myloop; -- 判断循环次数是否大于传递进来的参数,大于则退出 myloop 循环块
               END IF; -- 判断结束
               SELECT num; -- 循环没有结束输出
               SET num=num+1; -- 循环变量递增
           END LOOP myloop; -- myloop块的结束
       END //
   调用存储过程：CALL proc(11); =>10
               CALL proc(9); =>[SQL]CALL proc(9);受影响的行: 0时间: 0.000s（现在还不是很懂什么意思）
   8.2）
       DROP PROCEDURE IF EXISTS proc;
       DELIMITER //
       CREATE PROCEDURE proc(i INT(3))
       BEGIN
       DECLARE num INT(3) DEFAULT 10; -- 定义局部变量(循环的次数)
       myloop:LOOP -- 定义循环的块名称
       SELECT num; -- 循环没有结束输出
       SET num=num+1; -- 循环变量递增
       IF num<i THEN ITERATE myloop; -- 当num<i 的时候流程跳转到myloop开始位置
       ELSE LEAVE myloop; -- 当num<i 不满足是流程跳到myloop结束的位置
       END IF;
       END LOOP myloop; -- myloop块的结束
       END //
   调用存储过程：CALL proc(11); =>10
               CALL proc(9); =>10
9）
   -- while 语句
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3))
   BEGIN
   DECLARE num INT(3) DEFAULT 1; -- 定义局部变量(循环的次数)
   WHILE num<i DO
   SELECT num;
   SET num=num+1;
   END WHILE;
   END //
调用存储过程：CALL proc(3); =>会出现多个结果集
10）
   -- REPEAT UNTIL 循环
   DROP PROCEDURE IF EXISTS proc;
   DELIMITER //
   CREATE PROCEDURE proc(i INT(3))
   BEGIN
   DECLARE num INT(3) DEFAULT 1; -- 定义局部变量(循环的次数)
   REPEAT -- 开始循环
   SELECT num;
   SET num=num+1;
   UNTIL num>i -- 循环的条件(当条件满足则退出循环)
   END REPEAT;
   END //
调用存储过程：CALL proc(3); =>10（还不太懂）
11）
   -- 函数(不能有select语句) 必须定义返回值在函数体内必须有return语句
   DROP FUNCTION IF EXISTS myfun;
   DELIMITER //
   CREATE FUNCTION myfun(i INT(3)) RETURNS INT(3)
   BEGIN
   DECLARE mysum INT DEFAULT 0;
   DECLARE num INT DEFAULT 1;
   WHILE num<=i DO
       SET mysum=mysum+num;
       SET num=num+1;
   END WHILE;
   RETURN mysum;
   END //
调用存储过程：CALL proc(3); =>10

-- 在创建函数时报1418错误
SET GLOBAL log_bin_trust_function_creators=TRUE;

-- mysql中遇到;就开始执行

-- 函数(不能有select语句) 必须定义返回值在函数体内必须有return语句
DROP FUNCTION IF EXISTS myfun;
DELIMITER //
CREATE FUNCTION myfun(i INT(3)) RETURNS INT(3)
BEGIN
DECLARE mysum INT DEFAULT 0;
DECLARE num INT DEFAULT 1;
WHILE num<=i DO
SET mysum=mysum+num;
SET num=num+1;
END WHILE;
RETURN mysum;
END //

SHOW CREATE TABLE student

CREATE TABLE `student` (
`stuId` INT(3) NOT NULL AUTO_INCREMENT,
`stuName` VARCHAR(20) NOT NULL,
`stuSex` ENUM('男','女') DEFAULT NULL,
`stuAge` INT(3) DEFAULT NULL,
`cid` INT(3) DEFAULT NULL,
PRIMARY KEY (`stuId`),
KEY `cid` (`cid`),
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `class` (`classId`)
) ENGINE=INNODB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

SELECT * FROM STUDENT1

CREATE TABLE student1 AS (SELECT * FROM student WHERE 1=2)

-- 调用存储过程
CALL proc(10)

-- 调用函数
SELECT myfun(100)

-- 触发器(特殊的存储过程)
-- 1:自动调用的
-- 触发器也是数据对象(触发器按照执行的顺序分为 1:after 触发器 2:before触发器
-- 按照监听的动作分为 1:insert 触发器 2:update 触发器 3:delete 触发器)

-- SQL 触发器(自动产生两张内存表 new(新进来的数据) old(要删除的数据或者要修改的数据))

DROP TRIGGER IF EXISTS mytrigger;
DELIMITER //
CREATE TRIGGER mytrigger AFTER INSERT ON student FOR EACH ROW
BEGIN
INSERT INTO student1(stuId,stuName,stuSex,stuAge,cid)
VALUES(new.stuId,new.stuname,new.stuSex,new.stuage,new.cid);
END //

-- sqlsever(MS) mysql

-- 监听的对象是student
-- 事件类型是delete
-- 顺序是before
-- for each row (除了mysql以外的数据库都不是必须的)行级触发器语句级触发器
DROP TRIGGER IF EXISTS mytrigger;
DELIMITER //
CREATE TRIGGER mytrigger BEFORE DELETE ON student FOR EACH ROW
BEGIN
DELETE FROM student1 WHERE stuId=old.stuId;
END //

SELECT * FROM student1

SELECT * FROM student

INSERT INTO student(stuId,stuName,stuSex,stuAge,cid)
VALUES(13,'触发器','男',33,4);

SELECT * FROM class

INSERT INTO student1(stuId,stuName,stuSex,stuAge,cid)
VALUES(new.stuId,new.stuName,new.stuSex,new.stuAge,new.cid)

DELETE FROM student WHERE stuId=13

DELETE FROM student WHERE stuId>8