Using Views in SQL - Firebird

« Previous Topics
Dropping A View
Modify A View Definition?
Read-only And Updatable Views
Next Topics »
Using Query Plans For Views
Other Derived Tables
The Acid Properties

In 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 ^
« Previous Topics
Dropping A View
Modify A View Definition?
Read-only And Updatable Views
Next Topics »
Using Query Plans For Views
Other Derived Tables
The Acid Properties

List of Tutorials

List of Topics

Interview Questions

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics