字符串重排列--在线等,解决马上给分!!! 原字符串:AA+BB+CC-DD+EE-FF+WW希望排列成:AA+BB+CC+EE+WW-FF-DD请大家帮忙阿!!!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我想题目应该是:原字符串:AA+BB+CC+EE+WW-FF-DD希望排列成:AA+BB+CC-DD+EE-FF+WW 不明白啊!是sql语句么?用什么实现呢! 随手写了一个过程,不知道是否满足你的要求:create or replace function change_str(s varchar2) return varchar2as v_out varchar2(100); v_last varchar2(100); v_temp varchar2(100); iLoop pls_integer; iLoop2 pls_integer;begin v_out := ''; v_last := ''; iLoop := 1; while iLoop <= length(s) loop select substr(s, iLoop, 1) into v_temp from dual; if v_temp = '-' then v_last := v_last || v_temp; iLoop2 := iLoop + 1; while iLoop2 <= length(s) loop select substr(s, iLoop2, 1) into v_temp from dual; exit when (v_temp = '-') or (v_temp = '+'); v_last := v_last || v_temp; iLoop2 := iLoop2 + 1; end loop; iLoop := iLoop2; else v_out := v_out || v_temp; iLoop := iLoop + 1; end if; end loop; return v_out || v_last;end change_str;/运行:select change_str('AA+BB+CC-DD+EE-FF+WW') from dual;结果:CHANGE_STR('AA+BB+CC-DD+EE-FF+--------------------------------------------------------------------------------AA+BB+CC+EE+WW-DD-FF create or replace procedure String_ReOrder(STR varchar2) isbeginDECLARE STRLEN NUMBER; STRL VARCHAR2(100); STRR VARCHAR2(100); STRT VARCHAR2(100); BEGIN STRT := STR ;STRR := '';FOR I IN 1.. LENGTH(STR)-LENGTH(REPLACE(STR,'-','')) LOOP STRLEN := INSTR(STRT,'-',1); STRL := STRL||SUBSTR(STRT,1,STRLEN-1) ; STRT := SUBSTR(STRT,STRLEN); STRLEN := INSTR(STRT,'+',1); STRR := STRR||SUBSTR(STRT,1,STRLEN-1); STRT := SUBSTR(STRT,STRLEN); END LOOP;IF SUBSTR(STRT,1,1) = '+' THEN STRL := STRL||STRT;ELSE STRR := STRR||STRT;END IF; DBMS_OUTPUT.put_line(STRL||STRR);END ;end String_ReOrder;SQL> EXEC String_ReOrder('AA+BB+CC-DD+EE-FF+WW-W-WQ+589+456');AA+BB+CC+EE+WW+589+456-DD-FF-W-WQPL/SQL procedure successfully completed xiaoxiao1984(潇潇) 的方法算比较好,循环的次数比较少。但是这个循环的次数控制还是稍微多了点,可以不可以改为:while INSTR(STRT,'-',2)<0 and INSTR(STRT,'+',2)<0 loop......end loop 也建议xiaoxiao1984(潇潇)把过程改为函数,这样的话就可以在SQL中调用! hehe,减少循环次数,再改成函数吧 create or replace function String_ReOrder_Func_1 (STR varchar2) return varchar2 isbeginDECLARESTRLEN NUMBER;STRL VARCHAR2(100);STRR VARCHAR2(100);STRT VARCHAR2(100);BEGINSTRT := STR ;STRR := '';while INSTR(STRT,'-',2)>0 or INSTR(STRT,'+',2)>0 LOOP STRLEN := INSTR(STRT,'-',1);STRL := STRL||SUBSTR(STRT,1,STRLEN-1) ;STRT := SUBSTR(STRT,STRLEN);STRLEN := INSTR(STRT,'+',1);STRR := STRR||SUBSTR(STRT,1,STRLEN-1);STRT := SUBSTR(STRT,STRLEN);end loop;IF SUBSTR(STRT,1,1) = '+' THENSTRL := STRL||STRT;ELSESTRR := STRR||STRT;END IF;DBMS_OUTPUT.put_line(STRL||STRR);return (STRL||STRR);END ;end String_ReOrder_Func_1; VB6如何调用oracle存储过程呢 急问: 在操作系统启动时自动执行job? dbms_xmlsave 的用法??? Oracle数据库sql查询缓慢问题 一句创建序列的语句的问题 设置参数问题 请问,没有后缀名的文件是什么文件 (100分)oralce 8.1.7 在red hat linux 7.2下安装出现“虚拟机初始化。。。”问题???急,在线等待 什么语句能把一个表中的所有字段名列出来? DBA studio不能进入了是怎么回事 高手请进:Java中如何执行“show parameters;”这样的语句[在线等] 更新优化问题
希望排列成:AA+BB+CC-DD+EE-FF+WW
return varchar2
as
v_out varchar2(100);
v_last varchar2(100);
v_temp varchar2(100);
iLoop pls_integer;
iLoop2 pls_integer;
begin
v_out := ''; v_last := '';
iLoop := 1;
while iLoop <= length(s)
loop
select substr(s, iLoop, 1) into v_temp from dual;
if v_temp = '-' then
v_last := v_last || v_temp;
iLoop2 := iLoop + 1;
while iLoop2 <= length(s)
loop
select substr(s, iLoop2, 1) into v_temp from dual;
exit when (v_temp = '-') or (v_temp = '+');
v_last := v_last || v_temp;
iLoop2 := iLoop2 + 1;
end loop;
iLoop := iLoop2;
else
v_out := v_out || v_temp;
iLoop := iLoop + 1;
end if;
end loop;
return v_out || v_last;
end change_str;
/运行:
select change_str('AA+BB+CC-DD+EE-FF+WW') from dual;
结果:
CHANGE_STR('AA+BB+CC-DD+EE-FF+
--------------------------------------------------------------------------------
AA+BB+CC+EE+WW-DD-FF
begin
DECLARE
STRLEN NUMBER;
STRL VARCHAR2(100);
STRR VARCHAR2(100);
STRT VARCHAR2(100);
BEGIN
STRT := STR ;
STRR := '';FOR I IN 1.. LENGTH(STR)-LENGTH(REPLACE(STR,'-','')) LOOP
STRLEN := INSTR(STRT,'-',1);
STRL := STRL||SUBSTR(STRT,1,STRLEN-1) ;
STRT := SUBSTR(STRT,STRLEN);
STRLEN := INSTR(STRT,'+',1);
STRR := STRR||SUBSTR(STRT,1,STRLEN-1);
STRT := SUBSTR(STRT,STRLEN);
END LOOP;
IF SUBSTR(STRT,1,1) = '+' THEN
STRL := STRL||STRT;
ELSE
STRR := STRR||STRT;
END IF;
DBMS_OUTPUT.put_line(STRL||STRR);
END ;
end String_ReOrder;SQL> EXEC String_ReOrder('AA+BB+CC-DD+EE-FF+WW-W-WQ+589+456');
AA+BB+CC+EE+WW+589+456-DD-FF-W-WQ
PL/SQL procedure successfully completed
但是这个循环的次数控制还是稍微多了点,可以不可以改为:
while INSTR(STRT,'-',2)<0 and INSTR(STRT,'+',2)<0 loop
...
...
end loop
begin
DECLARE
STRLEN NUMBER;
STRL VARCHAR2(100);
STRR VARCHAR2(100);
STRT VARCHAR2(100);
BEGIN
STRT := STR ;
STRR := '';while INSTR(STRT,'-',2)>0 or INSTR(STRT,'+',2)>0 LOOP
STRLEN := INSTR(STRT,'-',1);
STRL := STRL||SUBSTR(STRT,1,STRLEN-1) ;
STRT := SUBSTR(STRT,STRLEN);
STRLEN := INSTR(STRT,'+',1);
STRR := STRR||SUBSTR(STRT,1,STRLEN-1);
STRT := SUBSTR(STRT,STRLEN);
end loop;IF SUBSTR(STRT,1,1) = '+' THEN
STRL := STRL||STRT;
ELSE
STRR := STRR||STRT;
END IF;DBMS_OUTPUT.put_line(STRL||STRR);
return (STRL||STRR);
END ;
end String_ReOrder_Func_1;