精华内容
下载资源
问答
  • 2018-05-22 09:34:00

        简单地说,存储过程就是一条或多条  SQL  语句的集合,可视为批文件,但是其作用不仅限于批处理。存储程序可以分为存储过程和函数。存储过程要用 CALL  语句来调用,并且只能用输出变量返回值。

    1.   创建存储过程

        语法格式:

        CREATE  PROCEDURE   SP_name  (  [ proc_parameter ] )

            [ characteristics  ...  ]   routine_body 

    【注释】

        a.    CREATE  PROCEDURE:    创建存储过程的关键字。
        b.    sp_name:    存储过程的名字。 

        c.    proc_parameter:为存储过程的参数列表,列表形式如下:

            [  IN  |   out   |   INOUT  ]  param_name  type 

           IN  表示输入参数;OUT  表示输出参数;INOUT  表示即可以输入也可以输出参数;param_name  表示参数名;type  表示参数类型 。
        d.    characteristics  指定存储过程的特性,有以下取值:
    •     LANGUAGE SQL:说明  routine_body 部分是由 SQL语句组成。
    •     [NOT]  DETERMINISTIC:指明存储过程执行的结果是否正确。DETERMINISTIC  表示结果是确定的,每次执行存储过程时,相同的输入会得到相同的输出。 NOT DETERMINISTIC  表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定值,默认为 NOT DETERMINISTIC。
    •     {CONTAINS SQL |  NO SQL  |  READS SQL DATA  |  MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含  SQL  语句,但是不包含读写数据的语句; NO SQL 表明子程序不包含SQL 语句;  READS SQL DATA  表明子程序包含读数据的语句;   MODIFIES SQL DATA  表明子程序包含写数据的语句。默认情况下,系统会指定为  CONTAINS SQL。
    •     SQL SECURITY {  DEFINER  |  INVOKER }:指明谁有权限来执行。DEFINER 表示只有定义者才能执行。INVOKER  表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER。
    •     COMMENT  ‘string’:注释信息,可以用来描述存储过程或函数。

    e.    routine body是SQL代码的内容,可以用BEGIN..END来表示SQL代码的开始和结束。


    【例】创建查看  fruits 表的存储过程。SQL 语句如下:

    mysql> DELIMITER  //
    mysql> CREATE  PROCEDURE  proc()
        -> BEGIN
        -> SELECT  *  FROM  fruits;
        -> END  //
    Query OK, 0 rows affected (0.35 sec)
    mysql> DELIMITER ;

        这个存储过程和使用  SELECT  语句查看表得到的结果是一样的, 当然存储过程也可以是很多语句的复杂组合,其本身也可以调用其他的函数,来组成更加复杂的操作。

        【注】    "DELIMITER   //”语句的作用是将  MySQL  的结束符设置为 // ,因为  MySQL 默认的语句结束符号为分号(;),为了避免与存储过程中的  SQL 语句结束符冲突,需要使用  DELIMITER  改变存储过程的结束符,并以“END  //”结束存储过程,存储过程,定义完毕之后再使用  DELIMITER  恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。


    【例】 创建名为 countproc  的存储过程,用于获取  fruits 表中的记录数。代码如下:

    mysql> DELIMITER  //
    mysql> CREATE  PROCEDURE  countproc  (  OUT  paraml  INT )
        -> BEGIN
        -> SELECT  COUNT(*)  INTO  paraml  FROM  fruits;
        -> END  //
    Query OK, 0 rows affected (0.07 sec)
    
    mysql> DELIMITER  ;

    2.   创建存储函数

        语法格式:

         CREATE  FUNCTION   func_name  (  [ func_parameter ] )

                 RETURNS  type   

         [ characteristics  ...  ]   routine_body 

    •     CREATE  FUNCTION  为创建存储函数的关键字;
    •     func_name  表示存储函数的名称;
    •     func_parameter  为存储函数的参数列表,参数列表的形式如下:
                [  IN   |    OUT   |   INOUT ]  param_name   type

          其中,IN 表示输入参数;  OUT 表示输出参数; INOUT  表示既可以输入也可以输出参数;  param_name 表示参数名称; type 表示参数的类型,该类型可以是  MySQL 数据库中的任意类型。

    •     RETURNS type  语句表示函数返同数据的类型;
    •     characteristic  指定存储函数的特性,取值与创建存储过程时相同。


    【例】 创建存储函数,名称为  namebyzip, 该函数返回  SELECT  语句的查询结果,数值类型为字符串型。代码如下:

    mysql> DELIMITER  //
    mysql> CREATE  FUNCTION  namebyzip()
        -> RETURNS  CHAR(50)
        -> RETURN  (  SELECT  s_name  FROM  suppliers  WHERE  s_call='48075');
        -> //
    Query OK, 0 rows affected (0.11 sec)
    
    mysql> DELIMITER  ;

    3.   变量的使用

        变量可以在子程序中声明并使用,这些变量的作用范围是在  BEGIN  ...  END 程序中。

       a.   定义变量

        语法格式:

        DECLARE  var_namel [, var_name] ...  date_type  [  DEFAULT  value ];

    • var_name  为局部变量的名称。
    • DEFAULT  value  子句给变最提供一个默认值,这个值除了可以声明为一个常数之外,还可以指定为一个表达式。如果没有DEFAULT  子句,初始值为NULL。

        【例】定义名称为  myparam  的变量,类型为  INT,默认值为100。代码如下:

     DECLARE  myparam  INT  DEFAULT  100;

        b.   为变量赋值

        变量定义之后,为变量赋值可以改变量的默认值,MysSQL中使用 SET 语句为变量赋值,语法格式如下:

        SET var_name = expr [ , var_name = expr ]   ...  ;


        【例】声明3个变量,分别为  varl、var2 和 var3, 数据类型为  INT,使用  SET  为变量赋值。代码如下:

    DECLARE var1, var2, var3 INT;
    SET var1 = 10, var2 = 20;
    SET var3 = var1 + var2;

      

        MySQL  中还可以通过  SELECT   ...  INTO  为一个或多个变量赋值, 语法如下:

        SELECT   col_name  [  ,  ...   ]   INTO Var_name  [  ,  ...   ]   table_expr;

    • col_name  表示字段名称;
    • var_name  表示定义的变量名称;
    • table_expr  表示查询条件表达式,包括表名称和 WHERE 子句。

    【例】声明变量 fruit_name  和  fruitprice  通过 SELECT ... INTO 语句查询指定记录并为变量赋值。  代码如下:

    DECLARE    fruitname  CHAR(50);
    
    DECLARE    fruitprice   DECIMAL(8,2);
    
    SELECT  f_name,f_price  INTO  fruitname,  fruitprice
    
    FROM  fruits  WHERE  f_id ='a1';
    
    

    4.   定义条件和处理程序

        定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。

        a.    定义条件

         DECLARE condition_name CONDITION FOR  [condition_type] 
       [condition_type]:
            SQLSTATE [VALUE] sqlstate_value  |  mysql_error_code


    •     condition name  参数表示条件的名称;
    •     condition_type  参数表示条件的类型;
    •     sqlstate_value 和  mysql_error_code  都可以表示  MySQL 中的错误,sqlstate_value为长度为 5 的字符串类型错误代码,mysql_error_code 为数值类型错误代码。例如,ERROR 1142(42000)中,sqlstate_value 的值是42000,mysql_ error_code的值是  1142。
        这个语句用于指定需要特殊处理的条件,可以将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的  DECLARE  HANDLER 语句中。


    【例】定义  ERROR 1148(42000)  错误, 名称为  command_not_allowed ,  用两种不同的方法来定义。代码如下:

      //方法一:  使用  sqlstate_value
      DECLARE command_not_allowed   CONDITION  FOR   SQLSTATE  '42000';
    
     //方法二:  使用mysql_error_code
      DECLARE  command_not_allowed CONDITION FOR 1148

        b.  定义处理程序

        语法格式如下:

        DECLARE   handler_type   HANDLER FOR   condition_value  [  ,...  ]    sp_statement
        handler_type:
      CONTINUE   |   EXIT  |  UNDO

        condition_value:
      SQLSTATE   [  VALUE  ] sqlstate_value
        |condition_name
        |SQLWARNING
        |NOT FOUND
        |SQLEXCEPTION
        |mysql_error_code

    【注释】

        (1)  hander_type  为错误处理方式,参数取3个值:  CONTINUE、 EXIT  和  UNDO.   CONTINUE  表示遇到错误不处理,继续执行; EXIT  表示遇到错误马上退出; UNDO  表示遇到错误后撒回之前的操作,MySOL中暂时不支持这样的操作。
        (2)   condition_value  表示错误类型,可以有以下取值:

    •     SQLSTATE  [VALUE]  sqlstate_value  表示包含5个字符的字符串错误值。
    •     condition_name  表示  DECLARE CONDITION  定义的错误条件名称。
    •     SQLWARNING  匹配所有以  01 开头的  SQLSTATE  错误代码。
    •     NOT FOUND  匹配所有以  02  开头的  SQLSTATE  错误代码。
    •     SQLEXCEPTION  匹配所有未被  SQLWARNING  或  NOT FOUND 捕获的  SQLSTATE  错误代码。
      (3) mysql_error_code 匹配数值类型错误代码。
      (4) sp_statement  参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程或函数。


    【例】定义处理程序的几种方式。代码如下:

    //方法一:捕获sqlstate_value
      DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
    

        第一种方法是捕获  sqlstate_value   值。如果遇到  sqlstate_value 值为 42S02, 执行  CONTINUE  操作,并且输出  NO_ SUCH_TABLE  信息。

     //方法二:  捕获  mysql_error_code
      DECLARE CONTINUE HANDLER FOR 1146 SET   @info= 'NO_SUCH_TABLE' ;

        第二种方法是捕获  mysql_error_code值。如果遇到  mysql_error_code值为  1146, 执行  CONTINUE  操作,并且输出NO_ SUCH_TABLE  信息。

     //方法三:  先定义条件,然后调用
      DECLARE  no_such_table CONDITION FOR 1146;
      DECLARE   CONTINUE   HANDLER   FOR  NO_SUCH_TABLE   SET @info= 'NO_SUCH_TABLE' ;

        第三种方法是先定义条件,然后再调用条件。这里先定义  no_such_table  条件,遇到 1146  错误就执行  CONTINUE  操作。

     //方法四:  使用  SQLWARNING
      DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
    

        第四种方法是使用  SQLWARNING。SQLWARNING  捕获所有以  01 开头的  sqlstate_value  然后执行  EXIT  操作,并且输出  ERROR  信息。

      //方法五:  使用 NOT FOUND
       DECLARE   EXIT   HANDLER  FOR   NOT  FOUND  SET @info= 'NO_SUCH_TABLE' ;

        第五种方法是使用  NOT FOUND。NOT FOUND  捕获所有以  02 开头的  sqlstate_value  值然后执行  EXIT  操作,并且输出NO_SUCH_TABLE 信息。

     //方法六:  使用 SOLEXCEPTION
      DECLARE  EXIT HANDLER   FOR   SQLEXCEPTON SET @infom='ERROR';
        第六种方法是使用  SQLEXCEPTION。SQLEXCEPTION  捕获所有未被  SQLWARNING  或  NOT FOUND 捕获的  sqlstate_ value值, 然后执行  EXIT 操作,并且输出  ERROR  信息。

      

    【例】定义条件和处理程序。

    mysql> CREATE TABLE t(s1 int,primary key(s1));
    Query OK, 0 rows affected
    
    mysql> DELIMITER //
    mysql> CREATE PROCEDURE handlerdemo()
        -> BEGIN
        -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
        -> SET @x=1;
        -> INSERT INTO t VALUES(1);
        -> SET @x=2;
        -> INSERT INTO t VALUES(1);
        -> SET @x=3;
        -> END;
        -> //
    Query OK, 0 rows affected
    
    mysql> DELIMITER ;
    mysql> CALL handlerdemo();
    Query OK, 0 rows affected
    
    mysql> SELECT @x;
    +----+
    | @x |
    +----+
    |  3 |
    +----+
    1 row in set


    5.   光标的使用

        查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。

        光标必须在声明处理程序之前被声明,并且变量和条件还必须在声明光标或处理程序之前被声明。 

        (1)   声明光标

        语法格式:

        DECLARE   cursor_name   CURSOR   FOR   selec_statement

    •     cursor_name  参数表示光标的名称;
    •      select_statement  参数表示  SELECT  语句的内容,返回一个用于创建光标的结果集。

       【例】 声明名称为  cursor_fruit 的光标。代码如下:

    DECLARE cursor_fruits CURSOR FOR SELECT f_name,f_price FROM fruits;
          光标的名称为 cursor_fruit,  SELECT  语句部分从 fruits  表中查询出  f_name  和  f_price 字段的值。


     (2)   打开光标

        语法格式:

        OPEN cursor_name

    【例】打开名为  cursor_ fruit  的光标。代码如下:

    OPEN cursor_fruits;

        (3)   使用光标

        语法格式:

        FETCH   cursor_name   INTO   var_name  [  , var_name  ]  ...  {参数名称}

    •     cursor_name  参数表示光标的名称;
    •     var_ name  参数表示将光标中的  SELECT  语句查询出来的信息存入该参数中,var_name  必须在声明光标之前就定义好。

        【例】 使用名为   cursor_fruit  的光标查询,并将查询出来的数据存入  fruit_name 和  fruit_price  两个变量中。

    代码如下:

    FETCH cursor_fruits INTO fruits_name,fruits_price;

        上面的示例中,将光标  cursor_fruit  中  SELECT  语句在询出来的信息存入  fruit_name 和  fruit_price   中。fruit_name 和  fruit_price  必须在前面已经定义。

        (4)   关闭光标

        语法格式:

        CLOSE cursor_name{光标名称}

         如果光标未被明确地关闭,就会在声明的复合语句的未尾被关闭 。 

        【例】关闭名为  cursor_fruit  的光标。代码如下:

      CLOSE cursor_fruit;

    【注】 MySQL中光标只能在存储过程和函数中使用。


    存储过程与自定义函数的区别

    • 存储过程实现的功能要复杂一些;而函数的针对性更强
    • 存储过程可以返回多个值;函数只能有一个返回值
    • 存储过程一般独立的来执行;而函数可以作为其他SQL语句的组成部分来出现。


    【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版

    更多相关内容
  • MySQL存储函数

    千次阅读 2021-06-09 11:25:16
    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。 1、创建存储函数

    MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。

    1、创建存储函数

    在MySQL中,创建存储函数使用CREATE FUNCTION关键字,其基本形式如下:

    CREATE FUNCTION func_name ([param_name type[,...]])
    RETURNS type
    [characteristic ...] 
    BEGIN
    	routine_body
    END;
    

    参数说明:

    (1)func_name :存储函数的名称。

    (2)param_name type:可选项,指定存储函数的参数。type参数用于指定存储函数的参数类型,该类型可以是MySQL数据库中所有支持的类型。

    (3)RETURNS type:指定返回值的类型。

    (4)characteristic:可选项,指定存储函数的特性。

    (5)routine_body:SQL代码内容。

    2、调用存储函数

    在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法基本相同。用户自定义的存储函数与MySQL内部函数性质相同。区别在于,存储函数是用户自定义的。而内部函数由MySQL自带。其语法结构如下:

    SELECT func_name([parameter[,…]]);
    

    示例:创建存储函数,实现根据用户编号,获取用户姓名功能。

    (1)先创建tb_user(用户信息表),并添加数据。

    -- 创建用户信息表
    CREATE TABLE IF NOT EXISTS tb_user
    (
    	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
    	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
    ) COMMENT = '用户信息表';
     
    -- 添加数据
    INSERT INTO tb_user(name) VALUES('jenrey`s csdn');
    INSERT INTO tb_user(name) VALUES('jenrey');
    INSERT INTO tb_user(name) VALUES('zhangsan');
    INSERT INTO tb_user(name) VALUES('lisi');
    INSERT INTO tb_user(name) VALUES('wangwu');
    INSERT INTO tb_user(name) VALUES('maliu');
    INSERT INTO tb_user(name) VALUES('https://blog.csdn.net/jenrey');
    

    查询数据结果:

    (2)创建存储函数

    -- 创建存储函数
    DROP FUNCTION IF EXISTS func_user;
    CREATE FUNCTION func_user(in_id INT)
    RETURNS VARCHAR(50)
    BEGIN
    	DECLARE out_name VARCHAR(50);
     
    	SELECT name INTO out_name FROM tb_user
    	WHERE id = in_id;
     
    	RETURN out_name;
    END;
    

    (3)调用存储函数

    -- 调用存储函数
    SELECT func_user(1);
    SELECT func_user(2);
    SELECT func_user(3);
    SELECT func_user(4);
    SELECT func_user(5);
    SELECT func_user(6);
    SELECT func_user(7);
    

    执行结果:

    3、修改存储函数

    MySQL中,通过ALTER FUNCTION 语句来修改存储函数,其语法格式如下:

    ALTER FUNCTION func_name [characteristic ...]
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
    

    上面这个语法结构是MySQL官方给出的,修改的内容可以包含SQL语句也可以不包含,既可以是读数据的SQL也可以是修改数据的SQL还有权限。此外在修改function的时候还需要注意你不能使用这个语句来修改函数的参数以及函数体,如果你想改变这些的话你就需要删除掉这个函数然后重新创建。

    4、删除存储函数

    MySQL中使用DROP FUNCTION语句来删除存储函数。

    示例:删除存储函数。

    DROP FUNCTION IF EXISTS func_user;
    

    其它补充:

    如果你在创建存储函数时提示以下错误:

    [Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is
    enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

    这是我们开启了bin-log, 我们就必须指定我们的函数是否是
    1 DETERMINISTIC 不确定的
    2 NO SQL 没有SQl语句,当然也不会修改数据
    3 READS SQL DATA 只是读取数据,当然也不会修改数据
    4 MODIFIES SQL DATA 要修改数据
    5 CONTAINS SQL 包含了SQL语句

    其中在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

    解决方法:

    解决办法也有两种, 第一种是在创建子程序(存储过程、函数、触发器)时,声明为DETERMINISTIC或NO SQL与READS SQL DATA中的一个, 例如: CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`()     DETERMINISTIC BEGIN #Routine body goes here... END;;

    第二种是信任子程序的创建者,禁止创建、修改子程序时对SUPER权限的要求,设置log_bin_trust_routine_creators全局系统变量为1。

    设置方法有三种:

    (1)在客户端上执行 SET GLOBAL log_bin_trust_function_creators = 1。

    (2)MySQL启动时,加上--log-bin-trust-function-creators选贤,参数设置为1。

    (3)在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加

    vi /etc/my.cnf

    log-bin-trust-function-creators=1

    再执行查看binlog参数的命令:show global variables like "%log_bin%"

    关于上面的配置解释可以参考:

    MySQL参数log_bin_trust_function_creators介绍

     

     

     

     

     

     

     

    展开全文
  • 在mysql中创建存储过程与函数

    千次阅读 2018-12-21 17:14:14
    1、创建存储过程和函数  存储过程是一条或者多条SQL语句的集合,相当于批处理文件,但是作用不... 创建存储过程是通过”CREATE PROCEDURE”语句来创建,语法格式: CREATE PROCEDURE sp_name ([proc_parame...

    1、创建存储过程和函数

        存储过程是一条或者多条SQL语句的集合,相当于批处理文件,但是作用不仅仅限于批处理。使用存储过程将简化操作,减少冗余的步骤,同时还可减少操作过程的失误,提高效率。

    (1)创建存储过程

             创建存储过程是通过”CREATE PROCEDURE”语句来创建,语法格式为:

    CREATE PROCEDURE sp_name ([proc_parameter])
    [characteristics…] routine_body

    各选项说明:

             CREATE PROCEDURE:创件存储过程的关键字

             sp_name:存储过程名称

             proc_parameter:指定存储过程的参数列表,列表形式为:[ IN | OUT | INOUT ] param_name type

             characteristics:用于指定存储过程的特性

             routine_body:mysql sql语句内容,使用BEGINE…END来表示SQL代码的开始和结束。

    # 创建存储过程前先将sql语句结束符改为//,以防止和默认的结束符冲突, 
    mysql> delimiter //
    mysql> create procedure proc()
        -> begin
        -> select * from course;
        -> end //
    Query OK, 0 rows affected (0.08 sec)
    # 存储过程创建完成后将结束符改为默认的结束符
    mysql> delimiter ;
    # 调用创建的存储过程
    mysql> call proc();
    +----+-------------+------------+
    | id | course_name | teacher_id |
    +----+-------------+------------+
    |  1 | math        |          3 |
    |  2 | english     |          2 |

    (2)创建存储函数

             函数与存储过程最大的区别就是函数调用有返回值,调用存储过程用call语句,而调用函数就直接引用函数名+参数即可,创建存储函数使用”CREATE FUNCTION”语句来创建,语法格式为:

    CREATE FUNCTION func_name ( param_name type )
    RESTURNS type
    [characteristic……] routine_body

    主要参数说明:

             Param_name:参数名称

             Type:参数类型

             RETURNS type:函数返回数据的类型

             Characteristic:指定存储函数的特性

    mysql> delimiter //
    # 创建一个函数
    mysql> create function stubak_update(param1 int)
        -> returns int
        -> begin
        -> update student_bak set gender=1 where sid=param1;
        -> select count(*) into @a from student_bak where sid>param1;
        -> return @a;
        -> end;
        -> //
    mysql> delimiter ;
    # 调用函数,并传入一个参数
    mysql> select stubak_update(1); 
    +------------------+
    | stubak_update(1) |
    +------------------+
    |                7 |
    +------------------+

    2、存储过程中使用变量

    (1)定义变量

             在存储过程中通过DECLARE语句定义变量,定义变量的格式如下:

             DECLARE var_name[,varname]… date_type [DEFAULT value];

             选项说明:

                       var_name:局部变量名称

                       DEFAULT value:用于给变量提供一个默认值

                       Type:用于指定变量的默认类型

    (2)为变量赋值

             声明后的变量可以通过select … into var_list进行赋值,或者通过set语句赋值,或者通过定义游标并使用fetch … into var_list赋值,使用set赋值的方式如下:

      SET var_name = expr [, var_name = expr] ……;
    # mysql存储过程中使用变量
    mysql> delimiter //
    mysql> create procedure sp1(v_sid int)
        -> begin
        -> declare xname varchar(10) default 'dayi123';
        -> declare xsex int;
        -> select sname,gender into xname,xsex from student_bak where sid=v_sid;
        -> select xname,xsex; 
        -> end;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    mysql> delimiter ;
    mysql> call sp1(1);
    +--------+------+
    | xname  | xsex |
    +--------+------+
    | Andrew |    1 |
    +--------+------+    
     

    3、定义条件和处理程序

             定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题是应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。

    (1)定义条件

             定义条件使用DECLARE语句,语法格式如下

      DECLARE condition_name CONDITION FOR [condition_type]
      [condition_type]:
      SQLSTATE [VALUE] sqlstate_value | mysql_error_code

    各选项说明:

             Condition_name:表示条件名称

             Condition_type:表示条件类型

             sqlstate_value | mysql_error_code:mysql中的错误,sqlstate_value为长度为5的字符串类型错误代码,mysql_error_code为数值类型的错误代码

    (2)定义处理程序

             定义处理程序时,使用DECLARE语句实现:

     DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement handler_type:
              CONTINUT | EXIT | UNDO
      Condition_value:
              SQLSTATE [VALUE] sqlstate_value | condition_name
      | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code

    各选项说明:

             Handler_type:错误处理方式,continue表示遇到错误不处理,exit为退出,undo为撤回之前操作。

             Condition_value表示错误类型,有以下的取值

                       SQLSTATE [VALUE] sqlstate_value:包含5个字符的字符串错误值

                       condition_name:declare condition定义错误的错误条件名称

                       SQLWARNING:匹配所有以01开头SQLSTATE错误代码

                       NOT FOUND:匹配所有以02开头的SQLSTATE错误代码

                       SQLEXCEPTION:匹配所有没有被SQLWARENING或NOT FOUNT捕获的SQLSTATE的错误代码

                       mysql_error_code:匹配数值类型的错误代码

             sp_statement:在遇到定义的错误时,需要执行的存储过程或函数。

    当condition发生但没有声明handler时,则存储过程和函数依照如下规则处理:

             发生SQLEXCEPTION错误,则执行exit退出

             发生SQLWARNING警告,则执行contine继续执行

             发生NOT FOUND情况,则执行continue继续执行

    # 创建一个存储过程并定义处理程序当错误吗为23000时跳过继续执行
    # 创建一张用于测试的表
    mysql> delimiter //
    mysql> create procedure handlerdemo()
        -> begin
        -> declare continue handler for sqlstate '23000' set @x2=1;
        -> set @x=1;
        -> insert into t values(1);
        -> set @x=2;
        -> insert into t values(1);
        -> set @x=3;
        -> end;
        -> //
    mysql> delimiter ;
    # 调用存储过程
    mysql> CALL handlerdemo();
    # 插看变量的值
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |    3 |
    +------+

    4、存储过程中的流程控制语句

        MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的流程控制语句,另外return语句也是函数中的特定流程控制语句。

    (1)case语句

             Case语句有两种语句格式分别如下:

             格式一:

     CASE case_value
              WHEN when_value THEN statement_list
              [WHEN when_value THEN statement_list]……
              [ELSE statement_list]
      END CASE

             格式二:

     CASH 
              WHEN search_condition THEN statement_list
              [WHEN search_condition THEN statement_list]……
              [ELSE statement_list]
      END CASE

             创建存储过程时使用case语句

    mysql> delimiter //
    mysql> CREATE PROCEDURE exp_case(v_sid int)
        ->   BEGIN
        ->     DECLARE v INT DEFAULT 1;
        ->     select gender into v from student_bak where sid=v_sid;
        ->     CASE 
        ->       WHEN v=0 THEN update student_bak set gender=1 where sid=v_sid;
        ->       WHEN v=1 THEN update student_bak set gender=0 where sid=v_sid;
        ->       ELSE
        ->           update student_bak set gender=-1 where sid=v_sid;
        ->     END CASE;
        ->   END;
        -> //
    mysql> delimiter ;

    (2)if语句

             If语句包含多个条件判断,根据条件判断的结果为TRUE或FALSE执行相应的语句,语法格式为:

    IF search_condition THEN statement_List
      [ELSEIF search_condition THEN statement_list] …
      [ELSE statement_list]
    END IF

             创建一个函数,使用if语句判断两个数的大小

    mysql> CREATE FUNCTION exp_if(n INT, m INT)
        ->   RETURNS VARCHAR(20)
        ->   BEGIN
        ->     DECLARE s VARCHAR(20);
        ->     IF n > m THEN SET s = '>';
        ->     ELSEIF n = m THEN SET s = '=';
        ->     ELSE SET s = '<';
        ->     END IF;
        ->     SET s = CONCAT(n, ' ', s, ' ', m);
        ->     RETURN s;
    ->   END //
    mysql> DELIMITER ;
    mysql> select exp_if(1,2);
    +-------------+
    | exp_if(1,2) |
    +-------------+
    | 1 < 2       |
    +-------------+

             If语句也可在创建存储过程及函数时嵌套使用,

    mysql> CREATE FUNCTION exp_if02 (n INT, m INT)
        ->   RETURNS VARCHAR(50)
        ->   BEGIN
        ->     DECLARE s VARCHAR(50);
        ->     IF n = m THEN SET s = 'equals';
        ->     ELSE
        ->       IF n > m THEN SET s = 'greater';
        ->       ELSE SET s = 'less';
        ->       END IF;
        ->       SET s = CONCAT('is ', s, ' than');
        ->     END IF;
        ->     SET s = CONCAT(n, ' ', s, ' ', m, '.');
        ->     RETURN s;
        ->   END //
    mysql> delimiter ;

    (3)iterate语句

             Iterate语句仅出现在loop,repeat,while循环语句中,其含义表示重新开始此循环,格式如下:

    ITERATE label

             Label表示自定义的标签名

    (4)leave语句

             Leave语句表明退出指定标签的流程控制语句块,通常会用在begin…end,以及loop,repeat,while的循环语句中,格式如下:

    LEAVE label

             Label表示要退出的标签名

    (5)LOOP语句

             Loop语句是存储过程或函数中表达循环执行的一种方式,LOOP内的语句一直重复执行直到循环被退出,跳出循环时使用LEVAVE子句,LOOP语句的基本格式如下:

    [begin_label:] LOOP
      Statement_list
    END LOOP [ END_LABEL ]

             创建存储过程使用loop循环语句实现变量的自增

    mysql> DELIMITER //
    # 创建存储过程
    mysql> CREATE PROCEDURE exp_loop(p1 INT)
        -> BEGIN
        ->   label1: LOOP
        ->     SET p1 = p1 + 1;
        ->     IF p1 < 10 THEN
        ->       ITERATE label1;
        ->     END IF;
        ->     LEAVE label1;
        ->   END LOOP label1;
        ->   SET @x = p1;
        -> END //
    mysql> DELIMITER ;
    # 调用存储过程
    mysql> call exp_loop(1);
    # 查看执行后变量的值
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |   10 |
    +------+

    (6)repeat语句

             Repeat语句用于创建一个带条件判断的循环过程,每次语句执行完毕之后,会对条件表达式进行判断,如果表达式为真,则循环结束;否则重复执行循环中的语句。

    [repeat_label:] REPEAT
      Statement_List
    UNTIL search_condition
    END REPEAT [repeat_label]

             Repeat_label为REPEAT语句的标注名称,该参数可省略

    mysql> delimiter //
    # 创建基于repeat循环的存储过程
    mysql> CREATE PROCEDURE exp_rep(p1 INT)
        -> BEGIN
        ->   SET @x = 0;
        ->   REPEAT
        ->     SET @x = @x + 1;
        ->   UNTIL @x > p1 END REPEAT;
        -> END
        -> //
    mysql> delimiter ;
    # 调用存储过程,并查看变量@x的最终值
    mysql> call exp_rep(100);
    mysql> select @x;
    +------+
    | @x   |
    +------+
    |  101 |
    +------+

    (7)while语句

             While语句也是用于创建一个带条件判断的存储过程,与REPEAT不同的是while在执行语句时先对指定的表达式进行判断,如果为真,则执行循环内的语句,否则退出,语句格式如下:

    [while_label:] WHILE expr_condition DO
      Statement_list
    END WHILE [while_label]

             创建基于while循环的存储过程,相对于repeat循环是先判断在执行。

    mysql> DELIMITER //
    mysql> CREATE PROCEDURE exp_whi(p1 INT)
        -> BEGIN
        ->   SET @b = 0;
        ->   WHILE @b < p1 DO
        ->     SET @b = @b + 1;
        ->   END WHILE;
        -> END;
        -> //
    mysql> DELIMITER ;
    mysql> call exp_whi(100);
    mysql> select @b;
    +------+
    | @b   |
    +------+
    |  100 |
    +------+

    5、游标

             Mysql查询语句可能返回多条记录,如果数据量大则需要在存储过程中和储存函数中使用游标来逐条读取查询结果中的记录。应用程序可以根据需要滚动或浏览其中的数据。

    (1)声明游标

             游标必须在声明处理程序之前被声明,并且变量和条件必须在声明游标或处理程序之前被声明。声明游标的语句如下:

    DECLARE cursor_name CURSOR FOR select_statement

    (2)打开游标

             打开游标的语句如下:

    OPEN cursor_name

    (3)使用游标

             使用游标语句如下

    FETCH cursor_name INTO var_name [,var_name] ……

             var_name:表示将光标中的SELECT 语句查询出来的信息存入该参数中,var_name必须在声明光标之前就定义好。

             使用游标时,数据集中的字段需要和INTO语句中定义的变量一一对应,数据集中的数据都fetch完之后,则返回NOT FOUND。

    (4)关闭游标

             关闭游标语句如下:

    CLOSE cursor_name 

    (5)游标的使用

    mysql> DELIMITER //
    mysql> CREATE PROCEDURE exp_cur()
        -> BEGIN
        ->   DECLARE done INT DEFAULT FALSE;
        ->   DECLARE a CHAR(16);
        ->   DECLARE b, c INT;
        ->   DECLARE cur1 CURSOR FOR SELECT sname,dept_id FROM student;
        ->   DECLARE cur2 CURSOR FOR SELECT id FROM course;
        ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        ->   OPEN cur1;
        ->   OPEN cur2;
        ->   read_loop: LOOP
        ->     FETCH cur1 INTO a, b;
        ->     FETCH cur2 INTO c;
        ->     IF done THEN
        ->       LEAVE read_loop;
        ->     END IF;
        ->     IF b = c THEN
        ->       INSERT INTO test VALUES (a,b);
        ->     ELSE
        ->       INSERT INTO test VALUES (a,c);
        ->     END IF;
        ->   END LOOP;
        ->   CLOSE cur1;
        ->   CLOSE cur2;
    -> END //
    mysql> DELIMITER ;

    6、查看创建的存储过程和函数

             在创建和存储过程和函数后可以通过”show status”语句或”show create”语句来查看创建的存储过程和函数,也可直接从系统的”information_schema”数据库中查询。

             使用”show status”语句查看存储过程和函数状态语句结构如下:

    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]
    # 查看创建的所有以”exp”开头的存储过程
    
    mysql> show procedure status like 'exp%' \G
    *************************** 1. row ***************************
                      Db: course
                    Name: exp_case
                    Type: PROCEDURE
                 Definer: root@localhost
    ……

             使用”show create”查看存储和函数状态语句如下:

    SHOW CREATE {PROCEDURE | FUNCTION} sp_name

             从”information_schema.routines”表中查看存储过程和函数信息的语句结构如下:

    Select * from information_schema.routines where routing_name=’sp_name’;

    7、修改创建的存储过程和函数

             存储过程及函数穿件完成后可以通过”alter”语句来修改存储过程或函数的特性,修改的语句如下:

    ALTER {PROCEDURE|FUNCTION} sp_name [characteristic …]

    Characteristic参数用于指定存储函数的特性,取值有:

             CONTAINS SQL:表示子程序包含SQL语句,但不包含读或写数据的语句。

             NO SQL:表示子程序中不包含SQL语句

             READS SQL DATA:表示子程序中包含读数据的语句

             MODIFIES SQL DATA:表示子程序中包含写数据的语句

             SQL SECURITY {DEFINER|INVOKER}:指明谁有权限来执行

             DEFINER:表示只有定义者自己才能够执行

             INVOKER:表名调用者可以执行

             COMMENT ‘string’:表示注释信息

    8、删除存储过程及存储函数

             删除存储过程及存储函数使用”drop”语句来删除,语法格式如下:

    DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

             删除一个创建的存储过程:

    mysql> drop procedure exp_whi;
    Query OK, 0 rows affected (0.00 sec)

    9、mysql触发器

             触发器是一个特殊的存储过程,触发器的作用是当表上有对应SQL语句发生时,则触发执行。

    (1)创建触发器

             创建触发器的语句如下:

    CREATE
      [DEFINER = {user | CURRENT_USER }]
      TRIGGER trigger_name
      Trigger_time trigger_event
      ON tbl_name FOR EACH ROW
      [trigger_order]
      trigger_body

    各选项说明:

             Definer:用来指定trigger的安全环境

             trigger_name:标识触发器的名称

             Trigger_time:指定触发器的执行时间,BEFORE代表在数据修改前执行,AFTER代表在修改后执行。

             Trigger_event:指定触发该触发器的具体事件,INSERT当新的一行数据插入表中时触发,UPDATE当表的一行数据被修改时触发,DELETE当表的一行数据被删除时触发,当执行insert into … on duplicate key update语句时,当碰到重复行执行update时,则触发update下的触发器

             tbl_name:标识建立触发器的表名

             Trigger_body:表示触发器触发之后要执行的一个或多个语句,在内部可以引用涉及表的字段,OLD.col_name表示行数据被修改或删除之前的字段数据,NEW.col_name表示行数据被插入或修改之后的字段数据

    # 先创建一张表用于在触发触发器时备份要修改的数据
    mysql> create table student_back(sid int,old_sname varchar(12),sname varchar(12),old_gender int,gender int,update_time time)//
    mysql> delimiter //
    # 创建触发器
    mysql> create trigger update_trigger
        -> after update
        -> on student for each row
        -> begin
        -> insert into student_back values(old.sid,old.sname,new.sname,old.gender,new.gender,now());
        -> end;
        -> //
    mysql> delimiter ;
    mysql> update student set gender=1 where sid=1;
    # 更新数据后查看备份的数据
    mysql> select * from student_back;
    +------+-----------+--------+------------+--------+-------------+
    | sid  | old_sname | sname  | old_gender | gender | update_time |       
    +------+-----------+--------+------------+--------+-------------+
    |    1 | Andrew    | Andrew |          0 |      1 | 19:12:29    |
    +------+-----------+--------+------------+--------+-------------+

    (2)查看触发器

             触发器创建好后可以通过”show triggers”命令查看,也可在”triggers”表中查看触发器信息。

             通过命令查看:show triggers;

             通过表查看语句:select * from information_schema.triggers where trigger_name=' trigger_name ' \G

    (3)删除触发器

             触发器可以通过”drop trigger”语句来删除,删除触发器的语句格式为:

    DROP TRIGGER [schema_name.] trigger_name

             schema_name表示数据库的名称为可选参数

    # 删除触发器
    mysql> drop trigger update_trigger;                                                             
    Query OK, 0 rows affected (0.00 sec)

     

    展开全文
  • 开始之前 首先创建如下两张表,并初始化一些数据。 创建存储过程(CREATE PROCEDURE) ...基本语法格式如下: ...其中:CREATE PROCEDURE为创建存储过程的关键字;sp_name存储过程的名称(唯一性,没有所谓的重...

    开始之前

    首先创建如下两张表,并初始化一些数据。

    创建存储过程(CREATE PROCEDURE)

    基本语法格式如下:

    CREATE PROCEDURE sp_name (parameters)
    [characteristics ...] routine_body

    其中:CREATE PROCEDURE为创建存储过程的关键字;sp_name为存储过程的名称(唯一性,没有所谓的重载方法概念);parameters为参数列表;characteristics指定存储过程的特性(该部分可以省略,即使用默认声明);routine_body是SQL代码的内容,可以用BEGIN...END来表示SQL代码的开始和结束。

    parameters 表现形式 --> [IN(OUT/INOUT)] param_name type。IN代表入参、OUT代表出参、INOUT代表既可以是入参也可以是出参, param_name参数名称,type是类型(类型是mysql数据库中支持的任意类型,VARCHAR需要指定长度,如VARCHAR(255))。

    characteristics 表现形式 --> 

    名称说明
    LANGUAGE SQL说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值
    [NOT] DETERMINISTIC指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为 NOT DETERMINISTIC
    {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;NO SQL表明子程序不包含SQL语句;READS SQL DATA说明子程序包含读写读数据的SQL语句;MODIFIES SQL DATA表明子程序包含写数据的语句;默认情况下,系统会指定为CONTAINS SQL。
    SQL SECURITY {DEFINER | INVOKER}指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER
    COMMENT 'string'注释信息,可以用来描述存储过程或存储函数。

    --简单的示例

    DELIMITER $$  -- 声明结束符
    CREATE PROCEDURE list_emp() -- 创建无参数存储过程
    	COMMENT '查询所有员工及其所在部门' -- characteristics 部分(可以省略)
    	BEGIN
    		SELECT * FROM tb_emp e
    		INNER JOIN tb_dept d
    		ON e.dept_id = d.dept_id;
    	END $$
    DELIMITER ; -- 恢复默认结束符
    
    CALL list_emp(); -- 调用存储过程
    
    SHOW PROCEDURE STATUS LIKE '%list_emp%'; -- 查看存储过程
    
    DROP PROCEDURE list_emp;  -- 删除存储过程,目前编写错误的话直接删除重写

    存储过程定义详情(SHOW PROCEDURE STATUS LIKE '%list_emp%'; -- 查看存储过程):

    执行结果(CALL list_emp(); -- 调用存储过程):

    --有参数的存储过程

    DELIMITER $$
    CREATE PROCEDURE count_emp(OUT total_emp INT)
    	COMMENT '统计员工数量'
    	BEGIN
    		SELECT COUNT(*) INTO total_emp FROM tb_emp;
    	END $$
    DELIMITER ;
    
    SET @total_emp = 10; -- 声明变量
    
    CALL count_emp(@total_emp); -- 调用存储过程
    
    SELECT @total_emp; -- 获取结果
    
    DROP PROCEDURE count_emp; -- 删除存储过程

    执行结果:

    总结说明

    1、这里的'DELIMITER $$'语句的作用是将mysql的结束符号设置为'$$',因为mysql默认的结束符号是';',为了避免与存储过程中的SQL语句结束符号冲突,需要使用'DELIMITER'改变存储过程的结束符,并以'END $$'结束存储过程。存储过程定义完毕再以'DELIMITER ;'恢复默认结束符号。亦可以指定其它符号为结束符,但是不能用反斜杠'\',它是mysql中的转义符。当然,简单的存储过程不更改结束符大多数情况下也是不会出现错误的。

    2、有参数的时候,VARCHAR类型需要指定长度,比如VARCHAR(255)。

    创建存储函数(CREATE FUNCTION)

    基本语法格式如下:

    CREATE FUNCTION func_name(params)
    RETURNS type
    [characteristics ...] routine_body

    其中:CREATE FUNCTION为创建存储函数的关键字;func_name为存储函数的名称(唯一性,没有所谓的重载方法概念);params为参数列表;RETURNS type语句表示函数返回数据的类型,可以是mysql中的任意数据类型;characteristics指定存储函数的特性(和存储过程一样);routine_body是存储函数主体。

    参数列表:IN、OUT、或INOUT只对PROCEDURE是合法的(FUNCTION中总是默认IN参数,所以声明存储函数入参的时候不不能声明IN,会报错,因为默认是IN)。RETURNS子句只能对FUNCTION作指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。

    -- 简单的示例:

    DELIMITER $$ -- 更改结束符
    CREATE FUNCTION count_dept()
    	RETURNS INT
    	COMMENT '统计部门数量'
    	BEGIN
    		RETURN (SELECT COUNT(*) FROM tb_dept);
    	END $$
    DELIMITER ; -- 还原结束符
    
    DROP FUNCTION count_dept; -- 删除存储函数
    
    SHOW FUNCTION STATUS LIKE '%count_dept%';  -- 查看存储函数定义
    
    SELECT count_dept();  -- 调用存储函数

    执行结果(SELECT count_dept();  -- 调用存储函数):

    变量的使用(关键字:DECLARE)

    变量可以在子程序中声明并使用,这些变量的作用域范围是在BEGIN...END程序中,不能单独在存储过程外部声明变量

    1、定义变量

    基本语法:

    DECLARE var_name[,var_name1]... date_type [DEFAULT value];

    var_name是局部变量的名称,可以同时声明多个变量,但是类型只能声明一次,就是说声明多个变量只能是同类型;date_type是变量类型,可以是mysql中任意的数据类型(VARCHAR需要指明长度,例如VARCHAR(255));DEFAULT 为变量设置默认值,如果是多参数,不能使用,默认值可以被声明为常量,也可以指定一个表达式。如果没有指定默认值,则为null

    简单的示例:

    DECLARE param1 INT DEFAULT 10; -- 声明单参数
    
    DECLARE param1, param2, param3 INT; -- 声明多参数

    2、为变量赋值

    定义变量后,可以为变量赋值以改变其值。

    通过SET...为变量赋值,基本语法:

    SET var_name = expr[,var_name=wxpr]....; -- 可以同时为单个或多个变量赋值,expr可以是具体的值,也可以是表达式。

    --简单的示例

    DECLARE param1, param2, param3 INT; -- 声明3个变量
    SET param1=10, param2=20; -- 为param1和param2赋具体的值
    SET param3 = param1 + param2; -- 将param1和param2的运算结果赋值给param3

    通过SELECT...INTO...为一个或多个变量赋值,基本语法如下:

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

    这个SELECT语法把选定的列直接存储到对应位置的变量(所以要求col_name和var_name一一对应)。col_name表示字段名称,var_name表示定义的变量名称;table_expr表示查询条件表达式,包括名称和WHERE子句。

    --简单的示例:

    SELECT dept_name, dept_addr INTO v_dept_name, v_dept_addr FROM tb_dept WHERE dept_id = 1;

    事务的使用

    事务并不会影响存储过程或存储函数的执行顺序,也不会中断执行(存储过程或存储函数始终会执行到END)。

    START TRANSACTION; -- 开始事务
    
    COMMIT; -- 提交事务
    
    ROLLBACK; -- 回滚事务

    流程控制的使用

    流程控制语句用来根据条件,控制语句的执行。mysql中用来构造控制流程的语句有:IF、CASE、LOOP、LEAVE、ITERATE、REPEAT、WHILE。

    IF语句(说明:mysql中还有一个IF()函数,注意区别):

    注意:IF语句必须配合THEN、END IF使用。除了IF还有ELSEIF(没有分开)和ELSE。

    基本语法:

    IF (expr_condition) THEN
    	....do something.....
    ELSEIF (expr_condition) THEN
    	....do something.....
    ELSE
    	....do something.....
    END IF;

    说明:如果表达式expr_condition计算结果为true,则执行THEN后面的逻辑,如果都不匹配则执行ELSE。条件判断表达式建议用括号包起来,增加可读性。

    CASE语句

    CASE语句也是一个条件判断语句。需要配合WHEN、THEN和END CASE使用

    CASE语句有两种语法格式:

    CASE
    	WHEN expr_condition THEN ...do something...
    	WHEN expr_condition THEN ...do something...
    	....多个 WHEN THEN 语句.....
            [ELSE ...do something...]
    END CASE;

    其中expr_condition为条件表达式,计算结果为true,则执行THEN后面的语句。多个WHEN...THEN依次执行,ELSE为可选条件。

    另外一种语法格式:

    CASE case_expr
    	WHEN expr THEN .....do something....
    	WHEN expr THEN .....do something...
    	....多个表达式...
    	[ELSE ...dosomething..]
    END CASE;

    其中,case_expr表示条件判断的表达式,WHEN后的表达式结果如果和case_expr匹配,则执行相应的THEN后面的语句。没有则执行ELSE,ELSE为可选。这种语法类似switch...case(建议使用第一种,逻辑清晰一点)。

    LOOP、REPEAT、WHILE、LEAVE和ITERATE

    其中LOOP、REPEAT和WHILE都是创建循环语句的关键词。

    LEAVE用来退出任何被标注的循环语句(类似break,只能用于循环语句)。

    ITERATE语句将执行顺序转到语句段开头处(类似continue,只能用于循环语句)。

    LOOP基本语法(需要配合 END LOOP):

    [loop_label]:LOOP
    	...do something....
    END LOOP [loop_label];

    其中,loop_label为可选,表示LOOP体的标签,do something为循环体。

    LOOP简单示例(配合LEAVE和ITERATE):

    DECLARE id INT DEFAULT 0;
    add_loop:LOOP
    	SET id = id + 1;
    	IF(id>10) THEN
    		LEAVE add_loop;
    	ELSE
    		ITERATE add_loop;
    	END IF;
    END LOOP add_loop;

    REPEAT基本语法(需要配合 UNTIL和END REPEAT):

    [repeat_label:] REPEAT
    	....do something....
    UNTIL expr_condition;
    END REPEAT [repeat_label];

    其中,repeat_label为可选,表示REPEAT循环语句的标签,UNTIL指定循环条件。REPEAT执行过程是,每次循环体执行完毕需要去判断一下条件表达式expr_condition,如果为true继续执行,否则结束循环。

    WHILE基本语法(需要配合DO和END WHILE使用):

    [while_lebel:] WHILE expr_condition DO
    	.....do something....
    END WHILE [while_label];

    其中,while_label为可选,表示WHILE循环语句的标签。WHILE的执行过程是,先判断条件表达式expr_condition,如果为true执行循环,否则结束循环(与REPEAT的区别是:WHILE先判断条件,REPEAT是后判断条件)。

    调用存储过程

    基本语法:

    CALL sp_name([params,...]);

    说明:如果存储过程没有参数,不能省略括号;参数个数要与存储过程定义的入参和出参个数匹配,用逗号隔开;出参类型的参数需要加上@符号。

    调用存储函数

    基本语法:

    SELECT func_name([params...]);

    说明:如果存储函数没有参数,不能省略括号;因为存储函数都是入参,所以只需注意匹配个数即可。

    查看存储过程和存储函数

    基本语法:

    SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern'];

    说明:查看存储过程即PROCEDURE、存储函数即FUNCTION。LIKE为过滤条件(按存储过程、函数名字匹配),如果没有则是查看所有定义。

    SHOW [CREATE | PROCEDURE] FUNCTION sp_name;

    此语句查看定义存储函数、存储过程的脚本。

    删除存储过程、存储函数

    基本语法:

    DROP [PROCEDURE | FUNCTION] [IF EXISTS] sp_name;

    说明:sp_name为存储过程或存储函数的全称。[IF EXISTS]是mysql的一个扩展,建议使用。如果存储过程或函数不存在,它可以防止错误的产生,但是会产生一个用SHOW WARNINGS查看的警告。

    修改存储过程、存储函数

    注意:只能修改存储过程或函数的定义,不能修改执行的逻辑代码或参数

    基本语法:

    ALTER [PROCEDURE | FUNCTION] sp_name [characteristic.....];

    其中,sp_name为存储过程或函数的名称,characteristic指定存储过程的特性。可以修改的特性和定义存储过程的时候的可选特性是一样的,不重复累赘


    总结

    1、存储过程和存储函数的区别(除了关键字:PROCEDURE、FUNCTION)

    本质上都是存储程序。

    参数类型不同:存储函数不允许声明出参类型,只能通过return关键字返回;

    调用方式不同:存储过程用CALL,存储函数用SELECT;

    存储函数限制比较多,多以建议使用存储过程,慎用存储函数。

    2、修改存储过程、存储函数

    存储过程或存储函数中的代码是不提供修改的,只能通过drop删除后,重新编写,或者直接编写一个新的程序。只能修改存储过程或存储函数的特性。

    3、存储过程和存储函数可以相互调用

    存储过程和存储函数包含自定义的SQL语句集合,所以,可以使用CALL或SELECT调用其它存储过程和存储函数。但是不能使用DROP删除其它存储过程和存储函数。

    4、注意区别参数名字和表中的字段名

    在定义存储过程和存储函数的时候,参数名称一定要与表中的字段名区别开来,否则可能出现无法预期的结果。

     

    展开全文
  • 存储过程,函数,触发器
  • 注意:关于示例所讲到的存储过程和函数及数据库表的创建,可通过查看之前的文章有进行介绍。...存储过程和存储函数创建以后,用户可以查看存储过程和存储函数的状态和定义。用户可以通过SHOW STATUS语句查看...
  • SQL Server常用函数 & SQL语法函数

    万次阅读 2020-08-07 09:29:10
    一、字符转换函数 1、ASCII() 返回字符表达式最左端字符的ASCII 码值。在ASCII()函数中,纯数字的字符串可不用‘’括起来,但含其它字符的字符串必须用‘’括起来使用,否则会出错。 2、CHAR() 将ASCII 码转换...
  • 通过CREATE FUNCTION语句创建函数 创建函数语法如下 CREATE FUNCTION function_name([function_parameter[,]]) RETURNS TYPE [characteristic] routine_body 其中: function_name表示所在创建函数的名字 ...
  • MySQL创建存储过程

    千次阅读 2021-02-03 07:36:36
    在开发过程中,经常会遇到重复使用某一个功能的情况,为此,MySQL引入了存储过程...大大减少数据库开发人员的工作量创建存储过程使用CREATE PROCEDURE语句CREATE PROCEDURE sp_name([proc_parameter])[characteri...
  • 从头开始学MySQL-------存储过程与存储函数(1)

    万次阅读 多人点赞 2018-11-03 17:00:19
    10.1.1 创建存储过程 存储过程就是一条或者多条SQL语句的集合... 创建存储过程的语句 CREATE PROCEDURE,创建存储函数的语句CREATE FUNCTION。 调用存储过程的语句CALL。 调用存储函数的形式就像调用MyS...
  • MySQL修改、删除存储过程和函数

    千次阅读 2019-09-02 16:49:04
    一、修改 ...其中,sp_name参数表示存储过程或函数的名称,characteristic参数指定存储函数的特性,可能取的取值有: contains SQL 表示子程序包含SQL语句,但不包含读写数据的语句。 no SQL 表示子...
  • 存储过程和函数能够将复杂的SQL逻辑封装在一起,应用程序无须关注存储过程和函数内部复杂的SQL逻辑,而只需要简单地调用存储过程和函数即可。 1.存储过程概述 1.1 概述 含义:存储过程的英文是 Stored Procedure 。...
  • 创建存储过程3. 调用存储过程4. 存储函数的使用5. 存储过程和函数的查看、修改、删除6. 关于存储过程使用的争议 MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起,应用程序...
  • GBase 8a 支持存储过程、自定义函数的定义和使用
  • Oracle--函数创建、调用和删除)

    千次阅读 2020-01-04 22:46:34
    函数 函数一般用于计算和返回一个值,可以将经常需要进行的计算写成函数函数的调用是表达式的一部分,而过程的...创建函数语法创建存储过程的语法也很相似。它们在数据库内采用相同的规则,参数的传递也相同。...
  • 存储过程和函数的操作

    千次阅读 2021-11-22 20:40:24
    2.2 创建函数语法形式 在MySQL中创建函数通过SQL语句CREATE FUNCTION来实现,其语法形式如下: CREATE FUNCTION function _name([function_parameter[,…]]) [characteristic…] routine_body; 在上述语句中,...
  • Python基础语法02:函数

    千次阅读 多人点赞 2022-05-15 09:22:23
    函数
  • SQLServer之创建标量函数

    千次阅读 2018-11-19 14:19:38
     用户定义函数是接受参数、执行操作(例如复杂计算)并将操作结果以值的形式返回的 Transact-SQL 或公共语言运行时 (CLR) 例程。 返回值可以是标量(单个)值或表。 使用此语句可以创建可通过以下方式使用的...
  • mysql中常用函数存储过程的创建

    千次阅读 2020-07-11 16:48:29
    mysql中常用函数存储过程的创建常用函数汇总数学函数字符串函数日期和时间函数条件判断函数系统函数加密函数其他函数自定义函数自定义变量的声明和赋值基本语法实例存储过程事务基本语法实例函数存储过程的区别 ...
  • Arduino 函数语法详解(含示例)三

    千次阅读 多人点赞 2019-02-01 18:17:13
    补救的办法是 使用pinMode()函数设置输出引脚。 注意:数字13号引脚难以作为数字输入使用,因为大部分的控制板上使用了一颗LED与一个电阻连接到他。如果启动了内部的20K上拉电阻,他的电压将在1.7V左右,而不是...
  • 数据库 存储过程和函数

    千次阅读 2018-04-15 12:55:24
    存储过程和函数的引入 存储过程和函数是在数据库中定义一些 SQL 语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。...创建存储过程和函数 1,创建存储过程 CREATE PROCEDURE sp_name(...
  • MySQL调用存储过程和函数

    千次阅读 2021-01-18 18:38:01
    存储过程和存储函数都是存储在服务器端的 SQL 语句集合。要想使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现。存储过程通过 CALL 语句来调用,存储函数的使用方法与 MySQL 内部函数的使用方法...
  • 8.存储过程实验 8.1存储过程定义 存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库...
  • 存储过程、函数、触发器的区别 比较项目 存储过程 函数 是否有返回值 可以有,也可以没有 必须有且只有一个 是否可以单独执行 可以 必须通过execute...
  • 存储程序可以分为存储过程和存储函数。 简单来说,存储过程就是一条或者多条SQL语句的集合,可视批文件,但是起作用不仅限于批处理。 使用call语句来调用存储过程,只能用输出变量返回值。 函数可以从语句外调用...
  • Python函数可变参数(*args,**kwargs)详解

    千次阅读 2021-04-27 01:28:57
    Python 在定义函数时也可以使用可变参数,即允许定义参数个数...1) 可变参数:形参前添加一个 '*'此种形式语法格式如下所示:*argsargs 表示创建一个名 args 的空元组,该元组可接受任意多个外界传入的非关键字...
  • mysql存储过程和函数总结

    千次阅读 2018-05-24 16:24:54
    存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数...
  • MySql存储过程与函数

    千次阅读 2015-08-06 08:21:26
    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中...
  • Matlab创建文件、function文件和函数调用

    万次阅读 多人点赞 2019-07-08 15:38:08
    目录 一、建立文件 二、函数调用 二、函数句柄 三、匿名函数 ...创建ffib.m文件,编写求Fibonacci数列的函数 function 输出形参表=函数名(输入形参表) 注释说明部分 函数体 若输出参数仅...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 169,555
精华内容 67,822
关键字:

创建存储函数的语法形式为