DATA365

In Oracle, view is a virtual table that does not physically exist.View does not store any data and It is stored in Oracle data dictionary. A view is created by a query joining one or multiple tables. Syntax : CREATE   VIEW <view_name> as SELECT   columns FROM    tables WHERE    conditions; Example let's take an example to create a view.In this example,we are creating two table emp and dept. EMP table:     CREATE TABLE  EMP      (    EMP_ID NUMBER,    EMP_NAME VARCHAR2(4000),    DEPTNO NUMBER     ) ;  / DEPT table: CREATE TABLE  DEPT       (    DEPTNO NUMBER ,           DNAME VARCHAR2(100)     )  ; /   Execute the following query to create a view name EMP_DET. Create View Query: CREATE VIEW EMP_DET AS   SELECT EMP.EMP_ID ,EMP.EMP_NAME,DEPT.DNAME FROM    EMP,DEPT WHERE   EMP.DEPTNO=DEPT.DEPTNO; Output: View created. You can now check the Oracle VIEW by this query:

Oracle support two types of temporary tables. Global Temporary Tables : Available since Oracle 8i and subject of this article. Private Temporary Tables : Available since Oracle 18c  Creation of Global Temporary Tables The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates the data should be deleted at the end of the transaction, or the end of the session. Example : CREATE GLOBAL TEMPORARY TABLE sample_temp_table (   ID           NUMBER,   DESC     VARCHAR2(20) ) ON COMMIT DELETE ROWS;  insert into sample_temp_table(ID,DESC) values(100,'Student 100 ');  SELECT COUNT(*) FROM sample_temp_table; Result :-  1 commit; SELECT COUNT(*) FROM sample_temp_table; Result :-  0

Oracle / PLSQL: INSERT STATEMENT:        The oracle INSERT statement is used to insert a single record or multiple records into table. Syntax for INSERT: For single record:- INSERT INTO Table_name ( Column1, Column2,…. ) VALUES (expression1, expression2,……..); For Multiple records:-   INSERT ALL INTO Table_name ( Column1, Column2,…. ) VALUES (expression1, expression2,……..); INTO Table_name ( Column1, Column2,…. ) VALUES (expression1, expression2,……..); Example for Insert Multiple rows: In below example we insert two rows in emp1 table in Oracle, Using a oracle INSERT statement  Insert all into emp1(ename, eid, doj, esal) values('a6',6,'02-02-2015',9000)                     into emp1(ename, eid, doj, esal) values('a5',5,'02-02-2015',9000)  select * from emp1;

Create Table :                             The Create table statement is used to create a new table in a database. Syntax for Create Table:                            CREATE TABLE Table_name ( Column1 data type (), Column2 data type(),…. );   The data type Parameters specifies the type of data the column can hold (e.g varchar, number, integer etc). Example for Create Table:                             In this example we create table called emp that contains four columns: ename,   eid, DOJ, esal:                             CREATE TABLE EMP1 ( ename varchar2 (20), eid number(6), DOJ date(), esal number(6));