CREATE OR REPLACE FUNCTION F_GetRootForExport( FID IN varchar2)RETURN INTis CID varchar2(64);
FID_TEMP varchar2(64);
BEGIN
FID_TEMP := FID WHILE EXISTS(SELECT 1 FROM BMS_BOM_EXPORT WHERE item_code_d=FID) LOOP CID:=FID
SELECT item_code into FID FROM BMS_BOM_EXPORT WHERE item_code_d=CID
IF FID_TEMP=FID
then
RETURN 1
end if
END LOOP; RETURN 2END;
上面是我写的一个函数,总是报错,语法上估计有问题,新手,请指教。
FID_TEMP varchar2(64);
BEGIN
FID_TEMP := FID WHILE EXISTS(SELECT 1 FROM BMS_BOM_EXPORT WHERE item_code_d=FID) LOOP CID:=FID
SELECT item_code into FID FROM BMS_BOM_EXPORT WHERE item_code_d=CID
IF FID_TEMP=FID
then
RETURN 1
end if
END LOOP; RETURN 2END;
上面是我写的一个函数,总是报错,语法上估计有问题,新手,请指教。
CID:=FID;
return 1;
return 2;
你的return 1放在了循环的里面,这样可能会存在多个值,那返回的时候会出错的
这样试试
CREATE OR REPLACE FUNCTION F_GetRootForExport( FID IN varchar2) RETURN INT is CID varchar2(64);
FID_TEMP varchar2(64);
fid_temp2 varchar2(64);
v_count number;
BEGIN
FID_TEMP := FID LOOP
SELECT count(1) into v_count FROM BMS_BOM_EXPORT WHERE item_code_d=FID;
exit when v_count=0; CID:=FID ;
SELECT item_code into fid_temp2 FROM BMS_BOM_EXPORT WHERE item_code_d=CID ;
IF FID_TEMP=fid_temp2
then
RETURN 1 ;
end if ;
END LOOP; RETURN 2 ;END;
你这个cid好像没有用啊,你自己整理下逻辑
CREATE OR REPLACE FUNCTION F_GetRootForExport( FID IN varchar2) RETURN INT is CID varchar2(64);
FID_TEMP varchar2(64);
fid_temp2 varchar2(64);
v_count number;
BEGIN
FID_TEMP := FID ;
fid_temp2:= fid; LOOP
SELECT count(1) into v_count FROM BMS_BOM_EXPORT WHERE item_code_d=fid_temp2;
exit when v_count=0; CID:=FID ;
SELECT item_code into fid_temp2 FROM BMS_BOM_EXPORT WHERE item_code_d=CID ;
IF FID_TEMP=fid_temp2
then
RETURN 1 ;
end if ;
END LOOP; RETURN 2 ;END;
RETURN INT
is
--CID varchar2(64);
FID_TEMP varchar2(64);
fid_temp2 varchar2(64);
v_count number;
BEGIN
FID_TEMP := FID ;
FID_TEMP2 := FID ;
LOOP
SELECT count(1) into v_count FROM BMS_BOM_EXPORT WHERE item_code_d=fid_temp2;
exit when v_count=0;
SELECT item_code into fid_temp2 FROM BMS_BOM_EXPORT WHERE item_code_d=fid_temp2;
IF FID_TEMP=fid_temp2 THEN
RETURN 1 ;
end if ;
END LOOP;
RETURN 2;
END; --不用在用一个CID来传值了!