How do I assign multiple variables based on other columns values from select (PL/SQL)-CodePudding

Hi I'm fairly new to this, wondering how to assign values to multiple variables from a select statement so that i can use them in 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;

The select query returns this:

DEPARTMENT          EMAILS                  

human resources     [email protected]                  
operations          [email protected]; [email protected]; [email protected]  
finance             [email protected]; [email protected]          
sales               [email protected]; [email protected]; [email protected]

It's expecting an INTO clause so I tried putting the LISTAGG part in a case statement to select into the variables based on department, but I can't get it to a point where I don't just get a syntax error.

EDIT: I should mention, not all offices have all 4 departments

My head hurts, thanks in advance for any help

CodePudding user response：

As mentioned in the comments it is hard to work out exactly what you want to do but I think you might be after a cursor.....

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;