Skip to content
Advertisement

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

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

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement