Banco de Dados - Senado
ORACLE
Ilka Kawashita – ikawashi@gmail.com
ORACLE
n Linguagens SQL e PL/SQL
n Procedures;
n Packages;
n Functions;
n Triggers;
n Views;
n Materialized views;
n Jobs e Sequences;
Linguagem SQL
n SQL – Structured Query Language
q INCITS/ISO/IEC 9075-*-2008
q ISO/IEC 9075-*:2008
q http://www.jcc.com/sql.htm
Linguagem PL/SQL
n Linguagem de comandos 3GL criada para processar comandos SQL.
n Provê sintaxe específica para esse fim e tem os mesmos typos de dados do SQL.
n No lado do servidor é armazenado e compilado no banco Oracle e é executado dentro do
executável Oracle.
n Herda automaticamente a robustez, segurança
e portabilidade do Banco de dados Oracle.
Transação
n São unidades lógicas de trabalho, usadas para dividir suas atividades no banco de dados.
n Sempre tem um início e um fim.
n Uma transação começa quando um dos seguintes eventos ocorrem:
q Você se conecta ao bd e executa o primeiro comando DML
q A transação anterior termina e você entra outro comando DML.
Transações
n Uma transação termina quando um dos seguintes eventos ocorrem:
q Um comando COMMIT ou ROLLBACK é executado;
q Um comando DDL, como o CREATE TABLE é executado, nesse caso um COMMIT é automaticamente executado;
q Um comando DCL, como o TABLE é executado, nesse caso um COMMIT é automaticamente executado;
q Quando você se desconecta do banco de dados;
q Se você sair normalmente do SQL*Plus usando o comando EXIT, um COMMIT é automaticamente executado;
q Se SQL*Plus terminar de forma anormal, um ROLLBACK é automaticamente executado;
q Quando um comando DML não é executado normalmente, um
ROLLBACK é automaticamente executado para esse comando
DML específico.
Propriedades ACID das Transações
n
A teoria de banco de dados tem uma definição mais rigorosa de transação e determina que uma transação tem quarto propriedades fundamentais conhecidas como ACID:
q
Transações atômicas são comitadas ou revertidas (rolled back) como um grupo, e são atômicas, ou seja, todos os comandos SQL contidos na transação são consideradas como uma unidade indivisível.
q
Transações consistentes asseguram que o banco de dados permanece em um estado consistente, ou seja, que o banco de dados inicia em um estado consistente e passa a outro estado consistente quando a
transação acaba.
q
O isolamento de transações separadas deve garantir que elas sejam executadas sem interferir uma com as outras.
q
A durabilidade garante que uma vez a transação seja comitada, as
alterações no banco de dados são preservadas, mesmo se a máquina
em que o software do banco rode dê problemas mais tarde.
Bloqueio de Transações (Locking)
n Uma transação não pode bloquear (lock)
uma linha quanto outra transação já obteve o bloqueio dessa mesma linha.
n A maneira mais fácil de entender bloqueios default é:
q Bloqueio de Leitura não bloqueia outra Leitura;
q Bloqueio de Escritura não bloqueia Leituras;
q Bloqueio de Escritura só bloqueia outra Escritura
quando eles tentam modificar a mesma linha.
Níveis de Isolamento das Transações
n Leitura fantasma (Phanton Read)
q T1 lê um conjunto de linhas retornadas por uma cláusula WHERE específica
q T2 então insere uma nova linha, que também satisfaz a cláusula WHERE que foi usada na consulta T1
q T1 então lê as linhas novamente usando a
mesma consulta, mas agora vê a linha adicional que acabou de ser inserida por T2.
q Essa nova linha é chamada “fantasma” por que
para T1 A linha parece ter surgido por mágica.
Níveis de Isolamento das Transações
n Leitura não repetíveis (Nonrepeatable reads)
q T1 lê uma linha, e T2 modifica essa mesma linha no momento em que T1 a lê.
q T1 então lê a mesma linha novamente e descobre que a linha que foi lida anteriormente está
diferente.
q Isso é conhecido como leitura não repetíveis por que a linha originalmente lida por T! Foi
modificada.
Níveis de Isolamento das Transações
n Leituras Sujas (Dirty reads)
q T1 modifica uma linha, mas não comita a alteração.
q T2 lê a linha alterada..
q T1 então executa um rollback, desfazendo a modificação.
q Agora a linha que foi lida por T2 não é mais válida (está suja) por que a modificação feita por T1
ainda não comitada quando T2 fez a leitura.
Níveis de Isolamento das Transações
n
Para tratar esses problemas potenciais, os bancos de dados implementam vários níveis de isolamento de transações para prevenir que transações concorrentes interfiram umas com as outras.
n
O standard SQL define os seguintes níveis de isolamento de transações, mostrados em ordem crescente de isolamento:
q
READ UNCOMMITTED Leitura fantasma (Phanton Read), Leituras não repetíveis (Nonrepeatable reads), e Leituras Sujas (Dirty reads) são permitidas.
q
READ COMMITTED Leitura fantasma (Phanton Read), Leituras não repetíveis (Nonrepeatable reads) são permitidas, mas Leituras Sujas (Dirty reads) não são.
q
REPEATABLE READ Leitura fantasma (Phanton Read) são permitidas, mas Leituras não repetíveis (Nonrepeatable reads), e Leituras Sujas (Dirty reads) não são.
q
SERIALIZABLE Leitura fantasma (Phanton Read), Leituras não
repetíveis (Nonrepeatable reads), e Leituras Sujas (Dirty reads) não são
permitidos
Níveis de Isolamento no Oracle
n O Oracle suporta os níveis de isolamento de transações READ COMMITTED e
SERIALIZABLE.
n Ele não suporta os níveis de isolamento de transações READ UNCOMMITTED ou
REPEATABLE READ.
n O nível de isolamento de transações default
definido pelo standard SQL é o SERIALIZABLE, mas o default usado pelo banco de dados
Oracle é o READ COMMITTED, que é aceitável
para a maioria das aplicações.
Procedures
n Uma procedure contém um grupo de comandos SQL e PL/SQL.
n Procedures permitem que a lógica do negócio seja centralizada no banco de dados.
n Procedures podem ser usadas em qualquer programa que acesse o banco de dados.
n O comando CREATE PROCEDURE é usa do para criar uma procedure
n A sintaxe simplificada do comando CREATE
PROCEDURE é:
CREATE PROCEDURE
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
procedure_body
END procedure_name;
SQL> create or replace procedure hello_world 2 as
3 begin
4 dbms_output.put_line('Hello World!');
5 end;
6 /
Procedure created.
SQL>
SQL> drop procedure hello_world
Procedure dropped.
Parâmetros
n IN é o modo default para um parâmetro;
n Parâmetros IN já têm um valor quando a procedure é executada;
n O valor de um parâmetro IN não pode ser modificado no corpo da procedure;
n Os parâmetros OUT são usados para valores que serão definidos no corpo da procedure;
n Os parâmetros IN OUT podem já ter um valor quando
a procedure é chamada, mas seus valores podem ser
alterados no corpo da procedure.
Packages/Pacote
n Packages encapsulam funcionalidades relacionadas em um pacote.
n Packages são tipicamente feitas de dois componentes: uma especificação e um corpo.
n A especificação de um package contém informação sobre o package.
n A especificação de um package lista as procedures e funções disponíveis
n Estas estão potencialmente disponíveis para todos os usuários do banco de dados
n A especificação do package geralmente não contém código.
n O corpo do package é que contém o código
CREATE OR REPLACE PACKAGE
SQL> create or replace package pkg_test1 2 as
3 function getArea (i_rad NUMBER) return NUMBER;
4 procedure p_print (i_str1 VARCHAR2 :='hello', 5 i_str2 VARCHAR2 :='world',
6 i_end VARCHAR2 :='!' );
7 end;
8 /
Package created.
SQL>
SQL> create or replace package body pkg_test1 2 as
3 function getArea (i_rad NUMBER)return NUMBER 4 is
5 v_pi NUMBER:=3.14;
6 begin
7 return v_pi * (i_rad ** 2);
8 end;
9
10 procedure p_print(i_str1 VARCHAR2 :='hello', 11 i_str2 VARCHAR2 :='world',
12 i_end VARCHAR2 :='!' ) 13 is
14 begin
15 DBMS_OUTPUT.put_line(i_str1||','||i_str2||i_end);
16 end;
17 end;
18 /
Package body created.
Functions/Funções
n Uma função é parecida com uma
procedure, só que sempre retorna um valor.
n O comando CREATE FUNCTION é usado para criar uma função, cuja sintaxe
simplificada mostrada no próximo slide
CREATE OR REPLACE FUNCTION
CREATE [OR REPLACE] FUNCTION function_na me
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type {IS | AS}
BEGIN
function_body
END function_name;
Parâmetros
n Onde:
q OR REPLACE especifica que a função deve substituir um função, se ela já existir.
q type especifica o tipo de parâmetro PL/SQL
n O Corpo da função tem que retornar um valor do tipo PL/SQL especificado na
cláusula RETURN.
Triggers
n Um trigger é um evento dentro do DBMS que pode causar que código seja executado automaticamente.
n Existem quatro tipos de triggers no banco de dados:
n Triggers ao nível de tabelas podem iniciar atividade antes ou depois de um evento INSERT, UPDATE, ou DELETE.
n Triggers no nível de views definem o que pode ser feito com uma view.
n Triggers no nível do banco de dados podem ser ativados durante o startup ou shutdown de um banco.
n Triggers no nível de sessão podem ser usados para
armazenar informação específica.
Exemplo de Trigger
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60)
6 );
SQL> create table product_audit(
2 product_id number(4) not null,
3 num_rows number(8) not null
4 );
CREATE TRIGGER
SQL> CREATE OR REPLACE TRIGGER myTrigger 2 AFTER INSERT ON company
3 FOR EACH ROW 4 BEGIN
5 UPDATE product_audit
6 SET num_rows =num_rows+1
7 WHERE product_id =:NEW.product_id;
8 IF (SQL%NOTFOUND) THEN
9 INSERT INTO product_audit VALUES (:NEW.product_id,1);
10 END IF;
11 END;
12 /
Views
n Uma view é uma consulta predefinida em uma ou mais tabelas.
n A recuperação informação de uma view é feita da mesma maneira que a recuperação em uma tabela.
n Operações DML (delete, insert, update) podem ser executadas nas tabelas base por algumas views.
n Views não armazenam dados, elas apenas acessam as linhas das tabelas base
n User_tables, user_sequences, e user_indexes são todas views.
n Views só permitem que o usuário recupere dados.
n Views podem esconder as tabelas de base
n Ao escrever consultas complexas como views, esconde-se a complexidade das mesmas do usuário.
n A view só permite que o usuário acesse apenas certas linhas das
tabelas base.
Views
n Subsets ou combinação de dados
n Simplificação
n Fonte dos dados: base tables ou views
n Armazenadas nos dicionário de dados
n Restringir acesso
n Não armazenam dados em disco
CREATE VIEW
CREATE [OR REPLACE] VIEW [{FORCE | NOFORCE}] VIEW view_name
[(alias_name[, alias_name...])] AS subquery [WITH {CHECK OPTION |
READ ONLY} CONSTRAINT constraint_name];
n Onde:
q OR REPLACE especifica que a view deve substituir uma view já existente, se presente
q FORCE especifica que a view deve ser criada
mesmo que a tabela base não exista
Parâmetros
n
NOFORCE especifica que a view não deve ser criada se a tabela base não existir.
NOFORCE é o default.
n
alias_name especifica o nome de um alias (apelido) de uma expressão na subquery.
n
O número de aliases deve ser o mesmo que o número de expresses na subquery.
n
subquery especifica a subquery que recupera dados das tabelas base.
n
Se aliases forem criados, eles podem ser usados na lista depois da cláusula SELECT.
n
WITH CHECK OPTION especifica que apenas as linhas que serão recuperadas pela subquery podem ser inseridas, atualizadas ou deletadas
n
Por default, linhas não são checadas para saber se elas são passíveis de serem recuperadas pela subquery antes de serem inseridas, atualizadas ou deletadas.
n
constraint_name especifica o nome da constraint WITH CHECK OPTION ou READ ONLY.
n
