oracle视图创建以及查询方式

--建包

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;

--调用

直接当成表调用即可