·  SQL – View

 

- Info:

  - View is table-like structure that presents only selected columns and records from one or more tables.

    It can present them in any way possible by SELECT statement.

  - Once view is created you can use SELECT statement on it but you can't UPDATE, INSERT or DELETE records.

 

- Create – From Subquery:

  - New VIEW is created by using columns and records from SELECT statement.

    CREATE VIEW SELECTED_PEOPLE AS

      SELECT NAME, AGE

      FROM   PEOPLE

      WHERE  BORN > SYSDATE - 100

 

  - If you define column names for new view, their number and type must match the number and type of columns in select.

    CREATE VIEW SELECTED_PEOPLE (PERSON_NAME, PERSON_AGE) AS

      SELECT NAME, AGE

      FROM   PEOPLE

    WHERE  BORN > SYSDATE - 100

 

  - If you use CREATE OR REPLACE statement, view will be created even if it already exists.

    CREATE OR REPLACE VIEW SELECTED_PEOPLE AS

      SELECT NAME, AGE

      FROM   PEOPLE

      WHERE  BORN > SYSDATE - 100

 

- Delete:

  - To delete view use DROP statement.

  - When view is droped no data is lost unlike when droping a table.

    DROP VIEW MYVIEW;

 

- Describe:

  - To display column names and their types use DESCRIBE statement.

    DESCRIBE MYVIEW;

 

- Comment – Create:

  - Each view and it's column can have a comment.

  - Notice that you must use TABLE keyword although you are ssetting comment to a view.

    COMMENT ON TABLE   MYVIEW

    IS                'Test view.';

    

    COMMENT ON COLUMN MYVIEW.COLUMN1

    IS                'Test column.';

 

- Comment – Delete:

  - View and column comments are deleted simply by setting them to an empty string.

  - Notice that you must use TABLE keyword although you are ssetting comment to a view.

    COMMENT ON TABLE   MYVIEW    

    IS                '';

 

    COMMENT ON COLUMN MYVIEW.COLUMN1 

    IS                '';