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).