create or replace function Get_DZString(fhpzbm in varchar2) return varchar2 is
Result varchar2(200);
begin
declare
/* 根据传入的编码得到到站字符串,如:到站1;到站2; */
--定义游标
cursor cur_dz(vfhpzbm varchar2) is
select dz from fh_fhpz where fhpzbm in (vfhpzbm) and scbr='N';
--执行游标
begin
for obj in cur_dz(fhpzbm) loop
if obj.dz is not null then
Result:=Result||obj.dz||';';
end if;
end loop;
end; return(Result);
end Get_DZString;函数编译通过,执行
-- Created on 2011-8-2 by ADMINISTRATOR
declare
-- Local variables here
ss varchar2(200);
begin
-- Test statements here
select get_dzstring('211000000131,221000000421') into ss from dual;
dbms_output.put_line(ss);
end;
输出内容为空,正确的输出应该是:贵州;重庆;select get_dzstring('21100000013') into ss from dual;
传入的字符串为单个的,输出正常,结果为:贵州;help me
Result varchar2(200);
begin
declare
/* 根据传入的编码得到到站字符串,如:到站1;到站2; */
--定义游标
cursor cur_dz(vfhpzbm varchar2) is
select dz from fh_fhpz where fhpzbm in (vfhpzbm) and scbr='N';
--执行游标
begin
for obj in cur_dz(fhpzbm) loop
if obj.dz is not null then
Result:=Result||obj.dz||';';
end if;
end loop;
end; return(Result);
end Get_DZString;函数编译通过,执行
-- Created on 2011-8-2 by ADMINISTRATOR
declare
-- Local variables here
ss varchar2(200);
begin
-- Test statements here
select get_dzstring('211000000131,221000000421') into ss from dual;
dbms_output.put_line(ss);
end;
输出内容为空,正确的输出应该是:贵州;重庆;select get_dzstring('21100000013') into ss from dual;
传入的字符串为单个的,输出正常,结果为:贵州;help me
select dz from fh_fhpz where fhpzbm in (vfhpzbm) and scbr='N';
修改为
cursor cur_dz(vfhpzbm varchar2) is
select dz from fh_fhpz where instr(fhpzbm,vfhpzbm)>0 and scbr='N';
2,用动态游标。
设计问题 哈哈 应该dbms_putline出来 看看 是不是对的结果
select dz from fh_fhpz where instr(fhpzbm,'211000000131,211000000135,221000000132')>0 and scbr='N';
执行结果还是空的,似乎不行哦。单独执行:
select dz from fh_fhpz where instr(fhpzbm,'211000000131')>0 and scbr='N';
是有结果的1,切分带逗号的字符串。
2,用动态游标。
这两个都怎么用?
select dz from fh_fhpz where instr(','||'211000000131,211000000135,221000000132'||',' ,','||fhpzbm||',')>0 and scbr='N';
2:declare
TYPE test_cursor is ref cursor;
cru2 test_cursor;begin
-- Test statements here
OPEN cru2 FOR 'select * from test1';end;