MySQL-快速入门(8)存储过程、存储函数

摘要:
deterministic表示结果是确定的。notdeterministic表示结果是不确定的,相同的输入可能得到不同的输出。definer表示只有定义者才能执行。5˃comment'string':注释信息,可以用来描述存储过程或函数。//最简单的存储过程createprocedurep_get_avg()begin...end;//"delimiter//"的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为分号";",//为了避免与存储过程中sql语句的结束符相冲突,需要使用delimiter改变存储过程的结束符,并//"end//"结束存储过程。

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表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。notdeterministic表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为notdeterministic。

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_value declare command_not_allowed condition for sqlstate '42000';
//方法二:使用mysql_error_code declare 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
[value]sqlstate_value |condition_name |mysql_error_code | notfound |sqlexception |sqlwarning

举例:

//方法一:捕获sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';

//方法二:捕获mysql_error_code
DECLARE 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 ';

//方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

//方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE ';

//方法六:使用SQLEXCEPTION
DECLARE 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:loop 
      fetch 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 then
statement_list
[elseif expr_condition then statement_list ...]... [else statement_list] end if

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

//第一种方式:
//匹配表达式的值
casecase_expr
  when  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: LOOP
  SET 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_condition
end repeat [repeat_label]

7》while语句

[while_label:]whileexpr_condition do
   statement_list
end 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 utf8
begin
  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_ci
  DatabaseCollation: utf8_general_ci
1 row in set (0.00sec)

mysql>

2》show create

show createprocedure | 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
TION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION`f_get_avg`() R
ETURNS char(50) CHARSET utf8
begin
  return (select avg(salary) fromtest);
endcharacter_set_client: utf8
collation_connection: utf8_general_ci
  DatabaseCollation: utf8_general_ci
1 row in set (0.00sec)

mysql>

3》从系统的information_schema数据库查询

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

select * frominformation_schema.Routines
where 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_ci
1 row in set (0.00sec)

mysql>

9、修改存储过程、函数(并不是修改其中的代码)

alter {procedure | function} sp_name [characteristic ...]
characteristic指定存储过程的特性,可能的取值有:
  contains sql:表示子程序包含sql语句,但不包含读或写数据的语句。
no sql:表示子程序中不包含sql语句。
reads sql data:表示子程序中包含读数据的语句。
modifies sql data:表示子程序中包含写数据的语句。
sql security {definer | invoker}:指明谁有权限来执行。
definer:表示只有定义者自己才能够执行。
invoker:表示调用者可以执行。
comment 'string':表示注释信息。

10、删除存储过程、函数

drop {procedure | function} [if exists] sp_name

免责声明:文章转载自《MySQL-快速入门(8)存储过程、存储函数》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇MYSQL中存储过程的创建,调用及语法Lua常用时间函数下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

18-MySQL DBA笔记-MySQL Server调优

第18章 MySQL Server调优 本章将为读者介绍针对MySQL Server的优化,这也是DBA最熟悉的领域之一。首先我们介绍MySQL的主要参数,然后,讲述常见硬件资源的优化。我们假设读者已经具备了足够的基础知识,所以,本章将更多的针对一些特定的主题进行叙述。18.1 概述 衡量数据库性能的指标,一般衡量数据库的性能有两个指标:响应时间和吞吐率。...

ADO.NET复习

目录 ADO.NET ADO.NET思维导图 命名空间 常用类介绍 Connection 类概述 Command 类概述 DataReader 类概述 DataAdapter 类概述 DataSet 类概述 DataTable DataRow类 DataColumn 类 DataSet:更新数据库(SqlCommandBuilder)...

oracle pl/sql 变量

一、变量介绍在编写pl/sql程序时,可以定义变量和常量;在pl/sql程序中包括有:1)、标量类型(scalar)2)、复合类型(composite) --用于操作单条记录3)、参照类型(reference) --用于操作多条记录4)、lob(large object)    二、标量(scalar)——常用类型1)、在编写pl/sql块时,如果要使用...

5个Android开发中比较常见的内存泄漏问题及解决办法

在Android开发中,内存泄漏是比较常见的问题,有过一些Android编程经历的童鞋应该都遇到过,但为什么会出现内存泄漏呢?内存泄漏又有什么影响呢? 在Android程序开发中,当一个对象已经不需要再使用了,本该被回收时,而另外一个正在使用的对象持有它的引用从而导致它不能被回收,这就导致本该被回收的对象不能被回收而停留在堆内存中,内存泄漏就产生了。 内存...

mysql快速保存插入大量数据一些方法总结

转载:http://www.111cn.net/database/mysql/53274.htm 说明: 这几天尝试了使用不同的存储引擎大量插入MySQL表数据,主要试验了MyISAM存储引擎和InnoDB。下面是实验过程: 实现:一、InnoDB存储引擎。创建数据库和表 代码如下 1 > CREATE DATABASE ecommerce...

MySQL---&amp;gt;数据库的简介和安装

1.什么是MySQL: MySQL是一个小型关系型数据库管理系统,开发者伟瑞典MySQL AB公司。 目前MySQL被广泛的应用在Internet上的中小型网站中。由于体积小,速度快,总体拥有成本低,尤其是开放源码这一特点能够降低往网站总体拥有成本而选择了MySQL作为数据库。 2.市面上的数据库: Oracle 应用广泛功能强大分布式数据库系统...