在本教程中，您将学习如何编写/开发返回多个值的存储过程。

MySQL存储函数只返回一个值。要开发返回多个值的存储过程，需要使用带有INOUT或OUT参数的存储过程。

-- 手动创建存储过程 CheckOrderStatus

use test;

DROP PROCEDURE IF EXISTS CountOrderByStatus;

DELIMITER $$

use test$$

CREATE PROCEDURE CheckOrderStatus(

IN cust_name VARCHAR(20),

OUT 50Count INT,

OUT 60Count INT,

OUT 80Count INT,

out 90Count INT)

BEGIN

-- 50Count

SELECT count(*) INTO 50Count

FROM orderinfo

WHERE buyer_name = cust_name

AND order_status = '';

-- 60Count

SELECT count(*) INTO 50Count

FROM orderinfo

WHERE buyer_name = cust_name

AND order_status = '';

-- 80Count

SELECT count(*) INTO 50Count

FROM orderinfo

WHERE buyer_name = cust_name

AND order_status = '';

-- 90Count

SELECT count(*) INTO 50Count

FROM orderinfo

WHERE buyer_name = cust_name

AND order_status = '';

END$$

DELIMITER ;

call CheckOrderStatus('哪吒',@50Count,@60Count,@80Count,@90Count);

SELECT @50Count,@60Count,@80Count,@90Count;

import pymysql

def callProcedure1(name):

    '''调用存储过程，

    输入参数：姓名，输出：统计状态数;

使用输出参数方式'''

    count1 = ''

    count2 = ''

    count3 = ''

    count4 = ''

    db = pymysql.connect('127.0.0.1','adil','helloyyj','test',3306,charset='UTF8')

    cur = db.cursor()

    # 调用    CheckOrderStatus    存储过程，传入4个参数

    cur.callproc('CheckOrderStatus', args= (name, count1, count2, count3,count4))

    # 以 python 固定格式获取返回的值：@_存储过程名_0, 第一个返回值

    t = cur.execute('SELECT @_CheckOrderStatus_0,@_CheckOrderStatus_1,@_CheckOrderStatus_2,@_CheckOrderStatus_3;')

    print(t)

    data = cur.fetchall()

    print(data)

    if data :

        for rec in data:

            for i in range(len(rec)):

                print(rec[i])

    cur.close()

    db.close()

def callProcedure12(name):

    '''调用存储过程，

    输入参数：编号，在线时间，输出：帐号，密码;

    使用select返回记录方式'''

    db = pymysql.connect('127.0.0.1', 'adil', 'helloyyj', 'test1', 3306, charset='UTF8')

    cur =db.cursor()

    # 调用 存储过程

    cur.execute('call CheckOrderStatus1("哪吒",@50Count,@60Count,@80Count,@90Count)')

    # 执行 select  获取 参数返回值

    t1 = cur.execute('SELECT @50Count,@60Count,@80Count,@90Count;')

    print(t1)

    data=cur.fetchall()

    print(data)

    if data :

        for rec in data:

            for i in range(len(rec)):

                print(rec[i])

    cur.close()

    db.close()

if __name__=='__main__':

    # callProcedure1('哪吒')

    callProcedure12('哪吒')

Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")

  result = self._query(query)

# 3719，“3719：'utf8'目前是该别名的别名 字符集UTF8MB3，将来会被UTF8MB4取代 发布。请考虑使用UTF8MB4以明确无误。