differences between function and procedures in oracle

I know that this question is all over..
But Im really struggling to understand,
I see everywhere that functions cannot be used to perform crud operations on the db ( such as update statement ) which is not true.
Other than that, Basically the MAIN difference between them is that a procedure can have in and out parameters 0->n but a function has a return, and does not store the value in an out parameter..
Yea there are some small differences like the way you invoke it, if Im not wrong a function can be called within a select statement whereas a procedure cannot.
So to be honest, I dont really see any difference bewteen those two.
What should I answer if Im asked " Why would you choose function over procedure" ( or the opposite ). THANKS

Well, that's true (except for a typo, here):

> function can be called within a select statement whereas a function cannot (bold part should be "procedure")

You'd choose function when there's something you want to return to caller. Use a procedure when you want to process something.

Functions - as you said - can be used in a select statement, which can be used in both SQL and PL/SQL. Procedures, on the other hand, require PL/SQL. It is way simpler to call a function than a procedure (presume f_today and p_today return sysdate; function as return value, procedure via its out parameter), e.g.

select f_today from dual;

than

declare
  l_today date;
begin
  p_today(l_today);
  dbms_output.put_line(l_today);
end;
/

Functions can execute DML operations, but only if they are autonomous transactions. That's not what you'd always want to do. If you want to perform DML, you - usually - pick a procedure.

Although it is possible to do probably everything in both of them, choose the one that is most appropriate for what you are currently doing. Sometimes it is a function, another time it is a procedure (and sometimes you create a function which is then called by the procedure).

It just depends.

Because procedures can have OUT parameters, they too can return a value to the caller. And because functions are internally PL/SQL blocks that can do everything procedures can do, they also process things. And DML is just as possible in either of them.

I think the real difference is that a function must have one and only one main return value (passed back by the RETURN clause), whereas a procedure does not have to return anything, and indeed cannot do so with a RETURN clause. But either can use OUT parameters to pass back information.

Of course only functions can be called in SQL (as opposed to PL/SQL). But besides that, you can pretty much do anything you need with either a function or a procedure. I think it makes good programming sense to use a function when the main point of it is to retrieve a single atomic value, and to use a procedure when the main point is to make changes to the database or there are multiple things that are to be passed back and no one item is conceptually the "main" thing.

However, let me further qualify. In my PL/SQL programming, I tend to use functions more often than procedures even if the main point isn't to get something back, because I nearly always want a return/status code back (success vs. failure), so status is a good "main return value" for just about anything, making functions a good fit. This style of programming handles exceptions close to their source and returns statuses from named functions rather than raising exceptions out of them. But this is only one way to do it. Anyway, I hope this helps.