К сожалению, большая часть моего опыта DB была с MSSQL, который имеет тенденцию содержать Ваш ручной намного больше, чем Oracle. То, что я пытаюсь сделать, довольно тривиально в tSQL, однако, pl/sql дает мне головную боль.
У меня есть следующая процедура:
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>
Когда я выполняю сам запрос, заменяя параметр ИДЕНТИФИКАТОРА ПОЛЬЗОВАТЕЛЯ строкой и удаляя "в" пункт, запрос хорошо работает и возвращает ожидаемую структуру.
Однако, когда процедура пытается выполнить запрос, передавая результаты функции 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 из процедуры.
Примечание: Я знаю, что альтернативный метод получения XML, использование методов DBMS существует, однако, именно мое понимание, что функциональность удерживается от использования в пользу SQL|XML.
Ваш код включает следующее:
u.USERID = USERID;
Хотя вы предполагаете, что пустой USERID должен быть параметром процедуры, Oracle фактически отдает предпочтение USERID, который является столбцом в таблице. Фактически он интерпретирует его как
u.USERID = u.USERID;
. Вы можете использовать
u.USERID = USPX_GetUserbyID.USERID;
, но рекомендуется использовать префикс для переменных PL / SQL, чтобы избежать путаницы. Я склоняюсь к v_ для переменных и i_, o_, io_ для входных, выходных и входных / выходных параметров.
Ваша ошибка не имеет отношения к XML. В PL / SQL, если у вас есть запрос, который возвращает несколько строк, вы должны перебирать строки с помощью курсора. Вы использовали ключевое слово INTO, которое может обрабатывать только одну строку (или XML в вашем случае) результат.
Очевидно, ваш запрос возвращает более одной строки. С учетом того, как вы структурировали свою процедуру, вам нужно написать свой запрос таким образом, чтобы он возвращал одну строку, содержащую весь XML. Тогда вы не должны получать ошибку.