043 - [Oracle PL/SQL] Restrictions when calling functions

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

 

 

 

 

 

When a function is called from update/ delete , then then the function can not Query or modify database tables modified by that statement 

Error: mutating table

 

 

 

create or replace function get_sal_tax
    (p_sal number)
    return number
is

begin
    commit;-- 컴파일 오류는 없지만 함수호출시 오류발생함.
    
    if p_sal<5000 then
        return p_sal* (10/100);
    else
        return p_sal* (15/100);
    end if;

end;
--------
Function GET_SAL_TAX compiled

 

 

 

--you can not function in select if the function contain commit;rollbacl
select employee_id, first_name, salary, get_sal_tax(salary)
from employees
where get_sal_tax(salary)>2000
order by get_sal_tax(salary);
------------------------------------------------------------
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 
ORA-06512: at "HR.GET_SAL_TAX", line 7
14552. 00000 -  "cannot perform a DDL, commit or rollback inside a query or DML "
*Cause:    DDL operations like creation tables, views etc. and transaction
           control statements such as commit/rollback cannot be performed
           inside a query or a DML statement.
*Action:   Ensure that the offending operation is not performed or
           use autonomous transactions to perform the operation within
           the query/DML operation.

 

 

 

--but it will work like this
declare
    v number;
begin
    v:=get_sal_tax(5000);
    DBMS_OUTPUT.put_line(v);
end;
------------------------------------------------------------
750
PL/SQL procedure successfully completed.

 

 

 

create or replace function get_sal_tax
    (p_sal number)
    return number
is

begin

    insert into DEPARTMENTS(DEPARTMENT_ID,DEPARTMENT_NAME)
    values (-99,'test');

    if p_sal<5000 then
        return p_sal* (10/100);
    else
        return p_sal* (15/100);
    end if;
 
end;
------------------------------------------------------------
Function GET_SAL_TAX compiled

 

 

 

--you can not use function in select if the function contain dml
select employee_id, first_name, salary, get_sal_tax(salary)
from employees
where get_sal_tax(salary)>2000
order by get_sal_tax(salary);
------------------------------------------------------------
ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "HR.GET_SAL_TAX", line 8
14551. 00000 -  "cannot perform a DML operation inside a query "
*Cause:    DML operation like insert, update, delete or select-for-update
           cannot be performed inside a query or under a PDML slave.
*Action:   Ensure that the offending DML operation is not performed or
           use an autonomous transaction to perform the DML operation within
           the query or PDML slave.

 

 

 

--but it will work like this
declare
    v number;
begin
    v:=get_sal_tax(5000);
    DBMS_OUTPUT.put_line(v);
end;
------------------------------------------------------------
750
PL/SQL procedure successfully completed.




select * from  DEPARTMENTS
where DEPARTMENT_ID=-99;
------------------------------------------------------------
DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          -99 test

 

 

 

--you can drop function

drop function get_sal_tax;