Original author: Jim Czuprynski
Replenish complicated demand with joint views
Use a connection view to bring the previous example to a higher level: allows us to access multiple table information. Extended the example:
Drop view hr.bv_employees;
Create or replace view hr.bv_employees
EMPID,
FNAME,
Lname,
Email,
Hire_date,
Job_ID,
Jobtitle,
Deptname)
AS
SELECT
E.employee_ID,
E.first_name,
E.last_name,
E.EMAIL,
E.HIRE_DATE,
E.JOB_ID,
j.job_title,
D.DEPARTMENT_NAME
From
Hr.employees E,
Hr.jobs J,
Hr.Departments D
WHERE E.JOB_ID = j.job_id
And E.DEPARTMENT_ID = D.DEPARTMENT_ID
/
Drop Public Synonym Employees;
Create Public Synonym Employees for BV_EMPLOYEES;
Grant SELECT, INSERT, UPDATE, DELETE ON HR.BV_EMPLOYEES TO OLTPROLE
Remember, when you create a complex view, Oracle disables each SQL statement of each SQL statement for the Basic Table, even for one. In addition, the INSERT statement can be applied to the primary key holding table in the view. The primary key holding table is its primary key or the only health view returned to the result set. In this example, it is an Employees table. All_updatable_columns data dictionary shows what can be updated.
SQL> SELECT
2 column_name,
3 Updatable,
4 INSERTABLE,
5 deletable
6 from all_updatable_columns
7 where = 'hr' and table_name = 'bv_employees';
Column_name Upd INS DEL
------------------------------ --- ---
Empid Yes Yes Yes YES YES
Fname Yes Yes Yes YES YES
Lname Yes YES YES YES
Email Yes YES YES YES YES
Hire_date yes yes yes yes
Job_id Yes Yes Yes Yes Yes
Jobtitle No No NO
DeptName No No NO
Use the package implementation of the package and data access standardization
The most extraordinary features of the Oracle package are their ability to package the data access to a database object. Our current development group is to describe database interface requirements for basic views of basic views using public properties and methods of packages (including traditional SET and GET object-oriented methods).
