Using Cursor for updating nested tables pl/sql

user_10630165 0
Viewed 492

I am having nested table 

create or replace type comm_type as object
 (comm_month varchar(5),
 comm_amount  number); 

create or replace TYPE comm_array AS VARRAY(12) OF comm_type;

alter table emp2 add commission comm_array

Now the question is how I can update comm_amount column using a cursor?

For now, I have this, but I cannot get the access to the sub table (commission, any suggestions?

DECLARE 
  CURSOR c_comm_amount_cursor IS 
   select c.comm_amount
    from emp2 e, table (e.commission) c
    where c.comm_month = 'DEC' for update of c.comm_month nowait;
BEGIN
   FOR emp_record IN c_comm_amount_cursor LOOP
    UPDATE emp2
     SET  emp2.commission.comm_amount = emp2.commission.comm_amount + 100
     WHERE CURRENT OF c_emp_cursor;
  END LOOP;
END;
/

EDIT

Here is the desc of my table:

Name       Null?    Type         
---------- -------- ------------ 
EMPNO      NOT NULL NUMBER(4)    
ENAME               VARCHAR2(10)    
BONUS               NUMBER       
COMMISSION          COMM_ARRAY

comm_array->12*times(comm_month, comm_amount)

ANd I want to update comm_amount in a specific month.

SOLUTION

DECLARE 
  CURSOR c_comm_amount_cursor IS 
   select c.comm_amount,c.comm_month, e.empno
    from emp2 e, table (e.commission) c
    where c.comm_month = 'DEC'for update of c.comm_month nowait;

BEGIN
  FOR emp_record IN c_comm_amount_cursor 
  LOOP
     UPDATE table(Select commission from emp2 where empno = emp_record.empno) e 
     SET  e.comm_amount = e.comm_amount + 100
     WHERE CURRENT OF c_comm_amount_cursor;
 END LOOP;
END;
/
plsql oracle
2 Answers

There are few limitation when you use varrays. One of them is when you do DML operations on table having columns of datatype varray like shown in your example. You can use a Nested table and achieve your requirement as shown below in my demo. However please bear in mind the nested table operations are quite complicated to understand. See below and read inline comments.

--Created Table emp2 with an additional column
CREATE TABLE emp2 (ename VARCHAR2(10));

--Object 
CREATE OR REPLACE TYPE COMM_TYPE AS OBJECT
 (COMM_MONTH VARCHAR(5),
 COMM_AMOUNT  NUMBER); 

--Created a Table of object rather then varray.
CREATE OR REPLACE  TYPE COMM_ARRAY AS TABLE OF COMM_TYPE;

--Modified table emp2. Added column commission as shown in your example
ALTER  TABLE EMP2 ADD COMMISSION COMM_ARRAY NESTED TABLE COMMISSION STORE AS TBA1;

--Inserted records
INSERT INTO EMP2 VALUES('AAA',COMM_ARRAY(COMM_TYPE('NOV',100)));
INSERT INTO EMP2 VALUES('BBB',COMM_ARRAY(COMM_TYPE('DEC',200)));

--Selected Records
SQL> SELECT C.COMM_AMOUNT,C.COMM_MONTH
  2      FROM EMP2 E, TABLE (E.COMMISSION) C
  3      WHERE C.COMM_MONTH = 'DEC';

COMM_AMOUNT COMM_
----------- -----
        200 DEC

--Block to Update records

DECLARE 
  CURSOR c_comm_amount_cursor IS 
   select c.comm_amount,c.comm_month
    from emp2 e, table (e.commission) c
    where c.comm_month = 'DEC'for update of c.comm_month nowait;
BEGIN
  FOR emp_record IN c_comm_amount_cursor 
  LOOP
     --With the help of table operator you can update records of a nested table but not varray.
     UPDATE table( Select commission from emp2 where ename = 'BBB') e --<--Make sure to use additional column of the table to make unique record selection for update
     SET  e.comm_amount = e.comm_amount + 100    
     WHERE CURRENT OF c_comm_amount_cursor;
 END LOOP;
 COMMIT;
END;
/

-- You can see update being done.

SQL> /

COMM_AMOUNT COMM_
----------- -----
        300 DEC

Also, as mentioned in comments, the use of loop looks redundant and the block can be further simplified as below:

BEGIN
     --With the help of table operator you can update records of a nested table but not varray.
 UPDATE table( Select commission from emp2 where ename = 'BBB') e --<--Make sure to use additional column of the table to make unique record selection for update
   SET  e.comm_amount = e.comm_amount + 100    
   WHERE  e.comm_month ='DEC';

 COMMIT;
END;

EDIT:

how can I update every employee, here you choose only one with name 'BBB'. Is there a way?

As mentioned in my comments you need can use dynamic SQL to update all the employees as show below: 

DECLARE 
v_sql varchar2(2000);
CURSOR c_enme_cursor IS 
   select ename
    from emp2;
BEGIN
FOR emp_recd IN c_enme_cursor 
LOOP
 v_sql:=q'[
           UPDATE table( Select commission from emp2 where ename = ']'||emp_recd.ename||q'[') e 
             SET  e.comm_amount = e.comm_amount + 100    
            -- WHERE  e.comm_month ='DEC'
          ]';
  EXECUTE IMMEDIATE V_SQL;          
END LOOP;
COMMIT;
END;
user_65295370

Hi the fast answer is:

DECLARE
   CURSOR C_COMM_AMOUNT_CURSOR
   IS
      SELECT ROWID ROW_ID
      FROM EMP2 E
      WHERE E.COMM_MONTH = 'DEC';
BEGIN
   FOR EMP_RECORD IN C_COMM_AMOUNT_CURSOR
   LOOP
      UPDATE TABLE (
                    SELECT COMMISSION
                    FROM EMP2
                    WHERE ROWID = ROW_ID
                   )
      SET COMM_AMOUNT = COMM_AMOUNT + 100
      WHERE ;--your where clause condition
   END LOOP;
END;

you should know when working with nested tables you have to use TABLE() around your nested table in SQL scripts, in PL/SQL it is bit more complicated and you have to create a nested object run time and fill it with your data and manipulate it and later save it on your table which is too far, I have experienced this problem and using ROWID was more readable.

user_15076690
Related