如何从内部调用获取记录集到存储过程? [英] How to get recordset from internal call to stored procedure?

问题描述

我有一个存储过程，内部我想调用另一个返回记录集的过程，如何通过'CALL'导航存储过程返回的记录集?

I have a stored procedure, internally I want to call another procedure that returns a record set, how do I get an navigate the record set returned by the stored procedure via the 'CALL' ?

[edit] 我一直在尝试按照建议使用 TEMPORARY TABLE，但遇到了问题:

[edit] I've been trying to use a TEMPORARY TABLE as suggested, but having problems:

    DROP TEMPORARY TABLE IF EXISTS tbl_HeadOfDepts;
    CREATE TEMPORARY TABLE tbl_HeadOfDepts (biDept_id tinyint(4))
    INSERT INTO tbl_HeadOfDepts CALL rsHeadOfAnyDepartments(vcCompKey, biWho_id);

我需要使用 CALL，因为 'rsHeadOfAnyDepartments' 不是函数，但这不会被接受.

I need to use CALL because 'rsHeadOfAnyDepartments' is not a function, but this will not be accepted.

工作正在进行中，但到目前为止我没有被编辑接受:

Work in progress, but what I have so far that is not accepted by editor:

    BEGIN
    #--
    # Procedure:
    #   rsWhoCanIaccess
    #
    # Parameters:
    #   vcCompKey, the key corresponding to the company
    #   biWho_id, the id of the person to check access for
    #
    # Returns:
    #   recordset containing all the people this person can access
    #--
        DECLARE tiSuperUser tinyint(4); 
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, 
                @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
                CALL procLogError(vcCompKey, CONCAT("rsWhoCanIaccess: "
                                        ,@errno, " (", @sqlstate, "): ", @text));
            END;        
    #Is this user a super user?
        SELECT tiIsSuperUser(vcCompKey, biWho_id) INTO tiSuperUser;
        SET tiSuperUser = 0;#Hack for testing
        IF (tiSuperUser = 1) THEN
    #The user is a superuser, return everyone in the company
            SELECT 
                t1.biPerson_id 
            FROM 
                tbl_people t1
            INNER JOIN
                tbl_companies t2
            ON
                t1.biCompany_id=t2.biCompany_id
            AND
                t2.vcKey=vcCompKey;         
        ELSE            
    #User is not a superuser, is the user head of any departments?
            DROP TEMPORARY TABLE IF EXISTS tbl_HeadOfDepts;
            CREATE TEMPORARY TABLE tbl_HeadOfDepts (biDept_id tinyint(4))
            INSERT INTO tbl_HeadOfDepts CALL rsHeadOfAnyDepartments(vcCompKey, biWho_id);

            SELECT * FROM tbl_HeadOfDepts;
        END IF;
    END

推荐答案

不，存储过程可以生成结果集，但不能直接将它们作为对其他存储过程的内部调用的输出来使用.您可以在性能方面做的最好的事情是填充一个非临时工作表并使用结果.

No, stored procedures can produce result sets, but not consume them directly as output from inner calls to other stored procedures. The best that you can do performance-wise is to populate a non-temporary work table and use the results.

根据您的软件和同时多个调用者的实际情况，您可能需要在某些控制表中包含一个带有 auto_increment (AI) 列的会话 ID 概念.这将确保在并发的情况下，多个调用者不会踩到彼此的行，从而使其不可行.

Depending on your software and the reality of multiple callers concurrently, you might need to include a session id concept with an auto_increment (AI) column in some control table. This would ensure that with concurrency, multiple callers are not stomping on each other's rows, thus making it non-viable.

从高层次上讲，该会话的工作方式如下.内部存储过程将从控制表中获得一个 AI 值(theSession)，使用它在工作表中填充安全分段的会话，并以 out 参数到外部(调用)存储过程.然后那个外层可以安全地使用这些行，并在最后清理(delete from workTable where sessionId=theSession).

How that session would work, at a high level, is the following. The inner stored proc would be handed an AI value (theSession) from the control table, use it to populate a safely segmented session in the work table, and return as an out parameter to the outer (calling) stored proc. That outer one could then safely use those rows, and clean up at the end (delete from workTable where sessionId=theSession).

为什么我建议使用非临时工作台?需要明确的是，工作台将是非临时的.首先，让 if exists drop 工作很麻烦.不过，最重要的是，它与性能有关.用于创建临时表的 DDL 调用并不便宜.只有当您进行性能测试以了解我的意思时，您才会相信这一点.这可能看起来微不足道，但在微不足道的操作中，那些创建 DDL 调用很可能占到完成内部存储过程所需的大部分时间.

Why do I suggest a non-temporary work table? To be clear, the work table would be non-temporary. First of all there is the hassle of getting the if exists drop to work. Most importantly, though, it is about performance. DDL calls for temporary table creation are not inexpensive. You will only believe this when you do performance testing to see what I mean. It may seem trivial, but in trivial operations, those DDL calls for creation could very well account for the lion share of the time necessary for the inner stored proc to complete.

这篇关于如何从内部调用获取记录集到存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！