2.1.3.1 Database Views

A database view is a named SQL query you can reuse to simplify data access across your APEX applications.

You can create a database view to name a reusable SELECT statement. It identifies a curated subset of data appropriate to a specific business use case. The query can filter and join the data from related tables as needed. Then you can use the view by name in your application pages and business logic. The Object Browser in App Builder lets you create and maintain your views.

For example, the statement below creates a view named mid_level_clerks_and_mgrs using the SELECT statement from a previous section:

CREATE OR REPLACE VIEW mid_level_clerks_and_mgrs AS
SELECT emp.ename, emp.sal, dept.loc
  FROM emp
  JOIN dept ON emp.deptno = dept.deptno
 WHERE sal BETWEEN 800 AND 2450
   AND job IN ('CLERK','MANAGER')

Once you define the view, you can select data from it just like a table, including adding additional filtering and ordering:

SELECT ename, sal, loc
  FROM mid_level_clerks_and_mgrs
 WHERE loc LIKE '%A%'
ORDER BY sal

This produces a result of:

ENAME             SAL LOC       
---------- ---------- ----------
SMITH             800 DALLAS    
JAMES             950 CHICAGO   
ADAMS            1100 DALLAS