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
posted on 2019-11-29 01:11:21 - ORACLE Tutorials
ORACLE - Trigger
posted on 2019-11-28 22:14:22 - ORACLE Tutorials
ORACLE - Cursor
posted on 2019-11-28 22:13:54 - ORACLE Tutorials
ChatGPT Prompt Examples
posted on 2023-06-21 22:37:19 - ChatGPT Tutorials
Chat GPT Key Use Cases
posted on 2023-06-21 21:03:17 - ChatGPT Tutorials
Prompt Frameworks
posted on 2023-06-21 19:33:06 - ChatGPT Tutorials