如下,建立了一个函数:CREATE OR REPLACE FUNCTION F_GETAUDITNAME(ID IN INTEGER, TYPE IN VARCHAR2)
RETURN VARCHAR2 IS
V_AUDITNAME VARCHAR2(50);
BEGIN
SELECT PERSONNAME
INTO V_AUDITNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = ID AND AU.AUDIT_TYPE = TYPE)
WHERE RN = 1;
RETURN V_AUDITNAME;
END;但之后我发现使用F_GETAUDITNAME返回的personname全部为空。SQL语句如下:
select F_GETAUDITNAME(t.id,'SD') from tbl_A t
后来我找了一个tbl_A中的存在的id,直接使用方法中的select语句查询,是有值的。如下:SELECT PERSONNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = 49533
AND AU.AUDIT_TYPE = 'SD')
WHERE RN = 1;为什么select F_GETAUDITNAME(t.id,'SD') from tbl_A t返回的全部是空了??麻烦各位高手指点迷津。今天早上郁闷死了!!
RETURN VARCHAR2 IS
V_AUDITNAME VARCHAR2(50);
BEGIN
SELECT PERSONNAME
INTO V_AUDITNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = ID AND AU.AUDIT_TYPE = TYPE)
WHERE RN = 1;
RETURN V_AUDITNAME;
END;但之后我发现使用F_GETAUDITNAME返回的personname全部为空。SQL语句如下:
select F_GETAUDITNAME(t.id,'SD') from tbl_A t
后来我找了一个tbl_A中的存在的id,直接使用方法中的select语句查询,是有值的。如下:SELECT PERSONNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = 49533
AND AU.AUDIT_TYPE = 'SD')
WHERE RN = 1;为什么select F_GETAUDITNAME(t.id,'SD') from tbl_A t返回的全部是空了??麻烦各位高手指点迷津。今天早上郁闷死了!!
INTO V_AUDITNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = ID AND AU.AUDIT_TYPE = TYPE)
WHERE RN = 1;你的这个值查询出来没值早报错了。
加个异常处理看看
CREATE OR REPLACE FUNCTION F_GETAUDITNAME(ID IN INTEGER, TYPE IN VARCHAR2)
RETURN VARCHAR2 IS
V_AUDITNAME VARCHAR2(50);
BEGIN
SELECT PERSONNAME
INTO V_AUDITNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = ID AND AU.AUDIT_TYPE = TYPE)
WHERE RN = 1;
RETURN V_AUDITNAME;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
END;
到底怎么回事了?我不是返回了吗??
--这样试试
CREATE OR REPLACE FUNCTION F_GETAUDITNAME(ID IN INTEGER, TYPE IN VARCHAR2)
RETURN VARCHAR2 IS
V_AUDITNAME VARCHAR2(50);
BEGIN
begin
SELECT PERSONNAME
INTO V_AUDITNAME
FROM (SELECT PERSONNAME,
ROW_NUMBER() OVER(PARTITION BY AU.AUDIT_ID, AU.AUDIT_TYPE ORDER BY AU.AUDIT_DATE) RN
FROM V_tbl_ZZ AU
WHERE AU.AUDIT_ID = ID AND AU.AUDIT_TYPE = TYPE)
WHERE RN = 1;
exception
when no_data_found then
V_AUDITNAME:='无此值';
null;
end ;
RETURN V_AUDITNAME;
END;