oracle 示例表 OE.customers

OE 用户下的customer表， 在一些国外的教科书中经常作为示例表讲解

其数据库脚本如下：

ALTER TABLE OE.CUSTOMERS DROP PRIMARY KEY CASCADE; DROP TABLE OE.CUSTOMERS CASCADE CONSTRAINTS; CREATE TABLE OE.CUSTOMERS ( CUSTOMER_ID NUMBER(6), CUST_FIRST_NAME VARCHAR2(20 BYTE) CONSTRAINT CUST_FNAME_NN NOT NULL, CUST_LAST_NAME VARCHAR2(20 BYTE) CONSTRAINT CUST_LNAME_NN NOT NULL, CUST_ADDRESS OE.CUST_ADDRESS_TYP, PHONE_NUMBERS OE.PHONE_LIST_TYP, NLS_LANGUAGE VARCHAR2(3 BYTE), NLS_TERRITORY VARCHAR2(30 BYTE), CREDIT_LIMIT NUMBER(9,2), CUST_EMAIL VARCHAR2(30 BYTE), ACCOUNT_MGR_ID NUMBER(6), CUST_GEO_LOCATION MDSYS.SDO_GEOMETRY, DATE_OF_BIRTH DATE, MARITAL_STATUS VARCHAR2(20 BYTE), GENDER VARCHAR2(1 BYTE), INCOME_LEVEL VARCHAR2(20 BYTE) ) COLUMN CUST_ADDRESS NOT SUBSTITUTABLE AT ALL LEVELS COLUMN CUST_GEO_LOCATION NOT SUBSTITUTABLE AT ALL LEVELS TABLESPACE EXAMPLE PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOLOGGING NOCOMPRESS VARRAY "CUST_GEO_LOCATION"."SDO_ORDINATES" STORE AS LOB ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE INDEX ( TABLESPACE EXAMPLE STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )) STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )) VARRAY "CUST_GEO_LOCATION"."SDO_ELEM_INFO" STORE AS LOB ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE INDEX ( TABLESPACE EXAMPLE STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )) STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT )) NOCACHE NOPARALLEL MONITORING; COMMENT ON TABLE OE.CUSTOMERS IS 'Contains customers data either entered by an employee or by the customer him/herself over the Web.'; COMMENT ON COLUMN OE.CUSTOMERS.ACCOUNT_MGR_ID IS 'References hr.employees.employee_id.'; COMMENT ON COLUMN OE.CUSTOMERS.CUST_GEO_LOCATION IS 'SDO (spatial) column.'; COMMENT ON COLUMN OE.CUSTOMERS.CUSTOMER_ID IS 'Primary key column.'; COMMENT ON COLUMN OE.CUSTOMERS.CUST_FIRST_NAME IS 'NOT NULL constraint.'; COMMENT ON COLUMN OE.CUSTOMERS.CUST_LAST_NAME IS 'NOT NULL constraint.'; COMMENT ON COLUMN OE.CUSTOMERS.CUST_ADDRESS IS 'Object column of type address_typ.'; COMMENT ON COLUMN OE.CUSTOMERS.PHONE_NUMBERS IS 'Varray column of type phone_list_typ'; COMMENT ON COLUMN OE.CUSTOMERS.CREDIT_LIMIT IS 'Check constraint.'; CREATE UNIQUE INDEX OE.CUSTOMERS_PK ON OE.CUSTOMERS (CUSTOMER_ID) NOLOGGING TABLESPACE EXAMPLE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX OE.CUST_ACCOUNT_MANAGER_IX ON OE.CUSTOMERS (ACCOUNT_MGR_ID) NOLOGGING TABLESPACE EXAMPLE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX OE.CUST_EMAIL_IX ON OE.CUSTOMERS (CUST_EMAIL) NOLOGGING TABLESPACE EXAMPLE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX OE.CUST_LNAME_IX ON OE.CUSTOMERS (CUST_LAST_NAME) NOLOGGING TABLESPACE EXAMPLE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX OE.CUST_UPPER_NAME_IX ON OE.CUSTOMERS (UPPER("CUST_LAST_NAME"), UPPER("CUST_FIRST_NAME")) NOLOGGING TABLESPACE EXAMPLE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE OE.CUSTOMERS ADD ( CONSTRAINT CUSTOMER_CREDIT_LIMIT_MAX CHECK (credit_limit <= 5000), CONSTRAINT CUSTOMER_ID_MIN CHECK (customer_id > 0), CONSTRAINT CUSTOMERS_PK PRIMARY KEY (CUSTOMER_ID) USING INDEX TABLESPACE EXAMPLE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 )); ALTER TABLE OE.CUSTOMERS ADD ( CONSTRAINT CUSTOMERS_ACCOUNT_MANAGER_FK FOREIGN KEY (ACCOUNT_MGR_ID) REFERENCES HR.EMPLOYEES (EMPLOYEE_ID) ON DELETE SET NULL); GRANT SELECT ON OE.CUSTOMERS TO BI; GRANT SELECT ON OE.CUSTOMERS TO PM;