mysql - 我的存储过程的返回值

Question

我使用存储过程来优化我的sql脚本的查询。这是一个在执行时更新数据库表的脚本，在没有结果的同时我没有错误。我如何返回存储过程的值？

这是我的脚本:

        DELIMITER |

        CREATE PROCEDURE data_table1_test()

        BEGIN

        DROP TEMPORARY  TABLE IF EXISTS ch.table1;

        CREATE TEMPORARY TABLE ch.table1
        SELECT STRAIGHT_JOIN
        SOITAB.champ1 as champ1,
        CONCAT(SOITAB.champ1,' ',SOITAB.fk_champcam ) as champ1_cam,SOITAB.champup as champup,
        supM(CASE WHEN (SOITAB.fk_champsois IN (8,11,12,14,18,19,20,47,51)) THEN 1 ELSE 0 END)/COUNT(DISTINCT CSSTAB.champicss) as champqtr,
        supM(CASE WHEN (SOITAB.fk_champsois In (2,3,4,5,6,7,8,11,12,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,47,51))
        THEN 1 ELSE 0 END)/COUNT(DISTINCT CSSTAB.champicss) as champqtn,
        CTAB.champidcam as champidcam,
        CTAB.champna as champna, 
        CTAB.champuk as champuk,
        CTAB.champst as champst,
        CTAB.champet as champet,
        CTAB.champiden as champiden,
        CTAB.champdeba as champdeba,
        CSTAB.champids as champids,
        CSTAB.champcat as champcat,
        CSTAB.champop as champop,
        CSTAB.champshco as champshco,
        CSTAB.champxc as champxc,
        LEFT(CSTAB.champxc,2) as champscon,
        CSTAB.champdowe as champdowe,
        CSTAB.fk_caogst as sht,
        CSTAB.pr as pr,
        CCTAB.champidcc as champidcc,
        CCTAB.champna as p_champna,
        CCTAB.champdes as pchampdes,
        CCTAB.fk_ca_br,
        MAX(LEFT(CCTAB.note,3)) AS bu,
        GROUP_CONCAT(champspn, ' by ',FORMAT(CSSTAB.qu,0)  ORDER BY champspn ASC SEPARATOR ' + ') as sup_champ1,
        supm(distinct(CSSTAB.npp*CSSTAB.qu)) AS champpfi,
        CsupTAB.champna as sup_champna,
        CsupTAB.id_champcsu as sup_id,
        CsupTAB.champiden as sup_champiden,
        CCDTAB.champidcc as champidcc,
        CCDTAB.ct1 as pct1,
        CCDTAB.ct2 as pct2,
        COUNT(distinct CSSTAB.champicss) as champseti,ch.PVGTAB.pv as pv, 
        supm(distinct(CSSTAB.npp*CSSTAB.qu)) AS npp,CHCTAB.p as p, 
        max(SOITAB.champcat) as lod

        FROM bdata.champsoit SOITAB

        LEFT OUTER JOIN bdata.champcsi CSTAB ON CSTAB.champ1 = SOITAB.champ1 
        LEFT OUTER JOIN bdata.champcam CTAB ON SOITAB.fk_champcam = CTAB.champidcam
        LEFT OUTER JOIN bdata.champcsi_set CSSTAB ON CSSTAB.fk_champcsi = CSTAB.champids
        LEFT OUTER JOIN bdata.champccco CCTAB ON CSTAB.fk_champccco = CCTAB.champidcc
        LEFT OUTER JOIN bdata.champcccohcca CCHCTAB ON CCTAB.champidcc = CCHCTAB.fk_champccco
        LEFT OUTER JOIN ch.champccde CCDTAB ON CCHCTAB.fk_c_cat= ch.CCDTAB.champidcc
        LEFT OUTER JOIN bdata.champcamhch CHCTAB ON CHCTAB.fk_champccco = CCTAB.champidcc
        LEFT OUTER JOIN ch.champpv PVGTAB on ch.PVGTAB.url = CCTAB.champidcc
        LEFT OUTER JOIN bdata.champcsu CsupTAB ON CCTAB.fk_champcsu = CsupTAB.id_champcsu

        WHERE CSTAB.champstat = 'active' AND CCTAB.champetap = 1 AND CCHCTAB.is_primary = '1'
        GROUP BY SOITAB.champ1,SOITAB.fk_champcam;

        END |
        DELIMITER;


        #####################champpgrte###############################################

        DELIMITER |
        CREATE PROCEDURE data_table2_test()
        BEGIN

        DROP TEMPORARY TABLE IF EXISTS ch.table2;
        CREATE TEMPORARY TABLE ch.table2

        SELECT STRAIGHT_JOIN

        champspete.champ1,
        supM(champspete.champqtn) as champqtn ,
        supM(champspete.champqtr) as champqtr, 
        count(distinct champspete.campaign_id) as champscam, 
        champspete.sup_champna, 
        champspete.lod

        FROM ch.champspete 

        GROUP BY ch.champspete.champ1;
        ALTER TABLE ch.champpgrte ADD INDEX (champ1);
        END |
        DELIMITER ;

Answer 1

您可以在程序中使用 OUT 参数。

DELIMITER $$
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),
 OUT total INT)
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;

调用流程

CALL CountOrderByStatus('Shipped',@total);
SELECT @total;