PROCEDURE

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Renata2016
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Seg, 29 Ago 2016 9:28 am

desenvolva um procedimento que liste em tela o nome e quantidade de amigos de
todos os usuário que possuírem mais do que X amigos (informado como
parâmetro no procedimento). A cada impressão de usuário o
procedimento deverá listar o nome dos amigos deste usuário

Selecionar tudo

CREATE TABLE tbusuario (
pkcodusu integer not null PRIMARY KEY,
nomeusu varchar2(30),
datanasc date,
sexo varchar2(1),
quantamigosativos integer,
quantmsgnaolidas integer,
nacionalidade varchar2(30)
);

CREATE TABLE tbamigos (
fkcodusua integer not null ,
fkcodusub integer not null ,
bloqueado integer,
PRIMARY KEY(fkcodusua,fkcodusub),
FOREIGN KEY(fkcodusua) REFERENCES tbusuario (pkcodusu),
FOREIGN KEY(fkcodusub) REFERENCES tbusuario (pkcodusu)
);

CREATE TABLE tbmensagem (
pkcodmensagem integer not null  PRIMARY KEY,
conteudo varchar2(150),
fkcodusuaorigem integer not null ,
fkcodusudestino integer not null ,
statuslida integer,
FOREIGN KEY(fkcodusuaorigem) REFERENCES tbusuario (pkcodusu),
FOREIGN KEY(fkcodusudestino) REFERENCES tbusuario (pkcodusu)
);

insert into tbusuario(pkcodusu,nomeusu,datanasc,sexo,quantamigosativos,quantmsgnaolidas,nacionalidade) values(1,'xuxa','02/03/1977','F',0,0,'Brasileiro');
insert into tbusuario(pkcodusu,nomeusu,datanasc,sexo,quantamigosativos,quantmsgnaolidas,nacionalidade) values(2,'Bozo','12/04/1985','M',0,0,'Brasileiro');
insert into tbusuario(pkcodusu,nomeusu,datanasc,sexo,quantamigosativos,quantmsgnaolidas,nacionalidade) values(3,'Gugu','07/05/1987','M',0,0,'Brasileiro');
insert into tbusuario(pkcodusu,nomeusu,datanasc,sexo,quantamigosativos,quantmsgnaolidas,nacionalidade) values(4,'Eliana','07/05/1991','F',0,0,'Brasileiro');
insert into tbusuario(pkcodusu,nomeusu,datanasc,sexo,quantamigosativos,quantmsgnaolidas,nacionalidade) values(5,'Patati','09/07/1991','M',0,0,'Brasileiro');
insert into tbusuario(pkcodusu,nomeusu,datanasc,sexo,quantamigosativos,quantmsgnaolidas,nacionalidade) values(6,'Patata','11/01/1990','M',0,0,'Brasileiro');

insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(1,3,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(1,4,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(1,6,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(6,5,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(6,2,1);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(6,3,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(4,2,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(4,3,0);
insert into tbamigos(fkcodusua,fkcodusub,bloqueado) values(5,1,0);

insert into tbmensagem(pkcodmensagem,conteudo,fkcodusuaorigem,fkcodusudestino,statuslida) values(1,'Ola amigo',1,5,0);
insert into tbmensagem(pkcodmensagem,conteudo,fkcodusuaorigem,fkcodusudestino,statuslida) values(2,'quer TC?',4,2,1);
insert into tbmensagem(pkcodmensagem,conteudo,fkcodusuaorigem,fkcodusudestino,statuslida) values(3,'Hoje não sera possivel',1,5,1);
insert into tbmensagem(pkcodmensagem,conteudo,fkcodusuaorigem,fkcodusudestino,statuslida) values(4,'Que dia complicado',5,1,1);
insert into tbmensagem(pkcodmensagem,conteudo,fkcodusuaorigem,fkcodusudestino,statuslida) values(5,'Vamos ver um filme?',4,2,0);

e esse é o codigo, mas da dando erro. AJUDA.

Selecionar tudo

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
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Bom dia Renata,

Não testei o seu código, mas está faltando um into no select da procedure.

Selecionar tudo

declare  v_qtdeamigo number;
no select

Selecionar tudo

SELECT COUNT(u.nomeusu) into v_qtdeamigo
Nunca vai entrar no SQL%NOTFOUND, como é um COUNT, sempre vai voltar uma valor, mesmo que zero.

Teste

Selecionar tudo

IF v_qtdeamigo = 0 then
Renata2016
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Seg, 29 Ago 2016 9:28 am

Fiz dessa forma:

C

Selecionar tudo

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;
Rodou, mas como faço exibir pra ver se está certo?
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Renata,

Selecionar tudo

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;
Dessa forma a procedure vai retornar a quantidade obtida na variável de entrada pqtd

Mas num caso destes talvez seja melhor você usar uma FUNCTION ao invés de PROCEDURE.
Depende um pouco do que como você está montando esta tela.
Renata2016
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Seg, 29 Ago 2016 9:28 am

Tem que ser procedure. No seu exemplo aparece esse erro "Errors: check compiler log"
Aquele outro que eu falei antes compilou, mas não consegui fazer aparecer o resultado. ta triste ohhhh. A trigger eu consegui fazer de boa agora procedure no oracle n me entra na cabeça.
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Renata,

No SELECT que eu te passei ficaram dois INTO, foi mal...

Selecionar tudo

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;
/

Tenta assim
Renata2016
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 7
Registrado em: Seg, 29 Ago 2016 9:28 am

Tentei, rodou mas n consigo fazer retornar
spernega
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 232
Registrado em: Ter, 19 Jun 2007 2:12 pm
Localização: São Paulo - SP

Renata,
Como você está executando a PROCEDURE?
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante