Pesquisar no Programero

quarta-feira, 3 de setembro de 2008

Usando o Oracle I


Olá a todos, nesta série de artigos vamos dar uma pincelada nos fundamentos SQL, como criar usuarios, tabelas, procedimentos, entre outras coisas.
Nesse que será o primeiro vamos criar uma pequena base de dados para os artigos seguintes, para os que ainda não possuem, podem baixar a versão gratuita do Oracle aqui, para a instalação existe um documento oficial, não é complicado de se fazer, devemos apenas nos atentar para não esquecer a senha dos usuários de administração do banco.
Criando um usuário
Antes de mais nada, devemos logar no banco como SYSTEM para podermos criar nosso usuário para então iniciar o processo de construção de nossa base:
Criando o usuário CURSO_SQL com senha de mesmo nome:
CREATE USER CURSO_SQL IDENTIFIED BY CURSO_SQL;
Agora precisamos garantir a esse usuário direitos de acesso e criação na base:
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE TRIGGER, CREATE SEQUENCE, CREATE VIEW, CREATE SESSION TO CURSO_SQL;
E por fim garantir acesso a tablespace:
GRANT UNLIMITED TABLESPACE TO CURSO_SQL;
Criando as tabelas
Após logar com o usuário criado anteriormente devemos criar nossa tabelas, antes dos códigos SQL vamos dar uma olhada em alguns dos tipos de dados e depois nos comandos necessários.
  • VARCHAR2(tamanho) - Conjunto de caracteres de comprimento variável, caso o tamanho especificado não seja atingido o que resta não ocupa espaço no banco.
  • CHAR(tamanho) - Conjunto de caracter de comprimento fixo, mais utilizado para campos de tamanho fixo.
  • NUMBER(t, d) - Números fixos ou de ponto flutuante, onde o numero apos a virgula define o total de casas decimais
  • DATE - Datas, armazena inclusive horas, minutos, segundos e século.
  • NVARCHAR2 - Conjunto de caracteres de comprimento variável que usam o conjunto nacional de caracteres, como o japones.
  • NCHAR - Conjunto de comprimento fixo que usam o conjunto nacional de caracter.
  • ROWID - Usado para armazenar rowids físicos (Endereço físico do registro no Banco de Dados).
  • RAW e LONG RAW - Usado para armazenar grandes quantidades de dados binários e figuras
  • BLOB - Dados binários de até 4 gigabytes.
  • BFILE - Dados binários armazenados em um arquivo externo.
Devemos respeitar certas regras antes da criação das tabelas:
  • Os nome devem iniciar com uma letra;
  • Devem ter de 1 a 30 caracteres;
  • Devem conter somente AZ, az, 09, _, $ e #;
  • Não podem ter o mesmo nome de outro objeto do mesmo usuário;
  • Não podem duplicar o nome de um objeto do Oracle;
O comando para criação:
  • CREATE TABLE [esquema.]tabela(coluna tipo_de_dados [DEFAULT expr][, ...]);
ou com base em uma subconsulta:
  • CREATE TABLE tabela[(coluna, coluna...)] AS subconsulta;
Para adicionar, modificar ou eliminar colunas:
  • ALTER TABLE tabela ADD (coluna tipo_de_dados [DEFAULT expr][,coluna tipo_de_dados]...);
  • ALTER TABLE tabela MODIFY (coluna tipo_de_dados [DEFAULT expr][,colun tipo_de_dados]...);
  • ALTER TABLE tabela DROP (coluna);
Para apagar uma tabela
  • DROP TABLE tabela;
Restrições
Além da criação das tabelas precisamos definir um conjunto de restrições para garantir a integridade relacional da base.
Podemos definir as restrições durante a criação da tabela
Nivel de coluna
  • CREATE TABLE [esquema.]tabela (coluna tipo_de_dados [DEFAULT expr][restrição_de_coluna],...[restrição_de_tabela][,...]);
ou Nivel de tabela
  • CREATE TABLE employees(employee_id  NUMBER(6), first_name   VARCHAR2(20), ... job_id VARCHAR2(10) NOT NULL,  CONSTRAIN emp_emp_id_pk  PRIMARY KEY (EMPLOYEE_ID));
Tipos de restrição
  • NOT NULL - Garante que na coluna indicada não serão aceitos valores nulos;
  • UNIQUE - Impossibilita que existam valores repetidos para determinada coluna;
  • PRIMARY KEY - Chave primária da tabela, pode haver apenas uma para cada tabela, identifica exclusivamente cada registro na tabela (é a combinação de NOT NULL E UNIQUE);
  • FOREIGN KEY - Chave estrangeira, responsável pela integridade referencial, estabelece um relacionamento entre a chave primaria ou exclusiva na mesma tabela ou em uma outra;
  • CHECK - Define uma condição a ser satisfeita no momento dacriação de uma tabela;
Criando as tabelas para os artigos posteriores
Nosso esquema será como o modelo abaixo:
Agora o código:
--
--
-- ## FORNECEDORES ##
CREATE TABLE FORNECEDORES( FORNECEDOR_ID NUMBER(9),
                           NOME          VARCHAR2(50),
                           EMAIL         VARCHAR2(30)
                          );   
--
ALTER TABLE FORNECEDORES ADD CONSTRAINT PK_FORNECEDOR PRIMARY KEY (FORNECEDOR_ID);
--
--
-- ## PRODUTOS ##
CREATE TABLE PRODUTOS( PRODUTO_ID     NUMBER(9),
                       DESCRICAO      VARCHAR2(80),
                       VALOR_UNITARIO NUMBER(10,2),
                       FORNECEDOR_ID  NUMBER(9)
                     );
--
ALTER TABLE PRODUTOS ADD CONSTRAINT PK_PRODUTOS PRIMARY KEY (PRODUTO_ID);
ALTER TABLE PRODUTOS ADD CONSTRAINT FK_PRODUTOS FOREIGN KEY (FORNECEDOR_ID) REFERENCES FORNECEDORES (FORNECEDOR_ID);
--
--
-- ## FUNCIONARIOS ##
CREATE TABLE FUNCIONARIOS( FUNCIONARIO_ID NUMBER(9),
                           NOME           VARCHAR2(50),
                           DATA_ADMISSAO  DATE,
                           DATA_DEMISSAO  DATE
                         );
--
ALTER TABLE FUNCIONARIOS ADD CONSTRAINT PK_FUNCIONARIOS PRIMARY KEY (FUNCIONARIO_ID);
--
--
-- ## CLIENTES ##
CREATE TABLE CLIENTES( CLIENTE_ID NUMBER(9),
                       NOME       VARCHAR2(50),
                       TELEFONE   VARCHAR2(20),
                       ENDERECO   VARCHAR2(100)
                     );
--
ALTER TABLE CLIENTES ADD CONSTRAINT PK_CLIENTES PRIMARY KEY (CLIENTE_ID);
--
--
-- ## PEDIDOS ##
CREATE TABLE PEDIDOS( PEDIDO_ID      NUMBER(9),
                      DATA_PEDIDO    DATE,
                      OBSERVACAO     VARCHAR2(100),
                      FUNCIONARIO_ID NUMBER(9),
                      CLIENTE_ID     NUMBER(9)
                    );
--
ALTER TABLE PEDIDOS ADD CONSTRAINT PK_PEDIDOS PRIMARY KEY (PEDIDO_ID);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_FUN FOREIGN KEY (FUNCIONARIO_ID) REFERENCES FUNCIONARIOS (FUNCIONARIO_ID);
ALTER TABLE PEDIDOS ADD CONSTRAINT FK_PEDIDOS_CLI FOREIGN KEY (CLIENTE_ID) REFERENCES CLIENTES (CLIENTE_ID);
--
--
-- ## ITEM_PEDIDOS ##
CREATE TABLE ITEM_PEDIDOS( PEDIDO_ID      NUMBER(9),
                           SEQUENCIA_ITEM NUMBER(9),
                           QUANTIDADE     NUMBER(4),
                           VALOR_ITEM     NUMBER(10,2),
                           PRODUTO_ID     NUMBER(9)
                         );
--
ALTER TABLE ITEM_PEDIDOS ADD CONSTRAINT PK_ITEM_PEDIDOS PRIMARY KEY(PEDIDO_ID, SEQUENCIA_ITEM);
ALTER TABLE ITEM_PEDIDOS ADD CONSTRAINT FK_ITEM_PEDIDOS_PED FOREIGN KEY(PEDIDO_ID) REFERENCES PEDIDOS (PEDIDO_ID);
ALTER TABLE ITEM_PEDIDOS ADD CONSTRAINT FK_ITEM_PEDIDOS_PRO FOREIGN KEY(PRODUTO_ID) REFERENCES PRODUTOS (PRODUTO_ID);
--
Bom, essa foi a introdução, agora que nossa base está construida iremo nos proximos artigos manipular as informações.

3 comentários:

  1. Recebi um selo do premio dardos e o estou repassando para os blogs que gosto, o seu foi um dos escolhidos.
    Visite meu blog e faça parte desta campanha.
    http://cozinhamasculina.blogspot.com

    ResponderExcluir
  2. OK e como fazemos para verificar se uma tabela já existe antes de criar a mesma, por exemplo imaginemos que ja existe um tabela PRODUTOS ao fazer CREATE TABLE PRODUTOS( PRODUTO_ID NUMBER(9),
    DESCRICAO VARCHAR2(80),
    VALOR_UNITARIO NUMBER(10,2),
    FORNECEDOR_ID NUMBER(9)
    ); vai dar erro como posso verificar se existe e em funcao do resultado criar a mesma?

    ResponderExcluir
  3. Olá ANÔNIMO, bom acredito que como responsável pelo banco você já deveria estar ciente dessa situação, mas imaginemos que por algum motivo você realmente não saiba, então vamos as possibilidades:

    - Se você estiver criando a tabela por meio de um script não se preocupe, ocorrerá erro mas o mesmo não irá interromper o fluxo do script;

    - Se for dentro de uma aplicação tente fazer um SELECT na mesma, porém nesse caso seria preciso tratar outra situação de erro;

    - Por último a melhor maneira de todas, procure por ela no dicionário de dados:

    SELECT table_name
    FROM user_tables
    WHERE table_name = 'PRODUTOS';

    Nesse caso se a consulta não retornar registro a tabela não existe.
    Espero ter ajudado, qualquer dúvida estamos ai...

    ResponderExcluir

Obrigado por Participar do programero, fique a vontade para agradecer, retificar, perguntar ou sugerir.