TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

Oracle View

Oracle View for beginners and professionals with examples on insert, select, update, delete, table, view, join, key, functions, procedures, indexes, cursor etc.

<< Back to ORACLE

Oracle View

In Oracle, 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 is created by a query joining one or more 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:

Let's take an example to create view. In this example, we are creating two tables suppliers and orders first.

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;
Next TopicOracle Queries




Related Links:

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf