Having problems with this select in Oracle PLSQL-CodePudding

I have this code:

create view nombres_lab as
    select L.NOMBRE_LAB, R.NOM_REG, count(*) as vacunas_suministradas
    from LABORATORIOS L
    inner join CREA_VACUNAS CV on l.cod_lab = cv.cod_lab
    inner join vacunas V on cv.cod_vac = v.cod_vac
    inner join vtorio VT on v.cod_vac = vt.cod_vac
    inner join datos_vac_sum DVS on vt.cod_vtorio = dvs.cod_vtorio
    inner join frasco_vac FV on dvs.s_unica = fv.s_unica
    inner join direccion D on vt.cod_dir = d.cod_dir
    inner join region R on d.cod_reg = r.cod_reg
    where v.cod_vac = 100202 or v.cod_vac = 100303
    group by l.nombre_lab, r.nom_reg;


create or replace procedure ranking is

    
begin
    
    
    select nombre_lab, nom_reg, count(*) as vacunas_suministradas, rank() over(order by count(*) desc) as ranking from nombres_lab group by nom_reg, nombre_lab;
    

end;

I've been trying to convert this select to an select into so that it can work inside the procedure, but nothing that i try works.

Error: PLS-00428: an INTO clause is expected in this SELECT statement

I know its weird to do just a select in the procedure and nothing more, but i want to know if its possible

CodePudding user response：

The count(*) inside a rank function seems like an issue. Try below, I think that should solve the problem.

WITH nombre_lab_summary AS
(
   SELECT
     nombre_lab, 
     nom_reg, 
     COUNT(*) AS vacunas_suministradas, 
   FROM nombres_lab
   GROUP BY
     nom_reg, 
     nombre_lab
)

SELECT 
  *,
  RANK() OVER (ORDER BY vacunas_suministradas DESC) AS ranking 
FROM nombre_lab_summary
GROUP BY
  nom_reg, 
  nombre_lab

CodePudding user response：

To answer your question (whether it is possible to do something like that): as Justin commented, it is (kind of), but you have to follow some rules.

For example (based on Scott's schema, as I don't have your tables):

SQL> CREATE OR REPLACE VIEW nombres_lab
  2  AS
  3     SELECT deptno nombre_lab, job nom_reg FROM emp;

View created.

This is a procedure which fixes error you got ("an INTO clause is expected in this SELECT statement"): declare some local variables and select into them.

SQL> CREATE OR REPLACE PROCEDURE ranking
  2  IS
  3     l_nombre_lab             nombres_lab.nombre_lab%TYPE;
  4     l_nom_reg                nombres_lab.nom_reg%TYPE;
  5     l_vacunas_suministradas  NUMBER;
  6     l_ranking                NUMBER;
  7  BEGIN
  8       SELECT nombre_lab,
  9              nom_reg,
 10              COUNT (*),
 11              RANK () OVER (ORDER BY COUNT (*) DESC)
 12         INTO l_nombre_lab,
 13              l_nom_reg,
 14              l_vacunas_suministradas,
 15              l_ranking
 16         FROM nombres_lab
 17     GROUP BY nom_reg, nombre_lab;
 18  END;
 19  /

Procedure created.

Will it work? Unfortunately, not. It would if query returned only one row, though:

SQL> EXEC ranking;
BEGIN ranking; END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.RANKING", line 8
ORA-06512: at line 1

So, what to do? You could use a loop (then you don't need any variables nor an into clause) and process values fetched. I don't have any smart ideas so I'm just displaying what cursor returned:

SQL> CREATE OR REPLACE PROCEDURE ranking
  2  IS
  3  BEGIN
  4     FOR cur_r IN (  SELECT nombre_lab,
  5                            nom_reg,
  6                            COUNT (*) vacunas_suministradas,
  7                            RANK () OVER (ORDER BY COUNT (*) DESC) ranking
  8                       FROM nombres_lab
  9                   GROUP BY nom_reg, nombre_lab)
 10     LOOP
 11        DBMS_OUTPUT.put_line (
 12              cur_r.nombre_lab
 13           || ' - '
 14           || cur_r.nom_reg
 15           || ' - '
 16           || cur_r.vacunas_suministradas
 17           || ' - '
 18           || cur_r.ranking);
 19     END LOOP;
 20  END;
 21  /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC ranking;
30 - SALESMAN - 4 - 1
20 - ANALYST - 2 - 2
20 - CLERK - 2 - 2
20 - MANAGER - 1 - 4
30 - CLERK - 1 - 4
30 - MANAGER - 1 - 4
10 - MANAGER - 1 - 4
10 - CLERK - 1 - 4
10 - PRESIDENT - 1 - 4

PL/SQL procedure successfully completed.

SQL>

Or, you could switch to a function that returns e.g. ref cursor:

SQL> CREATE OR REPLACE FUNCTION f_ranking
  2     RETURN SYS_REFCURSOR
  3  IS
  4     rc  SYS_REFCURSOR;
  5  BEGIN
  6     OPEN rc FOR   SELECT nombre_lab,
  7                          nom_reg,
  8                          COUNT (*),
  9                          RANK () OVER (ORDER BY COUNT (*) DESC)
 10                     FROM nombres_lab
 11                 GROUP BY nom_reg, nombre_lab;
 12
 13     RETURN rc;
 14  END;
 15  /

Function created.

SQL> var l_rc refcursor
SQL> exec :l_rc := f_ranking

PL/SQL procedure successfully completed.

SQL> print l_rc

NOMBRE_LAB NOM_REG     COUNT(*) RANK()OVER(ORDERBYCOUNT(*)DESC)
---------- --------- ---------- -------------------------------
        30 SALESMAN           4                               1
        20 ANALYST            2                               2
        20 CLERK              2                               2
        20 MANAGER            1                               4
        30 CLERK              1                               4
        30 MANAGER            1                               4
        10 MANAGER            1                               4
        10 CLERK              1                               4
        10 PRESIDENT          1                               4

9 rows selected.

SQL>

Therefore, yes - it is possible, but it depends on what you actually want to do.