以下是我的代碼:CREATE OR REPLACE FUNCTION GETBOMPARN(chld_lh IN VARCHAR2,parn_lh IN VARCHAR2,icount in int) RETURN int AS
BEGIN
declare
strc_lh varchar2(100);
retNum int;
inum int;
Type Bom_tp is table of RES_BOM.parn_litm%TYPE;
pbom Bom_tp:= Bom_tp();
begin
if icount is null then
inum:=0;
else
inum:=icount;
end if;
retnum:=0;
select distinct parn_litm BULK COLLECT into pbom from res_bom where chld_litm=chld_lh;
for v_index in pbom.first .. pbom.last loop
if (pbom(v_index)=parn_lh)THEN
retnum:=1;
return retnum;
exit;
else
strc_lh:=pbom(v_index);
end if;
end loop;
if (retnum=0) then
inum:=inum+1;
if inum<=8 then
select getbomparn(trim(strc_lh),parn_lh,inum) into retNum from dual;
end if;
return retnum;
end if;
end;
END GETBOMPARN;出現錯誤如下:
SQL 錯誤: ORA-06502: PL/SQL: 數字或值錯誤
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 17
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 29
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 29
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 29
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
BEGIN
declare
strc_lh varchar2(100);
retNum int;
inum int;
Type Bom_tp is table of RES_BOM.parn_litm%TYPE;
pbom Bom_tp:= Bom_tp();
begin
if icount is null then
inum:=0;
else
inum:=icount;
end if;
retnum:=0;
select distinct parn_litm BULK COLLECT into pbom from res_bom where chld_litm=chld_lh;
for v_index in pbom.first .. pbom.last loop
if (pbom(v_index)=parn_lh)THEN
retnum:=1;
return retnum;
exit;
else
strc_lh:=pbom(v_index);
end if;
end loop;
if (retnum=0) then
inum:=inum+1;
if inum<=8 then
select getbomparn(trim(strc_lh),parn_lh,inum) into retNum from dual;
end if;
return retnum;
end if;
end;
END GETBOMPARN;出現錯誤如下:
SQL 錯誤: ORA-06502: PL/SQL: 數字或值錯誤
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 17
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 29
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 29
ORA-06512: 在 "SHOPFLOOR.GETBOMPARN", line 29
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
RETURN NUMBER
IS
strc_lh varchar2(100);
retNum number := 0;
inum number := nvl(icount,0);
Type Bom_tp is table of RES_BOM.parn_litm%TYPE;
pbom Bom_tp;BEGIN select distinct parn_litm BULK COLLECT into pbom from res_bom where chld_litm = chld_lh;
for i in pbom.first..pbom.last loop
if pbom(i) = parn_lh THEN
retnum := 1;
return retnum;
exit;
else
strc_lh := pbom(i);
end if;
end loop;
if retnum = 0 then
inum := inum + 1;
if inum <= 8 then
retNum := getbomparn(trim(strc_lh),parn_lh,inum);
end if;
return retnum;
end if;END GETBOMPARN;
比如表A
col1 col2
111 222
111 223
111 224
222 333
222 334
223 335
223 336
333 441
333 442
333 444
....
这两个字段为索引,我知道两个数据(如444,111),我想知道444是否为包含有111.
我写了个函数,用递归的方法来循环读取,但是出错。To guolin_cai:
语法应该没有错,因为可以创建,只是在执行的时候出错。
我估计高手你能用connect by 把它搞出来,一个sql就完。
RETURN NUMBER
IS retNum number := 0; -- 0 not exist 1 existBEGIN for i in (select col2 from a start with col1 = parn_lh connect by prior col2 = col1)
loop
if i = chld_lh then
retNum := 1;
return retNum;
end if;
end loop;
return retNum;
exception
when others then
retNum := 0;
return retNum;
END GETBOMPARN;