database - 使用 "minus' 操作来查找是否满足条件

Question

好的，所以我正在尝试使用 pl/sql 来查明是否存在值，或者没有值。我知道我可能不得不使用“计数”，但不确定如何使用或在何处使用。这是我目前所拥有的...

CREATE OR REPLACE PROCEDURE Validate_Prereq_Met
AS
(
    p_snum      IN Enrollments.snum%TYPE,
    p_callnum   IN Enrollments.Callnum%TYPE
)
    v_pcnum   VARCHAR2;
    v_cnum    VARCHAR2;
BEGIN
    --classes needed minus classes taken
    SELECT pcnum AS v_pcnum
      FROM prereq
     WHERE cnum IN (SELECT cnum
                      FROM schclasses
                     WHERE callnum = p_callnum)
    MINUS
    SELECT cnum AS v_cnum
      FROM enrollments, schclasses
     WHERE enrollments.callnum = schclasses.callnum AND snum = p_snum;
END;

我需要它不返回任何内容(意味着满足要求)或返回一个值(意味着不满足要求)。

Answer 1

你有两个小错别字:

参数后面的“As”。替换 plsql block 的“声明”

如果 VARCHAR 不是参数，则必须为其定义长度(例如:VARCHAR2(100))。但是您想使用 NUMBER 来计算一些东西。

如何减去值

CREATE OR REPLACE PROCEDURE Validate_Prereq_Met
(
    p_snum      IN Enrollments.snum%TYPE,
    p_callnum   IN Enrollments.Callnum%TYPE
)
AS
    v_pcnum   NUMBER;
    v_cnum    NUMBER;
    v_result  NUMBER;
BEGIN
    --classes needed minus classes taken
    SELECT pcnum AS v_pcnum
      FROM prereq
     WHERE cnum IN (SELECT cnum
                      FROM schclasses
                     WHERE callnum = p_callnum)

    SELECT cnum AS v_cnum
      FROM enrollments, schclasses
     WHERE enrollments.callnum = schclasses.callnum AND snum = p_snum;

   v_result := v_pcnum - v_cnum;
   dbms_output.put_line('v_result : ' || v_result ); 
END;

如何用这些做一个函数

如果您声明一个函数，您可以返回结果值:

CREATE OR REPLACE FUNCTION Validate_Prereq_Met (
    p_snum      IN Enrollments.snum%TYPE,
    p_callnum   IN Enrollments.Callnum%TYPE)
    RETURN NUMBER
AS
    v_pcnum    NUMBER;
    v_cnum     NUMBER;
    v_result   NUMBER;
BEGIN
    --classes needed minus classes taken
    SELECT pcnum AS v_pcnum
      FROM prereq
     WHERE cnum IN (SELECT cnum
                      FROM schclasses
                     WHERE callnum = p_callnum);

    SELECT cnum AS v_cnum
      FROM enrollments, schclasses
     WHERE enrollments.callnum = schclasses.callnum AND snum = p_snum;

    v_result := v_pcnum - v_cnum;
    RETURN v_result;
END;

您使用了 MINUS

您使用的 MINUS 不会减去值。它用于从一组值中减去另一组值。

例子:

我们有两个表 - 一个包含 1,2 和 3，一​​个包含 1 和 3。现在我们可以从表 1 中减去表 2 的值。

SELECT *
  FROM (SELECT 1 FROM DUAL -- fake-table 1: 1,2,3
        UNION ALL
        SELECT 2 FROM DUAL
        UNION ALL
        SELECT 3 FROM DUAL)
MINUS
SELECT *
  FROM (SELECT 1 FROM DUAL -- fake-table 2: 1,3
        UNION ALL
        SELECT 3 FROM DUAL)

-- Result: 2