文档章节

mysql存储过程整理

世界和平维护者
 世界和平维护者
发布于 2016/10/16 11:56
字数 4760
阅读 37
收藏 0

1、存储过程的简介:

    我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

    一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

2、存储过程的有点:

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

3、关于mysql的存储过程:

    存储过程是数据库存储的一个重要的功能,但是MySQL在5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

    同时,要在mysql5.1以上版本创建子程序,必须具有CREATE ROUTINE权限,并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者;

4、存储过程的创建:

    (1)语法:

        CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body

         proc_parameter指定存储过程的参数列表,列表形式如下:[IN|OUT|INOUT] param_name type

        其中in表示输入参数,out表示输出参数,inout表示既可以输入也可以输出;param_name表示参数名称;type表示参数的类型该类型可以是MYSQL数据库中的任意类型有以下取值:

        characteristic: 
            LANGUAGE SQL 
              | [NOT] DETERMINISTIC 
              | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
              | SQL SECURITY { DEFINER | INVOKER } 
              | COMMENT 'string' 
            routine_body: 
           Valid SQL procedure statement or statements

        LANGUAGE SQL :说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值

        [NOT] DETERMINISTIC :指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。[NOT] DETERMINISTIC 表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为[NOT] DETERMINISTIC 

        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

        SQL SECURITY { DEFINER | INVOKER } :指明谁有权限来执行。DEFINER 表示只有定义者才能执行

INVOKER 表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER 

        COMMENT 'string' :注释信息,可以用来描述存储过程或函数

        routine_body是SQL代码的内容,可以用BEGIN...END来表示SQL代码的开始和结束

   (2) 格式

        MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体

    EG:

    DELIMITER //
    DROP PROCEDURE IF EXISTS simpleproc;
    CREATE PROCEDURE simpleproc (OUT param1 INT)
    BEGIN
    SELECT COUNT(*) INTO param1 FROM t_user;
    END //
    DELIMITER ;

我这是在使用Navicat for MySQL 上敲的,就不打算进入mysql客户端去敲了,个人觉得这个更方便快捷一点;如果熟悉linux的童鞋估计就不这么认为啦;

好了,废话不多说,先解释一下:(1)DELIMITER这个东西的作用就是告诉mysql,我的存储过程的结束符号是//,所以在你的存储过程中使用;不会被认为是结束符号;注:当使用delimiter命令时,你应该避免使用反斜杠(‘\’)字符,因为那是MySQL的 转义字符(2)drop procedure if exists的意思跟我们建表时的语句是一样的,如果存在这个存储过程则删除;(3)create procedure simpleproc (out param1 int) 的意思也很明确,就是创建这个过程,而且带有一个输出参数,注:在function中可以有return 语句,但是procedure是没有的,但是也可以做到将结果传回去,如上;(4) begin .... end 这中间包裹着的就是procedure的主程序,也就是主体部分;简而言之,把你要放在一起工作的sql都丢在这里就好;(5)DELIMITER;这个语句作为结束语句。目的是告诉mysql我现在不要使用//作为结束字符了,要换回;了;注:这个不止可是//,还可以是其他符号;

5、变量

        DECLARE语句被用来把不同项目局域到一个 子程序:局部变量,条件和 处理程序 及光标;DECLARE仅被用在BEGIN ... END复合语句里,并且必须在复合语句的开头,在任何其它语句之前。光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。

        declare 声明局部变量:DECLARE var_name[,...] type [DEFAULT value];这个语句被用来声明局部变量。要给变量提供一个默认值,请包含一个DEFAULT子句。值可以被指定为一个表达式,不需要为一个常数。如果没有DEFAULT子句,初始值为NULL。

        set 变量:SET var_name = expr [, var_name = expr] ...; 在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量,或者是全局服务器变量。在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型(局域 声明变量及全局和集体变量)可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下,此选项被识别,但是被忽略了。

6、定义处理程序

    有时候程序是会出错的,但是你希望你的程序在出错的情况下继续执行,declare就可以帮助我们解决这样的问题;具体定义如下:特定条件需要特定处理。这些条件可以联系到错误,以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时候应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行

    1、定义条件

           DECLARE condition_name CONDITION FOR[condition_type]

             [condition_type]:

            SQLSTATE[VALUE] sqlstate_value |mysql_error_code

            condition_name:表示条件名称

            condition_type:表示条件的类型

            sqlstate_value和mysql_error_code都可以表示mysql错误

            sqlstate_value为长度5的字符串错误代码

            mysql_error_code为数值类型错误代码,例如:ERROR1142(42000)中,sqlstate_value的值是42000,

            mysql_error_code的值是1142

//方法一:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'

//方法二:使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148

 

这个语句指定需要特殊处理条件。他将一个名字和指定的错误条件关联起来。

这个名字随后被用在定义处理程序的DECLARE HANDLER语句中

 

    2、定义处理程序

            DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

                handler_type:
                |CONTINUE:对一个CONTINUE处理程序,当前子程序的执行在执行 处理程序语句之后继续。
                | EXIT :对于EXIT处理程序,当 前BEGIN...END复合语句的执行被终止。
                | UNDO :UNDO 处理程序类型语句还不被支持。 ·
            condition_value:
                SQLSTATE [VALUE] sqlstate_value
                | condition_name
                | SQLWARNING:SQLWARNING是对所有以01开头的SQLSTATE代码的速记
                | NOT FOUND:NOT FOUND是对所有以02开头的SQLSTATE代码的速记
                | SQLEXCEPTION:SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记
                | mysql_error_code

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件,指定的语句被执行。
 

//方法一:捕获sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET @info='CAN NOT FIND'; 


//方法二:捕获mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1148SET @info='CAN NOT FIND';

 
//方法三:先定义条件,然后调用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
set @info='CAN NOT FIND'; 


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

//方法五:使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';
 
//方法六:使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

eg:

delimiter //
DROP PROCEDURE IF EXISTS sp2;
CREATE PROCEDURE sp2()
BEGIN
		DECLARE CONTINUE HANDLER for SQLSTATE '23000' set @info = 23000;
		SET @info = 1;
		INSERT INTO test.t_user VALUES (1,'test','test','test');
		SET @info = 2;
		INSERT INTO test.t_user VALUES (1,'test','test','test');		
		SET @info = 3;
END //
delimiter ;

-- 执行以下sp2
call sp2();

-- 查询以下@info 
SELECT @info;

--结果分析:如果程序没有出错,按照执行目的,结果@info = 3; 如果被捕获了 '23000' 则结果是@23000; 

使用continue作为执行handler_type的话,捕获异常就继续执行;所以结果为3;
使用exit作为执行handler_type的话,捕获异常后直接结束,所以结果为23000;

7、光标

        简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。光标当前是不敏感的,只读的及不滚动的。 不敏感意为服务器可以活不可以复制它的结果表。 光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。
    

    1、声明光标

        DECLARE cursor_name CURSOR FOR select_statement

        这个语句声明一个光标。也可以在子程序中定义多个光标,但是一个块中的每一个光标必须有唯一的名字。 SELECT语句不能有INTO子句。

    2、打开光标

        OPEN cursor_name 这个语句打开先前声明的光标。

    3、使用光标  FETCH

        FETCH cursor_name INTO var_name [, var_name] ... 这个语句用指定的打开光标读取下一行(如果有下一行的话),并且前进光标指针

    4、关闭光标

        CLOSE cursor_name 这个语句关闭先前打开的光标。 如果未被明确地关闭,光标在它被声明的复合语句的末尾被关闭。

    2、流程控制构造

1.IF语句

IF语句用来进行条件判断。根据是否满足条件,将执行不同的语句。其语法的基本形式如下:

IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF

其中,search_condition参数表示条件判断语句;statement_list参数表示不同条件的执行语句。

注意:MYSQL还有一个IF()函数,他不同于这里描述的IF语句

下面是一个IF语句的示例。代码如下:

delimiter //
DROP PROCEDURE IF EXISTS sp3;
CREATE PROCEDURE sp3()
BEGIN
	DECLARE age int DEFAULT 19;
	set @age1 = 0;
	SET @age2 = 0;
	set @age3 = 0;
	IF age>20 
		THEN SET @age1=age;  
	ELSEIF age=20 
		THEN SET @age2=age;  
	ELSE 
		SET @age3=-1;  
	END IF; 
END //
delimiter ;
call sp3(); 

该示例根据age与20的大小关系来执行不同的SET语句。

如果age值大于20,那么将count1的值加1;如果age值等于20,那么将count2的值加1;

其他情况将count3的值加1。IF语句都需要使用END IF来结束。

2.CASE语句

CASE语句也用来进行条件判断,其可以实现比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_value参数表示变量的取值;

statement_list参数表示不同when_value值的执行语句。

CASE语句还有另一种形式。该形式的语法如下:

CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE 

其中,search_condition参数表示条件判断语句;

statement_list参数表示不同条件的执行语句。

下面是一个CASE语句的示例。代码如下:

delimiter //
DROP PROCEDURE IF EXISTS sp4;
CREATE PROCEDURE sp4()
BEGIN
	DECLARE age int DEFAULT 19;
	set @age1 = 0;
	CASE age
		WHEN age > 20 THEN SET @age1 = age;
		WHEN age = 20 THEN SET @age1 = age-1;
		ELSE SET @age1 = -1;
	END CASE;
END //
delimiter ;
call sp4();
select @age1;

注意:这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句

并且用END CASE替代END来终止!!

 

3.LOOP语句

LOOP语句可以使某些特定的语句重复执行,实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句,必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下:

[begin_label:] LOOP 
statement_list 
END LOOP [end_label]

其中,begin_label参数和end_label参数分别表示循环开始和结束的标志,这两个标志必须相同,而且都可以省略;(例子与leave结合一起写)

4.LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下:

LEAVE label

其中,label参数表示循环的标志。

 

下面是一个LEAVE语句的示例。代码如下:

delimiter //
DROP PROCEDURE IF EXISTS sp5;
CREATE PROCEDURE sp5()
BEGIN
	set @age = 0;
	add_age:LOOP
	  SET @age = @age + 1;
	if @age>1000 
      THEN LEAVE add_age;
	end IF;
	END LOOP add_age;
END //
delimiter ;
-- 调用
call sp5();

-- 查看结果
select @age;

该示例循环执行count加1的操作。当count的值等于100时,则LEAVE语句跳出循环。

 

5.ITERATE语句

ITERATE语句也是用来跳出循环的语句。但是,ITERATE语句是跳出本次循环,然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下:

ITERATE label

其中,label参数表示循环的标志。

下面是一个ITERATE语句的示例。代码如下:

delimiter //
DROP PROCEDURE IF EXISTS sp6;
CREATE PROCEDURE sp6()
BEGIN
	DECLARE age int DEFAULT 0;
	DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET age = 23000;
	add_fun:LOOP
		SET age = age + 1;
		IF age > 10
			THEN INSERT into t_user value(1, 'lennon', 'lennon', 'lennon');
		ELSEIF age > 5 && age < 10
			THEN ITERATE add_fun;
		ELSE 
			select age;
		END IF;
	END LOOP add_fun;
END //
delimiter ;
call sp6();

说明:LEAVE语句和ITERATE语句都用来跳出循环语句,但两者的功能是不一样的。

LEAVE语句是跳出整个循环,然后执行循环后面的程序。而ITERATE语句是跳出本次循环,然后进入下一次循环。

使用这两个语句时一定要区分清楚。

 

6.REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时,就会跳出循环语句。REPEAT语句的基本语法形式如下:

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label]

其中,statement_list参数表示循环的执行语句;search_condition参数表示结束循环的条件,满足该条件时循环结束。

下面是一个REPEAT语句的示例。代码如下:

delimiter //
DROP PROCEDURE IF EXISTS sp7;
CREATE PROCEDURE sp7()
BEGIN
	set @age = 0;
	add_count:REPEAT
		set @age = @age + 1 ;
	UNTIL @age>100 END REPEAT add_count;
END //
delimiter ;

call sp7();

select @age;

该示例循环执行count加1的操作,count值为100时结束循环。

REPEAT循环都用END REPEAT结束。

 

7.WHILE语句

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时,执行循环内的语句。

WHILE语句的基本语法形式如下:

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

其中,search_condition参数表示循环执行的条件,满足该条件时循环执行;

statement_list参数表示循环的执行语句。

下面是一个ITERATE语句的示例。代码如下:

delimiter //
DROP PROCEDURE IF EXISTS sp8;
CREATE PROCEDURE sp8()
BEGIN
	set @age = 0;
	
	WHILE @age < 100 DO
		set @age = @age + 1 ;
	END WHILE;

END //
delimiter ;

call sp8();

select @age;

该示例循环执行count加1的操作,count值小于100时执行循环。

如果count值等于100了,则跳出循环。WHILE循环需要使用END WHILE来结束。

8、查看存储过程

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE  ' pattern ' ] ; 
SHOW CREATE { PROCEDURE | FUNCTION } sp_name ; 
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;

eg:

show PROCEDURE STATUS like 'sp6';

show CREATE PROCEDURE sp6;

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp6' ;

总结:

    终于是整理完一部分了,比较粗糙,但是我相信看完之后会有不小的收获;有一部分是摘抄网络的,有些是摘抄官方手册的,主要目的在于整理自己工作之余的笔记;鄙人菜鸟一枚,如果写的不好,请指出就好,别咬我;这些都是比较基础的整理,但也需要做一个系统的整理学习才能将sql掌握的更好,才能对mysql掌握的更好吧;

   流程大致为: 创建->定义变量(光标)->处理程序(有点类似try--catch)->流程控制;


 

 

 

© 著作权归作者所有

共有 人打赏支持
世界和平维护者
粉丝 7
博文 51
码字总数 76601
作品 0
深圳
程序员
分享一些MySQL的整理的一些资料

MySQL 资料整理目录 --------------------------- 一:MySQL 基本语句 (MySQL 基本语句.doc) 二:MySQL 存储过程(MySQL 存储过程.doc) 三:MySQL 监控(MySQL 监控.doc) 四:MySQL 技巧...

守望之心
2013/10/12
575
13
精简版MySQL分支--Drizzle

Drizzle,一个精简版的MySQL分支,在目前的MySQL代码基本之上,将存储过程、视图、触发器、查询缓存、PREPARE语句等等没什么必要 的功能从代码中删掉,简化对数据类型和存储引擎的支持,并且...

匿名
2008/10/07
16.6K
0
Drizzle 7.2.4-alpha 发布,精简的 MySQL 分支

Drizzle 7.2.4-alpha 发布,该版本支持 clang,增加一个 Sysbench Qewpie 测试套件,修复了很多 bug,支持动态插件和文档的完善。 Drizzle,一个精简版的MySQL分支,在目前的 MySQL代码基本之...

oschina
2012/09/24
1K
5
Drizzle 7.1.36 发布,精简的 MySQL 分支

Drizzle 7.1.36 发布,该版本修复了一些 bug,以及 Ubuntu 下的一些问题,修复了 Gearman 模块 (libgearman) 新老版本的一些问题。 Drizzle,一个精简版的MySQL分支,在目前的MySQL代码基本之...

oschina
2012/05/24
373
1
mysql存储过程入门与提高(第二篇)

以前面试的时候被问道为啥用mysql存储过程,或者存储过程的优点。作为一个专业的mysql dba。肯定是必须要掌握的。而我只是运维啊 哈哈哈。当时回答的不好,后来整理了 大概这几条,存储过程执...

落叶刀
2015/09/23
179
0

没有更多内容

加载失败,请刷新页面

加载更多

day96-20180923-英语流利阅读-待学习

英国王子也不看好人工智能,理由却和霍金不同 Daniel 2018-09-23 1.今日导读 2016 年 3 月 9 日至 15 日,世界围棋冠军李世石与谷歌研发的计算机围棋程序 AlphaGo 进行人机大战并以 1 比 4 ...

飞鱼说编程
52分钟前
3
0
今天在码云遇到一个很有意思的人 for Per.js

今天在码云遇到一个很有意思的人,他在我的Per.js项目下面评论了一句,大意为“你试试这句代码,看看速度到底是你快还是Vue快”【当然,这个评论被我手残不小心删掉了...】。 然后我就试了,...

Skyogo
57分钟前
25
0
Java -------- 首字母相关排序总结

Java 字符串数组首字母排序 字符串数组按首字母排序:(区分大小写) String[] strings = new String[]{"ba","aa","CC","Ba","DD","ee","dd"}; Arrays.sort(strings); for (int i ...

切切歆语
59分钟前
2
0
还在用 Git 的 -f 参数强推仓库,你这是在作死!

最近,美国一个程序员因为同事不写注释,代码不规范,最严重的是天天使用 git push -f 参数强行覆盖仓库,该程序员忍无可忍向四名同事开抢,其中一人情况危急!!! 不写注释、代码不规范是一...

红薯
今天
497
0
NPM报错终极大法

所有的错误基本上都跟node的版本相关 直接删除系统中的node 重新安装 sudo rm -rf /usr/local/{bin/{node,npm},lib/node_modules/npm,lib/node,share/man/*/node.*} 重新安装 $ n lts$ npm...

lilugirl
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部