存储过程

 

一：

例如：

  客户----服务器   进行信息交流（数据库）

1）         insert  into

2）         update

3）         select

  优点：

１、    批处理，减少客户和服务器之间的交流次数

２、    服务器端运行，移植性好；

３、    安全要求高的行业；

４、    包含流程控制结构的SQL语句的集合

 

二、创建存储过程

语法格式：

  ;---每条语句的结束

  声明新的结束符(任选，避免在MYSQL环境下有特殊含义):

    delimiter(分隔符)  //

    create  procedure(过程)  p_name(in  c_name  数据类型)

    [特征描述1、使用什么语言创建存储过程2、结果的确定性3、4、权限5、注释]

    SQL语句集合（一条，多条）

例如：

   create  procedure  p1()  select * from  t2;

或 create  procedure  p1()

    begin

      流程控制结构

      SQL语句集合

    end//

 

   练习1：创建存储过程，显示“Hello World”;

      步骤：1、检验mysql 版本  

                 select  version()

                 select  @@version

2、存储过程是属于数据库的

               show  databases;

               create  databases  db1;

                 use  db1;

            3、声明结束符

                  delimiter  //

4、创建存储过程

          create  procedure  p1()  select “hello,world”//

            5、调用存储过程

                  call  p1()//

            6、恢复结束符

                  delimiter  ;

 

练习2：

       创建表t1(s1  int)

        insert  into  数据：5

        根据以上内容创建存储过程，显示t1当中的记录信息

2、参数的应用

        in  输入参数的使用

        set  @x=3;

        select  @x;

 

        调用必须有对应的实参（类型、数量）将信息带入到存储过程

 

        create  procedure  p2(in  para1  int)  set  @x=para1;

        call  p2(18)；

        select  @x;

       

  用户定义变量:声明开始set @x=3;

               到断开连接结束

               不能和其他用户共享

  作用域：

     存储过程的局部变量：临时结果

      作用范围:从声明位置开始，到end结束

     语法格式

       1)分步

          declare(声明)  var_name  type;

          var_name  初值是null

          set  var_name=值;

       2)declare  var_name  type  default  值；

       3)declare  v1,v2  int  default  6;

 

     out 输出参数的使用

         create  procedure  p8(out  para1  int)

          begin

          set  para1=10;

          end//

          call  p8(x)----à错   x 在end就结束了

          call  p8(@x)--à对   @x在end还没结束

          select  @x;

          select  @y

 

1、         嵌套

create  procedure  p9()

begin

declare  v1  char(10)  default  ‘outer’;

begin

declare  v1  char(10)  default  ‘inner’;

select  v1;

end;

       select  v1;

       end//

       call  p9()//

      结果：inner  outer从里往外显示

 

三、流程控制结构

  第一组：条件分支结构（if、case）

  语法：

      if      条件    then     语句集合

      elseif   条件    then     语句集合

      else    语句集合

      end  if;  注意有空格

   注意：

形参中的变量如果和存储过程中的局部变量同名，局部变量把形参中的变量会“覆盖”；

       形参变量在存储过程中无大小写的要求；

 练习：创建存储过程，如果参数<60，给表tb1插入数据

 

 

          if  para1>=60  then

            insert  into  tb1  values(2);

          else

            insert  into  tb1  values(0);

          end  if;

         end//

         call  tb1_add();

 

 

   case

     语法

      case  变量

        when  变量取值1  then  语句集合

        when  变量取值2  then  语句集合

       else  语句集合

       end  case;

 

  case

    when  变量取值1  表达式  then  语句集合

    when  变量取值2  表达式  then  语句集合

   else

  end case;

 

…………..

 if  str=’M’  then

set  sex=’男’；

 else  if  str=’F’  then

set  sex=’女’;

else

set  sex=’无’;

end if;

end//

 

循环结构

   while…..end  while

   loop….end   loop

   repeat….end   repeat

   goto(不推荐使用，流程混乱，淘汰)

 

 

create  procedure  p10()

begin

  declare  v1  int;

  set  v1=0;

  while  v1<5  do  -----à循环的入口(必须满足的条件)

insert  into  tb1  values(v1);

set  v1=v1+1;   ----à循环的出口(退出循环的条件)

end  while;

end//

call  p10();//

select  *  from  tb1;//

提醒：执行结束后的系统返回是针对最后一条insert 语句

create  procedure  p11()

 begin

  declare  v  int;

  set  v=0;

  repeat

   insert  into  tb1  values(v);

   set  v=v+1;    -------出口条件

   until  v>5;     ------入口条件

end  repeat;

end//

 

create  procedure  p12()

 begin

   declare  v  int;

   set  v=0;

    loop_lable:loop

    insert  into  tb1  values(v);

set  v=v+1;

if  v>5  then

  leave  loop  label;

end if;

   end loop;

end//

删除存储过程

  drop  procedure  名；

四、select  ….into  变量列表

只能应用在存储过程

create  procedure  p12(out  para1  char(20))

begin

 select  s1  into  para1  from  tb1;

end//

select  返回结果：表、列、行、值

游标？

  步骤：

    创建游标：declare

    打开游标；open

    获取记录；fetch

    关闭游标；close

 

例题练习：

     

mysql> delimiter //
mysql> create procedure dele(in xh int)
    -> begin
    ->  delete from tb1 where s1=xh;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call dele(6)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
    -> //
+------+
| s1   |
+------+
|   19 |
+------+
1 row in set (0.00 sec)

mysql> create procedure bianl()
    -> begin
    ->  declare num int(4);
    ->  declare str1,str2 varchar(6);
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure compare1(k1 int,k2 int,out k3 char(6))
    -> begin
    ->  if k1>k2 then set k3='大于';
    ->  elseif k1=k2 then set k3='等于';
    ->  else set k3='小于';
    ->  end if;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call compare1(2,3,@x);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
    -> //
+------+
| @x   |
+------+
| 小于 |
+------+
1 row in set (0.00 sec)

mysql> call compare1(8,3,@x);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
    -> //
+------+
| @x   |
+------+
| 大于 |
+------+
1 row in set (0.00 sec)

mysql> create procedure sex1(in str char(6),out sex char(6))
    -> begin
    ->   case
    ->    when str='m' then set sex='男';
    ->    when str='f' then set sex='女';
    ->    else set sex='无';
    ->   end case;
    -> end//
Query OK, 0 rows affected (0.01 sec)

mysql> call sex1('m',@x)//
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
    -> //
+------+
| @x   |
+------+
| 男   |
+------+
1 row in set (0.00 sec)

mysql> create procedure dowhile()
    -> begin
    ->  declare v1 int default 5;
    ->  while v1>0 do set v1=v1-1;
    ->  select v1;
    ->  end while;
    -> end//
Query OK, 0 rows affected (0.03 sec)

mysql> call dowhile()//
+------+
| v1   |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

+------+
| v1   |
+------+
|    3 |
+------+
1 row in set (0.01 sec)

+------+
| v1   |
+------+
|    2 |
+------+
1 row in set (0.03 sec)

+------+
| v1   |
+------+
|    1 |
+------+
1 row in set (0.05 sec)

+------+
| v1   |
+------+
|    0 |
+------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.06 sec)

mysql> create procedure repe()
    -> begin
    ->  declare v1 int default 6;
    ->  repeat v1=v1-1;
    ->  until v1<1;
    ->  select v1;
    ->  end repeat;
    -> end//

mysql> create procedure doloop()
    -> begin
    ->  set @a=10;
    ->  label:loop
    ->   set @a=@a-1;
    ->   if @a<0 then
    ->     leave label;
    ->   end if;
    ->  end loop label;
    -> end//
Query OK, 0 rows affected (0.06 sec)

mysql> select @a;
    -> //
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> call doloop()//
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;
    -> //
+------+
| @a   |
+------+
| -1   |
+------+
1 row in set (0.00 sec)