如何在PL/SQL中编写查询以使用游标从“帐户"表中找到3个最大余额? [英] How can i write a query in PL/SQL to find 3 maximum balance from Account table using cursor?

问题描述

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;

我正在尝试在ACCOUNT表上找到3个最大余额，但这不起作用！

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

推荐答案

对此有一个简单的查询:

There is simple query for this:

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

将其嵌入查询中

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;

这篇关于如何在PL/SQL中编写查询以使用游标从“帐户"表中找到3个最大余额?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！