MySQL存储过程和函数(九)

Published on in MySQL with 0 views and 0 comments

存储过程和函数

  • 存储过程就是一条或者多条SQL语句的集合,可视为批文件,但是其作用不仅限于批处理

创建存储过程和函数

  • 存储程序可以分为存储过程函数,MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURECREATE FUNCTION。使用CALL语句来调用存储过程,只能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。

创建存储过程

  • 创建存储过程需要使用CREATE PROCEDURE语句,基本语法格式如下:

    DELIMITER //   --将结束符号指定为//
    CREATE PROCEDURE sp_name([proc_parameter])
    [characteristics...] BEGIN routine_body;
    END //
    

    CREATE PROCEDURE为用来创建存储函数的关键字sp_name存储过程的名称routine_body是SQL代码的内容,可以用BEGIN...END来表示SQL代码的开始和结束;proc_parameter为指定存储过程的参数列表,列表形式如下:

    [ IN | OUT | INOUT ] param_name type;
    

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

    characteristics指定存储过程的特性,有以下取值:

    • LANGUAGE SQL:说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值。
    • [NOT] DETERMINISTIC:指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为NOT DETERMINISTIC
    • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFY SQL DATA }:指明子程序使用SQL语句的限制。CONTAINS SQL 表明子程序包含SQL语句,但是不包含读写数据的语句;NO SQL 表明子程序不包含SQL语句;READS SQL DATA 说明子程序包含读数据的语句;MODIFYS SQL DATA 表明子程序包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL
    • SQL SECURITY { DEFINER | INVOKER }:指明谁有权限来执行。DEFINER 表示只有定义者才能执行。INVOKER 表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER
    • COMMENT 'string':注释信息,可以用来描述存储过程或函数。
  • 提示:“DELIMITER //” 语句的作用是将MySQL的结束符设置为//,并以“END //”结束存储过程。存储过程定义完毕之后再使用 “DELIMITER ;” 恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。当使用DELIMITER 命令时,应该避免使用反斜杠(‘\’)字符,因为反斜杠是MySQL的转义字符。

创建存储函数

  • 创建存储函数,需要使用CREATE FUNCTION语句,基本语法格式如下:

    CREATE FUNCTION func_name( [func_parameter] )
    RETURNS type
    [characteristics ...] routine_body; 
    --rountine_body里必须包含RETURN VALUE语句,格式为RETURN 返回内容;
    

    CREATE FUNCTION 为用来创建存储函数的关键字;func_name表示存储函数的名称func_parameter为存储过程中的参数列表,参数列表形式如下:

    [ IN | OUT | INOUT ] param_name type;
    

    func_parameter指定存储函数的参数列表,含义与创建存储过程时相同。

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

  • 提示:

    • 如果再存储函数中的RETURN语句返回一个类型不同于函数的RETURNS子句指定类型的值,返回值将被强制为恰当的类型。
    • 指定参数为INOUTINOUT只对PROCEDURE是合法的。(FUNCTION中总是默认为IN参数。)RETURNS子句只能对FUNCTION做指定,对函数而言是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。

变量的使用

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

    在存储过程中使用DECLARE语句定义变量,语法格式如下:

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

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

  2. 为变量赋值

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

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

    在存储过程中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量,或者是全局服务器变量,如系统变量或者用户变量。其中不同的变量类型(局域声明变量及全局变量)可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

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

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

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

定义条件和处理程序

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

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

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

    其中,condition_name参数表示条件的名称condition_type参数表示条件的类型sqlstate_valuemysql_error_type都可以表示MySQL的错误,sqlstate_value为长度为5的字符串类型错误代码mysql_error_code数值类型错误代码。例如:ERROR 1142(42000)中,sqlstate_value的值是42000,mysql_error_code的值为1142。

  2. 定义处理程序

    定义处理程序时,使用DECLARE语句的语法如下:

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

    其中,handler_type为错误处理方式,参数取3个值:CONTINUEEXITUNDOCONTINUE表示遇到错误不处理,继续执行;EXIT遇到错误马上退出;UNDO表示遇到错误后撤回之前的操作,MySQL中暂时不支持这样的操作。

    condition_value表示错误类型,可以有以下取值:

    • SQLSTATE [VALUE] sqlstate_value包含5个字符的字符串错误值。
    • condition_name表示DECLARE CONDITION定义的错误条件名称。
    • SQLWARNING匹配所有以01开头的SQLSTATE错误代码。
    • NOT FOUND匹配所有以02开头的SQLSTATE错误代码。
    • SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。
    • mysql_error_code匹配数值类型的错误代码。

    sp_statement参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程或函数。

光标的使用

  • 查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
  • 光标的必须在声明处理程序之前被声明,并且变量和条件还必须在声明光标或处理程序之前被声明。
  1. 声明光标

    MySQL中使用DECLARE关键字来声明光标,其语法的基本形式如下:

    DECLARE cursor_name CURSOR FOR select_statement;
    

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

  2. 打开光标

    打开光标的语法如下:

    OPEN cursor_name{光标名称};
    
  3. 使用光标

    使用光标的语法如下:

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

    其中,cursor_name参数表示光标的名称;var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中,var_name必须在前面已经定义。

  4. 关闭光标

    关闭光标的语法如下:

    CLOSE cursor_name {光标名称};
    
  • 提示:MySQL中光标只能在存储过程和函数中使用。

流程控制的使用

  • 流程控制语句用来根据条件控制语句的执行。MySQL中用来构造控制流程的语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。
  1. IF语句

    • IF语句包含多个条件判断,根据判断的结果为true或false执行相应的语句,语法格式如下:

      IF expr_condition THEN statement_list
      	[ELSEIF expr_condition THEN statement_list]...
      	[ELSE statement_list]
      END IF;
      
    • 提示:MySQL中还有一个IF() 函数,它不同于这里描述的IF语句

  2. CASE语句

    • CASE是另一个进行条件判断的语句,有两种语句格式,CASE语句的第1种格式如下:

      CASE case_expr
      	WHEN when_value THEN statement_list
      	[WHEN when_value THEN statement_list] ...
      	[ELSE statement_list]
      END CASE;
      
    • CASE语句的第2种格式如下:

      CASE
      	WHEN expr_condition THEN statement_list
      	[WHEN expr_condition THEN statement_list] ...
      	[ELSE statement_list]
      END CASE;
      
    • 这些用在存储程序里的CASE语句与 “控制流程函数” 里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句,并且用END CASE替代END来终止。

  3. LOOP语句

    • LOOP循环语句重复执行某些语句,与IF与CASE语句相比,LOOP只是创建一个循环操作的过程,并不进行条件判断。LOOP内的语句一直重复执行,直到循环被退出,跳出循环过程,使用LEAVE语句,LOOP语句的基本格式如下:

      [lOOP_label:] LOOP
      	statement_list
      END LOOP [lOOP_label];
      

      loop_label表示LOOP语句的标注名称,该参数可以省略;statement_list参数表示需要循环执行的语句。

  4. LEAVE语句

    LEAVE语句用来退出任何被标注的流程控制构造,LEAVE语句的基本格式如下:

    LEAVE label;
    

    其中,label参数表示循环的标志。LEAVE和BEGIN...END或循环一起被使用。

  5. ITERATE语句

    ITERATE语句将执行顺序转到语句段开头处,语句基本格式如下:

    ITERATE label;
    

    ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为“再次循环”,label参数表示循环的标志。ITERATE语句必须跟在循环标志的前面。

  6. REPEAT语句

    REPEAT语句创建一个带条件判断的循环过程,每次语句执行完毕之后,会对条件表达式进行判断,若表达式为真,则循环结束;否则重复执行循环中的语句。REPEAT语句的基本格式如下:

    [repeat_label:] REPEAT
    	statement_list
    UNTIL expr_condition
    END REPEAT [repeat_label];
    

    REPEAT语句内的语句或语句群被重复,直到expr_condition为真。

  7. WHILE语句

    WHILE语句创建一个带条件判断的循环过程,与REPEAT不同,WHILE在执行语句时,先对指定的表达式进行判断,如果为真,就执行循环内的语句,否则退出循环。WHILE语句的基本格式如下:

    [while_label] WHILE expr_condition DO
    	statement_list
    END WHILE [while_label];
    

调用存储过程

  • 存储过程和函数有多种调用方法。存储过程必须使用CALL语句调用,并且存储过程和数据库相关,如果要执行其他数据库中的存储过程,需要指定数据库名称。存储函数的调用与MySQL中预定义的函数的调用方式相同。

调用存储过程

  • 存储过程就是通过CALL语句进行调用的,语法如下:

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

    CALL语句调用一个先前用CREATE PROCEDURE 创建的存储过程,其中sp_name为存储过程名称,parameter为存储过程的参数。

调用存储函数

  • 在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于,存储函数是用户自己定义的,而内部函数是MySQL的开发者定义的。

查看存储过程和函数

  • MySQL存储了存储过程和函数的状态信息,用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看,也可直接从系统的information_schema数据库中查询。

使用SHOW STATUS语句查看存储过程和函数的状态

  • SHOW STATUS 语句可以查看存储过程和函数的状态,其基本语法结构如下:

    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'patten'];
    

    这个语句是一个MySQL的扩展,它返回子程序的特征,如数据库、名字、类型、创建者及创建和修改日期。

使用SHOW CREATE语句查看存储过程和函数的定义

  • MySQL可以使用SHOW CREATE 语句查看存储过程和函数的状态。

    SHOW CREATE {PROCEDURE | FUNCTION} sp_name;
    

从information_schema.Routines表中查看存储过程和函数的信息

  • MySQL中存储过程和函数的信息存储在 information_schema 数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法格式如下:

    SELECT * FROM information_schema.Routines
    WHERE ROUTINES_NAME = 'sp_name';
    

    其中,ROUTINES_NAME字段中存储的是存储过程和函数的名称;sp_name参数表示存储过程或函数的名称。

修改存储过程和函数

  • 使用ALTER语句可以修改存储过程或函数的特性。其基本语法格式如下:

    ALTER {PROCEDURE | PROCEDURE} sp_name [characteristic ...];
    

    characteristic参数指定存储函数的特性,可能的取值有:

    • CONTAINS SQL:表示子程序包含SQL语句,但不包含读或写数据的语句
    • NO SQL:表示子程序中不包含SQL语句
    • READS SQL DATA:表示子程序中包含读数据的语句
    • MODIFIES SQL DATA:表示子程序中包含写数据的语句
    • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限来执行
      • DEFINER:表示只有定义者自己才能执行
      • INVOKER:表示调用者可以执行
    • COMMENT 'string':表示注释信息

删除存储过程和函数

  • 删除存储过程和函数,可以使用DROP语句,其语法结构如下:

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

    IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存在,那么它可以防止发生错误,产生一个用SHOW WARNING查看的警告。

注意事项

  • MySQL存储过程和函数有什么区别?

    在本质上它们都是存储程序。函数只能通过return语句返回单个值或者表对象;而存储过程不允许执行return,但是可以通过out参数返回多个值。函数限制比较多,不能使用临时表,只能用表变量,还有一些函数都不可用等,而存储过程的限制相对较少。函数可以嵌入SQL语句中使用,可以在SELECT语句中作为查询语句的一个部分调用;而存储过程一般是作为一个独立的部分来执行。

  • 存储过程中的代码可以改变吗?

    目前,MySQL没有提供对已存在的存储过程代码的修改。如果必须要修改存储过程,就只能删除后重建或者新建。

  • 存储过程中可以调用其他存储过程吗?

    可以。但不能使用DROP语句删除其他存储过程。

  • 存储过程的参数可以使用中文吗?

    当传入的参数值可能是中文时,需要在定义存储过程的时候,在后面加上character set gbk,不然调用存储过程中使用中文参数会出错。基本语法格式如下:

    CREATE PROCEDURE sp_name (IN|OUT|INOUT param_name param_type character set gbk [...])
    

标题:MySQL存储过程和函数(九)
作者:wangdj
地址:https://www.dblog.cool/articles/2024/11/13/1731507925137.html