Gerando Procedures automaticamente no SQL 2000
Parte 01/05 – Tabelas e constraints
Uma das tarefas mais “braçais” do SQL Server é a escrita de procedures. É sempre o mesmo “copy and paste”, muitas vezes tedioso, quando a query envolve muitas tabelas ou campos.
Nessa série de cinco matérias, vou mostrar:
a) como criar um conjunto de tabelas relacionadas, conceituando as principais constraints utilizadas e mostrando a forma mais simples de uso de cada uma;
b) como criar uma procedure que “escreva” para você uma procedure de INSERT para qualquer tabela;
c) como criar uma procedure que “escreva” para você uma procedure de DELETE (lógico ou fisico) para qualquer tabela;
d) como criar uma procedure que “escreva” para você uma procedure de UPDATE para qualquer tabela;
e) como criar uma procedure que “escreva” para você uma procedure de SELECT para qualquer conjunto de tabelas.
Definindo constraints
Quando digitamos dados em uma tabela qualquer, podemos ter situações de erro, como:
a) o mesmo ator sendo cadastrado com dois códigos diferentes (por exemplo, em uma tabela de atores);
b) o cadastramento duplicado de um filme (numa tabela de filmes, por exemplo);
c) o cadastramento de dois filmes com o mesmo código (também numa tabela de filmes);
d) a falta de preenchimento de campos obrigatórios;
e) o preenchimento incorreto de campos;
f) prenchimento de um ator inexistente na tabela de atores, para associá-lo a um filme também inexistente (como na tabela filme_ator)
Para que evitemos esses possíveis erros, usamos as propriedades dos campos e constraints. A forma mais básica de se diminuir erros de dados incorretos é definir corretamente os DATATYPES dos campos. Por exemplo, se um campo deve armazenar uma data, devemos usar o datatype datetime ou o smalldatetime, e nunca um char, varchar, nchar, nvarchar ou text, por exemplo. Esta simples definição de tipo já inibe o cadastramento de data incorreta. Lógico que essa dica também vale para o cadastramento de campos que devem aceitar somente números, inteiros ou não.
Listagem 01 – EXEMPLO DE USO DE DATATYPES
USE PUBS
GO
CREATE TABLE TESTE
(
CODIGO INT,
NOME VARCHAR(50),
DATA_CADASTRO DATETIME
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO TESTE VALUES (1, 'AGNALDO', GETDATE())
/*
RESULTADO:
(1 row(s) affected)
*/
INSERT INTO TESTE VALUES (2, 'ANA', '2006-13-15') -- DATA_CADASTRO INVÁLIDA
/*
RESULTADO:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
*/
INSERT INTO TESTE VALUES ('A1BC', 'MARIA', NULL) -- CÓDIGO INVÁLIDO
/*
RESULTADO:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'A1BC' to a column of data type int.
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
Para obrigar (ou não) o preenchimento de um campo, usamos os valores NULL ou NOT NULL para a propriedade NULLABLE de cada campo. Onde o valor for NULL, pode-se deixar de fornecer valor para ele. Se o valor for NOT NULL, o campo se torna obrigatório.
Listagem 02 – EXEMPLO DE USO DE NULL/NOT NULL
USE PUBS
GO
CREATE TABLE TESTE
(
CODIGO INT NULL,
NOME VARCHAR(50) NOT NULL,
DATA_CADASTRO DATETIME NOT NULL
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO TESTE VALUES (1, 'AGNALDO', GETDATE())
/*
RESULTADO:
(1 row(s) affected)
*/
INSERT INTO TESTE VALUES (2, NULL, '2006-12-15') -- NULL PARA O CAMPO NOME
/*
RESULTADO:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'NOME', table 'pubs.dbo.TESTE'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO TESTE VALUES (3, 'MARIA', NULL) -- NULL PARA O CAMPO DATA_CADASTRO
/*
RESULTADO:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'DATA_CADASTRO', table 'pubs.dbo.TESTE'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
Para se evitar que um mesmo código (supondo que a tabela tenha um campo código) seja cadastrado duas vezes, definimos que esse campo tenha a propriedade IDENTITY. Essa propriedade atribui, de forma automática, um valor ao campo. Só podemos definir um único campo IDENTITY por tabela.
Listagem 03 – EXEMPLO DE USO DE IDENTITY
USE PUBS
GO
CREATE TABLE TESTE
(
CODIGO INT NOT NULL IDENTITY,
NOME VARCHAR(50) NOT NULL,
DATA_CADASTRO DATETIME NOT NULL
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO TESTE VALUES ('AGNALDO', GETDATE())
INSERT INTO TESTE VALUES ('JOÃO', '2006-12-15')
/*
RESULTADO:
(1 row(s) affected)
(1 row(s) affected)
*/
SELECT * FROM TESTE
/*
RESULTADO:
CODIGO NOME DATA_CADASTRO
------- --------- ------------------------
1 AGNALDO 2006-07-01 16:57:51.577
2 JOÃO 2006-12-15 00:00:00.000
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
E para preencher um campo com um valor pré-definido, quando o usuário não informar o valor para um campo? Usamos a propriedade DEFAULT. Essa propriedade atribui um valor padrão ao campo, quando o usuário não definir conteúdo para ele.
Listagem 04 – EXEMPLO DE USO DE DEFAULT
USE PUBS
GO
CREATE TABLE TESTE
(
CODIGO INT NOT NULL IDENTITY,
NOME VARCHAR(50) NOT NULL,
DATA_CADASTRO DATETIME NOT NULL DEFAULT GETDATE()
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO TESTE VALUES ('AGNALDO', '2006-12-15')
INSERT INTO TESTE VALUES ('JOÃO', DEFAULT) -- INSERE O DEFAULT EM DATA_CADASTRO
INSERT INTO TESTE(NOME) VALUES ('CHICO') -- INSERE O DEFAULT EM DATA_CADASTRO
/*
RESULTADO:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
*/
SELECT * FROM TESTE
/*
RESULTADO:
CODIGO NOME DATA_CADASTRO
------- --------- ------------------------
1 AGNALDO 2006-12-15 00:00:00.000
2 JOÃO 2006-07-01 18:01:33.623
3 CHICO 2006-07-01 18:01:37.015
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
Mas, só isso basta? Claro que não. Aí que entram as constraints. Constraints definem REGRAS QUE AVALIAM E VALIDAM os valores digitados pelo usuário. A primeira constraint que veremos é a UNIQUE, que define que o campo não pode ter valores repetidos. Podemos ter até 254 campos definidos como UNIQUE na mesma tabela.
Listagem 05 – EXEMPLO DE USO DE UNIQUE
USE PUBS
GO
CREATE TABLE TESTE
(
CODIGO INT NOT NULL IDENTITY,
NOME VARCHAR(50) NOT NULL UNIQUE,
DATA_CADASTRO DATETIME NOT NULL
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO TESTE VALUES ('AGNALDO', GETDATE())
INSERT INTO TESTE VALUES ('JOÃO', '2006-12-15')
/*
RESULTADO:
(1 row(s) affected)
(1 row(s) affected)
*/
INSERT INTO TESTE VALUES ('JOÃO', '2006-12-31')
/*
RESULTADO:
Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UQ__TESTE__2D27B809'. Cannot insert duplicate key in object 'TESTE'.
The statement has been terminated.
*/
INSERT INTO TESTE VALUES ('PEDRO', '2006-12-31')
/*
RESULTADO:
(1 row(s) affected)
*/
SELECT * FROM TESTE
/*
RESULTADO:
CODIGO NOME DATA_CADASTRO
------- --------- ------------------------
1 AGNALDO 2006-07-01 18:14:34.090
2 JOÃO 2006-12-15 00:00:00.000
4 PEDRO 2006-12-31 00:00:00.000
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
Vemos, na listagem acima, que não foi incluído o registro (linha) que receberia o código 3. Por que? Quando ocorre um erro de CONSTRAINT e existe na tabela um campo definido como IDENTITY, o código que seria usado é descartado. Isso é assim por causa da necessidade que o SQL Server em manter um alto desempenho.
Imagine um ambiente onde existam 1000 máquinas cadastrando dados na mesma tabela. A constraint somente é testada no momento da inclusão/alteração/exclusão dos dados. Se for inclusão (como na listagem acima) e, se der erro de constraint em 15 das máquinas, o que o SQL Server deveria fazer? Sincronizar os códigos de todos os 985 usuários que não tenham dado erro? Isso levaria muito tempo. Por isso a escolha do SQL Server é descartar os códigos.
Uma outra constraint que usamos é o CHECK, que realiza uma verificação no conteúdo de um campo, quando um valor é fornecido para ele. Valores null não são validados. Se o conteúdo do campo não estiver de acordo com a regra definida, o comando não é executado. Devemos sempre nos lembrar que a sintaxe do CHECK é compatível com a da cláusula WHERE, sendo válidas regras como:
a) Campo > 10;
b) Campo <= 1000;
c) Campo between 1 and 10;
d) Campo in ('abóbora', 'batata', 'caqui')
e) Campo like '[1234567890][1234567890][1234567890]-[1234567890]'
Listagem 05 – EXEMPLO DE USO DE CHECK
USE PUBS
GO
CREATE TABLE TESTE
(
CODIGO INT NOT NULL IDENTITY,
NOME VARCHAR(50) NOT NULL UNIQUE,
DATA_CADASTRO DATETIME NOT NULL CHECK ( DATA_CADASTRO BETWEEN '20060101' AND '20061231' )
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO TESTE VALUES ('AGNALDO', GETDATE())
/*
RESULTADO:
(1 row(s) affected)
*/
INSERT INTO TESTE VALUES ('JOÃO', '2004-12-31')
/*
RESULTADO:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'CK__TESTE__DATA_CADA__300424B4'. The conflict occurred in database 'pubs', table 'TESTE', column 'DATA_CADASTRO'.
The statement has been terminated.
*/
INSERT INTO TESTE VALUES ('PEDRO', '2006-12-31')
/*
RESULTADO:
(1 row(s) affected)
*/
SELECT * FROM TESTE
/*
RESULTADO:
CODIGO NOME DATA_CADASTRO
------- --------- ------------------------
1 AGNALDO 2006-07-01 18:14:34.090
3 PEDRO 2006-12-31 00:00:00.000
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
As duas últimas constraints são usadas para relacionar tabelas e, geralmente, vêm aos pares. São a PRIMARY KEY, que define que não se pode cadastrar valores repetidos para um campo qualquer e a FOREIGN KEY, que valida o valor digitado pelo usuário em uma tabela, pesquisando o mesmo valor na chave primária de outra tabela. Vale ressaltar que uma tabela pode ter diversas chaves estrangeiras (FOREIGN KEYS), mas APENAS UMA chave primária (PRIMARY KEY).
Listagem 06 – EXEMPLO DE USO DE PRIMARY E FOREIGN KEYS
USE PUBS
GO
CREATE TABLE PAI
(
CODIGO INT NOT NULL IDENTITY PRIMARY KEY,
NOME VARCHAR(50) NOT NULL
)
/*
RESULTADO:
The command(s) completed successfully.
*/
CREATE TABLE FILHO
(
CODIGO INT NOT NULL IDENTITY PRIMARY KEY,
NOME VARCHAR(50) NOT NULL,
CODIGO_DO_PAI INT NULL FOREIGN KEY REFERENCES PAI
)
/*
RESULTADO:
The command(s) completed successfully.
*/
INSERT INTO PAI VALUES ('AGNALDO')
INSERT INTO PAI VALUES ('JOÃO')
INSERT INTO PAI VALUES ('CHICO')
/*
RESULTADO:
(1 row(s) affected)
*/
SELECT * FROM PAI
/*
RESULTADO:
CODIGO NOME
------- ---------
1 AGNALDO
2 JOÃO
3 CHICO
*/
INSERT INTO FILHO VALUES ('BRUNO', 1) -- FILHO DO PAI 1 (AGNALDO)
INSERT INTO FILHO VALUES ('NETINHO', 1) -- FILHO DO PAI 1 (AGNALDO)
INSERT INTO FILHO VALUES ('PEDRO', 1) -- FILHO DO PAI 1 (AGNALDO)
/*
RESULTADO:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
*/
INSERT INTO FILHO VALUES ('PEDRO', 4) -- FILHO DO PAI 4 (NÃO ESTÁ CADASTRADO)
/*
RESULTADO:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__FILHO__CODIGO_DO__35BCFE0A'. The conflict occurred in database 'pubs', table 'PAI', column 'CODIGO'.
The statement has been terminated.
*/
INSERT INTO FILHO VALUES ('JOÃOZINHO', 2) -- FILHO DO PAI 2 (JOÃO)
/*
RESULTADO:
(1 row(s) affected)
*/
SELECT * FROM FILHO
/*
RESULTADO:
CODIGO NOME CODIGO_DO_PAI
------- ----------- -------------
1 BRUNO 1
2 NETINHO 1
3 PEDRO 1
5 JOÃOZINHO 2
*/
DROP TABLE TESTE
/*
RESULTADO:
The command(s) completed successfully.
*/
Criação das tabelas
Para que esses exemplos fiquem consistentes, criaremos um database para cadastro de filmes (dvds), representado pelo diagrama abaixo:
Figura 1.
No diagrama acima, temos as seguintes informações:
|
|
O campo é uma CHAVE PRIMÁRIA (primary key). Se existirem dois ou mais campos marcados com esse símbolo, todos eles farão parte da MESMA chave primária |
|
|
Relacionamento entre duas tabelas |
|
|
Tabela que contém a CHAVE PRIMÁRIA (primary key) do relacionamento |
|
|
Tabela que contém a CHAVE ESTRANGEIRA (foreign key) relacionamento |
Listagem 07 - CRIANDO O DATABASE E AS TABELAS
USE MASTER
GO
IF EXISTS(SELECT 1 FROM SYSDATABASES WHERE NAME = 'FILMES')
DROP DATABASE FILMES
GO
CREATE DATABASE FILMES
GO
USE FILMES
CREATE TABLE ATOR
(
COD_ATOR INT NOT NULL IDENTITY PRIMARY KEY,
NOM_ATOR VARCHAR(50) NOT NULL UNIQUE,
DAT_INC DATETIME NOT NULL DEFAULT GETDATE(),
DAT_EXC DATETIME NULL
)
CREATE TABLE DIRETOR
(
COD_DIRETOR INT NOT NULL IDENTITY PRIMARY KEY,
NOM_DIRETOR VARCHAR(50) NOT NULL UNIQUE,
DAT_INC DATETIME NOT NULL DEFAULT GETDATE(),
DAT_EXC DATETIME NULL
)
CREATE TABLE GENERO
(
COD_GENERO INT NOT NULL IDENTITY PRIMARY KEY,
NOM_GENERO VARCHAR(50) NOT NULL UNIQUE,
DAT_INC DATETIME NOT NULL DEFAULT GETDATE(),
DAT_EXC DATETIME NULL
)
CREATE TABLE FILME
(
COD_FILME INT NOT NULL IDENTITY PRIMARY KEY,
NOM_FILME VARCHAR(50) NOT NULL,
NOM_ORIGINAL_FILME VARCHAR(50) NULL,
ANO_FILME INT NULL CHECK(ANO_FILME BETWEEN 1940 AND YEAR(GETDATE())),
DAT_INC DATETIME NOT NULL DEFAULT GETDATE(),
DAT_EXC DATETIME NULL
)
CREATE TABLE FILME_ATOR
(
COD_FILME INT NOT NULL FOREIGN KEY REFERENCES FILME,
COD_ATOR INT NOT NULL FOREIGN KEY REFERENCES ATOR,
CONSTRAINT PK_FILME_ATOR PRIMARY KEY(COD_FILME, COD_ATOR)
)
CREATE TABLE FILME_DIRETOR
(
COD_FILME INT NOT NULL FOREIGN KEY REFERENCES FILME,
COD_DIRETOR INT NOT NULL FOREIGN KEY REFERENCES DIRETOR,
CONSTRAINT PK_FILME_DIRETOR PRIMARY KEY(COD_FILME, COD_DIRETOR)
)
CREATE TABLE FILME_GENERO
(
COD_FILME INT NOT NULL FOREIGN KEY REFERENCES FILME,
COD_GENERO INT NOT NULL FOREIGN KEY REFERENCES GENERO,
CONSTRAINT PK_FILME_GENERO PRIMARY KEY(COD_FILME, COD_GENERO)
)
/*
RESULTADO:
Query batch completed. (aparece na barra de status do Query Analyzer)
*/
Lembre-se: não me preocupei em ensinar a sintaxe das constraints, que pode ser conteúdo de uma outra matéria, pois o foco é apenas a sua definição conceitual.
Agora, é só aguardar as próximas quatro matérias, onde mostrarei a criação automática das procedures. Até mais.
