How can i write a query in PL/SQL to find 3 maximum balance from Account table using cursor?

Question

DECLARE
     CUSTID NUMBER; ANO NUMBER; BALANC NUMBER; TYP ACCOUNT.TYPE%TYPE; STATU ACCOUNT.STATUS%TYPE;
     CURSOR S IS SELECT *  FROM ACCOUNT WHERE STATUS = 'active';
BEGIN
    OPEN S;
     FOR A IN 1..3 LOOP
        FETCH S 
        DBMS_OUTPUT.PUT_LINE('CUST ID : '||CUSTID||'   NO:'||ANO || '  TYPE :' || TYP || '   STATUS :' || STATU); 
     END LOOP;
CLOSE S;
END;

i'm trying to find 3 maximum balance on ACCOUNT table but it doesn't work !

" it doesn't work " isn't very helpful. Obviously looking at your code we can guess what might be going wrong. But why should we have to guess? You ought to tell us what happens when you run your code, if your get errors what they are, etc — APC, Apr 28 '17 at 11:25
>> SELECT * FROM ACCOUNT WHERE STATUS = 'active' ORDER BY BALANCE desc; << what should i put in this query in print a 3 maximum balance ? — Eng. Sultan, Apr 28 '17 at 11:31

score 0 · Answer 1 · answered Apr 28 '17 at 11:34

0

You don't need PL/SQL or a cursor for this:

SELECT * 
FROM 
  SELECT a.*,
         dense_rank() over (order by balance desc) as rnk
  FROM account 
  WHERE status = 'active' 
) t
WHERE rnk <= 3;

answered Apr 28 '17 at 11:34

score 0 · Answer 2 · answered Apr 28 '17 at 13:22

Like @a_horse_with_no_name wrote, you don't need pl/sql to do that. In fact, if you have oracle 12c, you even have order by offset. If yo REALLY want to do that in pl/sql you need to change that fetch, and add the INTO clause, for that, you'll need a rowtype variable:

declare
  CURSOR S IS SELECT *  FROM ACCOUNT WHERE STATUS = 'active';
  v_row account%rowtype;
begin
  OPEN S;
     FOR A IN 1..3 LOOP
        FETCH S into v_row;
        DBMS_OUTPUT.PUT_LINE('CUST ID : '||v_row.CUSTID||'   NO:'||v_row.ANO || '  TYPE :' || v_row.TYP || '   STATUS :' || v_row.STATU); 
     END LOOP;
CLOSE S;
end;

ps: there are a lot better ways to do that. I'm just mending your code.

score 0 · Answer 3 · answered Apr 30 '17 at 10:42

There is simple query for this:

select * from(select * from account order by balance desc)where rownum<=3

Embed it in your query

DECLARE
 CUSTID NUMBER; ANO NUMBER; BALANC NUMBER; TYP ACCOUNT.TYPE%TYPE; STATU ACCOUNT.STATUS%TYPE;
 CURSOR S IS (select * from(select * from account order by balance desc)where rownum<=3)

 BEGIN
OPEN S;
 FOR A IN 1..3 LOOP
    FETCH S 
    DBMS_OUTPUT.PUT_LINE('CUST ID : '||CUSTID||'   NO:'||ANO || '  TYPE :' || TYP || '   STATUS :' || STATU); 
 END LOOP;
CLOSE S;
END;

How can i write a query in PL/SQL to find 3 maximum balance from Account table using cursor?

3 Answers3

Linked