我正在尝试更新 Oracle 包中的存储过程。最初,SP 被设置为假设将单个项目 id 传递给它,但现在有可能以逗号分隔字符串的形式传递一组项目 id。
我正在尝试将此字符串拆分为一组字符串并循环遍历它。我设法拆分了字符串,但似乎无法将其分配给可用于循环的变量。
PROCEDURE USPGETSOMTHING
(
IPSITEMIDS VARCHAR2,
....,
CUR_OUT OUT GETDATACURSOR
)
IS
...;
V_NEWITEM VARCHAR2(4000);
V_NEWITEMLIST VARCHAR2(4000);
-- Tried declaring an array; not good!
type array_t is table of varchar(20);
V_NEWITEMLIST array_t;
BEGIN
-- string passed to SP is in the form of "'LP060500105','EM060500103'"
V_NEWITEM := REPLACE(IPSITEMIDS, '''', '');
-- This comma-separated string will actually be V_NEWITEM
SELECT REGEXP_SUBSTR('LP060500105,EM060500103', '[^,]+', 1, LEVEL) INTO V_NEWITEMLIST FROM DUAL
CONNECT BY instr('LP060500105,EM060500103', ',',1, LEVEL-1) > 0;
以上不起作用,我得到:
ORA-01422: exact fetch returns more than requested number of rows
我需要解决的下一个问题是在数据表中返回结果。
目前,我使用:
IF (...)
....
LVSQUERY:='SELECT '''|| V_NEWITEM ||''' AS ITEMID, '''|| V_OUTCOME ||''' AS OUTCOME FROM DUAL';
END IF;
OPEN CUR_OUT FOR LVSQUERY;
我如何确保结果数据表的行数与项目 ID 的数量一样多,这是拆分项目 ID 字符串的结果?
您可以尝试使用 CURSOR 并遍历记录。
PROCEDURE USPGETSOMTHING
(
IPSITEMIDS VARCHAR2,
....,
CUR_OUT OUT GETDATACURSOR
)
IS
...;
V_NEWITEM VARCHAR2(4000);
V_NEWITEMLIST VARCHAR2(4000);
CURSOR cur IS
SELECT REGEXP_SUBSTR('LP060500105,EM060500103', '[^,]+', 1, LEVEL) V_NEWITEMLIST
FROM DUAL
CONNECT BY instr('LP060500105,EM060500103', ',',1, LEVEL-1) > 0;
-- Tried declaring an array; not good!
type array_t is table of varchar(20);
V_NEWITEMLIST array_t;
BEGIN
-- string passed to SP is in the form of "'LP060500105','EM060500103'"
V_NEWITEM := REPLACE(IPSITEMIDS, '''', '');
FOR rec IN cur LOOP
--- whatever you want to do referencing the field in the cursor by rec.V_NEWITEMLIST
END LOOP;
你也可以试试 OPEN ... FOR
PROCEDURE uspgetsomthing
( IPSITEMIDS VARCHAR2,
CUR_OUT OUT SYS_REFCURSOR
)
IS
BEGIN
OPEN CUR_OUT FOR
SELECT REGEXP_SUBSTR('LP060500105,EM060500103', '[^,]+', 1, LEVEL) V_NEWITEMLIST
FROM DUAL
CONNECT BY instr('LP060500105,EM060500103', ',',1, LEVEL-1) > 0;
END; -- Procedure
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句