MySQL存储过程相互调用

例一：

--表格
      mysql> select corderno,mshippingcharges,mgiftwrapcharges from orders;
      +----------+------------------+------------------+
      | corderno | mshippingcharges | mgiftwrapcharges |
      +----------+------------------+------------------+
      | 000001   |                6 |             1.25 |
      | 000002   |                8 |                2 |
      | 000003   |               12 |                0 |
      +----------+------------------+------------------+
      9 rows in set
       
      --
      create procedure prccharges(in orderno char(6),
             out shippingCharges float(4,2),out wrapCharges float(4,2))
             begin 
                 select mshippingcharges into shippingCharges from 
                 orders where corderno = orderno;
                 select mgiftwrapcharges into wrapcharges from orders where corderno = orderno;
             end;
             
      --执行时命令
      call prccharges('000001',@pp,@qq);
      select @pp,@qq;
    
      --执行结果
      mysql> select @pp,@qq;
      +-----+------+
      | @pp | @qq  |
      +-----+------+
      | 6   | 1.25 |
      +-----+------+
      1 row in set
    
    
      create procedure prcHandLingCharges(in orderno char(6),
         out handlingCharges float(4,2))
            begin 
              declare ppp float(4,2);
              declare qqq float(4,2);
              call prccharges(orderno,ppp,qqq);
              set handlingCharges = ppp + qqq;  
            end;
       
      
      --执行时命令
     call prcHandLingCharges('000001',@qqqq);
     select @qqqq;
    
      --执行结果
      mysql> select @qqqq;
      +-------+
      | @qqqq |
      +-------+
      | 7.25  |
      +-------+
      1 row in set
    

例二:

01

mysql>

02	mysql> delimiter $$

03

mysql>

04	mysql> CREATE PROCEDURE myProc()

05	->      MODIFIES SQL DATA

06

-> BEGIN

07	->      DECLARE l_status VARCHAR(20);

08

->

09	->     CALL myProc1(l_status);

10	->        IF l_status='Duplicate Entry' THEN

11	->               SELECT CONCAT('Warning: using existing definition for location ') AS warning;

12	->      END IF;

13

-> END$$

14	Query OK, 0 rows affected (0.00 sec)

15

16

mysql>

17	mysql> CREATE PROCEDURE myProc1(OUT out_status VARCHAR(30))

18

-> BEGIN

19	->    set out_status = 'Duplicate Entry';

20

-> END$$

21	Query OK, 0 rows affected (0.00 sec)

22

23

mysql>

24

mysql>

25	mysql> delimiter ;

26	mysql> call myProc();

27	+--------------------------------------------------+

28	| warning                                          |

29	+--------------------------------------------------+

30	| Warning: using existing definition for location  |

31	+--------------------------------------------------+

32	1 row in set (0.00 sec)

33

34	Query OK, 0 rows affected (0.01 sec)

35

36	mysql> drop procedure myProc;

37	Query OK, 0 rows affected (0.00 sec)

38

39	mysql> drop procedure myProc1;

40	Query OK, 0 rows affected (0.00 sec)

41

42

mysql>

43

mysql>