您的位置:新葡亰496net > 网络数据库 > 新葡亰496net存储过程,MySQL中的存储过程

新葡亰496net存储过程,MySQL中的存储过程

发布时间:2019-07-27 06:26编辑:网络数据库浏览(109)

    什么是存储过程

    简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;

    ps:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用;

    一、存储过程

    MySQL存储过程及触发器,mysql存储过程

    一、存储过程

    存储过程的基本格式如下:

    -- 声明结束符
    -- 创建存储过程
    DELIMITER $  -- 声明存储过程的结束符
    CREATE PROCEDURE pro_test()    --存储过程名称(参数列表)
    BEGIN
        -- 可以写多个sql语句;      -- sql语句 流程控制
        SELECT * FROM employee;
    END $  -- 结束 结束符
    
    -- 执行存储过程
    CALL pro_test();   -- CALL 存储过程名称(参数);
    
    -- 删除存储过程
    DROP PROCEDURE pro_test;
    
    参数:
    IN:   表示输入参数,可以携带数据带存储过程中
    OUT: 表示输出参数,可以从存储过程中返回结果
    INOUT: 表示输入输出参数,既可以输入功能,也可以输出功能
    

     

    1. 带有输入参数的存储过程

        需求:传入一个员工的id,查询员工信息

    DELIMITER $
    CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
    BEGIN
        SELECT * FROM employee WHERE id=eid;
    END $ 
    
    -- 调用
    CALL pro_findById(4);
    

     

    2. 带有输出参数的存储过程

    DELIMITER $
    CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT:输出参数
    BEGIN
            -- 给参数赋值
        SET str='hellojava';
    END $
    

    如何接受返回参数的值呢?这里涉及到MySQL的变量

     

    MySQL变量一共有三种:

    全局变量

    全局变量又叫内置变量,是mysql数据库内置的变量 ,对所有连接都起作用。

    查看所有全局变量: show variables

    查看某个全局变量: select @@变量名

    修改全局变量: set 变量名=新值

    character_set_client: mysql服务器的接收数据的编码

    character_set_results:mysql服务器输出数据的编码

     

    会话变量

    只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!

    定义会话变量: set @变量=值

    查看会话变量: select @变量

    局部变量

    在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失。

     

    回到上面这个存储过程,如何接受返回参数的值呢?

    新葡亰496net存储过程,MySQL中的存储过程。定义一个会话变量name, 使用name会话变量接收存储过程的返回值

    CALL pro_testOut(@NAME);

    查看变量值

    SELECT @NAME;

     

    3. 带有输入输出参数的存储过程

    DELIMITER $
    CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT: 输入输出参数
    BEGIN
       -- 查看变量
       SELECT n;
       SET n =500;
    END $
    
    -- 调用
    SET @n=10;
    
    CALL pro_testInOut(@n);
    
    SELECT @n;
    

     

    4. 带有条件判断的存储过程

    需求:输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。其他数字,返回“错误输入”;

    DELIMITER $
    CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
    BEGIN
        IF num=1 THEN
            SET str='星期一';
        ELSEIF num=2 THEN
            SET str='星期二';
        ELSEIF num=3 THEN
            SET str='星期三';
        ELSE
            SET str='输入错误';
        END IF;
    END $
    
    --调用
    CALL pro_testIf(4,@str);
    
    SELECT @str;
    

     

    5. 带有循环功能的存储过程

    需求: 输入一个整数,求和。例如,输入100,统计1-100的和

    DELIMITER $
    CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
    BEGIN
        -- 定义一个局部变量
        DECLARE i INT DEFAULT 1;
        DECLARE vsum INT DEFAULT 0;
        WHILE i<=num DO
              SET vsum = vsum i;
              SET i=i 1;
        END WHILE;
        SET result=vsum;
    END $
    
    --调用
    CALL pro_testWhile(100,@result);
    
    SELECT @result;
    

     

    6. 使用查询的结果赋值给变量(INTO)

    DELIMITER $
    CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
    BEGIN
        SELECT empName INTO vname FROM employee WHERE id=eid;
    END $
    
    --调用
    CALL pro_findById2(1,@NAME);
    
    SELECT @NAME;
    

     

    二、 触发器

    触发器作用:当操作了某张表时,希望同时触发一些动作/行为,可以使用触发器完成。

    需求: 当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据

    CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
         INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
    

    以上触发器是基于insert的,以下两个是基于update和delete的。

    -- 创建触发器(修改)
    CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
         INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
    
    -- 创建触发器(删除)
    CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
         INSERT INTO test_log(content) VALUES('员工表删除了一条记录');
    

     

    说明:

    1. MySQL默认将分号,即“;”作为语句的分隔符。如果是这样的话,则一个存储过程将很难正常创建,因为它的BEGIN和END之间可以是任意数量的SQL语句,而每条SQL语句都以分号结束。所以,在创建存储过程时,必须重新定义分隔符。在上述用例中,均将“$”作为新的分隔符,注意,在创建完存储过程后,必须重新设置回原来的分隔符“;”。

    以上基于传智播客JAVA就业班视频MySQL相关内容整理。

    一、存储过程 存储过程的基本格式如下: -- 声明结束符 -- 创建存储过程 DELIMITER $ -- 声明存储过程的...

    存储过程

    创建存储过程:

    有哪些特性

    有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;

    函数的普遍特性:模块化,封装,代码复用;

    速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;

     

    存储过程的基本格式如下:

    创建存储过程

    DELIMITER $       -- 声明存储过程的结束符
    CREATE PROCEDURE pro_test()           --存储过程名称(参数列表)
    BEGIN             -- 开始
        -- 可以写多个sql语句;          -- sql语句 流程控制
        SELECT * FROM employee;
    END $            -- 结束 结束符
    
    -- 定义结束符号
    DELIMITER $
    CREATE PROCEDURE pro_test() 
    BEGIN
        SELECT * FROM employee; 
    END $
    
    -- 调用存储过程
    CALL pro_test();
    

    存储过程的基本格式如下:

    新葡亰496net 1

    -- 声明结束符
    -- 创建存储过程
    DELIMITER $  -- 声明存储过程的结束符
    CREATE PROCEDURE pro_test()    --存储过程名称(参数列表)
    BEGIN
        -- 可以写多个sql语句;      -- sql语句 流程控制
        SELECT * FROM employee;
    END $  -- 结束 结束符
    
    -- 执行存储过程
    CALL pro_test();   -- CALL 存储过程名称(参数);
    
    -- 删除存储过程
    DROP PROCEDURE pro_test;
    
    参数:
    IN:   表示输入参数,可以携带数据带存储过程中
    OUT: 表示输出参数,可以从存储过程中返回结果
    INOUT: 表示输入输出参数,既可以输入功能,也可以输出功能
    

    新葡亰496net 2

     

    1. 带有输入参数的存储过程

        需求:传入一个员工的id,查询员工信息

    新葡亰496net 3

    DELIMITER $
    CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
    BEGIN
        SELECT * FROM employee WHERE id=eid;
    END $ 
    
    -- 调用
    CALL pro_findById(4);
    

    新葡亰496net 4

     

    2. 带有输出参数的存储过程

    DELIMITER $
    CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT:输出参数
    BEGIN
            -- 给参数赋值
        SET str='hellojava';
    END $
    

    如何接受返回参数的值呢?这里涉及到MySQL的变量

     

    MySQL变量一共有三种:

    全局变量

    全局变量又叫内置变量,是mysql数据库内置的变量 ,对所有连接都起作用。

    查看所有全局变量: show variables

    查看某个全局变量: select @@变量名

    修改全局变量: set 变量名=新值

    character_set_client: mysql服务器的接收数据的编码

    character_set_results:mysql服务器输出数据的编码

     

    会话变量

    只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!

    定义会话变量: set @变量=值

    查看会话变量: select @变量

    局部变量

    在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失。

     

    回到上面这个存储过程,如何接受返回参数的值呢?

    定义一个会话变量name, 使用name会话变量接收存储过程的返回值

    CALL pro_testOut(@NAME);

    查看变量值

    SELECT @NAME;

     

    3. 带有输入输出参数的存储过程

    新葡亰496net 5

    DELIMITER $
    CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT: 输入输出参数
    BEGIN
       -- 查看变量
       SELECT n;
       SET n =500;
    END $
    
    -- 调用
    SET @n=10;
    
    CALL pro_testInOut(@n);
    
    SELECT @n;
    

    新葡亰496net 6

     

    4. 带有条件判断的存储过程

    需求:输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。其他数字,返回“错误输入”;

    新葡亰496net 7

    DELIMITER $
    CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
    BEGIN
        IF num=1 THEN
            SET str='星期一';
        ELSEIF num=2 THEN
            SET str='星期二';
        ELSEIF num=3 THEN
            SET str='星期三';
        ELSE
            SET str='输入错误';
        END IF;
    END $
    
    --调用
    CALL pro_testIf(4,@str);
    
    SELECT @str;
    

    新葡亰496net 8

     

    5. 带有循环功能的存储过程

    需求: 输入一个整数,求和。例如,输入100,统计1-100的和

    新葡亰496net 9

    DELIMITER $
    CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
    BEGIN
        -- 定义一个局部变量
        DECLARE i INT DEFAULT 1;
        DECLARE vsum INT DEFAULT 0;
        WHILE i<=num DO
              SET vsum = vsum i;
              SET i=i 1;
        END WHILE;
        SET result=vsum;
    END $
    
    --调用
    CALL pro_testWhile(100,@result);
    
    SELECT @result;
    

    新葡亰496net 10

     

    6. 使用查询的结果赋值给变量(INTO)

    新葡亰496net 11

    DELIMITER $
    CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
    BEGIN
        SELECT empName INTO vname FROM employee WHERE id=eid;
    END $
    
    --调用
    CALL pro_findById2(1,@NAME);
    
    SELECT @NAME;
    

    新葡亰496net 12

     

    -- 声明结束符
    -- 创建存储过程
    DELIMITER $  -- 声明存储过程的结束符
    CREATE PROCEDURE pro_test()    --存储过程名称(参数列表)
    BEGIN
        -- 可以写多个sql语句;      -- sql语句 流程控制
        SELECT * FROM employee;
    END $  -- 结束 结束符
    
    -- 执行存储过程
    CALL pro_test();   -- CALL 存储过程名称(参数);
    
    -- 删除存储过程
    DROP PROCEDURE pro_test;
    
    参数:
    IN:   表示输入参数,可以携带数据带存储过程中
    OUT: 表示输出参数,可以从存储过程中返回结果
    INOUT: 表示输入输出参数,既可以输入功能,也可以输出功能
    

    执行存储过程

    CALL pro_test();          -- CALL 存储过程名称(参数);
    

    新葡亰496net 13

    存储过程弊端

    不同数据库,语法差别很大,移植困难,换了数据库,需要重新编写;

    不好管理,把过多业务逻辑写在存储过程不好维护,不利于分层管理,容易混乱,一般存储过程适用于个别对性能要求较高的业务,其它的必要性不是很大;

    ...

     

    参数:

    IN:   表示输入参数,可以携带数据带存储过程中
    OUT: 表示输出参数,可以从存储过程中返回结果
    INOUT: 表示输入输出参数,既可以输入功能,也可以输出功能
    

    Paste_Image.png

    1. 带有输入参数的存储过程

    带有输入参数的存储过程

    -- 需求:传入一个员工的id,查询员工信息

    DELIMITER $
    CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
    BEGIN
        SELECT * FROM employee WHERE id=eid;
    END $ 
    

    调用

    CALL pro_findById(4);
    

    *** 删除存储过程:***

        需求:传入一个员工的id,查询员工信息

    3.2 带有输出参数的存储过程
    DELIMITER $
    CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT:输出参数
    BEGIN
        -- 给参数赋值
    SET str='helljava';
    END $
    
    DROP PROCEDURE pro_testByAvg;
    
    DELIMITER $
    CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数
    BEGIN
        SELECT * FROM employee WHERE id=eid;
    END $ 
    
    -- 调用
    CALL pro_findById(4);
    
    删除存储过程

    DROP PROCEDURE pro_testOut;
    -- 调用
    -- 如何接受返回参数的值??

    mysql数据库三种变量:

     

    MySQL的变量

    全局变量(内置变量):mysql数据库内置的变量 (所有连接都起作用)

    • 查看所有全局变量: show variables
    • 查看某个全局变量: select @@变量名
    • 修改全局变量: set 变量名=新值
    • character_set_client: mysql服务器的接收数据的编码
    • character_set_results:mysql服务器输出数据的编码

    会话变量: 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!
    -- 定义会话变量: set @变量=值
    -- 查看会话变量: select @变量

    局部变量: 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失!!

    1. 定义一个会话变量name,

    2. 使用name会话变量接收存储过程的返回值

       CALL pro_testOut(@NAME);
      

    查看变量值

    SELECT @NAME;
    
    1. 全局变量。mysql内置的变量,mysql程序关闭的时候全局变量才会失效!!
      -- character_set_client: mysql接收的客户端的数据编码
      -- character_set_results: mysql使用什么编码输出给客户端数据
      -- 查看某个全局变量: select @@变量名
      -- 修改某个全局变量: set @@变量名=值
    2. 会话变量。变量只在某次登录的会话中有效!退出连接,会话变量数据失效!!
      -- 查看某个会话变量: select @变量名
      -- 修改/定义某个会话变量: set @变量名=值
    3. 局部变量:在存储过程中定义的变量。存储过程结束局部变量失效!!
      -- 查看某个局部变量: select 变量名
      -- 修改某个局部变量: set 变量名=值
      -- 定义某个局部变量: declare 变量名 数据类型;

    2. 带有输出参数的存储过程

    带有输入输出参数的存储过程
    DELIMITER $
    CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT: 输入输出参数
    BEGIN
       -- 查看变量
       SELECT n;
       SET n =500;
    END $
    

    调用

    SET @n=10;
    CALL pro_testInOut(@n);
    SELECT @n;
    

    带有输入参数的存储过程:

    DELIMITER $
    CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT:输出参数
    BEGIN
            -- 给参数赋值
        SET str='hellojava';
    END $
    

    3.4 带有条件判断的存储过程

    需求:输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。其他数字,返回“错误输入”;

    DELIMITER $
    CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
    BEGIN
    IF num=1 THEN
            SET str='星期一';
        ELSEIF num=2 THEN
            SET str='星期二';
        ELSEIF num=3 THEN
            SET str='星期三';
        ELSE
            SET str='输入错误';
        END IF;
    END $
    
    CALL pro_testIf(4,@str);
    
    SELECT @str;
    
    -- 需求: 传入员工id查询对应的员工
    DELIMITER $
    CREATE PROCEDURE pro_testByIn(IN eid INT)  -- 参数类型(IN) 参数名称 数据类型(int)
    BEGIN
        SELECT * FROM employee WHERE id=eid;
    END $
    
    -- 调用
    CALL pro_testByIn(2);
    

    如何接受返回参数的值呢?这里涉及到MySQL的变量

    带有循环功能的存储过程

    需求: 输入一个整数,求和。例如,输入100,统计1-100的和

    DELIMITER $
    CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
    BEGIN
        -- 定义一个局部变量
        DECLARE i INT DEFAULT 1;
        DECLARE vsum INT DEFAULT 0;
        WHILE i<=num DO
              SET vsum = vsum i;
              SET i=i 1;
        END WHILE;
        SET result=vsum;
    END $
    
    DROP PROCEDURE pro_testWhile;
    
    
    CALL pro_testWhile(100,@result);
    
    SELECT @result;
    
    USE day16;
    

    带有输出参数的存储过程:

     

    使用查询的结果赋值给变量(INTO)
    DELIMITER $
    CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
    BEGIN
        SELECT empName INTO vname FROM employee WHERE id=eid;
    END $
    
    CALL pro_findById2(1,@NAME);
    
    SELECT @NAME;
    
    DELIMITER $
    CREATE PROCEDURE pro_testByOut(OUT n VARCHAR(20))
    BEGIN
        -- 修改变量n(在存储过程中,n(会话变量)被当成临时变量处理 )
        SET n = '输出参数';
    END $           
    -- 定义会话变量去接收输出参数数据          
    -- set @n='eric';
    CALL pro_testByOut(@n);
    -- 查看会话变量n
    SELECT @n;
    

    MySQL变量一共有三种:

    触发器

    带有输入输出参数的存储过程:

    全局变量

    触发器作用

    当操作了某张表时,希望同时触发一些动作/行为,可以使用触发器完成!!
    例如: 当向员工表插入一条记录时,希望同时往日志表插入数据

    需求: 当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据

    DELIMITER $
    CREATE PROCEDURE pro_testByInOut(INOUT n VARCHAR(20))
    BEGIN
        -- 查看n变量
        SELECT n;
        -- 修改n变量
        SET n = '500';
    END $
    -- 定义会话变量调用存储过程
    SET @n='100';
    CALL pro_testByInOut(@n);
    -- 查看n
    SELECT @n;
    

    全局变量又叫内置变量,是mysql数据库内置的变量 ,对所有连接都起作用。

    创建触发器(添加)
     CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
    

    插入数据

    INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);
    INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);
    

    创建触发器(修改)

    CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
    

    修改

    UPDATE employee SET empName='eric' WHERE id=7;
    

    创建触发器(删除)

    CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
    INSERT INTO test_log(content) VALUES('员工表删除了一条记录');
    

    删除

    DELETE FROM employee WHERE id=7;
    

    带有判断条件的存储过程:

    查看所有全局变量: show variables

    mysql权限问题

    -- mysql数据库权限问题:root :拥有所有权限(可以干任何事情)
    -- 权限账户,只拥有部分权限(CURD)例如,只能操作某个数据库的某张表
    -- 如何修改mysql的用户密码?
    -- password: md5加密函数(单向加密)

    SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

    -- mysql数据库,用户配置 : user表

    USE mysql;
    
    SELECT * FROM USER;
    

    -- 修改密码

    UPDATE USER SET PASSWORD=PASSWORD('123456') WHERE USER='root';
    

    -- 分配权限账户

    GRANT SELECT ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
    GRANT DELETE ON day16.employee TO 'eric'@'localhost' IDENTIFIED BY '123456';
    
    需求: 输入一个num整数,num=1 ,输出‘星期一’,num=2,输出‘星期二’,num=3,输出‘星期三’,否则,输出‘错误参数’
    DELIMITER $
    CREATE PROCEDURE pro_testByIf(IN num INT,OUT str VARCHAR(20))
    BEGIN
        IF num=1 THEN
            SET str = '星期一';
        ELSEIF num= 2 THEN
            SET str ='星期二';
        ELSEIF num=3 THEN
            SET str = '星期三';
        ELSE
            SET str = '错误参数';
        END IF;
    END $
    
    CALL pro_testByIf(5,@str);
    SELECT @str;
    

    查看某个全局变量: select @@变量名

    带有循环条件的存储过程:

    修改全局变量: set 变量名=新值

    需求: 输入一个num,计算从1到num的总和。
    DELIMITER $
    CREATE PROCEDURE pro_testByWhile(IN num INT,OUT score INT)
    BEGIN
        -- int result =0;
        -- for(int i=1;i<=100;i  ){
        --  result  = i;
        -- }
    
        -- 定义局部变量
        DECLARE i INT DEFAULT 1;
        DECLARE result INT DEFAULT 0;
        WHILE i<=num DO
            SET result = result   i;
            SET i = i 1;
        END WHILE;
        SET score = result;
    END $
    
    CALL pro_testByWhile(200,@score);
    SELECT @score;
    

    character_set_client: mysql服务器的接收数据的编码

    携带数据库的数据给输出参数(INTO):

    character_set_results:mysql服务器输出数据的编码

     需求: 传入员工id,查询对应的员工,输出员工姓名
    DELIMITER $
    CREATE PROCEDURE pro_testByData(IN eid INT,OUT sname VARCHAR(20))
    BEGIN
        SELECT NAME INTO sname FROM employee WHERE id=eid;
    END $
    
    CALL pro_testByData(2,@sname);
    SELECT @sname;
    

     

    存储过程练习:

    会话变量

    在student表的数据基础上,设计一个存储过程:
    
        1)计算出所有学生的英语平均分
        2)
            如果平均分,大于80分,且小于等于100分,输出'优秀'
            如果平均分,大于等于60分,且小于等于80分,输出'良好'
            如果平均分,小于60分,输出'不及格'
    USE day14;
    
    SELECT * FROM student2;
    
    DELIMITER $
    CREATE PROCEDURE pro_testByAvg(OUT str VARCHAR(20),OUT tavg FLOAT)
    BEGIN
        DECLARE savg FLOAT DEFAULT 0.0; -- 定义一个局部变量
        SELECT AVG(english) INTO savg FROM student2; -- 把查询到的结果放到变量中
        IF savg>80 AND savg<=100 THEN
            SET str = '优秀'; 
        ELSEIF savg>=60 AND savg<=80 THEN
            SET str = '良好';
        ELSE 
            SET str = '不及格';
        END IF;
        SET tavg = savg;    
    END $
    
    CALL pro_testByAvg(@str,@tavg);
    SELECT  @str;
    SELECT @tavg;
    

    只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!

    定义会话变量: set @变量=值

    查看会话变量: select @变量

    局部变量

    在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失。

     

    回到上面这个存储过程,如何接受返回参数的值呢?

    定义一个会话变量name, 使用name会话变量接收存储过程的返回值

    CALL pro_testOut(@NAME);

    查看变量值

    SELECT @NAME;

     

    3. 带有输入输出参数的存储过程

    DELIMITER $
    CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT: 输入输出参数
    BEGIN
       -- 查看变量
       SELECT n;
       SET n =500;
    END $
    
    -- 调用
    SET @n=10;
    
    CALL pro_testInOut(@n);
    
    SELECT @n;
    

     

    4. 带有条件判断的存储过程

    需求:输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。其他数字,返回“错误输入”;

    DELIMITER $
    CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
    BEGIN
        IF num=1 THEN
            SET str='星期一';
        ELSEIF num=2 THEN
            SET str='星期二';
        ELSEIF num=3 THEN
            SET str='星期三';
        ELSE
            SET str='输入错误';
        END IF;
    END $
    
    --调用
    CALL pro_testIf(4,@str);
    
    SELECT @str;
    

     

    5. 带有循环功能的存储过程

    需求: 输入一个整数,求和。例如,输入100,统计1-100的和

    DELIMITER $
    CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
    BEGIN
        -- 定义一个局部变量
        DECLARE i INT DEFAULT 1;
        DECLARE vsum INT DEFAULT 0;
        WHILE i<=num DO
              SET vsum = vsum i;
              SET i=i 1;
        END WHILE;
        SET result=vsum;
    END $
    
    --调用
    CALL pro_testWhile(100,@result);
    
    SELECT @result;
    

     

    6. 使用查询的结果赋值给变量(INTO)

    DELIMITER $
    CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
    BEGIN
        SELECT empName INTO vname FROM employee WHERE id=eid;
    END $
    
    --调用
    CALL pro_findById2(1,@NAME);
    
    SELECT @NAME;
    

     

    二、 触发器

    触发器作用:当操作了某张表时,希望同时触发一些动作/行为,可以使用触发器完成。

    需求: 当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据

    CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
         INSERT INTO test_log(content) VALUES('员工表插入了一条记录');
    

    以上触发器是基于insert的,以下两个是基于update和delete的。

    -- 创建触发器(修改)
    CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
         INSERT INTO test_log(content) VALUES('员工表修改了一条记录');
    
    -- 创建触发器(删除)
    CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
         INSERT INTO test_log(content) VALUES('员工表删除了一条记录');
    

     

    说明:

    1. MySQL默认将分号,即“;”作为语句的分隔符。如果是这样的话,则一个存储过程将很难正常创建,因为它的BEGIN和END之间可以是任意数量的SQL语句,而每条SQL语句都以分号结束。所以,在创建存储过程时,必须重新定义分隔符。在上述用例中,均将“$”作为新的分隔符,注意,在创建完存储过程后,必须重新设置回原来的分隔符“;”。

    以上基于传智播客JAVA就业班视频MySQL相关内容整理。

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net存储过程,MySQL中的存储过程

    关键词: