Which do you mean? Function or procedure?
In any case the answer is the same for both.
DMLYou can have (static or dynamic) DML in a function:
create table t (
x int
);
create or replace function f
return int as
update_count int;
begin
insert into t values (1);
update t set x = x + 1
return count(*) into update_count;
return update_count;
end;
/
var v number;
exec :v := f;
select * from t;
X
----------
2
Though we recommend that functions are free from side-effects. Which includes changing the contents of a table with insert/update/delete. This is because if a function does have side effects, it can stop you using it in SQL.
http://docs.oracle.com/database/122/ADFNS/coding-subprograms-and-packages.htm#GUID-69A02381-FE34-42D9-8A19-9AA8A51514C6 You should place your DML in a procedure instead.
Also remember: the official definition of DML includes select.
DDLYou can't have static DDL in a function:
create or replace function f
return int as
update_count int;
begin
create table t2 ( x int );
return update_count;
end;
/
Errors: check compiler log
sho err
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
Though you can use dynamic SQL to execute DDL:
create or replace function f
return int as
update_count int;
begin
execute immediate 'create table t2 ( x int )';
return update_count;
end;
/
sho err
var v number;
exec :v := f;
desc t2
Name Null? Type
---- -------- ----------
X NOT NULL NUMBER(38)
This is
usually a questionable practice. Using functions to create or alter your objects is generally a bad idea. Though there are some DDL commands you may wish to run (such as truncate).