# 我是新来的，求解一个字符串处理的问题

weivivia 2004-04-30 12:59:42

select f_include('a,bc,81','b,c'),f_include('a,bc,81','bc,a') from dual;
F_INCLUDE('A,BC,81','B,C') F_INCLUDE('A,BC,81','BC,A')
-------------------------- ---------------------------
0 1

create or replace function f_include(p_a varchar2,p_b varchar2) return number
is
result number:=1;
p_a_r varchar2(100) :='';
p_b_r varchar2(100) :='';
a varchar2(100):=p_a;
b varchar2(100):=p_b;
begin
if p_a_r!=p_b_r then
for i in 1..length(a) loop
p_a_r:=p_a_r||substr(a,i,instr(a,',')-1);
a:=substr(a,instr(a,',')+1);
for j in 1..length(b) loop
p_b_r:=p_b_r||substr(b,j,instr(b,',')-1);
b:=substr(b,instr(b,',')+1);
end loop;
result :=0;
end loop;
end if;
return result;
end;

...全文
86 9 点赞 打赏 收藏 举报

9 条回复

navis 2004-09-21

• 打赏
• 举报

weivivia 2004-05-08

create or replace function f_include(p_a varchar2,p_b varchar2) return number
is
result number:=1;
p_a_r varchar2(100) :='';
p_b_r varchar2(100) :='';
a varchar2(100) := p_a || ',';
b varchar2(100) := p_b || ',';
begin
for i in 1..length(b) loop
p_b_r := p_b_r || substr(b,i,1);
if substr(b,i,1) = ',' then
for j in 1..length(a) loop
p_a_r := p_a_r || substr(a,j,1);
if substr(a,j,1) = ',' then
if p_b_r = p_a_r then
p_a_r := '';
p_b_r := '';
goto out_loop;
elsif j = length(a) then
return 0;
end if;
p_a_r := '';
end if;
end loop;
<<out_loop>>
null;
end if;
end loop;
return 1;
end;
• 打赏
• 举报

snowy_howe 2004-05-05
CREATE OR REPLACE FUNCTION f_include (p_a VARCHAR2, p_b VARCHAR2)
RETURN NUMBER
IS
TYPE tab IS VARRAY (100) OF VARCHAR2 (10);

t_a tab := tab (1,2,3,4,5,6,7,8,9,10);
t_b tab := tab (1,2,3,4,5,6,7,8,9,10);
a VARCHAR2 (100) := p_a;
b VARCHAR2 (100) := p_b;
i INTEGER := 1; --逗号的位置
m INTEGER := 1; --a中逗号的数量，控制数组的下标。
n INTEGER := 1; --b中逗号的数量，控制数组的下标。
RESULT NUMBER := 1;
BEGIN
LOOP
i := INSTR (a, ',');

IF i <> 0
THEN
t_a (m) := SUBSTR (a, 1, i - 1);
a := SUBSTR (a, i + 1);
ELSE
t_a (m) := a;
EXIT;
END IF;

m := m + 1;
END LOOP;

LOOP
i := INSTR (b, ',');

IF i <> 0
THEN
t_b (n) := SUBSTR (b, 1, i - 1);
b := SUBSTR (b, i + 1);
ELSE
t_b (n) := b;
EXIT;
END IF;

n := n + 1;
END LOOP;

FOR i IN 1 .. n
LOOP
FOR j IN 1 .. m
LOOP
IF t_a (j) <> t_b (i)
THEN
RESULT := 0;
ELSE
RESULT := 1;
EXIT;
END IF;
END LOOP;

IF RESULT = 0
THEN
RETURN RESULT;
END IF;
END LOOP;

RETURN RESULT;
END;

• 打赏
• 举报

snowy_howe 2004-05-05
SQL> select 1 from dual where 'a' != '';

• 打赏
• 举报

weivivia 2004-04-30

is
result number:=1;
p_a_r varchar2(100) :='';
p_b_r varchar2(100) :='';
a varchar2(100):=p_a;
b varchar2(100):=p_b;
begin
<<lab1>>
p_b_r:=substr(b,1,instr(b,',')-1);
b:=substr(b,instr(b,',')+1);
<<lab2>>
p_a_r:=substr(a,1,instr(a,',')-1);
a:=substr(a,instr(a,',')+1);
if p_a_r!='' and p_b_r!='' and p_a_r=p_b_r then
goto lab1;
elsif p_a_r!='' and p_b_r!=''and p_a_r!=p_b_r then
result:=0;
goto lab2;
end if;
return result;
end;
/
select f_include('a,bc,81','b,c'),f_include('a,bc,81','bc,a') from dual;
/

SQL>

Function created

F_INCLUDE('A,BC,81','B,C') F_INCLUDE('A,BC,81','BC,A')
-------------------------- ---------------------------
1 1

elsif p_a_r!='' and p_b_r!=''and p_a_r!=p_b_r then
result:=0;

• 打赏
• 举报

weivivia 2004-04-30

• 打赏
• 举报

atao245993 2004-04-30

• 打赏
• 举报

atao245993 2004-04-30

• 打赏
• 举报

welyngj 2004-04-30

• 打赏
• 举报

Oracle

1.6w+

Oracle开发相关技术讨论

2004-04-30 12:59