Как выполнить функцию, которая возвращает таблицу и при этом удаляет данные из другой таблицы?

Question

[Даниил Мирошниченко]

Надо сделать функцию удаления всех сотрудников из заданного подразделения. Возвращаемое значение - список удаленных сотрудников в формате: код сотрудника, имя сотрудника.

Я написал эту функцию. На вход передается название отделения. Создается курсор, с помощью которого я получу всех сотрудников, работающих в этом отделении. Потом в цикле for я удаляю сотрудников из таблицы.

CREATE TYPE EMP_ROW AS OBJECT (
		EMP_ID NUMBER,
		EMP_NAME VARCHAR2(150)
);
/

CREATE TYPE EMP_TAB AS TABLE OF EMP_ROW;
/
CREATE OR REPLACE FUNCTION DROP_EMPS (DEPT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%TYPE) 
RETURN EMP_TAB PIPELINED AS   
	CURSOR C_EMPS IS
	    SELECT E.EMPLOYEE_ID, (E.FIRST_NAME||' '||E.LAST_NAME) EMP_NAME
	    FROM EMPLOYEES E
	    WHERE E.DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE D.DEPARTMENT_NAME = DEPT_NAME)
      FOR UPDATE;

	BEGIN
		FOR EMP IN C_EMPS LOOP
     
			PIPE ROW(EMP_ROW(EMP.EMPLOYEE_ID, EMP.EMP_NAME)); 
      
      DELETE FROM EMPLOYEES     
      WHERE CURRENT OF C_EMPS;      
      
		END LOOP;       
    RETURN;
    
	END DROP_EMPS;
/

Пытаюсь запустить:
SELECT * FROM TABLE(DROP_EMPS('Administration'));

Выводится ошибка:

ORA-14551: cannot perform a DML operation inside a query

Я так понял, что нельзя выполнить удаление из таблицы в SELECT-запросе...

Если убрать удаление записей в функции, то работает нормально, т.е. проблема именно в этом.

Answer 1

[Даниил Мирошниченко]

Вообщем выкрутился из этой ситуации...
Вместо конвейерной табличной функции я использовал просто табличную функцию. Возвращал полученное значение в соответствующий тип переменной в блоке pl/sql, а потом через цикл for выводил на экран полученные данные.

Код функции:

CREATE OR REPLACE FUNCTION DROP_EMPS (DEPT_NAME IN DEPARTMENTS.DEPARTMENT_NAME%TYPE) 
	RETURN EMP_TAB AS  	

		CURSOR C_EMPS IS
		    SELECT E.EMPLOYEE_ID, (E.FIRST_NAME||' '||E.LAST_NAME) EMP_NAME
		    FROM EMPLOYEES E
		    WHERE E.DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE D.DEPARTMENT_NAME = DEPT_NAME)
	      FOR UPDATE;

	    CURSOR C_DEPTS IS
	    	SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE D.DEPARTMENT_NAME = DEPT_NAME;

	    V_TMP_DEPT_ID DEPARTMENTS.DEPARTMENT_ID%TYPE;

	    EMPS EMP_TAB := EMP_TAB();    

	    MISSING_DEPT EXCEPTION;

		BEGIN
			OPEN C_DEPTS;
			FETCH C_DEPTS INTO V_TMP_DEPT_ID;

			IF C_DEPTS%NOTFOUND THEN
				RAISE MISSING_DEPT;
			END IF;
	    
	    CLOSE C_DEPTS;

			FOR EMP IN C_EMPS LOOP			
		        UPDATE EMPLOYEES
		        SET MANAGER_ID = NULL
		        WHERE MANAGER_ID = EMP.EMPLOYEE_ID;
		        
		        DELETE FROM JOB_HISTORY
		        WHERE EMPLOYEE_ID = EMP.EMPLOYEE_ID;
		        
		        EMPS.EXTEND;
		        EMPS(EMPS.LAST) := EMP_ROW(EMP.EMPLOYEE_ID, EMP.EMP_NAME);
	        
		        DELETE FROM EMPLOYEES     
		        WHERE CURRENT OF C_EMPS;      	      
			END LOOP;       
	    RETURN EMPS;

	    EXCEPTION
	    	WHEN MISSING_DEPT THEN
	    		RAISE_APPLICATION_ERROR(-20005, 'MISSING DEPARTMENT');     
	    
		END DROP_EMPS;
END PKG_EMP;

Вызов функции:

SET SERVEROUTPUT ON
DECLARE
  DELETED_EMPS EMP_TAB;
  V_EMP_ID NUMBER;
BEGIN
  DBMS_OUTPUT.enable;
  DELETED_EMPS := DROP_EMPS('IT');
  FOR I IN 1..DELETED_EMPS.COUNT LOOP
    DBMS_OUTPUT.put_line(DELETED_EMPS(I).EMP_ID||' '||DELETED_EMPS(I).EMP_NAME);
  END LOOP;      
END;
/

Answer 2

[parikLS]

Чтобы удалить данные из таблицы используя вызов функции в селекте, эта функция должна быть скомпилирована с указанием прагмы автономной транзакции. Если вы хотите возвращать таблицу, вы можете использовать предложение returning для получения удаленных записей, например

CREATE PACKAGE PKG_EMP 
IS
TYPE emp_rec IS RECORD (id emp.id%TYPE,
                       name emp.name%TYPE);

TYPE emp_nt IS TABLE OF emp_rec;

FUNCTION delete_emp (in_deptid IN department.id%TYPE) RETURN emp_nt PIPELINED;
END;
/
CREATE PACKAGE BODY PKG_EMP IS
FUNCTION delete_emp (in_deptid IN department.id%TYPE) RETURN emp_nt PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_return_emp emp_nt;
BEGIN
    BEGIN
        DELETE FROM EMP
        WHERE deptid = in_deptid
        RETURNING id, name BULK COLLECT INTO v_return_emp;
            COMMIT;
        EXCEPTION WHEN OTHERS THEN 
            ROLLBACK;
    END;
    
    FOR indx IN 1 .. v_return_emp.COUNT
        LOOP
            PIPE ROW (v_return_emp(indx));
        END LOOP;
    RETURN;
END;
END;
/
SELECT * FROM TABLE (PKG_EMP.DELETE_EMP (10) )