How can i store results of a query in an array?-CodePudding

I have to store last 5 employees_id's from employees table into an array. I made the query correct and i have the array, but i can't understand the syntax to store the results in that array. Here's my code

    type tip_cod IS VARRAY(20) OF NUMBER;
    coduri tip_cod := tip_cod(6);

and here's my query

SELECT employee_id FROM (
SELECT employee_id from employees
where commission_pct IS NULL
order by salary asc)
WHERE ROWNUM < 6;

How can i store the results from the query in the array? As i need to update those values in another query. I know this can be done in a simpler way with subqueries but i'm doing it this way to better understand the concept of arrays in sql

CodePudding user response：

Assuming you are using Oracle then you can use a PL/SQL block:

DECLARE
  TYPE tip_cod IS VARRAY(20) OF NUMBER;
  coduri tip_cod;
BEGIN
  SELECT employee_id
  BULK COLLECT INTO coduri
  FROM (
    SELECT employee_id
    from   employees
    where  commission_pct IS NULL
    order by salary asc
  )
  WHERE ROWNUM < 6;

  -- Do something with coduri
  FOR i IN 1 .. coduri.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(coduri(i));
  END LOOP;
END;
/

db<>fiddle here