我在视图里调用了函数,如果用sql语句在pl/sql中执行,RECEIPTEXESMEMO列全部都有值,没有问题;但是通过程序查询时,RECEIPTEXESMEMO列在第五行以后的数据丢失了,全部为null值。视图:
CREATE OR REPLACE VIEW V_FEE_RECEIPT AS
SELECT RECEIPT.RECEIPTINDEXID,
RECEIPT.RECEIPTCUSTOMERID,
FEs.f_Getreceiptbookingid(RECEIPT.RECEIPTINDEXID) AS RECEIPTEXESMEMO
FROM FES.T_FEE_RECEIPT RECEIPT
LEFT JOIN FES.T_FEE_INVOICE INVOICE ON
INVOICE.INVOICEINDEXID = RECEIPT.INVOICEINDEXID
函数:
create or replace function F_GETRECEIPTBOOKINGID( receiptindexid in varchar2 )
return varchar2
is
str_list varchar2(4000) default null;
str varchar2(20) default null;
begin
for x in ( SELECT ER.Bookingno
FROM FES.T_FEE_EXESRECEIPT ER
WHERE ER.RECEIPTID = receiptindexid ) loop
if not x.bookingno is null then
if INSTR(str_list, x.bookingno, 1, 1)='0' or INSTR(str_list, x.bookingno, 1, 1) is null then
str_list := str_list || str || to_char(x.bookingno);
str := ', ';
end if;
end if;
end loop;
return str_list;
end;
CREATE OR REPLACE VIEW V_FEE_RECEIPT AS
SELECT RECEIPT.RECEIPTINDEXID,
RECEIPT.RECEIPTCUSTOMERID,
FEs.f_Getreceiptbookingid(RECEIPT.RECEIPTINDEXID) AS RECEIPTEXESMEMO
FROM FES.T_FEE_RECEIPT RECEIPT
LEFT JOIN FES.T_FEE_INVOICE INVOICE ON
INVOICE.INVOICEINDEXID = RECEIPT.INVOICEINDEXID
函数:
create or replace function F_GETRECEIPTBOOKINGID( receiptindexid in varchar2 )
return varchar2
is
str_list varchar2(4000) default null;
str varchar2(20) default null;
begin
for x in ( SELECT ER.Bookingno
FROM FES.T_FEE_EXESRECEIPT ER
WHERE ER.RECEIPTID = receiptindexid ) loop
if not x.bookingno is null then
if INSTR(str_list, x.bookingno, 1, 1)='0' or INSTR(str_list, x.bookingno, 1, 1) is null then
str_list := str_list || str || to_char(x.bookingno);
str := ', ';
end if;
end if;
end loop;
return str_list;
end;
--这个是什么情况?
已写入 file afiedt.buf 1 begin
2 if not (1=1) is not null then --1=1 为true没有输出el
3 dbms_output.put_line('el');
4 end if;
5* end;
SQL> set serveroutput on;
SQL> /PL/SQL 过程已成功完成。SQL> ed
已写入 file afiedt.buf 1 begin
2 if not (12=1) is not null then --12=1为false也没有输出el
3 dbms_output.put_line('el');
4 end if;
5* end;
SQL> /PL/SQL 过程已成功完成。SQL> ed
已写入 file afiedt.buf 1 begin
2 --if not (12=1) is not null then
3 dbms_output.put_line('el');
4 --end if;
5* end;
SQL> /
elPL/SQL 过程已成功完成。
RECEIPTEXESMEMO列在第五行以后的数据丢失了,全部为null值这个情况在函数;里面返回什么值
if not x.bookingno is null then 如果x.bookingno为null才为TRUE 否则这个永远为FALSE
应该是有输出的
SQL>
SQL> declare
2 begin
3 if not 1 is null then
4 dbms_output.put_line('123');
5 end if;
6 end;
7
8 /
123
PL/SQL procedure successfully completed
SQL>
我错了
if not (1=1) is not null then 多了个not
该打啊
贴几条view里是null的数据对应的基表数据看看.
感觉是数据的问题.
一、如果单独执行函数没有任何问题
二、如果把不显示的那一列的数据拿出来单独通过视图查询,也能显示出来receiptexesmemo列值
三、数据没问题,因为我都已经查看过
四、现在的问题是,通过数据库直接执行sql和通过程序查询结果是不一样的。怀疑是不是通过程序查询时,对数据量还有一些别的限制造成的。
RECEIPTEXESMEMO列在第五行以后的数据丢失了,全部为null值这个情况在函数;里面返回什么值
create or replace function F_GETRECEIPTBOOKINGID( receiptindexid in varchar2 )
return varchar2
is
str_list varchar2(4000) default null;
str varchar2(20) default null;
begin
for x in ( SELECT ER.Bookingno
FROM FES.T_FEE_EXESRECEIPT ER
WHERE ER.RECEIPTID = receiptindexid ) loop
if not x.bookingno is null then
if INSTR(str_list, x.bookingno, 1, 1)='0' or INSTR(str_list, x.bookingno, 1, 1) is null then
str_list := str_list || str || to_char(x.bookingno);
str := ', ';
end if;
else str_list:='test'; --如果x.bookingno为null则返回 test
end if;
end loop;
return str_list;
end;