How to read value inside a Database View?-CodePudding

I have the below view.

CREATE OR REPLACE VIEW vw_myview AS
SELECT
emp_joining_dt,
emp_joining_dt 365 as emp_reminder_dt
FROM EMP
/

After 1 year of employee's joining, a reminder need to be sent. Daily job reads emp_reminder_dt and triggers it.

365 days is mostly constant. But, the requirement is that the company might wish to change it to 183 or 730.

So, the value 365 is configured in a table and read from DB.

CREATE OR REPLACE VIEW vw_myview AS
SELECT
emp_joining_dt,
emp_joining_dt MyPackage.get_reminder_days as emp_reminder_dt
FROM EMP
/

The problem with above approach is that everytime the view is queried it makes a call to database to read the value from the configuration table.

Concern has been raised that frequent query to Database should be avoided.

Is there a way to read the value 365 1st time from DB and then on from some cache. Then when the value is changed, read 1st time from DB and then on from some cache?

Using cache was just my guess but is there any other way?

CodePudding user response：

I would create a special table for changing parameters. One of the easiest way looks like this (SCD type 2):

create table emp_reminder_dt(
   emp_reminder_dt int,
   modified_dt date default sysdate,
   active varchar2(1 byte) not null check(active in ('Y','N')),
   active_uniq varchar2(1) invisible generated always as (case when active='Y' then 'Y' end),
   constraint c_active_uniq unique (active_uniq)
);

So you could have always one active record: invisible column active_uniq (and unique constraint on it) doesn't allow to have more than 1 active record.

Then just insert your current value:

insert into emp_reminder_dt(emp_reminder_dt,active)
  values(365, 'Y');
commit;

Now you can easily get active value:

CREATE OR REPLACE VIEW vw_myview AS
SELECT
emp_joining_dt,
emp_joining_dt
   (select r.emp_reminder_dt 
    from emp_reminder_dt r 
    where r.active_uniq='Y'
   ) 
  as emp_reminder_dt
FROM EMP

Later you can easily change active row to active='N' and add new record with new value, so you will have a simple history of changes.

Moreover, you can use other SCD types to allow different values for different time intervals.

CodePudding user response：

Any reasonable application has perhaps dozens of these 1off items that you do not want to hard code a value. For these I generally create a General_Parameters table. It is a generalization of the suggestion by @SayanMalakshinov. Something along the lines of:

create table general_parameters(
             parm_name    varchar2(64)
           , parm_integer integer 
           , parm_date    date
           , parm_text    varchar2(4000) 
           -- other parameter types
           , constraint general_parameters_pk
                        primary key (parm_name)  
           ); 

insert into general_parameters(parm_name, parm_integer) 
     values ('emp reminder interval',365);

Creating the view then is:

create or replace view emp_reminder_view as
   select emp_id
        , emp_joining_dt 
        , emp_joining_dt
             (select parm_integer
                from general_parameters
               where parm_name = 'emp reminder interval'
             )
            as emp_reminder_dt
    from emp;

Changing the reminder interval is a simple matter of updating a table entry.
Note: Added emp_id to view as 2 dates without any qualifier becomes useless in a hurry.