Oracle Learning (10): View, index, serial number, synonym

1. Knowledge Points: Can be read against the following record screen

 view, sequence, index, Synonym Sql>--view: Virtual table sql>--View advantages: Simplify complex queries, limit data access (more banks), provide independent data, the same data can be displayed differently sql>-first view: Employee number name annual salary sql> Create VIEW View1 2 as 3 select Empno,ename,sal,sal*12 annlsal from EMP;  Sql>--If the display does not have permissions, you need authorization, see 1-1sql>--Complex view sql> Create or replace view view2--if VIEW2 exists replace, otherwise create 2 as 3 Select EMPNO,ENAME,SAL,SAL*12 annlsal,dname 4 from EMP E, Dept d 5 where E.deptno=d.deptno 6 with read only;--readonly View sq L>--sequence sequencesql> create sequence myseq; sql>-Insert Data sql> INSERT INTO TESTSEQ values (myseq. Nextval, ' AAA '); Sql>-sequence discontinuous:sql>--1. Memory, length 20sql>--2. A sequence is a public object sql>--3. Rollbacksql>--Create INDEX Table sql>--create index myindex on EMP (DEPTNO); Sql>--synonyms, which is alias Sql>--for HR. Employees alias sql> create synonym hremp for hr. EMPLOYEES; 

Figure 1-1 Grant to Scott

2. Actual execution of the results screen under Sqlplus

Sql> host clssql>--View sql>--First view: Employee number name monthly salary sql> CREATE VIEW view1 2 as 3 select Empno,ename,sal,sal*1 2 annlsal from Emp;create view View1 * 1th line error: ORA-01031: Insufficient permissions sql>/view created. Sql> desc View1 is the name empty? Type-------------------------------------------------------------------------------------------------------------                                                                               --------------------------------------EMPNO Not NULL number (4) ename VARCHAR2 (                                                                                      SAL number (7,2) annlsal     Numbersql> select * from View1;                                                EMPNO ename SAL Annlsal                                                                ---------- ---------- ----- ----------       7369 SMITH 800                                                                                                                      9600                                                                                                                      7499 ALLEN 1600 19200                                                                                                                      7521 WARD 1250 15000                                                                                                                      7566 JONES 2975 35700                                                                                                                      7654 MARTIN 1250 15000 7698 B LAKE 2850 34200 77                                                                                                                      CLARK 2450 29400                                                                                                                      7788 SCOTT 3000 36000                                                                                                                      7839 KING 5000 60000                                                                                                                      7844 TURNER 1500 18000       7876 ADAMS                                                                                                                     1100 13200                                                                                                  EMPNO ename SAL Annlsal              ---------- ---------- ----- ----------                                                                                                                      7900 JAMES 950 11400                                                                                                                      7902 FORD 3000 36000 7934 MILLER 1                                                                                                                300 15600 14 rows have been selected.       sql> CREATE VIEW view2 2 as 3 select Empno,ename,sal,sal*12 annlsal,dname 4 from EMP E, Dept d 5 where E.deptno=d.deptno; view is created.     Sql> select * from View2;                                                                                                          EMPNO ename SAL annlsal dname ---------- ---------- ----- ---------- --------------7369 SMIT                                                                                                             H-9600                                                                                                                7499 ALLEN 19200 SALES                                                                                                                7521 WARD 1250 15000 SALES                                                                                                             7566 JONES 2975 35700 7654 MARTIN 12                                                                                                                15000 SALES                                                                                                   7698 BLAKE 2850 34200 SALES             7782 CLARK 2450 29400 ACCOUNTING                                                                                                             7788 SCOTT 36000                                                                                                           7839 KING 60000 ACCOUNTING      7844 TURNER 1500                                                                                                                18000 SALES                                                                                                            7876 ADAMS 1100 13200                                                                                                          EMPNO ename SAL annlsal dname                                        ---------- ---------- ----- ---------- --------------                                                               7900 JAMES 950 11400 SALES 7902 FORD 3000 3600 0 The 793                                                                                                     4 MILLER 1300 15600 ACCOUNTING 14 rows have been selected. Sql> Create or Replace View View2 2 as 3 select Empno,ename,sal,sal*12 annlsal,dname 4 from EMP E , Dept d 5 where E.deptno=d.deptno 6 with Read only; View is created. Sql> host clssql>--sequence sequencesql> create sequence myseq; sequence created. Sql> CREATE TABLE Testseq 2 (Tid number,tname varchar2 (20)); Sql> Select Myseq. Currval from Dual;select Myseq. Currval from Dual * 1th line error: ORA-08002: Sequence myseq. Currval has not defined sql> select Myseq in this session. NEXTval from dual;                                                                                                                                            Nextval                                                                                                                                                     ----------                                                                                                                                            1 1 rows have been selected.                                                                                                                                            Sql>/Nextval                                                                                                                                                     ----------                                                                                                                                            2 1 rows have been selected. Sql> Select Myseq.   Currval from dual;         Currval                                                                                                                          ----------                                                                                                                                            2 1 rows have been selected. sql> INSERT into TESTSEQ values (myseq. Nextval, ' AAA '); 1 rows have been created. Sql>/created 1 rows. Sql>/created 1 rows. Sql> commit; commit completed.       Sql> select * from Testseq;                                                                                                                                      TID Tname                                                                                                                                ---------- --------------------                                                                                                   3 AAA                                              4 AAA                                                                                                                                        5 AAA 3 rows have been selected. sql> INSERT into TESTSEQ values (myseq. Nextval, ' AAA '); 1 rows have been created. Sql>/created 1 rows. sql> rollback; fallback is complete. sql> INSERT into TESTSEQ values (myseq. Nextval, ' AAA '); 1 rows have been created.       Sql> select * from Testseq;                                                                                                                                      TID Tname                                                                                                                                ---------- --------------------                                                                                                                                                 3 AAA4 AAA                                                                                                                                                 5 AAA                                                                                                                                        8 AAA 4 rows have been selected. sql>/*sql> sequence discontinuous:sql> 1. Memory, length 20sql> 2. The sequence is a public object sql> 3. The rollback sequence is discontinuous:sql> 1. Memory, length 20sql> 2. The sequence is a public object sql> 3. Rollbacksql> */sql> host clssql>--synonym sql> show useruser for "SCOTT" sql> Select COUNT (*) from HR. Employees;select Count (*) from HR.                                                                                                                                            EMPLOYEES * 1th line error: ORA-00942: Table or view does not exist sql>/COUNT (*)                                        ----------                                                                                                           107 1 rows have been selected. Sql>--for HR. Employees alias sql> create synonym hremp for hr. Employees;create synonym hremp for hr. employees* 1th Line error: ORA-01031: Insufficient permissions <span style= "White-space:pre" ></span>SQL>/synonyms created.  Sql> Select COUNT (*) from hremp;                                                                                                                                            COUNT (*)                                                                                                                                                   ----------                                                                                                                                            107 1 rows have been selected. Sql> Spool Off