/* 将输入字符串反转输出的存储函数 */SQL> CREATE OR REPLACE FUNCTION fuc_strTurn(str IN CHAR) 2 RETURN CHAR 3 IS 4 value VARCHAR2(512); 5 loopBegin INT:=0; 6 loopEnd INT:=0; 7 BEGIN 8 loopEnd:=LENGTH(str); 9 FOR loopBegin IN 1..loopEnd LOOP 10 --DBMS_OUTPUT.PUT(substr(str,(loopEnd-loopBegin+1),1)); 11 value:=value || substr(str,(loopEnd-loopBegin+1),1); 12 END LOOP; 13 RETURN value; 14 END fuc_strTurn; 15 /函数已创建。SQL> select phone_no from tb_phone_no where rownum<6;PHONE_N ------- 1300240 1300241 1300242 1300243 1300244SQL> select FUC_STRTURN(phone_no) from tb_phone_no where rownum<6;FUC_STRTURN(PHONE_NO) ---------------------------------------------------------------------- 0420031 1420031 2420031 3420031 4420031
create or replace function reverse (f_str varchar2) return varchar2 is f_output varchar2(500); f_pos number; begin select length(f_str) into f_pos from dual; loop exit when f_pos = 0; f_output := f_output || substr(f_str,f_pos,1); f_pos := f_pos-1; end loop; return f_output;
exception when no_data_found then null; when others then raise; end reverse; / ---------------------------------------------- select col,reverse(col) from t1 === COL REVERSE(COL) hgeqop poqegh trqewgf fgweqrt fdsasg gsasdf hgs sgh ortiotuyo oyutoitro
2 RETURN CHAR
3 IS
4 value VARCHAR2(512);
5 loopBegin INT:=0;
6 loopEnd INT:=0;
7 BEGIN
8 loopEnd:=LENGTH(str);
9 FOR loopBegin IN 1..loopEnd LOOP
10 --DBMS_OUTPUT.PUT(substr(str,(loopEnd-loopBegin+1),1));
11 value:=value || substr(str,(loopEnd-loopBegin+1),1);
12 END LOOP;
13 RETURN value;
14 END fuc_strTurn;
15 /函数已创建。SQL> select phone_no from tb_phone_no where rownum<6;PHONE_N
-------
1300240
1300241
1300242
1300243
1300244SQL> select FUC_STRTURN(phone_no) from tb_phone_no where rownum<6;FUC_STRTURN(PHONE_NO)
----------------------------------------------------------------------
0420031
1420031
2420031
3420031
4420031
f_output varchar2(500);
f_pos number;
begin
select length(f_str) into f_pos from dual;
loop
exit when f_pos = 0;
f_output := f_output || substr(f_str,f_pos,1);
f_pos := f_pos-1;
end loop;
return f_output;
exception
when no_data_found then
null;
when others then
raise;
end reverse;
/
----------------------------------------------
select col,reverse(col) from t1
===
COL REVERSE(COL)
hgeqop poqegh
trqewgf fgweqrt
fdsasg gsasdf
hgs sgh
ortiotuyo oyutoitro