我有一張如下表：
CREATE TABLE BRANDACTIVITYTYPE(
BRANDID VARCHAR2(50),
ACTIVITYCODE VARCHAR2(10),
CONSTRAINT PK_BRANDACTY primary key(BRANDID, ACTIVITYCODE),
CONSTRAINT FK_BRAND_BRANDACTY FOREIGN KEY (BRANDID) REFERENCES BRAND(BID) ON DELETE CASCADE,
CONSTRAINT FK_ACTIVITYCODE_BRANDACTY FOREIGN KEY (ACTIVITYCODE) REFERENCES ACTIVITYTYPE(ACTIVITYCODE) ON DELETE CASCADE
);
表中的當前資料如下：
現在，我撰寫了一個存盤程序來檢查新插入的行brandId 和ActivityCode 是否已經存在于BRANDACTIVITYTYPE 表中。如果存在，則無需將元組插入另一個表中。
create or replace PROCEDURE add_re_rule
(
brandId IN VARCHAR2,
activityCode IN VARCHAR2,
points IN NUMBER,
ret OUT INT
)
AS
SAMERULECOUNT INT;
ACTYPECOUNT INT;
BEGIN
SELECT COUNT(BRANDID) INTO ACTYPECOUNT FROM BRANDACTIVITYTYPE WHERE BRANDID = brandId AND ACTIVITYCODE = activityCode;
SELECT COUNT(BRANDID) INTO SAMERULECOUNT FROM RERULE WHERE BRANDID = brandId AND ACTIVITYCODE = activityCode;
Dbms_Output.Put_Line(ACTYPECOUNT);
IF SAMERULECOUNT > 0 THEN
ret := 0;
ELSIF ACTYPECOUNT = 0 THEN
ret := 2;
ELSE
-- Insert into rerule table
INSERT INTO RERULE(BRANDID, ACTIVITYCODE, POINTS, VERSIONNO) values (brandId, activityCode, points, 1);
ret := 1;
END IF;
END;
/
當我在查詢下運行時，它回傳 0，這是正確的。
SELECT COUNT(BRANDID) FROM BRANDACTIVITYTYPE WHERE BRANDID = 'b01' AND ACTIVITYCODE = 'A05';
但是，當我運行存盤程序時，它回傳 4 表示 BRANDACTIVITYTYPE 表的所有元組計數是錯誤的并進入 else 條件。它應該回傳 0。
SET SERVEROUTPUT ON;
DECLARE ret int;
BEGIN
add_re_rule('b01', 'A05', 60, ret);
END;
我錯過了什么嗎？
uj5u.com熱心網友回復：
您對程序的引數和列名使用了相同的名稱，因此 example 的 where 子句SELECT COUNT(BRANDID) INTO SAMERULECOUNT FROM RERULE WHERE BRANDID = brandId AND ACTIVITYCODE = activityCode;不會按預期執行。只需更改引數的名稱。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/349020.html