1.oracle获取中文拼音首字母
CREATE OR REPLACE FUNCTION FUNC_GET_PY(z varchar2)
RETURN varchar2
ISpy varchar2(20); --与源字符对应的拼音码curr_no integer; --当前字符序号maxlen integer; --z(源字符串)的最大长度l_schar varchar2(100); --截取后的源字符串py_char char(1); --当前字符的拼音码l_vzchar varchar2(2); --当前字符py_flag smallint; --当前字符是否存在拼音码的标志curr_len integer; --当前拼音码长度
BEGIN--初始化参数curr_no:=1;py:='';py_char:='';l_vzchar:='';py_flag:=0;l_schar:=trim(z);maxlen:=length(l_schar);if maxlen = 0 thenreturn '';end if;curr_len := 0;--拼音码的最大长度为20且不超过字符的最大长度
<<L>> while curr_no <= maxlen and curr_len < 20 loop--l_vzchar:=substr(z,curr_no,1);select substr(l_schar,curr_no,1) into l_vzchar from dual;select count(py) into py_flag from trans_twzk where z = l_vzchar;if py_flag = 1 thenselect py into py_char from trans_twzk where z = l_vzchar;--py_char:=nvl(py_char,' ');else--'%-+~.'用其本身代替if l_vzchar = '%' or l_vzchar = '-' or l_vzchar = '+' or l_vzchar = '~' or l_vzchar = '.' thenpy_char:=l_vzchar;--'()（）［］'和' '则跳过elsif l_vzchar = '(' or l_vzchar = ')' or l_vzchar = '（' or l_vzchar = '）' or l_vzchar = '［' or l_vzchar = '］' or l_vzchar=' ' or l_vzchar='[' or l_vzchar=']' thenselect substr(l_schar,1,curr_no-1) || substr(l_schar,curr_no+1,length(l_schar)) into l_schar from dual; --跳过当前字符串maxlen:=maxlen-1; --跳过则源字符长度减1goto l;--其他的未找到的字符(如汉字)则用'z'来代替elsepy_char:='z';end if;end if;curr_no:=curr_no+1;py:=py || py_char;curr_len := length(py);end loop;return trim(py);
END;
2.获取WB
CREATE OR REPLACE FUNCTION FUNC_GET_WB(z varchar2)
RETURN varchar2
ISwb varchar2(20);curr_no integer; --当前字符串序号maxlen integer; --z的最大长度l_schar varchar2(500);wb_char char(1); --当前字符的拼音码l_vzchar varchar2(2); --当前字符wb_flag smallint;
BEGIN--初始化参数--maxlen:=length(z);l_schar:=trim(z);maxlen:=length(l_schar);curr_no:=1;wb:='';wb_char:='';l_vzchar:='';wb_flag:=0;if maxlen = 0 thenreturn '';end if;--拼音码的最大长度为6且不超过字符的最大长度
<<L>> while curr_no < 20 and curr_no <= maxlen loop--l_vzchar:=substr(z,curr_no,1);select substr(l_schar,curr_no,1) into l_vzchar from dual;--判断是否有对应的五笔码select count(wb) into wb_flag from trans_twzk where z = l_vzchar;if wb_flag = 1 thenselect wb into wb_char from trans_twzk where z = l_vzchar;--wb_char:=nvl(wb_char,' ');--没有对应的五笔码的处理else--'%-+~.'字符用其本身代替if l_vzchar = '%' or l_vzchar = '-' or l_vzchar = '+' or l_vzchar = '~' or l_vzchar = '.' thenwb_char:=l_vzchar;--'()（）［］'和' '则跳过elsif l_vzchar = '(' or l_vzchar = ')' or l_vzchar = '（' or l_vzchar = '）' or l_vzchar = '［' or l_vzchar = '］' or l_vzchar=' ' thenselect substr(l_schar,1,curr_no-1) || substr(l_schar,curr_no+1,length(l_schar)) into l_schar from dual;maxlen:=maxlen-1;goto l;--其他的未找到的字符(如汉字)则用'z'来代替elsewb_char:='z';end if;end if;curr_no:=curr_no+1;wb:=wb || wb_char;end loop;return wb;
END;
3.获取年龄
CREATE OR REPLACE function fun_age(age in varchar2) return number isFunctionResult number;f_age varchar2(10);h_age varchar2(10);
beginif age is null or trim(age) = '' thenFunctionResult := '';elseselect to_number(substr(age,0,length(age)-1)) into f_age from dual;select substr(age,-1) into h_age from dual;if h_age = 'Y' thenFunctionResult := f_age;elseFunctionResult :=0;end if;end if;return FunctionResult;exceptionwhen others thenreturn 0;
end fun_age;
#4.
CREATE OR REPLACE function fun_age_md(age in varchar2) return varchar2 isFunctionResult varchar2(2);f_age varchar2(10);h_age varchar2(10);
beginif age is null or trim(age) = '' thenFunctionResult := '';elseselect to_number(substr(age,0,length(age)-1)) into f_age from dual;select substr(age,-1) into h_age from dual;if h_age = 'M' thenFunctionResult := f_age;else if h_age = 'D' thenFunctionResult := f_age;end if ;end if;end if;return FunctionResult;exceptionwhen others thenreturn 0;
end fun_age_md;