Procedure in oracle

Question

I can not very well create procedures with the output values, how to receive a value, and return it after the call, someone could help me with an exercise that I am doubtful

Create a procedure that is named after a particular piece and present the total amount currently stored of this part and the location where it is stored the largest amount of this piece (Corridor Description and number of the Receptacle).

    create or replace PROCEDURE estoquePeca(pNome IN varchar2) 
   totalArmazenado number;
   localMaiorQuantidade number;
   descricaoCorredor varchar2;
   numeroReceptaculo number;

IS
select sum(p.quant_total) as total, r.num_recept, 
from receptaculos r, pecas p, corredores c
where pNome = p.descricao and p.peca = r.peca and r.cod_corr = c.corredor;

BEGIN

END;
end estoquePeca;

Answer 1

I didn’t create the tables to test, maybe I need some adjustments, but I believe this will serve as a good basis to improve the procedure:

--CHAMADA
BEGIN
  DECLARE
    v_maximo number;
    v_total number;
    v_local_desc varchar2(50);
    v_num_recep number;
  BEGIN
    estoquePeca(p_nome => "alguma coisa",
                p_maximo => v_maximo, 
                p_total => v_total, 
                p_local_desc => v_local_desc, 
                p_num_recep => v_num_recep);
    Dbms_Output.put_line(v_maximo);
    Dbms_Output.put_line(v_total);
    Dbms_Output.put_line(v_local_desc);
    Dbms_Output.put_line(v_num_recep);
  END;
END;

-- PROCEDURE
CREATE OR REPLACE PROCEDURE estoquePeca(p_nome IN varchar2, 
                                        p_maximo OUT NUMBER, 
                                        p_total OUT NUMBER, 
                                        p_local_desc OUT VARCHAR2(50), 
                                        p_num_recep OUT NUMBER) 
IS 
BEGIN 
   SELECT MAX(r.quant_atual), SUM(r.quant_atual), c.descricao, r.num_recept
     INTO p_maximo, p_total, p_local_desc , p_num_recep
     FROM receptaculos r, pecas p, corredores c
    WHERE p.descricao LIKE '%'||p_nome||'%'
      AND p.peca = r.peca 
      AND r.cod_corr = c.corredor;

END;
/