Oracle - Handle empty resultset on IF THEN ELSE statement
which should be the IF condition when the variable assignation results from an empt开发者_开发知识库y resultset?
Example:
CREATE OR REPLACE Function get_values
( chv_input IN varchar2 )
RETURN varchar2
IS
chv_output varchar2(100);
BEGIN
select 'value'
into chv_output
from dual where 1=2;
IF chv_output is null THEN --this condition is not working
chv_output := 'null';
ELSE
chv_output := 'not null';
END IF;
RETURN chv_output;
END;
--select 1, get_values('112') from dual
Try this instead:
EXCEPTION
WHEN NO_DATA_FOUND
chv_output := 'null';
精彩评论