Oracle创建数据表综合实例

港控/mmm° 2021-09-14 06:42 327阅读 0赞

Oracle创建数据表综合实例。

# 1、创建数据表 #

--判断数据表是否存在，存在则删除
    DECLARE
      IS_EXIST NUMBER;
    BEGIN
      SELECT COUNT(*) INTO IS_EXIST FROM ALL_TABLES WHERE TABLE_NAME = 'STUDENT_INFO';
      IF IS_EXIST = 1 THEN
         --删除表以及所有引用这个表的视图、约束或触发器等
         EXECUTE IMMEDIATE 'DROP TABLE STUDENT_INFO CASCADE CONSTRAINTS'; 
      END IF;
    END;
    
    --创建“学生信息”数据表
    CREATE TABLE STUDENT_INFO
    (
      STU_ID INT,                   --学号
      STU_NAME VARCHAR2(8),         --姓名
      EMAIL VARCHAR2(20),           --邮箱
      SEX CHAR(2),                  --性别
      AGE INT,                      --年龄
      CLASS_ID VARCHAR2(20),        --班级编号
      REGDATE DATE DEFAULT SYSDATE  --创建期间
    );

# 2、添加注释 #

--添加表注释
    COMMENT ON TABLE STUDENT_INFO IS '学生信息表';
    
    --添加字段注释：
    COMMENT ON COLUMN STUDENT_INFO.STU_ID IS '学号';
    COMMENT ON COLUMN STUDENT_INFO.STU_NAME IS '姓名';
    COMMENT ON COLUMN STUDENT_INFO.EMAIL IS '邮箱';
    COMMENT ON COLUMN STUDENT_INFO.SEX IS '性别';
    COMMENT ON COLUMN STUDENT_INFO.AGE IS '年龄';
    COMMENT ON COLUMN STUDENT_INFO.CLASS_ID IS '班级编号';
    COMMENT ON COLUMN STUDENT_INFO.REGDATE IS '创建期间';

# 3、创建约束 #

--创建学号的主键约束
    ALTER TABLE STUDENT_INFO ADD CONSTRAINT STU_PK PRIMARY KEY(STU_ID);
    
    --创建姓名的非空约束
    ALTER TABLE STUDENT_INFO MODIFY STU_NAME NOT NULL;
    
    --创建邮箱的唯一性约束
    ALTER TABLE STUDENT_INFO ADD CONSTRAINT EMAIL_UK UNIQUE(EMAIL);
    
    --创建性别的默认约束
    ALTER TABLE STUDENT_INFO MODIFY SEX DEFAULT '女';
    
    --创建年龄的检查约束
    ALTER TABLE STUDENT_INFO ADD CONSTRAINT AGE_CK CHECK(AGE>0 AND AGE<120);

或者在创建表的同时设置约束

--创建“学生信息”数据表
    CREATE TABLE STUDENT_INFO
    (
      STU_ID INT PRIMARY KEY,                              --学号,并设置主键约束 
      STU_NAME VARCHAR2(8)NOT NULL,                        --姓名,并设置非空约束
      EMAIL VARCHAR2(20)CONSTRAINT EMAIL_UK UNIQUE,        --邮箱,并设置唯一性约束  
      SEX CHAR(2)DEFAULT '女',                             --性别，并设置默认约束  
      AGE INT CONSTRAINT AGE_CK CHECK(AGE>0 AND AGE<120),  --年龄，并设置检查约束  
      CLASS_ID VARCHAR2(20),                               --班级编号
      REGDATE DATE DEFAULT SYSDATE                         --创建期间
    );

# 4、创建索引 #

--创建班级编号索引
    CREATE INDEX STUDENT_INDEX ON STUDENT_INFO(CLASS_ID);

# 5、创建序列 #

--创建序列
    CREATE SEQUENCE STU_SEQ  
    START WITH 1  
    INCREMENT BY 1  
    MINVALUE 1  
    MAXVALUE 9999999999999

创建一个行级触发器，该触发器在STUDENT\_INFO（学生信息）表插入数据时被触发，并且在该触发器的主体中实现设置STUDENT\_INFO（学生信息）表STU\_ID列的值。

CREATE OR REPLACE TRIGGER TRI_INSERT_STU    
      BEFORE INSERT    
      ON STUDENT_INFO  --关于STUDENT_INFO数据表的STU_ID，在插入STU_ID列之前，引起该触发器的运行    
      FOR EACH ROW     --创建行级触发器    
    BEGIN    
      SELECT STU_SEQ.NEXTVAL    
      INTO :NEW.STU_ID    
      FROM DUAL;       --从序列中生成一个新的数值，赋值给当前插入行的STU_ID列    
    END;

# 6、测试 #

--测试
    INSERT INTO STUDENT_INFO(STU_NAME,EMAIL,AGE,CLASS_ID) VALUES('张三','zhangsan@qq.com',18,'1001');