uft8 mysql 存储过程_MySQL-快速入门（8）存储过程、存储函数

秒速五厘米 2023-01-12 04:15 141阅读 0赞

1、存储过程

1》创建存储过程：create procedure

create procedure sp\_name (\[in | out | inout\]param\_name type)\[characteristics ...\] routine\_body

characteristics指定存储过程的特性：

1>language sql：说明routine\_body部分是由sql语句组成的，当前系统支持的语言为sql，sql是language特性的唯一值。

2>\[not\] deterministic：指明存储过程执行的结果是否确定。deterministic表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。not deterministic表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为not deterministic。

3>｛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。

4>sql security\{definer | invoker\}：指明谁有权限来执行。definer表示只有定义者才能执行。invoker表示拥有权限的调用者可以执行。默认情况下，系统指定为definer。

5>comment 'string'：注释信息，可以用来描述存储过程或函数。

routine\_body：sql代码的内容，可以用begin...end;来表示sql代码的开始和结束。

//最简单的存储过程

create procedurep\_get\_avg()begin...end;

//"delimiter //"的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号";"，

//为了避免与存储过程中sql语句的结束符相冲突，需要使用delimiter改变存储过程的结束符，并

//"end//"结束存储过程。"delimiter ;"则是恢复默认的";"结束符。

mysql> delimiter //mysql> create procedurep\_get\_avg()-> begin

\-> select \* fromtest;-> end//Query OK,0 rows affected (0.02sec)

mysql> delimiter ;

2、存储函数

1》创建存储函数：create function

//指定in、out、inout只对procedure是合法的，在function中总是默认为in类型

//如果return返回的类型不同于returns指定的类型，返回值将会被强制转换为恰当的类型。

create function func\_name(\[in | out | inout\]param\_name type)returnstype\[characteristic ...\] routine\_body

mysql> delimiter //mysql> create functionf\_get\_avg()-> returns char(25)-> begin

\-> return (select name from test where id=1);-> end//Query OK,0 rows affected (0.00sec)

mysql> delimiter ;

mysql> delimiter //mysql> drop function if existsf\_get\_avg;-> CREATE FUNCTIONf\_get\_avg()-> RETURNS int

\-> begin

\-> declare i int default 0;-> select 666 into i fromdual;-> returni;-> end//Query OK,0 rows affected (0.02sec)

Query OK,0 rows affected (0.02sec)

mysql>delimiter ;

mysql> selectf\_get\_avg();+-------------+

| f\_get\_avg() |

\+-------------+

| 666 |

\+-------------+

1 row in set (0.00sec)

mysql>

注：trigger和function都需要写成 select ...into  这种句式，否者会报1415错误？

CREATE DEFINER=\`root\`@\`localhost\` FUNCTION \`f\_get\_avg\`() RETURNS int(11)begin

declare i int default 0;/\*select 666 into i from dual;\*/

returni;end

CREATE DEFINER=\`root\`@\`localhost\` FUNCTION \`f\_get\_avg\`() RETURNS int(11)begin

declare i int default 0;/\*select 666 into i from dual;\*/

set i=999;returni;end

3、变量的使用

1》在存储过程中使用declare语句定义变量：

declare var\_name \[,var\_name\]... data\_type \[default value\];

即：

declare var1,var2,var3 int;

2》为变量赋值：定义变量后，MySQL使用set语句为变量赋值

set var\_name = expr \[,var\_name=expr\] ...;

或者

select col\_name\[,...\] into var\_name\[,...\] tb\_expr;

即：

set var1=10,var2=20;

4、定义条件和处理程序：都是使用declare关键字

1》定义条件

//codition\_name：表示条件的名称；condition\_type参数表示条件的类型；

//sqlstste\_value和mysql\_error\_code：都可以表示MySQL的错误

//sqlstate\_value：为长度为5的字符串类型错误代码。

//mysql\_error\_code：为数值类型错误代码。

//例如：error 1142(42000)中，sqlstate\_value的值是42000，mysql\_error\_code的值为1142。

declare condition\_name condition for \[condition\_type\]

其中\[condition\_type\]:sqlstate\[value\] sqlstate\_value

| mysql\_error\_code

即：

declare condition\_name condition for sqlstate \[value\] sqlstate\_value

或者

declare condition\_name condition for mysql\_error\_code

举例：

//定义"error 1148(42000)"错误，名称为command\_not\_allowed//方法一：使用sqlstate\_valuedeclare command\_not\_allowed condition for sqlstate '42000';//方法二：使用mysql\_error\_codedeclare command\_not\_allowed condition for 1148;

2》定义处理程序

declare handler\_type handler for condition\_value\[，...\]sp\_statement

sp\_statement：程序语句段，表示遇到定义的错误时，需要执行的存储过程或函数

//错误处理方式

//continue：表示遇到错误不处理。继续执行；

//exit：遇到错误马上退出；

//undo：表示遇到错误后撤回之前的操作，MySQL暂时不支持这样的操作。

handler\_type:continue | exit |undo

//错误类型

//sqlstate \[value\] sqlstate\_value：包含5个字符的字符串错误值

//condition\_name：表示declare condition定义的错误条件名称

//mysql\_error\_code：匹配数值类型错误代码

//sqlwarning：匹配所有以01开头的sqlstate错误代码

//not found：匹配所有以02开头的sqlstate错误代码

//sqlexception：匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码

condition\_value:

举例：

//方法一：捕获sqlstate\_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO\_SUCH\_TABLE';//方法二：捕获mysql\_error\_codeDECLARE CONTINUE HANDLER FOR 1146 SET @info='NO\_SUCH\_TABLE';//方法三：先定义条件，然后调用DECLARE no\_such\_table CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR NO\_SUCH\_TABLE SET @info='NO\_SUCH\_TABLE';//方法四：使用SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五：使用NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO\_SUCH\_TABLE';//方法六：使用SQLEXCEPTIONDECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

mysql> DELIMITER //mysql> CREATE PROCEDUREhandlerdemo ()-> 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 (0.02sec)

mysql>DELIMITER ;

mysql>CALL handlerdemo();

Query OK,0 rows affected (0.02sec)

mysql> select @x;+------+

| @x |

\+------+

| 3 |

\+------+

1 row in set (0.00sec)

mysql> select @x2;+------+

| @x2 |

\+------+

| 1 |

\+------+

1 row in set (0.00sec)

mysql> select \* fromt;+----+

| s1 |

\+----+

| 1 |

\+----+

1 row in set (0.00sec)

mysql>

@x是一个用户变量，执行结果@x等于3，说明存储过程被执行到了最后面一句。如果省去异常处理那一句，第2个insert因为主键约束强制失败之后，存储过程可能已经采取默认(exit)路径，此时select @x返回的结果可能是2。

"@var\_name"表示用户变量，使用set语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。

当客户端连接退出时，该客户端连接的所有变量将自动释放。

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

1》声明光标

declare cursor\_name cursor for select\_statement;

2》打开关标

open cursor\_name;

3》使用光标

fetch cursor\_name into var\_name\[，var\_name\] ...;

4》关闭光标

close cursor\_name;

举个例子：

create procedurep3()begin

declare id int;declare name varchar(15);declare flag int default 0;--声明游标

declare mc cursor for select \* fromclass1;declare continue handler for not found set flag = 1;--打开游标

openmc;--获取结果

loop\_label\_0:loopfetch mc intoid,name;if flag=1 then --当无法fetch会触发handler continue

leave loop\_label\_0;end if;--这里是为了显示获取结果

insert into class2 values(id,name);--关闭游标

endloop;closemc;end;

call p3();--不报错

select \* from class2;

6、流程控制使用

MySQL中用于流程控制的语句有：if、case、loop、leave、iterate、repeat、while语句。每个流程可能包含一个单独语句，或者使用begin...end构造的符合语句，构造可以被嵌套。

1》if语句

//statement\_list可以包含一个或者多个语句if expr\_condition thenstatement\_list\[elseif expr\_condition then statement\_list ...\]...\[else statement\_list\]

end if

2》case语句。与“控制流程函数”中的case是不同的。

//第一种方式：//匹配表达式的值casecase\_exprwhen when\_value thenstatement\_list\[when when\_value then statement\_list\]...\[else statement\_list\] //不能有else null子句

end case

//第二种方式：//逐个表达式执行，直到有一个表达式为true被执行case

when expr\_condition thenstatement\_list\[when expr\_condition then statement\_list\]...\[else statement\_list\] //不能有else null子句

end case

3》loop语句

\[loop\_label:\]loop

statement\_list

//如果要跳出循环,这时候label是必须的

if expr\_condition then leave loop\_label

end if;end loop \[loop\_label\]

4》leave语句

//用来退出任何被标注的流程控制构造，包括begin...end和循环体。

leave label

5》iterate语句

//将执行顺序转到语句段开头处

//iterate只可以出现在loop、repeat、while语句内，iterate表示再次循环，label参数表示循环的标志。

//iterate语句必须跟在循环标志前面。

iterate label

CREATE PROCEDUREdoiterate()BEGIN

DECLARE p1 INT DEFAULT 0;

my\_loop: LOOPSET p1= p1 + 1;IF p1 < 10 THENITERATE my\_loop;

ELSEIF p1> 20 THENLEAVE my\_loop;END IF;SELECT 'p1 is between 10 and 20';ENDLOOP my\_loop;END

6》repeat语句

创建一个带条件判断的循环过程，每次语句执行完毕，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。相当于do...while语句。

\[repeat\_label:\]repeat

statement\_list

until expr\_conditionend repeat \[repeat\_label\]

7》while语句

\[while\_label:\]whileexpr\_condition do

statement\_listend while \[while\_label\]

7、调用存储过程、存储函数

存储过程的调用必须使用call语句，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称。

存储函数的调用与MySQL中定义的函数的调用方式相同。

1》调用存储过程

call sp\_name(\[parameter\[,...\]\])

CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`p\_get\_name\`(in i\_id int,out o\_name varchar(25))

begin

select name into o\_name from test t where t.id=i\_id;

end

\-----------------------------------

mysql> call p\_get\_name(1,@name);

Query OK,1 row affected (0.00sec)

mysql> select @name;+-------+

| @name |

\+-------+

| Lucy |

\+-------+

1 row in set (0.00sec)

mysql>

2》调用存储函数：与MySQL内部定义的函数一样的使用方法。

CREATE DEFINER=\`root\`@\`localhost\` FUNCTION \`f\_get\_avg\`() RETURNS char(50) CHARSET utf8begin

return (select avg(salary) fromtest);end

\--------------------------------------

mysql> selectf\_get\_avg();+-------------+

| f\_get\_avg() |

\+-------------+

| 1180 |

\+-------------+

1 row in set (0.00sec)

mysql>

8、查看存储过程和函数，方法有3种：

1》show status

//\[like 'pattern'\]指匹配存储过程或函数的名称

show ｛procedure | function｝ status \[like 'pattern'\]

mysql> show procedure status like 'p\_get\_name%'\\G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*Db: mybatis

Name: p\_get\_name

Type:PROCEDUREDefiner: root@localhostModified:2019-09-05 00:28:04Created:2019-09-05 00:28:04Security\_type: DEFINER

Comment:

character\_set\_client: utf8

collation\_connection: utf8\_general\_ciDatabaseCollation: utf8\_general\_ci1 row in set (0.00sec)

mysql>

2》show create

show create ｛procedure | function｝ sp\_name

mysql> show create functionf\_get\_avg \\G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Function: f\_get\_avg

sql\_mode: STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITU

TIONCreate Function: CREATE DEFINER=\`root\`@\`localhost\` FUNCTION\`f\_get\_avg\`() R

ETURNSchar(50) CHARSET utf8begin

return (select avg(salary) fromtest);endcharacter\_set\_client: utf8

collation\_connection: utf8\_general\_ciDatabaseCollation: utf8\_general\_ci1 row in set (0.00sec)

mysql>

3》从系统的information\_schema数据库查询

MySQL中存储过程和存储函数的信息存储在information\_schema数据库下的Routines表中。

select \* frominformation\_schema.Routineswhere routine\_name='sp\_name' and routine\_type='procedure | function';

mysql> select \* from information\_schema.routines where routine\_name='p\_get\_name'

and routine\_type='procedure'\\G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*SPECIFIC\_NAME: p\_get\_name

ROUTINE\_CATALOG: def

ROUTINE\_SCHEMA: mybatis

ROUTINE\_NAME: p\_get\_name

ROUTINE\_TYPE:PROCEDUREDATA\_TYPE:

CHARACTER\_MAXIMUM\_LENGTH:NULLCHARACTER\_OCTET\_LENGTH:NULLNUMERIC\_PRECISION:NULLNUMERIC\_SCALE:NULLDATETIME\_PRECISION:NULLCHARACTER\_SET\_NAME:NULLCOLLATION\_NAME:NULLDTD\_IDENTIFIER:NULLROUTINE\_BODY: SQL

ROUTINE\_DEFINITION:begin

select name into o\_name from test t where t.id=i\_id;endEXTERNAL\_NAME:NULLEXTERNAL\_LANGUAGE:NULLPARAMETER\_STYLE: SQL

IS\_DETERMINISTIC: NO

SQL\_DATA\_ACCESS:CONTAINSSQL

SQL\_PATH:NULLSECURITY\_TYPE: DEFINER

CREATED:2019-09-05 00:28:04LAST\_ALTERED:2019-09-05 00:28:04SQL\_MODE: STRICT\_TRANS\_TABLES,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBS

TITUTION

ROUTINE\_COMMENT:

DEFINER: root@localhostCHARACTER\_SET\_CLIENT: utf8

COLLATION\_CONNECTION: utf8\_general\_ci

DATABASE\_COLLATION: utf8\_general\_ci1 row in set (0.00sec)

mysql>