未测试,试一下
create or replace procedure DP_TEST(h_key in VARCHAR2,h_value in VARCHAR2 ) is
v_key VARCHAR2(100);
v_value VARCHAR2(100);
v_splitkey varchar2(10):=';';
begin
loop
exit when h_key is null or h_value is null;
if instr(h_key,v_splitkey)>0 then
v_key:=substr(h_key,1,instr(h_key,v_splitkey)-1);
h_key:=substr(h_key,instr(h_key,v_splitkey)+1);
else
v_key:=h_key;
h_key:=null;
end if;
if if instr(h_value,v_splitkey)>0 then
v_value:=substr(h_value,1,instr(h_value,v_splitkey)-1);
h_value:=substr(h_value,instr(h_value,v_splitkey)+1);
else
v_value:=h_value;
h_value:=null;
end if;
insert into t(key,value)values(v_key,v_value);
end loop;
commit;
end;
create or replace procedure DP_TEST(h_key in VARCHAR2,h_value in VARCHAR2 ) is
v_key VARCHAR2(100);
v_value VARCHAR2(100);
v_splitkey varchar2(10):=';';
begin
loop
exit when h_key is null or h_value is null;
if instr(h_key,v_splitkey)>0 then
v_key:=substr(h_key,1,instr(h_key,v_splitkey)-1);
h_key:=substr(h_key,instr(h_key,v_splitkey)+1);
else
v_key:=h_key;
h_key:=null;
end if;
if if instr(h_value,v_splitkey)>0 then
v_value:=substr(h_value,1,instr(h_value,v_splitkey)-1);
h_value:=substr(h_value,instr(h_value,v_splitkey)+1);
else
v_value:=h_value;
h_value:=null;
end if;
insert into t(key,value)values(v_key,v_value);
end loop;
commit;
end;
获取字符串中分号的个数加一即可
length(h_key)-length(replace(h_key,';',''))+1
这里有两个 if if 是不是写错了?
我删除了一个还是错误。
create or replace procedure DP_TEST(h_key in VARCHAR2,h_value in VARCHAR2 ) is
p_key varchar2(4000);
p_value varchar2(4000);
v_key VARCHAR2(100);
v_value VARCHAR2(100);
v_splitkey varchar2(10):=';';
begin
p_key:=h_key;
p_value:=h_value;
loop
exit when p_key is null or p_value is null;
if instr(p_key,v_splitkey)>0 then
v_key:=substr(p_key,1,instr(p_key,v_splitkey)-1);
p_key:=substr(p_key,instr(p_key,v_splitkey)+1);
else
v_key:=p_key;
p_key:=null;
end if;
if instr(p_value,v_splitkey)>0 then
v_value:=substr(p_value,1,instr(p_value,v_splitkey)-1);
p_value:=substr(p_value,instr(p_value,v_splitkey)+1);
else
v_value:=p_value;
p_value:=null;
end if;
insert into t(key,value)values(v_key,v_value);
end loop;
commit;
end;
v_key:=substr(p_key,1,instr(p_key,v_splitkey)-1);
p_key:=substr(p_key,instr(p_key,v_splitkey)+1);
else
v_key:=p_key;
p_key:=null;
end if; 能否把这段诠释写一写。看得不是很明白,所以我不知道v_key 有多少个值。如果我想取A 或者B 出来怎么取?
v_key:=substr(p_key,1,instr(p_key,v_splitkey)-1);--把字符串中的第一个字符分离出来,存到v_key
p_key:=substr(p_key,instr(p_key,v_splitkey)+1);--把剩余的字符串存到p_key
else--若不存在分号,则此次的字符为最后一个字符
v_key:=p_key;--把p_key赋值给v_key
p_key:=null;--同时把p_key置空,以推出循环
end if;
如何我现在还要把分割出来的值拼接成:decode(key,A,0,B,0,C,0,D,0,F,0,1)
这个怎么拼了。
应该是拼成这样吧,字符串需要用引号引起来的,加了几行,拼接的结果放在v_result里面了
create or replace procedure DP_TEST(h_key in VARCHAR2,h_value in VARCHAR2 ) is
p_key varchar2(4000);
p_value varchar2(4000);
v_key VARCHAR2(100);
v_value VARCHAR2(100);
v_splitkey varchar2(10):=';';
v_result varchar2(4000);
begin
p_key:=h_key;
p_value:=h_value;
loop
exit when p_key is null or p_value is null;
if instr(p_key,v_splitkey)>0 then
v_key:=substr(p_key,1,instr(p_key,v_splitkey)-1);
p_key:=substr(p_key,instr(p_key,v_splitkey)+1);
else
v_key:=p_key;
p_key:=null;
end if;
if instr(p_value,v_splitkey)>0 then
v_value:=substr(p_value,1,instr(p_value,v_splitkey)-1);
p_value:=substr(p_value,instr(p_value,v_splitkey)+1);
else
v_value:=p_value;
p_value:=null;
end if;
v_result:=v_result||','''||v_key||''',0'
insert into t(key,value)values(v_key,v_value);
end loop;
v_result:='decode(key'||v_result||',1) '
commit;
end;
v_result:=v_result||','''||v_key||''',0'
v_result:='decode(key'||v_result||',1) '
如果我代入 select DC_ID into ID from da_test group by DC_ID having sum(v_result)=0; 这里就好报错无效的字符。
或者 select DC_ID into ID from da_test group by DC_ID having sum('decode(key'||v_result||',1) ')=0; 都是一样报无效字符
想拼接到里面正常运行,需要用动态语句执行的
想拼接到里面正常运行,需要用动态语句执行的动态?对我来说是新词语啊!怎么才叫动态?有例子么?
ID number
type curtype is REF cursor;
A curtype;
begin
open A FOR 'select DC_ID from da_test group by DC_ID having sum('||v_result||')=0';
fetch A into id;
close A;
END;
p_key varchar2(4000);
p_value varchar2(4000);
v_key VARCHAR2(100);
v_value VARCHAR2(100);
v_splitkey varchar2(10):=';';
v_result varchar2(4000);
ID number;
type curtype is REF cursor;
A curtype;
begin
p_key:=h_key;
p_value:=h_value;
loop
exit when p_key is null or p_value is null;
if instr(p_key,v_splitkey)>0 then
v_key:=substr(p_key,1,instr(p_key,v_splitkey)-1);
p_key:=substr(p_key,instr(p_key,v_splitkey)+1);
else
v_key:=p_key;
p_key:=null;
end if;
if instr(p_value,v_splitkey)>0 then
v_value:=substr(p_value,1,instr(p_value,v_splitkey)-1);
p_value:=substr(p_value,instr(p_value,v_splitkey)+1);
else
v_value:=p_value;
p_value:=null;
end if;
v_result:=v_result||','''||v_key||''',0'
insert into t(key,value)values(v_key,v_value);
end loop;
v_result:='decode(key'||v_result||',1) '
commit;
open A FOR 'select DC_ID from da_test group by DC_ID having sum('||v_result||')=0';
fetch A into id;
close A;end;
我的查询语句得到的是多个id的,怎么只给我返回一个id?其他的呢?
open A FOR '你的语句';
fetch A into id;
while A %found loop
--逐个id进行处理
fetch A into id;
end loop;
close A;
你这个是语法问题
前面写了if,后面没有end if与他对应
需要在end前增加 end if;另外感觉你的业务逻辑可能有点问题ps:你这帖子也太长了,与开贴时的问题已经基本没啥关系了