从Oracle存储过程返回XML。

Unfortunately most of my DB experience has been with MSSQL which tends to hold your hand a lot more than Oracle. What I'm trying to do is fairly trivial in tSQL, however, pl/sql is giving me a headache.

不幸的是，我的大多数DB经验都是使用MSSQL的，它比Oracle更倾向于握住您的手。在tSQL中，我要做的是相当琐碎的事情，然而，pl/sql让我头疼。

I have the following procedure:

我有以下程序:

    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;

When executed, it should return an XML document with the following structure:

执行时，应返回具有以下结构的XML文档:

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

When I execute the query itself, replacing the USERID parameter with a string and removing the "into" clause, the query runs fine and returns the expected structure.

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

However, when the procedure attempts to execute the query, passing the results of the XMLELEMENT function into the USERRECORD output parameter, I get the following exception:

但是，当过程尝试执行查询时，将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

I'm baffled trying to nail this down, and unfortunately my google-fu hasn't helped. I've found plenty of Oracle SQL|XML examples, but none that deal with XML returns from a procedure.

我很困惑，想要把它搞定，但不幸的是，我的google-fu没有帮上忙。我已经找到了大量的Oracle SQL|XML示例，但是没有一个示例处理过程返回的XML。

Note: I know that an alternate method of retrieving XML using DBMS methods exists, however, it's my understanding that that functionality is deprecated in favor of SQL|XML.

注意:我知道使用DBMS方法检索XML的另一种方法是存在的，但是，我的理解是，这个功能已经不支持SQL|XML。

3 个解决方案

#1

Your code includes the following :

您的代码包括以下内容:

u.USERID = USERID;

While you intend the bare USERID to be the procedure's parameter, Oracle actually gives preference to the USERID that is the column in the table. In effect it interprets it as

当您打算将裸USERID作为过程的参数时，Oracle实际上为表中的列USERID提供了首选项。实际上，它把它解释为。

u.USERID = u.USERID;

You can use

您可以使用

u.USERID = USPX_GetUserbyID.USERID;

but it is good practice to use a prefix for PL/SQL variables to avoid confusion. I tend towards v_ for variables and i_, o_, io_ for input, output and input/output parameters.

但是，为PL/SQL变量使用前缀以避免混淆是很好的做法。我倾向于v_ for变量和i_， o_， io_用于输入、输出和输入/输出参数。

#2

Your error has nothing to do with XML. In PL/SQL if you have a query which returns multiple rows, you must loop through the rows with a cursor. You have used the INTO keyword which can handle only a single row (or XML in your case) result.

您的错误与XML无关。在PL/SQL中，如果有一个返回多行的查询，则必须使用游标对行进行循环。您已经使用了INTO关键字，它只能处理单个行(在您的例子中是XML)结果。

#3

You query is apparently returning more than one row. With the way you have structured your procedure, you need to write your query in a way that it returns a single row containing all of the XML. Then you should not receive the error.

您的查询显然返回了不止一行。通过构建过程的方式，您需要以一种返回包含所有XML的一行的方式来编写查询。那么您不应该收到错误。

#1