pregunta 5 lab 4 bd

CREATE OR REPLACE PROCEDURE P_Top3Clientes( pMontoTotal OUT NUMBER) IS
BEGIN

--Cantidad Clientes sobre monto mas vendido
SELECT SUM(TOTAL) INTO pMontoTotal
FROM
(
SELECT IDCLIENTE, TOTAL FROM
(SELECT  CLI.IDCLIENTE, SUM( PED.MONTOTOTAL) TOTAL
FROM L4_CLIENTE CLI , L4_PEDIDO PED
WHERE  CLI.IDCLIENTE = PED.IDCLIENTE
AND TO_CHAR(PED.FECHAPEDIDO, 'MM-YYYY') = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM-YYYY')
GROUP BY CLI.IDCLIENTE
ORDER BY 2 DESC
) CLIENTES
WHERE ROWNUM<=3
) CLIENTES2;

--update de clasificacion de clientes
UPDATE L4_CLIENTE
SET IDCLASCLIENTE=5
WHERE IDCLASCLIENTE IN (SELECT IDCLIENTE
                      FROM (SELECT IDCLIENTE, TOTAL
                            FROM (SELECT  CLI.IDCLIENTE, SUM( PED.MONTOTOTAL) TOTAL
                                  FROM L4_CLIENTE CLI , L4_PEDIDO PED
                                  WHERE  CLI.IDCLIENTE = PED.IDCLIENTE
                                  AND TO_CHAR(PED.FECHAPEDIDO, 'MM-YYYY') = TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM-YYYY')
                                  GROUP BY CLI.IDCLIENTE
                                  ORDER BY 2 DESC) CLIENTES
                                  WHERE ROWNUM<=3));

END P_Top3Clientes;