MySQL数据库入门（三）存储过程 游标

存储过程就是有业务逻辑和流程的集合（为以后的使用而保存的一条或多条MySQL语句的集合）， 可以在存储过程中创建表，更新数据， 删除等等。

为什么要使用存储过程

1、通过把处理封装在容易使用的单元中，简化复杂的操作(正如前面例子所述)。

2、由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

3、简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。

存储过程的创建

CREATE PROCEDURE  过程名([[IN|OUT|INOUT]参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]])

 [特性 ...]

过程体

create procedureporcedureName ()

begin

    select name from user;

end;

 

DELIMITER //

create procedureporcedureName (OUT s int)

    begin

      select count(*) into s from students;

end

//

DELIMITER ;

 

分隔符

MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER;”的意为把分隔符还原。

 

参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

IN参数：

仅需要将数据传入存储过程，并不需要返回计算后的该值。

OUT参数：

不接受外部传入的数据，仅返回计算之后的值。

INOUT参数：

需要数据传入存储过程经过调用计算后，再传出返回值。

过程体

过程体的开始与结束使用BEGIN与END进行标识。

 

 

 

DROP procedure IFEXISTS 2paramtest;

 

DELIMITER $$

USE zyttest$$

CREATEDEFINER=`root`@`localhost` PROCEDURE `2paramtest`(in paramin int)

BEGIN

         select paramin;

         set paramin = 12;

    select paramin;

END$$

 

DELIMITER ;

 

 

变量

存储过程变量

语法：DECLARE变量名1[,变量名2...] 数据类型 [默认值];

特征：不能在参数名称前加“@”,主要用在存储过程中,在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL

例如：declaretemp1 int;

Set temp1 = 10;

 

会话变量

语法：SET变量名 = 变量值 [,变量名= 变量值 ...]

特征：会话变量 , 则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量

例如：declaretemp1 = 2;

变量一般以@开头

例如：

SELECT 'Hello World' into @x;

         SELECT @x;

 

         SET @y='Goodbye Cruel World';

         SELECT @y;

         SET @z=1+2+3;

         SELECT @z;

 

查询存储过程

SELECT name FROMmysql.proc WHERE db='数据库名';

SELECT routine_nameFROM information_schema.routines WHERE routine_schema='数据库名';

SHOW PROCEDURE STATUSWHERE db='数据库名';

 

#查看存储过程详细信息

SHOW CREATE PROCEDURE数据库.存储过程名;

 

 

 

 

存储过程的修改

 

ALTER PROCEDURE  sp_name [characteristic ...]

characteristic:

{ CONTAINS SQL | NOSQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY {DEFINER | INVOKER }

| COMMENT 'string'

 

sp_name参数表示存储过程或函数的名称；

characteristic参数指定存储函数的特性。

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序中不包含SQL语句；

READS SQL DATA表示子程序中包含读数据的语句；

MODIFIES SQL DATA表示子程序中包含写数据的语句。

SQL SECURITY {DEFINER | INVOKER }指明谁有权限来执行，DEFINER表示只有定义者自己才能够执行；INVOKER表示调用者可以执行。

COMMENT 'string'是注释信息。

 

例如：

#将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行。

ALTER  PROCEDURE num_from_employee

  MODIFIES SQL DATA

  SQL SECURITY INVOKER ;

#将读写权限改为READS SQL DATA，并加上注释信息'FIND NAME'。

ALTER  PROCEDURE name_from_employee

  READS SQL DATA

  COMMENT 'FIND NAME' ;

 

存储过程的删除

DROP PROCEDURE [过程1[,过程2…]]

删除一个或多个存储过程。

 

存储过程的控制语句

1.变量作用域

内部变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，不再可见了，在存储

过程外再也找不到这个内部变量，但是可以通过out参数或者将其值指派给会话变量来保存其值

 

 

例如:

delimiter$$

DROPprocedure IF EXISTS testparam $$

createprocedure testparam()

begin

         declare var1 varchar(67);

    set var1 = "outer";

    begin

                  declare var1 varchar(67);

        set var1 = "inner";

        select var1;

    end;

    #select var1;

end$$

delimiter$$

条件语句

IF-THEN-ELSE语句

delimiter $$

drop procedure ifexists testcontrol $$

create proceduretestcontrol()

begin

         declare temp int;

    set temp = -1;

    if temp>0 then

                  select temp;

         else

                  select 5;

         end if;

end $$

delimiter $$

CASE-WHEN-THEN-ELSE语句

delimiter$$

dropprocedure if exists testcase $$

createprocedure testcase()

begin

         declare temp int;

    set temp = 1;

    case temp

                  when 0 then

                          select 0;

                  when 1 then

                          select 1;

                  when 2 then

                          select 2;

         end case;

end$$

delimiter$$

循环语句

WHILE-DO…END-WHILE

delimiter$$

dropprocedure if exists testwhile $$

createprocedure testwhile()

begin

         declare temp int;

    set temp = 3;

    while(temp>0) do

                  select "true";

        set temp = temp - 1;

         end while;

end$$

delimiter$$

REPEAT...END REPEAT

delimiter $$

drop procedure if exists testrepeat$$

create procedure testrepeat()

begin

         declaretemp int;

   set temp = 5;

   repeat

                  settemp = temp + 1;

        select temp;

         untiltemp > 7

   end repeat;

end $$

delimiter $$

 

游标，应用程序对查询语句select  返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作

提供对基于游标位置而对表中数据进行删除或更新的能力

 

#游标 对同一个结果集进行不同的操作delimiter $$drop procedure if exists procname ;create procedure procname()BEGIN#创建一个初始变量用来存放游标读取出来的值declare temp varchar(20) default '' ;#创建一个初始变量用来存放游标读取出来的值的集合declare endtemp varchar(200) default '' ;#创建一个初始变量用来做一个flagdeclare val int default 1 ;#创建一个游标declare cur cursor #给personinfo.grade结果集添加游标for select grade from personinfo ;#在 FETCH 语句中引用的游标位置处于结果表最后一行之后设置val的值为0 declare continue handler for sqlstate '02000' set val = 0 ;#开启游标open cur ;#将游标中的内容取出存储在temp中fetch cur into temp ;while val != 0 do if endtemp = '' then set endtemp = temp ;elseset endtemp = concat(endtemp,',',temp);end if ;fetch cur into temp ;end while ;select endtemp ;#关闭游标close cur ;END $$delimiter $$call procname();