MySQL：存储过程和函数

2022-07-12 20:15:04

存储过程和函数

一、创建存储过程和函数

1、创建存储过程

语法：

CREATE

    PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

create procedure：存储的关键字
sp_name：存储过程的名字
proc_parameter：存储过程的参数列表characteristic存储过程的特性
- 格式：
  - [ IN | OUT | INOUT ] param_name type
- 介绍：
  - in----输入，out----输出，inout----可以输入也可以输出
  - param_name：参数名称
  - type：参数的类型（MySQL的任意类型）
routine_body sql的代码

【例】

mysql> delimiter //

mysql> create procedure guocheng() show databases;

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

delimiter语句将结束符设置为//。
注意：
create procedure关键字
guocheng()过程名
show databases;函数hangshu（）的作用：显示所有的数据库；
使用delimiter语句时，不要使用“/”；因为“/”是MySQL的转移符；

2、创建存储函数

语法：

CREATE

    FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

[characteristic ...] routine_body

create function 存储函数的关键字
sp_name存储函数的名称
func_parameter存储过程的参数列表type-----函数返回的数据类型
- 格式：
  - [ IN | OUT | INOUT ] param_name type
- 介绍：
  - in----输入，out----输出，inout----可以输入也可以输出
  - param_name 参数名称
  - type 参数的类型（MySQL的任意类型）
routine_body sql的代码
characteristic存储过程的特性

【例】

mysql> delimiter //

mysql> create

    -> function hangshu()

    -> returns char(50)

    -> return (select * from guke);

    -> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

3、变量

介绍变量

变量可以在子程序中声明并使用，这些变量的使用范围是begin……end程序中

定义变量语法：

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

介绍赋值变量语法：
- var_name 局部变量名称
- type变量类型
- DEFAULT value给变量提供一个默认值，默认值可以是常数，也可以是表达式，系统默认为空值

SET var_name=expr[,var_name=expr]……；

注意：不同的变量类型（局域声明变量及全局变量）可以被混合；
4、定义条件和处理程序

定义条件
- 语法：

DECLARE condition_name CONDITION FOR condition_value

  condition_value：

  SQLSTATE [VALUE] sqlstate_value| mysql_error_code

介绍
- condition_name 条件名字
- condition_value 条件类型
- sqlstate_value和 mysql_error_code表示MySQL的错误，使用这个语句需要它将一个名字和指定的错误条件想关联起来

定义处理程序

- 语法

DECLARE handler_action HANDLER

    FOR condition_value [, condition_value] ...

    statement

介绍
- Statement：表示程序语句块
- handler_action:表示错误处理方式
- CONTINUE| EXIT| UNDO
- CONTINUE：表示遇到错误不处理，继续执行
- EXIT：表示遇到错误马上退出
- UNDO：遇到错误回到前一次的操作，MySQL暂时不支持
- condition_value:表示错误类型

5、光标的使用------光标只能在存储过程和函数中使用

声明光标
- 语法：declare 光标名 cursor for select语句内容
打开光标
- 语法：open 光标名
使用光标
- 语法：
- fetch cursor_name into var_name[,var_name]……{参数名称}
  - 说明
  - cursor_name 光标名
  - var_name 表示将光标中的select语句查询出来的信息存入该参数中。
  - 【注意】
  - 必须在声明光标之前就定义好
关闭光标
- 语法：
- close 光标名
  - 【注意】
  - 如果没有被明确地关闭，光标在它被声明的复合语句的末尾被关闭

6、流程控制的使用

if语句
- 介绍：
  - 包含多个条件判断，根据判断结果为true或false执行相应的语句，与Java语句类似
- 语法：

IF search_condition THEN statement_list

    [ELSEIF search_condition THEN statement_list] ...

    [ELSE statement_list]

END IF

case语句
- 语法：

CASE case_value

    WHEN when_value THEN statement_list

    [WHEN when_value THEN statement_list] ...

    [ELSE statement_list]

END CASE

- 参数说明
  - case_value 表示条件判断的表达式，决定那个when语句执行
  - when_value表示表达式可能的值，如果when_value与case_value相等，则执行在then后面的statement_list语句；
  - statement_list表示不同条件的执行语句，

loop语句
- 介绍：
  - 循环语句重复执行语句
- 语法：

[begin_label:] LOOP

    statement_list

END LOOP [end_label]

- 参数说明
  - begin_label:表示标注名称
  - statement_list：要循环的语句
leave语句
- 介绍：
  - 退出循环
- 语法：
  - LEAVE label
- 参数说明：
  - Label：退出循环的要求
iterate语句
- 介绍：
  - 将执行顺序转到语句段开头处
- 语法：
  - ITERATE label
- 参数说明
  - Label：开始循环的要求
- 注意：
  - 只能用于LOOP、REPEAT和WHILE语句中

while语句
介绍：
带条件判断的循环过程，与repeat语句不同，先对指定的表达式进行判断，如果符合，则开始循环，否则退出循环；
语法：

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

参数说明
- begin_label:表示标注名称
- search_condition条件表达式

二、调用存储过程和函数

1、调用存储过程

语法：

call 存储过程名字（[存储过程的参数[,……]])

2、调用存储函数

语法：

select 存储函数名字（[存储过程的参数[,……]])

三、查看存储过程和函数

1、使用show status语句查看存储过程和函数的状态

语法：

show {procedure|function}status[like’存储过程或存储函数的名称’]

注意：

显示：数据库、名字、类型、创建者、创建和修改日期

2、使用show create语句查看存储过程和函数的定义

语法：

show create{procedure|function}存储过程或存储函数的名称

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

语法：

select * from information_schema.Routines

where routine_name=’存储过程或存储函数的名称’;

四、修改存储过程和函数

语法：

ALTER {procedure|function} 存储过程或存储函数的名称 [characteristic ...]

characteristic:

    COMMENT 'string'

  | LANGUAGE SQL

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

五、删除存储过程和函数

语法：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

码农公寓