Banco de Dados - Senado - Gran Cursos Presencial

Banco de Dados - Senado
ORACLE
Ilka Kawashita – [email protected]
Material preparado: Ilka Kawashita
ORACLE
Linguagens SQL e PL/SQL
n  Procedures;
n  Packages;
n  Functions;
n  Triggers;
n  Views;
n  Materialized views;
n  Jobs e Sequences;
n 
Prof.: Ilka Kawashita
[email protected]
Linguagem SQL
n 
SQL – Structured Query Language
INCITS/ISO/IEC 9075-*-2008
q  ISO/IEC 9075-*:2008
q  http://www.jcc.com/sql.htm
q 
Prof.: Ilka Kawashita
[email protected]
Linguagem PL/SQL
n 
n 
n 
n 
Linguagem de comandos 3GL criada para
processar comandos SQL.
Provê sintaxe específica para esse fim e tem os
mesmos typos de dados do SQL.
No lado do servidor é armazenado e compilado
no banco Oracle e é executado dentro do
executável Oracle.
Herda automaticamente a robustez, segurança
e portabilidade do Banco de dados Oracle.
Prof.: Ilka Kawashita
[email protected]
Transação
n 
n 
n 
São unidades lógicas de trabalho, usadas para
dividir suas atividades no banco de dados.
Sempre tem um início e um fim.
Uma transação começa quando um dos
seguintes eventos ocorrem:
q 
q 
Você se conecta ao bd e executa o primeiro comando DML
A transação anterior termina e você entra outro comando DML.
Prof.: Ilka Kawashita
[email protected]
Transações
n 
Uma transação termina quando um dos seguintes eventos ocorrem:
q 
q 
q 
q 
q 
q 
q 
Um comando COMMIT ou ROLLBACK é executado;
Um comando DDL, como o CREATE TABLE é executado, nesse
caso um COMMIT é automaticamente executado;
Um comando DCL, como o TABLE é executado, nesse caso um
COMMIT é automaticamente executado;
Quando você se desconecta do banco de dados;
Se você sair normalmente do SQL*Plus usando o comando
EXIT, um COMMIT é automaticamente executado;
Se SQL*Plus terminar de forma anormal, um ROLLBACK é
automaticamente executado;
Quando um comando DML não é executado normalmente, um
ROLLBACK é automaticamente executado para esse comando
DML específico.
Prof.: Ilka Kawashita
[email protected]
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.
Prof.: Ilka Kawashita
[email protected]
Bloqueio de Transações (Locking)
n 
n 
Uma transação não pode bloquear (lock)
uma linha quanto outra transação já obteve o
bloqueio dessa mesma linha.
A maneira mais fácil de entender bloqueios
default é:
q 
q 
q 
Bloqueio de Leitura não bloqueia outra Leitura;
Bloqueio de Escritura não bloqueia Leituras;
Bloqueio de Escritura só bloqueia outra Escritura
quando eles tentam modificar a mesma linha.
Prof.: Ilka Kawashita
[email protected]
Níveis de Isolamento das Transações
n 
Leitura fantasma (Phanton Read)
q 
q 
q 
q 
T1 lê um conjunto de linhas retornadas por uma
cláusula WHERE específica
T2 então insere uma nova linha, que também
satisfaz a cláusula WHERE que foi usada na
consulta T1
T1 então lê as linhas novamente usando a
mesma consulta, mas agora vê a linha adicional
que acabou de ser inserida por T2.
Essa nova linha é chamada “fantasma” por que
para T1 A linha parece ter surgido por mágica.
Prof.: Ilka Kawashita
[email protected]
Níveis de Isolamento das Transações
n 
Leitura não repetíveis (Nonrepeatable reads)
q 
q 
q 
T1 lê uma linha, e T2 modifica essa mesma linha
no momento em que T1 a lê.
T1 então lê a mesma linha novamente e descobre
que a linha que foi lida anteriormente está
diferente.
Isso é conhecido como leitura não repetíveis por
que a linha originalmente lida por T! Foi
modificada.
Prof.: Ilka Kawashita
[email protected]
Níveis de Isolamento das Transações
n 
Leituras Sujas (Dirty reads)
q 
q 
q 
q 
T1 modifica uma linha, mas não comita a
alteração.
T2 lê a linha alterada..
T1 então executa um rollback, desfazendo a
modificação.
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.
Prof.: Ilka Kawashita
[email protected]
Níveis de Isolamento das Transações
n 
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.
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
Prof.: Ilka Kawashita
[email protected]
Níveis de Isolamento no Oracle
n 
n 
n 
O Oracle suporta os níveis de isolamento de
transações READ COMMITTED e
SERIALIZABLE.
Ele não suporta os níveis de isolamento de
transações READ UNCOMMITTED ou
REPEATABLE READ.
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.
Prof.: Ilka Kawashita
[email protected]
Procedures
n 
n 
n 
n 
n 
Uma procedure contém um grupo de comandos SQL e
PL/SQL.
Procedures permitem que a lógica do negócio seja
centralizada no banco de dados.
Procedures podem ser usadas em qualquer programa
que acesse o banco de dados.
O comando CREATE PROCEDURE é usa do para criar
uma procedure
A sintaxe simplificada do comando CREATE
PROCEDURE é:
Prof.: Ilka Kawashita
[email protected]
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.
Prof.: Ilka Kawashita
[email protected]
Parâmetros
n 
n 
n 
n 
n 
IN é o modo default para um parâmetro;
Parâmetros IN já têm um valor quando a procedure é
executada;
O valor de um parâmetro IN não pode ser modificado
no corpo da procedure;
Os parâmetros OUT são usados para valores que
serão definidos no corpo da procedure;
Os parâmetros IN OUT podem já ter um valor quando
a procedure é chamada, mas seus valores podem ser
alterados no corpo da procedure.
Prof.: Ilka Kawashita
[email protected]
Packages/Pacote
n 
n 
n 
n 
n 
n 
n 
Packages encapsulam funcionalidades relacionadas em um
pacote.
Packages são tipicamente feitas de dois componentes: uma
especificação e um corpo.
A especificação de um package contém informação sobre o
package.
A especificação de um package lista as procedures e funções
disponíveis
Estas estão potencialmente disponíveis para todos os
usuários do banco de dados
A especificação do package geralmente não contém código.
O corpo do package é que contém o código
Prof.: Ilka Kawashita
[email protected]
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.
Prof.: Ilka Kawashita
[email protected]
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.
Prof.: Ilka Kawashita
[email protected]
Functions/Funções
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
n 
Prof.: Ilka Kawashita
[email protected]
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;
Prof.: Ilka Kawashita
[email protected]
Parâmetros
n 
Onde:
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
q 
n 
O Corpo da função tem que retornar um
valor do tipo PL/SQL especificado na
cláusula RETURN.
Prof.: Ilka Kawashita
[email protected]
Triggers
n 
n 
n 
n 
n 
n 
Um trigger é um evento dentro do DBMS que pode
causar que código seja executado automaticamente.
Existem quatro tipos de triggers no banco de dados:
Triggers ao nível de tabelas podem iniciar atividade
antes ou depois de um evento INSERT, UPDATE, ou
DELETE.
Triggers no nível de views definem o que pode ser feito
com uma view.
Triggers no nível do banco de dados podem ser ativados
durante o startup ou shutdown de um banco.
Triggers no nível de sessão podem ser usados para
armazenar informação específica.
Prof.: Ilka Kawashita
[email protected]
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 );
Prof.: Ilka Kawashita
[email protected]
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 /
Prof.: Ilka Kawashita
[email protected]
Views
n 
n 
n 
n 
n 
n 
n 
n 
n 
Uma view é uma consulta predefinida em uma ou mais tabelas.
A recuperação informação de uma view é feita da mesma maneira
que a recuperação em uma tabela.
Operações DML (delete, insert, update) podem ser executadas nas
tabelas base por algumas views.
Views não armazenam dados, elas apenas acessam as linhas das
tabelas base
User_tables, user_sequences, e user_indexes são todas views.
Views só permitem que o usuário recupere dados.
Views podem esconder as tabelas de base
Ao escrever consultas complexas como views, esconde-se a
complexidade das mesmas do usuário.
A view só permite que o usuário acesse apenas certas linhas das
tabelas base.
Prof.: Ilka Kawashita
[email protected]
Views
n 
n 
n 
n 
n 
n 
Subsets ou combinação de dados
Simplificação
Fonte dos dados: base tables ou views
Armazenadas nos dicionário de dados
Restringir acesso
Não armazenam dados em disco
Prof.: Ilka Kawashita
[email protected]
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 
q 
OR REPLACE especifica que a view deve
substituir uma view já existente, se presente
FORCE especifica que a view deve ser criada
mesmo que a tabela base não exista
Prof.: Ilka Kawashita
[email protected]
Parâmetros
n 
n 
n 
n 
n 
n 
n 
n 
n 
NOFORCE especifica que a view não deve ser criada se a tabela base não existir.
NOFORCE é o default.
alias_name especifica o nome de um alias (apelido) de uma expressão na
subquery.
O número de aliases deve ser o mesmo que o número de expresses na
subquery.
subquery especifica a subquery que recupera dados das tabelas base.
Se aliases forem criados, eles podem ser usados na lista depois da cláusula
SELECT.
WITH CHECK OPTION especifica que apenas as linhas que serão recuperadas
pela subquery podem ser inseridas, atualizadas ou deletadas
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.
constraint_name especifica o nome da constraint WITH CHECK OPTION ou
READ ONLY.
WITH READ ONLY especifica que linhas só podem lidas das tabelas de base.
Prof.: Ilka Kawashita
[email protected]
Tipos de Views
n 
Existem dois tipos básicos de views:
q 
Views simples que contém uma subquery para
recuperar dados de apenas uma tabela base
n 
q 
Views complexas que contém uma subquery que
n 
n 
n 
q 
Operações DML só podem ser executadas em views
simples
Recupera dados de várias tabelas base
Agrupa linhas usando as cláusulas GROUP BY ou
DISTINCT
Contém a chamada à uma função
Operações DML não podem ser executadas em
views complexas
Prof.: Ilka Kawashita
[email protected]
Restrições das Views
n 
Restrições para criar Views que permitem
INSERT, UPDATE e DELETE
q 
q 
q 
DELETE - GROUP BY, funções de grupo (AVG,
COUNT, SUM, etc.), DISTINCT
UPDATE – idem DELETE +ROWNUM, colunas
com expressão
INSERT- idem UPDATE + colunas NOT NULL
das base tables não usadas
Prof.: Ilka Kawashita
[email protected]
View Simples
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- --------------01 Jason
Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James
Smith
12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia
Rice
24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black
15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda
Green
30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David
Larry
31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James
Cat
17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
Prof.: Ilka Kawashita
[email protected]
View Simples
SQL> CREATE VIEW my_view AS
2 SELECT *
3 FROM employee
4 WHERE id < 5;
View created.
SQL>
SQL> select * from my_view;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- --------------01 Jason
Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James
Smith
12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia
Rice
24-OCT-82 21-APR-99 2344.78 Vancouver Manager
SQL>
SQL> drop view my_view;
Prof.: Ilka Kawashita
[email protected]
View
dropped.
View Complexa
SQL> create table courses
2 ( code
VARCHAR2(6)
3 , description VARCHAR2(30)
4 , category CHAR(3)
5 , duration NUMBER(2)) ;
Table created.
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
Prof.: Ilka Kawashita
[email protected]
View Joining Duas Tabelas
SQL> create or replace view crs_course_schedule as
2 select o.course as course_code, c.description, o.begindate
3 from course_schedule o
4
join
5
courses c
6
on (o.course = c.code);
View created.
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
SQL> drop table courses;
Table dropped.
Prof.: Ilka Kawashita
[email protected]
Materialized Views
n 
Uma view materilizada deve ser vista
como:
Tipo especial de view que existe fisicamente
no banco de dados;
q  Pode conter joins e/ou funções agregadas;
q  Foram criadas para melhorar o tempo de
execução das consultas pelo cálculo prévio de
joins caros e operações de agregação antes
da execução;
q  Usadas nas DWs
q 
Prof.: Ilka Kawashita
[email protected]
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW costs_mv
PCTFREE 0
STORAGE (initial 8k next 8k pctincrease 0)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS SELECT time_id, prod_name, SUM
( unit_cost) AS sum_units, COUNT(unit_cost)
AS count_units, COUNT(*) AS cnt
FROM costs c, products p WHERE c.prod_id =
p.prod_id GROUP BY time_id, prod_name;
Prof.: Ilka Kawashita
[email protected]
Sequences
Sequence é um item do banco de dados
que gera uma sequência de inteiros.
n  Geralmente usado para popular uma
coluna chave primária numérica.
n 
Prof.: Ilka Kawashita
[email protected]
Criar Sequência
CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[ { MAXVALUE maximum_num | NOMAXVALUE } ]
[ { MINVALUE minimum_num | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE cache_num | NOCACHE } ]
[ { ORDER | NOORDER } ];
SQL> CREATE TABLE test (
2
record_id NUMBER(18,0),
3
record_text VARCHAR2(10)
4 );
Table created.
SQL>
SQL> CREATE SEQUENCE test_seq;
Prof.: Ilka Kawashita
[email protected]
Sequência
SQL> create sequence deptno_seq start with 50 increment by 10;
Sequence created.
SQL>
SQL> select deptno_seq.nextval, deptno_seq.currval
2 from dual;
NEXTVAL CURRVAL
---------- ---------50
50
SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.
Prof.: Ilka Kawashita
[email protected]
Jobs
n 
Um conjunto de uma ou mais atividades
administrativas que são programadas com
o Oracle Enterprise Manager Job System
que roda em bancos gerenciados ou
outros serviços.
Prof.: Ilka Kawashita
[email protected]
Exemplo JOB
SQL>
SQL> CREATE TABLE run_table (message VARCHAR2(40));
Table created.
SQL>
SQL> -- create a procedure to insert rows into the test table:
SQL> CREATE OR REPLACE PROCEDURE p_run_insert IS
2 BEGIN
3 INSERT INTO run_table VALUES ('Execution at ' || to_char(sysdate, 'ddmon-yy hh:mi:ss') ) ;
4 COMMIT ;
5 END;
6 /
Procedure created.
Prof.: Ilka Kawashita
[email protected]
Exemplo JOB – Cont.
SQL>
SQL> -- schedule that procedure to run every 10 seconds (approximately):
SQL> VARIABLE p_jobno number
SQL> BEGIN
2 DBMS_JOB.SUBMIT (:p_jobno,'P_RUN_INSERT;', SYSDATE, 'SYSDATE + (1
0/(24*60*60))');
3 COMMIT; /* must commit after SUBMIT */
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> PRINT p_jobno
P_JOBNO
---------122
Prof.: Ilka Kawashita
[email protected]
Exemplo JOB – Cont.
SQL> exec dbms_job.remove(1)
BEGIN dbms_job.remove(1); END;
*
ERROR at line 1:
ORA-23421: job number 1 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 171
ORA-06512: at line 1
SQL>
SQL> drop table run_table;
Table dropped.
Prof.: Ilka Kawashita
[email protected]
Referências
n 
Oracle Database 10g SQL, Osborne ORACLE
Press Series, McGraw-Hill Osborne Media; 1st
edition (20/02/2004), ISBN-13: 978-0072229813
n 
n 
http://www.java2s.com/Tutorial/Oracle
http://www.oracle.com/technetwork/database/
features/plsql/index.html
n 
http://www.oracle.com/technetwork/database/
focus-areas/bi-datawarehousing/twp-bi-dwmaterialized-views-10gr2--131622.pdf
Prof.: Ilka Kawashita
[email protected]