Oracle 示例表 OE.customers
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;
评论暂时关闭