Gerando Procedures automaticamente no SQL 2000

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Veja nesse artigo enviado por Agnaldo Diogo dos Santos, como criar Procedures automaticamente no SQL Server 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.

 

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?