CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlsql-deletedata-integrity
StevieP
StevieP

Reputation: 131

PL/SQL Procedure to Delete Specific Rows

Trying to create a stored procedure to delete all goods supplied by a distributor (x) as well as the references to the goods in my LINE table. My problem is there are 3 tables in total. So i need to delete the references from my LINE table first before deleting the items in GOODS table and I can't seem to figure it out correctly. I feel like I have it just need some help with a few syntax errors.

CREATE OR REPLACE PROCEDURE PRC_DeleteProd(x IN INTEGER)
AS
    v_dcode    distributor.d_code%type;
    v_gcode    goods.g_code%type;
    v_gcode2   line.g_code%type;

 CURSOR v_delete_cursor IS
   SELECT goods.g_code, line.g_code , d_code
     FROM distributor
     JOIN goods ON (distributor.d_code = goods.g_code)
     JOIN line ON (goods.g_code = line.g_code);

BEGIN

  OPEN v_delete_cursor;

  LOOP
    FETCH v_delete_cursor INTO v_dcode, v_gcode, v_gcode2;
    EXIT WHEN v_cus_cursor%NOTFOUND;

    IF x = v_dcode THEN
      DELETE FROM line WHERE v_gcode2 = x;

      DELETE FROM goods WHERE v_gcode = x;
    END IF;
  END LOOP;
END;
/

Upvotes: 1

Views: 7546

Answers (1)

Simimmo
Simimmo

Reputation: 668

You are not using correclty AND keyword

DELETE FROM line
WHERE v_gcode = x
AND
DELETE FROM goods
WHERE v_gcode = x;

should be

DELETE FROM line
WHERE v_gcode = x;

DELETE FROM goods
WHERE v_gcode = x;

Upvotes: 2

Related Questions