declare v_char varchar2(20); --根据你的函数返回类型来定义这个变量的类型 begin v_char:=functionname(参数); --直接赋值给变量就可以了 dbms_output.put_line(v_char); end;
w_IRTN := FUN******();
这样我知道可以,我要的是这样的形式 select functionname(参数) from dual
函数 CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2, TWO IN VARCHAR2) RETURN VARCHAR2 IS ISN VARCHAR2(15); IMAC VARCHAR2(15); BEGIN ISN := TSN; ---- update UPDATE SAJET.G_SSC_MAC SET SERIAL_NUMBER = ISN WHERE CHILD_MAC = (SELECT MIN(CHILD_MAC) FROM SAJET.G_SSC_MAC WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO) AND LENGTH(SERIAL_NUMBER) = 3 AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN); ---- query SELECT CHILD_MAC INTO IMAC FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN AND PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO); RETURN IMAC; EXCEPTION WHEN OTHERS THEN IMAC := ''; RETURN IMAC; END;获取返回值 SELECT 'MAC:'||SSC_GETMAC_BYPARTID('S.YTB09P007D','A23145001') FROM DUAL;
--你的确定只有一个更新? CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,TWO IN VARCHAR2) RETURN VARCHAR2 IS ISN VARCHAR2(15); IMAC VARCHAR2(15); BEGIN ISN := TSN; ---- update UPDATE SAJET.G_SSC_MAC SET SERIAL_NUMBER = ISN WHERE CHILD_MAC = (SELECT MIN(CHILD_MAC) FROM SAJET.G_SSC_MAC WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO) AND LENGTH(SERIAL_NUMBER) = 3 AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN); commit;---加个提交 ---- query begin SELECT CHILD_MAC INTO IMAC FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN AND PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO); EXCEPTION WHEN OTHERS THEN IMAC := ''; end; RETURN IMAC; END;
确实只有一个更新,commit我加过,效果一样的:不行
还是为空?你去掉异常部分 IMAC := ''; 在试试
---你的改成这样 CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,TWO IN VARCHAR2) RETURN VARCHAR2 IS ISN VARCHAR2(15); IMAC VARCHAR2(15); BEGIN ISN := TSN; ---- update UPDATE SAJET.G_SSC_MAC SET SERIAL_NUMBER = ISN WHERE CHILD_MAC = (SELECT MIN(CHILD_MAC) FROM SAJET.G_SSC_MAC WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO) AND LENGTH(SERIAL_NUMBER) = 3 AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN); commit;---加个提交 ---- query SELECT CHILD_MAC INTO IMAC FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN AND PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO); RETURN IMAC; exception when others then dbms_output.put_line(sqlcode||' '||sqlerrm); END;--我的你试试 CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,TWO IN VARCHAR2) RETURN VARCHAR2 IS ISN VARCHAR2(15); IMAC VARCHAR2(15); BEGIN ISN := TSN; ---- update UPDATE SAJET.G_SSC_MAC SET SERIAL_NUMBER = ISN WHERE CHILD_MAC = (SELECT MIN(CHILD_MAC) FROM SAJET.G_SSC_MAC WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO) AND LENGTH(SERIAL_NUMBER) = 3 AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN) return CHILD_MAC into IMAC; commit;---加个提交 RETURN IMAC; exception when others then dbms_output.put_line(sqlcode||' '||sqlerrm); END;
SELECT CHILD_MAC INTO IMAC FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN AND PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO); 说明你的CHILD_MAC 值为null没有值
declare
v_char varchar2(20); --根据你的函数返回类型来定义这个变量的类型
begin
v_char:=functionname(参数); --直接赋值给变量就可以了
dbms_output.put_line(v_char);
end;
w_IRTN := FUN******();
select functionname(参数) from dual
CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,
TWO IN VARCHAR2)
RETURN VARCHAR2 IS
ISN VARCHAR2(15);
IMAC VARCHAR2(15);
BEGIN
ISN := TSN;
---- update
UPDATE SAJET.G_SSC_MAC
SET SERIAL_NUMBER = ISN
WHERE CHILD_MAC =
(SELECT MIN(CHILD_MAC)
FROM SAJET.G_SSC_MAC
WHERE PART_ID =
(SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO)
AND LENGTH(SERIAL_NUMBER) = 3
AND ON_USED <> 'Y')
AND NOT EXISTS
(SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN);
---- query
SELECT CHILD_MAC
INTO IMAC
FROM SAJET.G_SSC_MAC
WHERE SERIAL_NUMBER = ISN
AND PART_ID =
(SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO);
RETURN IMAC;
EXCEPTION
WHEN OTHERS THEN
IMAC := '';
RETURN IMAC;
END;获取返回值
SELECT 'MAC:'||SSC_GETMAC_BYPARTID('S.YTB09P007D','A23145001') FROM DUAL;
--你的确定只有一个更新?
CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,TWO IN VARCHAR2) RETURN VARCHAR2
IS
ISN VARCHAR2(15);
IMAC VARCHAR2(15);
BEGIN
ISN := TSN;
---- update
UPDATE SAJET.G_SSC_MAC
SET SERIAL_NUMBER = ISN
WHERE CHILD_MAC =
(SELECT MIN(CHILD_MAC)
FROM SAJET.G_SSC_MAC
WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO)
AND LENGTH(SERIAL_NUMBER) = 3
AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN);
commit;---加个提交
---- query
begin
SELECT CHILD_MAC
INTO IMAC
FROM SAJET.G_SSC_MAC
WHERE SERIAL_NUMBER = ISN
AND PART_ID =
(SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO);
EXCEPTION
WHEN OTHERS THEN
IMAC := '';
end;
RETURN IMAC;
END;
在试试
---你的改成这样
CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,TWO IN VARCHAR2) RETURN VARCHAR2
IS
ISN VARCHAR2(15);
IMAC VARCHAR2(15);
BEGIN
ISN := TSN;
---- update
UPDATE SAJET.G_SSC_MAC
SET SERIAL_NUMBER = ISN
WHERE CHILD_MAC =
(SELECT MIN(CHILD_MAC)
FROM SAJET.G_SSC_MAC
WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO)
AND LENGTH(SERIAL_NUMBER) = 3
AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN);
commit;---加个提交
---- query
SELECT CHILD_MAC
INTO IMAC
FROM SAJET.G_SSC_MAC
WHERE SERIAL_NUMBER = ISN
AND PART_ID =
(SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO);
RETURN IMAC;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
END;--我的你试试
CREATE OR REPLACE FUNCTION SSC_GETMAC_BYPARTID(TSN IN VARCHAR2,TWO IN VARCHAR2) RETURN VARCHAR2
IS
ISN VARCHAR2(15);
IMAC VARCHAR2(15);
BEGIN
ISN := TSN;
---- update
UPDATE SAJET.G_SSC_MAC
SET SERIAL_NUMBER = ISN
WHERE CHILD_MAC =
(SELECT MIN(CHILD_MAC)
FROM SAJET.G_SSC_MAC
WHERE PART_ID = (SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO)
AND LENGTH(SERIAL_NUMBER) = 3
AND ON_USED <> 'Y') AND NOT EXISTS (SELECT 1 FROM SAJET.G_SSC_MAC WHERE SERIAL_NUMBER = ISN) return CHILD_MAC into IMAC;
commit;---加个提交
RETURN IMAC;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
END;
INTO IMAC
FROM SAJET.G_SSC_MAC
WHERE SERIAL_NUMBER = ISN
AND PART_ID =
(SELECT MODEL_ID FROM SAJET.G_WO_BASE WHERE WORK_ORDER = TWO);
说明你的CHILD_MAC 值为null没有值