如何将 PL/SQL 本地定义的类型与 TABLE 运算符一起使用

Question

我正在开发一个触发器，可以更新一个记录部门及其工资总额的表格。每次更新 EMP 表时。触发器将更新此表。为了避免突变表的错误，我实现了"复合触发器"类型的触发器。

在这里，我把你的实现：

CREATE OR REPLACE TRIGGER EJERCICIO_27 
FOR INSERT OR UPDATE OR DELETE ON EMP
COMPOUND TRIGGER
TYPE t_DeptToUpdate IS TABLE OF NUMBER(2,0);
deptToUpdate t_DeptToUpdate;
-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN
IF NOT :OLD.DEPTNO MEMBER OF deptToUpdate THEN
deptToUpdate.EXTEND;
deptToUpdate(deptToUpdate.LAST) := :OLD.DEPTNO;
END IF;
END AFTER EACH ROW;
-- AFTER STATEMENT Section:
AFTER STATEMENT IS
BEGIN
DBMS_OUTPUT.PUT_LINE('SE ACTUALIZAN UN TOTAL DE: ' || deptToUpdate.COUNT);
MERGE INTO SALARIO_DEPARTAMENTOS SD USING (
SELECT DEPTNO, NVL(SUM(SAL), 0) AS SUM_TOTAL FROM EMP
WHERE DEPTNO IN (SELECT * FROM TABLE(deptToUpdate))
GROUP BY DEPTNO
) D
ON (SD.DEPTNO = D.DEPTNO)
WHEN MATCHED THEN 
UPDATE SET
SAL_TOT = D.SUM_TOTAL
WHERE SD.DEPTNO = D.DEPTNO
WHEN NOT MATCHED THEN 
INSERT (DEPTNO, SAL_TOT)
VALUES(D.DEPTNO, D.SUM_TOTAL);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR : ' || SQLCODE || 'MENSAJE: ' || SQLERRM);
END AFTER STATEMENT;
END;
/

使用集合作为查询 MERGE 语句的源时遇到的问题。我知道在Oracle 12c(我使用的是Oracle 12c R2 Enterprise(中，我可以将TABLE运算符与本地定义的类型一起使用。如本文所述，在PL/SQL中使用具有本地定义类型的TABLE运算符。

编译器返回我的错误是：

LINE/COL ERROR
-------- -----------------------------------------------------------------
20/5     PL/SQL: SQL Statement ignored
22/40    PL/SQL: ORA-22905: no se puede acceder a las filas de un elemento de tabla no anidada
22/46    PLS-00642: tipos de recopilación local no permitidos en sentencias SQL

有人可以告诉我使用什么方法，而无需在原理图中创建任何类型？提前谢谢。

编辑

错误消息的翻译：

ORA-22905: Rows of a non-nested table element can not be accessed
PLS-00642: Local collection types not allowed in SQL statements

Answer 1

是否需要使用集合？如果没有，那么似乎一个简单的行级触发器应该可以工作，如下所示：

Create Or Replace Trigger emp_trig
After Insert Or Update Or Delete On emp
For Each Row
Begin
-- Subtract old salary from old department
Update department_salary ds
Set ds.tot_salary = ds.tot_salary - :old.salary
Where ds.dept =  :old.dept;
If Inserting Or Updating Then
-- Add new salary to new department
Update department_salary ds
Set ds.tot_salary = ds.tot_salary + :new.salary
Where ds.dept =  :new.dept;
If SQL%Rowcount = 0 Then
Insert Into department_salary  (dept, tot_salary) Values(:new.dept, :new.salary);
End If;
End If;
End;
/

旧值和新值的单独更新语句处理更新行可能包含已更改部门编号的情况。

Answer 2

正如LauDec所评论的那样，维护部门列表和总工资的一种非常简单的方法是这样：

Create Or Replace View salario_departamentos_view As
Select deptno, Sum(sal) As sal_tot From emp
Group By deptno;

这个解决方案的美妙之处在于，数据始终与EMP表完美协调，并且非常容易理解，即使对于SQL技能最少的人来说也是如此。