0
    DECLARE
         CUSTID ACCOUNT.CUST_ID%TYPE;
         ANO ACCOUNT.A_NO%TYPE;
         TYP ACCOUNT.TYPE%TYPE;
         BALANC ACCOUNT.BALANCE%TYPE;
         STATU ACCOUNT.STATUS%TYPE;
         CURSOR S2 IS SELECT CUST_ID , A_NO , TYPE , BALANCE, STATUS FROM ACCOUNT WHERE STATUS = 'locked' ORDER BY BALANCE desc;
    BEGIN
OPEN S2;
FOR A IN 1..3 LOOP
        FETCH S2 INTO CUSTID ,ANO , TYP, BALANC, STATU ;
            INSERT INTO ACC_LOCKED (A_NO, CUST_ID, TYPE, BALANCE, STATUS)
            SELECT  CUST_ID ,A_NO , TYPE , BALANCE, STATUS FROM ACCOUNT     WHERE STATUS = 'locked' ORDER BY BALANCE desc;
     END LOOP;
     CLOSE S2;
 END;




ORA-00001: unique constraint (PK_ACC_LOCKED) violated

I wonder how to do it with cursor.I can do it without it, but I want to make it differently.

TuralAsgar
  • 1,275
  • 2
  • 13
  • 26
Eng. Sultan
  • 17
  • 1
  • 7
  • you don't need a cursor or PL/SQL for that. A simple `insert into ... select ...` will be a **lot** more efficient –  Apr 28 '17 at 13:58
  • I know but in this case i wana insert the highest 3 balance from the cursor using loop with number 3 . – Eng. Sultan Apr 28 '17 at 14:00
  • Then just select the three highest values. –  Apr 28 '17 at 14:03
  • would you please tell me how to do it ? – Eng. Sultan Apr 28 '17 at 14:04
  • I already told you in your other question: http://stackoverflow.com/a/43679237/330315 –  Apr 28 '17 at 15:22
  • If you must use a cursor, which as pointed out is absolutely not necessary, then you need to replace the select statement inside the loop with a VALUES clause. As currently written what you are actually trying to do is insert ALL accounts with status 'locked' 3 times. BTW the sequence of columns in the insert doesn't agree with the select. – Belayer Apr 29 '17 at 21:04

0 Answers0