--建包
create or replace package p_view_param is
function set_nodecode(nodecode varchar2) return varchar2;--打回节点
function set_endstatus(endstatus varchar2) return varchar2;--打回状态
function set_nodecodeo(nodecodeo varchar2) return varchar2;--通过节点
function set_endstatuso(endstatuso varchar2) return varchar2;--通过状态
function set_id(id number) return number;--数据id
function get_nodecode return varchar2;--打回节点
function get_endstatus return varchar2;--打回状态
function get_nodecodeo return varchar2;--通过节点
function get_endstatuso return varchar2;--通过状态
function get_id return number;--数据id
end p_view_param;
--包体内容
create or replace package body p_view_param is
--获取查询条件参数 返回body
nodecodeValue varchar2(80);
endstatusValue varchar2(30);
nodecodeoValue varchar2(80);
endstatusoValue varchar2(30);
idValue number;
function set_nodecode(nodecode varchar2) return varchar2 is
begin
nodecodeValue := nodecode; return nodecode;
end;
function set_endstatus(endstatus varchar2) return varchar2 is
begin
endstatusValue := endstatus; return endstatus;
end;
function set_nodecodeo(nodecodeo varchar2) return varchar2 is
begin
nodecodeoValue := nodecodeo; return nodecodeo;
end;
function set_endstatuso(endstatuso varchar2) return varchar2 is
begin
endstatusoValue := endstatuso; return endstatuso;
end;
function set_id(id number) return number is
begin
idValue := id; return id;
end;
function get_nodecode return varchar2 is
begin
return nodecodeValue;
end;
function get_endstatus return varchar2 is
begin
return endstatusValue;
end;
function get_nodecodeo return varchar2 is
begin
return nodecodeoValue;
end;
function get_endstatuso return varchar2 is
begin
return endstatusoValue;
end;
function get_id return number is
begin
return idValue;
end;
end p_view_param;
--创建视图
create or replace view v_firstpass as
select min(f2.id) as id,min(f2.time) as f2time,f2.applyid
from re_approvalflow f2
inner join (select min(f.id) as id, f.applyid as applyid
from re_approvalflow f
where f.nodecode =p_view_param.get_nodecode
and f.endstatus = p_view_param.get_endstatus group by f.applyid) fst
on f2.applyid = fst.applyid
and f2.id > fst.id
and f2.nodecode =p_view_param.get_nodecodeo and f2.endstatus=p_view_param.get_endstatuso group by f2.applyid;
create or replace view v_secondpass as
select min(f2.id) as id,min(f2.time) as f2time,f2.applyid
from re_approvalflow f2
where f2.id > p_view_param.get_id
and f2.nodecode =p_view_param.get_nodecodeo and f2.endstatus=p_view_param.get_endstatuso group by f2.applyid;
--调用
直接当成表调用即可