User defined function can only have select statements

functionsoracleplsqlstored-procedures

One of the main differences between UDF and SP is that UDF can only have select statements inside it and not insert/update/delete statements. Can someone please explain the reason behind this? The below function:

create function test(..)
...
BEGIN 
insert into EMPLOYEE('22',12000,'john');
return 0;
END

is not valid. But why is this so?

Best Answer

This is not about function (UDF) vs procedure. It is the context where you use them.

You can have DML operations in a function:

SQL> create table t1 (c1 number);

Table created.

SQL> create or replace function f1 return number as
  2  begin
  3    insert into t1 values (1);
  4    commit;
  5    return 0;
  6  end;
  7  /

Function created.

SQL> declare
  2    i number;
  3  begin
  4    i := f1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from t1;

        C1
----------
         1

But if you use your function as and UDF in a query:

SQL> select f1 from dual;
select f1 from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "BP.F1", line 3

You can still work around this with an autonomous transaction:

SQL> create or replace function f2 return number as
  2    pragma autonomous_transaction;
  3  begin
  4    insert into t1 values (1);
  5    commit;
  6    return 0;
  7  end;
  8  /

Function created.

SQL> select f2 from dual;

        F2
----------
         0

SQL> select * from t1;

        C1
----------
         1
         1

But should you do this? I do not think so.

Related Question