why we can't call stored procedure from sql select?

Putting out parameters to one side, a procedure doesn't return a value. Expressions in the select clause return values to the client. So it makes no sense to use something that returns nothing.

Expressions in other SQL clauses (where, order by, etc.) must return a value for these to work! For example what would the following even mean:

select * from t
where  proc = 1;

Proc returns nothing! So you have to use a function.

why stored function called from sql select statement has only in variables?

Firstly: using out parameters in a function is generally a bad idea. These are for returning a single value. If you need to return multiple values, then you should create an object or record and return that.

But when it comes to using functions with out parameters in a SQL statement, what would the out values map to?

Think about the following:

select f1(out1, out2)
from   t
where  f2(out1, out2) = 1;

You have a single column in your select. How would you return the out variables? Generate new columns? Create a type on-the-fly? There isn't a defined answer for this.

And the function in the where clause makes even less sense. If the function never returns 1, the query returns no rows. But you've still executed it. How on earth do you capture the out values?!

So we avoid these issues by preventing out parameters in functions in SQL.

can sys_refcursor exhibits dynamic sql?

Can you do dynamic SQL with ref cursors? Yes, yes you can!

create or replace function f (p int)
  return sys_refcursor as 
 cur sys_refcursor ;
begin
  if p = 1 then
    open cur for 'select * from dual where dummy  = ''X'' and 1 = ' || p;
  else
    open cur for select 'blah' from dual;
  end if;
 
  return cur;
end;
/

var c refcursor;

exec :c := f(1);

print :c;

C
-
DUMMY 
----- 
X

exec :c := f(2);

print :c;

C
-
'BLAH' 
------ 
blah 

(obviously you'd use bind variables in the real world...)