Plsql oracle apex

Question

我在过程中使用此循环并在i中使用电子邮件ID，但是当我试图运行时，我没有发现数据错误，所以我想检查存储在中的值我

使用的代码：

  for i in ( select EMAIL
              into l_user_mail 
              from employee A CONNECT BY PRIOR lower(EMAIL) = lower(MANAGER_EMAIL) 
                                START WITH lower(GUID) in  (select replace(lower(group_name),'_org_slack') 
                                                              from dynamic_group where id = '81')
           ) loop
    l_user_mail:=l_user_mail || i.EMAIL;
  end loop;

如何在sql命令提示中检查i的返回值。

我想看到i

Answer 1

我想看到i

中的值

您的FOR LOOP语法不正确。INTO子句不应在For Loop中使用。请参阅下面如何做到这一点。

 FOR I IN
          (SELECT     EMAIL
                 FROM EMPLOYEE A
           CONNECT BY PRIOR LOWER (EMAIL) = LOWER (MANAGER_EMAIL)
           START WITH LOWER (GUID) IN (
                             SELECT REPLACE (LOWER (GROUP_NAME), '_org_slack')
                               FROM DYNAMIC_GROUP
                              WHERE ID = '81') )
     LOOP
          -- l_user_mail:=:P60_IDP_GROUPS;
          L_USER_MAIL := L_USER_MAIL || I.EMAIL;
          -- To display value of I 
          DBMS_OUTPUT.PUT_LINE (I.EMAIL);
     END LOOP;

演示：

SQL> DECLARE
  2       L_USER_MAIL                   VARCHAR2 (10);
  3  BEGIN
  4       FOR I IN (SELECT     LEVEL
  5                       FROM DUAL
  6                 CONNECT BY LEVEL < 10)
  7       LOOP
  8            -- l_user_mail:=:P60_IDP_GROUPS;
  9            L_USER_MAIL := L_USER_MAIL || I.level;
 10            DBMS_OUTPUT.PUT_LINE (I.level);
 11       END LOOP;
 12  END;
 13  /
1
2
3
4
5
6
7
8
9
PL/SQL procedure successfully completed.