【原创】Mysql存储过程学习笔记1

1.为什么选择存储过程：
    1.1 降低网络流量  
    1.2 处理需要检查、循环、多语句但没有用户交互的重复性任务
    1.3 可移植性好，因为与具体语言无关
2
    2.1选择分隔符
      mysql>DELIMITER //
         不使用“；”的原因是：存储过程中有许多语句，所以要选择一个不容易在程序中出现的分隔符
    2.2恢复分隔符
      mysql>Delimiter ;
3  显示存储过程内容：
    show create procedure proc_name;
  show procedure status; ---->列出所有的存储过程
  show function status;
  show table status;

4. 存储体的内容可以是任意数据库操作语句
   但不能有对例程都表操作的数据库操作语句

5. 调用： call p1();//

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
6. Characteristics Clauses 特征子句
   6.1
      create procedure p2()
      Language sql                <--
      not deterministic           <--
      sql security definer        <--
      comment 'a Procedure'       <--
      select current_date,Rand() from t//

   language sql一般无用，只是指下面主本是由SQL语言所写，但是IBM的DB2需要这句，兼容性问题。
   not deterministic（不确定性） 是传递给系统的信息。
                     如上面主体中含有select 语句，那返回肯定是未知的。
                     因此称其 not deterministic
                     但Mysql内置的优化程序不会注意这个，至少现在不注意。
       sql security definer 在调用时检查创建过程用户的权限 。还有一个（sql security invoker)
                     如过程己经调用，则不再检查了。
                     而(invoker)则还需要检查！

7.Parameter 参数
    7.1 create procedure p5()...
    7.2 create procedure p5([in] name data-type)...
    7.3 create procedure p5(out name data-type)...
    7.4 create procedure p5(inout name data-type)...
8 设置一个变量 set @x=0
 in:     create procedure p5(p int) set @x=p//
      call p5(123)//
      select @x//  
 out:   create procedure p6(out p int)
       ->set p=-5//
        call p6(@y)
        select @y
 Compound Statements 复合语句：
    create procedure p7()
     begin
      set @a=5;
      set @b=5;
     insert into person values('lihua',21,'m','store@163.com')
     select name form person where age>@a*@b-10;
   end;//

9. Variables 变量
    declare a int;
    set a=5;
   变量一旦声明，就能在任何能使用会话变量、文字、列名的地方使用

10.   不会改变的sql_mode

mysql> set sql_mode='ansi' //
mysql> create procedure p3()select'a'||'b'//
mysql> set sql_mode=''//
mysql> call p3()//
              不会改变的不会改变的不会改变的不会改变的
+------------+
| 'a' || 'b' |
+------------+
| ab           |
+------------+
这说明：MYSQL 在过程创建时会自动保持运行环境。

(3) Example with DEFAULT clause 含有DEFAULT有例子
 CREATE PROCEDURE p10 ()
BEGIN
  DECLARE a, b INT DEFAULT 5;
  INSERT INTO t VALUES (a);
  SELECT s1 * a FROM t WHERE s1 >= b;
END; //

(5) Scope作用域：
   CREATE PROCEDURE p11 ()
    结果显示了过程能正常工作
BEGIN
 DECLARE x1 CHAR(5) DEFAULT 'outer';
 BEGIN
  DECLARE x1 CHAR(5) DEFAULT 'inner';
  SELECT x1;
  END;
 SELECT x1;
 END; //
内部高于外部

11. Conditions and IF-THEN-ELSE 条件式和IF-THEN-ELSE －end if;   (注意在endif后有分号)

12.CASE指令：
1.
drop procedure if exists p13//
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
   DECLARE variable1 INT;
   SET variable1 = parameter1 + 1;
   CASE variable1
    WHEN 0 THEN insert into person values('PP',11,'m','mm@163.com');
    WHEN 1 THEN insert into person values('MM',22,'f','ff@163.com');
    ELSE        insert into person values('ELSE',33,'el','else@163.com');
   END CASE;
END; //

13.Loops循环语句
    WHILE ... END WHILE
    LOOP ... END LOOP
    REPEAT ... END REPEAT
    GOTO
－－－－－－－－－－－－－－－－－－－－－－－
14.  WHILE ... END WHILE      执行前检查结果
CREATE PROCEDURE p14 ()
BEGIN
 DECLARE v INT;
 SET v = 0;
 WHILE v < 5 DO
  INSERT INTO t VALUES (v);
  SET v = v + 1;
 END WHILE;
END; //

          
PRIME：
drop procedure if exists pro_prime2//
   create procedure pro_prime2(in num int)
    begin
      declare i,j,x,y int default 0;
      select PRI_NUM into j from prime;
      select 'count ',j;
      while i<num do
         set x=2;
         pp1:while x<=sqrt(j) do
            if j%x=0 then
               set y=1;
               leave pp1;
            else
               set x=x+1;
            end if;
         end while;
         if y=1 then
           set y=0;
         else
           set i=i+1;
           insert into prime values(j);
         end if;
           set j=j+1;
      end while;
    end;//

--------------------------------------------
15. REPEAT ... END REPEAT  执行后检查结果，while是执行前。。。[注：UNTIL语句后面可以没有分号]
 CREATE PROCEDURE p15 ()
BEGIN
  DECLARE v INT;
  SET v = 0;
  REPEAT
   INSERT INTO t VALUES (v);
   SET v = v + 1;
   UNTIL v >= 5
  END REPEAT;
END; //
－－－－－－－－－－－－－－－－－－－－－－－
16. LOOP ... END LOOP
 CREATE PROCEDURE p16 ()
BEGIN
                          过程
      我们可以看到调用
  DECLARE v INT;
  SET v = 0;
  loop_label: LOOP
    INSERT INTO t VALUES (v);
    SET v = v + 1;
    IF v >= 5 THEN                <--利用if/leave退出loop循环
      LEAVE loop_label;
    END IF;
  END LOOP;
END; //

----------------------------------------------------------------
 |  可以在begin、while、repeat、loop语句前使用标号           ｜
----------------------------------------------------------------
 |  也可以在语句结束时使用标号，但并不十分有用，只是         ｜
 |  这是良好的编程习惯。方便他人阅读。 如：end while label1; ｜
----------------------------------------------------------------

ITERATE 迭代      如果目标是迭代语句的话，就必须用到leave 语句
它类似于C语言中的continue;

CREATE PROCEDURE p20 ()
BEGIN
 DECLARE v INT;
 SET v = 0;
 loop_label: LOOP
   IF v = 3 THEN
     SET v = v + 1;
     ITERATE loop_label;
     END IF;
   INSERT INTO t VALUES (v);
   SET v = v + 1;
   IF v >= 5 THEN
     LEAVE loop_label;
     END IF;
   END LOOP;
END; //

----------------------------------------
17. GOTO    [不推荐使用]

CREATE PROCEDURE p...
BEGIN
...
LABEL label_name;
...
GOTO label_name;
...
END;