
P86
5.1
CONN stu01/stu01pwd@orcl
DROP TABLE stu;
CREATE TABLE stu (
  sid VARCHAR2(8) PRIMARY KEY, 
  name VARCHAR2(20) NOT NULL,  
  age NUMBER(3),
  addr VARCHAR2(40));
CONN /@orcl AS SYSDBA
ALTER USER stu01 QUOTA UNLIMITED ON USERS;
CONN stu01/stu01pwd@orcl
CREATE TABLE stu (
  sid VARCHAR2(8) PRIMARY KEY, 
  name VARCHAR2(20) NOT NULL,  
  age NUMBER(3),
  addr VARCHAR2(40));
INSERT INTO stu VALUES('08050301','',20,'ڽ');
INSERT INTO stu VALUES('08050302','',20,'');
COMMIT;

P87
5.2
CONN scott/tiger@orcl
DROP TABLE emp_bak;
CREATE TABLE emp_bak
  AS SELECT * FROM emp;
DESC emp_bak
SELECT * FROM emp_bak;

P88
5.3
CONN stu01/stu01pwd@orcl
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;  

P90
SELECT table_name,constraint_type,
      constraint_name,search_condition
  FROM  user_constraints
  ORDER BY table_name , constraint_type;
SELECT table_name , column_name , constraint_name
  FROM  user_cons_columns
  ORDER BY table_name , column_name;
5.4
CONN scott/tiger@orcl
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;

P91
5.5
CONN scott/tiger@orcl
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;

P91
5.6
CONN scott/tiger@orcl
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 20
    STORAGE  
     ( INITIAL  4K 
      NEXT  2K 
      PCTINCREASE 0 
      MINEXTENTS 1 
      MAXEXTENTS 1 ) 
    OVERFLOW  
    STORAGE 
      ( INITIAL  4K 
        NEXT  2K 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 1 );

P93
5.7
CONN scott/tiger@orcl
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);

P94
5.8
CONN scott/tiger@orcl
INSERT INTO cluster_dept VALUES(100,'ܲð','ó11');
CREATE INDEX emp_dept_index ON CLUSTER emp_dept
  STORAGE (INITIAL 50K
           NEXT 50K
           MINEXTENTS 2
           MAXEXTENTS 10
           PCTINCREASE 0)
  TABLESPACE users 
  INITRANS 2
  MAXTRANS 5
  PCTFREE 5;
INSERT INTO cluster_dept VALUES(100,'ܲð','ó11');

P96
5.9
CONN scott/tiger@orcl
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;
SET PAGESIZE 200
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);
SELECT ename,sal FROM part_emp;

P97
5.10
CONN system/systempwd@orcl AS SYSDBA
DROP TABLESPACE ts1 INCLUDING CONTENTS;
DROP TABLESPACE ts2 INCLUDING CONTENTS;
DROP TABLESPACE ts3 INCLUDING CONTENTS;
DROP TABLESPACE ts4 INCLUDING CONTENTS;
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@orcl
DROP TABLE hash_emp;
CREATE TABLE hash_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 HASH (empno)
  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 hash_emp SELECT * FROM emp;
COMMIT;
SELECT empno,ename,job FROM hash_emp PARTITION (SYS_P21);
SELECT empno,ename,job FROM hash_emp PARTITION (SYS_P22);
SELECT empno,ename,job FROM hash_emp PARTITION (SYS_P23);
SELECT empno,ename,job FROM hash_emp PARTITION (SYS_P24);

P99
5.11 
CREATE TABLE dept_hash (
   deptno NUMBER(2),
   dname VARCHAR(14),
   loc VARCHAR2(13))
  STORAGE (INITIAL 10K)
  PARTITION BY HASH(deptno)
    ( PARTITION p1 TABLESPACE ts1, 
      PARTITION p2 TABLESPACE ts2,
      PARTITION p3 TABLESPACE ts3, 
      PARTITION p4 TABLESPACE ts4
    );

P100
5.12
CONN scott/tiger@orcl
DROP TABLE sales_records;
CREATE TABLE sales_records (
    empno NUMBER(4),     
    part_no VARCHAR2(12),   
    qty NUMBER (7,3),   
    unit_price NUMBER (9,2),   
    total_prices NUMBER (10,2), 
    loc VARCHAR2(12))    
  PARTITION BY LIST(loc)
     ( PARTITION sales_records_p1 VALUES('','') TABLESPACE ts1, 
       PARTITION sales_records_p2 VALUES('','') TABLESPACE ts2, 
       PARTITION sales_records_p3 VALUES('','') TABLESPACE ts3, 
       PARTITION sales_records_p4 VALUES('','') TABLESPACE ts4);
INSERT INTO sales_records
  VALUES(1001,'080091',1,3500,3500,'');
INSERT INTO sales_records
  VALUES(1001,'103511',3,700,2100,'');
INSERT INTO sales_records
  VALUES(1002,'080091',3,3500,10500,'');
COMMIT;
SELECT * FROM sales_records PARTITION (sales_records_p2);
SELECT * FROM sales_records PARTITION (sales_records_p4);
 
P101
5.13
CONN scott/tiger@orcl
DROP TABLE stock;
CREATE TABLE stock (
   owner_id VARCHAR2(10), 
   stock_id VARCHAR2(12), 
   stock_name VARCHAR2(20), 
   unit_price NUMBER(7,2), 
   available_qty NUMBER(7),  
   catalog VARCHAR2(20),     
   remark VARCHAR2(400))  
  PARTITION BY RANGE (unit_price) 
    SUBPARTITION BY HASH(stock_id)
      SUBPARTITIONS 4 STORE IN (ts1,ts2,ts3,ts4)
  (PARTITION price_1000 VALUES LESS THAN (1000),
   PARTITION price_3000 VALUES LESS THAN (3000),
   PARTITION price_5000 VALUES LESS THAN (5000),
   PARTITION price_10000 VALUES LESS THAN (10000),
   PARTITION price_max VALUES LESS THAN (MAXVALUE));
SET PAGESIZE 200
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
  WHERE table_name='STOCK'
  ORDER BY partition_name,subpartition_name;

P103
5.14
SET ECHO ON
CONNECT /@orcl 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@orcl
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;

CONNECT scott/tiger@orcl
ALTER SESSION ENABLE PARALLEL DML; 
SET LINESIZE 100
SET PAGESIZE 200
SELECT * FROM empxt;
DELETE FROM hash_emp;
COMMIT;
INSERT INTO hash_emp  SELECT * FROM empxt;
COMMIT;
SELECT * FROM hash_emp;

CONNECT scott/tiger@orcl
SET HEADING OFF
SPOOL d:\data\emp\data\emp.txt
SELECT EMPNO||','||ENAME||','||JOB||','||MGR||','
         ||to_char(HIREDATE,'yyyy-mm-dd')||','
         ||SAL||','||COMM||','||DEPTNO
  FROM EMP
  ORDER BY empno;
SPOOL OFF
SET HEADING ON

P105

CONN scott/tiger@orcl
DROP TYPE cust_address_typ FORCE;
CREATE TYPE cust_address_typ AS OBJECT
( street_address VARCHAR2(40),
  postal_code VARCHAR2(10),
  city VARCHAR2(30),
  state_province VARCHAR2(10),
  country_id CHAR(2)
);
/
DROP TABLE customers;
CREATE TABLE customers
( customer_id NUMBER(6),
  cust_first_name VARCHAR2(20)
         CONSTRAINT cust_fname_nn NOT NULL,
  cust_last_name VARCHAR2(20)
         CONSTRAINT cust_lname_nn NOT NULL,
  cust_address cust_address_typ
);
INSERT INTO customers VALUES
  (1001,'Ҽ','',
   cust_address_typ('̺','100000','','㽭','01'));
UPDATE customers c SET c.cust_address.postal_code ='312000'
  WHERE c.cust_address.city = '';
SELECT customer_id,cust_last_name||cust_first_name,
       c.cust_address.postal_code
  FROM customers c;

P107
()Ķ
CONN scott/tiger@orcl
CREATE TYPE data_typ AS OBJECT
( year NUMBER,
  MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER
);
/
CREATE TYPE BODY data_typ IS
  MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
    BEGIN
     RETURN (year + invent);
   END;
END;
/
ALTER TYPE data_typ
  ADD MEMBER FUNCTION qtr(der_qtr DATE)
      RETURN CHAR CASCADE;
CREATE OR REPLACE TYPE BODY data_typ IS
  MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS
    BEGIN
      RETURN (year + invent);
    END;
  MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS
    BEGIN
      IF (der_qtr < TO_DATE('01-APR', 'DD-MON')) THEN
         RETURN 'FIRST';
      ELSIF (der_qtr < TO_DATE('01-JUL', 'DD-MON')) THEN
         RETURN 'SECOND';
      ELSIF (der_qtr < TO_DATE('01-OCT', 'DD-MON')) THEN
         RETURN 'THIRD';
      ELSE
         RETURN 'FOURTH';
      END IF;
   END;
END;
/
Ӧþ
DROP TABLE test_type;
CREATE TABLE test_type(
 a number(3),
 b data_typ);
INSERT INTO test_type VALUES
  (1,data_typ(1998));
SELECT a,t.b.year,t.b.prod(10) FROM test_type t;
DROP TYPE BODY data_typ;
DROP TYPE data_typ;

P108
Ƕױ
CONN scott/tiger@orcl
DROP TYPE people_typ FORCE;
CREATE TYPE people_typ AS OBJECT (
  last_name VARCHAR2(25),
  department_id NUMBER(4),
  salary NUMBER(8,2));
/
DROP TYPE people_tab_typ FORCE;
CREATE TYPE people_tab_typ AS TABLE OF people_typ;
/
DROP TABLE hr_info CASCADE CONSTRAINTS;
CREATE TABLE hr_info (
    department_id NUMBER(4),
    location_id NUMBER(4),
    manager_id NUMBER(6),
    people people_tab_typ) 
  NESTED TABLE people STORE AS people_stor_tab;
INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
INSERT INTO TABLE(SELECT h.people FROM hr_info h
                  WHERE h.department_id = 280)
  VALUES ('Smith', 280, 1750);
UPDATE TABLE(SELECT h.people FROM hr_info h
             WHERE h.department_id = 280) p
  SET p.salary = p.salary + 100;
DELETE TABLE(SELECT h.people FROM hr_info h
             WHERE h.department_id = 280) p
  WHERE p.salary > 1700;

P109
CONN /@orcl AS SYSDBA
ALTER USER stu01 QUOTA UNLIMITED ON TS1;
CONN stu01/stu01pwd@orcl
ALTER TABLE stu MOVE TABLESPACE ts1
  STORAGE (INITIAL 10k NEXT 20k MINEXTENTS 1 PCTINCREASE 0);

P110
5.15
CONN scott/tiger@orcl
ALTER TABLE course MODIFY (cname VARCHAR2(20)); 
ALTER TABLE course MODIFY (cno VARCHAR2(2));

P110
5.16
CONN scott/tiger@orcl
DESC student
SELECT * FROM student;
ALTER TABLE student ADD (birthday DATE DEFAULT sysdate);
DESC student
SELECT * FROM student;
 
P111
5.17
CONN scott/tiger@orcl
SELECT * FROM student;
ALTER TABLE student SET UNUSED (birthday);
DESC STUDENT;
ALTER TABLE student DROP UNUSED COLUMNS;
ALTER TABLE student DROP COLUMN birthday;
DESC STUDENT;
SELECT * FROM student;
ALTER TABLE student ADD (birthday date DEFAULT sysdate);
SELECT * FROM student;

P112
5.18
CONN scott/tiger@orcl
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;

P112
5.19
CONN scott/tiger@orcl
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);

P113
5.20
CONN scott/tiger@orcl
ALTER TABLE student MODIFY PRIMARY KEY disable;
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno disable;
ALTER TABLE student MODIFY PRIMARY KEY disable;
ALTER TABLE score  MODIFY CONSTRAINT score_fk_sno enable;
ALTER TABLE student MODIFY PRIMARY KEY enable;
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno enable;

P113
5.21
CONN scott/tiger@orcl
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;

P114
ɾԼ
CONN scott/tiger@orcl
ALTER TABLE emp DROP CONSTRAINT check_total_sal;
INSERT INTO emp(deptno,empno,ename,sal,comm)
 VALUES(10,1020,'Jordan',4800,500);
5.22
CONN scott/tiger@orcl
DROP TABLE student;
DROP TABLE student CASCADE CONSTRAINTS;
5.23
CONN scott/tiger@orcl
TRUNCATE TABLE course;
TRUNCATE TABLE score;


