1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

select into vs create view

Discussion in 'SQL PL/SQL' started by monkey, Jun 2, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    Please, what is the main difference between select into and create view in oracle?

    Many tanks!!!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Select .. into .. from .. is strictly a PL/SQL construct used to populate PL/SQL variables from the results of a query. It does not create any objects in the database. Create view DOES create a view (when successfully written and executed).


    Code (SQL):

    SQL> DECLARE
      2          myempno emp.empno%TYPE;
      3  BEGIN
      4          dbms_output.put_line(myempno);
      5
      6          SELECT empno INTO myempno FROM emp WHERE rownum = 1;
      7
      8          dbms_output.put_line(myempno);
      9  END;
     10  /
    7369


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> CREATE OR REPLACE VIEW empview
      2  AS SELECT empno, ename, job, sal, deptno
      3  FROM emp;


    VIEW created.


    SQL>
    SQL> DESC empview

     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     SAL                                                NUMBER(7,2)
     DEPTNO                                             NUMBER(2)


    SQL>
    SQL> SELECT * FROM empview;


         EMPNO ENAME      JOB              SAL     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK            800         20
          7499 ALLEN      SALESMAN        1600         30
          7521 WARD       SALESMAN        1250         30
          7566 JONES      MANAGER         2975         20
          7654 MARTIN     SALESMAN        1250         30
          7698 BLAKE      MANAGER         2850         30
          7782 CLARK      MANAGER         2450         10
          7788 SCOTT      ANALYST         3000         20
          7839 KING       PRESIDENT       5000         10
          7844 TURNER     SALESMAN        1500         30
          7876 ADAMS      CLERK           1100         20


         EMPNO ENAME      JOB              SAL     DEPTNO
    ---------- ---------- --------- ---------- ----------
          7900 JAMES      CLERK            950         30
          7902 FORD       ANALYST         3000         20
          7934 MILLER     CLERK           1300         10
          7939 DUKE       CEO             5000         10
          7949 PRINCE     CFO             5000         10
          7959 QUEEN      CIO             5000         10
          7869 JACK       PRESIDENT       5000         10


    18 ROWS selected.


    SQL>
     
     
    monkey likes this.
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    As David notes above, SELECT INTO and views have no relationship (or similarity) to each other. Perhaps you were thinking about CTAS (CREATE TABLE AS SELECT) vs CREATE VIEW?

    If so, CTAS creates a new physical table using data from one or more existing tables. The new table takes up space and has no further dependency on the table(s) it was created from after the statement completes.

    CREATE VIEW creates a data dictionary object that is nothing more than a stored query of one or more tables in the database. A view can be queried as if it were a table, but does not itself contain any data or table up space (beyond a tiny amount in the data dictionary storing the text of the SQL statement). The view is dependent on the table(s) referenced and will become invalid if they are changed or dropped.
     
    monkey likes this.