使用:pl/sql developer
create or replace function SP_STUDENT_TRAIN_COUNT_LIST(KSSJ in VARCHAR,
JSSJ IN VARCHAR,
DWMC in VARCHAR,
XYZT in VARCHAR)
return SYS_REFCURSOR is
rs SYS_REFCURSOR;
begin
DELETE FROM TB_TRAIN_STUDENT_COUNT;
if ( NVL(XYZT,'') = '' and NVL(JSSJ,'') = '' and NVL(KSSJ,'') = '' and NVL(DWMC,'') = '') then
open rs for
select 1 from dual;
ELSIF (NVL(XYZT,'') = '' and NVL(JSSJ,'') <> '' and NVL(KSSJ,'') <> '' and NVL(DWMC,'') = '' )THEN
open rs for
select 2 from dual;
ELSE
open rs for
select 3 from dual;
END IF;
COMMIT;
return rs;
Exception
WHEN OTHERS THEN
open rs for
select 0 from dual;
return rs;
end;----------------------------=====TESTbegin
-- Call the function
:result := sp_student_train_count_list(kssj => :kssj,
jssj => :jssj,
dwmc => :dwmc,
xyzt => :xyzt);
end;
参数:
kssj ='2009-01-01 00:00:01'
jssj ='2011-01-01 00:00:59'
dwmc =''
xyzt =''
结果:3
问题:
按理结果为:2,问题出现在哪里
create or replace function SP_STUDENT_TRAIN_COUNT_LIST(KSSJ in VARCHAR,
JSSJ IN VARCHAR,
DWMC in VARCHAR,
XYZT in VARCHAR)
return SYS_REFCURSOR is
rs SYS_REFCURSOR;
begin
DELETE FROM TB_TRAIN_STUDENT_COUNT;
if ( NVL(XYZT,'') = '' and NVL(JSSJ,'') = '' and NVL(KSSJ,'') = '' and NVL(DWMC,'') = '') then
open rs for
select 1 from dual;
ELSIF (NVL(XYZT,'') = '' and NVL(JSSJ,'') <> '' and NVL(KSSJ,'') <> '' and NVL(DWMC,'') = '' )THEN
open rs for
select 2 from dual;
ELSE
open rs for
select 3 from dual;
END IF;
COMMIT;
return rs;
Exception
WHEN OTHERS THEN
open rs for
select 0 from dual;
return rs;
end;----------------------------=====TESTbegin
-- Call the function
:result := sp_student_train_count_list(kssj => :kssj,
jssj => :jssj,
dwmc => :dwmc,
xyzt => :xyzt);
end;
参数:
kssj ='2009-01-01 00:00:01'
jssj ='2011-01-01 00:00:59'
dwmc =''
xyzt =''
结果:3
问题:
按理结果为:2,问题出现在哪里
--例如
SELECT NVL('','IS NULL') FROM DUAL;
--输出
NVL('','ISNULL')
IS NULL--判断她的值的话,要用变量接收
SELECT NVL('','IS NULL') into 变量 FROM DUAL;
declare
v_in varchar2(20):='';
v_tep varchar2(20);
begin
SELECT NVL('','IS NULL') into v_tep FROM DUAL;
if v_tep='IS NULL' then
dbms_output.put_line('dfd');
end if;
end;
要不改成 XYZT is null 或者 NVL(XYZT,'@') = '@'这类的