Can I delete rows using user defined functions in oracle?

I created a user defined function to delete some data. It doesn't work with delete but works with select. I am Oracle 9i.

The function is something like this:

create or replace function UFN_PURGEDATA(INPUTID IN VarChar2) return number is
  Result number;
begin

  Result := 0;


   DELETE FROM MyTable WHERE MyTable.ID=INPID;

  COMMIT; 

   Result := 1;
 EXCEPTION WHEN OTHERS THEN    

  return(Result);
end UFN_PURGEDATA;

Then I use select UFN_PURGEDATA('开发者_StackOverflow中文版test') from dual to run it but got result 0.

---

The answer to your question is "no".

If you remove your error "handling" you will find that the delete is failing with an exception like:

ORA-14551: cannot perform a DML operation inside a query

i.e. you cannot perform an insert, update or delete from within a function called in a SELECT statement.

To execute this function in an IDE or SQL Plus, wrap it in some more PL/SQL like this:

declare
  l_result number;
begin
  l_result := my_function(123);
end;

However, you will need to add a RETURN statement to your function first otherwise it will fail.

(NB I said "handling" above in quotes because it is really "mishandling" - it completely disguises the actual problem in a very unhelpful way.)

---

You can perform DML inside a function used in a SELECT if you add PRAGMA AUTONOMOUS_TRANSACTION. For example:

create or replace function UFN_PURGEDATA(INPUTID IN VarChar2) return number is
    pragma autonomous_transaction;
begin
  DELETE FROM MyTable WHERE MyTable.ID=INPUTID;
  COMMIT; 
  return 1;
  EXCEPTION WHEN OTHERS THEN    
  return 0;
end UFN_PURGEDATA;
/

But you definitely want to avoid this approach if possible. In general, there's no way to know how many times a function will be executed if it's used in a SELECT.

---

Yes you can delete rows using user-defined functions in Oracle, but not from within a SELECT statement.

There are a couple of problems with your code:
- you don't return a value if your function does not raise an exception
- you must not use a function performing DML in a SELECT statement; if you remove your exception block, you get an ORA-14551

---

Why not create a procedure (instead of function) with OUT parameter returning the number? Without doing the autonomous transaction trick, Oracle doesn't want you running functions (used in selects) with "side-effects" (understandable why we don't want a select to result in DML changes).