MySQL：存储过程和函数

存储过程和函数

一、创建存储过程和函数

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