第 2章.txt

oracle操作基础基础基础基础基础基础基础基础基础基础基础基础

例2.1
conn scott/tiger@oradb
DROP TABLE stu;
CREATE TABLE stu (
  sid varchar2(8) Primary Key,   
  name varchar2(20) Not NULL,  
  age number(3),
  addr varchar2(40));

例2.2 conn scott/tiger@oradb
DROP TABLE emp_old;
CREATE TABLE emp_old
   AS SELECT * FROM emp;
DESC emp_old;
SELECT * FROM emp_old;

例2.3
conn scott/tiger@oradb
DROP TABLE stu2;
CREATE TABLE stu2 (
  sid varchar2(8) Primary Key,  
  name varchar2(20) Not NULL,   
  age number(3),
  addr varchar2(40))
  Storage(  initial 100k 
           next 20k      
           Minextents 1   
           Maxextents 99 
           Pctincrease 10) 
  tablespace USERS       
             pctfree 10  
             pctused 40   
             initrans 2      
             maxtrans 10;    

例2.4 
DROP TABLE student CASCADE CONSTRAINTS;
CREATE TABLE student(
  sno    VARCHAR2(10) Primary Key,
  name   VARCHAR2(20) Not NULL UNIQUE,
  sex    VARCHAR2(2)  Check (sex IN('男','女')),
  age    NUMBER(2)    Check (age BETWEEN 16 And 20),
  class  VARCHAR2(7)  Not NULL);
DROP TABLE course CASCADE CONSTRAINTS;
CREATE TABLE course(
   cno    VARCHAR2(4) PRIMARY  KEY,
   cname  VARCHAR2(16) NOT NULL,
   remark varchar2(200) Default '专业课');
DROP TABLE score;
CREATE TABLE score
  ( sno    VARCHAR2(5)  References  STUDENT(SNO)  ON DELETE CASCADE,
    cno    VARCHAR2(4)  References  COURSE(CNO)  ON DELETE CASCADE,
    score  NUMBER(5,2),
    PRIMARY KEY(SNO,CNO) );
INSERT INTO student VALUES('01080','张三','男',19,'01-01');
INSERT INTO course(cno,cname) VALUES('1E23','Oracle数据库');
INSERT INTO score VALUES('01080','1E23',89);
commit;

例2.5
DROP TABLE student CASCADE CONSTRAINTS;
CREATE TABLE student(
  sno    VARCHAR2(10),
  name   VARCHAR2(20) Not NULL,
  sex    VARCHAR2(2),
  age    NUMBER(2),
  class  VARCHAR2(7)  Not NULL,
  CONSTRAINT stu_pk_sno PRIMARY KEY (sno),
  CONSTRAINT stu_unique_name UNIQUE(name),
  CONSTRAINT stu_check_sex CHECK (sex IN('男','女')),
  CONSTRAINT stu_check_age CHECK (age BETWEEN 16 And 20));
DROP TABLE course CASCADE CONSTRAINTS;
CREATE TABLE course(
   cno    VARCHAR2(4),
   cname  VARCHAR2(16) NOT NULL,
   remark varchar2(200) Default '专业课',
   CONSTRAINT course_pk_sno PRIMARY KEY (cno));
DROP TABLE score;
CREATE TABLE score
  ( sno    VARCHAR2(5),
    cno    VARCHAR2(4),
    score  NUMBER(5,2),
    CONSTRAINT score_fk_sno FOREIGN KEY(SNO) References  STUDENT(SNO)  ON DELETE CASCADE,
    CONSTRAINT score_fk_cno FOREIGN KEY(CNO) References COURSE(CNO)  ON DELETE CASCADE,
    CONSTRAINT score_pk_sno_cno PRIMARY KEY(SNO,CNO));
INSERT INTO student VALUES('01080','张三','男',19,'01-01');
INSERT INTO course(cno,cname) VALUES('1E23','Oracle数据库');
INSERT INTO score VALUES('01080','1E23',89);
commit;

例2.6
ALTER TABLE	course MODIFY (cname varchar2(20));  
ALTER TABLE	course MODIFY (cno varchar2(2));   

例2.7
DESC student
SELECT * FROM student;
ALTER TABLE	student ADD (birthday DATE DEFAULT sysdate); 
DESC student
SELECT * FROM student;

例2.8 
SELECT * FROM student;
ALTER TABLE student SET UNUSED (birthday);
DESC STUDENT;
ALTER TABLE student DROP UNUSED COLUMNS;
DESC STUDENT;
--ALTER TABLE student DROP COLUMN birthday;
SELECT * FROM student;
ALTER TABLE student ADD(birthday date DEFAULT sysdate);
SELECT * FROM student;

例2.9
ALTER TABLE emp
 ADD CONSTRAINT check_total_sal
  CHECK (sal + comm <= 5000) ;
INSERT INTO emp(deptno,empno,ename,sal,comm) VALUES(10,1010,'John',2000,400);
INSERT INTO emp(deptno,empno,ename,sal,comm) VALUES(10,1020,'Jordan',4800,500);

例2.10
//启动与禁用主键约束
//下面语句执行是不能成功的，因为：该主键被参照表score引用
ALTER TABLE student MODIFY PRIMARY KEY disable;
//禁用了score表的外键约束
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno disable;
//禁用student表的主键约束
ALTER TABLE student MODIFY PRIMARY KEY disable;
//使score表的外键启用，但是：失败。因为：参照的的列不是主键
ALTER TABLE score  MODIFY CONSTRAINT score_fk_sno enable;
//使student的主键恢复
ALTER TABLE student MODIFY PRIMARY KEY enable;
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno enable;

例2.11
//重命名约束
ALTER TABLE score RENAME CONSTRAINT score_fk_ sno TO score_fk_ stu_sno;
COL OWNER FORMAT A10
COL TABLE_NAME FORMAT A10
COL CONSTRAINT_NAME FORMAT A20
SELECT OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME
 FROM ALL_CONSTRAINTS WHERE owner='SCOTT'
 ORDER BY TABLE_NAME,CONSTRAINT_TYPE;

例2.12
//删除约束
ALTER TABLE emp DROP CONSTRAINT check_total_sal;
INSERT INTO emp(deptno,empno,ename,sal,comm) VALUES(10,1020,'Jordan',4800,500);

例2.13 
//修改student表的存储参数
ALTER TABLE student
    pctfree 10
    pctused 40
    initrans 2
    maxtrans 10;

例2.14
//重命名表和列
DESC student;
ALTER TABLE student RENAME COLUMN age TO sage;
DESC student;
ALTER TABLE student RENAME TO stu_temp;
RENAME stu_temp TO student;
SELECT * FROM tab;

例2.15
//删除表
DROP TABLE student;
DROP TABLE student CASCADE CONSTRAINTS;

例2.16
//创建按索引组织的表
DROP TABLE countries;
CREATE TABLE countries(
      country_id      CHAR(2),
      country_name    VARCHAR2(40),
      currency_name   VARCHAR2(25),
      currency_symbol  VARCHAR2(3),
      region          VARCHAR2(15),
      CONSTRAINT country_id_pk PRIMARY KEY (country_id )) 
    ORGANIZATION INDEX     
    INCLUDING   country_name     
    PCTTHRESHOLD 2          
    STORAGE               
     ( INITIAL  4K 
      NEXT  2K 
      PCTINCREASE 0 
      MINEXTENTS 1 
      MAXEXTENTS 1 ) 
    OVERFLOW   
    STORAGE      
      ( INITIAL  4K 
        NEXT  2K 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 1 );

例2.17
//创建簇 
CREATE CLUSTER emp_dept (deptno NUMBER(3))
  PCTUSED 80
  PCTFREE 5
  SIZE 600
TABLESPACE users
STORAGE (INITIAL 200K
         NEXT 300K
         MINEXTENTS 2
         MAXEXTENTS 20
         PCTINCREASE 33);
//删除簇
DROP TABLE cluster_dept;

//基于簇创建表
CREATE TABLE cluster_dept(
  DEPTNO NUMBER(3),
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13))
  CLUSTER emp_dept(deptno); 
DROP TABLE cluster _emp;
CREATE TABLE cluster_emp(
  EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(3))
  CLUSTER emp_dept(deptno);

例2.18 
INSERT INTO cluster_dept VALUES(100,'aaa','aaaloc');
DROP INDEX emp_dept_index;
CREATE INDEX emp_dept_index ON CLUSTER emp_dept
 STORAGE (INITIAL 50K
          NEXT 50K
          MINEXTENTS 2
          MAXEXTENTS 10
          PCTINCREASE 33)
 TABLESPACE users
    INITRANS 2
    MAXTRANS 5
    PCTFREE 5;
INSERT INTO cluster_dept VALUES(100,'aaa','aaaloc');
commit;

例2.19 
DROP TABLE part_emp;
CREATE TABLE part_emp(
  EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(3))
PARTITION BY RANGE (sal)
  (PARTITION SAL_1000 VALUES LESS THAN (1000) TABLESPACE users,
   PARTITION SAL_2000 VALUES LESS THAN (2000) TABLESPACE users,
   PARTITION SAL_3000 VALUES LESS THAN (3000) TABLESPACE users,
   PARTITION SAL_4000 VALUES LESS THAN (4000) TABLESPACE users,
   PARTITION SAL_max VALUES LESS THAN (MAXVALUE) tablespace users);
INSERT INTO part_emp SELECT * FROM emp;
commit;
SELECT ename,sal FROM part_emp PARTITION (sal_1000);
SELECT ename,sal FROM part_emp PARTITION (sal_2000);
SELECT ename,sal FROM part_emp PARTITION (sal_3000);
SELECT ename,sal FROM part_emp PARTITION (sal_4000);
SELECT ename,sal FROM part_emp PARTITION (sal_max);

例2.20 
conn system/system@oradb as sysdba
CREATE TABLESPACE ts1 DATAFILE '%ORACLE_HOME%\database\ts1.dbf' size 1M reuse;
CREATE TABLESPACE ts2 DATAFILE '%ORACLE_HOME%\database\ts2.dbf' size 1M reuse;
CREATE TABLESPACE ts3 DATAFILE '%ORACLE_HOME%\database\ts3.dbf' size 1M reuse;
CREATE TABLESPACE ts4 DATAFILE '%ORACLE_HOME%\database\ts4.dbf' size 1M reuse;
conn scott/tiger@oradb
CREATE TABLE test_hash_table(
   id NUMBER,
   name VARCHAR2 (60))
 PARTITION BY HASH (id)
 PARTITIONS 4
 STORE IN (ts1, ts2, ts3, ts4);
COLUMN TABLE_NAME FORMAT A20
COLUMN PARTITION_NAME FORMAT A20
SELECT TABLE_NAME, PARTITION_NAME
 FROM USER_TAB_PARTITIONS ORDER BY TABLE_NAME;
INSERT INTO TEST_HASH_TABLE VALUES(1,'AAAA');
INSERT INTO TEST_HASH_TABLE VALUES(2,'BBBB');
INSERT INTO TEST_HASH_TABLE VALUES(3,'CCCC');
INSERT INTO TEST_HASH_TABLE VALUES(4,'DDDD');
INSERT INTO TEST_HASH_TABLE VALUES(5,'EEEE');
COMMIT;
SELECT * FROM test_hash_table PARTITION (sys_p1);
SELECT * FROM test_hash_table PARTITION (sys_p2);
SELECT * FROM test_hash_table PARTITION (sys_p3);
SELECT * FROM test_hash_table PARTITION (sys_p4);

例2.21 
CREATE TABLE dept_hash (
   deptno NUMBER(3),
   deptname VARCHAR(32))
 STORAGE (INITIAL 10K)
 PARTITION BY HASH(deptno)
   ( PARTITION p1 TABLESPACE ts1, 
     PARTITION p2 TABLESPACE ts2,
     PARTITION p3 TABLESPACE ts3, 
     PARTITION p4 TABLESPACE ts4);

例2.22
DROP TABLE dept_list;
CREATE TABLE dept_list (
   deptno NUMBER(3),
   deptname VARCHAR(32),
   loc VARCHAR(32))
PARTITION BY LIST(loc)
   ( PARTITION p1 VALUES('东','东南','华东') TABLESPACE ts1, 
     PARTITION p2 VALUES('南','华南','中南') TABLESPACE ts2, 
     PARTITION p3 VALUES('西','西南','西北') TABLESPACE ts3, 
     PARTITION p4 VALUES('北','华北','东北') TABLESPACE ts4);
INSERT INTO dept_list VALUES(1,'销售1部','华南');
INSERT INTO dept_list VALUES (2,'销售2部','东南');
INSERT INTO dept_list VALUES (3,'销售3部','东北');
commit;
SELECT * FROM dept_list PARTITION (p1);
SELECT * FROM dept_list PARTITION (p2);
SELECT * FROM dept_list PARTITION (p3);
SELECT * FROM dept_list PARTITION (p4);

例2.23
DROP TABLE emp_comp;
CREATE TABLE emp_comp(
  EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(3))
PARTITION BY RANGE (sal) 
SUBPARTITION BY HASH(empno) SUBPARTITIONS 3 STORE IN (ts1, ts2,ts3)
  (PARTITION sal_comp1 VALUES LESS THAN (2000),
   PARTITION sal_comp2 VALUES LESS THAN (MAXVALUE));
COLUMN TABLE_NAME FORMAT A20
COLUMN PARTITION_NAME FORMAT A20
COLUMN SUBPARTITION_NAME FORMAT A20
SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME 
  FROM USER_TAB_SUBPARTITIONS 
  ORDER BY TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME;
INSERT INTO emp_comp SELECT * FROM emp;
COMMIT;
SELECT ename,sal FROM emp_comp PARTITION(sal_comp1);
SELECT ename,sal FROM emp_comp SUBPARTITION(SYS_SUBP5);
SELECT ename,sal FROM emp_comp SUBPARTITION(SYS_SUBP6);
SELECT ename,sal FROM emp_comp SUBPARTITION(SYS_SUBP7);
SELECT ename,sal FROM emp_comp PARTITION(sal_comp2);
SELECT ename,sal FROM emp_comp SUBPARTITION(SYS_SUBP8);
SELECT ename,sal FROM emp_comp SUBPARTITION(SYS_SUBP9);
SELECT ename,sal FROM emp_comp SUBPARTITION(SYS_SUBP10);

例2.24 
SET ECHO ON;
CONNECT /@oradb AS SYSDBA
CREATE OR REPLACE DIRECTORY dat_dir AS 'd:\data\emp\data';
CREATE OR REPLACE DIRECTORY log_dir AS 'd:\data\emp\log';
CREATE OR REPLACE DIRECTORY bad_dir AS 'd:\data\emp\bad';
GRANT READ ON DIRECTORY dat_dir TO scott;
GRANT WRITE ON DIRECTORY log_dir TO scott;
GRANT WRITE ON DIRECTORY bad_dir TO scott;
CONNECT scott/tiger@oradb
DROP TABLE empxt;
CREATE TABLE empxt (
empno NUMBER(4),
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr NUMBER(4),
  hiredate DATE,
  sal NUMBER(7,2),
  comm NUMBER(7,2),
  deptno NUMBER(2))
ORGANIZATION EXTERNAL     
(
  TYPE ORACLE_LOADER 
  DEFAULT DIRECTORY dat_dir
  ACCESS PARAMETERS
  (
    records delimited by newline
    badfile bad_dir:'empxt%a_%p.bad'
    logfile log_dir:'empxt%a_%p.log'
    fields terminated by ','
    missing field VALUES are NULL
    ( empno, ename, job, mgr,
      hiredate char date_format date mask "yyyy-mm-dd",
      sal, comm, deptno
    )
  )
  LOCATION ('emp1.dat', 'emp2.dat')    
)
PARALLEL    
REJECT LIMIT UNLIMITED;

ALTER SESSION ENABLE PARALLEL DML;  
SELECT * FROM empxt;
DELETE FROM emp_comp;
COMMIT;
INSERT INTO emp_comp  SELECT * FROM empxt;
COMMIT;

--emp1.dat
7369,SMITH,CLERK,7902,1980-12-17,800,0,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,0,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,0,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,0,10
--emp2.dat
7788,SCOTT,ANALYST,7566,1987-04-19,3000,0,20
7839,KING,PRESIDENT,,1981-11-17,5000,0,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23,1100,0,20
7900,JAMES,CLERK,7698,1981-12-03,950,0,30
7902,FORD,ANALYST,7566,1981-12-03,3000,0,20
7934,MILLER,CLERK,7782,1982-01-23,1300,0,10