How to do condition check in PLSQL-CodePudding

I have a table like below:

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
);

And the current data the table holds below:

Now, I have written a stored procedure to check if new inserted row brandId and ActivityCode is already present or not in BRANDACTIVITYTYPE table. If present, then no need to insert the tuple in another table.

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;
/

When I run below query, it return 0 which is correct.

SELECT COUNT(BRANDID) FROM BRANDACTIVITYTYPE WHERE BRANDID = 'b01' AND ACTIVITYCODE = 'A05';

But, when I run the stored procedure, it returns 4 means all the tuple count of the BRANDACTIVITYTYPE table which is wrong and goes into the else condition. It should return 0.

SET SERVEROUTPUT ON;
DECLARE ret int;
BEGIN
   add_re_rule('b01', 'A05', 60, ret);
END;

Am I missing anything?

CodePudding user response：

You have used the same names for the parameter of the procedure and the column names and therefore the where clause of for example SELECT COUNT(BRANDID) INTO SAMERULECOUNT FROM RERULE WHERE BRANDID = brandId AND ACTIVITYCODE = activityCode; will not do as intended. Just change the names of the parameter.