Transações com procedures no MySQL e Herança

Leia isto "em cerca de 20 minutos".

Introdução

Chega de ficar apenas aprendendo ‘create table’ no banco de dados MySQL, aprenda como se criar Procedures, View e Trigger e comunicação entre as mesmas nessa postagem.

Quando se cria uma aplicação que se necessita de uma comunicação com bando de dados, seja ela de qual linguagem de programação for, o acesso entre “Aplicação Banco” sempre é um dos principais fatores para uma boa otimização, ganha tempo e velocidade de processos. A maioria da estrutura e performasse de uma aplicação, está na própria aplicação, mas isso não quer dizer que você não possa melhorar o desempenho realizando boas praticas de código no seu banco de dados. Você consegue fazer várias tarefas que poderiam ser feitas na aplicação, apenas com códigos PL/SQL.

Neste post, será passado como se deve realizar transações no seu banco de dados utilizando as famosas PROCEDURES do MySQL, que é um dos mais conceituados banco de dados.

Para começar, utilize o script SQL abaixo com a opção de criar um database e uma tabela de Clientes no MySQL.

Criando: Database e Tables

 1 DELIMITER $$
 2 
 3 DROP DATABASE IF EXISTS `TRANSACTIONS` $$
 4 
 5 CREATE DATABASE `TRANSACTIONS` $$
 6 
 7 USE `TRANSACTIONS` $$
 8 
 9 DROP TABLE IF EXISTS `Clientes`$$
10 
11 CREATE TABLE `Clientes` (
12   `codCliente` int(11) NOT NULL AUTO_INCREMENT,
13   `Nome` varchar(100) NOT NULL,
14   `Endereco` varchar(100) NOT NULL,
15   PRIMARY KEY (`codCliente`)
16 )$$
17 
18 DROP TABLE IF EXISTS `Cliente_Fisico` $$
19 
20 CREATE TABLE `Cliente_Fisico` (
21   `codFisico` int(11) NOT NULL AUTO_INCREMENT,
22   `idClienteF` int(11) NOT NULL,
23   `CPF` varchar(20) NOT NULL,
24   PRIMARY KEY (`codFisico`),
25   KEY `idClienteF` (`idClienteF`),
26   FOREIGN KEY (`idClienteF`) REFERENCES `Clientes` (`codCliente`)
27 )$$
28 
29 DROP TABLE IF EXISTS `Cliente_Juridico` $$
30 
31 CREATE TABLE `Cliente_Juridico` (
32   `codJuridico` int(11) NOT NULL AUTO_INCREMENT,
33   `idClienteJ` int(11) NOT NULL,
34   `CNPJ` varchar(20) NOT NULL,
35   PRIMARY KEY (`codJuridico`),
36   KEY `idClienteJ` (`idClienteJ`),
37  FOREIGN KEY (`idClienteJ`) REFERENCES `Clientes` (`codCliente`)
38 ) $$

Como podem perceber a tabela de Clientes é uma tabela pai das tabelas Cliente_Fisico e Cliente_Juridico, ou seja, uma Herança.

Com a herança de tabelas criada, vamos criar uma VIEW para realizar o READ do CRUD, pois temos uma herança de tabelas e precisa-se fazer um JOIN dos registros de Cliente com Cliente Físico e Cliente Jurídico para listar todos os registros de nosso database.

Criando: View de Clientes

1 DELIMITER $$
2 DROP VIEW IF EXISTS `View_Cliente_Fisico_Juridico` $$
3 CREATE VIEW `View_Cliente_Fisico_Juridico` AS
4 SELECT * FROM `Clientes` `C`
5 LEFT JOIN `Cliente_Fisico` `F` ON (`C`.`codCliente` = `F`.`idClienteF`)
6 LEFT JOIN `Cliente_Juridico` `J` ON (`C`.`codCliente` = `J`.`idClienteJ`)
7 $$

Também precisa-se criar um TRIGGER (gatilho) com a função de deletar a herança de um determinado registro. Por exemplo: A tabela Cliente contem os registros padrão de um cliente, mas que pertence aos clientes físicos e clientes jurídicos também, então para realizar uma exclusão de um cliente, temos que remover os registros da tabela herdada (Cliente Físico e Jurídico) e posteriormente da tabela Clientes, dependendo de qual registro irá ser excluído.

Criando: Trigger

 1 DELIMITER $$
 2 DROP TRIGGER IF EXISTS `TRG_HERANCA_CLIENTE` $$
 3 CREATE
 4 TRIGGER `Trg_Delete_Cliente_Fisico_Juridico`
 5 BEFORE DELETE ON `Clientes`
 6 FOR EACH ROW
 7 BEGIN
 8 DELETE FROM Cliente_Fisico WHERE idClienteF = OLD.codCliente;
 9 DELETE FROM Cliente_Juridico WHERE idClienteJ = OLD.codCliente;
10 END$$

Como podem ver, nessa TRIGGER, irá remover os dos da tabela Clientes, Cliente Físico e Cliente Jurídico atraves das chaves estrangeiras. Exclui os “filhos” para depois excluir o “pai” de nossa herança.

Com a toda estrutura de TABLES, VIEW e TRIGGER criadas, vem a parte mais esperada (tan tan tan taaann), vamos criar a nossa “linda e bonita” procedure de transações, que será responsável por fazer todas as criações, leituras, alterações e remoções de registros, ou seja, uma procedure com capacidade de relizar o CRUD (Create, Read, Update e Delete) no banco de dados.

Criando: Procedure

  1 DROP PROCEDURE IF EXISTS `TRANSACTION_CLIENTS` $$
  2 
  3 CREATE PROCEDURE `TRANSACTION_CLIENTS`(
  4 
  5 `@OPERACAO` VARCHAR(20),
  6 `@TABELA` VARCHAR(50),
  7 
  8 IN `@codCliente` INT ,
  9 IN `@Nome` VARCHAR(100),
 10 IN `@Endereco` VARCHAR(100),
 11 
 12 IN `@codFisico` INT,
 13 IN `@idCliF` INT,
 14 IN `@CPF` VARCHAR(20),
 15 
 16 IN `@codJuridico` INT,
 17 IN `@idCliJ` INT,
 18 IN `@CNPJ` VARCHAR(20)
 19 
 20 
 21 )
 22  BEGIN
 23 
 24 -- OPERAÇÕES DECLARADAS
 25 DECLARE `OP1` VARCHAR(50) DEFAULT 'CREATE';
 26 DECLARE `OP2` VARCHAR(50) DEFAULT 'DELETE';
 27 DECLARE `OP3` VARCHAR(50) DEFAULT 'UPDATE';
 28 DECLARE `OP4` VARCHAR(50) DEFAULT 'READ';
 29 -- TABELAS DECLARADAS A SERÃO OPERADAS
 30 DECLARE `TAB1` VARCHAR(50) DEFAULT 'CLIENTE_FISICO';
 31 DECLARE `TAB2` VARCHAR(50) DEFAULT 'CLIENTE_JURIDICO';
 32 DECLARE `TAB3` VARCHAR(50) DEFAULT 'CLIENTES';
 33 
 34 
 35 
 36 DECLARE exception SMALLINT DEFAULT 0;
 37 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET exception = 1;
 38 
 39 -- INSERT DA TABELA FISICO
 40 IF (`@TABELA` = `TAB1`  AND `@OPERACAO` = `OP1` AND `@codCliente` IS NULL) THEN
 41    BEGIN -- B01
 42       IF ((`@Nome` <> "") AND (`@Endereco` <> "") AND(`@CPF` <> ""))THEN
 43           BEGIN
 44              START TRANSACTION;
 45              INSERT INTO Clientes VALUES(NULL,`@Nome`, `@Endereco`);
 46                 IF exception = 1 THEN
 47                    SELECT 'Erro ao inserir na tabela Cliente' AS Msg;
 48                    ROLLBACK;
 49                 ELSE
 50                    SELECT DISTINCT LAST_INSERT_ID() INTO @idClienteF FROM Clientes;
 51                 END IF;
 52                      IF exception = 1 THEN
 53                         SELECT 'Erro ao selecionar o ultimo ID inserido' AS Msg;
 54                         ROLLBACK;
 55                      ELSE
 56                         INSERT INTO Cliente_Fisico VALUES(NULL,@idClienteF,`@CPF`);
 57                         SELECT 'Cadastro efetuado com sucesso' AS Msg;
 58                         COMMIT;
 59                      END IF;
 60 
 61            END;
 62         ELSE
 63            SELECT 'Parametros necessários para realizar a operação' AS Msg;
 64 
 65  END IF;
 66    END; -- LIMITE FISICO
 67 ELSE
 68 -- INSERT DA TABELA JURIDICO
 69 IF (`@TABELA` = `TAB2`  AND `@OPERACAO` = `OP1` AND `@codCliente` IS NULL) THEN
 70    BEGIN -- B01
 71       IF ((`@Nome` <> "") AND (`@Endereco` <> "") AND(`@CNPJ` <> ""))THEN
 72           BEGIN
 73              START TRANSACTION;
 74              INSERT INTO Clientes VALUES(NULL,`@Nome`, `@Endereco`);
 75                 IF exception = 1 THEN
 76                    SELECT 'Erro ao inserir na tabela Cliente' AS Msg;
 77                    ROLLBACK;
 78                 ELSE
 79                    SELECT DISTINCT LAST_INSERT_ID() INTO @idClienteJ FROM Clientes;
 80                 END IF;
 81                      IF exception = 1 THEN
 82                         SELECT 'Erro ao selecionar o ultimo ID inserido' AS Msg;
 83                         ROLLBACK;
 84                      ELSE
 85                         INSERT INTO Cliente_Juridico VALUES(NULL,@idClienteJ,`@CNPJ`);
 86                         SELECT 'Cadastro efetuado com sucesso' AS Msg;
 87                         COMMIT;
 88                      END IF;
 89 
 90            END;
 91         ELSE
 92            SELECT 'Parametros necessários para realizar a operação' AS Msg;
 93 
 94  END IF;
 95    END;
 96 ELSE
 97 -- UPDATE TABELA DE FISICO
 98 IF (`@TABELA` = `TAB1`  AND `@OPERACAO` = `OP3`) THEN
 99    BEGIN
100 UPDATE Clientes SET `Nome` = `@Nome`, `Endereco` = `@Endereco` WHERE `codCliente` = `@codCliente`;
101         IF exception = 1 THEN
102             SELECT 'Erro ao atualizar tabela de Cliente' AS Msg;
103             ROLLBACK;
104         END IF;
105 UPDATE Cliente_Fisico SET `CPF` = `@CPF` WHERE `codFisico` = `@codFisico`;
106         IF exception = 1 THEN
107             SELECT 'Erro ao atualizar tabela de Cliente Físico' AS Msg;
108             ROLLBACK;
109         ELSE
110             SELECT 'Atualização realizada com sucesso' AS Msg;
111 
112        END IF;
113 END;
114 ELSE
115 -- UPDATE TABELA DE JURIDICO
116 IF (`@TABELA` = `TAB2`  AND `@OPERACAO` = `OP3`) THEN
117    BEGIN
118 UPDATE Clientes SET `Nome` = `@Nome`, `Endereco` = `@Endereco` WHERE `codCliente` = `@codCliente`;
119         IF exception = 1 THEN
120             SELECT 'Erro ao atualizar tabela de Cliente' AS Msg;
121             ROLLBACK;
122         END IF;
123 UPDATE Cliente_Juridico SET `CNPJ` = `@CNPJ` WHERE `codJuridico` = `@codJuridico`;
124         IF exception = 1 THEN
125             SELECT 'Erro ao atualizar tabela de Cliente Físico' AS Msg;
126             ROLLBACK;
127         ELSE
128             SELECT 'Atualização realizada com sucesso' AS Msg;
129 
130        END IF;
131 
132 END;
133 ELSE
134 -- DELETE TABELA DE FISICO
135 IF (`@TABELA` = `TAB1`  AND `@OPERACAO` = `OP2`) THEN
136    BEGIN
137         DELETE FROM Clientes WHERE `codCliente` = `@codCliente`;
138         IF exception = 1 THEN
139             SELECT 'Erro ao excluir o registro da tabela de Cliente' AS Msg;
140             ROLLBACK;
141         ELSE
142             SELECT 'Registro excluído da tabela de Cliente com sucesso' AS Msg;
143         END IF;
144 
145    END;
146 ELSE
147 -- DELETE TABELA DE JURIDICO
148 IF (`@TABELA` = `TAB2`  AND `@OPERACAO` = `OP2`) THEN
149    BEGIN
150         DELETE FROM Clientes WHERE `codCliente` = `@codCliente`;
151         IF exception = 1 THEN
152             SELECT 'Erro ao excluir o registro da tabela de Cliente' AS Msg;
153             ROLLBACK;
154         ELSE
155             SELECT 'Registro excluído da tabela de Cliente com sucesso' AS Msg;
156         END IF;
157    END;
158    ELSE
159    -- SELECIONAR TODOS CLIENTES
160 IF (`@TABELA` = `TAB3`  AND `@OPERACAO` = `OP4`) THEN
161    BEGIN
162         SELECT * FROM TRANSACTIONS.View_Cliente_Fisico_Juridico;
163    END;
164 ELSE
165 SELECT 'Parametros necessários para realizar a operação' AS Msg;
166 
167 END IF;
168 
169 END IF;
170 
171 END IF;
172 
173 END IF;
174 
175 END IF;
176 
177 END IF;
178 
179 
180 END IF;
181 
182 END$$

Na parte de criação (Create) de registros através dessa procedure, foi utilizado a função LAST_INSERT_ID() do MySQL, como temos uma herança de tabelas, essa função nos da uma opção de resgatar o ultimo registro inserido na tabela Clientes, pegando seu ID (Primary Key) e incluindo na tabela Cliente Físico ou Cliente Jurídico no campo de Foreign Key, dependendo de qual cliente você vai inserir, claro. Também pode-se perceber, foi declarado uma variável “exception” , com um valor default igual a 0 (zero), e atribuindo um SQLEXCEPTION com o valor de 1(um) caso de erro na transação, uma mensagem de exceção será disparada. O restante da procedure é basicamente implicado com lógicas de IF e ELSE.

NOTA: Para relizar as transações com o procedure, utiliza-se o CALL no MySQL. Lembrando que todos os parâmetros que estão presente no procedure, devem ser especificados na execução da procedure com o CALL, mesmo esses parâmetros não tendo uma implicância significativa, eles devem ser carregados com **NULL **.

Um exemplo:

 1 -- Criando um Cliente Físico
 2 CALL TRANSACTION_CLIENT ('CREATE','CLIENTE_FISICO',null,'William C. Canin','Rua XYZ',null,null,'01-234-567-89',null,null,null);
 3 -- Criando um Cliente Jurídico
 4 CALL TRANSACTION_CLIENT ('CREATE','CLIENTE_JURIDICO',null,'William C. Canin','Rua YXZ',null,null,null,null,null,'88-124-3697/15');
 5 -- Alterando um Cliente Físico
 6 CALL TRANSACTION_CLIENT ('UPDATE','CLIENTE_FISICO',null,'William C. Canin','Rua ABC',null,null,'01-234-567-89',null,null,null);
 7 -- Deletando um Cliente Físico
 8 CALL TRANSACTION_CLIENT ('DELETE','CLIENTE_FISICO',1,null,null,null,null,null,null,null,null);
 9 -- Selecionado todos os registros
10 CALL TRANSACTION_CLIENT ('READ','CLIENTES',null,null,null,null,null,null,null,null,null);

Como podem ver, ao criar (CREATE) um registro com CALL, dependendo de ser cliente físico ou jurídicos, os campos preenchidos são distintos. Já na parte de remoção de registros (DELETE), precisa-se apenas especificar a chave primária da tabela Clientes, que o trigger que criamos, será responsável por remover os dados da tabela “filho”. No CALL de selecionar (READ) os registros, apenas é necessário informar os parâmetros de operação e parâmetros da tabela a ser realizada a transação, já que utilizamos uma VIEW contendo todos os campos para a listagem completa.

Bom, vou me despedindo por aqui, espero que você tenha entendido um pouco de transações com procedure através de tabelas com herança no MySQL. Um abraço. Morfei!


A Palavra:

"E nenhum de vocês pode encompridar a sua vida, por mais que se preocupe com isso."
(Mateus 6:27 NTLH)

Deixe seu comentário