Using Views in SQL - Firebird
Interview Questions
All Tutorials
Job SearchIn SQL a view behaves in many ways just like a regular table. You can select from it, with or without ORDER BY, GROUP BY, and WHERE clauses.
If it is naturally updatable, or it has been made updatable by triggers, and the appropriate SQL privileges exist, you can perform both positioned and searched update, insert, and delete operations on it, which will operate on the underlying table. You can also do the following:
- You can create views of views.
- You can process the output of a selection from a view in PSQL modules.
- You can perform a JOIN between a view and other views and tables. In some cases, you can join views with selectable stored procedures.
For a simple illustration, we will create a view and a stored procedure on the Employee table and join them. This is the view:
CREATE VIEW V_EMP_NAMES AS SELECT EMP_NO, LAST_NAME, FIRST_NAME FROM EMPLOYEE ^ COMMIT ^This is the stored procedure:
CREATE PROCEDURE P_EMP_NAMES RETURNS ( EMP_NO SMALLINT; EMP_NAME VARCHAR(35)) AS BEGIN FOR SELECT EMP_NO, FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEE INTO :EMP_NO, :EMP_NAME DO SUSPEND; END ^ COMMIT ^This is a query that joins them:
SELECT V.EMP_NO, V.LAST_NAME, V.FIRST_NAME, P.EMP_NAME FROM V_EMP_NAMES V JOIN P_EMPNAMES P ON V.EMP_NO = P.EMPNO ^
