从 Oracle 存储过程返回 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2512028/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
XML Return from an Oracle Stored Procedure
提问by Tequila Jinx
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。
回答by Gary Myers
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_ 用于变量和 i_、o_、io_ 用于输入、输出和输入/输出参数。
回答by dacracot
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）结果。
回答by Doug Porter
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 的单行的方式编写查询。那么你不应该收到错误。
