如何根据select (PL/SQL)中的其他列值分配多个变量

Question

嗨，我对此相当陌生，想知道如何从选择语句中分配值给多个变量，以便我可以在apex中使用它们。

DECLARE
emails_hr varchar2(2000);
emails_ops varchar2(2000);
emails_finance varchar2(2000);
emails_sales varchar2(2000);
BEGIN
SELECT
DEPARTMENT,
LISTAGG(lower(EMAIL), '; ') WITHIN GROUP (ORDER BY DISPLAY_AREA) as EMAILS
FROM (
select
DEPARTMENT,
EMAIL 
from CONTACT_TABLE
where EMAIL is not NULL
and OFFICE_ID = 100
)
GROUP BY DEPARTMENT;
END;

选择查询返回如下:

DEPARTMENT          EMAILS                  
human resources     [e-mail]                  
operations          [e-mail]; [e-mail]; [e-mail]  
finance             [e-mail]; [e-mail]          
sales               [e-mail]; [e-mail]; [e-mail]  

它期待一个INTO子句，所以我试着把LISTAGG部分放在一个case语句中，根据部门选择变量，但我不能让它到一个点，我不只是得到一个语法错误。

编辑:我应该提一下，不是所有的办公室都有四个部门

我头疼，提前感谢你的帮助

Answer 1

正如评论中提到的，很难确定你想要做什么但我认为你可能在光标之后.....

DECLARE
emails_hr varchar2(2000);
emails_ops varchar2(2000);
emails_finance varchar2(2000);
emails_sales varchar2(2000);
CURSOR C_DEPARTMENTS IS       
SELECT DEPARTMENT,
LISTAGG(lower(EMAIL), '; ') WITHIN GROUP (ORDER BY DISPLAY_AREA) as EMAILS
FROM (SELECT DEPARTMENT, EMAIL 
FROM  CONTACT_TABLE
WHERE EMAIL is not NULL
AND   OFFICE_ID = 100
)
GROUP BY DEPARTMENT;
BEGIN
FOR v_row in c_departments LOOP
-- do whatever you want in here eg
dbms_output.put_line('department = ' || v_row.department);
dbms_output.put_line('emails = ' || v_row.emails);
END LOOP;
END;