我常用的一个自定义函数,用来计算字符串的,返回数值的一个函数: create or replace function func(str in varchar2) return number as num number; begin execute immediate 'select '||str||' from dual' into num; return num; end;select func('3+2') from dual;
--再来一个自定义函数实现行列转换:功能于10g中wm_concat()函数一样--Sql代码 --创建function create or replace function my_concat(p_id in number) return varchar2 is result varchar2(4000); begin for rs in (select role from test2 where id=p_id) loop result :=result || rs.role || ','; end loop; result := rtrim(result,','); --去掉最后一个逗号 return result; end;
我先来:to_date
create or replace function func(str in varchar2)
return number
as
num number;
begin
execute immediate 'select '||str||' from dual' into num;
return num;
end;select func('3+2') from dual;
--再来一个自定义函数实现行列转换:功能于10g中wm_concat()函数一样--Sql代码
--创建function
create or replace function my_concat(p_id in number)
return varchar2
is
result varchar2(4000);
begin
for rs in (select role from test2 where id=p_id) loop
result :=result || rs.role || ',';
end loop;
result := rtrim(result,','); --去掉最后一个逗号
return result;
end;