mysql存储过程函数_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