CODEWITHSUNDEEP
plsql
Ti J
Ti J

Reputation: 271

PL/SQL How to iterate and update from an query inside a loop

I don't know how to iterate and update from a query result inside the loop. Is it possible to loop again from the query inside my first loop? Here is my code:

CREATE OR REPLACE PROCEDURE "myTEST" (sp_type in char)

IS
CURSOR c1 IS  

    SELECT SP_ID FROM CI_SP
    WHERE SP_TYPE_CD = sp_type;

    sp_id char(10);
    item_id_eq CI_SP_EQ.ITEM_ID_EQ%type;
BEGIN

FOR sp_rec in c1
LOOP
  DBMS_OUTPUT.PUT_LINE(sp_rec.sp_id); 

  SELECT ITEM_ID_EQ INTO item_id_eq FROM CI_SP_EQ
  WHERE SP_ID = sp_rec.sp_id;      
  DBMS_OUTPUT.PUT_LINE('item id eq :' || item_id_eq); 

  -- iterate here for each item_id_eq
  -- execute update for each item_id_eq also
END LOOP;

END myTEST;

Upvotes: 0

Views: 163

Answers (1)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

Instead of looping twice you could just do a join between CI_SP & CI_SP_EQ and get it done in one shot:

CREATE OR REPLACE PROCEDURE "myTEST"(sp_type IN CHAR) IS
BEGIN
  FOR item IN (SELECT item_id_eq
                 FROM ci_sp_eq JOIN ci_sp USING (sp_id)
                WHERE sp_type_cd = sp_type) LOOP
    -- do your stuff.
    NULL;
  END LOOP;
END mytest;

I think you wouldn't even need a PL/SQL block, just a simple UPDATE will do, but I don't exactly know what you're trying to do.

Some other comments:

  • Don't create objects enclosed in "quotes", the object name is now case sensitive. In your case, the compilation will fail because you've created procedure name as "myTEST" and end it with mytest, which Oracle will treat it as "MYTEST" and you'll get compile error because of syntax check fail
  • Use VARCHAR2 instead of CHAR, CHAR will pad spaces if the input doesn't match the length specifier and will lead to further problems

Upvotes: 2

Related Questions