MySQL存储过程相互调用

绝地灬酷狼 2021-06-24 14:34 554阅读 0赞

**1、存储过程相互调用：**

--表格
          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

2、在存储过程中，可以使用动态执行sql的方式来返回结果集。eg：

DELIMITER $$ 
    DROP procedure IF EXISTS pro_report_data $$  
    CREATE procedure pro_report_data(in startT  varchar(100),in stopT  varchar(100),in buyerFilter varchar(20),gfs varchar(2000),qfs varchar(2000))  
    BEGIN   
    	declare colsStr varchar(2000);
    	declare tableStr varchar(2000);
    	declare whereStr varchar(2000);
    	declare groupStr varchar(2000);
    	declare sqls varchar(2000);
    	
    	call createReportSql4Other(null,null,0,buyerFilter,gfs,qfs,2,startT,stopT,0,colsStr,tableStr,whereStr,groupStr);
    	set sqls= concat('select ',colsStr,' from ',tableStr,' where ',whereStr,' group by ',groupStr);
    	set @ms=sqls; 
    	PREPARE s1 from @ms; 
    	EXECUTE s1; 
    	deallocate prepare s1; 
    END$$
    DELIMITER;