CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
J.Lee
J.Lee

Reputation: 91

Using explicit cursor and loop, can't find error

I'm trying to figure out the code to add a new column to the ATA_ENTERTAINER table called MORE_THAN_ONE with a NUMBER datatype (this code will be outside of PL/SQL code). This column will hold the number of style types an entertainer has if they have more than one (if they have one, place a NULL in this column).

My code should, using an explicit cursor and loop structure, go through each entertainer and determine the number of styles they have. If an entertainer has more than one, I need modify the value in the MORE_THAN_ONE column for that entertainer with the number of styles (a NULL should be put in this column if not more than one). I also need to use FOR UPDATE and WHERE CURRENT OF as part of my solution. Using a basic loop to solve this problem, and an IF for any decision structures.

This is what I have right now. It doesn't show me any errors, but somehow, my table doesn't update/change when I run this code.

DECLARE
    ata_entId ata_entertainer.entertainer_id%TYPE; 
    ata_st_entId ata_entertainers_style.entertainer_id%TYPE; 
    ata_more ata_entertainer.more_than_one%TYPE;
    ata_count NUMBER(10) := 0; 

    CURSOR ata_rec IS 
        SELECT  entertainer_id
        FROM ata_entertainers_style
        WHERE ata_st_entId = ata_entId
        FOR UPDATE;
BEGIN 
    OPEN ata_rec; 
    LOOP 
    FETCH  ata_rec INTO ata_st_entId; 
    EXIT WHEN ata_rec%NOTFOUND; 
    IF ata_st_entId = ata_entId THEN ata_more := ata_count+1; 
    UPDATE ata_entertainer SET more_than_one = ata_more
    WHERE CURRENT of ata_rec;
    END IF;
    END LOOP;
    CLOSE ata_rec; 
    END;
    /

enter image description here

Upvotes: 0

Views: 499

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

Solution could be this one:

DECLARE
    ata_more ata_entertainer.more_than_one%TYPE;

    CURSOR ata_rec IS 
        SELECT entertainer_id
        FROM ata_entertainer
        FOR UPDATE;
BEGIN 

   for aEntertainer in ata_rec LOOP
      select count(*)
      into ata_more
      from ATA_ENTERTAINERS_STYLE
      WHERE entertainer_id = aEntertainer.entertainer_id;
      IF ata_more > 1 then
          UPDATE ata_entertainer SET more_than_one = ata_more
          WHERE CURRENT of ata_rec;
      END IF;
   END LOOP;
END;

Actually I don't know by heart if WHERE CURRENT OF works in FOR ... IN LOOP - I leave this to be find out by you.

In real life you would run this update with a single statement:

update ata_entertainer a SET more_than_one = 
   (select NULLIF(count(*), 1) 
    FROM ATA_ENTERTAINERS_STYLE b 
    where a.entertainer_id = b.entertainer_id);

Upvotes: 1

Abra
Abra

Reputation: 20914

Think how you would do this yourself. You have a list of style codes and entertainer IDs. You need to go through the list and count how many times the same entertainer ID appears. You have to do this for every distinct entertainer ID in the list. That would mean going through the list several times. What if you could sort the list by entertainer ID? Then you would only need to go through the list once, because all the rows with the same entertainer ID appear together. Hence your cursor should be...

cursor ATA_REC is
  select ENTERTAINER_ID
    from ATA_ENTERTAINERS_STYLE
   order by ENTERTAINER_ID
for update;

So you start going through the rows in your list. As long as the entertainer ID of the current row is the same as that of the previous row, you increment your count. When a new entertainer ID appears, you start a new count, right? Also, when the entertainer ID changes, you have the number of styles for the last entertainer ID. If that count is more than one (1), you need to update the ATA_ENTERTAINER table.

Here is my solution. Note that it compiles (in Oracle 11g Express Edition) but I did not test it on sample data (too lazy :-)

declare
  ATA_ENT_ID        ATA_ENTERTAINER.ENTERTAINER_ID%type;
  L_ENTERTAINER_ID  ATA_ENTERTAINER.ENTERTAINER_ID%type;
  L_FIRST           boolean;
  L_SUM             number(3); -- Assume less than one thousand styles for single entertainer.
--
  cursor ATA_REC is
    select ENTERTAINER_ID
      from ATA_ENTERTAINERS_STYLE
     order by ENTERTAINER_ID
    for update;
begin
  L_ENTERTAINER_ID := -1;
  L_FIRST := true;
  L_SUM := 0;
  open ATA_REC;
  loop
    fetch ATA_REC into ATA_ENT_ID;
    exit when ATA_REC%notfound;
    if ATA_ENT_ID = L_ENTERTAINER_ID then
      L_SUM := L_SUM + 1;
    else
      if L_FIRST then
        L_FIRST := false;
        L_SUM := 1;
        L_ENTERTAINER_ID := ATA_ENT_ID;
      else
        if L_SUM > 1 then
          update ATA_ENTERTAINER
             set MORE_THAN_ONE = L_SUM
           where current of ATA_REC;
        end if;
      end if;
      L_SUM := 0;
      L_ENTERTAINER_ID := ATA_ENT_ID;
    end if;
  end loop;
--
  -- Make sure we update the last entertainer.
  if L_SUM > 1 then
    update ATA_ENTERTAINER
       set MORE_THAN_ONE = L_SUM
     where current of ATA_REC;
  end if;
end;

Upvotes: 1

Related Questions