我有以下程序:
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_表示输入,输出和输入/输出参数.