CODEWITHSUNDEEP
oracle-databaseplsql
Sergio Sánchez Sánchez
Sergio Sánchez Sánchez

Reputation: 1836

How using PL/SQL Locally Defined Types with TABLE Operator

I am developing a trigger that keeps a table updated where the departments are recorded and the total sum of their salaries. Each time the EMP table is updated. The trigger updates this table. To avoid the error of mutant tables, I have implemented a "COMPOUND TRIGGER" type trigger.

Here I put your implementation:

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

The problem I have when using the collection as a source for querying the MERGE statement. I understood that in Oracle 12c (I am using Oracle 12c R2 Enterprise) I could use the TABLE operator with locally defined types. As discussed in this post Using the TABLE Operator with Locally Defined Types in PL/SQL.

Exactly the errors that the compiler returns me are:

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

Someone can tell me what approach to use without having to create any type in the schematic?. Thanks in advance.

EDIT

Translation of error messages:

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

Upvotes: 1

Views: 773

Answers (2)

Kirby
Kirby

Reputation: 724

Are you required to use a collection? If not, then it seems that a simple row-level trigger should work, something like this:

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

The separate update statements for old and new values handle cases when an updated row might contain a changed department number.

Upvotes: 1

Kirby
Kirby

Reputation: 724

As LauDec commented, a very straightforward way to maintain a list of departments and total salary would be with a view such as this:

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

The beauty of this solution is that the data is always in perfect harmony with the EMP table, and it's very easy to understand, even for someone with minimal SQL skills.

Upvotes: 1

Related Questions