How to create a Stored Procedure for two tables

Question

How to create a Stored Procedure for two tables

Navigation

#1 by (2 votes)
#2 by (2 votes)

2

I'm trying to do this Stored Procedure, but it's giving error.

Can I make an insert with proceed?

DELIMITER $$

DROP PROCEDURE IF EXISTS 'centralrit'.'sp_InterdDetalhe_Encrypt' $$
CREATE PROCEDURE 'centralrit'.'sp_InterdDetalhe_Encrypt'
  (
  in p_id int(11),
  in p_usuario_id int(11),
  in p_nome text,
  in p_cpf text,
  in p_nome_pai text,
  in p_nome_mae text,
  in p_data_cadastro datetime,
  in p_serventia_id int,
  in p_natureza text,
  in p_livro text,
  in p_folha text,
  in p_termo text,
  in p_ativo TINYINT(1),
  in p_lixeira TINYINT(1)
 )
BEGIN
  insert into interditado
  (usuario_id, nome, cpf, nome_pai, nome_mae, data_cadastro)
  values
  (
    p_usuario_id,
    aes_encrypt(p_nome, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_cpf, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_nome_pai, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_nome_mae, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    p_data_cadastro
  );

insert into interditado_detalhe (interditado_id, usuario_id, serventia_id, data_cadastro as cadastro_detalhe,
natureza, livro, folha, termo, ativo, lixeira) values
  (
    p_id,
    p_usuario_id,
    p_serventia_id,
    p_data_cadastro,
    aes_encrypt(p_natureza, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_livro, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_folha, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_termo, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    p_ativo,
    p_lixeira
  );

END $$

DELIMITER ;

Error: Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asdf, nature, book, leaf, term, active, trash) values ('at line 33

mysql

asked by anonymous 22.07.2016 / 02:56

2 answers

2

The second insert has a syntax error. Remove the as cadastro_detalhe that will work.

insert into interditado_detalhe 
(interditado_id, usuario_id, serventia_id, data_cadastro /*as cadastro_detalhe*/,
natureza, livro, folha, termo, ativo, lixeira) 
values (...)

22.07.2016 / 11:16

Why, when I change the size of the window, do the drawn components disappear? Laravel 5 QueryBuilder, results in simplified collection

score 2 · Accepted Answer

Yes, you can do any CRUD with a Stored Procedure for two tables or even more or even use CRUD (create, read, update and delite) in a single Stored Procedure.

Your error is because you are trying to rename your column in an INSERT, this can only be done in a SELECT, in the cases of INSERT, UPDATE, or DELETE (where *), you do not have to rename your columns.

In short, you do not need to rename your columns in these cases.

Change your Procedure to.

 DELIMITER $$

    DROP PROCEDURE IF EXISTS 'centralrit'.'sp_InterdDetalhe_Encrypt' $$
    CREATE PROCEDURE 'centralrit'.'sp_InterdDetalhe_Encrypt'
      (
          in p_id int(11),
          in p_usuario_id int(11),
          in p_nome text,
          in p_cpf text,
          in p_nome_pai text,
          in p_nome_mae text,
          in p_data_cadastro datetime,
          in p_serventia_id int,
          in p_natureza text,
          in p_livro text,
          in p_folha text,
          in p_termo text,
          in p_ativo TINYINT(1),
          in p_lixeira TINYINT(1)
     )

    BEGIN
        insert into interditado (usuario_id, nome, cpf, nome_pai, nome_mae, data_cadastro)
        values
        (
            p_usuario_id,
            aes_encrypt(p_nome, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_cpf, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_nome_pai, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_nome_mae, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            p_data_cadastro
        );

        insert into interditado_detalhe (interditado_id, usuario_id, serventia_id, 
        data_cadastro, natureza, livro, folha, termo, ativo, lixeira) values
        (
            p_id,
            p_usuario_id,
            p_serventia_id,
            p_data_cadastro,
            aes_encrypt(p_natureza, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_livro, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_folha, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_termo, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            p_ativo,
            p_lixeira
        );

    END $$

DELIMITER ;