orcle带参视图的创建和使用

oracle带参视图使用方式

步骤一 创建包

CREATE OR REPLACE package p_view_reqParam is
–定义开始日期–
function set_X(X varchar2) return varchar2;
function get_X return varchar2;
–定义结束日期–
function set_Y(Y varchar2) return varchar2;
function get_Y return varchar2;

end p_view_reqParam;

步骤二 创建包体
create or replace package body p_view_reqParam is
paramX varchar2(20);
paramY varchar2(20);

function set_X(X varchar2) return varchar2 is
begin
paramX:=X;
return X;
end;

function get_X return varchar2 is
begin
return paramX;
end;

function set_Y(Y varchar2) return varchar2 is
begin
paramY:=Y;
return Y;
end;

function get_Y return varchar2 is
begin
return paramY;
end;

end p_view_reqParam;

第三步 创建视图

CREATE OR REPLACE VIEW v_queryCon AS
SELECT *from tableA a where a.x = p_view_reqParam.get_X() and a.y=p_view_reqParam.get_Y()

第四步 查询方式
SELECT *
FROM v_queryCon
WHERE p_view_reqParam.set_X(‘1’)=‘1’
and p_view_reqParam.set_Y(‘2’) = ‘2’