在oracle sql的循环中选择？

niwlg2el 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(303)

我的存储过程用于从表中包含名称的字符串中获取以“，”分隔的ID的字符串值 AUTH_GROUPS .
例如：

id        name

1         role_1
2         role_2
3         role_3

输入为： 'role_1,role_2,role_3' 输出为： '1,2,3' ```
CREATE OR REPLACE PROCEDURE PROCEDURE1(P_ERROR_MSG OUT VARCHAR2, P_ROLE_STRING IN VARCHAR2 ) AS
BEGIN
DECLARE
lvOutPut VARCHAR2(2000);
vId varchar2(1000);
BEGIN
lvOutPut := '';
FOR i IN
(SELECT trim(regexp_substr(P_ROLE_STRING, '[^,]+', 1, LEVEL)) l
FROM dual
CONNECT BY LEVEL <= regexp_count(P_ROLE_STRING, ',')+1
)
LOOP
select id into vId from AUTH_GROUPS where NAME = i; --this line got error 'expression is of wrong type'
lvOutPut := lvOutPut || vId || ',';
END LOOP;
P_ERROR_MSG := lvOutPut;
P_ERROR_MSG := substr(P_ERROR_MSG, 1, LENGTH(P_ERROR_MSG) - 1);
END;
END PROCEDURE1;

但我评论的那行有个错误。我试过了 `i.1` 或者 `i.value` 但还是有错误。

sql oracle

来源：https://stackoverflow.com/questions/62532569/select-in-a-loop-in-oracle-sql

2条答案

按热度按时间

mjqavswn1#

您需要使用实际的列名。我是循环句柄名称。

....
BEGIN
    lvOutPut := '';
  FOR i IN
    (SELECT trim(regexp_substr(P_ROLE_STRING, '[^,]+', 1, LEVEL)) l -- this is column name to be used inside the loop
    FROM dual
    CONNECT BY LEVEL <= regexp_count(P_ROLE_STRING, ',')+1
    )
  LOOP
    select id into vId from AUTH_GROUPS where NAME = i.l;  -- change here
...

赞(0）回复(0）举报 2021-07-24

2g32fytz2#

/* Formatted on 6/23/2020 2:08:34 PM (QP5 v5.354) */
CREATE OR REPLACE PROCEDURE PROCEDURE1 (P_ERROR_MSG        OUT VARCHAR2,
                                        P_ROLE_STRING   IN     VARCHAR2)
AS
BEGIN
    DECLARE
        lvOutPut   VARCHAR2 (2000);
        vId        VARCHAR2 (1000);
    BEGIN
        lvOutPut := '';

        FOR i IN ( SELECT TRIM (REGEXP_SUBSTR (P_ROLE_STRING,
                                                  '[^,]+',
                                                  1,
                                                  LEVEL))    l
                        FROM DUAL
                  CONNECT BY LEVEL <= REGEXP_COUNT (P_ROLE_STRING, ',') + 1)
        LOOP
            SELECT id
              INTO vId
              FROM AUTH_GROUPS
             WHERE NAME = i.l; --this line got error 'expression is of wrong type'

            lvOutPut := lvOutPut || vId || ',';
        END LOOP;

        P_ERROR_MSG := lvOutPut;
        P_ERROR_MSG := SUBSTR (P_ERROR_MSG, 1, LENGTH (P_ERROR_MSG) - 1);
    END;
END PROCEDURE1;

赞(0）回复(0）举报 2021-07-24

我来回答

在oracle sql的循环中选择？

2条答案

相关问题

热门标签

最新问答