SQL过程-比较值

Question

我正在尝试创建一个在完成某些检查后将信息添加到表中的过程。其中一项检查要求比较另一个表中是否已存在另一个表中的字段。我目前正在尝试寻找一种方法来将单个字段与另一个表的整个列进行比较，以查看它们是否匹配。
例如。单个字段是PrerequisiteID，我需要确保此ID在 课程
表中的下存在CourseID。我正在努力研究如何比较的单列prerequisiteID和整列courseID。任何帮助，将不胜感激。

我正在做的实际问题是：

编写一个程序 NEW_COURSE ，该程序使用给定的课程详细信息来提供全新的课程。在开设新课程之前，该程序将确保：

1. 它的前提课程（如果有的话）已经存在于“ 课程” 表中。为简单起见，我们假装一门课程最多只能有一门课程为前提。

2. 课程的先决条件必须处于较低水平。例如，ISYS326的前提条件必须是100级或200级课程，而不能是300级课程。

如果以上任一要求失败，则该过程将引发一个异常WRONG_PREREQ，该异常将打印适当的警报消息，并且不会修改 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), 
    constraint PK_COURSE primary key (COURSEID) 
);

create table HAS_PREREQUISITE ( 
    COURSEID CHAR(8) not null, 
    PREREQUISITEID CHAR(8) not null, 
    constraint PK_HAS_PREREQUISITE primary key (COURSEID, PREREQUISITEID) 
);

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_COURSE1 foreign key (PREREQUISITEID) 
references COURSE (COURSEID);

到目前为止，这是我的过程，我知道这是不正确的，我只是想在上面做一些事情：

CREATE OR REPLACE 
PROCEDURE NEW_COURSE
(
    c_course_ID      COURSE.COURSEID%type,
    c_dept_ID         COURSE.DEPTID%type,
    c_coursename    COURSE.COURSENAME%type,
    c_textbook         COURSE.TEXTBOOK%type,
    c_credithour       COURSE.CREDITHOUR%type,
    c_max_enrol       COURSE.MAX_ENROL%type,
    c_actual_enrol    COURSE.ACTUAL_ENROL%type,
    c_availability      COURSE.AVAILABILITY%type,
    p_prerequisite_ID  HAS_PREREQUISITE.PREREQUISITEID%type
)

IS

BEGIN

  IF (p_prerequisite_ID != COURSE.courseID) THEN WRONG_PREREQ (-20205, 'That prerequisite does not exist');
  ELSE IF( p_prerequisite_ID > c_course_ID) THEN WRONG_PREREQ(-20205, 'That is not a valid prerequisite');
  ELSE
  INSERT INTO COURSE (COURSEID, DEPTID, COURSENAME, TEXTBOOK, CREDITHOUR, MAX_ENROL, ACTUAL_ENROL, AVAILABILITY)
  VALUES (c_course_ID, c_dept_ID, c_coursename, c_textbook, c_credithour, c_max_enrol, c_actual_enrol, c_availability);
  END IF;

END NEW_COURSE;

课程数据：

+----------+----------+------------+----------+------------+-----------+--------------+--------------+
| CourseID |  DeptID  | COURSENAME | TEXTBOOK | CREDITHOUR | MAX_ENROL | ACTUAL_ENROL | AVAILABILITY |
+----------+----------+------------+----------+------------+-----------+--------------+--------------+
| ISYS224  | FSE      |            |          |            |           |              |              |
| COMP225  | FSE      |            |          |            |           |              |              |
| ISYS114  | FSE      |            |          |            |           |              |              |
| COMP115  | FSE      |            |          |            |           |              |              |
+----------+----------+------------+----------+------------+-----------+--------------+--------------+

先决条件没有任何数据，但这是表的定义：

+----------------+--------------+
|  Column Name   |  Data Type   |
+----------------+--------------+
| COURSEID       | CHAR(8 BYTE) |
| PREREQUISITEID | CHAR(8 BYTE) |
+----------------+--------------+

Answer 1

因此，我们这里拥有的是智能密钥，而智能密钥却是愚蠢的（因为它们难以验证，并且在使用程序的一部分上总是需要额外的代码）。COURSEIDISYS224分为三个部分：

• ISYS =主题
• 2 =课程水平
• 24 =班级编号（例如）

如果您的数据模型是正确的（显然不是您的老师的错，那是您的老师的错），解决方案将很简单，因为查询将很明显。

实际上，您需要自己将COURSEID分解成其组成部分。我们假设SUBJECT总是四个字符，因为其他任何事情都会使人感到悲伤。然后查询是：

select c.courseid
from course c 
where substr(c.courseid, 1, 4) = substr(p_prerequisite_id, 1, 4)
and substr(c.courseid, 5, 1) < substr(p_prerequisite_id, 5, 1)
;

如果先决条件不符合规则，则将抛出NO_DATA_FOUND。使用异常部分可以很好地处理此问题。

或者，您可以选择必备课程记录到局部变量中，然后在PL / SQL代码中进行切入。

select c.courseid into l_prerequisite_course
from course c 
where c.courseid = p_prerequisite_id;

if substr(l_prerequisite_course, 1, 4) = substr(p_prerequisite_id, 1, 4)
or substr(l_prerequisite_course, 5, 1) < substr(p_prerequisite_id, 5, 1)
then raise ...
;