在Oracle中创建带参数的视图

1.通常oracle的视图是不能带参数的，但有时候可以通过变通来实现。比如创建了一个视图

CREATE OR REPLACE VIEW REVENUE_STATISTICS AS
SELECT *  FROM A

2.如果需要给定where条件 where a.year='2014',这时候可以通过创建包和包体来实现设置和传递参数

//创建包

create or replace package p_view_param is

  function set_param(num number) return number;

  function get_param return number;

end p_view_param;

//创建包体

create or replace package body p_view_param is

paramValue number;

function set_param(num number) return number is

begin

paramValue := num;

return num;

end;

function get_param return number is

begin

return paramValue;

end;

end p_view_param;

 

3.然后修改视图语句

CREATE OR REPLACE VIEW REVENUE_STATISTICS AS
SELECT *  FROM A  WHERE A.YEAR = p_view_param.get_param()

4.调用slq语句

SELECT * FROM REVENUE_STATISTICS WHERE P_VIEW_PARAM.SET_PARAM(2014)=2014