MySQL存储过程的特性_mysql 存储过程

什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

ps:存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

有哪些特性

有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

函数的普遍特性：模块化，封装，代码复用；

速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

创建存储过程

语法

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代码的开始和结束。

下面的语句创建一个查询t1表全部数据的存储过程

DROP PROCEDURE IF EXISTS Proc;

DELIMITER //

CREATE PROCEDURE Proc()

BEGIN

SELECT * FROMt3;

END//DELIMITER ;

CALL Proc();

t3表是我们上一节创建的表

这里的逻辑是

1、先判断是否有Proc() 这个存储过程，有就drop掉

2、创建Proc() 存储过程

3、执行Proc() 存储过程

注意：“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，为了避免与存储过程

中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END//”结束存储过程。

存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符！！！！！！！！！！！

创建一个简单的存储过程

存储过程proc_adder功能很简单，两个整型输入参数a和b，一个整型输出参数sum，功能就是计算输入参数a和b的结果，赋值给输出参数sum；

几点说明：

DELIMITER ;;：之前说过了，把默认的输入的结束符;替换成;;。

DEFINER：创建者；

--------------------------------Procedure structure for `proc_adder`------------------------------

DROP PROCEDURE IF EXISTS`proc_adder`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)

BEGIN#Routine body goes here...

DECLARE c int;

if a is null then set a = 0;

end if;

if b is null then set b = 0;

end if;

set sum = a +b;

END;;

DELIMITER ;

执行以上存储结果，验证是否正确，如下图，结果OK：

set @b=5;

call proc_adder(2,@b,@s);

select @s as sum;

存储过程中的控制语句

IF语句：

--------------------------------Procedure structure for `proc_if`------------------------------

DROP PROCEDURE IF EXISTS`proc_if`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_if`(IN type int)

BEGIN#Routine body goes here...

DECLARE c varchar(500);

IF type = 0 THEN

set c = 'param is 0';

ELSEIF type = 1 THEN

set c = 'param is 1';

ELSE

set c = 'param is others, not 0 or 1';

END IF;

selectc;

END;;

DELIMITER ;

CASE语句：

--------------------------------Procedure structure for `proc_case`------------------------------

DROP PROCEDURE IF EXISTS`proc_case`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_case`(IN type int)

BEGIN#Routine body goes here...

DECLARE c varchar(500);

CASEtype

WHEN 0 THEN

set c = 'param is 0';

WHEN 1 THEN

set c = 'param is 1';

ELSE

set c = 'param is others, not 0 or 1';

END CASE;

selectc;

END;;

DELIMITER ;

循环while语句：

--------------------------------Procedure structure for `proc_while`------------------------------

DROP PROCEDURE IF EXISTS`proc_while`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_while`(IN n int)

BEGIN#Routine body goes here...

DECLARE i int;

DECLARE s int;

SET i = 0;

SET s = 0;

WHILE i <=n DO

set s = s +i;

set i = i + 1;

END WHILE;

SELECTs;

END;;

DELIMITER ;

其它：略~

DROP PROCEDURE IF EXISTS`useCursor`;

DELIMITER ;;CREATE PROCEDURE`useCursor`()BEGIN

/*局部变量的定义 declare*/

DECLARE userid VARCHAR(50);DECLARE rolename VARCHAR(50);DECLARE STOP INT DEFAULT 0;DECLARE cur CURSOR FOR (SELECT a.userid AS userid,a.rolename AS rolename FROMrole a);DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP = NULL;/*开游标*/

OPENcur;/*游标向下走一步，将查询出来的两个值付给定义的两个变量*/

FETCH cur INTOuserid,rolename;/*循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开*/

WHILE ( STOP IS NOT NULL) DOUPDATE USER SET user.`bumen`=rolename WHERE id =userid ;/*游标向下走一步*/

FETCH cur INTOuserid,rolename;END WHILE;CLOSEcur;END;;

DELIMITER ;

DROP PROCEDURE IF EXISTS`keyCursor`;

DELIMITER ;;CREATE PROCEDURE`keyCursor`()BEGIN

/*局部变量的定义 declare*/

DECLARE createid VARCHAR(50);DECLARE deno VARCHAR(50);DECLARE STOP INT DEFAULT 0;DECLARE cur CURSOR FOR (SELECT a.`createid` AScreateid,

SUBSTRING_INDEX(SUBSTRING_INDEX(b.`ELEMENT_ALLPARENT_ID`,',',3),',',-1) ASdenoFROM `jso_annual_key_work` a LEFT JOIN`structure` bON a.`createid` =b.`ELEMENT_ID`

);DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP = NULL;/*开游标*/

OPENcur;/*游标向下走一步，将查询出来的两个值付给定义的两个变量*/

FETCH cur INTOcreateid,deno;/*循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开*/

WHILE ( STOP IS NOT NULL) DOUPDATE jso_annual_key_work SET `deptno` = deno WHERE `createid` =createid ;/*游标向下走一步*/

FETCH cur INTOcreateid,deno;END WHILE;CLOSEcur;END;;

DELIMITER ;

存储过程弊端

不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；

不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；

修改存储过程和函数，只能修改他们的权限，目前MYSQL还不提供对已存在的存储过程和函数代码的修改

如果要修改，只能通过先DROP掉，然后重新建立新的存储过程和函数来实现