Resumo

Prévia do material em texto

Linguagem Avançada de Banco de Dados
1) EQUI-JOIN
NATURAL JOIN: É a junção duas ou mais colunas que possuem o mesmo nome e o mesmo tipo de dados. Sintaxe:
SELECT *
FROM table1
NATURAL JOIN table2;
EQUI JOIN: É utilizado com o JOIN seguido da palavra ON e possui a mesma função do Natural Join, porém neste caso, é necessário especificar as colunas a serem relacionadas. Sintaxe:
SELECT column_list 
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;
SELF JOIN: É a junção de uma tabela com ela mesmo. Sintaxe:
SELECT a.column_name, b.column_name... 
FROM table1 a, table1 b 
WHERE a.common_filed = b.common_field;
2) NON-EQUI-JOIN: É a junção que envolve qualquer operador de comparação, além da igualdade. Sintaxe:
SELECT *
FROM table_name1, table_name2 
WHERE table_name1.column [> | < | >= | <= ] table_name2.column;
3) OUTER-JOIN
LEFT OUTER JOIN: Retorna todos os registros da tabela da esquerda (isto é, a primeira tabela mencionada), com os valores da tabela direita correspondente, ou quando não há correspondência retorna o valor NULL. Sintaxe:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
RIGHT OUTER JOIN: Retorna todos os registros da tabela da direita (isto é, a primeira tabela mencionada), com os valores da tabela esquerda correspondente, ou quando não há correspondência retorna o valor NULL. Sintaxe:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
FULL OUTER JOIN: Retorna todos os registros das tabelas à esquerda e à direita e apresentará valores nulos para os registros sem correspondência. Sintaxe:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
CROSS JOIN: Cada registro da primeira tabela se relaciona com todos os elementos da segunda tabela. Sintaxe:
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
VIEW: Pode ser considerada como uma tabela virtual ou uma consulta armazenada para visualizar uma ou mais entidades que compõem uma base de dados. É selecionada através do comando select. Vantagens:
· Restringir o acesso aos dados: através do comando select é possível filtrar linhas e colunas que não podem ser visualizadas por todos os usuários;
· Independência de dados;
· Representar diferentes visões dos mesmos dados.
Sintaxe:
CREATE VIEW nome_view
AS SELECT campos FROM tabela(s)
WHERE condição;
SEQUENCE: São objetos que geram números sequenciais e são muito utilizados na criação de IDs para chave primária na inserção de registros na tabela. Sintaxe:
CREATE SEQUENCE nome_sequence
INCREMENT BY n
START WITH n
MAXVALUE n |NOMAXVALUE
CACHE n | NOCACHE
CYCLE | NOCYCLE;
ÍNDICES: São objetos do banco que você pode criar para aumentar o desempenho das queries.
Um índice é criado automaticamente quando você define:
· uma constraint de primary key; 
· uma constraint unique.
Usuários podem criar índices em determinadas colunas para aumentar a velocidade de acesso na recuperação das linhas. Sintaxe:
CREATE INDEX index
ON table (column[, column]...);
DICIONÁRIO DE DADOS: É um conjunto de tabelas e visões que armazenam informações sobre o Banco de Dados que incluem os nomes dos usuários do servidor Oracle, os privilégios concedidos aos usuários, os nomes dos objetos do Banco de Dados, as constraints de Tabela e as informações de auditoria. 
	Há quatro categorias de views de dicionário de dados, sendo que cada categoria possui um prefixo distinto, que identifica o uso pretendido.
· USER_: contém detalhes dos objetivos pertencentes ao usuário;
· ALL_: contém detalhes dos objetos para os quais o usuário ganhou direitos de acesso e dos objetos pertencentes ao usuário;
· DBA_: contém detalhes dos usuários com privilégios de DBA para acessar qualquer objeto do banco de dados;
· VS_: armazena informações sobre o desempenho ou bloqueio do servidor do banco de dados, disponível para os administradores do banco. 
PL/SQL: Program Language SQL é a linguagem de programação da Oracle que permite desenvolver programas que são armazenados, compilados e executados dentro do servidor de banco de dados Oracle. É tipicamente utilizado em aplicações que requerem alto desempenho na execução de suas tarefas.
Vantagens:
· Portabilidade: qualquer máquina que rode o banco Oracle pode executar a mesma aplicação PL/SQL;
· Integração com o gerenciador de banco: visto que as variáveis podem ser definidas a partir da definição de colunas no banco, as alterações nele realizadas podem automaticamente (ou seja, sem manutenções) refletir nos blocos PL/SQL;
· Capacidade Procedural: comandos de repetição, controle de fluxo e tratamento de erros;
· Produtividade: desenvolvimento de procedures, functions e triggers.
Sintaxe:
· DECLARE (opcional): Variáveis, cursores, constantes, tabelas, estruturas, excepetions;
· BEGIN (obrigatório): SQL statements, PL/SQL statements;
· EXCEPTION (opcional): Ações que deverão ser executadas quando ocorrer erros;
· END (obrigatório).
	Podemos ter três tipos de blocos PL/SQL: anônimos, procedures e functions. 
%TYPE - HERANÇA DE TIPO E TAMANHO: É usada para declarar uma variável com base em: um campo de uma tabela e outra variável já declarada. 
CURSORES: São áreas compostas de linhas e colunas em memória que servem para armazenar o resultado de uma seleção que retorna zero ou mais registros. Temos dois tipos de cursores: 
· Implícitos: declarado e gerenciado pelo Oracle;
· Explícito: declarado e gerenciado pelo programador (OPEN, FETCH e CLOSE).
STORED PROCEDURE: São blocos PL/SQL armazenados no servidor de banco de dados de forma compilada que NÃO retorna valor. Podem ou não receber parâmetros e esses parâmetros podem ser de três tipos:
· IN (padrão): passa um valor do ambiente chamador para o subprograma e esse valor não pode ser alterado dentro do subprograma;
· OUT: passa um valor do subprograma para o ambiente chamador;
· IN/OUT: passa um valor do ambiente chamados para o subprograma. Esse valor pode ser alterado dentro do subprograma e retornado com o valor atualizado para o ambiente chamador.
Somente variáveis do tipo OUT ou IN/OUT podem sofrer alterações dentro da procedure.
Sintaxe:
	CREATE OR REPLACE PROCEDURE Nome_Proc
		(Argumento1 modo Tipo_de_Dados,
Argumento2 modo Tipo_de_Dados,
Argumento3 modo Tipo_de_Dados)
IS ou AS
Variáveis locais, constantes...
BEGIN
Bloco PL/SQL
Excepetion
END nome_do_procedure;
FUNCTIONS (Funções em português): São blocos PL/SQL nomeados, muito semelhantes às Stored Procedures. A diferença principal é que as Funções obrigatoriamente devem retornar um valor.
As funções são muito utilizadas para computar valores, promover reusabilidade e facilidade de manutenção e podem ser chamadas a partir de outros blocos PL/SQL ou até mesmo por instruções SQL.
Sintaxe:
	CREATE OR REPLACE PROCEDURE Nome_Funcao
		(Argumento1 modo Tipo_de_Dados,
Argumento2 modo Tipo_de_Dados,
Argumento3 modo Tipo_de_Dados)
RETURN Tipo_de_Dado
IS ou AS
Declarações
BEGIN
Bloco PL/SQL
Excepetion
END nome_da_funcao;
	As funções não podem ser usadas nas seguintes opções:
· Em uma cláusula de restrição CHECK de um CREATE TABLE ou ALTER TABLE, em uma cláusula DEFAULT de uma instrução CREATE TABLE ou ALTER TABLE. 
· Além disso, quando uma função é chamada a partir de uma consulta ou instrução DML, a função NÃO pode ter OUT ou IN OUT como parâmetros de informações.
EXCEPTIONS: São utilizados no Oracle quando ocorre algum erro. Existem dois tipos de exceções dentro do Oracle:
· Exceções Predefinidas: são exceções existentes implicitamente dentro do Oracle e que são disparadas automaticamente por ele quando ocorre um erro no programa;
· Exceções Definidas pelo Usuário: são exceções que precisam ser declaradas e disparadas pelo usuário. O Oracle desconhece sua existência.
	Sintaxe:
DECLARE
-- variáveis
BEGIN
--
-- comandos
BEGIN
-- comandos
EXCEPTION
WHEN <exception 1> THEN
-- comandos ou mensagens
WHEN <exception n> THEN
-- comandos ou mensagens
END;
-- mais comandos
--
EXCEPTION
WHEN <exception 1> THEN
-- comandosou mensagens
WHEN <exception n> THEN
-- comandos ou mensagens
END;
PACKAGES: É um componente o qual podemos agrupar funções, procedures, variáveis, tipos de dados e etc, como componentes em uma biblioteca armazenada no banco, onde vários usuários poderão ter acesso e se beneficiar de tudo que tem no package com o intuito de organizar esses objetos por assunto.
	Uma package é composta por:
· Especificação: especification;
· Corpo: body.
	Sintaxe:
· Área de Especificação: Declarações públicas, ou seja, as variáveis, constantes, cursores, exceções e subprogramas que estarão disponíveis para uso externo à package.
CREATE OR REPLACE PACKAGE Nome_da_Package IS
	Para procedures e functions, só os cabeçalhos (interface)
	PROCEDURE Nome_da_Procedure (Lista_de_Parâmetros);
FUNCTIONS Nome_da_Function (Lista_de_Parâmetros);
Declaração variáveis, constantes, exceções e cursores públicos
END
· Corpo: Declarações privadas que estarão disponíveis apenas dentro da própria package e a definição das ações para os subprogramas, sejam eles públicos (área de especificação) ou privados.
CREATE OR REPLACE PACKAGE BODY Nome_da_Package IS
Declaração variáveis, constantes, exceções e cursores públicos
	PROCEDURE Nome_da_Procedure (Lista_de_Parâmetros)
	IS
	BEGIN
	END;
FUNCTIONS Nome_da_Function (Lista_de_Parâmetros);
RETURN tipo
	BEGIN
	RETURN
	END;
END
TRIGGERS: São blocos PL/SQL disparados automaticamente e implicitamente sempre que ocorrer um evento associado a uma determinada tabela (INSERT, UPDATE e DELETE).
	As Triggers são amplamente utilizadas para a realização de tarefas relacionadas com validações, restrições de acesso, rotinas de segurança e consistência de dados; assim, estes controles deixam de ser executados pela aplicação e passam a ser executados pelos Triggers em determinadas situações:
· Mecanismos de validação envolvendo múltiplas tabelas;
· Criação de conteúdo de uma coluna derivada de outras colunas da tabela;
· Realizar análise e atualizações em outras tabelas com base em alterações e/ou 
· inclusões da tabela atual;
· Preenchimento de campo resultante de uma expressão;
· Crítica aos dados com mensagens mais adequadas às regras do negócio;
· Acessos que alterem linhas de uma tabela ou eventos que ocorram no BD podem ser registrados em outra tabela (auditoria).
Triggers representam unidades de programa que são executadas, automaticamente, antes ou depois de um comando disparador, que pode ser tanto um DML (update, insert ou delete) como um DDL (create, alter, drop, truncate table), ou mesmo um evento ocorrido no BD (conexão, por exemplo).
A criação de uma Trigger envolve duas etapas:
· Um comando SQL que vai disparar o Trigger (INSERT, DELETE, UPDATE);
· A ação que o Trigger vai executar (Geralmente um bloco de códigos SQL).
A utilização de triggers possui algumas limitações:
· Não é possível criar um Trigger para uma visão;
· O resultado da execução de um Trigger é retornado para a aplicação que o chamou;
· O comando TRUNCATE TABLE não pode ser reconhecido por um Trigger.
A diferença da trigger para stored procedure, é a forma de execução. Uma trigger é disparada no momento da execução de um UPDADE, INSERT OU DELETE.