时间戳

首页 > 解决方案 > 使用游标更新嵌套表 pl/sql

oracle - 使用游标更新嵌套表 pl/sql

问题描述

我有嵌套表

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

现在的问题是如何comm_amount使用游标更新列？

现在，我有这个，但我无法访问子表（委员会，有什么建议吗？

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

编辑

这是我的桌子的描述：

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

comm_array->12*times(comm_month, comm_amount)

我想在特定月份更新 comm_amount。

解决方案

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

标签： oracleplsql

解决方案

使用时几乎没有限制varrays。其中之一是当您DML对具有数据类型列的表进行操作时，varray如示例中所示。您可以使用 aNested table并实现您的要求，如下所示在我的演示中。但是请记住，嵌套表操作非常难以理解。请参阅下文并阅读内联注释。

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

--阻止更新记录

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

-- 你可以看到更新正在完成。

SQL> /

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

此外，正如评论中提到的，循环的使用看起来是多余的，该块可以进一步简化如下：

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;

编辑：

我如何更新每个员工，在这里您只选择一个名为“BBB”的员工。有办法吗？

正如我在评论中提到的，您可以使用它dynamic SQL来更新所有员工，如下所示：

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;

推荐阅读