Oracle PL/SQL 实例精解所用STUDENT模式代码

最新推荐文章于 2019-08-20 23:27:00 发布

weixin_34148340

最新推荐文章于 2019-08-20 23:27:00 发布

阅读量106

点赞数

原文链接：http://blog.51cto.com/wyhstar460/1053148

版权

CREATE TABLE "STUDENT"."COURSE"
   ( "COURSE_NO" NUMBER(8,0) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(50 BYTE),
"COST" NUMBER(9,2),
"PREREQUISITE" NUMBER(8,0),
"CREATE_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATE_DATE" DATE NOT NULL ENABLE,
"MODIFY_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."COURSE"."COURSE_NO" IS '唯一的课程编号';

   COMMENT ON COLUMN "STUDENT"."COURSE"."DESCRIPTION" IS '课程的全名';

   COMMENT ON COLUMN "STUDENT"."COURSE"."COST" IS '注册该课程的费用';

   COMMENT ON COLUMN "STUDENT"."COURSE"."PREREQUISITE" IS '本课程的预备课程的ID编号';

   COMMENT ON COLUMN "STUDENT"."COURSE"."CREATE_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."COURSE"."CREATE_DATE" IS '审计列(说明插入日期)';

   COMMENT ON COLUMN "STUDENT"."COURSE"."MODIFY_BY" IS '审计列(说明谁做最后一次更新)';

   COMMENT ON COLUMN "STUDENT"."COURSE"."MODIFIED_DATE" IS '审计列(最后一次更新的日期)';

______________________________________________________________________________________________
CREATE TABLE "STUDENT"."SECTION"
   ( "SECTION_ID" NUMBER(8,0) NOT NULL ENABLE,
"COURSE_NO" NUMBER(8,0) NOT NULL ENABLE,
"SECTION_NO" NUMBER(3,0) NOT NULL ENABLE,
"START_DATE_TIME" DATE,
"LOCATION" VARCHAR2(50 BYTE),
"INSTRUCTOR_ID" NUMBER(8,0) NOT NULL ENABLE,
"CAPACITY" NUMBER(3,0),
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."SECTION"."SECTION_ID" IS '课程的唯一ID';

   COMMENT ON COLUMN "STUDENT"."SECTION"."COURSE_ID" IS '本班级所属课程的课程编号';

   COMMENT ON COLUMN "STUDENT"."SECTION"."SECTION" IS '本班级在课程中的编号';

   COMMENT ON COLUMN "STUDENT"."SECTION"."START_DATE_TIME" IS '本班级开班日期和时间';

   COMMENT ON COLUMN "STUDENT"."SECTION"."LOCATION" IS '本班级开班的地址';

   COMMENT ON COLUMN "STUDENT"."SECTION"."INSTRUCTOR_ID" IS '教授本班级的教师ID编号';

   COMMENT ON COLUMN "STUDENT"."SECTION"."CAPACITY" IS '班班级允许的注册的最大学生数量';

   COMMENT ON COLUMN "STUDENT"."SECTION"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."SECTION"."CREATED_DATE" IS '审计列(说明插入日期)';

   COMMENT ON COLUMN "STUDENT"."SECTION"."MODIFIED_BY" IS '审计列(说明谁做最后一次更新)';

   COMMENT ON COLUMN "STUDENT"."SECTION"."MODIFIED_DATE" IS '审计列(最后一次更新的日期)';
__________________________________________________________________________________________
CREATE TABLE "STUDENT"."STUDENT"
   ( "STUDENT_ID" NUMBER(8,0) NOT NULL ENABLE,
"SALUTATION" VARCHAR2(5 BYTE),
"FIRST_NAME" VARCHAR2(25 BYTE),
"LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"STREET_ADDRESS" VARCHAR2(50 BYTE),
"ZIP" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"PHONE" VARCHAR2(15 BYTE),
"EMPLOYER" VARCHAR2(50 BYTE),
"REGISTRATION_DATE" DATE NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."STUDENT"."STUDENT_ID" IS '学生的唯一ID';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."SALUTATION" IS '学生的称谓(Ms/Mr/Dr)';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."FIRST_NAME" IS '学生的第一个名字';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."LAST_NAME" IS '学生的姓氏';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."STREET_ADDRESS" IS '学生所在街道的地址';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."ZIP" IS '学生所在区域的邮编';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."PHONE" IS '学生的电话号码';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."EMPLOYER" IS '该学生就职的公司名称';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."REGISTRATION_DATE" IS '学生的注册日期';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."CREATED_DATE" IS '审计列(说明谁插入日期)';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."MODIFIED_BY" IS '审计列（说明谁做最后一次更新）';

   COMMENT ON COLUMN "STUDENT"."STUDENT"."MODIFIED_DATE" IS '审计列（最后一次更新的日期）';
________________________________________________________________________________________________
CREATE TABLE "STUDENT"."ENROLLMENT"
   ( "STUDENT_ID" NUMBER(8,0) NOT NULL ENABLE,
"SECTION_ID" NUMBER(8,0) NOT NULL ENABLE,
"ENROLL_DATE" DATE NOT NULL ENABLE,
"FINAL_GRADE" NUMBER(3,0),
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."STUDENT_ID" IS '学生的唯一ID';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."SECTION_ID" IS '班级的编号';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."ENROLL_DATE" IS '学生注册某班级的日期';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."FINAL_GRADE" IS '学生在这个班级所得的最终成绩';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."CREATED_DATE" IS '审计列（说明插入日期）';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."MODIFIED_BY" IS '审计列（说明谁做最后一次更新）';

   COMMENT ON COLUMN "STUDENT"."ENROLLMENT"."MODIFIED_DATE" IS '审计列（最后一次更新的时间）';
________________________________________________________________________________________________
CREATE TABLE "STUDENT"."INSTUCTOR"
   ( "INSTUCTOR_ID" NUMBER(8,0) NOT NULL ENABLE,
"SALUTATION" VARCHAR2(5 BYTE),
"FIRST_NAME" VARCHAR2(25 BYTE),
"LAST_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"STREET_ADDRESS" VARCHAR2(50 BYTE),
"ZIP" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"PHONE" VARCHAR2(15 BYTE),
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."INSTUCTOR_ID" IS '教师的唯一ID';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."SALUTATION" IS '教师的称谓(Ms/Mr/Dr)';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."FIRST_NAME" IS '教师的第一个名字';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."LAST_NAME" IS '教师的姓氏';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."STREET_ADDRESS" IS '教师所在街道的地址';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."ZIP" IS '教师所在区域的邮编';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."PHONE" IS '教师的电话号码';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."CREATED_BY" IS '审计列（说明谁插入数据）';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."CREATED_DATE" IS '审计列（说明插入日期）';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."MODIFIED_BY" IS '审计列(说明谁做最后一次更新)';

   COMMENT ON COLUMN "STUDENT"."INSTUCTOR"."MODIFIED_DATE" IS '审计列（最后一次）更新的日期';
______________________________________________________________________________________________
CREATE TABLE "STUDENT"."ZIPCODE"
   ( "ZIP" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"CITY" VARCHAR2(25 BYTE),
"STATE" VARCHAR2(2 BYTE),
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."ZIP" IS '邮政编码';

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."CITY" IS '本邮编所属的城市名称';

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."STATE" IS '美国州的编码缩写';

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."CREATED_DATE" IS '审计列（说明插入日期）';

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."MODIFIED_BY" IS '说明谁做的最后一次更新';

   COMMENT ON COLUMN "STUDENT"."ZIPCODE"."MODIFIED_DATE" IS '最后一次更新的日期';
_______________________________________________________________________________________________
CREATE TABLE "STUDENT"."GRADE_TYPE"
   ( "GRADE_TYPE_CODE" CHAR(2 BYTE) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE"."GRADE_TYPE_CODE" IS '标识成绩分类的唯一性代码';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE"."DESCRIPTION" IS '对该代码的描述性文本';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE"."CREATED_DATE" IS '审计列(说明插入日期)';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE"."MODIFIED_BY" IS '审计列（说明谁做最后一次更新)';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE"."MODIFIED_DATE" IS '审计列（最后一次更新的日期）';
________________________________________________________________________________________________
CREATE TABLE "STUDENT"."GRADE_TYPE_WIGHT"
   ( "SECTION_ID" CHAR(2 BYTE) NOT NULL ENABLE,
"GRADE_TYPE_CODE" NUMBER(3,0) NOT NULL ENABLE,
"NUMBER_PER_SECTION" NUMBER(3,0) NOT NULL ENABLE,
"PERCENT_OF_FINAL_GRADE" NUMBER(3,0) NOT NULL ENABLE,
"DROP_LOWEST" CHAR(1 BYTE) NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."SECTION_ID" IS '班级的ID';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."GRADE_TYPE_CODE" IS '标识某种成绩分类的代码';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."NUMBER_PER_SECTION" IS '多少次该成绩类型的成绩会计算在内';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."PERCENT_OF_FINAL_GRADE" IS '成绩出现的百分比';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."DROP_LOWEST" IS '当计算最终成绩时，是否要去掉最低成绩';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."CREATED_DATE" IS '审计列（说明插入日期）';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."MODIFIED_BY" IS '审计列（说明谁做最后一次更新）';

   COMMENT ON COLUMN "STUDENT"."GRADE_TYPE_WIGHT"."MODIFIED_DATE" IS '审计列（最后一次更新的日期）';
_______________________________________________________________________________________________________________________
CREATE TABLE "STUDENT"."GRADE"
   ( "STUDENT_ID" NUMBER(8,0) NOT NULL ENABLE,
"SECTION_ID" NUMBER(8,0) NOT NULL ENABLE,
"GRADE_TYPE_CODE" CHAR(2 BYTE) NOT NULL ENABLE,
"GRADE_CODE_OCCURENCE" NUMBER(38,0) NOT NULL ENABLE,
"NUMERIC_GRADE" NUMBER(3,0) NOT NULL ENABLE,
"COMMENTS" VARCHAR2(2000 BYTE),
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."GRADE"."STUDENT_ID" IS '学生的ID';

   COMMENT ON COLUMN "STUDENT"."GRADE"."SECTION_ID" IS '班级的ID';

   COMMENT ON COLUMN "STUDENT"."GRADE"."GRADE_TYPE_CODE" IS '标识某种成绩分类的代码';

   COMMENT ON COLUMN "STUDENT"."GRADE"."GRADE_CODE_OCCURENCE" IS '某班级中成绩的序号';

   COMMENT ON COLUMN "STUDENT"."GRADE"."NUMERIC_GRADE" IS '成绩值';

   COMMENT ON COLUMN "STUDENT"."GRADE"."COMMENTS" IS '教师对该成绩的评语';

   COMMENT ON COLUMN "STUDENT"."GRADE"."CREATED_BY" IS '审计列(说明谁插入的数据)';

   COMMENT ON COLUMN "STUDENT"."GRADE"."CREATED_DATE" IS '审计列（说明插入日期）';

   COMMENT ON COLUMN "STUDENT"."GRADE"."MODIFIED_BY" IS '审计列(说明谁做最后一次更新)';

   COMMENT ON COLUMN "STUDENT"."GRADE"."MODIFIED_DATE" IS '审计列（最后一次更新的日期）';
______________________________________________________________________________________________
CREATE TABLE "STUDENT"."GRADE_CONVERSION"
   ( "LETTER_GRADE" VARCHAR2(2 BYTE) NOT NULL ENABLE,
"GRADE_POINT" NUMBER(3,2) NOT NULL ENABLE,
"MAX_GRADE" NUMBER(3,0) NOT NULL ENABLE,
"MIN_GRADE" NUMBER(3,0) NOT NULL ENABLE,
"CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"CREATED_DATE" DATE NOT NULL ENABLE,
"MODIFIED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"MODIFIED_DATE" DATE NOT NULL ENABLE
   ) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."LETTER_GRADE" IS '标识成绩的唯一ID';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."GRADE_POINT" IS '成绩的分类';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."MAX_GRADE" IS '对应字母的最大值';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."MIN_GRADE" IS '对应字母的最小值';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."CREATED_BY" IS '审计列(说明谁插入数据)';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."CREATED_DATE" IS '审计列(说明谁插入日期)';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."MODIFIED_BY" IS '审计列(说明谁做最后一次更新)';

   COMMENT ON COLUMN "STUDENT"."GRADE_CONVERSION"."MODIFIED_DATE" IS '审计列(最后一次更新的日期)';