use values stored in varray sql-oracle?-CodePudding

I am trying to get my last 5 employees (ones with lowest salary) and raise their salary by 5%; I am using a varray to store their id's but i don't know how to use those ids in a update statement (something like update employees \ set salary = salary * 1.05 \ where id_employee in varray)

here's what i have for now:

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;

  -- after i store their ids in coduri i want to update their salary
  FOR i IN 1 .. coduri.COUNT LOOP
    
    DBMS_OUTPUT.PUT_LINE(coduri(i));
    
    
  END LOOP;

END;
/

CodePudding user response：

If you are practicing the use of loops to do things one at a time (not a good approach for this task!) you can replace your calls to put_line with insert statements, something like

...
  update employees set salary = 1.05 * salary where employee_id = coduri(i);
...

The beauty of PL/SQL is that you can embed such plain-SQL statements directly within PL/SQL code, no need for preparation of any kind.

After you are done with the updates, you will need to commit for the changes to be committed - usually after the procedure, not within it.

Alternatively, if you want a single update (with an in condition), you will need to define the varray table at the schema level, not within the anonymous block (or procedure). This is because the update statement is a SQL statement, which can't "see" locally defined data types. Then, in the update statement you will need to use the table operator to unwind its members. Something like this:

create type tip_cod is varray(20) of number;
/

DECLARE
  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;

update employees set salary = 1.05 * salary
where employee_id in (select * from table(coduri));
END;
/

commit;

Notice how the varray type is defined on its own, then it is used in the PL/SQL block. Also don't forget the commit at the end.

When you work with collection types, there is also the member of predicate, as in employee_id member of coduri. Alas, this only works with locally-defined data types; since the varray type must be declared at the schema level (so that it can be used in a SQL statement within the PL/SQL code), you can't use member of and you must unwind the array explicitly, with the table operator.

CodePudding user response：

There id much more to collections (Oracle term for array). There are 3 types:

Varrays
Associative Arrays
Nested Tables

If you want to understand collections you must understand all 3. (imho: Of the 3 Varrays are the most limited).

Mathguy presents 1 option, "casting" the array as a table, via the TABLE(...) function. I'll present another: Nested Table combined with Bulk Collect/Forall combination to accomplish he update.

declare
    type employee_id_att is table of hr.employees.employee_id%type; 
    employee_id_array employee_id_att; 
    
begin
    select employee_id 
      bulk collect 
      into employee_id_array
      from hr.employees
     where commission_pct is null
     order by salary
     fetch first 5 rows only; 
     
     forall emp_indx in 1 ..  employee_id_array.count
        update hr.employees 
           set salary = 1.05 * salary
         where employee_id = employee_id_array(emp_indx);
         
end ; 
/

Take Away: There is much, much more to collections than defining a LOOP. Spend some time with the documentation and write tests and examine the results. But the important thing when you do not understand write some code. It will probably fail, that is good, so write something else. Do not be afraid of errors/ exceptions, in development they are friend. And if there is something you cannot understand then post a specific question. Be prepared to show several failed attempts; that will give the community an idea of your thinking and whether you are on the correct path or not.