日历

2018年十二月
« 11月    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

最近评论

    linux企业应用

    mysql第十二部分 mysql存储过程的编程风格

        前面我们已经编辑了很多的存储过程,虽然每一个过程都能够运行且达到相应的目标,但是实际上并不符合mysql的编程风格。真正的编程风格是这样的:
    mysql> CREATE PROCEDURE proce()
        -> BEGIN
        ->     /* THERE MAYBE SOME COMMENTS!*/
        ->     UPDATE t1 SET filed=2;
        -> END;//
        关键字应该大写,我们已经编辑的众多存储过程一般都是使用小写字母,但真正的编程风格中关键字应该是大写,在命名约定中,数据表明最好是以“t”开头,而每个域,最好以“s”开头。
        注释和C语言中的一样:在BEGIN后的语句一般缩进一个TAB字符或4个空格,END则和BEGIN在同一列。
       
        下面来看几个例子,并学习一下:

         1、Stored Procedure Example: tables_concat()     字符串连接的函数

    下面是一个把所有表明链接到一个字符串的函数,可以和mysql内建的group_concat()函数比较一下:
    mysql> CREATE PROCEDURE tables_connect(OUT parameter_1 VARCHAR(1000))
        -> BEGIN
        -> DECLARE variable_1 VARCHAR(100);
        -> DECLARE cursor_1 CURSOR FOR SELECT table_name FROM information_schema.tables;
        -> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
        -> SET sql_mode=’ansi’;
        -> SET parameter_1=”;
        -> OPEN cursor_1;
        -> LOOP
        ->     FETCH cursor_1 INTO variable_1;
        ->     SET parameter_1=parameter_1||variable_1||’.’;
        -> END LOOP;
        -> CLOSE cursor_1;
        -> END;//
    Query OK, 0 rows affected (0.00 sec)
        在这个存储过程中,DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;这条语句没有任何作用,和其他DBMS的NULL语句一样;将sql_mode设置为’ansi’以便”||”能正常连接,在退出存储过程后sql_mode仍为’ansi’;FETCH cursor1 INTO variable1;当FETCH没有返回行时,由于声明EXIT出错处理,所以会跳出LOOP,这也是跳出LOOP循环的一个方法,即使用EXIT。
          如果你已经使用loop循环建立了这个存储过程的话,还想用repeat重新建立这个存储过程,那么你应该在使用repeat建立这个存储过程之前,执行下面这条命令:
    mysql> drop procedure tables_connect//
    当然,如果不喜欢用loop循环语句的话,可以用repeat,呵呵:
    mysql> create procedure tables_connect(out parameter_1 varchar(1000))
        -> begin
        -> declare variable_1 varchar(100);
        -> declare x int default 0;
        -> declare cursor_1 cursor for select table_name from information_schema.tables;
        -> declare continue handler for not found set x=1;
        -> set sql_mode=’ansi’;
        -> set parameter_1=”;
        -> open cursor_1;
        -> repeat
        ->     fetch cursor_1 into variable_1;
        ->     set parameter_1=parameter_1||variable_1||’ ‘;
        -> until x=1 end repeat;
        -> close cursor_1;
        -> end;//
          建立好这个存储过程后,接下来就可以调用这个存储过程了,如果你像我一样不幸的话,也许会遇到下面这个错误:
    mysql> call tables_connect(@x)//
    Query OK, 0 rows affected,38warnings (0.03 sec)
        这里提示一些警告信息,如果你想查看结果的话你会发现结果为NULL或0,还是首先看一下这些警告吧:
    mysql> show warnings//
    +———+——+——————————————+
    | Level | Code | Message |
    +———+——+——————————————+
    | Warning | 1292 | Truncated incorrect DOUBLE value: ‘.’   所有的错误信息都是一样的incorrect DOUBLE value: ‘.’,实际上是mysql并没有按照我们设定的set sql_mode=’ansi’;来做,为了解决这个问题,可以在这个建立这个存储过程之前执行一下这条命令: 
    mysql> set sql_mode:=’ansi’;
        然后再建立我们的存储过程,当然再建立这个存储过程可以包含set sql_mode := ‘ansi’; 这条语句,呵呵,你也可以试试没有这句的情况!实际上都是可以的!(^_^)
        下面可以调用这个过程并查看结果了:
    mysql> call tables_connect(@x)//
    Query OK, 0 rows affected (0.00 sec)

    mysql> select @x//
    +——-+
    | @x                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
    +———–+
    | CHARACTER_SETS.COLLATIONS.COLLATION_CHARACTER_SET_APPLICABILITY.COLUMNS.COLUMN_PRIVILEGES.KEY_COLUMN_USAGE.ROUTINES.SCHEMATA.SCHEMA_PRIVILEGES.STATISTICS.TABLES.TABLE_CONSTRAINTS.TABLE_PRIVILEGES.TRIGGERS.USER_PRIVILEGES.VIEWS.error_log.t1.t2.t3.t4.columns_priv.db.func.help_category.help_keyword.help_relation.help_topic.host.proc.procs_priv.tables_priv.time_zone.time_zone_leap_second.time_zone_name.time_zone_transition.time_zone_transition_type.user. |
    +————–+
    1 row in set (0.00 sec)所有的表的名字都连接起来了!

    下面建立一个存储过程来获得整型包含行数的结果集,和其他DBMS中的ROWNUM()函数类似。这个过程需要一个用户变量来保存在每次调用rowline()后的结果,这个过程就命名为@rowline吧,建立过程如下:
    mysql> CREATE FUNCTION rowline()
        -> RETURNS INT
        -> BEGIN
        ->     SET @rowline=@rowline+1;
        ->     RETURN @rowline;
        -> END;//
    ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
        出错了!!在编写这个存储过程前,执行一下这条指令:mysql> set global log_bin_trust_function_creators=1;//,好了再建立这个过程就没有错误了,哈哈!其实这个函数的效果和下面的一样:
    mysql> set @rowline=0;//
    Query OK, 0 rows affected (0.00 sec)

    mysql> select @line=@line+1 as line,filed from t1;//
    +——+——–+
    | line  | filed  |
    +——+——–+
    |    0 | 362880 |
    +——+——–+
    1 row in set (0.00 sec)
        那么对于建立的函数,可以这样使用,下面是执行步骤和执行结果:
    mysql> set @rowline=0;//
    Query OK, 0 rows affected (0.00 sec)

    mysql> select rowline(),filed from t1;//
    +———–+——–+
    | rowline() | filed  |
    +———–+——–+
    |         1 | 362880 |
    +———–+——–+
    1 row in set (0.00 sec)

     在累加函数rowline()的基础上,新建立一个函数,即可以向函数体内传递参数的函数:
     mysql> CREATE FUNCTION running_total(addr INT)
        -> RETURNS INTEGER
        -> BEGIN
        ->     SET @running_total=@running_total+addr;
        ->     RETURN @running_total;
        -> end;//
    Query OK, 0 rows affected (0.00 sec)
        如果在创建这个函数的时候CREATE FUNCTION running_total(addr INT)中,在括号内的写法是(IN addr INT),那么编写完后会提示语法错误如下:
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IN addr INT)
    RETURNS INTEGER
    BEGIN
    SET @running_total=@running_total+addr;
    R’ at line 1
        其实你只要把参数中的表示输入的IN删除掉,就不会报错了!…(^_^)!!
        调用这个函数,并执行select后的结果如下:
    mysql> SET @running_total=0;//
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT running_total(filed),filed FROM t1 ORDER BY filed;//
    +———————-+——-+
    | running_total(filed) | filed |
    +———————-+——-+
    |                    1 |     1 |
    |                    3 |     2 |
    |                    6 |     3 |
    |                   10 |     4 |
    +———————-+——-+
    4 rows in set (0.00 sec)  

        running_total()函数在ORDER BY完成后调用,但这样既不标准也不能被移植,不是很完美!

                   Procedure Example: MyISAM “Foreign Key” insertion  MyISAM存储引擎不支持外键,但是你可以将这个逻辑加入存储过程引擎进行检查。

    mysql> create procedure fk_insert(p_fk int,p_animal varchar(10))
        -> begin
        ->     declare v int;
        ->     begin
        ->         declare exit handler for sqlexception,not found set v=0;
        ->         if p_fk is not null then
        ->             select 1 into v from tpk where cpk=p_fk limit 1;
        ->             insert into tfk values(p_fk,p_animal);
        ->         else
        ->             set v=1;
        ->         end if;
        ->     end;
        ->     if v<>1 then
        ->         drop table `the insertion failed`;#注意这个地方不是“’”,而是tab键上方键的输入
        ->     end if;
        -> end;
        -> //
    注意::SQLEXCEPTION或NOT FOUND条件都会导致v变成0,如果这些条件为假,则v会变成1,因为SELECT会给v赋值1,而EXIT HANDLER没有运行。下面看看运行结果:
    mysql> create table tpk(cpk int primary key);//
    Query OK, 0 rows affected (0.02 sec)

    mysql> create table tfk(cfk int,canimal varchar(10));//
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into tpk(cpk) values(1),(3),(5);//
    Query OK, 3 rows affected (0.00 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> call fk_insert(1,’wombat’);//
    Query OK, 1 row affected (0.01 sec)

    mysql> call fk_insert(null,’wallaby’);//
    Query OK, 1 row affected (0.00 sec)

    mysql> call fk_insert(6,’wendigo’);//
    ERROR 1051 (42S02): Unknown table ‘the insertion failed’

          Procedure Example: Error Propagation        错误传递
         设想如果过程1调用过程2,过程2调用了过程3,过程3中的错误就会传递到过程1。如果没有异常处理器捕获异常,那异常就会传递,导致过程2出错,最后导致过程1出错,最终异常传递到了调用者(MySQL客户端实例)。这种特性使得标准SQL中存在SIGNAL语句来使异常强制发生,其他DBMS中也有类似措施 (RAISEERROR )。MySQL还不支持SIGNAL,直到支持此特性的新版本推出,大家就可以用SIGNAL来进行处理了。大家也可以用下面的异常处理方式。

    mysql> create procedure procedure1()
        -> begin
        ->     call procedure2();
        ->     set @x=1;
        -> end;//
    Query OK, 0 rows affected (0.00 sec)

    mysql> create procedure procedure2()
        -> begin
        ->     call procedure3();
        ->     set @x=2;
        -> end;//
    Query OK, 0 rows affected (0.00 sec)

    mysql> create procedure procedure3()
        -> begin
        ->     drop table error.`error #7815`;
        ->     set @x=3;
        -> end;//
    Query OK, 0 rows affected (0.01 sec)
        调用过程1,显示如下结果:
    mysql> call procedure1();//
    ERROR 1051 (42S02): Unknown table ‘error #7815’,这样就将错误传递上来了。|
         @x并没有改变,因为没有一条”SET @x = …”语句成功运行,而使用DROP可以产生一些可供诊断的错误信息。不过,如果有名字叫做`error`的表存在,就不能这么使用了

        Procedure Example: Library  库
         对库的应用有详细的规格说明。我们希望拥有权限的用户都能调用过程,所以我们这样设置:
                 GRANT ALL ON database-name.* TO user-name;        
         如果要其他用户只有访问使用过程的权限,没有问题,只需要定义SQL SECURITY DEFINER特性就可以了,而这个选项是默认的,但最好显式的声明出来。
         下面是一个向数据库中添加书本信息的过程,这里必须测试书的id是否确定,书名是否为空。例子             是对MySQL不支持的CHECK限制功能的替代。            
     mysql> create procedure add_book(p_book_id int,p_book_title varchar(100))
        -> sql security definer
        -> begin
        ->     if p_book_id<0 or p_book_title=” then
        ->         select ‘Warning: Bad parameter’;
        ->     end if;
        ->     insert into books values(p_book_id,p_book_title);
        -> end;//
    Query OK, 0 rows affected (0.00 sec)
        我们需要一个添加买主的过程,过程必须检查是否有超过一个的买主,如有则给出警告。这个功能可以在一个子查询中完成,如下:  IF (SELECT COUNT(*) FROM table-name) > 2) THEN … END IF;      

         不过,在作此书时子查询功能有漏洞,于是我们用”SELECT COUNT(*) INTO variable-name” 代替。            
    mysql> create procedure add_patron(p_patron_id int,p_patron_name varchar(100)) sql security definer
        -> begin
        ->     declare v int default 0;
        ->     select count(*) from patrons into v;
        ->     if v>2 then
        ->         select ‘Warning : aready there are ‘,v,’ patrons’;
        ->     end if;
        ->     insert into patrons values(p_patron_id,p_patron_name);
        -> end;//
    Query OK, 0 rows affected (0.00 sec)  
        下面我们需要书本付帐的过程。在事务处理过程中我们希望显示已经拥有本书的买主,以及这些买主拥有的书,这些信息可以通过对游标CURSOR的Fetch来获得,我们会使用两种不同的方法来测试是否fetch数据已经完毕,第一种是检查变量在fetch动作后是否是NULL,第二种是通过NOT FOUND错误处理捕获fetch的失败动作。如果两个游标在不同的BEGIN/END块中程序看起来会显得整洁,但我们要在主BEGIN/END块中声明这些变量,这样做才能使它们的作用域覆盖整个过程。           mysql> create procedure checkout(p_patron_id int,p_book_id int) sql security definer
        -> begin
        ->  declare v_patron_id,v_book_id int;
        ->  declare no_more boolean default false;
        ->  declare continue handler for not found set no_more=true;
        ->  begin
        ->    declare cursor_1 cursor for select patron_id from transactions where book_id=p_book_id;
        ->    open cursor_1;
        ->    set v_patron_id=NULL;
        ->    fetch cursor_1 into v_patron_id;
        ->    if v_patron_id is not null then
        ->       select ‘book is already out to this patron:’,v_patron_id;
        ->     end if;
        ->     close cursor_1;
        ->   end;
        ->  begin
        ->    declare cursor_2 cursor for select book_id from transactions where patron_id=p_patron_id;
        ->    open cursor_2;
        ->    book_loop:loop
        ->       fetch cursor_2 into v_book_id;
        ->       if no_more then
        ->           leave book_loop;
        ->       end if;
        ->       select ‘patron already has this book:’,v_book_id;
        ->    end loop;
        ->    end;
        -> insert into transactions values(p_patron_id,p_book_id);
        -> end;
        -> //
    Query OK, 0 rows affected (0.11 sec)

         Procedure Example: Hierarchy (I)  分层次

       hierarchy()过程实现的是其他DBMS中CONNECT BY部分功能。我们拥有一个Persons表,表中的后代通过person_id列与祖先相连。我们通过参数start_with传递列表的第一个人。然后按顺序显示祖先和后代。这个过程(实际是两个)代码在后面两页,相信大家学了这么久,阅读得仔细点也可以理解,不过开始我还是会给一些说明性的注释。hierarchy()过程接受person的名字作为输入参数,作用有点像初始化动作,不过重要的是hierarchy()建立了个临时表,用来存储查找到的结果行的数据。然后hierarchy()调用了hierarchy2()过程,此时hierarchy2() 过程开始进行循环,不停的调用自身。如果父亲只有1或0个儿子,这一步就不需要了,但事实上不会如此,所以要查询到每个分支,因此要对树进行不断的循环查询,直到最后节点才返回分支点进行另一个分支的查询。这里还在每条SQL语句执行后使用了错误处理set a flag (error),如果语句失败,程序使用”SELECT ‘string'”输出诊断信息,然后离开出错的过程。同时在这里还使用了复合语句的嵌套(就是在BEGIN END块中放置BEGIN END块),所以可以为关联特定语句的变量和出错处理进行声明。不过记住在外层复合语句中的声明在内层语句中仍有效,除非使用了重载,还有就是在内层复合语句完毕后,内层的声明就失效。 下面首先是hierarchy()过程的创建代码,另一个则是hierarchy2()过程的,在此之后的是成功调用hierarchy()过程的结果。 

      mysql> CREATE PROCEDURE hierarchy(start_with char(10))
        ->      proc:
        ->      BEGIN
        ->          DECLARE temporary_table_exists BOOLEAN;
        ->          BEGIN
        ->              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
        ->              DROP TABLE IF EXISTS Temporary_Table;
        ->          END;
        ->          BEGIN
        ->              DECLARE v_person_id,v_father_id INT;
        ->              DECLARE v_person_name CHAR(20);
        ->              DECLARE done,error BOOLEAN DEFAULT FALSE;
        ->              DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
        ->              DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=TRUE;
        ->              CREATE TEMPORARY TABLE Temporary_Table(person_id INT,person_name CHAR(20),father_id INT);
        ->              IF error THEN
        ->                 SELECT ‘CREATE TEMPORARY failed’;LEAVE proc;END IF;
        ->              SET Temporary_table_exists=TRUE;
        ->              SELECT PERSON_id,person_name INTO v_person_id,v_person_name FROM Persons WHERE person_name=start_with limit 1;
        ->              IF error THEN
        ->                 SELECT ‘First SELECT failed’;LEAVE proc;END IF;
        ->              IF v_person_id IS NOT NULL THEN
        ->                 INSERT INTO Temporary_Table VALUES(v_person_id,v_person_name,v_father_id);
        ->              IF error THEN
        ->                 SELECT ‘First INSERT failed’;LEAVE proc;END IF;
        ->              CALL hierarchy2(v_person_id);
        ->              IF error THEN
        ->                 SELECT ‘First CALL hierarchy2() failed’;END IF;
        ->              END if;
        ->             SELECT person_id,person_name,father_id FROM Temporary_Table;
        ->             IF error THEN
        ->             SELECT ‘Temporary SELECT failed’;LEAVE proc;END IF;
        ->          END;
        ->          IF temporary_table_exists THEN
        ->             DROP TEMPORARY TABLE Temporary_Table;
        ->             END IF;
        ->      END;//
    Query OK, 0 rows affected (0.03 sec)

    mysql> CREATE PROCEDURE hierarchy2(start_with INT)
        -> proc:
        -> BEGIN
        ->   DECLARE v_person_id,v_father_id INT;
        ->   DECLARE v_person_name CHAR(20);
        ->   DECLARE done,error BOOLEAN DEFAULT FALSE;
        ->   DECLARE cursor_1 CURSOR FOR SELECT person_id,person_name,father_id FROM Persons WHERE father_id=start_with;
        ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
        ->   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=TRUE;
        ->   OPEN cursor_1;
        ->   IF error THEN
        ->   SELECT ‘OPEN failed’; LEAVE proc;END IF;
        ->   REPEAT
        ->     SET v_person_id=NULL;
        ->     FETCH cursor_1 INTO v_person_id,v_person_name,v_father_id;
        ->     IF error THEN
        ->     SELECT ‘FETCH failed’;LEAVE proc;END IF;
        ->     IF done=FALSE THEN
        ->       INSERT INTO Temporary_Table VALUES(v_person_id,v_person_name,v_father_id);
        ->       IF error THEN
        ->       SELECT ‘INSERT in hierarchy2() failed’;END IF;
        ->     CALL hierarchy2(v_person_id);
        ->     IF error THEN
        ->     SELECT ‘Recursive CALL hierarchy2() failed’;END IF;
        ->     END IF;
        ->     UNTIL done=TRUE
        ->   END REPEAT;
        ->   CLOSE cursor_1;
        ->   IF error THEN
        ->   SELECT ‘CLOSE failed’;END IF;
        -> END;//
    Query OK, 0 rows affected (0.00 sec)                                                                                                                                

       下面是调用hierarchy()后的结果: 

    mysql> CREATE TABLE Persons(person_id INT,person_name CHAR(20),father_id INT);//
    Query OK, 0 rows affected (0.14 sec)

    mysql> INSERT INTO Persons VALUES(1,’Grandpa’,NULL);//
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO Persons VALUES(2,’Pa-1′,1),(3,’Pa-2′,1);//
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> INSERT INTO Persons VALUES(4,’Grandson-1′,3),(5,’Grandson-2′,3);//
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> call hierarchy(‘Grandpa’);//
    +————————————+
    | Recursive CALL hierarchy2() failed |
    +————————————+
    | Recursive CALL hierarchy2() failed |
    +————————————+
    1 row in set (0.04 sec)

    +————–+
    | FETCH failed |
    +————–+
    | FETCH failed |
    +————–+
    1 row in set (0.04 sec)

    +———–+————-+———–+
    | person_id | person_name | father_id |
    +———–+————-+———–+
    |         1 | Grandpa     |      NULL |
    |         2 | Pa-1        |         1 |
    +———–+————-+———–+
    2 rows in set (0.06 sec)

    Query OK, 0 rows affected, 1 warning (0.06 sec)  #真正的结果不应该是这样,应该是5个人都能查出来,没有时间调了:sad:

               查询从祖先一直查到孙子,然后又从儿子节点开始查询,使用的是“深度优先”算法。也许hierarchy()过程过于复杂,会有些我没考虑到的漏洞。循环太深的话也许会出错,试想如果 有100代,则当有人是自己的爷爷时(这里是直译的,不过大家也能想到的,就是其中树的节点互相缠绕,形成一个环),就会进入无限循环。不过我在这里只是为了说明MySQL也可通过对存储过程的循环实现CONNECT BY功能。

    评论已关闭。