oracle基本操作

简单介绍oracle数据库的常用操作命令

 

 //删除表空间JSPHOME

drop tablespace JSPHOME including contents and datafiles;

 

//创建表空间JSPHOME :

create tablespace JSPHOME

logging

datafile 'C:/oracle/product/10.2.0/oradata/orcl/JSPHOME.dbf'

size 32m

autoextend on

next 32m maxsize 2048m

extent management local;

 

//删除用户oracle:

DROP USER oracle CASCADE

 

//创建用户并指定表空间

create user oracle identified by pass

default tablespace JSPHOME ;

 

//授于oracle 用户dba,resource的权限

grant dba,resource to oracle;

 

//新建表TEST_PERSON，主键为code

ALTER TABLE TZZY.TEST_PERSON
 DROP PRIMARY KEY CASCADE;

DROP TABLE TZZY.TEST_PERSON CASCADE CONSTRAINTS;

CREATE TABLE TZZY.TEST_PERSON
(
  CODE  VARCHAR2(20 BYTE),
  NAME  VARCHAR2(20 BYTE),
  AGE   INTEGER,
  DEPT  VARCHAR2(20 BYTE)
)
TABLESPACE TZZY
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX TZZY.TEST_PERSON_PK ON TZZY.TEST_PERSON
(CODE)
LOGGING
TABLESPACE TZZY
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE TZZY.TEST_PERSON ADD (
  CONSTRAINT TEST_PERSON_PK
 PRIMARY KEY
 (CODE)
    USING INDEX
    TABLESPACE TZZY
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

 

//新建表TEST_INFO，主键id自增，通过序列生成器和触发器来实现id自增

ALTER TABLE TZZY.TEST_INFO
 DROP PRIMARY KEY CASCADE;

DROP TABLE TZZY.TEST_INFO CASCADE CONSTRAINTS;

CREATE TABLE TZZY.TEST_INFO
(
  ID        INTEGER                             NOT NULL,
  INFOTIME  DATE,
  INFODESC  VARCHAR2(200 BYTE),
  PERSONID  VARCHAR2(20 BYTE)
)
TABLESPACE TZZY
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX TZZY.TEST_INFO_PK ON TZZY.TEST_INFO
(ID)
LOGGING
TABLESPACE TZZY
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

 

//触发器TZZY.TEST_INFO_T 用来操作序列器TZZY.TEST_INFO_S实现id自增
CREATE OR REPLACE TRIGGER TZZY.TEST_INFO_T
BEFORE INSERT ON TZZY.TEST_INFO FOR EACH ROW
BEGIN
SELECT TZZY.TEST_INFO_S.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

ALTER TABLE TZZY.TEST_INFO ADD (
  CONSTRAINT TEST_INFO_PK
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE TZZY
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

 

 

//新增外键用来关联TEST_PERSON

ALTER TABLE TZZY.TEST_INFO ADD (
  CONSTRAINT FK_PERSON
 FOREIGN KEY (PERSONID)
 REFERENCES TZZY.TEST_PERSON
    ON DELETE CASCADE);

 

 

//新建序列器TZZY.TEST_INFO_S

DROP SEQUENCE TZZY.TEST_INFO_S;

CREATE SEQUENCE TZZY.TEST_INFO_S
  START WITH 1
  MAXVALUE 999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

 

//新建触发器TEST_PERSON_CLEAR_T，当主表TEST_PERSON删除时，与其关联的子表TEST_INFO 都将被删除

DROP TRIGGER TZZY.TEST_PERSON_CLEAR_T;
CREATE OR REPLACE TRIGGER TZZY.TEST_PERSON_CLEAR_T
AFTER DELETE ON TEST_PERSON
FOR EACH ROW
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   delete from TEST_INFO where PERSONID=:OLD.CODE;
   COMMIT;
END ;
/