Trabalhando com tabelas no Oracle, MySQL e SQL Server

Na década de 70 foi publicado um artigo pelo pesquisador da IBM, Ted Cold que tratava o uso de cálculos de álgebra relacional para facilitar o acesso a dados pelos usuários. Neste artigo foi divulgada as 12 leis de um banco de dados relacional, o que contrariava os sistemas de bancos de dados da época de nível hierárquico.

Logo após, a IBM criou um grupo de pesquisa para desenvolver um sistema voltado para armazenamento de informações e facilidade de acesso ao usuário. Logo foram gerados protótipos de bancos de dados com o intuito de que esses protótipos logo virassem produto.

Logo que esse grupo de pesquisa tornou-se o SQL/DS, foi também desenvolvida uma linguagem para manipulação dos dados SQL – Structured Query Language, que é a mesma hoje adotada como padrão ISO para criação de SGBD Relacional.

Em 1976 foi lançado como produto, não pela IBM, o primeiro SGBD Relacional, que trazia muito dos conceitos dos estudos realizados pela IBM. Em 1980 começaram a ser lançado outros SGBD Relacionais: pela Oracle com o Oracle 2 e, logo após pela IBM com o SQL/DS que atualmente é o DB2.

Conceituando Tabelas

Banco de dados é um container de informações e sua finalidade é armazenar informações e liberar fácil acesso ao usuário. As informações do banco de dados são armazenadas em tabelas, que são compostas por linhas e colunas, muito parecida com uma planilha de Excel e são ordenadas de maneira lógica conforme são criadas.

As linhas de uma tabela são consideradas registros de uma coluna e cada coluna é considerada um registro de uma tabela. Tabelas são objetos de um banco de dados e também são pertencentes a um mesmo staff (no SQL Server as tabelas pertencem a um schema e no Oracle a tablespaces).

Uma tabela pertencer a um “grupo” faz com que se tenha um controle maior a nível de segurança, tornando possível separar objetos e usuários por grupos e nos dá mais flexibilidade para conceder acesso aos usuários.

A mudança do modelo hierárquico é um assunto primordial, pois os SGBDs têm um modelo relacional que faz com que as tabelas se ligassem.

Uma tabela passou a ter ligação uma com a outra através de chaves, as quais trazem mais facilidade de acesso aos dados. Estas chaves são conhecidas como PRIMARY KEY (Chave Primária) ou FOREIGN KEY (Chave Estrangeira), as quais são as maiores responsáveis pelo desempenho no Banco de dados.

As chaves PRIMARY KEY e FOREIGN KEY não são obrigatorias segundo o padrão ANSI.

É comum ver tabelas com diversas triggers e procedures automatizando as ações sobre as tabelas. Na Listagem 1 podemos observar o comando que nos traz a listagem de chaves PRIMARY KEY e FOREIGN KEY.

Listagem 1. Comando para trazer listagem de chaves primárias e estrangeiras do SQL Server.

  SELECT 
  o.name, o.object_id, o.principal_id, o.schema_id, s.name N'Parente', 
  o.type, o.type_desc, o.create_date, o.modify_date, 
  o.is_ms_shipped, o.is_published, o.is_schema_published 
  FROM sys.objects o
  Join sys.objects s
  ON 
  o.parent_object_id = s.object_id
  WHERE o.type_desc in (
  'PRIMARY_KEY_CONSTRAINT',
  'FOREIGN_KEY_CONSTRAINT'
  )

Na Listagem 2 você poderá identificar as chaves PRIMARY KEY e FOREIGN KEY no banco de dados Oracle.

Listagem 2. Comando para retornar chaves primárias e estrangeiras no banco de dados Oracle.

  SELECT 
  a.table_name||' ('||  rtrim(max(decode(c.position,1,c.column_name))||','||  
  max(decode(c.position,2,c.column_name))||','||  max(decode(c.position,3,
  c.column_name))||','||  max(decode(c.position,4,c.column_name)),
  ',')||') referencia '||  b.table_name||' ('||  rtrim(max(decode(d.position,1,d.column_name))||','||  
  max(decode(d.position,2,d.column_name))||','||  max(decode(d.position,3,d.column_name))||','||  
  max(decode(d.position,4,d.column_name)),',')||')' relacionamentos  
  FROM  user_constraints a,  user_constraints b,  user_cons_columns c,  user_cons_columns d  
  WHERE  a.r_constraint_name=b.constraint_name 
  AND  a.constraint_name=c.constraint_name 
  AND  b.constraint_name=d.constraint_name 
  AND  a.constraint_type='R' 
  AND  b.constraint_type in ('P', 'U')  
  GROUP BY a.table_name, b.table_name  
  ORDER BY 1;

Outra forma de manter a integridade dos dados é utilizando a CONSTRAINT, que restringe os dados inseridos em uma tabela. As CONSTRAINTS podem ser definidas já na criação da tabela.

São exemplos de CONSTRAINTS:

• PRIMARY KEY: Principal chave de uma tabela. No momento em que se define uma Primary, está se dizendo que toda a coluna é uma Primary key. Responsável pela ordenação dos dados, na maioria das vezes a chave Primary é uma Foreign Key em uma outra tabela.
• FOREIGN KEY: Chave que faz a relação de uma tabela com a outra, realizando a ligação de informações. Na maioria das vezes são as Primary Key e a Foreign Key responsáveis por Joins e Subquerys.
• UNIQUE – Define que em uma coluna os valores não se repetem, os valores são únicos. Exemplo: a coluna CPF não poderá conter dois CPF iguais.
• NOT NULL – Define que um campo não pode receber o valor NULL. O valor NULL se aplica a campos onde não se tem a informação requerida, ou seja, são informações “desconhecidas”.
• CHECK – Limita que uma coluna só possa ser preenchida com valores definidos pela CONSTRAINT Check. Exemplo: na tabela clientes, a coluna sexo só pode ser preenchida com “M” ou “F”, tratando a integridade da tabela.

Essas CONSTRAINTS podem ser definidas na criação ou depois de já criada as tabelas. Caso a tabela já esteja criada, você poderá utilizar a claúsula ALTER TABLE ADD CONSTRAINT.

Para iniciar a criação de uma tabela também é necessário o conhecimento dos data types ou tipos de dados, que irá definir o tipo de dado a preencher a coluna. Exemplo: em uma coluna no qual o data type é INT, a coluna só poderá ser preenchida com valores numéricos inteiros. É muito importante que os data types sejam analisados antes da criação da tabela, pois após definir uma coluna com tipo de dado Int não será possível preenche-lo com string.

São exemplos de data types:

• Date: Utilizado para tipo de dados datas.
• Number: Utilizado para tipo de dados numéricos.
• Varchar: Utilizado para tipo de dados string.
• Int: Utilizado para tipo de dados numéricos inteiros.

Os data types são detalhes importantes para a manipulação dos dados na tabela: em um INSERT ou em um UPDATE, é necessário que o tipo de dados inserido seja o mesmo tipo de dados da criação da tabela. Há formas de converter um data type utilizando a cláusula CONVERT ou CAST, que pode manipular os tipos de dados.

Observe que para manipulação de tabelas alguns data types devem obedecer a alguns padrões, como a utilização de ‘’ (caractere plique) quando se insere ou altera um campo como data ou string.

Funções DDL e DML

Existem duas funções que são básicas para se trabalhar com tabelas: as funções DML (Data Manipulation Language) ou seja, são funções de manipulação de dados; e também as funções DDL (Data Definition Language) ou seja, funções de definição.

Tratando das funções DML, DDL ou DCL poderia estar utilizando o banco de dados Oracle, MySQL, SQL Server ou outro banco de dados relacionais que essas funções seriam ainda assim necessárias.

As Funções DDL trabalham com objetos e se aplicam para criação, alteração ou exclusão de objetos. Aplicam-se as funções DLL CREATE, ALTER e DROP. Na Listagem 3 podemos verificar a utilização da função DDL.

Listagem 3. Utilização da função DDL no MySQL.

  CREATE TRIGGER Tr_Nome_da_Trigger
 BEFORE INSERT ON Nome_da_Tabela
 FOR EACH ROW
 BEGIN
 IF Campo_da_Tabela THEN
 SET Campo_da_Tabela = 0 ;
 END IF;
 END;

Na Listagem 4 pode ser verificada a utilização da função DDL no banco de dados Oracle. Lembrando que faz parte da função DDL somente a cláusula CREATE, o restante do corpo é o básico exigido para a criação do objeto. Funções como CREATE, ALTER OU DROP sempre estão fazendo referência a um objeto do banco.

Listagem 4. Template de criação de procedure no Oracle.

  CREATE PROCEDURE Nome_Da_Procedure
    parametro ,parametro
  IS
     Declaração
  BEGIN
      Campo de execução de seleção
  Exceção de seleção
  END;

Na Listagem 5 pode-se identificar a utilização da função DDL no banco de dados SQL Server. Antes da utilização desta função sempre é recomendado que seja realizado um backup, pois está diretamente alterando objetos que armazenam informações do banco.

Listagem 5. Utilizando ALTER no SQL Server.

  ALTER TABLE schema.nome_do_objeto(
              Campo data type,
              Campo data type,
              Campo data type,
              Campo data type,
  ) ON [PRIMARY]

As Funções DML são aquelas utilizadas para manipular as informações contidas em uma tabela, ou seja, para selecionar dados, inserir, atualizar ou deletá-los. Funções DDL e DML devem ter seus acessos muito bem controlados para garantir a integridade da informação.

Das funções DML, a mais utilizada é a SELECT, responsável pela seleção de dados. A sintaxe básica do SELECT obriga somente a utilização do mesmo, diferentemente de outras funções DML que obrigam a utilização de outras cláusulas.

Manipulando Tabelas

A utilização das funções DML tem uma sintaxe muito parecida nos bancos de dados relacionais. São elas: SELECT, UPDATE, INSERT e DELETE, assim como as DML, que são: CREATE, ALTER e DROP.

A utilização delas requer acesso, ou seja, temos que ter acesso aos objetos para utilizá-la. Na Listagem 6 podemos verificar a utilização do CREATE TABLE.

Listagem 6. Sintaxe básica do CREATE TABLE

 CREATE TABLE Nome_da_Tabela
  (Coluna_Nome_1 data_type(Tamanho),
  Coluna__Nome_2 data_type(Tamanho),
  Coluna_Nome_3 data_type(Tamanho),
  ....)

Na Listagem 7 verificamos como se aplica na criação real de uma tabela no SQL, no MySQL e no Oracle.

Listagem 7. Utilização do CREATE TABLE.

  /*Utilização no SQL Server*/
   
  CREATE TABLE Pessoal
  (
  PessoalID Int,
  Nome Varchar(255) (NULL|NOT NULL),
  Sobrenome Varchar(255) (NULL|NOT NULL),
  Endereco Varchar(255),
  Cidade Varchar(255)
  );
   
   
  /*Utilização no MySQL*/
   
   
  CREATE TABLE Contatos (
  Nome Varchar(50) (NULL|NOT NULL),
  Email Varchar(50),
  Telefone Varchar(25) (NULL|NOT NULL),
  Celular           Varchar(25)
  )ENGINE=CSV;
   
   
  /*Utilização no Oracle*/
   
  CREATE TABLESPACE Nome_Tablespace LOGGING DATAFILE ‘/u01/app/oracle/oradata/TSH1/nome_tablespace.dbf’ 
  SIZE 100m AUTOEXTEND ON NEXT 100m EXTENT MANAGEMENT LOCAL
   
  CREATE TABLE Funcionarios (
  Nome Varchar(100),
  Sobre_nome Varchar(100),
  Telefone Char(11),
  Endereco Varchar(30),
  Idade    Number
  );

O mecanismo de armazenamento CSV (Comma Separated Values — valores separados por vírgulas) armazena os dados em arquivos de texto, separando os registros com vírgulas. ENGINE=CSV foi utilizado na Listagem 7 na sintaxe do MySQL.

Verifique que na sintaxe da criação de tabela do Oracle foi criada uma Tablespace que será a dona do objeto e logo em seguida veio a sintaxe da criação do objeto tabela.

Pode-se verificar mais uma vez a utilização da função DDL ALTER TABLE na Listagem 8, alterando colunas em uma tabela já criada no banco de dados, tanto alterando o nome de coluna como o data type.

Listagem 8. Utilização do ALTER TABLE alterando colunas de uma tabela.

  /*Utilização no MySQL
  Alterando data type de uma coluna*/
   
  ALTER TABLE Nome_Tabela
  MODIFY COLUMN Nome_Coluna data type
   
  /*Utilização no Oracle
  Alterando data type de uma coluna*/
   
  ALTER TABLE Nome_Tabela
  MODIFY Nome_Coluna data type
   
  /*Utilização no SQL Server
  Alterando data type de uma coluna*/
   
  ALTER TABLE table_name
  ALTER COLUMN column_name datatype

Com a função ALTER TABLE também é possível adicionar colunas a tabelas já existentes. Note que a função sofre pequenas alterações em sua sintaxe na utilização nos três bancos.

Na Listagem 9 podemos verificar a utilização do ALTER TABLE para adicionar colunas a uma tabela já existente no banco de dados.

Listagem 9. Utilizando a Função ALTER para adicionar colunas.

  /*Utilização no SQL Server
  Utilizando coluna em uma Tabela*/
   
  ALTER TABLE Nome_Tabela 
  ADD Nome_Coluna Data Type  (NULL|NOT NULL)
  /*Utilização no MySQL
  Utilizando coluna em uma Tabela*/
   
  ALTER TABLE Nome_Tabela 
  ADD Nome_Coluna Data Type (NULL|NOT NULL) AFTER Coluna;
   
  /*Utilização no Oracle
  Utilizando coluna em uma Tabela*/
   
  ALTER TABLE Nome_Tabela
  ADD Nome_Coluna  data type (NULL|NOT NULL) 

A Função DROP TABLE é uma função DDL que deleta um objeto do banco de dados. Assim como as outras funções DDL, ela também está presente em outros bancos de dados relacionais. Está função deve ter seus acessos bem controlados em ambientes de produção, podendo afetar a integridade dos dados.

Na Listagem 10 podemos ver a utilização da função DROP TABLE.

Listagem 10. Deletando tabelas com a função DROP TABLE.

  DROP TABLE Nome_Tabela

Caso a tabela no qual esteja sendo deletada tenha relação de FOREIGN KEY com outra tabela, a mesma não poderá ser deletada até que suas relações sejam desfeitas, por exemplo, caso queira deletar a tabela de clientes e a mesma tem relação com a tabela de vendas, só será possível concluir a ação se antes for desfeita a relação com a tabela vendas.

A função SELECT é a função mais utilizada em qualquer banco de dados, pois retorna a informação para o usuário. Ela depende somente da mesma para ser executada. Além disso podemos replicar tabelas e colunas com SELECT INTO.

Com a utilização desta função você pode ainda retornar informações presentes em mais de uma tabela, usando as cláusulas subquery, JOIN, UNION, EXCEPTION, dentre outras.

O SELECT pode ainda realizar cálculos em um banco de dados. Exemplo: SELECT 1 + 1.

Na Listagem 11 podemos verificar a sintaxe básica da função SELECT.

Listagem 11. Sintaxe básica do SELECT

  SELECT * FROM Nome_Tabela

A utilização do * indica que será retornada todas as colunas e linhas da tabela, mas essa forma de consulta não é recomendada devido ao alto custo da consulta. Ao invés de utilizar *, recomenda-se que sejam informadas as colunas necessárias.

Na Listagem 12 utiliza-se o JOIN para retornar informações de mais de uma tabela, realizando a associação através da chave primária de uma tabela que é a chave estrangeira em outra tabela.

Listagem 12. Utilizando a cláusula JOIN

  SELECT C.NOME_COLUNA, V.NOME_COLUNA
  FROM NOME_TABELA C
  JOIN NOME_TABELA V
  ON
  C.ID_COLUNA = V.ID_COLUNA

Por default, o JOIN já é identificado como INNER JOIN. Também é possível utilizar LEFT JOIN, RIGHT JOIN, FULL JOIN, OUTER JOIN dentre outros. Na Listagem 13 podemos verificar a seleção de dados utilizando a relação de tabelas com subquery.

Listagem 13. Realizando consulta com subquery.

  SELECT NOME_COLUNA, NOME_COLUNA, NOME_COLUNA
  FROM NOME_TABELA_1
  WHERE NOME_COLUNA IN (SELECT NOME_COLUNA 
  FROM NOME_TABELA_2)

A função INSERT é utilizada para inserir uma nova linha em uma tabela. Esta função pode vir acompanhada da cláusula INTO ou não.

Na utilização do INSERT, se não forem declarados os nomes das colunas, será utilizado o padrão e o banco de dados entenderá que o INSERT será aplicado em todas as colunas.

Na utilização da cláusula INSERT os valores especificados devem estar de acordo com os data type das colunas informadas.

Ainda com a cláusula INSERT você pode criar uma tabela através de um SELECT, ou seja, as informações retornadas no SELECT serão agora parte de uma outra tabela.

Na Listagem 14 pode-se verificar a sintaxe básica da utilização da função INSERT.

Listagem 14. Sintaxe básica do INSERT.

  INSERT INTO NOME_TABELA VALUES (VALOR_1, VALOR_2, VALOR_3)

Na Listagem 15 podemos verificar a utilização do INSERT seguido do SELECT para criação de uma nova tabela que terá os dados retornados do SELECT.

Listagem 15. Utilizando INSERT com SELECT.

  INSERT INTO NOME_TABELA_2 SELECT * FROM NOME_TABELA_1

A função UPDATE atualiza dados em uma tabela já existente e vem acompanhado do SET, que informa o campo que será atualizado. É importante que um UPDATE venha acompanhado, na maioria das vezes, por um WHERE que filtra o dado que será atualizado.

Na Listagem 16 podemos ver a sintaxe básica de utilização do UPDATE com o WHERE para filtrar o campo que será alterado. Caso não utilize o filtro WHERE, a alteração será aplicada em toda a coluna especificada na cláusula SET.

Listagem 16. Sintaxe básica do UPDATE.

  UPDATE Nome_Tabela SET Nome_Coluna
  WHERE Filtro

Já a função DELETE é utilizada para deletar informações de uma tabela contida no banco de dados. Assim como no INSERT, é importante que seja acompanhado da cláusula WHERE para evitar deletar todos os dados de uma tabela.

Na Listagem 17 verifica-se a utilização do DELETE para deletar uma tabela.

Listagem 17. Utilização do DELETE FROM.

  DELETE FROM Nome_Tabela

Baseado neste artigo podemos criar e manipular dados de uma tabela, através de funções DDL e DML, lembrando que em ambiente de produção deve-se ter uma regra para concessão de acesso a usuários nos objetos. As tabelas são os objetos mais importantes contido em um banco de dados, devido a estar nela todas as informações requeridas por seu usuário final. A mudança para banco de dados relacional trouxe extrema facilidade de consulta a tabelas, tornando hoje os principais bancos de dados a trabalharem de maneira relacional. Assim como qualquer objeto de banco de dados, uma tabela está ligada a um SCHEMA.

Bibliografia

Books Online SQL Server: http://technet.microsoft.com/en-us/library/ms130214.aspx