Go语言视频零基础入门到精通

卡卡的梦

下Mian是我創建的日志和固hua視图，其中調用le自定义函数getRiskSerialNo，该函数生成一ge类似序列号的lie，lie明为OrderNo，但该列不能刷新，cha出来值是空的，不使用固化视图直接使用sql语句查的话是没问题的，但此chu由于特殊原因又Bi须使用固hua视图，请问高手该如何jie決？？？
CREATE MATERIALIZED VIEW LOG ON lcgrppol WITH PRIMARY K1EY;

CREATE MATERIALIZED VIEW mv_lcgrppol REFRESH FORCE WITH PRIMARY KEY
AS select getRiskSerialNo(a.gRPContno,a.grppolno) OrderNo,a.* from lcgrppol a order by a.grpcontno;

自ding义函數如下：
create or replace function getRiskSerialNo(tGrpContNo in varchar2,tPolNo in varchar2)
return varchar2 is
pragma autonomous_transaction;
tRiderNo varchar2(2) := '';
begin
tRiderNo := '1';
select t2.RiderNo into tRiderNo from
(select t1.*,rownum RiderNo from
(select lcgrppol.grpcontno grpcontno,lcgrppol.grppolno,
riskcode,
substr(riskcode, 2, 1) as a,
makedate,
substr(riskcode, 3, 4) as b
from lcgrppol
union
select lbgrppol.grpcontno grpcontno,lbgrppol.grppolno,riskcode,
substr(riskcode, 2, 1) as a,
makedate,
substr(riskcode, 3, 4) as b
from lbgrppol
order by grpcontno,a, makedate, b) t1 where t1.grpcontno = tGrpContNo) t2
where t2.grppolno = tPolNo;

if length(tRiderNo)<2 then
tRiderNo := concat('0',tRiderNo);
end if;

return(tRiderNo);
end getRiskSerialNo;

欢迎来到Java学習者论坛,转zai请注明地址:http://www.javaxxz.com.