Pular para o conteúdo principal

O que devemos conhecer sobre SQL/Oracle Parte 10: Criando outros objetos de schema


Criando e usando VIEWS simples e complexas

Uma VIEW (visão) nada mais é que um comando SELECT nomeado, e armazenado na base de dados, sendo acessado da mesma maneira que uma tabela comum. Possui como objetivo ocultar a complexidade da consulta que o forma, seja apenas para méritos de facilidade de uso ou segurança.
A criação de uma VIEW é muito simples, basta utilizar o comando de criação seguido da consulta, vale ressaltar que expressões devem ser nomeadas caso contrário um erro ocorrerá, sua sintaxe é a seguinte:

CREATE VIEW VW_FUNCIONARIOS AS SELECT F.COD, F.NOME FROM FUNCIONARIOS F;

Também é possível criar VIEWs capazes de atualizar a tabela na qual ela é baseada, para tal devemos ter em mente:

  • As restrições da tabela devem ser respeitadas;
  • Se a consulta utilizar a cláusula GROUP BY ou qualquer outro tipo de agregação ela não será gerada;
  • Se a cláusula DISTINCT for utilizada também não será possível à criação;
  • Se na cláusula FROM houver referencia a outras tabelas, ou subconsultas, também não será possível;


Não podemos deixar de mencionar que subconsultas utilizadas na cláusula FROM de um comando SELECT são consideradas VIEWs, conhecidas por INLINE VIEWS.
Outra questão importante é a possibilidade de uma VIEW passar para o estado de inválido, nessa situação ela não poderá ser utilizada, para torna-la valida novamente basta utilizar o comando:

ALTER VIEW VW_FUNCIONARIOS COMPILE;

Resumindo:
  • VIEW é um comando SELECT armazenado no banco de dados e possui um nome;
  • Colunas e expressões utilizadas no comando SELECT se tornam as colunas da VIEW;
  • É possível usar VIEWS em comandos SELECT, do mesmo modo que uma tabela é usada;
  • É possível fazer INSERT, UPDATE ou DELETE em VIEWS, dependendo das restrições referentes a tabela da VIEW, colunas requeridas (NOT NULL) devem estar listadas, o comando SELECT não pode conter funções agregadas, não pode conter a clausula DISTINCT e também não funciona quando existem mais de uma tabela na clausula FROM (exceto se a chave primaria for preservada no resultado da consulta e as restrições anteriores forem obedecidas);
  • INLINE VIEW é uma subconsulta que substitui a referencia de uma tabela na clausula FROM;
  • Uma VIEW pode ser tratada como uma tabela, salvo algumas restrições;
  • Uma VIEW baseada em uma tabela que posteriormente é alterada devera ser recompilada com o comando ALTER VIEW NOME_VIEW COMPILE;


Criando, mantendo e usando SEQUENCES

Uma SEQUENCE é capaz de gerar números sequenciais, tendo como principal uso a geração de números para chaves primarias, lembrando que não existe nada em sua estrutura que impeça seu uso em outras circunstancias. Uma SEQUENCE possui as seguintes opções:

  • INCREMENT BY: valor utilizado para incrementar ou decrementar (caso o valor seja negativo) a cada requisição da SEQUENCE, se omitido assume o valor um;
  • START WITH: primeiro numero da SEQUENCE, se omitido assume MINVALUE para SEQUENCES ascendentes, MAXVALUE para descendentes, e no caso de NOMAXVALUE ou NOMINVALUE terem sido escolhidos e valor default passa a ser um;
  • MAXVALUE: define o maior valor para a SEQUENCE, se omitido é assumido NOMAXVALUE;
  • NOMAXVALUE: define que não existe numero máximo para a SEQUENCE;
  • MINVALUE: define o menor numero da SEQUENCE, se omitido assume NOMINVALUE, a menos que ele seja requerido pela presença do CYCLE, em outras situações assume um;
  • NOMINVALUE: indica que não existe valor mínimo;
  • CYCLE: quando o limite da SEQUENCE é atingido a geração de valores reinicia, quando ascendente e atinge MAXVALUE recebe MINVALUE, quando descendente e atinge MINVALUE recebe MAXVALUE;
  • NOCYCLE: quando a SEQUENCE atinge seu limite a geração de números é interrompida, ele é default;


A sintaxe de criação de uma SEQUENCE é a seguinte:

CREATE SEQUENCE SEQ_FUNCIONARIOS START WITH 10 INCREMENT BY 1;

Uma SEQUENCE só pode ser usada para recuperar o próximo valor (NEXTVAL) ou o atual (CURRVAL), quando solicitado o próximo valor a SEQUENCE pega o valor atual incrementa de acordo com suas regras e devolve o novo valor, veja um exemplo de uso:

INSERT INTO FUNCIONARIOS(COD, NOME) VALUES(SEQ_FUNCIONARIOS.NEXTVAL, ‘MARIA’);

Resumindo:
  • SEQUENCE é um objeto que gera números de acordo com suas regras;
  • Uma SEQUENCE especifica um numero inicial, um numero de incremento (positivo ou negativo), e limites opcionais;
  • SEQUENCES são normalmente usadas para alimentar PRIMARY KEY;
  • A pseudocoluna NEXTVAL retorna o próximo valor disponível da SEQUENCE, independente da sessão;
  • A pseudocoluna CURRVAL retorna o valor atual existente, já definido pelo NEXTVAL, referenciado por sessão e após o uso do NEXTVAL;
  • As pseudocolunas são expressões validas e podem ser usadas em qualquer local permitido para expressões, como a clausula SELECT;
  • A pseudocoluna NEXTVAL sempre gera um novo valor, mesmo que o comando onde a SEQUENCE esteja falhe;


Criando e mantendo INDEXES

O INDEX é utilizado para agilizar consultas nas tabelas, sendo sempre considerado nas cláusulas WHERE e ORDER BY, logo uma coluna indexada presente nessas cláusulas irá resultar em uma execução mais rápida.
Esse objeto é criado de maneira implícita a cada geração de uma restrição de chave primaria ou única. Ele pode fazer referencia a uma ou mais colunas, quando possui mais de uma coluna é considerado composto. Veja a seguir como criar ou remover um índice:

CREATE INDEX IX_FUNCIONARIO_DEP ON FUNCIONARIOS(COD, DEPARTAMENTO);
DROP INDEX IX_FUNCIONARIO_DEP;

Resumindo:
  • Um INDEX é baseado em uma ou mais colunas de uma tabela;
  • O INDEX copia os dados da coluna que faz referencia a ele para realizar uma pré-seleção com o intuito de deixar mais rápido as consultas futuras;
  • Quando um comando SELECT, UPDATE ou DELETE é executado em uma tabela indexada, e a coluna que possui o INDEX é alterada, ocorre um WORKLOAD automático para atualizar seus dados;
  • O INDEX pode ser usado com uma ou mais colunas, quando varias colunas são usadas o INDEX é considerado composto;


Criando SYNONYMS privados e públicos

Um SYNONYM é um apelido que pode ser concedido para tabelas, sequências ou mesmo outros sinônimos. Seu uso se dá principalmente em casos onde a diminuição da complexidade dos nomes se faz necessária. Um sinônimo pode ser considerado publico ou privado, sendo os públicos criados com a cláusula PUBLIC, nesse caso o sinônimo passa a pertencer a uma conta especial do Oracle chamada PUBLIC, onde todos os usuários da base possuem acesso. Se a criação do sinônimo não contiver o PUBLIC então ele passa a pertencer ao usuário da sessão atual, sendo visível apenas a este. É importante salientar que é perfeitamente possível termos, por exemplo, sinônimos com o mesmo nome, sendo um publico e outro privado, a precedência aplicada nesse caso será sempre a do objeto do usuário local. Veja um exemplo de criação:

CREATE PUBLIC SYNONYM VWF FOR VW_FUNCIONARIOS;

Devemos ter em mente que o objeto criado acima passa a ser visível a todos os usuários da base, porem seu uso efetivo se dará apenas para aqueles que tiverem direito de acesso a VIEW em questão.

Resumindo:
  • SYNONYM é um apelido para outros objetos do banco de dados;
  • Um SYNONYM pode ser criado para objetos que ainda não existem;
  • Um SYNONYM privado é criado com o comando CREATE SYNONYM para o usuário da sessão atual;
  • O SYNONYM público é criado com o comando CREATE PUBLIC SYNONYM, pertencendo ao usuário PUBLIC;
  • SYNONYMS públicos se tornam automaticamente visíveis para todos os usuários do banco, porem para poder usa-los o usuário da sessão atual deve possuir privilégios sobre o objeto ao qual o SYNONYM esta referenciando;



Comentários

Postagens mais visitadas deste blog

Iniciar e Parar Serviços do Windows (Delphi)

Em certas ocasiões nos deparamos com a necessidade de manipular determinadas atividades do SO, como iniciar ou parar um banco de dados, ou qualquer outro serviço que esteja funcionando no momento. Segue abaixo um código que encontrei na Internet para tal finalidade (não me recordo à fonte, assim que eu a encontrar colocarei).
Iniciar Serviço:

uses WinSvc;
//
// start service
//
// return TRUE if successful
//
// sMachine:
//   machine name, ie: \SERVER
//   empty = local machine
//
// sService
//   service name, ie: Alerter
//
function ServiceStart(
  sMachine,
  sService : string ) : boolean;
var
//
  // service control
  // manager handle
  schm,
//
  // service handle
  schs   : SC_Handle;
//
  // service status
  ss     : TServiceStatus;
//
  // temp char pointer
  psTemp : PChar;
//
  // check point
  dwChkP : DWord;
begin
  ss.dwCurrentState := -1;
// connect to the service
  // contr…

Alterar cores do PageControl (Delphi)

O padrão Windows todo cinza não é muito atraente, por isso quando nos utilizarmos do PageControl podemos alterar suas cores e fontes da seguinte maneira:
Em primeiro lugar devemos alterar a propriedade OwnerDraw para TRUE;
Depois implementar seu método DrawTab da seguinte maneira: //pinta a fonte
Control.Canvas.Font.Color:=clBlack;
//
// pinta a paleta / aba
Control.Canvas.brush.Color:=clSkyBlue;

PageControl1.Canvas.Rectangle(Rect);
Control.Canvas.TextOut(Rect.left+5,Rect.top+3,PageControl1.Pages[tabindex].Caption);
// pinta a parte interna (tabsheet)
PageControl1.Pages[TabIndex].brush.Color := Control.Canvas.brush.Color;
PageControl1.Pages[TabIndex].Repaint;

Caso a intenção seja manter cada aba com seu próprio estilo basta adicionar um CASE filtrando o índice das abas: case TabIndex of
  0: Control.Canvas.Font.Color:=clBlack;
  1: Control.Canvas.Font.Color:=clWindow;
  2: Control.Canvas.Font.Color:=clRed;
en…

Listar arquivos existentes em diretório (Delphi)

Mostraremos uma maneira simples e prática para listar o conteúdo de um diretório com a opção de incluir nessa listagem os arquivos de seus subdiretórios. No exemplo abaixo temos um Edit para receber o diretório a ser pesquisado um CheckBox para indicar se os subdiretórios entrarão na pesquisa um botão para efetuar a pesquisa e um Memo para listar os arquivos encontrados, no final um Edit que receberá o cálculo final (em bytes) da soma do tamanho dos arquivos. procedure TForm1.Button1Click(Sender: TObject);
begin
  tamanhoTotal := 0;
  memLista.Lines.Clear;
  ListarArquivos(edtDiretorio.Text, chkSub.Checked);
  Edit1.Text := IntToStr( tamanhoTotal );
end;
procedure TForm1.ListarArquivos(Diretorio: string; Sub:Boolean);
var
  F: TSearchRec;
  Ret: Integer;
  TempNome: string;
begin
  Ret := FindFirst(Diretorio+'\*.*', faAnyFile, F);
try
while Ret = 0 do
begin
if TemAtributo(F.Attr, faDirectory) then
begin
if (F.Name <> '.') And (F.Name &l…