So i'm trying to create some seed data for a database that uses zip codes. I've created an array of 22 arbitrary zip code strings, and i'm trying to loop through the array and update one of the zips to every row in a table. Based on what I read and tried (I'm a 1st year, so I'm probably missing something), this should work, and does when I just output the array value based on the count of the table. this issue is in the row id subquery. When I run it in my console, it doesn't throw any errors, but it never completes and I think it's stuck in an infinite loop. How can I adjust this so that it will update the field and not get stuck?

declare
    t_count NUMBER;
    TYPE zips IS VARRAY(22) OF CHAR(5);
    set_of_zips zips;
    i NUMBER;
    j NUMBER :=1;
BEGIN
    SELECT count(*) INTO t_count FROM T_DATA;
    set_of_zips:= zips('72550', '71601', '85920', '85135', '95451', '90021', '99611', '99928', '35213', '60475', '80451', '80023', '59330', '62226', '27127', '28006', '66515', '27620', '66527', '15438', '32601', '00000');
    FOR i IN 1 .. t_count  LOOP
        UPDATE T_DATA
        SET T_ZIP=set_of_zips(j)
        ---
        WHERE           rowid IN (
            SELECT ri FROM (
                SELECT rowid AS ri 
                FROM T_DATA
                ORDER BY T_ZIP
            )
        ) = i;
        ---
        j := j   1;
        IF j > 22 
            THEN 
                j := 1;
        END IF;
    END LOOP;
    COMMIT;
end;

CodePudding user response：

You don't need PL/SQL for this.

UPDATE t_data
SET t_zip = DECODE(MOD(ROWNUM,22) 1,
1,'72550',
2,'71601',
3,'85920',
4,'85135',
5,'95451',
6,'90021',
7,'99611',
8,'99928',
9,'35213',
10,'60475',
11,'80451',
12,'80023',
13,'59330',
14,'62226',
15,'27127',
16,'28006',
17,'66515',
18,'27620',
19,'66527',
20,'15438',
21,'32601',
22,'00000')