Procedure

[Renata2016]

Develop a procedure that links the name and quantity of friends of
all the user who own more than X friends (informed as
parameter in the procedure). At each user print the
procedure should list the name of the friends of this user




And that's the code, but giving error. HELP.

Select all

CREATE OR REPLACE PROCEDURE busca_amigo(pqtd IN OUT number) 
IS 
BEGIN 
 
    SELECT COUNT(u.nomeusu) as qtdeamigo 
    FROM tbusuario u 
    WHERE u.pkcodusu = tbamigos.fkcodusua 
 
   GROUP BY u.pkcodusu 
   HAVING count(u.nomeusu) >= pqtd;       
 
    
   IF SQL%NOTFOUND 
         THEN raise_application_error('20013','Nome Invalido!', 'a'); 
  END IF; 
END;

[spernega]

Good morning Renata,

I did not test your code, but it is missing a into the Select from the procedure.

Select all

declare  v_qtdeamigo number;

In select

Select all

SELECT COUNT(u.nomeusu) into v_qtdeamigo 

will never enter the SQL% Notfound, as is a count, will always return a value, even if zero.

test

Select all

IF v_qtdeamigo = 0 then

[Renata2016]

I did it this way:

Select all

REATE OR REPLACE PROCEDURE busca_amigo(pqtd IN OUT number, pkamigo IN number) 
IS 
BEGIN 
 declare v_qtdeamigo number; 
 BEGIN 
   SELECT COUNT (u.nomeusu) as qtdeamigo 
   into v_qtdeamigo 
   FROM tbusuario u 
   WHERE u.pkcodusu = pkamigo 
    
   GROUP BY u.pkcodusu 
   HAVING count(u.nomeusu) >= pqtd; 
 END; 
END;

turned, but how do I display to see if it's right?

[spernega]

Renata,

Select all

CREATE OR REPLACE PROCEDURE busca_amigo(pqtd IN OUT number, pkamigo IN number) 
IS 
  v_qtdeamigo number; 
BEGIN 
  SELECT COUNT (u.nomeusu) into v_qtdeamigo 
  into v_qtdeamigo 
  FROM tbusuario u 
  WHERE u.pkcodusu = pkamigo 
  GROUP BY u.pkcodusu 
  HAVING count(u.nomeusu) >= pqtd; 
 -- 
 pqtd := v_qtdeamigo; 
 -- 
END;

In this way the procedure will return the amount obtained in the PQTD input variable

but in one case of these Maybe you'd better use a function instead of procedure.
depends a bit of what as you are setting up this screen.

[Renata2016]

It has to be procedure. In your example this error appears "Errors: Check Compiler Log"
that other that I said before compiled, but I could not make the result appear. Ohhhh sad. The trigger I managed to make good now procedure in Oracle n enters my head.

[spernega]

Renata,

In the select that I passed you were two inlet, it was badly ...

Select all

CREATE OR REPLACE  
PROCEDURE busca_amigo(pqtd IN OUT number,  
                      pkamigo IN number) IS 
 -- 
 v_qtdeamigo number; 
 -- 
BEGIN 
 -- 
 SELECT COUNT (u.nomeusu) into v_qtdeamigo 
 FROM tbusuario u 
 WHERE u.pkcodusu = pkamigo 
 GROUP BY u.pkcodusu 
 HAVING count(u.nomeusu) >= pqtd; 
 -- 
 pqtd := v_qtdeamigo; 
 -- 
END; 
/

]] Try this way

[Renata2016]

I tried, ran but I can not make return

[spernega]

Renata,
How are you running procedure?