mysql存储过程

最新推荐文章于 2024-10-13 21:08:42 发布

吕禹哲

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量275

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/maziqiang__/article/details/122915355

版权

-- 首先创建一个存储过程就类似于创建了一个方法但是没有大括号的存在用begin和end代替
-- 另外首先就要重写结束符号因为在sql中分号是表示执行完毕的意识但是存储过程
DELIMITER $$;
CREATE PROCEDURE pro_demo1()
BEGIN
SELECT 'hello';
END $$;
-- 这就是创建出一个存储过程的基本语法，首先要重写结束符号，然后在创建存储过程procedure，然后就写方法名
-- 方法的大括号就用begin和end来表示存储过程的代码都是有开始就有结束的，这个要注意

-- 调用使用call关键字就相当于调用方法一样
CALL pro_demo1();

DELIMITER $$;
CREATE PROCEDURE pro_demo2()
BEGIN
SELECT * FROM t_employees;
END $$;

CALL pro_demo2();

-- 存储过程中的三种变量局部变量会话变量/用户变量只保存在一个会话中其他的会话就访问不了系统变量

-- 局部变量
DELIMITER $$;
CREATE PROCEDURE pro_demo3()
BEGIN
   -- 首先先创建变量首先需要关键字 declare 变量名数据类型
   DECLARE v_id INT(4);
   -- 无法在声明的时候声明并赋值只能先给默认值
   DECLARE v_name VARCHAR(20) DEFAULT '张三';
   -- 然后给局部变量赋值要结合 set 关键字 =不是赋值的意思是前面有了一个set 才让他具有赋值的意思
   SET v_id = 66;
   SET v_name = '吕军培';
   -- 然后光声明不使用也是没有用的
   SELECT v_id,v_name;
END $$;

-- 不能修改只能先删除再创建
DROP PROCEDURE pro_demo3;

SELECT v_id;

CALL pro_demo3();

-- 会话变量用户变量
DELIMITER $$;
CREATE PROCEDURE pro_demo4()
BEGIN
   -- 会话变量不用声明
   -- 直接赋值的时候就可以顺便帮忙创建出来了用户变量在前面加上一个@
   SET @v_name = '哇哈哈';

END $$;

-- 要先调用方法才会给这个用户变量赋值
CALL pro_demo4();

-- 用户变量只在当前用户上有效
SELECT @v_name;

-- 用户变量也可以直接在外部创建使用相同变量名会直接覆盖
SET @v_name = '哦呵呵';
SET @v_name1 = '干干干';

SELECT @v_name1;

-- 参数类型 in 传入(输入)参数输出参数 out inout 既可以输入参数也可以输出参数

-- in输入参数类型
DELIMITER $$;
CREATE PROCEDURE pro_demo5(IN v_num INT)
BEGIN
   -- 可以直接拿这个传入的参数进行赋值
   SET v_num = v_num+10;
   SELECT v_num;
END $$;

-- 直接可以传入参数进行使用
CALL pro_demo5(200);

-- 创建变量另外的用途可以把查询到的值赋值到创建出来的变量中
DELIMITER $$;
CREATE PROCEDURE pro_demo6()
BEGIN
   -- 不过缺点就是一个变量只能存储一个值不能存储多个值
   DECLARE v_name VARCHAR(20);
   -- 直接在查询语句上面用into关键字将值赋值给这个新的变量中去
   SELECT first_name INTO v_name FROM t_employees WHERE employee_id = 100;

   SELECT v_name;
END $$;

CALL pro_demo6();

-- 参数 out 就是调用方法的时候可以用一个形参来代表要调用的时候实参要提供一个变量来接收返回的结果
-- 相当于一个返回值一样
DELIMITER $$;
CREATE PROCEDURE pro_demo7(OUT result INT)
BEGIN
SET result = 1000;
END $$;

-- 使用用户变量来接受返回值
CALL pro_demo7(@v_result);
-- 使用输出
SELECT @v_result;

-- inout 可以作为传入也可以作为输出

DELIMITER $$;
CREATE PROCEDURE pro_demo8(INOUT result VARCHAR(20))
BEGIN
SET result = CONCAT("欢迎您:",result);
END $$;

-- 首先要传入一个有值的变量给它
SET @v_name = '吕军培';
-- 然后传入时会将传入的值做操作然后再返回出去
CALL pro_demo8(@v_name);

SELECT @v_name;

-- 流程控制语句 if 控制语句写完if马上写 end if
DELIMITER $$;
CREATE PROCEDURE pro_demo9(IN num INT)
BEGIN
   -- 先设置一个变量用于接收结果 if - then - else
   DECLARE result VARCHAR(20);
   IF -- if后面没有小括号所以就可以直接写语句就可以
       num >= 8000 THEN SET result = '有钱人';
   ELSEIF -- elseif 是连在一起的不要用空格隔开
       num >= 6000 THEN SET result = '一般人';
   ELSE
       SET result = '穷人';
   END IF;
   SELECT result;
END $$;

CALL pro_demo9(5999);

-- 流程控制语句 case 语句 when then 就有点类似于我们的switch
DELIMITER $$;
CREATE PROCEDURE pro_demo10(IN num INT)
BEGIN
   CASE num
       WHEN 1 THEN SET @result = '星期一';
       WHEN 2 THEN SET @result = '星期二';
       WHEN 3 THEN SET @result = '星期三';
       WHEN 4 THEN SET @result = '星期四';
       WHEN 5 THEN SET @result = '星期五';
       WHEN 5 THEN SET @result = '星期五';
       ELSE SET @result = '星期天';
   END CASE;

   SELECT @result;
END $$;

CALL pro_demo10(1);

-- 循环结构 while - end while
DELIMITER $$;       -- 循环初始条件
CREATE PROCEDURE pro_demo11(IN num INT)
BEGIN
   DECLARE word VARCHAR(255) DEFAULT '';
   -- while() 相当于后面加一个小括号里面直接写上我们的条件就可以了循环条件
   WHILE num > 0
   -- do就相当于我们大括号的开始
   DO
       -- 循环要做的事情
       SET word = CONCAT(word,'--',num);
       -- 迭代部分
       SET num = num -1;
   END WHILE;
   SELECT word;
END $$;

-- 调用while循环
CALL pro_demo11(10);

-- loop 循环
DELIMITER $$;       -- 循环初始条件
CREATE PROCEDURE pro_demo12(IN num INT)
BEGIN
   DECLARE word VARCHAR(255) DEFAULT '';
   v_loop:LOOP -- 只有循环的功能没有帮忙跳出的功能只能手动跳出就相当于我们java的while(true){}
       -- 需要 if else来帮忙循环
       IF
           num = 0 THEN LEAVE v_loop;
       ELSE
           -- 循环要做的事情
           SET word = CONCAT(word,'--',num);
           -- 迭代部分
           SET num = num -1;
       END IF;
   END LOOP;
   -- 查询显示结果
   SELECT word;
END $$;

CALL pro_demo12(50);

-- 相当于java中的
-- while（true）{
--    if（num == 0）{
--        break;
--    }else{
--        word = word + num;
--        num--;
--
--    }
--
-- }

-- 还有一个 break 就是跳过本次循环执行下一次循环
DELIMITER $$;       -- 循环初始条件
CREATE PROCEDURE pro_demo13(IN num INT)
BEGIN
   DECLARE word VARCHAR(255) DEFAULT '';
   v_loop:LOOP -- 只有循环的功能没有帮忙跳出的功能只能手动跳出就相当于我们java的while(true){}
       -- 需要 if else来帮忙循环
       SET word = CONCAT(word,'--',num);
       -- 迭代部分
       SET num = num -1;
       IF
           num = 0 THEN LEAVE v_loop;
       ELSE
           SELECT '1';
           ITERATE v_loop; -- ITERATE 就相当于java中的break;
       END IF;
   END LOOP;
   -- 查询显示结果
   SELECT word;
END $$;

CALL pro_demo13(5);

-- 游标的使用有8步
DELIMITER $$;
CREATE PROCEDURE pro_demo14()
BEGIN
   -- 1 定义要存值的变量
   DECLARE v_userid INT;
   DECLARE v_userName VARCHAR(20);
   -- 2 声明要用于表示游标找不到数据的标识变量默认是0代表有值，1代表没有值
   DECLARE v_flag INT DEFAULT 0;
   -- 3 声明游标用声明关键字
   DECLARE v_cur CURSOR FOR SELECT userId , username FROM `user`;
   -- 4 设置当游标找不到数据时标识变量的值为多少
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_flag = 1;
   -- 5 使用打开游标
   OPEN v_cur;
   -- 6 通过循环变量来对游标中存储的结果集做操作这里也可以替换为loop循环
   WHILE v_flag != 1
       DO
       -- 7 通过fetch关键字来设置游标指向数据,数据取出顺序和数据查询是的字段顺序一致,注意数据类型和个数
       -- 也就是在游标中取值赋值
       FETCH v_cur INTO v_userid,v_userName;
       SELECT v_userid,v_userName;
   END WHILE;
   -- 8 关闭游标
   CLOSE v_cur;
END $$;

CALL pro_demo14();

-- 触发器 trigger
-- 首先创建一个旧的表 old new 对象 old就是修改、删除之前的老数据 new就是修改、新增之后的新数据
CREATE TABLE old_emp AS SELECT * FROM teacher WHERE 1 = 2;

-- 创建触发器共有五种情况进行组合 before after update delete insert
CREATE TRIGGER emp_del BEFORE DELETE
ON teacher FOR EACH ROW -- ON teacher FOR EACH ROW 在教师表中的每行受到影响时
-- 在删除任意一行的时候都对老数据触发一个新增到老表做一个备份操作
INSERT INTO old_emp VALUES (old.tno,old.tname,old.tsex,old.tbirthday,old.prof,old.depart);

-- 除此之外还可以组合成任意的逻辑

DELETE FROM teacher WHERE tno = 866;