User defined function can only have select statements

Question

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?

• Sathiya Kumar V M: User-defined functions cannot be used to perform actions that modify the database state. You can check this link for detailed answer. stackoverflow.com/questions/6150888/…
• Aaron Bertrand: @SathiyaKumar This isn't about SQL Server, though.
• Sathiya Kumar V M: @AaronBertrand yup. Not only noticed the tags ;-)

Answer 1

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.

• Andriy M: Your last statement still begs another "Why?". And I think the answer to that "why" would work as the answer to the OP's "why" too, even though the OP was incorrect about permissibility of DML in functions. Just saying. I like the answer anyway, learned something new.
• Balazs Papp: @AndriyM I find it a very bad idea to create code with unexpected side effects. If I run a query to just read data, I expect it to query data, and not modify something else in the background. Yes, I know, auditing, block cleanout and other mechanisnms can cause unexcpected writes on reads as well...
• Andriy M: Oh, you don't need to tell me, because my position on this is exactly the same as yours. It's shaping it into words that I have trouble with, otherwise I would've posted my own answer or edited yours :)