(Resolvido) Ajuda em Stored Procedure

Pompeu · Junho 24, 2009

alguém poderia me ajudar a fazer uma stored procedure baseada nessa rotina feita em Delphi, já tentei de tudo quanto é forma mas não entendo muito de cursores ?

DTM.TBSocios.Active := False;
DTM.TBSocios.SQL.Clear;
DTM.TBSocios.SQL.Add ('Select * from socios');
DTM.TBSocios.SQL.Add ('order by nome');
DTM.TBSocios.Active := True;

DTM.TBSocios.First;
while not DTM.TBSocios.Eof do
begin
DTMRelatorios.TBRelatorios.Close;
DTMRelatorios.TBRelatorios.SQL.Clear;
DTMRelatorios.TBRelatorios.SQL.Add('insert into cria_relatorio (id, tiposocio, ntitulo, codigo, nome) Values (' + chr(39)
+ DTM.TBSocios.FieldByName('id').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBSocios.FieldByName('tiposocio').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBSocios.FieldByName('ntitulo').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBSocios.FieldByName('codigo').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBSocios.FieldByName('nome').AsString + chr(39) + ')');
DTMRelatorios.TBRelatorios.ExecSQL;

DTMRelatorios.TBRelatorios.Close;
DTMRelatorios.TBRelatorios.SQL.Clear;
DTMRelatorios.TBRelatorios.SQL.Add('commit');
DTMRelatorios.TBRelatorios.ExecSQL;

DTM.TBDependentes.Active := False;
DTM.TBDependentes.SQL.Clear;
DTM.TBDependentes.SQL.Add ('Select * from dependentes where idsocio = ' + chr(39) + DTM.TBSocios.FieldByName('ID').AsString + chr(39) + ' AND ntitulo = ' + chr(39) + DTM.TBSocios.FieldByName('ntitulo').AsString + chr(39));
DTM.TBDependentes.SQL.Add ('order by codigo');
DTM.TBDependentes.Active := True;

DTM.TBDependentes.First;
while not DTM.TBDependentes.Eof do
begin
DTMRelatorios.TBRelatorios.Close;
DTMRelatorios.TBRelatorios.SQL.Clear;
DTMRelatorios.TBRelatorios.SQL.Add('insert into cria_relatorio (idsocio' +
', codigo_d' +
', nome_d' +
', dtnasc' +
', grau_parentesco' +
', status' +
') Values (' + chr(39)
+ DTM.TBDependentes.FieldByName('idsocio').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBDependentes.FieldByName('codigo').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBDependentes.FieldByName('nome').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBDependentes.FieldByName('dtnasc').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBDependentes.FieldByName('grau_parentesco').AsString + chr(39) + ', ' + chr(39)
+ DTM.TBDependentes.FieldByName('status').AsString + chr(39) + ')');
DTMRelatorios.TBRelatorios.ExecSQL;

DTMRelatorios.TBRelatorios.Close;
DTMRelatorios.TBRelatorios.SQL.Clear;
DTMRelatorios.TBRelatorios.SQL.Add('commit');
DTMRelatorios.TBRelatorios.ExecSQL;

DTM.TBDependentes.Next;
end;
DTM.TBSocios.Next;
end;

Obriado e se possível mostrar um exemplo ... Obrigado mesmo

Denis Courcy · Junho 24, 2009

Oi, 'Pompeu'

Para facilitar, poderia, por favor, publicar as estruturas das tabelas: socios, cria_relatorio e dependentes?

Obrigado.

Pompeu · Junho 24, 2009

segue estrutura das tabelas:

Socios:

--

-- Estrutura da tabela `socios`

--

CREATE TABLE IF NOT EXISTS `socios` (
  `id` int(11) NOT NULL,
  `idsociop` int(11) default NULL,
  `tiposocio` int(11) NOT NULL,
  `desctiposocio` varchar(22) default NULL,
  `ntitulo` varchar(3) NOT NULL,
  `codigo` int(11) NOT NULL,
  `nome` varchar(50) NOT NULL,
  `endereco` varchar(50) default NULL,
  `numero` varchar(4) default NULL,
  `cidade` varchar(25) default NULL,
  `foneres` varchar(14) default NULL,
  `celular` varchar(14) default NULL,
  `bairro` varchar(25) default NULL,
  `cep` varchar(9) default NULL,
  `dtnasc` varchar(10) default NULL,
  `profissao` varchar(20) default NULL,
  `cpf` varchar(14) default NULL,
  `rg` varchar(12) default NULL,
  `estadocivil` varchar(15) default NULL,
  `email` varchar(50) default NULL,
  `dtinclusao` varchar(10) default NULL,
  `dtbaixa` varchar(10) default NULL,
  `motivo` varchar(30) default NULL,
  `tiposociob` int(11) default NULL,
  `codigosociob` int(11) default NULL,
  `nomesociob` varchar(50) default NULL,
  `status` varchar(12) default 'A',
  `codigodep` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Dependentes: -- -- Estrutura da tabela `dependentes` --

CREATE TABLE IF NOT EXISTS `dependentes` (
  `id` int(11) NOT NULL default '0',
  `idsocio` int(11) default NULL,
  `tiposocio` int(11) NOT NULL,
  `ntitulo` varchar(3) default NULL,
  `codigo` int(11) default NULL,
  `nome` varchar(50) default NULL,
  `dtnasc` varchar(10) default NULL,
  `grau_parentesco` varchar(15) default NULL,
  `status` varchar(7) default NULL,
  `dtbaixa` varchar(10) default NULL,
  `motivo` varchar(30) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Cria Relatorio: -- -- Estrutura da tabela `cria_relatorio` --

CREATE TABLE IF NOT EXISTS `cria_relatorio` (
  `id` int(11) default NULL,
  `tiposocio` int(11) default NULL,
  `ntitulo` varchar(3) default NULL,
  `codigo` int(11) default NULL,
  `nome` varchar(50) default NULL,
  `idsocio` int(11) default NULL,
  `codigo_d` int(11) default NULL,
  `nome_d` varchar(50) default NULL,
  `dtnasc` varchar(10) default NULL,
  `grau_parentesco` varchar(15) default NULL,
  `status` varchar(7) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Obrigado Denis

Denis Courcy · Junho 24, 2009

'Pompeu',

Tecnicamente você não precisa de criar a tabela cria_relatorio nem criar a storage procedure, pois a query abaixo já resolverá seu problema:

SELECT s.id, s.tiposocio, s.ntitulo, s.codigo, s.nome, d.codigo, d.nome, d.dtnasc, d.grau_parentesco, d.status
FROM socios s
LEFT JOIN dependentes d ON where d.idsocio = s.ID AND d.ntitulo = s.ntitulo
ORDER BY s.nome

Mas, se você quer armazenar os valores na tabela então use esta query

INSERT INTO cria_relatorio (id, tiposocio, ntitulo, codigo, nome, codigo_d, nome_d, dtnasc, grau_parentesco, status)
SELECT s.id, s.tiposocio, s.ntitulo, s.codigo, s.nome, d.codigo, d.nome, d.dtnasc, d.grau_parentesco, d.status
FROM socios s
LEFT JOIN dependentes d ON where d.idsocio = s.ID AND d.ntitulo = s.ntitulo
ORDER BY s.nome

Se ainda assim, necessitar da storage procedure tal como foi gerada no código DELPHI, então aguarde um pouco para que eu possa passar para o código para SQL.

Pompeu · Junho 24, 2009

na verdade eu precisaria de uma resposta assim para gerar o relatorio:

SOCIO JOAO

dep maria

dep jose

SOCIO PEDRO

dep marcos

dep cicero

Etc, e caso o socio não tenha dependentes aparece somente o nome dele

é isso ... e muitissimo obrigado pela atenção

Denis Courcy · Junho 24, 2009

'Pompeu' ,

Seguindo o código apresentado, temos duas procedures. Uma para o sócio e outra para o dependente que será chamada pela procedure do sócio.

Você deverá chamar a Storage Procedure assim: CALL cria_relatorio;

Abaixo o código da procedure do sócio:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `pompeu`.`cria_relatorio`$$

CREATE PROCEDURE `pompeu`.`cria_relatorio` ()
BEGIN
   DECLARE r_id              INTEGER;
   DECLARE r_tiposocio       INTEGER;
   DECLARE r_ntitulo         CHAR(3);
   DECLARE r_codigo          INTEGER;
   DECLARE r_nome            CHAR(50);
   
   DECLARE socio_eof INTEGER DEFAULT 0;
   

   DECLARE cur_socio CURSOR FOR
      SELECT id, tiposocio, ntitulo, codigo, nome
      FROM socios 
      ORDER BY nome;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET socio_eof = 1; 
   
   OPEN cur_socio;
   FETCH cur_socio INTO r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome;
   WHILE NOT socio_eof DO
      INSERT INTO cria_relatorio (id, tiposocio, ntitulo, codigo, nome) 
      VALUES (r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome);

      CALL Cria_relatorio_dep(r_id, r_ntitulo);
      
      FETCH cur_socio INTO r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome;
   END WHILE;
   CLOSE cur_socio;
END$$

DELIMITER;$$

O código para o dependente é:

DELIMITER $$;

DROP PROCEDURE IF EXISTS `pompeu`.`Cria_relatorio_dep`$$

CREATE PROCEDURE `pompeu`.`Cria_relatorio_dep` (p_idsocio INTEGER, ntitulo CHAR(3))
BEGIN
   DECLARE r_idsocio         INTEGER;
   DECLARE r_codigo_d        INTEGER;
   DECLARE r_nome_d          CHAR(50);
   DECLARE r_dtnasc          CHAR(10);
   DECLARE r_grau_parentesco CHAR(15);
   DECLARE r_status          CHAR(7);

   DECLARE depen_eof INTEGER DEFAULT 0;

   DECLARE cur_dependente CURSOR FOR
      SELECT idsocio, codigo, nome, dtnasc, grau_parentesco, `status` 
      FROM dependentes 
      WHERE idsocio = p_idsocio AND ntitulo = p_ntitulo
      ORDER BY codigo;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET depen_eof = 1;    

   OPEN cur_dependente;
   FETCH cur_dependente INTO r_idsocio, r_codigo_d, r_nome_d, r_dtnasc, r_grau_parentesco, r_status;
   WHILE NOT depen_eof DO
      INSERT INTO cria_relatorio (idsocio, codigo_d, nome_d, dtnasc, grau_parentesco, `status`)
      VALUES (r_idsocio, r_codigo_d, r_nome_d, r_dtnasc, r_grau_parentesco, r_status);
      FETCH cur_dependente INTO r_idsocio, r_codigo_d, r_nome_d, r_dtnasc, r_grau_parentesco, r_status;
   END WHILE;
   CLOSE cur_dependente;
END$$

DELIMITER;$$

Este código pode ser otimizado.

Pompeu · Junho 24, 2009

estou testando aqui ok ... daqui a pouco retorno dizendo se deu certo mas a princípio sim ^^

Pompeu · Junho 24, 2009

Denis ... deu certo ... não sei nem como agradecer ... essa rotina vai me ajudar em muitas outras q preciso ... só tive q fazer algumas alterações mas funcionou exatamente como eu precisava

Muitissimo obrigado e se precisar de algo estamos aí ^^

segue código corrigido pra você ver como eu precisava:

SOCIO

DELIMITER $$

DROP PROCEDURE IF EXISTS `clubedec_ccsa`.`sp_cria_relatorio`$$

CREATE PROCEDURE `clubedec_ccsa`.`sp_cria_relatorio` ()

BEGIN

DECLARE r_id INTEGER;

DECLARE r_tiposocio INTEGER;

DECLARE r_ntitulo CHAR(3);

DECLARE r_codigo INTEGER;

DECLARE r_nome CHAR(50);

DECLARE socio_eof INTEGER DEFAULT 0;

DECLARE cur_socio CURSOR FOR

SELECT id, tiposocio, ntitulo, codigo, nome

FROM socios

ORDER BY nome;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET socio_eof = 1;

DELETE FROM cria_relatorio;

OPEN cur_socio;

FETCH cur_socio INTO r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome;

WHILE NOT socio_eof DO

INSERT INTO cria_relatorio (id, tiposocio, ntitulo, codigo, nome)

VALUES (r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome);

CALL sp_cria_relatorio_dep(r_id, r_ntitulo);

FETCH cur_socio INTO r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome;

END WHILE;

CLOSE cur_socio;

END$$

DELIMITER;$$

DEPENDENTE

DELIMITER $$

DROP PROCEDURE IF EXISTS `clubedec_ccsa`.`sp_cria_relatorio_dep` $$

CREATE PROCEDURE`clubedec_ccsa`.`sp_cria_relatorio_dep`(p_idsocio INTEGER, p_ntitulo CHAR(3))

BEGIN

DECLARE r_idsocio INTEGER;

DECLARE r_codigo_d INTEGER;

DECLARE r_ntitulo_d CHAR(50);

DECLARE r_nome_d CHAR(50);

DECLARE r_dtnasc CHAR(10);

DECLARE r_grau_parentesco CHAR(15);

DECLARE r_status CHAR(7);

DECLARE depen_eof INTEGER DEFAULT 0;

DECLARE cur_dependente CURSOR FOR

SELECT idsocio, ntitulo, codigo, nome, dtnasc, grau_parentesco, `status`

FROM dependentes

WHERE idsocio = p_idsocio

ORDER BY codigo;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET depen_eof = 1;

OPEN cur_dependente;

FETCH cur_dependente INTO r_idsocio, r_ntitulo_d, r_codigo_d, r_nome_d, r_dtnasc, r_grau_parentesco, r_status;

WHILE NOT depen_eof DO

INSERT INTO cria_relatorio (idsocio, ntitulo_d, codigo_d, nome_d, dtnasc, grau_parentesco, `status`)

VALUES (r_idsocio, r_ntitulo_d, r_codigo_d, r_nome_d, r_dtnasc, r_grau_parentesco, r_status);

FETCH cur_dependente INTO r_idsocio, r_ntitulo_d, r_codigo_d, r_nome_d, r_dtnasc, r_grau_parentesco, r_status;

END WHILE;

CLOSE cur_dependente;

END $$

DELIMITER ;

Um grande abraço amigo

Denis Courcy · Junho 24, 2009

'Pompeu'

Já que deu certo, veja a otimização:

DELIMITER $$

DROP PROCEDURE IF EXISTS `clubedec_ccsa`.`sp_cria_relatorio`$$
CREATE PROCEDURE `clubedec_ccsa`.`sp_cria_relatorio` ()
BEGIN
   DECLARE r_id              INTEGER;
   DECLARE r_tiposocio       INTEGER;
   DECLARE r_ntitulo         CHAR(3);
   DECLARE r_codigo          INTEGER;
   DECLARE r_nome            CHAR(50);
   
   DECLARE socio_eof INTEGER DEFAULT 0;


   DECLARE cur_socio CURSOR FOR
      SELECT id, tiposocio, ntitulo, codigo, nome
      FROM socios
      ORDER BY nome;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET socio_eof = 1;

   DELETE FROM cria_relatorio;

   OPEN cur_socio;
   FETCH cur_socio INTO r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome;
   WHILE NOT socio_eof DO
      INSERT INTO cria_relatorio (id, tiposocio, ntitulo, codigo, nome) 
      VALUES (r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome);

      INSERT INTO cria_relatorio (idsocio, ntitulo_d, codigo_d, nome_d, dtnasc, grau_parentesco, `status`)
      SELECT idsocio, ntitulo, codigo, nome, dtnasc, grau_parentesco, `status`
      FROM dependentes
      WHERE idsocio = r_id
      ORDER BY codigo;

      FETCH cur_socio INTO r_id, r_tiposocio, r_ntitulo, r_codigo, r_nome;
   END WHILE;
   CLOSE cur_socio;
END$$

Pode dar

DROP PROCEDURE IF EXISTS `clubedec_ccsa`.`sp_cria_relatorio_dep`;

nesta procedure. Não vai mais precisar dela.

Pompeu · Junho 24, 2009

Vou deixar da primeira forma que deu certo Denis ... sabe porque ... vou utilizar a procedure de dependentes para gerar outros relatorios q na verdade vai dar uns 10 mais ou menos e como os dependentes são sempre juntos com outroas informações, vou optar em deixar uma procedure de dependentes separada q assim eu posso chama-la de qualquer outra procedure q eu criar (confesso q eu nem sabia q dava pra chamar uma procedure dentro de outra rs), mas mesmo assim salvei a procedure otimizada caso eu precise, está salva e servirá de consulta para tirar dúvidas também.

Cara ... se não tem noção o quanto me ajudou, como estou começando com stored procedures vai servir de referência para a criação de outras ... essas q você criou pra mim já resolveu muitas dúvidas q eu tinha

Vlw mesmo Denis

Abraço

Entrar

(Resolvido) Ajuda em Stored Procedure

Pergunta

Pompeu

Link para o comentário

Compartilhar em outros sites

9 respostass a esta questão

Posts Recomendados

Denis Courcy

Link para o comentário

Compartilhar em outros sites

Pompeu

Link para o comentário

Compartilhar em outros sites

Denis Courcy

Link para o comentário

Compartilhar em outros sites

Pompeu

Link para o comentário

Compartilhar em outros sites

Denis Courcy

Link para o comentário

Compartilhar em outros sites

Pompeu

Link para o comentário

Compartilhar em outros sites

Pompeu

Link para o comentário

Compartilhar em outros sites

Denis Courcy

Link para o comentário

Compartilhar em outros sites

Pompeu

Link para o comentário

Compartilhar em outros sites

Estatísticas dos Fóruns

Navegação

Atividades