CODEWITHSUNDEEP
loopsplsqlnested
Shlomix
Shlomix

Reputation: 65

PLSQL and loops

I have a table that sometimes gets updated with records that miss a '0' or several consecutive zeroes. The records length should be 8 digits beyond the decimal point (10 characters in total).

For example - a record that originally looks like 1.12345600 will end up like 1.123456

I made a script that checks all the records length and adds to each record that is shorter than 10 characters a '0'.

The problem is that it only add a '0' once. The above example would look like 1.1234560 and not 1.12345600 as I want it. This is the script :

DECLARE

       CURSOR dif IS
            SELECT CUST_CODE, CUST_ID, CONTRACT_NUM, MSISDN 
            FROM project1;


BEGIN

        FOR a in dif LOOP

            IF LENGTH (a.CUST_CODE)<10
            THEN
            UPDATE project1
            SET CUST_CODE=a.CUST_CODE||'0'      
            WHERE CUST_CODE=a.CUST_CODE;
            END IF;
        END LOOP;
        commit;

END;

After it finishes running, a single '0' is added. If I'll run the script again, it will add another '0' to any records that is still shorter than 10 characters. Reocrds that have 7 characters would require running it a 3rd time.

I'm guessing there should be another loop somewhere to keep checking the records until it all reaches the required length. Any idea?

Upvotes: 1

Views: 334

Answers (5)

redsoxlost
redsoxlost

Reputation: 1235

DECLARE

       CURSOR dif IS
            SELECT CUST_CODE, CUST_ID, CONTRACT_NUM, MSISDN 
            FROM project1;
v_temp number;

BEGIN

        FOR a in dif LOOP
v_temp := 10-LENGTH (a.CUST_CODE)
            IF LENGTH (a.CUST_CODE)<10
            THEN
            UPDATE project1
            SET CUST_CODE=RPAD(a.CUST_CODE,v_temp,'0') ;
            WHERE CUST_CODE=a.CUST_CODE;
            END IF;
        END LOOP;
        commit;

END;

You can use that above query

Upvotes: 0

user2912405
user2912405

Reputation: 133

You can use rpad to solve your problem! Below follow the reference documentation. http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions140.htm

Upvotes: 0

Harshit
Harshit

Reputation: 560

If you want to implement it with SQL given above are the best ways but is you want to implement it via PLSQL you need to correct your logic. You are running the loop for the number of records retched in the cursor and then appending each one with a Single zero i.e. '0' which is exactly what you have written in code.

If you want it to make to length 10 then implement it as below.

             IF LENGTH (a.CUST_CODE)<10
        THEN
        v_len = 10-length (a.cust_code-- declare a variable
        for 
        i in 1..v_len
        loop
        v_pad=v_pad||'0';
        end loop;
        UPDATE project1
        SET CUST_CODE=a.CUST_CODE|| v_pad      
        WHERE CUST_CODE=a.CUST_CODE;
        END IF;

Hope this will help you to implement in PLSQL

Upvotes: 0

Kirill Leontev
Kirill Leontev

Reputation: 10931

If I were you I'd do it with a single statement.

UPDATE project1 SET CUST_CODE=rpad(CUST_CODE, 10, '0');

Upvotes: 3

Shepherdess
Shepherdess

Reputation: 651

You can use rpad function: http://www.techonthenet.com/oracle/functions/rpad.php (the third example in the link is what you want). 

 SET CUST_CODE=rpad(a.CUST_CODE, 10, '0')

Upvotes: 3

Related Questions