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