ORACLE
ORACLE VIEWS
Create View
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE QUERIES
SELECT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER

CREATE VIEW



Oracle - Create View


View is a virtual table that does not physically exist. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.
A VIEW in Oracle is created by joining one or more tables. When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View. So, yes, you can update the data in an Oracle VIEW providing you have the proper privileges to the underlying Oracle tables.


Oracle CREATE VIEW


Syntax :
CREATE VIEW view_name AS  
SELECT columns  
FROM tables  
WHERE conditions;  

Parameters :

  • view_name: It specifies the name of the Oracle VIEW that you want to create.


    EXAMPLE :

    Suppliers table:
    CREATE TABLE  "SUPPLIERS"  
       (    "SUPPLIER_ID" NUMBER,   
        "SUPPLIER_NAME" VARCHAR2(4000),   
        "SUPPLIER_ADDRESS" VARCHAR2(4000)  
       )  
    /  
    

    Orders table:
    CREATE TABLE  "ORDERS"   
       (    "ORDER_NO." NUMBER,   
        "QUANTITY" NUMBER,   
        "PRICE" NUMBER  
       )  
    /  
    

    Execute the following query to create a view name sup_orders.


    Create View Query:

    CREATE VIEW sup_orders AS  
    SELECT suppliers.supplier_id, orders.quantity, orders.price  
    FROM suppliers  
    INNER JOIN orders  
    ON suppliers.supplier_id = supplier_id  
    WHERE suppliers.supplier_name = 'VOJO';  
    

    Output:
    View created.
    0.21 seconds
    

    You can now check the Oracle VIEW by this query:


    SELECT * FROM sup_orders;  
    
    
    Output:
    SUPPLIER_ID	QUANTITY	PRICE
      3	         35	          70
      3	         26	         125
      3	         18	         100
    3 rows returned in 0.00 seconds
    

    Oracle Update VIEW :


    In Oracle, the CREATE OR REPLACE VIEW statement is used to modify the definition of an Oracle VIEW without dropping it.


    Syntax:
    CREATE OR REPLACE VIEW view_name AS  
      SELECT columns  
      FROM table  
      WHERE conditions;   
    

    Example:

    Execute the following query to update the definition of Oracle VIEW called sup_orders without dropping it.


    CREATE or REPLACE VIEW sup_orders AS  
      SELECT suppliers.supplier_id, orders.quantity, orders.price  
      FROM suppliers  
      INNER JOIN orders  
      ON suppliers.supplier_id = supplier_id  
      WHERE suppliers.supplier_name = 'HCL';  
    

    You can now check the Oracle VIEW by this query:

    SELECT * FROM sup_orders;  
    
    

    Output:
    SUPPLIER_ID	QUANTITY	PRICE
          1	         35	         70
          1	         26	        125
          1	         18	        100
    row(s) 1 - 3 of 3		
    

    Oracle DROP VIEW :


    The DROP VIEW statement is used to remove or delete the VIEW completely.


    SYNTAX:
    DROP VIEW view_name;  
    
    

    Example:
    DROP VIEW sup_orders;  
    
    


    What Is Oracle

    What Is Oracle

    posted on 2019-11-29 01:11:21 - ORACLE Tutorials


    TRIGGER

    ORACLE - Trigger

    posted on 2019-11-28 22:14:22 - ORACLE Tutorials


    CURSOR

    ORACLE - Cursor

    posted on 2019-11-28 22:13:54 - ORACLE Tutorials


    MySQL TRIGGERS

    MySQL - Triggers

    posted on 2019-11-29 21:44:07 - mysql Tutorials


    Grant_ Revoke Privilege

    MySQL - Grant_ Revoke Privilege

    posted on 2019-11-26 23:15:04 - mysql Tutorials


    MySQL Vs SQL

    MySQL Vs SQL

    posted on 2019-11-25 05:02:26 - mysql Tutorials