如何从内部调用到存储过程获取记录集? [英] 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.

该会议将在更高层次上进行的方式如下.内部存储的proc将从控制表中获得一个AI值(theSession)，使用它在工作表中填充一个安全分段的会话，并作为out参数返回到外部存储(调用) proc.然后，那个外面的人可以安全地使用这些行，并在末尾进行清理(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屋！