Oracle view中变量使用
1、新建 package（类似java接口）
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接口实现）
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 中使用变量
Java代码
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使用
Java代码
select * from vw_test
where p_view_param.setStringOne('20130519') = '20130519'
and p_view_param.setStringTwo('ALL') = 'ALL'
