RE: Why the stored functions can not be called from DML Statements? Why cant we modify the database by function?
>>Why the stored functions can not be called from DML Statements? We can call a stored function from dml statement.
Here is simple example
CREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER IS
BEGIN
RETURN(10);
END;
SELECT TEST_1 FROM DUAL;
update emp
set sal = sal+ sal*test_1();
To be called from an sql statement a function should follow the following purity rules. 1) When called from a SELECT statement , the function should not modify database objects. 2) When called from INSERT , UPDATE, DELETE statements , the function can not query or modify the tables that was modified by the statement. 3)When called from SELECT , DELETE, UPDATE, INSERT Statements , a function should not contain TCL, DDL commands.
>>Why cant we modify the database by function
The main purpose of a function is to compute a value. Generally we use procedures to make modifications in database. If you want you can do modifications using functions also. But you can't call such functions from DML statements.
