oracle的function的语法,Oracle function语法

2018-3-30 遇到需要使用SQL方法拼接字符串的情况，就研究了一下SQL简单的方法应用

--定义入参数[参数名 in 参数类型]

create or replace function p_gettype(se_type in varchar2)

--定义返回类型

return varchar2

is

v_calling_type varchar2(45);

v_called_type varchar2(45);

v_tr_in varchar2(45);

v_tr_out varchar2(45);

v_sett_name varchar2(90);

begin

--查出v_calling_type

select call_type_name into v_calling_type from uparam.settle_a_def

where call_type = substr(se_type,1,2);

--查出v_called_type

select call_type_name into v_called_type from uparam.settle_a_def

where call_type = substr(se_type,3,2);

--查出v_tr_in

select tr_type_name into v_tr_in from uparam.tr_b_def

where tr_type = substr(se_type,5,2);

--查出v_tr_out

select tr_type_name into v_tr_out from uparam.tr_b_def

where tr_type = substr(se_type,7,2);

--返回字符串 calling_type(tr_in)->called_type||(tr_out);

v_sett_name := v_calling_type ||'('|| v_tr_in||')->'||v_called_type||'('|| v_tr_out||')';

return v_sett_name;

exception

when others then

return null;

end p_gettype;