Mysql之存储过程和函数

ameimeng

　　Mysql之存储过程和函数
　　存储过程就是一条或多条SQL语句的集合，可视为批文件，但是其作用不仅用于批处理。
　　存储程序分为：1、存储过程2、函数
　　使用Call语句来调用存储过程，只能用输出变量返回值。
　　一、创建存储过程
　　语法：
create procedure sp_name(proc_parameter)　　
[characteristics……] routine_body
　　
创建存储函数名为sp_name，存储过程的名为：proc_parameter
　　指定存储参数列表为：
　　[IN | OUT | INOUT] param_name type
　　其中IN表示输入参数，OUT表示输出参数，INOUT表示即可输入也可输出
　　param_name表示参数名称
　　type 表示参数类型，该类型可以是Mysql数据库中的任意类型。
　　characteristics 指定存储过程的特性，有以下取值：
　　LANGUAGE SQL：说明routine_body部分是由SQL语句组成，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
　　[NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。
　　1. DETERMINISTIC表示结果是正确的。每次执行存储过程时，相同输入会得到相同的输出。
　　2. NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
　　{ CONTAINS SQL | NO SQL |REDAS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。
　　1. CONTAINS SQL：表示子程序包含SQL语句，但是不包含读写数据的语句。
　　2. NO SQL：表示子程序不包含SQL语句。
　　3. REDAS SQL DATA ：说明子程序包含数据的语句。
　　4. MODIFIES SQL DATA：表明子程序包含写数据的语句。默认为CONTAINS SQL。
　　SQL SECURITY { DEFINER | INVOKER}:指明谁有权限来执行。
　　1. DEFINER表示只有定义者才能执行。
　　2. INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER
　　COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。
　　routine_body是SQL代码的内容。通常用begin……end表示SQL代码的开始和结束。
　　编写存储过程并不是简单的事情，可能存储过程中需要复杂的SQL语句，并且要创建存储过程的权限；但是使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误、提高效率，因此存储过程非常的有用，而且应该尽量学会使用。
　　例1：
mysql> delimiter //      # 定义SQL语句的结束符号为//，使用这条命令时，应该避免（‘\’）字符，因为反斜线是Mysql的转意符。　　
mysql> create procedure p1()
　　
  -> begin
　　
  -> select  * from t;
　　
  -> end //
　　
mysql> delimiter ;
　　
mysql> show procedure status \G        # 查看存储过程信息
　　
mysql> call p1    # 读取这个存储过程
　　例2：
mysql> delimiter //　　
mysql> create procedure p2(n int)
　　
  -> begin
　　
  -> select * from t where id = n;
　　
  -> end //
　　
mysql> delimiter ;
　　
mysql> show procedure status \G
　　
mysql> call p2(1)         # 需要带入取值
　　例3：
mysql> create database db_proc;　　
mysql> use db_proc
　　
mysql> CREATE TABLE `proc_test` (
　　
  -> `id` tinyint(4) NOT NULL AUTO_INCREMENT,
　　
  -> `username` varchar(20) NOT NULL,
　　
  -> `password` varchar(20) NOT NULL,
　　
  -> PRIMARY KEY (`id`)
　　
  -> ) ENGINE=MyISAM AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;
　　

　　
mysql> delimiter //
　　
mysql> create procedure mytest(in name varchar(20),in pwd varchar(20))
　　
  -> begin
　　
  -> insert into proc_test(username,password) values(name,pwd);
　　
  -> end //
　　
mysql> delimiter ;
　　
mysql> call mytest('lxq','password') ;
　　
mysql> select * from proc_test;      # 验证插入了数据
　　************************
　　mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT
　　Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])
　　IN 输入参数
　　表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
　　OUT 输出参数
　　该值可在存储过程内部被改变，并可返回
　　INOUT 输入输出参数
　　调用时指定，并且可被改变和返回
　　IN参数例子：
mysql> DELIMITER //　　
mysql> CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
　　
    -> BEGIN
　　
    -> SELECT p_in;     # 查询输入参数
　　
    -> SET p_in=2;      # 修改
　　
    -> select p_in;     #查看修改后的值
　　
    -> END //
　　
mysql> DELIMITER ;
　　执行结果:
mysql> set @p_in=1;　　
mysql> call sp_demo_in_parameter(@p_in);
　　
mysql> select @p_in;
　　
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值
　　OUT参数例子
mysql> DELIMITER //　　
mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
　　
    -> BEGIN
　　
    -> SELECT p_out;    # 查看输出参数
　　
    -> SET p_out=2;     # 修改参数值
　　
    -> SELECT p_out;    # 看看有否变化
　　
    -> END //
　　
mysql> DELIMITER ;
　　执行结果:
mysql> SET @p_out=1;　　
mysql> CALL sp_demo_out_parameter(@p_out);
　　
mysql> SELECT @p_out;
　　
p_out在存储过程中被修改,直接影响@p_out的值
　　INOUT参数例子：
mysql> DELIMITER //　　
mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
　　
    -> BEGIN
　　
    -> SELECT p_inout;
　　
    -> SET p_inout=2;
　　
    -> SELECT p_inout;
　　
    -> END;
　　
mysql> DELIMITER ;
　　执行结果：
set @p_inout=1;　　
call sp_demo_inout_parameter(@p_inout);
　　
select @p_inout;
　　****************************
　　二、特定异常
　　在MySQL中，特定异常需要特定处理。这些异常可以联系到错误，以及子程序中的一般流程控制。定义异常是事先定义程序执行过程中遇到的问题，异常处理定义了在遇到问题时对应当采取的处理方式，并且保证存储过程或者函数在遇到错误时或者警告时能够继续执行。
　　1 异常定义
　　1.1 语法
DECLARE condition_name CONDITION FOR [condition_type];　　1.2 说明
condition_name 参数表示异常的名称；　　
condition_type 参数表示条件的类型，condition_type由SQLSTATE [VALUE] sqlstate_value|mysql_error_code组成：
　　
    sqlstate_value和mysql_error_code都可以表示MySQL的错误；
　　
    sqlstate_value为长度为5的字符串类型的错误代码；
　　
    mysql_error_code为数值类型错误代码；
　　1.3 示例
　　定义“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 自定义异常处理
　　2.1 异常处理语法
DECLARE handler_type HANDLER FOR condition_value [,...] sp_statement　　2.2 参数说明
handler_type: CONTINUE|EXIT|UNDO　　
    handler_type为错误处理方式，参数为3个值之一；
　　
    CONTINUE表示遇到错误不处理，继续执行；
　　
    EXIT表示遇到错误时马上退出；
　　
    UNDO表示遇到错误后撤回之前的操作，MySQL暂不支持回滚操作；
　　

　　
condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
　　
    condition_value表示错误类型；
　　
    SQLSTATE [VALUE] sqlstate_value为包含5个字符的字符串错误值；
　　
    condition_name表示DECLARE CONDITION定义的错误条件名称；
　　
    SQLWARNING匹配所有以01开头的SQLSTATE错误代码；
　　
    NOT FOUND匹配所有以02开头的SQLSTATE错误代码；
　　
    SQLEXCEPTION匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
　　
    mysql_error_code匹配数值类型错误代码；
　　2.3 异常捕获方法
方法一：捕获sqlstate_value异常　　
这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为"42S02"，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息
　　
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
　　

　　
方法二：捕获mysql_error_code异常
　　
这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出"NO_SUCH_TABLE"信息；
　　
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> create table test.t(s1 int,primary key(s1));
　　
mysql> delimiter //
　　
mysql> create procedure handlerdermo()
　　
    -> begin
　　
    -> declare CONTINUE HANDLER FOR SQLSTATE '23000' set @x2 = 1;
　　
    -> set @x = 1;
　　
    -> insert into test.t values (1);
　　
    -> set @x = 2;
　　
    -> insert into test.t values (1);
　　
    -> set @x = 3;
　　
    -> end //
　　
mysql> delimiter ;
　　
mysql> call handlerdermo();
　　
mysql> select @x;
　　
mysql> select * from test.t;
　　三、函数
　　函数的作用：提高代码的复用率
　　函数可以调用函数中的方法来实现某些功能
　　利用now()来实现空参数函数：
mysql> select now();　　
+---------------------+
　　
| now()          |
　　
+---------------------+
　　
| 2018-08-16 18:19:09 |
　　
+---------------------+
　　

　　
mysql> select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
　　
+------------------------------------------------------+
　　
| date_format(now(),'%Y年%m月%d号 %H点%i分%s秒')       |
　　
+------------------------------------------------------+
　　
| 2018年08月16号 18点19分57秒                          |
　　
+------------------------------------------------------+
　　------------------------------------------------------------------------------------
mysql> create function my_time() returns varchar(50)　　
   -> return   date_format(now(),'%Y-%m-%d %H-%i-%s');
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> select my_time();
　　
+---------------------+
　　
| my_time()           |
　　
+---------------------+
　　
| 2018-08-16 18-22-10 |
　　
+---------------------+
　　函数分为空参数函数和传参函数
　　注意：函数必需要有返回值类型用returns描述
　　returns后面跟的是函数体
　　如果函数体只有单条就直接描述
　　函数体如果有多条 在returns后面 begin开始  函数体结束后要写end结束
　　end之前一定要确定返回值
　　-----------------------------------------------------------------------------------------------
　　创建传参函数：
mysql> CREATE FUNCTION cont_AVG(num1 int,num2 int) RETURNS decimal(8,2)　　
    -> RETURN (num1+num2)/2;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
mysql> select cont_AVG(2,2);
　　
+---------------+
　　
| cont_AVG(2,2) |
　　
+---------------+
　　
|    2.00     |
　　
+---------------+
　　
1 row in set (0.00 sec)
　　

　　
mysql> select cont_AVG(3,2);
　　
+---------------+
　　
| cont_AVG(3,2) |
　　
+---------------+
　　
|     2.50     |
　　
+---------------+
　　创建给stu表添加用户的多函数体传参函数：
mysql> desc stu;　　
+-------+-------------+------+-----+---------+-------+
　　
| Field | Type      | Null | Key | Default | Extra |
　　
+-------+-------------+------+-----+---------+-------+
　　
| id   | int(11)    | NO   |    | NULL   |     |
　　
| name  | varchar(10) | NO   |    | NULL   |     |
　　
+-------+-------------+------+-----+---------+-------+
　　
mysql> select * from stu;
　　
Empty set (0.00 sec)
mysql> delimiter //　　
mysql> create function adduse(u_id int unsigned,u_name varchar(10))
　　
    -> returns int unsigned
　　
    -> begin
　　
    -> insert stu values(u_id,u_name);
　　
    -> return last_insert_id();
　　
    -> end //
　　
mysql> delimiter ;
　　
mysql> select adduse(1,'zs');
　　
+----------------+
　　
| adduse(1,'zs') |
　　
+----------------+
　　
|      0      |
　　
+----------------+
　　
1 row in set (0.02 sec)
　　

　　
mysql> select adduse(2,'ls');
　　
+----------------+
　　
| adduse(2,'ls') |
　　
+----------------+
　　
|      0      |
　　
+----------------+
　　
1 row in set (0.01 sec)
　　

　　
mysql> select adduse(3,'ww');
　　
+----------------+
　　
| adduse(3,'ww') |
　　
+----------------+
　　
|      0      |
　　
+----------------+
　　
1 row in set (0.02 sec)
　　

　　
mysql> select * from stu;
　　
+----+------+
　　
| id | name |
　　
+----+------+
　　
| 1  | zs   |
　　
| 2  | ls   |
　　
| 3  | ww   |
　　
+----+------+
　　
3 rows in set (0.00 sec)