Oracle view 中变量使用

1、新建 package（类似java接口）

create or replace package p_view_param  is
    function setStringOne(strOne varchar2) return varchar2;
    function getStringOne  return varchar2;
    function setStringTwo(strTwo varchar2) return varchar2;
    function getStringTwo  return varchar2;
    function setStringThree(strThree varchar2) return varchar2;
    function getStringThree  return varchar2;

    function setNumberOne(numOne number) return number;
    function getNumberOne  return number;
    function setNumberTwo(numTwo number) return number;
    function getNumberTwo  return number;    
end p_view_param;

2、实现 package （类似java接口实现）

create or replace package body p_view_param is
  strOneValue varchar2(50);
  strTwoValue varchar2(50);
  strThreeValue varchar2(50);
  numOneValue number ;
  numTwoValue number ;
  function setStringOne(strOne varchar2) return varchar2 is
  begin
    strOneValue:=strOne;
    return strOneValue;
  end;
  function getStringOne  return varchar2 is
  begin
    return strOneValue;
  end;
  function setStringTwo(strTwo varchar2) return varchar2 is
  begin
    strTwoValue:=strTwo;
    return strTwoValue;
  end;
  function getStringTwo  return varchar2 is
  begin
    return strTwoValue;
  end;  
  function setStringThree(strThree varchar2) return varchar2 is
  begin
    strThreeValue:=strThree;
    return strThreeValue;
  end;
  function getStringThree  return varchar2 is
  begin
    return strThreeValue;
  end; 

  function setNumberOne(numOne number) return number is
  begin
    numOneValue:=numOne;
    return numOneValue;
  end;
  function getNumberOne  return number is
  begin
    return numOneValue;
  end;  
  function setNumberTwo(numTwo number) return number is
  begin
    numTwoValue:=numTwo;
    return numTwoValue;
  end;
  function getNumberTwo  return number is
  begin
    return numTwoValue;
  end; 

end p_view_param;

3、view 中使用变量

create or replace view vw_test as
select *
from test_table
where yyyymmdd = p_view_param.getStringOne()
and prod = p_view_param.getStringTwo();

4、view使用

select * from vw_test  
where p_view_param.setStringOne('20130519') = '20130519'
and p_view_param.setStringTwo('ALL') = 'ALL'