Oracle---视图插参数

1.创建一个参数Package

create or replace package p_view_param is -- Author : ALANN -- Created : 2017/12/22 15:59:53 -- Purpose : function set_param1(begin_date date) return date; function get_param1 return date; function set_param2(end_date date) return date; function get_param2 return date; end p_view_param;

create or replace package body p_view_param is param1 date; param2 date; function set_param1(begin_date date) return date is begin param1:=begin_date; return begin_date; end; function get_param1 return date is begin return param1; end; function set_param2(end_date date) return date is begin param2:=end_date; return end_date; end; function get_param2 return date is begin return param2; end; end p_view_param;

2.创建一个VIEW

CREATE OR REPLACE VIEW TEST_TABLE1_V AS SELECT "NAME","AGE","TIME" FROM TEST_TABLE1 WHERE TIME=P_VIEW_PARAM.get_param1();

VIEW对应的TABLE里面的数据

查询结果

3.为什么要使用VIEW插参数

之前工作中有遇到COST特别高VIEW,查询非常慢，使用可以VIEW插参数可以有效的优化VIEW的查询速度。

有兴趣的可以试一下。