从Oracle存储过程返回XML

我有以下程序：

CREATE OR REPLACE PROCEDURE USPX_GetUserbyID (USERID USERS.USERID%TYPE,USERRECORD OUT XMLTYPE) AS
BEGIN

    SELECT XMLELEMENT("user",XMLATTRIBUTES(u.USERID AS "userid",u.companyid as "companyid",u.usertype as "usertype",u.status as "status",u.personid as "personid"),XMLFOREST(  p.FIRSTNAME AS "firstname",p.LASTNAME AS "lastname",p.EMAIL AS "email",p.PHONE AS "phone",p.PHONEEXTENSION AS "extension"),XMLELEMENT("roles",(SELECT XMLAGG(XMLELEMENT("role",r.ROLETYPE))
                    FROM USER_ROLES r
                    WHERE r.USERID = USERID
                        AND r.ISACTIVE = 1
                )
            ),XMLELEMENT("watches",(SELECT XMLAGG(
                    XMLELEMENT("watch",XMLATTRIBUTES(w.WATCHID AS "id",w.TICKETID AS "ticket")
                    )
                )
                FROM USER_WATCHES w
                WHERE w.USERID = USERID
                AND w.ISACTIVE = 1
                )
            )
        ) AS "RESULT"
    INTO USERRECORD
    FROM USERS u 
    LEFT JOIN PEOPLE p ON p.PERSONID = u.PERSONID
    WHERE u.USERID = USERID;
    END USPX_GetUserbyID;

执行时,它应返回具有以下结构的XML文档：

<user userid="" companyid="" usertype="" status="" personid="">
    <firstname />
    <lastname />
    <email />
    <phone />
    <extension />
    <roles>
        <role />
    </roles>
    <watches>
        <watch id="" ticket="" />
    </watches>
</user>

当我执行查询本身,用字符串替换USERID参数并删除“into”子句时,查询运行正常并返回预期的结构.

但是,当该过程尝试执行查询时,将XMLELEMENT函数的结果传递给USERRECORD输出参数,我得到以下异常：

Error report: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "USPX_GETUSERBYID",line 4 ORA-06512: at line 3
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

我很困惑,试图解决这个问题,不幸的是我的google-fu没有帮助.我发现了大量的Oracle SQL | XML示例,但没有任何处理XML的过程返回.

注意：我知道存在使用DBMS方法检索XML的替代方法,我的理解是不推荐使用该功能而使用SQL | XML.

u.USERID = USERID;

虽然您希望将裸USERID作为过程的参数,但Oracle实际上优先考虑表中列的USERID.实际上它将其解释为

u.USERID = u.USERID;

您可以使用

u.USERID = USPX_GetUserbyID.USERID;

但最好使用PL / SQL变量的前缀来避免混淆.我倾向于v_表示变量,而i_,o_,io_表示输入,输出和输入/输出参数.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!