CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqluser-defined-functions
ghostrider
ghostrider

Reputation: 2238

User defined function can only have select statements

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?

Upvotes: 0

Views: 2051

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

The insert statement inside your function is missing the values keyword;

insert into EMPLOYEE('22',12000,'john');

should be

insert into EMPLOYEE values ('22',12000,'john');

though it's better to include the list of column names too. From the small part of the code you showed that is the only thing that is invalid. There could be other errors in the bits you have omitted. (If the first column in your table is numeric then you shouldn't be passing a string - it works but does implicit conversion and is best avoided. And if the column is a string, should it be really?)

UDF can only have select statements inside it and not insert/update/delete statements

That is not correct. You can have DML (insert/update/delete) in a function, but you can only call it from a PL/SQL context (though even in PL/SQL, it's often said that functions should query data with no side effects and only procedures should modify data; but that is not restricted by the language itself):

create table employee (id varchar2(3), salary number, name varchar2(10));

Table EMPLOYEE created.

create function test(unused number)
return number as
BEGIN 
  insert into EMPLOYEE (id, salary, name)
  values ('22',12000,'john');
  return 0;
END;
/

Function TEST compiled


declare
  rc number;
begin
  rc := test(42);
end;
/

PL/SQL procedure successfully completed.

select * from employee;

ID      SALARY NAME      
--- ---------- ----------
22       12000 john

But you cannot call it from a SQL context:

select test(42) from dual;

ORA-14551: cannot perform a DML operation inside a query 
ORA-06512: at "MYSCHEMA.TEST", line 4

The documentation lists restrictions on functions called from SQL, and goes into more detail in this warning:

Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language.

If the function was allowed to do DML then you would have no control over how many times that DML was performed. If it was doing an insert, for instance, it might try to insert the same row twice and either duplicate data or get a constraint violation.

Upvotes: 6

William Robertson
William Robertson

Reputation: 16001

Just to summarise the comments, you can have DML inside a PL/SQL function.

What you can't do is call that function from SQL, because a select statement shouldn't also apply updates and deletes and so on as a hidden side effect.

For one thing, the SQL language reserves the right to execute the query any way it chooses, in any order, and with any caching it decides make use of. (It might even stop and restart during execution. That's up to the SQL engine.) Therefore your function might get called once or a hundred times, in any order, depending on the execution plan, and so the results would be unpredictable.

Upvotes: 2

Related Questions