CODEWITHSUNDEEP
oracle-databaseplsql
Setu Kumar Basak
Setu Kumar Basak

Reputation: 12042

How to do condition check in PLSQL

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:

BRANDID ACTIVITYCODE
b01 A01
b01 A02
b02 A01
b02 A02

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 there's 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;

Error starting at line : 32 in command -
DECLARE ret int;
BEGIN
  add_re_rule('b01', 'AOS', 60, ret);
END;
Error report -
ORA-02291: integrity constraint (C##SCOTT.FK_ACTIVITYCODE_RE) violated - parent key not found
ORA-06512: at "C##SCOIT.ADD RE RULE", line 23
ORA-06512: at line 3
02291. 00000 - "integrity constraint (%s.%s) viclated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.

Am I missing anything?

Upvotes: 1

Views: 65

Answers (1)

doberkofler
doberkofler

Reputation: 10421

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.

Upvotes: 1

Related Questions