精华内容
下载资源
问答
  • Mysql储存过程

    2013-10-27 10:52:03
    一、Mysql储存过程简介:  储存过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是...
    一、Mysql储存过程简介: 

      储存过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

    存储过程通常有以下优点:

    1)存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

    2)存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。

    3)存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

    4)存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

    5)存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。

    二、mysql储存过程的创建等语句:

    1、CREATE PROCEDURE (创建储存过程)

       CREATE PROCEDURE 存储过程名 (参数列表)

       BEGIN

       SQL语句代码块

       END

    注:由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//。 当使用delimiter命令时,你应该避免使用反斜杠(‘\’)字符,因为那是MySQL的转义字符。

    如:

        mysql> delimiter //

        mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

        -> BEGIN

        -> SELECT COUNT(*) INTO param1 FROM t;

        -> END

        -> //

       Query OK, 0 rows affected (0.00 sec)

    2、ALTER  PROCEDURE(修改储存过程)

       ALTER PROCEDURE  存储过程名 SQL语句代码块

    这个语句可以被用来改变一个存储程序的特征。

    3、DROP  PROCEDURE (删除储存过程)

       DROP PROCEDURE IF EXISTS存储过程名

    eg:DROP PROCEDURE IF EXISTS proc_employee (proc_employee 存储过程名)

    注:这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

    4、SHOW  CREATE PROCEDURE(类似于SHOW CREATE TABLE,查看一个已存在的存储过程信息)

       SHOW CREATE PROCEDURE 存储过程名

    5、SHOW PROCEDURE STATUS (列出所有的存储过程)

       SHOW PROCEDURE STATUS

    6、CALL 语句(存储过程的调用)

       CALL 存储过程名(参数列表)

    CALL语句调用一个先前用CREATE PROCEDURE创建的程序。 

    CALL语句可以用声明为OUT或INOUT参数的参数给它的调用者传回值。

    哪怕该存储过程没有参数传递,存储过程名称后面也必须加括号。

    7、BEGIN ... END(复合语句)

         [begin_label:] 

       BEGIN

         [statement_list]

        END 

         [end_label]

    存储子程序可以使用BEGIN ... END复合语句来包含多个语句。 statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号“;”来结尾。复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。 

    8、DECLARE语句(用来声明局部变量)

    DECLARE语句被用来把不同项目局域到一个子程序:局部变量DECLARE仅被用在BEGIN ... END复合语句里,并且必须在复合语句的开头,在任何其它语句之前。

    9、存储程序中的变量

    1)DECLARE局部变量

       DECLARE var_name[,...] type [DEFAULT value]

    这个语句被用来声明局部变量。

    要给变量提供一个默认值,请包含一个DEFAULT子句。

    值可以被指定为一个表达式,不需要为一个常数。

    如果没有DEFAULT子句,初始值为NULL。 

    局部变量的作用范围在它被声明的BEGIN ... END块内。

    它可以被用在嵌套的块中,除了那些用相同名字声明变量的块。

    2)变量SET语句

       SET var_name = expr [, var_name = expr] 

    在存储程序中的SET语句是一般SET语句的扩展版本。

    被参考变量可能是子程序内声明的变量,或者是全局服务器变量。 

    在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。

    其中不同的变量类型(局域声明变量及全局和集体变量)可以被混合起来。

    这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

    3)SELECT ... INTO 语句

       SELECT col_name[,...] INTO var_name[,...] table_expr

    这个SELECT语法把选定的列直接存储到变量。

    因此,只有单一的行可以被取回。 

    SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

    注:用户变量名在MySQL 5.1中是对大小写不敏感的。

    重要: SQL变量名不能和列名一样。如果SELECT ... INTO这样的SQL语句包含一个对列的参考,并包含一个与列相同名字的局部变量,MySQL当前把参考解释为一个变量的名字。

    10、MySQL 存储过程参数类型(in、out、inout)

    1)MySQL 存储过程参数(in)

    MySQL 存储过程  “in” 参数:跟 C 语言的函数参数的值传递类似,MySQL 存储过程内部可能会修改此参数,但对in类型参数的修改,对调用者(caller)来说是不可见的(not visible)。

    2)MySQL 存储过程参数(out)

    MySQL 存储过程  “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值。

    3)MySQL 存储过程参数(inout)

    MySQL 存储过程  inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过  inout 参数传递值给存储过程。

    总结:如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回,就要使用“inout” 类型参数。

    三、MySQL存储函数-基本常用函数

    1、字符串类

    1)mysql储存过程函数-字符串函数:

    CHARSET(str) //返回字串字符集

    CONCAT (string2 [,... ]) //连接字串

    INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0

    LCASE (string2 ) //转换成小写

    LEFT (string2 ,length ) //从string2中的左边起取length个字符

    LENGTH (string ) //string长度

    LOAD_FILE (file_name ) //从文件读取内容

    LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置

    LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length

    LTRIM (string2 ) //去除前端空格

    REPEAT (string2 ,count ) //重复count次

    REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str

    RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length

    RTRIM (string2 ) //去除后端空格

    STRCMP (string1 ,string2 ) //逐字符比较两字串大小,

    SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,

    注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1

    2)函数使用实例:

    mysql> select substring('abcd',0,2);  

    +-----------------------+  

    | substring('abcd',0,2) |  

    +-----------------------+  

    |                       |  

    +-----------------------+  

    1 row in set (0.00 sec)  


    mysql> select substring('abcd',1,2);  

    +-----------------------+  

    | substring('abcd',1,2) |  

    +-----------------------+  

    |     ab                |  

    +-----------------------+  

    1 row in set (0.02 sec)  

    TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符

    UCASE (string2 ) //转换成大写

    RIGHT(string2,length) //取string2最后length个字符

    SPACE(count) //生成count个空格



    2、数学类

    1)mysql储存函数-数学函数:

    ABS (number2 ) //绝对值

    BIN (decimal_number ) //十进制转二进制

    CEILING (number2 ) //向上取整

    CONV(number2,from_base,to_base) //进制转换

    FLOOR (number2 ) //向下取整

    FORMAT (number,decimal_places ) //保留小数位数

    HEX (DecimalNumber ) //转十六进制

    注:HEX()中可传入字符串,则返回其ASC-11码,如HEX('DEF')返回4142143

    也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19

    LEAST (number , number2 [,..]) //求最小值

    MOD (numerator ,denominator ) //求余

    POWER (number ,power ) //求指数

    RAND([seed]) //随机数

    ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

    注:返回类型并非均为整数,如下文:

    2)函数使用实例:

    a、默认变为整形值

    mysql> select round(1.23);  

    +-------------+  

    | round(1.23) |  

    +-------------+  

    |           1 |  

    +-------------+  

    1 row in set (0.00 sec)  


    mysql> select round(1.56);  

    +-------------+  

    | round(1.56) |  

    +-------------+  

    |           2 |  

    +-------------+  

    1 row in set (0.00 sec) 

    b、可以设定小数位数,返回浮点型数据

    mysql> select round(1.567,2);  

    +----------------+  

    | round(1.567,2) |  

    +----------------+  

    |           1.57 |  

    +----------------+  

    1 row in set (0.00 sec) 

    SIGN (number2 ) //



    3、日期时间类

    ADDTIME (date2 ,time_interval ) //将time_interval加到date2

    CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区

    CURRENT_DATE ( ) //当前日期

    CURRENT_TIME ( ) //当前时间

    CURRENT_TIMESTAMP ( ) //当前时间戳

    DATE (datetime ) //返回datetime的日期部分

    DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间

    DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime

    DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间

    DATEDIFF (date1 ,date2 ) //两个日期差

    DAY (date ) //返回日期的天

    DAYNAME (date ) //英文星期

    DAYOFWEEK (date ) //星期(1-7) ,1为星期天

    DAYOFYEAR (date ) //一年中的第几天

    EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分

    MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串

    MAKETIME (hour ,minute ,second ) //生成时间串

    MONTHNAME (date ) //英文月份名

    NOW ( ) //当前时间

    SEC_TO_TIME (seconds ) //秒数转成时间

    STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示

    TIMEDIFF (datetime1 ,datetime2 ) //两个时间差

    TIME_TO_SEC (time ) //时间转秒数]

    WEEK (date_time [,start_of_week ]) //第几周

    YEAR (datetime ) //年份

    DAYOFMONTH(datetime) //月的第几天

    HOUR(datetime) //小时

    LAST_DAY(date) //date的月的最后日期

    MICROSECOND(datetime) //微秒

    MONTH(datetime) //月

    MINUTE(datetime) //分返回符号,正负或0

    SQRT(number2) //开平方

    展开全文
  • MySQL储存过程

    2020-09-24 21:09:25
    查看数据库下的所有储存过程: show procedure status; 查看指定的存储过程: show procedure status like “new_procedure”; 查看存储过程或函数的创建代码: show create procedure proc_name; show

    概念

    存储过程简单来说,就是为以后的使用而保存
    的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用
    不仅限于批处理。

    存储过程相关的SQL语句

    查看数据库下的所有储存过程: show procedure status;
    查看指定的存储过程: show procedure status like “new_procedure”;
    查看存储过程或函数的创建代码:
    show create procedure proc_name;
    show create function func_name;
    创建存储过程

    USE `test`;
    DROP procedure IF EXISTS `new_procedure`;
    
    DELIMITER $$
    USE `test`$$
    CREATE PROCEDURE `new_procedure` ()
    BEGIN
    	select id from students;
    END$$
    
    DELIMITER ;
    

    其中,DELIMITER $ $ 告诉命令行实用程序使用 $ $ 作为新的语句结束分隔符,可以看到标志存储过程结束的END定义为END $$而不是END;。这样,存储过程体内的;仍然保持不动,并且正确地传递给数据库引擎。最后,为恢复为原来的语句分隔符,

    调用存储过程: call proc_name;
    删除存储过程: drop procedure proc_name;

    使用参数

    变量

    	-- 首先遍历所有的猪栏id
        declare pigpen_id int; -- 猪栏id变量
        declare pig_age int; -- 猪栏下的平均日龄
    
    USE `zxzl_dev`;
    DROP procedure IF EXISTS `new_procedure`;
    
    DELIMITER $$
    USE `zxzl_dev`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(
    out d1  int, out d2 int
    )
    BEGIN
    	select min(swinery_name) into d1 from swinery_link_dev;
        select min(device_id) into d1 from swinery_link_dev;
        
    END$$
    
    DELIMITER ;
    

    调用 call new_procedure(@n, @d);
    查看参数 selece @n;

    游标

    。游标(cursor)是一个存储在MySQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。

    游标只能用于存储过程和函数

    游标的使用步骤

    • 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。
    • 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
    • 对于填有数据的游标,根据需要取出(检索)各行。
    • 在结束游标使用时,必须关闭游标。

    创建游标 declare d_p cursor
    声明游标 declare d_p cursor for select id, average_age, feed_num, guest_id from farm_record where swinery_name is not null;

    CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
    BEGIN
    	declare room int;
        declare device int;
        declare d_p cursor for select room_id, device_id from room_device;
        open d_p;
        fetch d_p into room, device;
        close d_p;
    END
    

    也可使用多个游标,嵌套使用

    +----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure                              | sql_mode                                                                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | character_set_client | collation_connection | Database Collation |
    +----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | update_reality_feeding_chart_procedure | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`%` PROCEDURE `update_reality_feeding_chart_procedure`()
    BEGIN
            -- 首先遍历所有的猪栏id
        declare pigpen_id int; -- 猪栏id变量
        declare pig_age int; -- 猪栏下的平均日龄
        declare pig_total int;
        declare guest_id_t int;
        declare flag_farm_record int default 0;
    
        -- 定义游标,将sql的结果查询集赋值到游标中
        declare d_p cursor for select id, average_age, feed_num, guest_id from farm_record where swinery_name is not null;
    
            -- 声明当游标遍历完后将标志变量置成某个值
            declare continue handler for not found set flag_farm_record=1;
        -- 更新猪日龄
        -- update farm_record set average_age = average_age + 1;
            -- 打开游标
            open d_p;
    
            -- 变量顺序要和sql结果列的顺序一致
        fetch d_p into pigpen_id, pig_age, pig_total, guest_id_t;
    
        while flag_farm_record<>1 do
                    -- 执行业务逻辑
            begin
                declare device_id_t int; -- 猪栏id变量
                declare devs_feeding_total int default 0;
                            declare flag_swinery_dev int default 0;
                            declare devs_total int default 0;
                    declare swinery_dev_dp cursor for select device_id from swinery_link_dev where swinery_name = pigpen_id;
                            declare continue handler for not found set flag_swinery_dev=1;
    
                open swinery_dev_dp;
                fetch swinery_dev_dp into device_id_t;
                -- 查询当前猪栏下的所有设备
                            while flag_swinery_dev<>1 do
                                    -- 根据device_id_t查询前一天的此设备的料量
                                    begin
                                            declare dev_feeding_total int default 0;
                        declare flag_statistics_info int default 0;
                        declare statistics_info_dp cursor for select equi_acc_mat from dev_statistics_info where to_days(CURDATE()) - to_days(record_time) = 1 and device_id = device_id_t;
                                            declare continue handler for not found set flag_statistics_info=1;
                                            open statistics_info_dp;
                        fetch statistics_info_dp into dev_feeding_total;
                        set devs_feeding_total =  devs_feeding_total + dev_feeding_total;
                        set devs_total = devs_total + 1;
                        close statistics_info_dp;
                                    end;
                                    fetch swinery_dev_dp into device_id_t;
                            end while;
                close swinery_dev_dp;
                -- 实际饲喂曲线中插入
                if devs_total then
                                    insert into feeding_reality_chart (guest_id, swinery_id, average_feeding, pig_age) values (guest_id_t, pigpen_id, devs_feeding_total / pig_total, pig_age);
                            end if;
                            set devs_feeding_total = 0;
                            set devs_total = 0;
            end;
                    fetch d_p into pigpen_id, pig_age, pig_total, guest_id_t;
            -- 查找这个猪栏下的所有绑定的设备
                    -- declare d_p cursor for select id, average_age, feed_num from farm_record where swinery_name is not null;
            end while;
        close d_p;
        -- 更新猪日龄
        update farm_record set average_age = average_age + 1;
    END | utf8                 | utf8_general_ci      | utf8_general_ci    |
    +----------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    
    展开全文
  • mysql 储存过程

    2020-05-12 11:51:07
    储存过程的基本使用 DROP procedure IF EXISTS `getGameName`;#删除储存过程 # 设置结束符为$$,因为在begin,end之前的语句结束符是分号; # 这样改一下可以使代码的可读性更强一点 DELIMITER $$ #注意参数名不能...

    储存过程的基本使用

    DROP procedure IF EXISTS `getGameName`;#删除储存过程
    # 设置结束符为$$,因为在begin,end之前的语句结束符是分号;
    # 这样改一下可以使代码的可读性更强一点
    DELIMITER $$
    
    #注意参数名不能与字段名相同
    CREATE PROCEDURE getGameName(
    	 IN gameid INT, #入参
    	 OUT g_name VARCHAR(45), #出参
    	 OUT pin_yin VARCHAR(45)) #出参
    BEGIN
    	 SELECT gamename
    	 INTO g_name
    	 FROM cy_game
    	 WHERE id = gameid;
    	
    	 SELECT pinyin
    	 INTO pin_yin
    	 FROM cy_game
    	 WHERE id = gameid;
    END$$
    DELIMITER ;
    
    call getGameName(4,@g_name,@pin_yin);#调用储存过程
    SELECT @g_name,@pin_yin;#返回值
    
    SHOW PROCEDURE STATUS; #显示有哪些储存过程
    
    SHOW CREATE PROCEDURE getGameInfo;#显示指定储存过程的信息,包括代码
    
    一. 定义

      存储过程是存储在数据库目录中的一段声明性SQL语句。 触发器,其他存储过程以及Java,Python,PHP等应用程序可以调用存储过程。

      自身的存储过程称为递归存储过程。大多数数据库管理系统支持递归存储过程。 但是,MySQL不支持它。 在MySQL中实现递归存储过程之前,您应该检查MySQL数据库的版本

    二. 优缺点

      MySQL是最受欢迎的开源RDBMS,被社区和企业广泛使用。 然而,在它发布的第一个十年期间,它不支持存储过程,存储函数,触发器和事件。自从MySQL 5.0版本以来,这些功能被添加到MySQL数据库引擎,使其更加灵活和强大。


    MySQL存储过程的优点

      通常存储过程有助于提高应用程序的性能。当创建,存储过程被编译之后,就存储在数据库中。 但是,MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。 在编译存储过程之后,MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程,则使用编译版本,否则存储过程的工作方式类似于查询。

      存储过程有助于减少应用程序和数据库服务器之间的流量,因为应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。
    存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序,以便开发人员不必开发存储过程中已支持的功能。
    存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限,而不向基础数据库表提供任何权限。

      除了这些优点之外,存储过程有其自身的缺点,在数据库中使用它们之前,您应该注意这些缺点。

    MySQL存储过程的缺点

      如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外,如果您在存储过程中过度使用大量逻辑操作,则CPU使用率也会增加,因为数据库服务器的设计不当于逻辑运算。
    存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
    很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是,MySQL不提供调试存储过程的功能。

      开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

      内存,CPU开销大,复杂逻辑困难,不便调试,开发和维护不易

      MySQL存储过程有自己的优点和缺点。开发应用程序时,您应该决定是否应该或不应该根据业务需求使用存储过程。

    三. 创建储存过程,调用储存过程
    1. 使用命令行创建
    #创建储存过程
    DELIMITER $$
     CREATE PROCEDURE GetAllProducts()
       BEGIN
       SELECT *  FROM products;
       END $$
    DELIMITER ;
    
    #调用储存过程
    CALL GetAllProducts();
    

    注:在第一次调用DELIMITER可以不用加分号

    1. navicat客户端创建:
      a) 打开数据库,左侧目录 ->函数->右击新建函数
      在这里插入图片描述
      b). 设置参数,入参,出参,出入参
      在这里插入图片描述
      c) 编写sql语句,保存,创建完毕
      在这里插入图片描述
      d) 调用储存过程
        直接在相关的函数右击,点击运行储存过程,即可。如果需要参数,会弹出请输出参数的框,输入相关的参数,用逗号隔开即可。
    四. 变量
    声明变量

    要在存储过程中声明一个变量,可以使用DECLARE语句,如下所示:

    DECLARE variable_name datatype(size) DEFAULT default_value;
    

      下面来更详细地解释上面的语句:

    • 首先,在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
    • 其次,指定变量的数据类型及其大小。变量可以有任何MySQL数据类型,如INTVARCHARDATETIME等。
    • 最后,当声明一个变量时,它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。

      例如,可以声明一个名为total_sale的变量,数据类型为INT,默认值为0,如下所示:

    DECLARE total_sale INT DEFAULT 0;
    

    MySQL允许您使用单个DECLARE语句声明共享相同数据类型的两个或多个变量,如下所示:

    DECLARE x, y INT DEFAULT 0;
    

    注:这里和C/C++系列的有点不同,在C/C++里面,如果这样声明两个变量

    int *a,b;
    

    声明的是两个类型的变量,一个是指向一整数型的指针a,另一个是整型b。而在sql是,它声明的是一种类型的变量。

    分配变量值

      当声明了一个变量后,就可以开始使用它了。要为变量分配一个值,可以使用SET语句,例如:
    DECLARE total_count INT DEFAULT 0;
    SET total_count = 10;
    

      上面语句中,分配total_count变量的值为10。除了SET语句之外,还可以使用SELECT INTO语句将查询的结果分配给一个变量。 请参阅以下示例:

    DECLARE total_products INT DEFAULT 0
     
    SELECT COUNT(*) INTO total_products
    FROM products
    

      在上面的例子中:

      首先,声明一个名为total_products的变量,并将其值初始化为0。然后,使用SELECT INTO语句来分配值给total_products变量,从示例数据库(yiibaidb)中的products表中选择的产品数量。

    变量范围(作用域)

      一个变量有自己的范围(作用域),它用来定义它的生命周期。 如果在存储过程中声明一个变量,那么当达到存储过程的END语句时,它将超出范围,因此在其它代码块中无法访问。

      如果您在BEGIN END块内声明一个变量,那么如果达到END,它将超出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量,因为变量仅在自己的作用域中有效。 但是,在不同范围内声明具有相同名称的变量不是很好的编程习惯。

      以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。

      在本教程中,我们向您展示了如何在存储过程中声明变量,并讨论了变量的范围(作用域)。

    五. 存储过程参数

    MySQL存储过程参数示例


    1. IN参数示例
        以下示例说明如何使用GetOfficeByCountry存储过程中的IN参数来查询选择位于特定国家/地区的办公室。
    USE `yiibaidb`;
    DROP procedure IF EXISTS `GetOfficeByCountry`;
     
    DELIMITER $$
    USE `yiibaidb`$$
    CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
    BEGIN
    	 SELECT * 
    	 FROM offices
    	 WHERE country = countryName;
     END$$
     
    DELIMITER ;
    

      countryName是存储过程的IN参数。在存储过程中,我们查询位于countryName参数指定的国家/地区的所有办公室。

    注:它的变量是直接利用参数名调用的,这也就是为什么参数名不能和字段名一样的原因。

      假设我们想要查询在美国(USA)的所有办事处,我们只需要将一个值(USA)传递给存储过程,如下所示:

    CALL GetOfficeByCountry('USA');
    

      执行上面查询语句,得到以下结果

    在这里插入图片描述
      要在法国获得所有办事处,我们将France字符串传递给GetOfficeByCountry存储过程,如下所示:

    CALL GetOfficeByCountry('France')
    
    1. OUT参数示例

      以下存储过程通过订单状态返回订单数量。它有两个参数:

    • orderStatusIN参数,它是要对订单计数的订单状态。
    • total:存储指定订单状态的订单数量的OUT参数。

    以下是CountOrderByStatus存储过程的源代码。

    USE `yiibaidb`;
    DROP procedure IF EXISTS `CountOrderByStatus`;
     
    DELIMITER $$
    CREATE PROCEDURE CountOrderByStatus(
     IN orderStatus VARCHAR(25),
     OUT total INT)
    BEGIN
    	 SELECT count(orderNumber)
    	 INTO total
    	 FROM orders
    	 WHERE status = orderStatus;
    END$$
    DELIMITER ;
    

      要获取发货订单的数量,我们调用CountOrderByStatus存储过程,并将订单状态传递为已发货,并传递参数(@total)以获取返回值。

    CALL CountOrderByStatus('Shipped',@total);
    SELECT @total;
    

      执行上面查询语句后,得到以下结果

    +--------+
    | @total |
    +--------+
    |    303 |
    +--------+
    1 row in set
    
    1. INOUT参数示例
        以下示例演示如何在存储过程中使用INOUT参数。如下查询语句 -
    DELIMITER $$
    CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
    BEGIN
     	SET count = count + inc;
    END$$
    DELIMITER ;
    

      上面查询语句是如何运行的?

      set_counter存储过程接受一个INOUT参数(count)和一个IN参数(inc)。在存储过程中,通过inc参数的值增加计数器(count)。

      下面来看看如何调用set_counter存储过程:

    SET @counter = 1;
    CALL set_counter(@counter,1); -- 2
    CALL set_counter(@counter,1); -- 3
    CALL set_counter(@counter,5); -- 8
    SELECT @counter; -- 8
    
    六. 返回多个值的存储过程示例

      我们来看看示例数据库(yiibaidb)中的orders表。

    mysql> desc orders;
    +----------------+-------------+------+-----+---------+-------+
    | Field          | Type        | Null | Key | Default | Extra |
    +----------------+-------------+------+-----+---------+-------+
    | orderNumber    | int(11)     | NO   | PRI | NULL    |       |
    | orderDate      | date        | NO   |     | NULL    |       |
    | requiredDate   | date        | NO   |     | NULL    |       |
    | shippedDate    | date        | YES  |     | NULL    |       |
    | status         | varchar(15) | NO   |     | NULL    |       |
    | comments       | text        | YES  |     | NULL    |       |
    | customerNumber | int(11)     | NO   | MUL | NULL    |       |
    +----------------+-------------+------+-----+---------+-------+
    7 rows in set
    

      以下存储过程接受客户编号,并返回发货(shipped),取消(canceled),解决(resolved)和争议(disputed)的订单总数。

    DELIMITER $$
     
    CREATE PROCEDURE get_order_by_cust(
     IN cust_no INT,
     OUT shipped INT,
     OUT canceled INT,
     OUT resolved INT,
     OUT disputed INT)
    BEGIN
     -- shipped
     		SELECT
                count(*) INTO shipped
            FROM
                orders
            WHERE
                customerNumber = cust_no
                    AND status = 'Shipped';
     
     -- canceled
     		SELECT
                count(*) INTO canceled
            FROM
                orders
            WHERE
                customerNumber = cust_no
                    AND status = 'Canceled';
     
     -- resolved
     		SELECT
                count(*) INTO resolved
            FROM
                orders
            WHERE
                customerNumber = cust_no
                    AND status = 'Resolved';
     
     -- disputed
     		SELECT
                count(*) INTO disputed
            FROM
                orders
            WHERE
                customerNumber = cust_no
                    AND status = 'Disputed';
     
    END$$
    

      除IN参数之外,存储过程还需要4个额外的OUT参数:shipped, canceled, resolveddisputed。 在存储过程中,使用带有COUNT函数的SELECT语句根据订单状态获取相应的订单总数,并将其分配给相应的参数。

      要使用get_order_by_cust存储过程,可以传递客户编号和四个用户定义的变量来获取输出值。

      执行存储过程后,使用SELECT语句输出变量值。

    +----------+-----------+-----------+-----------+
    | @shipped | @canceled | @resolved | @disputed |
    +----------+-----------+-----------+-----------+
    |       22 |         0 |         1 |         1 |
    +----------+-----------+-----------+-----------+
    1 row in set
    
    从PHP调用返回多个值的存储过程

      以下代码片段显示如何从PHP程序中调用返回多个值的存储过程。

    <?php
    /**
     * Call stored procedure that return multiple values
     * @param $customerNumber
     */
    function call_sp($customerNumber)
    {
        try {
            $pdo = new PDO("mysql:host=localhost;dbname=yiibaidb", 'root', '123456');
     
            // execute the stored procedure
            $sql = 'CALL get_order_by_cust(:no,@shipped,@canceled,@resolved,@disputed)';
            $stmt = $pdo->prepare($sql);
     
            $stmt->bindParam(':no', $customerNumber, PDO::PARAM_INT);
            $stmt->execute();
            $stmt->closeCursor();
     
            // execute the second query to get values from OUT parameter
            $r = $pdo->query("SELECT @shipped,@canceled,@resolved,@disputed")
                      ->fetch(PDO::FETCH_ASSOC);
            if ($r) {
                printf('Shipped: %d, Canceled: %d, Resolved: %d, Disputed: %d',
                    $r['@shipped'],
                    $r['@canceled'],
                    $r['@resolved'],
                    $r['@disputed']);
            }
        } catch (PDOException $pe) {
            die("Error occurred:" . $pe->getMessage());
        }
    }
     
    call_sp(141);
    

      在@符号之前的用户定义的变量与数据库连接相关联,因此它们可用于在调用之间进行访问。

    七. 循环语句

      MySQL提供循环语句,允许您根据条件重复执行一个SQL代码块。 MySQL中有三个循环语句:WHILEREPEATLOOP

      我们将在以下部分中更详细地检查每个循环语句。

    WHILE循环

      WHILE语句的语法如下:

    WHILE expression DO
       statements
    END WHILE
    

    WHILE循环在每次迭代开始时检查表达式。 如果expressioneTRUE,MySQL将执行WHILEEND WHILE之间的语句,直到expressionFALSE。 WHILE循环称为预先测试条件循环,因为它总是在执行前检查语句的表达式。

    下面的流程图说明了WHILE循环语句:

    在这里插入图片描述
      以下是在存储过程中使用WHILE循环语句的示例:

    DELIMITER $$
    
     DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
     
     CREATE PROCEDURE test_mysql_while_loop()
     BEGIN
    	 DECLARE x  INT;
    	 DECLARE str  VARCHAR(255);
    	 
    	 SET x = 1;
    	 SET str =  '';
    	 
    	 WHILE x  <= 5 DO
    		 SET  str = CONCAT(str,x,',');
    		 SET  x = x + 1; 
    	 END WHILE;
    	 
    	 SELECT str;
     END$$
    DELIMITER ;
    

      在上面的test_mysql_while_loop存储过程中:

      首先,重复构建str字符串,直到x变量的值大于5。然后,使用SELECT语句显示最终的字符串。要注意,如果不初始化x变量的值,那么它默认值为NULL。 因此,WHILE循环语句中的条件始终为TRUE,并且您将有一个不确定的循环,这是不可预料的。

      下面来测试test_mysql_while_loopstored调用存储过程:

    CALL test_mysql_while_loop();
    

      执行上面查询语句,得到以下结果 -

    mysql> CALL test_mysql_while_loop();
    +------------+
    | str        |
    +------------+
    | 1,2,3,4,5, |
    +------------+
    1 row in set
     
    Query OK, 0 rows affected
    
    REPEAT循环

    REPEAT循环语句的语法如下:

    REPEAT
     	statements;
    UNTIL expression
    END REPEAT
    

      首先,MySQL执行语句,然后评估求值表达式(expression)。如果表达式(expression)的计算结果为FALSE,则MySQL将重复执行该语句,直到该表达式计算结果为TRUE

      因为REPEAT循环语句在执行语句后检查表达式(expression),因此REPEAT循环语句也称为测试后循环。

      下面的流程图说明了REPEAT循环语句的执行过程:
    在这里插入图片描述

      我们可以使用REPEAT循环语句重写test_mysql_while_loop存储过程,使用WHILE循环语句:

    DELIMITER $$
     DROP PROCEDURE IF EXISTS mysql_test_repeat_loop$$
     CREATE PROCEDURE mysql_test_repeat_loop()
     BEGIN
    	 DECLARE x INT;
    	 DECLARE str VARCHAR(255);
    	 
    	 SET x = 1;
    	 SET str =  '';
    	 
    	 REPEAT
    		 SET  str = CONCAT(str,x,',');
    		 SET  x = x + 1; 
    	 UNTIL x  > 5
    	 END REPEAT;
    	 
    	        SELECT str;
     END$$
    DELIMITER ;
    

    注:要注意的是UNTIL表达式中没有分号;

      执行上面查询语句,得到以下结果 -

    mysql> CALL mysql_test_repeat_loop();
    +------------+
    | str        |
    +------------+
    | 1,2,3,4,5, |
    +------------+
    1 row in set
     
    Query OK, 0 rows affected
    
    LOOP,LEAVE和ITERATE语句

      有两个语句允许您用于控制循环:

    • LEAVE语句用于立即退出循环,而无需等待检查条件。LEAVE语句的工作原理就类似PHP,C/C++,Java等其他语言的break语句一样。
    • ITERATE语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于PHP,C/C++,Java等中的continue语句。

      MySQL还有一个LOOP语句,它可以反复执行一个代码块,另外还有一个使用循环标签的灵活性。

      以下是使用LOOP循环语句的示例。

    CREATE PROCEDURE test_mysql_loop()
     BEGIN
    	 DECLARE x  INT;
    	 DECLARE str  VARCHAR(255);
    	 
    	 SET x = 1;
    	 SET str =  '';
    	 
    	 loop_label:  
    	 LOOP
    		 IF  x > 10 THEN 
    		 	LEAVE  loop_label;
    		 END  IF;
    		 
    		 SET  x = x + 1;
    		 IF (x mod 2) THEN
    		     ITERATE  loop_label;
    		 ELSE
    		    SET  str = CONCAT(str,x,',');
    		 END IF;
    	 END LOOP;    
    	 SELECT str;
    END;
    

      以上存储过程仅构造具有偶数字符串的字符串,例如2,4,6等。在LOOP语句之前放置一个loop_label循环标签。

    • 如果x的值大于10,则由于LEAVE语句,循环被终止。
    • 如果x的值是一个奇数,ITERATE语句忽略它下面的所有内容,并开始一个新的迭代。
    • 如果x的值是偶数,则ELSE语句中的块将使用偶数构建字符串。

      执行上面查询语句,得到以下结果 -

    mysql> CALL test_mysql_loop();
    +-------------+
    | str         |
    +-------------+
    | 2,4,6,8,10, |
    +-------------+
    1 row in set
     
    Query OK, 0 rows affected
    
    八.MySQL游标简介

      要处理存储过程中的结果集,请使用游标。游标允许您迭代查询返回的一组行,并相应地处理每行。

      MySQL游标为只读,不可滚动和敏感。

    • 只读:无法通过光标更新基础表中的数据。
    • 不可滚动:只能按照SELECT语句确定的顺序获取行。不能以相反的顺序获取行。 此外,不能跳过行或跳转到结果集中的特定行。
    • 敏感:有两种游标, 敏感游标和不敏感游标。敏感游标指向实际数据,不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快,因为它不需要临时拷贝数据。但是,对其他连接的数据所做的任何更改都将影响由敏感游标使用的数据,因此,如果不更新敏感游标所使用的数据,则更安全。 MySQL游标是敏感的。

      您可以在存储过程,存储函数和触发器中使用MySQL游标。

    使用MySQL游标

    首先,必须使用DECLARE语句声明游标:

    DECLARE cursor_name CURSOR FOR SELECT_statement;
    

      游标声明必须在变量声明之后。如果在变量声明之前声明游标,MySQL将会发出一个错误。游标必须始终与SELECT语句相关联。

    接下来,使用OPEN语句打开游标。OPEN语句初始化游标的结果集,因此您必须在从结果集中提取行之前调用OPEN语句。

    OPEN cursor_name;
    

      然后,使用FETCH语句来检索光标指向的下一行,并将光标移动到结果集中的下一行。

    FETCH cursor_name INTO variables list;
    

      当光标不再使用时,应该关闭它。
      之后,可以检查是否有任何行记录可用,然后再提取它。最后,调用CLOSE语句来停用光标并释放与之关联的内存,如下所示:

    CLOSE cursor_name;
    

      当使用MySQL游标时,还必须声明一个NOT FOUND处理程序来处理当游标找不到任何行时的情况。 因为每次调用FETCH语句时,游标会尝试读取结果集中的下一行。 当光标到达结果集的末尾时,它将无法获得数据,并且会产生一个条件。 处理程序用于处理这种情况。

      要声明一个NOT FOUND处理程序,参考以下语法:

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    

      finished是一个变量,指示光标到达结果集的结尾。请注意,处理程序声明必须出现在存储过程中的变量和游标声明之后。

      下图说明了MySQL游标如何工作。
    在这里插入图片描述

    MySQL游标示例

      为了更好地演示,我们将开发一个存储过程,来获取MySQL示例数据库(yiibaidb)中employees表中所有员工的电子邮件列表。

    首先,声明一些变量,一个用于循环员工电子邮件的游标和一个NOT FOUND处理程序:

    DECLARE finished INTEGER DEFAULT 0;
    DECLARE email varchar(255) DEFAULT "";
     
    -- declare cursor for employee email
    DEClARE email_cursor CURSOR FOR 
    SELECT email FROM employees;
     
    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;
    

      接下来,使用OPEN语句打开email_cursor

    OPEN email_cursor;
    

      然后,迭代电子邮件列表,并使用分隔符(;)连接每个电子邮件:

    get_email: LOOP
     FETCH email_cursor INTO v_email;
     IF v_finished = 1 THEN 
     LEAVE get_email;
     END IF;
     -- build email list
     SET email_list = CONCAT(v_email,";",email_list);
    END LOOP get_email;
    

    注:单层循环END LOOP后面的标签名可加可不加,如果嵌套循环,则必须加。
      之后,在循环中,使用v_finished变量来检查列表中是否有任何电子邮件来终止循环。
      最后,使用CLOSE语句关闭游标:

    CLOSE email_cursor;
    

    build_email_list存储过程所有代码如下:

    DELIMITER $$
     
    CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
    BEGIN
     
    	DECLARE v_finished INTEGER DEFAULT 0;
    	DECLARE v_email varchar(100) DEFAULT "";
    	 
    	 -- declare cursor for employee email
    	 DEClARE email_cursor CURSOR FOR 
    	 SELECT email FROM employees;
    	 
    	 -- declare NOT FOUND handler
    	 DECLARE CONTINUE HANDLER 
    	 FOR NOT FOUND SET v_finished = 1;
    	 
    	 OPEN email_cursor;
    	 
    	 get_email: LOOP 
    		 FETCH email_cursor INTO v_email;	 
    		 IF v_finished = 1 THEN 
    		 	LEAVE get_email;
    		 END IF;		 
    		 -- build email list
    		 SET email_list = CONCAT(v_email,";",email_list); 
    	END LOOP get_email;
    	 
    	CLOSE email_cursor;
     
    END$$
     
    DELIMITER ;
    

      可以使用以下脚本测试build_email_list存储过程:

    SET @email_list = "";
    CALL build_email_list(@email_list);
    SELECT @email_list;
    

    注:由于内容比较长,这里就不放上输出结果了。

    九. 显示储存过程
    显示存储过程字符

    要显示存储过程的字符,请使用SHOW PROCEDURE STATUS语句如下:

    SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];
    

      SHOW PROCEDURE STATUS语句是对SQL标准的MySQL扩展。此语句提供存储过程的字符,包括数据库,存储过程名称,类型,创建者等。可以使用LIKEWHERE子句根据各种标准过滤出存储过程。

      要列出您有权访问的数据库的所有存储过程,请使用SHOW PROCEDURE STATUS语句,如下所示:

    SHOW PROCEDURE STATUS;
    

      如果要在特定数据库中显示存储过程,可以在SHOW PROCEDURE STATUS语句中使用WHERE子句:

    SHOW PROCEDURE STATUS WHERE db = 'yiibaidb';
    

    如果要显示具有特定模式的存储过程,例如,名称包含product字符,则可以使用LIKE操作符,如以下命令:

    SHOW PROCEDURE STATUS WHERE name LIKE '%product%';
    
    显示存储过程的源代码

      要显示特定存储过程的源代码,请使用SHOW CREATE PROCEDURE语句如下:

    SHOW CREATE PROCEDURE stored_procedure_name
    

    SHOW CREATE PROCEDURE关键字之后指定存储过程的名称。例如,要显示GetAllProducts存储过程的代码,请使用以下语句:

    SHOW CREATE PROCEDURE GetAllProducts;
    
    展开全文
  • mysql储存过程

    2019-10-06 09:50:43
    存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql。  使用存储过程的优点: #1. 用于替代程序写的SQL语句,实现程序与sql解耦 #2. 基于网络传输,传别名的...

    1.介绍

    存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql。

     使用存储过程的优点:

    #1. 用于替代程序写的SQL语句,实现程序与sql解耦
    
    #2. 基于网络传输,传别名的数据量小,而直接传sql数据量大 

      使用存储过程的缺点:

    #1. 程序员扩展功能不方便

    2.程序与数据库结合使用方式

    #方式一:
        MySQL:存储过程
        程序:调用存储过程
    
    #方式二:
        MySQL:
        程序:纯SQL语句
    
    #方式三:
        MySQL:
        程序:类和对象,即ORM(本质还是纯SQL语句)

    3.创建简单储存过程(无参)

    delimiter //
    create procedure p1()
    BEGIN
        select * from blog;
        INSERT into blog(name,sub_time) values("xxx",now());
    END //
    delimiter ;
    
    #在mysql中调用
    call p1(); #类似于MySQL的函数,但不是函数昂,别搞混了,MySQL的函数(count()\max()\min()等等)都是放在sql语句里面用的,不能单独的使用,存储过程是可以直接调用的  call 名字+括号;
    #MySQL的视图啊触发器啊if判断啊等等都能在存储过程里面写,这是一大堆的sql的集合体,都可以综合到这里面
    #在python中基于pymysql调用
    cursor.callproc('p1') 
    print(cursor.fetchall())

    4.创建储存过程(有参)

    对于存储过程,可以接收参数,其参数有三类:
    
    #in          仅用于传入参数用
    #out        仅用于返回值用
    #inout     既可以传入又可以当作返回值

    in:传入参数:

    delimiter //
    create procedure p2(
        in n1 int,  #n1参数是需要传入的,也就是接收外部数据的,并且这个数据必须是int类型
        in n2 int
    )
    BEGIN
        
        select * from blog where id > n1;  #直接应用变量
    END //
    delimiter ;
    #调用存储过程的两种方式:或者说是两个地方吧
      #在mysql中调用
      call p2(3,2)
    
      #在python中基于pymysql调用
      cursor.callproc('p2',(3,2))
      print(cursor.fetchall())

      #通过存储过程的传参来看,也能体现出我们学习的Python的灵活性,传参不需要指定类型,也不需要声明这个参数是传入的还是返回出来的,参数既可以传入,这个参数也可以直接通过return返回。

      out:返回值:

    #查看存储过程的一些信息:show create procedure p3; #查看视图啊、触发器啊都这么看,还可以用\G,show create procedure p3\G;\G的意思是你直接查看表结构可能横向上显示不完,\G是让表给你竖向显示,一row是一行的字段delimiter //
    create procedure p3(
        in n1 int,
        out res int
    )
    BEGIN
        select * from blog where id > n1;  
        set res = 1;  #我在这里设置一个res=1,如果上面的所有sql语句全部正常执行了,那么这一句肯定也就执行了,那么此时res=1,如果我最开始传入的时候,给res的值设置的是0,#那么你想,最后我接收到的返回值如果是0,那么说明你中间肯定有一些sql语句执行失败了#注意写法:out的那个参数,可以用set来设置,set设置之后表示这个res可以作为返回值,并且不需要像python一样写一个return,你直接set之后的值,就是这个存储过程的返回值
    END //
    delimiter ;
    
    #在mysql中调用
    set @res=0; #这是MySQL中定义变量名的固定写法(set @变量名=值),可以自己规定好,0代表假(执行失败),1代表真(执行成功),如果这个被改为1了,说明存储过程中的sql语句执行成功了
    call p3(3,@res);#注意:不要这样写:call p3(3,1),这样out的参数值你写死了,没法确定后面这个1是不是成功了,也就是说随后这个out的值可能改成0了,也就是失败了,但是这样你就判断不了了,你后面查看的这个res就成1了,所以这个参数应该是一个变量名昂,定义变量名就是上一句,如果你直接传一个常量数字,会报错的,写法不对。
    select @res; #看一下这个结果,就知道这些sql语句是不是执行成功了,大家明白了吗~~~
    
    #在python中基于pymysql调用,在python中只需要知道存储过程的名字就行了
    cursor.callproc('p3',(3,0)) #0相当于set @res=0,为什么这里这个out参数可以写常数0啊,因为你用的pymysql,人家会帮你搞定,pymysql其实会帮你写成这样:第一个参数变量名:@_p3_0=3,第二个:@_p3_1=0,也就是pymysql会自动帮你对应上一个变量名,pymysql只是想让你写的时候更方便#沿着网络将存储过程名和参数发给了mysql服务端,比咱们发一堆的sql语句肯定要快对了,mysql帮你调用存储过程
    print(cursor.fetchall()) #查询select的查询结果
    
    cursor.execute('select @_p3_0,@_p3_1;') #@_p3_0代表第一个参数,@_p3_1代表第二个参数,即返回值
    print(cursor.fetchall())#别忘了关掉:cursor.close()conn.close()#注意昂:存储过程在哪个库里面建的,就只能在哪个库里面用

      inout:既可传入又可以返回值:

    delimiter //
    create procedure p4(
        inout n1 int
    )
    BEGIN
        select * from blog where id > n1;
        set n1 = 1;
    END //
    delimiter ;
    
    #在mysql中调用
    set @x=3;
    call p4(@x);
    select @x;
    
    
    #在python中基于pymysql调用
    cursor.callproc('p4',(3,))
    print(cursor.fetchall()) #查询select的查询结果
    
    cursor.execute('select @_p4_0;') 
    print(cursor.fetchall())

      存储过程结合事务来写:

    delimiter //
                create procedure p4(
                    out status int
                )
                BEGIN
                    1. 声明如果出现异常则执行{
                        set status = 1;
                        rollback;
                    }
                       
                    开始事务
                        -- 由秦兵账户减去100
                        -- 方少伟账户加90
                        -- 张根账户加10
                        commit;
                    结束
                    
                    set status = 2;
                    
                    
                END //
                delimiter ;
    
    #实现
    delimiter //
    create PROCEDURE p5(
        OUT p_return_code tinyint
    )
    BEGIN 
        DECLARE exit handler for sqlexception   #声明如果一旦出现异常则执行下面的这个begin和end里面的操作
        BEGIN 
            -- ERROR   #--是什么啊,忘了吧,是注释的意思,就告诉你后面是对错误的处理
            set p_return_code = 1;  #将out返回值改为1了,这是你自己规定的,1表示出错了
            rollback;  #回滚事务
        END; 
    
        DECLARE exit handler for sqlwarning  #声明了出现警告信息之后你的操作行为
        BEGIN 
            -- WARNING 
            set p_return_code = 2; 
            rollback; 
        END; 
    
        START TRANSACTION;  #其实咱们这个存储过程里面就是执行这个事务,并且一直检测着这个事务,一旦出错或者出现警告,就rollback
            DELETE from tb1; #事务里面的任何一条sql执行失败或者执行出现警告,都会执行上面我们声明的那些对应的操作,如果没有任何的异常,就会自动执行下面的commit,并执行后面成功的sql
            insert into blog(name,sub_time) values('yyy',now());  #拿我的代码进行测试的时候,别忘了改成你自己库里的表,还有表里面对应的字段名要有的,自己测试的时候,可以自己写一个错误的sql来试试看
        COMMIT; 
    
        -- SUCCESS 
        set p_return_code = 0; #0代表执行成功
    
    END //
    delimiter ;
    
    #在mysql中调用存储过程
    set @res=123;
    call p5(@res);
    select @res;
    
    #在python中基于pymysql调用存储过程
    cursor.callproc('p5',(123,)) #注意后面这个参数是个元祖,别忘了逗号,按照我们上面规定的,上面有三个值0,1,2:0成功、1失败、2警告也是失败。所以我们传给这个out参数的值只要不是这三个值就行了,这里给的是100
    print(cursor.fetchall()) #查询select的查询结果
    
    cursor.execute('select @_p5_0;')
    print(cursor.fetchall())#执行成功以后,查看一下结果就能看到执行后的值了

    5.执行存储过程

      在MySQL中执行存储过程:

    -- 无参数
    call proc_name()
    
    -- 有参数,全in
    call proc_name(1,2)
    
    -- 有参数,有in,out,inout
    set @t1=0;
    set @t2=3;
    call proc_name(1,2,@t1,@t2)
    
    执行存储过程

      在python中基于pymysql来执行存储过程:

    #!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 执行存储过程
    cursor.callproc('p1', args=(1, 22, 3, 4))
    # 获取执行完存储的参数
    cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
    result = cursor.fetchall()
    
    #conn.commit()
    cursor.close()
    conn.close()
    
    
    print(result)

    6.除存储过程

    drop procedure proc_name;

    转载于:https://www.cnblogs.com/zdqc/p/11498761.html

    展开全文
  • Mysql储存过程是一组为了完成特定功能的SQL语句集,经过编译之后存储在数据库中,当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了,简而言之就是一组已经写好的命令,需要...
  • Mysql储存过程是一组为了完成特定功能的SQL语句集,经过编译之后存储在数据库中,当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了,简而言之就是一组已经写好的命令,需要...
  • 一 前言本篇内容是关于mysql储存过程的知识,学习本篇的基础是知识追寻者之前发过的文章(公众号读者看专辑)《SQL-你真的了解什么SQL么?》《SQL-小白最佳入门sql查询一》《SQL-小白最佳入门sql查询二》《SQL- 多年...
  • 存储过程需要MySQL 5 MySQL 5添加了对存储过程的支持,因此,本章内容适用于MySQL 5及以后的版本。迄今为止,使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单,经常会有一个完整的操作...
  • 存储过程需要MySQL 5 MySQL 5添加了对存储过程的支持,因此,本章内容适用于MySQL 5及以后的版本。迄今为止,使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单,经常会有一个完整的操作...
  • 储存过程本文章原创,转载需注明出处.前提: 在大型数据库中来源: 为了完成特定功能的SQL语句集定义: 储存在数据库中, 用户通过指定储存过程的名字并给出参数(带有参数的)来执行它声明: 储存过程是数据库中一个重要的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 905
精华内容 362
关键字:

mysql储存过程

mysql 订阅