我在我的程序中收到错误，有助于添加新课程，并在描述中给出了要求 [英] I keep getting errors in my procedure that help add new course and i have put requirements in description

问题描述

该过程需要确保：

1.课程表中已存在必要的课程（如果有）。为简单起见，
我们假装课程最多可以有一门课程作为先决条件。

2. 课程的先决条件必须在较低的水平。例如， ISYS326 的前提条件必须是 a100 - 或 200 - 级别课程，不能是 300 级别课程。

如果上述任一条件都失败，则该过程会引发一个例外 WRONG_PREREQ ，它会打印一个适当的警报消息，并且不修改课程 table。

在过程 CID = courseid PID = prerequisiteid和DID = deptid 他们都是 not null 值

所以这个程序将用于输入新课程

CREATE OR REPLACE PROCEDURE NEW_COURSE（

CID IN COURSE.COURSEID％TYPE，

CNAME IN COURSE.COURSENAME％TYPE，

PID IN HAS_PREREQUISITE.PREREQUISITEID％TYPE，

DID IN DEPARTMENT.DEPTID ％TYPE
）

IS

CONDITION1 NUMBER;

CONDITION2 NUMBER;

WRONG_ PREQ EXCEPTION;

BEGIN
/ *可以在任何SQL * /

IF之前检查（SUBSTR（CID，5,3）< = SUBSTR（PID，5,3））THEN / * PREREQUISITE具有更高的ID

p>

RAISE WRONG_PREQ;

END IF;

SELECT COUNT（COURSEID）

INTO CONDITION1

FROM COURSE

WHERE COURSEID = PID;

/ *这可以在这里检查，而不运行第二个选择* /

IF（CONDITION1< 1）THEN /违反条件1，不存在
PREEQUISITE课程 /

WRONG_PREQ;

END IF;

SELECT COUNT（*）

INTO CONDITION2

FROM HAS_PREREQUISITE

WHERE COURSEID = CID

AND PREREQUISITEID = PID

AND SUBSTR（CID，5,3）> SUBSTR（PID，5,3）;

IF（CONDITION2 = 1）THEN / em>记录在HAS_PREREQUISITE中的记录
表 /

RAISE WRONG_PREQ;

END IF;

INSERT INTO COURSE VALUES（CID，CNAME，'NULL'，'NULL'）;

INSERT INTO HAS_PREREQUISITE VALUES（CID，PID）;

INSERT INTO DEPARTMENT VALUES（DEPTID，'NULL'）;

EXCEPTION

WHEN WRONG_PREQ THEN

DBMS_OUTPUT .PUT_LINE（'COURSEID或PREREQUISITE ID is INVALID'）;

END NEW_COURSE;

Oracle SQL Developer。

DDL

/ =========== ================================================== = /

/ * DBMS名称：ORACLE版本11g * /

/ *创建于：11/10/2016 3:47:29 PM * /

/ ================= ============================================= /

alter table ACADEMIC_REC
drop constraint FK_ACADEMIC_RELATIONS_STUDENT;

alter table ACADEMIC_REC
drop constraint FK_ACADEMIC_RELATIONS_COURSE;

alter table COURSE
drop约束FK_COURSE_OFFERS_DEPARTME;

alter table ENROLS
下拉约束FK_ENROLS_ENROLS_STUDENT;

alter table ENROLS
drop constraint FK_ENROLS_ENROLS2_TUT_PRAC;

alter table HAS_PREREQUISITE
drop约束FK_HAS_PRER_HAS_PRERE_COURSE;

alter table HAS_PREREQUISITE
drop constraint FK_HAS_PRER_HAS_PRERE_COURSE;

alter table STAFF
drop constraint FK_STAFF_RELATIONS_DEPARTME;

alter table TEACHING_INFORMATION
drop constraint FK_TEACHING_RELATIONS_STAFF;

alter table TEACHING_INFORMATION
drop constraint FK_TEACHING_RELATIONS_COURSE; p>

alter table TUT_PRAC
drop constraint FK_TUT_PRAC_HAS_COURSE;

alter table TUT_PRAC
drop constraint FK_TUT_PRAC_RELATIONS_STAFF;

下拉列表ACADEMIC_REC级联约束;

下拉列表COURSE级联约束;

drop table ENROLS级联约束;

下拉列表HAS_PREREQUISITE级联约束s;

drop table STAFF级联约束;

drop table STUDENT级联约束;

drop table TEACHING_INFORMATION cascade constraints;

drop table TUT_PRAC级联约束;

/ ============================================= ================= /

/ *表：ACADEMIC_REC * /

/ ================================= ======================= /

创建表ACADEMIC_REC

（

STUID CHAR（8）not null，

COURSEID CHAR（8）not null，

状态VARCHAR2（5），

YEAR NUMBER ），

SEMESTER CHAR（2），

GRADE VARCHAR2（2）

约束CKC_GRADE_ACADEMIC检查（GRADE为空或（

（'HD'，'D'，'CR'，'P'，'F'））），

约束PK_ACADEMIC_REC主键（STUID，COURSEID）
）;

/ ====== ================================================== ==== /

/ *表：COURSE * /

/ ==================================================

创建表课程

（

COURSEID CHAR（8）not null，

DEPTID CHAR（8）not null，

COURSENAME VARCHAR2（20），

TEXTBOOK VARCHAR2（20），

CREDITHOUR NUMBER（2），

MAX_ENROL NUMBER（4），

ACTUAL_ENROL NUMBER ），

可用性编号（4），

课程_TIME CHAR（7），

约束PK_COURSE主键（COURSEID）
）;

/ ============== ============================================== /

/ *表：DEPARTMENT * /

/ ====== ================================================== ==== /

创建表DEPARTMENT
（

DEPTID CHAR 8）not null，

DEPTNAME VARCHAR2（20），

DEPTCONTACTNO NUMBER（10），

BUILDING VARCHAR2（5），

约束PK_DEPARTMENT主键（DEPTID）

）;

/ ====================== $ / $ $ /
$ b

/ *表：ENROLS * /

/ ============== ============================================== /

创建表ENROLS
（

p>

STUID CHAR（8）not null，

CLASSID CHAR（8）not null，

约束PK_ENROLS主键（STUID，CLASSID）
）;

/ ======= ================================================== ===== /

/ *表：HAS_PREREQUISITE * /

/ em > ================================================= $ //

创建表HAS_PREREQUISITE
（

COURSEID CHAR（8）not null，

PREREQUISITEID CHAR（8）not null，

约束PK_HAS_PREREQUISITE主键（COURSEID，PREREQUISITEID）
）;

/ ============ ================================================ /

/ *表：STAFF * /

/ ==== ================== $ / $ $ /
$ b

创建表STAFF
（

STAFFID CHAR（8）not null，

DEPARTMENTID CHAR（8）not null，

STAFFNAME VARCHAR2（50），

STAFFADDRESS VARCHAR2 （70），

STAFFCONTACTNO NUMBER（10），

STAFFEMAIL VARCHAR2（50），

OFFICENO NUMBER（5），

ROLE VARCHAR2（10），

约束PK_STAFF主键（STAFFID）
）;

/ ============= =============================================== /

/ *表：STUDENT * /

/ ======= ================================================== ===== /

创建表STUDENT
（

STUID CHAR （8）not null，

DEGREE VARCHAR2（10），

MAJOR VARCHAR2（10），

STU_NAME VARCHAR2（50），

STU_ADDRESS VARCHAR2（70），

CONTACTNO NUMBER（10） ，

电子邮件VARCHAR2（50），

约束PK_STUDENT主键（STUID）
）;

/ =========================== ============================= /

/ *表：TEACHING_INFORMATION * /

/ ===================== b b b b b b b b b b b b>>>>>>>>>
$ b

创建表TEACHING_INFORMATION
（

STAFFID CHAR（8）not null，

COURSEID CHAR（8）not null，

SEMESTER CHAR（2）not null，

YEAR NUMBER（4）not null，

约束PK_TEACHING_INFORMATION主键（STAFFID，COURSEID，SEMESTER，YEAR）
）;

/ ===== ================================================== $ //
$ b

/ *表：TUT_PRAC * /

/ =============================================== =============== /

创建表TUT_PRAC
（

CLASSID CHAR（8）not null，

COURSEID CHAR（8）not null，

STAFFID CHAR（8）not null，

TYPE VARCHAR2（5），

DAY VARCHAR2 （10），

时间日期

ROOMID CHAR（10），

NO_OF_SEATS NUMBER（2），

约束PK_TUT_PRAC主键（CLASSID）
）;

alter table ACADEMIC_REC
add约束FK_ACADEMIC_RELATIONS_STUDENT外键（STUID）
引用STUDENT（STUID）;

alter table ACADEMIC_REC
添加约束FK_ACADEMIC_RELATIONS_COURSE外键（COURSEID）
参考COURSE（COURSEID）;

alter table COURSE
添加约束FK_COURSE_OFFERS_DEPARTME外键（DEPTID）
引用DEPARTMENT（DEPTID）;

alter table ENROLS
添加约束FK_ENROLS_ENROLS_STUDENT外键（STUID）
引用STUDENT（STUID）;

alter table ENROLS
添加约束FK_ENROLS_ENROLS2_TUT_PRAC外键（CLASSID）
引用TUT_PRAC（CLASSID）;

alter table HAS_PREREQUISITE
add约束FK_HAS_PRER_HAS_PRERE_COURSE外键（COURSEID）
引用COURSE（COURSEID）;

alter table HAS_PREREQUISITE
添加约束FK_HAS_PRER_HAS_PRERE_COURSE外键（PREREQUISITEID）
参考COURSE（COURSEID）;

alter table STAFF
添加约束FK_STAFF_RELATIONS_DEPARTME外键（DEPARTMENTID）
引用DEPARTMENT（DEPTID）;

alter table TEACHING_INFORMATION
添加约束FK_TEACHING_RELATIONS_STAFF外键（STAFFID）
引用STAFF（STAFFID）;

alter table TEACHING_INFORMATION
添加约束FK_TEACHING_RELATIONS_COURSE外键（COURSEID）
引用COURSE（COURSEID）;

alter table TUT_PRAC
添加约束FK_TUT_PRAC_HAS_COURSE外键（COURSEID）
引用COURSE（COURSEID）;

alter table TUT_PRAC

add约束FK_TUT_PRAC_RELATIONS_STAFF外键（STAFFID）
引用STAFF（STAFFID）;

解决方案

我试图编辑你的代码移动一些检查以避免无用的查询，并显示您要编辑的内容;遵循评论：

 创建或替换过程NEW_COURSE（
 CID IN COURSE.COURSEID％TYPE，
 CNAME IN COURSE.COURSENAME％TYPE，
 PID IN HAS_PREREQUISITE.PREREQUISITEID％TYPE，
 DID IN DEPARTMENT.DEPTID％TYPE 
）
 IS 
 CONDITION1 NUMBER; 
 CONDITION2 NUMBER; 
 WRONG_PREQ EXCEPTION; 
 BEGIN 
 / *这可以在任何SQL * / 
 IF之前检查（SUBSTR（CID，5,3）< = SUBSTR（PID，5,3））THEN / * PREREQUISITE具有较高的编号* / 
 RAISE WRONG_PREQ; 
 END IF; 
 
 
 
 SELECT COUNT（COURSEID）
 INTO CONDITION1 
 FROM COURSE 
 WHERE COURSEID = PID; 
 
 / *这可以在这里检查，而不运行第二个选择* / 
 IF（CONDITION1<> 1）THEN / *违反条件1，不存在
 PREEQUISITE课程* / 
 RAISE WRONG_PREQ; 
 END IF; 
 
 
 SELECT COUNT（*）
 INTO CONDITION2 
 FROM HAS_PREREQUISITE 
 WHERE COURSEID = CID 
和PREREQUISITEID = PID 
 AND SUBSTR（CID，5,3）> SUBSTR（PID，5,3）; 
 
 IF（CONDITION2 = 1）THEN / *记录在HAS_PREREQUISITE中存在
 TABLE * / 
 RAISE WRONG_PREQ; 
 END IF; 
 
 
 
 INSERT INTO COURSE VALUES（CID，CNAME，'NULL'，'NULL'）; / *编辑语句以适应表结构也是'NULL'或null？* / 
 INSERT INTO HAS_PREREQUISITE VALUES（CID，PID）; / *表只有2列* / 
 INSERT INTO DEPARTMENT VALUES（DEPTID，'NULL'）; / *编辑语句以适应表结构。还有'NULL'还是空？ * / 
 EXCEPTION 
 WHEN WRONG_PREQ THEN 
 DBMS_OUTPUT.PUT_LINE（'COURSEID或PREREQUISITE ID is INVALID'）; 
 END NEW_COURSE; 
  

the procedure needs to ensure that:

1.The prerequisite courses for it (if any) already exist in the COURSE table. For simplicity, we will pretend that a course can have at most one course as prerequisite.

2. The prerequisite for a course must be at a lower level. For instance, the prerequisite for ISYS326 must be a100- or 200-level course, and cannot be a 300 level course.

If either of the above requirements fails, the procedure raises an exception WRONG_PREREQ that prints an appropriate alert message, and does not modify the COURSE table.

in the procedure CID =courseid PID=prerequisiteid and DID=deptid they are all not null values

so this procedure will be used to enter new course

CREATE OR REPLACE PROCEDURE NEW_COURSE (

CID IN COURSE.COURSEID%TYPE,

CNAME IN COURSE.COURSENAME%TYPE,

PID IN HAS_PREREQUISITE.PREREQUISITEID%TYPE,

DID IN DEPARTMENT.DEPTID%TYPE )

IS

CONDITION1 NUMBER;

CONDITION2 NUMBER;

WRONG_PREQ EXCEPTION;

BEGIN /* this can be checked before any SQL */

IF (SUBSTR(CID,5,3) <= SUBSTR(PID,5,3)) THEN /*PREREQUISITE HAS A HIGHER ID

THAN COURSEID */

RAISE WRONG_PREQ;

END IF;

SELECT COUNT(COURSEID)

INTO CONDITION1

FROM COURSE

WHERE COURSEID = PID;

/* this can be checked here, without running the second select */

IF (CONDITION1 <>1) THEN /VIOLATION OF CONDITION 1, NO EXISTING PREREQUISITE COURSE/

RAISE WRONG_PREQ;

END IF;

SELECT COUNT(*)

INTO CONDITION2

FROM HAS_PREREQUISITE

WHERE COURSEID = CID

AND PREREQUISITEID = PID

AND SUBSTR(CID,5,3) > SUBSTR(PID,5,3);

IF (CONDITION2 = 1) THEN /THE RECORD ALREADYS EXISTS IN HAS_PREREQUISITE TABLE/

RAISE WRONG_PREQ;

END IF;

INSERT INTO COURSE VALUES(CID,CNAME,'NULL','NULL' );

INSERT INTO HAS_PREREQUISITE VALUES(CID,PID);

INSERT INTO DEPARTMENT VALUES (DEPTID,'NULL');

EXCEPTION

WHEN WRONG_PREQ THEN

DBMS_OUTPUT.PUT_LINE('COURSEID OR PREREQUISITE ID IS INVALID');

END NEW_COURSE;

This is done is Oracle SQL Developer.

DDL

/==============================================================/

/* DBMS name: ORACLE Version 11g */

/* Created on: 11/10/2016 3:47:29 PM */

/==============================================================/

alter table ACADEMIC_REC drop constraint FK_ACADEMIC_RELATIONS_STUDENT;

alter table ACADEMIC_REC drop constraint FK_ACADEMIC_RELATIONS_COURSE;

alter table COURSE drop constraint FK_COURSE_OFFERS_DEPARTME;

alter table ENROLS drop constraint FK_ENROLS_ENROLS_STUDENT;

alter table ENROLS drop constraint FK_ENROLS_ENROLS2_TUT_PRAC;

alter table HAS_PREREQUISITE drop constraint FK_HAS_PRER_HAS_PRERE_COURSE;

alter table HAS_PREREQUISITE drop constraint FK_HAS_PRER_HAS_PRERE_COURSE;

alter table STAFF drop constraint FK_STAFF_RELATIONS_DEPARTME;

alter table TEACHING_INFORMATION drop constraint FK_TEACHING_RELATIONS_STAFF;

alter table TEACHING_INFORMATION drop constraint FK_TEACHING_RELATIONS_COURSE;

alter table TUT_PRAC drop constraint FK_TUT_PRAC_HAS_COURSE;

alter table TUT_PRAC drop constraint FK_TUT_PRAC_RELATIONS_STAFF;

drop table ACADEMIC_REC cascade constraints;

drop table COURSE cascade constraints;

drop table ENROLS cascade constraints;

drop table HAS_PREREQUISITE cascade constraints;

drop table STAFF cascade constraints;

drop table STUDENT cascade constraints;

drop table TEACHING_INFORMATION cascade constraints;

drop table TUT_PRAC cascade constraints;

/==============================================================/

/* Table: ACADEMIC_REC */

/==============================================================/

create table ACADEMIC_REC

(

STUID CHAR(8) not null,

COURSEID CHAR(8) not null,

STATUS VARCHAR2(5),

YEAR NUMBER(4),

SEMESTER CHAR(2),

GRADE VARCHAR2(2)

constraint CKC_GRADE_ACADEMIC check (GRADE is null or (GRADE in

('HD','D','CR','P','F'))),

constraint PK_ACADEMIC_REC primary key (STUID, COURSEID) );

/==============================================================/

/* Table: COURSE */

/==============================================================/

create table COURSE

(

COURSEID CHAR(8) not null,

DEPTID CHAR(8) not null,

COURSENAME VARCHAR2(20),

TEXTBOOK VARCHAR2(20),

CREDITHOUR NUMBER(2),

MAX_ENROL NUMBER(4),

ACTUAL_ENROL NUMBER(4),

AVAILABILITY NUMBER(4),

COURSE_TIME CHAR(7),

constraint PK_COURSE primary key (COURSEID) );

/==============================================================/

/* Table: DEPARTMENT */

/==============================================================/

create table DEPARTMENT (

DEPTID CHAR(8) not null,

DEPTNAME VARCHAR2(20),

DEPTCONTACTNO NUMBER(10),

BUILDING VARCHAR2(5),

constraint PK_DEPARTMENT primary key (DEPTID)

);

/==============================================================/

/* Table: ENROLS */

/==============================================================/

create table ENROLS (

STUID CHAR(8) not null,

CLASSID CHAR(8) not null,

constraint PK_ENROLS primary key (STUID, CLASSID) );

/==============================================================/

/* Table: HAS_PREREQUISITE */

/==============================================================/

create table HAS_PREREQUISITE (

COURSEID CHAR(8) not null,

PREREQUISITEID CHAR(8) not null,

constraint PK_HAS_PREREQUISITE primary key (COURSEID, PREREQUISITEID) );

/==============================================================/

/* Table: STAFF */

/==============================================================/

create table STAFF (

STAFFID CHAR(8) not null,

DEPARTMENTID CHAR(8) not null,

STAFFNAME VARCHAR2(50),

STAFFADDRESS VARCHAR2(70),

STAFFCONTACTNO NUMBER(10),

STAFFEMAIL VARCHAR2(50),

OFFICENO NUMBER(5),

ROLE VARCHAR2(10),

constraint PK_STAFF primary key (STAFFID) );

/==============================================================/

/* Table: STUDENT */

/==============================================================/

create table STUDENT (

STUID CHAR(8) not null,

DEGREE VARCHAR2(10),

MAJOR VARCHAR2(10),

STU_NAME VARCHAR2(50),

STU_ADDRESS VARCHAR2(70),

CONTACTNO NUMBER(10),

EMAIL VARCHAR2(50),

constraint PK_STUDENT primary key (STUID) );

/==============================================================/

/* Table: TEACHING_INFORMATION */

/==============================================================/

create table TEACHING_INFORMATION (

STAFFID CHAR(8) not null,

COURSEID CHAR(8) not null,

SEMESTER CHAR(2) not null,

YEAR NUMBER(4) not null,

constraint PK_TEACHING_INFORMATION primary key (STAFFID, COURSEID, SEMESTER, YEAR) );

/==============================================================/

/* Table: TUT_PRAC */

/==============================================================/

create table TUT_PRAC (

CLASSID CHAR(8) not null,

COURSEID CHAR(8) not null,

STAFFID CHAR(8) not null,

TYPE VARCHAR2(5),

DAY VARCHAR2(10),

TIME DATE,

ROOMID CHAR(10),

NO_OF_SEATS NUMBER(2),

constraint PK_TUT_PRAC primary key (CLASSID) );

alter table ACADEMIC_REC add constraint FK_ACADEMIC_RELATIONS_STUDENT foreign key (STUID) references STUDENT (STUID);

alter table ACADEMIC_REC add constraint FK_ACADEMIC_RELATIONS_COURSE foreign key (COURSEID) references COURSE (COURSEID);

alter table COURSE add constraint FK_COURSE_OFFERS_DEPARTME foreign key (DEPTID) references DEPARTMENT (DEPTID);

alter table ENROLS add constraint FK_ENROLS_ENROLS_STUDENT foreign key (STUID) references STUDENT (STUID);

alter table ENROLS add constraint FK_ENROLS_ENROLS2_TUT_PRAC foreign key (CLASSID) references TUT_PRAC (CLASSID);

alter table HAS_PREREQUISITE add constraint FK_HAS_PRER_HAS_PRERE_COURSE foreign key (COURSEID) references COURSE (COURSEID);

alter table HAS_PREREQUISITE add constraint FK_HAS_PRER_HAS_PRERE_COURSE foreign key (PREREQUISITEID) references COURSE (COURSEID);

alter table STAFF add constraint FK_STAFF_RELATIONS_DEPARTME foreign key (DEPARTMENTID) references DEPARTMENT (DEPTID);

alter table TEACHING_INFORMATION add constraint FK_TEACHING_RELATIONS_STAFF foreign key (STAFFID) references STAFF (STAFFID);

alter table TEACHING_INFORMATION add constraint FK_TEACHING_RELATIONS_COURSE foreign key (COURSEID) references COURSE (COURSEID);

alter table TUT_PRAC add constraint FK_TUT_PRAC_HAS_COURSE foreign key (COURSEID) references COURSE (COURSEID);

alter table TUT_PRAC

add constraint FK_TUT_PRAC_RELATIONS_STAFF foreign key (STAFFID) references STAFF (STAFFID);

解决方案

I tried to edit your code moving some checks around to avoid unuseful queries and showing you what to edit; follow the comments:

CREATE OR REPLACE PROCEDURE NEW_COURSE (
  CID   IN COURSE.COURSEID%TYPE, 
  CNAME IN COURSE.COURSENAME%TYPE,
  PID   IN HAS_PREREQUISITE.PREREQUISITEID%TYPE,
  DID   IN DEPARTMENT.DEPTID%TYPE
)
IS 
  CONDITION1 NUMBER;
  CONDITION2 NUMBER;
  WRONG_PREQ EXCEPTION;
BEGIN
  /* this can be checked before any SQL */
  IF (SUBSTR(CID,5,3) <= SUBSTR(PID,5,3)) THEN /*PREREQUISITE HAS A HIGHER ID THAN COURSEID */
    RAISE WRONG_PREQ;
  END IF;



  SELECT COUNT(COURSEID) 
    INTO CONDITION1
    FROM  COURSE 
   WHERE COURSEID = PID;

  /* this can be checked here, without running the second select */
  IF (CONDITION1 <>1)  THEN /*VIOLATION OF CONDITION 1, NO EXISTING 
                              PREREQUISITE COURSE*/
    RAISE WRONG_PREQ;
  END IF; 


  SELECT COUNT(*)
    INTO CONDITION2
    FROM HAS_PREREQUISITE
   WHERE COURSEID = CID 
     AND PREREQUISITEID = PID 
     AND SUBSTR(CID,5,3) > SUBSTR(PID,5,3);

  IF (CONDITION2 = 1) THEN /*THE RECORD ALREADYS EXISTS IN HAS_PREREQUISITE 
                             TABLE*/
    RAISE WRONG_PREQ;
  END IF;



  INSERT INTO COURSE VALUES(CID,CNAME,'NULL','NULL' );  /* edit the statement to fit the table structure  also 'NULL' or null ?*/
  INSERT INTO HAS_PREREQUISITE VALUES(CID,PID);         /* the table only has 2 colums */
  INSERT INTO DEPARTMENT VALUES (DEPTID,'NULL');        /* edit the statement to fit the table structure. also 'NULL' or null ? */
EXCEPTION
  WHEN WRONG_PREQ THEN
    DBMS_OUTPUT.PUT_LINE('COURSEID OR PREREQUISITE ID IS INVALID');
END NEW_COURSE;

这篇关于我在我的程序中收到错误，有助于添加新课程，并在描述中给出了要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！