Differences between revisions 1 and 9 (spanning 8 versions)
⇤ ← Revision 1 as of 2020-11-22 01:15:20 →
Size: 813
Editor: localhost
Comment:
← Revision 9 as of 2020-11-22 07:58:44 → ⇥
Size: 13507
Editor: localhost
Comment:
Deletions are marked like this. Additions are marked like this.
Line 59: Line 59:

DML: insert update delete

{{{
SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON


SQL> desc dept;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO NOT NULL NUMBER(2)
 DNAME VARCHAR2(14)
 LOC VARCHAR2(13)

SQL> insert into dept values(50,'NETWORK','BEJING');

1 row created.

SQL> insert into dept values(70,null,null);

1 row created.

SQL> insert into dept values(null,null,null);
insert into dept values(null,null,null)
                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")


SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70

6 rows selected.

}}}

{{{
SQL> create table dept1
  2 as
  3 select * from dept
  4 where 1=2;

Table created.


SQL> insert into dept1
  2 select * from dept;

6 rows created.

SQL> select * from dept1;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70

6 rows selected.


SQL> truncate table dept1;

Table truncated.

SQL> select * from dept1;

no rows selected


}}}


== update ==
{{{
SQL> update emp set sal=sal+200 where ename='SMITH';

1 row updated.


SQL> update emp set sal=sal+300 where sal>2000;

6 rows updated.

  1 select ename,sal from emp
  2* fetch next 3 rows only
SQL> /

ENAME SAL
-------------------- ----------
SMITH 1100
ALLEN 1700
WARD 1350


SQL> select sal from emp where ename='KING';

       SAL
----------
      5000

  1* select * from emp where ename='SMITH'
SQL> /

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
      7369 SMITH CLERK 7902 17-DEC-80 5000 20


}}}

== delete ==
DML delete by condition or line.
is able to rollback before commit

{{{
SQL> delete from emp;


14 rows deleted.

SQL> SQL> select * from emp;

no rows selected

SQL> rollback;
Rollback complete.

}}}

== truncate ==
  * delete all rows
cannot easy to undone

== LOCK ==
 * Terminal 1
{{{
SQL> update dept set loc='BEIJIN' where deptno=70;

1 row updated.


  1* select * from dept
SQL> /

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70 BEIJIN

6 rows selected.

}}}

  * Terminal 2
{{{
  1* select * from dept
SQL> /

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70

6 rows selected.

SQL> update dept set loc='SHANGHAI' where deptno=70;

will waiting...
}}}

  * Terminal 3
{{{
SQL> update dept set loc='GUANGZHOU' where deptno=60;

1 row updated.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70
 60 GUANGZHOU

7 rows selected.
}}}

DEPTNO 70(Related) not commit


== savepoint ==
{{{
SQL> rollback;
Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70

6 rows selected.

SQL> savepoint a;

Savepoint created.

SQL> insert into dept(deptno) values(73);

1 row created.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70
 73

7 rows selected.

SQL> insert into dept(deptno) values(71);

1 row created.

SQL> savepoint b;

Savepoint created.

SQL> insert into dept(deptno) values(72);

1 row created.

SQL> savepoint c;

Savepoint created.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70
 73
 71
 72

9 rows selected.

SQL> rollback to a;

Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70

6 rows selected.



}}}


== table ==
{{{
create table department (
dept_id number(2),
name char(20)
master char(10) constraint dept_mas_nn not null,
location varchar(100) constraint dept_loc_nn not null
)

create table department (
dept_id number(2),
name char(20),
master char(10) constraint dept_mas_nn not null,
location varchar(100) constraint dept_loc_nn not null
)
SQL> /

Table created.

SQL> select * from department;

no rows selected

SQL> desc department;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 DEPT_ID NUMBER(2)
 NAME CHAR(20)
 MASTER NOT NULL CHAR(10)
 LOCATION NOT NULL VARCHAR2(100)

}}}

{{{
create table student (
id number(4)
name char(10) constraint stu_nu

}}}

== read only table ==
{{{
ALTER TABLE emp READ ONLY;

-- perform table maintenance and then
-- return table back to read/write mode

ALTER TABLE emp READ WRITE;
}}}

=== rename table ===
{{{
rename student to stu
}}}
  
  * dba_tables
{{{
  1 select table_name,OWNER from dba_tables
  2* fetch next 5 rows only
SQL> /

TABLE_NAME OWNER
------------------------------ ------------------------------
ICOL$ SYS
COL$ SYS
IND$ SYS
TAB$ SYS
CLU$ SYS
}}}

== View ==

  1 create view v1
  2 as
  3 select empno,ename,sal,comm
  4* from emp
SQL> /

View created.

SQL> select * from v1;

     EMPNO ENAME SAL COMM
---------- -------------------- ---------- ----------
      7369 SMITH 5000
      7499 ALLEN 1700 300
      7521 WARD 1350 500
      7566 JONES 3375
      7654 MARTIN 1350 1400
      7698 BLAKE 3250
      7782 CLARK 2850
      7788 SCOTT 3400
      7839 KING 5400
      7844 TURNER 1600 0
      7876 ADAMS 1200

     EMPNO ENAME SAL COMM
---------- -------------------- ---------- ----------
      7900 JAMES 1050
      7902 FORD 3400
      7934 MILLER 1400

14 rows selected.

SQL> desc user_views;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME NOT NULL VARCHAR2(128)
 TEXT_LENGTH NUMBER
 TEXT LONG
 TEXT_VC VARCHAR2(4000)
 TYPE_TEXT_LENGTH NUMBER
 TYPE_TEXT VARCHAR2(4000)
 OID_TEXT_LENGTH NUMBER
 OID_TEXT VARCHAR2(4000)
 VIEW_TYPE_OWNER VARCHAR2(128)
 VIEW_TYPE VARCHAR2(128)
 SUPERVIEW_NAME VARCHAR2(128)
 EDITIONING_VIEW VARCHAR2(1)
 READ_ONLY VARCHAR2(1)
 CONTAINER_DATA VARCHAR2(1)
 BEQUEATH VARCHAR2(12)
 ORIGIN_CON_ID NUMBER
 DEFAULT_COLLATION VARCHAR2(100)
 CONTAINERS_DEFAULT VARCHAR2(3)
 CONTAINER_MAP VARCHAR2(3)
 EXTENDED_DATA_LINK VARCHAR2(3)
 EXTENDED_DATA_LINK_MAP VARCHAR2(3)
 HAS_SENSITIVE_COLUMN VARCHAR2(3)

SQL> SELECT VIEW_NAME,TEXT from USER_VIEWS;

VIEW_NAME
--------------------------------------------------------------------------------
TEXT
--------------------------------------------------------------------------------
V1
select empno,ename,sal,comm
from emp





SQL> create view v2
  2 as
  3 select ename,sal,job,dname,loc
  4 from emp,dept
  4 where emp.deptno=dept.deptno;
  5 /


SQL> create view v3
  2 as
  3 select deptno,avg(sal) as avg_sal,max(sal) max_sal, min(sal) min_sal,count(sal) count_sal,sum(sal) as sum_sal
  4 from emp
  5 group by deptno;

SQL> select * from v3;

    DEPTNO AVG_SAL MAX_SAL MIN_SAL COUNT_SAL SUM_SAL
---------- ---------- ---------- ---------- ---------- ----------
 30 1550 2250 1050 6 9300
 10 2550 4400 1400 3 7650
 20 2475 4000 1200 5 12375




  1 create view v4
  2 as
  3* select ename,sal from emp where sal>2000
SQL> /

View created.

SQL> select * from v4;

ENAME SAL
-------------------- ----------
SMITH 5000
JONES 3375
BLAKE 3250
CLARK 2850
SCOTT 3400
KING 5400
FORD 3400

7 rows selected.

SQL> update v4 set sal=sal-1000;

7 rows updated.

SQL> select * from v4;

ENAME SAL
-------------------- ----------
SMITH 4000
JONES 2375
BLAKE 2250
SCOTT 2400
KING 4400
FORD 2400

6 rows selected.


SQL> create or replace view v4
  2 as
  3 select ename,sal,comm
  4 from emp
  5 where sal>2000
  6 with check option
  7 /

View created.

SQL> select * from v4;

ENAME SAL COMM
-------------------- ---------- ----------
SMITH 4000
JONES 2375
BLAKE 2250
SCOTT 2400
KING 4400
FORD 2400

6 rows selected.

SQL> update v4 set sal=sal-1000;
update v4 set sal=sal-1000
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


}}}

== sequence ==
{{{
SQL> create sequence s1;

Sequence created.

SQL> select user from dual;

USER
-------------
SCOTT


SQL> select s1.currval from dual;

   CURRVAL
----------
  1

SQL> select s1.nextval from dual;

   NEXTVAL
----------
  2

SQL> /

   NEXTVAL
----------
  3

SQL> /

   NEXTVAL
----------
  4


  1* insert into dept values(s1.nextval,'xxxx','xxxx')
SQL> /

1 row created.

SQL> select * from dept;

    DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
 10 ACCOUNTING NEW YORK
 20 RESEARCH DALLAS
 30 SALES CHICAGO
 40 OPERATIONS BOSTON
 50 NETWORK BEJING
 70 BEIJIN
  1 xxxx xxxx


SQL> create sequence s2
  2 increment by 5
  3 start with 1000
  4 maxvalue 10000
  5 cycle
  6 cache 20
  7 /

Sequence created.

SQL> select s2.nextval from dual;

   NEXTVAL
----------
      1000

SQL> /

   NEXTVAL
----------
      1005

SQL> /

   NEXTVAL
----------
      1010

SQL> alter sequence s2 increment by 1;

Sequence altered.

SQL> select s2.nextval from dual;

   NEXTVAL
----------
      1011

SQL> /

   NEXTVAL
----------
      1012

SQL> /

   NEXTVAL
----------
      1013

}}}

== index ==
{{{
SQL> create index idx1 on emp(ename);

Index created.

SQL> column index_name format a10
SQL> column table_name format a20
SQL> select index_name,table_name,UNIQUENESS FROM user_indexes;

INDEX_NAME TABLE_NAME UNIQUENESS
---------- -------------------- ------------------
PK_DEPT DEPT UNIQUE
PK_EMP EMP UNIQUE
IDX1 EMP NONUNIQUE

SQL> select index_name,table_name,UNIQUENESS FROM user_indexes where INDEX_NAME='IDX1';

INDEX_NAME TABLE_NAME UNIQUENESS
---------- -------------------- ------------------
IDX1 EMP NONUNIQUE

SQL> create index idx4 on emp(ename,job);

Index created.

SQL> create index idx2 on emp(sal);

Index created.

SQL> select index_name,table_name,UNIQUENESS FROM user_indexes;

INDEX_NAME TABLE_NAME UNIQUENESS
---------- -------------------- ------------------
PK_DEPT DEPT UNIQUE
PK_EMP EMP UNIQUE
IDX1 EMP NONUNIQUE
IDX4 EMP NONUNIQUE
IDX2 EMP NONUNIQUE


}}}

Describe 首頁/2020-11-22 here.

* OCP Day2

Operator

SQL> select distinct job from emp where deptno=20;

JOB
------------------
CLERK
ANALYST
MANAGER

  1  select distinct job from emp where deptno=20
  2  UNION
  3* select distinct job from emp where deptno=30
SQL> /

JOB
------------------
ANALYST
CLERK
MANAGER
SALESMAN

  1  select distinct job from emp where deptno=20
  2  UNION all
  3* select distinct job from emp where deptno=30
SQL> /

JOB
------------------
CLERK
ANALYST
MANAGER
SALESMAN
CLERK
MANAGER

6 rows selected.

  1  select distinct job from emp where deptno=20
  2  minus
  3* select distinct job from emp where deptno=30
SQL> /

JOB
------------------
ANALYST

Managing Tables Using DMS Statements

DML: insert update delete 

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON


SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> insert into dept values(50,'NETWORK','BEJING');

1 row created.

SQL> insert into dept values(70,null,null);

1 row created.

SQL> insert into dept values(null,null,null);
insert into dept values(null,null,null)
                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO")


SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70

6 rows selected.

SQL> create table dept1 
  2  as
  3  select * from dept
  4  where 1=2;

Table created.


SQL> insert into dept1
  2  select * from dept;

6 rows created.

SQL> select * from dept1;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70

6 rows selected.


SQL> truncate table dept1;

Table truncated.

SQL> select * from dept1;

no rows selected

update

SQL> update emp set sal=sal+200 where ename='SMITH';

1 row updated.


SQL> update emp set sal=sal+300 where sal>2000;

6 rows updated.

  1  select ename,sal from emp
  2* fetch next 3 rows only
SQL> /

ENAME                       SAL
-------------------- ----------
SMITH                      1100
ALLEN                      1700
WARD                       1350


SQL> select sal from emp where ename='KING';

       SAL
----------
      5000

  1* select * from emp where ename='SMITH'
SQL> /

     EMPNO ENAME                JOB                       MGR HIREDATE            SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-DEC-80          5000                    20

delete

DML delete by condition or line. is able to rollback before commit 

SQL> delete from emp;


14 rows deleted.

SQL> SQL> select * from emp;

no rows selected

SQL> rollback;
Rollback complete.

truncate

  • delete all rows

cannot easy to undone

LOCK

  • Terminal 1 

SQL> update dept set loc='BEIJIN' where deptno=70;

1 row updated.


  1* select * from dept
SQL> /

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70                              BEIJIN

6 rows selected.
  • Terminal 2 

  1* select * from dept
SQL> /

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70

6 rows selected.

SQL> update dept set loc='SHANGHAI' where deptno=70;

will waiting...
  • Terminal 3 

SQL> update dept set loc='GUANGZHOU' where deptno=60; 

1 row updated.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70
        60                              GUANGZHOU

7 rows selected.

DEPTNO 70(Related) not commit

savepoint

SQL> rollback;
Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70

6 rows selected.

SQL> savepoint a;

Savepoint created.

SQL> insert into dept(deptno) values(73);

1 row created.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70
        73

7 rows selected.

SQL> insert into dept(deptno) values(71);

1 row created.

SQL> savepoint b;

Savepoint created.

SQL> insert into dept(deptno) values(72);

1 row created.

SQL> savepoint c;

Savepoint created.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70
        73
        71
        72

9 rows selected.

SQL> rollback to a;

Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70

6 rows selected.

table

create table department (
dept_id number(2),
name char(20)
master char(10) constraint dept_mas_nn not null,
location varchar(100) constraint dept_loc_nn not null
)

create table department (
dept_id number(2),
name char(20),
master char(10) constraint dept_mas_nn not null,
location varchar(100) constraint dept_loc_nn not null
)
SQL> /

Table created.

SQL> select * from department;

no rows selected

SQL> desc department;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPT_ID                                            NUMBER(2)
 NAME                                               CHAR(20)
 MASTER                                    NOT NULL CHAR(10)
 LOCATION                                  NOT NULL VARCHAR2(100)

create table student (
id number(4)
name char(10) constraint stu_nu

read only table

ALTER TABLE emp READ ONLY;

-- perform table maintenance and then
-- return table back to read/write mode

ALTER TABLE emp READ WRITE;

rename table

rename student to stu
  • dba_tables 

  1  select table_name,OWNER from dba_tables
  2* fetch next 5 rows only
SQL> /

TABLE_NAME                     OWNER
------------------------------ ------------------------------
ICOL$                          SYS
COL$                           SYS
IND$                           SYS
TAB$                           SYS
CLU$                           SYS

View

  • 1 create view v1 2 as 3 select empno,ename,sal,comm 4* from emp

SQL> /

View created.

SQL> select * from v1;

  • EMPNO ENAME SAL COMM

  • 7369 SMITH 5000 7499 ALLEN 1700 300 7521 WARD 1350 500 7566 JONES 3375 7654 MARTIN 1350 1400 7698 BLAKE 3250 7782 CLARK 2850 7788 SCOTT 3400 7839 KING 5400 7844 TURNER 1600 0 7876 ADAMS 1200
  • EMPNO ENAME SAL COMM

  • 7900 JAMES 1050 7902 FORD 3400 7934 MILLER 1400

14 rows selected.

SQL> desc user_views;

  • Name Null? Type

  • VIEW_NAME NOT NULL VARCHAR2(128) TEXT_LENGTH NUMBER TEXT LONG TEXT_VC VARCHAR2(4000) TYPE_TEXT_LENGTH NUMBER TYPE_TEXT VARCHAR2(4000) OID_TEXT_LENGTH NUMBER OID_TEXT VARCHAR2(4000) VIEW_TYPE_OWNER VARCHAR2(128) VIEW_TYPE VARCHAR2(128) SUPERVIEW_NAME VARCHAR2(128) EDITIONING_VIEW VARCHAR2(1) READ_ONLY VARCHAR2(1) CONTAINER_DATA VARCHAR2(1) BEQUEATH VARCHAR2(12) ORIGIN_CON_ID NUMBER DEFAULT_COLLATION VARCHAR2(100) CONTAINERS_DEFAULT VARCHAR2(3) CONTAINER_MAP VARCHAR2(3) EXTENDED_DATA_LINK VARCHAR2(3) EXTENDED_DATA_LINK_MAP VARCHAR2(3) HAS_SENSITIVE_COLUMN VARCHAR2(3)

SQL> SELECT VIEW_NAME,TEXT from USER_VIEWS;

VIEW_NAME

TEXT

V1 select empno,ename,sal,comm from emp

SQL> create view v2

  • 2 as 3 select ename,sal,job,dname,loc 4 from emp,dept 4 where emp.deptno=dept.deptno; 5 /

SQL> create view v3

  • 2 as 3 select deptno,avg(sal) as avg_sal,max(sal) max_sal, min(sal) min_sal,count(sal) count_sal,sum(sal) as sum_sal 4 from emp 5 group by deptno;

SQL> select * from v3;

  • DEPTNO AVG_SAL MAX_SAL MIN_SAL COUNT_SAL SUM_SAL

  • 30 1550 2250 1050 6 9300 10 2550 4400 1400 3 7650 20 2475 4000 1200 5 12375
  • 1 create view v4 2 as

    3* select ename,sal from emp where sal>2000

SQL> /

View created.

SQL> select * from v4;

ENAME SAL

SMITH 5000 JONES 3375 BLAKE 3250 CLARK 2850 SCOTT 3400 KING 5400 FORD 3400

7 rows selected.

SQL> update v4 set sal=sal-1000;

7 rows updated.

SQL> select * from v4;

ENAME SAL

SMITH 4000 JONES 2375 BLAKE 2250 SCOTT 2400 KING 4400 FORD 2400

6 rows selected.

SQL> create or replace view v4

  • 2 as 3 select ename,sal,comm 4 from emp

    5 where sal>2000 6 with check option 7 /

View created.

SQL> select * from v4;

ENAME SAL COMM

SMITH 4000 JONES 2375 BLAKE 2250 SCOTT 2400 KING 4400 FORD 2400

6 rows selected.

SQL> update v4 set sal=sal-1000; update v4 set sal=sal-1000

ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

}}}

sequence

SQL> create sequence s1;

Sequence created.

SQL> select user from dual;

USER
-------------
SCOTT


SQL> select s1.currval from dual;

   CURRVAL
----------
         1

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> /

   NEXTVAL
----------
         4


  1* insert into dept values(s1.nextval,'xxxx','xxxx')
SQL> /

1 row created.

SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
        50 NETWORK                      BEJING
        70                              BEIJIN
         1 xxxx                         xxxx


SQL> create sequence s2
  2  increment by 5
  3  start with 1000
  4  maxvalue 10000
  5  cycle
  6  cache 20
  7  /

Sequence created.

SQL> select s2.nextval from dual;

   NEXTVAL
----------
      1000

SQL> /

   NEXTVAL
----------
      1005

SQL> /

   NEXTVAL
----------
      1010

SQL> alter sequence s2 increment by 1;

Sequence altered.

SQL> select s2.nextval from dual;

   NEXTVAL
----------
      1011

SQL> /

   NEXTVAL
----------
      1012

SQL> /

   NEXTVAL
----------
      1013

index

SQL> create index idx1 on emp(ename);

Index created.

SQL> column index_name format a10
SQL> column table_name format a20
SQL> select index_name,table_name,UNIQUENESS FROM user_indexes;

INDEX_NAME TABLE_NAME           UNIQUENESS
---------- -------------------- ------------------
PK_DEPT    DEPT                 UNIQUE
PK_EMP     EMP                  UNIQUE
IDX1       EMP                  NONUNIQUE

SQL> select index_name,table_name,UNIQUENESS FROM user_indexes where INDEX_NAME='IDX1';

INDEX_NAME TABLE_NAME           UNIQUENESS
---------- -------------------- ------------------
IDX1       EMP                  NONUNIQUE

SQL> create index idx4 on emp(ename,job);

Index created.

SQL> create index idx2 on emp(sal);

Index created.

SQL> select index_name,table_name,UNIQUENESS FROM user_indexes;

INDEX_NAME TABLE_NAME           UNIQUENESS
---------- -------------------- ------------------
PK_DEPT    DEPT                 UNIQUE
PK_EMP     EMP                  UNIQUE
IDX1       EMP                  NONUNIQUE
IDX4       EMP                  NONUNIQUE
IDX2       EMP                  NONUNIQUE

首頁/2020-11-22 (last edited 2020-11-22 07:58:44 by localhost)