我有一个自定义函数,如
CREATE OR REPLACE FUNCTION FGETCY (L_SEQNO IN NUMBER) RETURN CHAR
AS
LNAME VARCHAR2(30);
NAME VARCHAR2(30);
BEGIN
LNAME := '';
NAME := '';
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY1 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'、';
END IF;
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY2 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'、';
END IF;
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY3 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'、';
END IF;
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY4 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'';
END IF;
RETURN RTRIM(LNAME,'、');
EXCEPTION WHEN OTHERS THEN
RETURN RTRIM(LNAME,'、');
END FGETCY;如果有一个cy2为空,那么就会异常退出,怎么样才能实现不退出,而继续执行呢?
CREATE OR REPLACE FUNCTION FGETCY (L_SEQNO IN NUMBER) RETURN CHAR
AS
LNAME VARCHAR2(30);
NAME VARCHAR2(30);
BEGIN
LNAME := '';
NAME := '';
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY1 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'、';
END IF;
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY2 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'、';
END IF;
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY3 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'、';
END IF;
SELECT USERNAME INTO NAME FROM CL_CY TA,PERSONNEL TB WHERE TB.USERCODE=CY4 AND TA.SEQNO = L_SEQNO;
if NAME is not null then
LNAME := LNAME || NAME||'';
END IF;
RETURN RTRIM(LNAME,'、');
EXCEPTION WHEN OTHERS THEN
RETURN RTRIM(LNAME,'、');
END FGETCY;如果有一个cy2为空,那么就会异常退出,怎么样才能实现不退出,而继续执行呢?
SELECT DECODE(USERNAME,null,'BLANK', USERNAME) INTO然后在后面的判断中加上
if NAME is not null and Name != 'BLANK' then