Labidi Orcl PL SQL F2
Labidi Orcl PL SQL F2
Labidi Orcl PL SQL F2
Plano (1)
Introduo ..................................................................... 5 SQL*Plus ..................................................................... 12
Plano (2)
User Variables ..................................................... 125 Transaes .......................................................... 138 Controle de Concorrncia .................................... 150 Relatrios ............................................................ 160
Plano (3)
Functions ............................................................... 358 Triggers ................................................................. 366 Packages ............................................................... 391 Cursores ................................................................ 406 Excees ............................................................... 431 Concluso ............................................................. 439
Introduo
A arquitetura Oracle praticamente a mesma para
User
Prog. Cliente
Prog. Servidor
BD
Ambiente Oracle
Oracle
tem
como
base
de
trabalho
linguagem SQL:
O ambiente composto por:
SQL, SQL*Plus,
PL/SQL, e
DBNavigator.
SQL
Linguagem de comandos para comunicao com os
SQL
uma linguagem puramente interativa de construo e submisso de comandos individuais, embora os comandos sejam macro comandos muito poderosos.
tem muita fora, porem ela no procedural: no dar para agrupar as palavras chaves em programas executveis.
SQL
PL / SQL
a linguagem procedural da Oracle para
escrever aplicaes lgicas e manipular dados fora do banco de dados, permitindo lgica de
programao.
Ela composta essencialmente de todos os
comandos SQL padro e mais um grupo adicional que permite utilizar SQL de forma procedural.
Prof. Dr. Sofiane Labidi 9
SQL * Plus
o ambiente que permite tornar procedural os
comandos individuais do composto de um editor reunir grupos de frases gravadas sob a forma executadas.
O
SQL. Este ambiente de texto, que permite SQL, que podem ser de arquivo e ento poder e vrias
10
SQL * PLus, alm de manipular comandos SQL blocos PL/SQL, executa outras tarefas (formatao, etc.).
Prof. Dr. Sofiane Labidi
Oracle Navigator
Conjunto de recursos (telas e formulrios) a
11
SQL * PLUS
Pronunciada "sequel plus" um ambiente para execuo dos comandos
SQL e PL/SQL. Ela tem por objetivo realizar a interface interativa com o BD. SQL*Plus permite de realizar operaes como:
Formatar, armazenar, recuperar, e executar comandos SQL e blocos PL/SQL; Listar definies de tabeles e vises; Atribuir valores para a execuo de comandos.
Prof. Dr. Sofiane Labidi 12
Se conectando ao SQL*Plus
Portanto, para que um usurio possa se conectar
ao SQL*Plus, cadastrado.
Os
preciso
que
ele
esteja
usurios padro so criados logo na instalao do Oracle: Scott cuja a senha TIGER, System / Manager, etc. BD (tabelas, Views, etc.).
13
Se conectando ao SQL*Plus
Para se conectar ao SQL*Plus 8i via Windows:
14
Usurio/Senha
PLUS33W
No diretrio C:/ORAWIN95/BIN
15
Se conectando ao SQL*Plus
Ao ser acionado a partir do menu, o editor ativado aps
Caso estivermos trabalhando em uma rede e tentando acessar um BD remoto, preciso informar o Host String: este seria por exemplo o X: mais as informaes da localizao fsica do BD.
Prof. Dr. Sofiane Labidi 16
Log On
SCOTT / TIGER
DEMO / DEMO PO7 / PO7 SYSTEM / MANAGER
SYS / CHANGE_ON_INSTALL
Prof. Dr. Sofiane Labidi 17
Editor Oracle
Uma vez realizada a identificao com sucesso, o
editor liberado para uso mostrando o prompt SQL> a espera de comandos para a execuo.
18
Edio de Comandos
Os comandos so editados aps o prompt: SQL> SCOTT / TIGER e se terminam por um ponto-virgula.
Eles so executados logo aps de digitar
; e
teclar o enter.
O comando fica gravado dentro de um buffer em
um arquivo chamado afiedt.buf at que um novo comando seja digitado ou que o SQL+ seja encerrado.
O buffer s pode contm um comando.
Prof. Dr. Sofiane Labidi 19
Edio de Comandos
Usar o comando R[UN] ou
/ para re-executar o
comando do buffer. A diferena entre os dois que o RUN exibe o comando antes de o executar.
20
Edio de Comandos
Quando o comando comprido, podemos colocar
um hfen (-) no final da linha, apertar o enter, e continuar digitando o comando na linha seguinte.
Uma melhor soluo de usar o I[NPUT] que
permite de acrescentar novas linhas ao mesmo comando no buffer. Para entrar um novo comando usando o Input deve-se primeiro limpar o Buffer usando o comando Clear Buff[er].
Prof. Dr. Sofiane Labidi 21
Edio de Comandos
SQL> Input Select * FROM SQL> Input 1
2
3 4
Select * FROM
Dept Where
Loc Like DALLAS
SQL> /
DEPTNO DNAME LOC ----------- ---------------- ----------20 RESEARCH DALLAS SQL>
22
Edio de Comandos
Para visualizar uma linha especfica n: List n (a a linha n torna-se a linha atual)
Para visualizar um intervalo: List n m
Edio de Comandos
Edio de Comandos
possvel tambm modificar um texto por um outro em uma linha usando o C[HANGE]: C/fro/from A4 C/4 Substirui fro por from Acrescenta 4 no final da linha Tirar o 4 da linha (apenas a primeira ocorrncia)
Prof. Dr. Sofiane Labidi 25
Edio de Comandos
possvel substituir uma linha completa do buffer usando n comando: SQL> 2 where loc like CHICAGO SQL> l 1 Select * From Dept 2 Where loc like CHICAGO SQL>
26
Arquivo de Comandos
(command file ou script)
Para salvar o comando do buffer em um arquivo
usa-se o SAV[E]:
SQL> Clear Buffer buffer cleared SQL> INPUT 1 CREATE TABLE ALUNO 2 (CPD Number(4) , 3 Nome Varchar(30) , 4 Sexo Char(1) , 5 Fone Varchar(10)) 6 SQL> SAVE criar.sql
27
Arquivo de Comandos
(command file ou script)
Geralmente usa-se um editor de comandos para
corrigir e salvar um conjunto de comandos. O Editor acionado usando o comando ED[IT] que por default abri o buffer (arquivo afiedt.buf). o texto digitado tratado pelo wordpad no buffer
Arquivo de Comandos
(command file ou script)
Graas ao comando GET podemos carregar os
29
Arquivo de Comandos
(command file ou script)
Cada comando no script deve terminar por ; Para colocar comentrios, usar:
REMARK (da
C[hange]/old/new substitua a 1a ocorrncia de uma palavra old por new Change /texto deleta a 1a ocorrncia de texto no comando Spool arq arquiva os resultados dos Queries no arquivo arq.lst Spo[ol] off|out encerra ou encerra e imprime o spool Exit ou Quit para sair do SQL * Plus HELP comando traz o arquivo de ajuda
Prof. Dr. Sofiane Labidi 31
* n (ou n) n m last
del (ou del *) del last del n m del * last Clear Screen
Aplicao
SQL> Clear Buffer buffer cleared SQL> INPUT 1 CREATE TABLE ALUNO 2 (CPD Number(4) , 3 Nome Varchar(30) , 4 Sexo Char(1) , 5 Fone Varchar(10)) 6 SQL> SAVE criar.sql
Prof. Dr. Sofiane Labidi 33
Aplicao
Considerando o comando anterior, e usando os
comandos de edio:
Acrescentar os dois campos D_Nascimento e
Endereco tabela aps o campo Sexo.
34
Manipulao
GET criar.sql L 4 4 * Sexo I 5i D_Nascimento Date, Char(1), L 2 2 * CPD C /4/5 L 2 2 * CPD C/, Primary Key, (ou L * Last) Char(1), Varchar(50) , Varchar(10))
Prof. Dr. Sofiane Labidi 35
Number(4),
Number(5),
6i Endereco
7i L 4 7 4 Sexo 6 Endereco 7 * Fone
Varchar(50) ,
5 D_Nascimento Date,
Variveis do SQL
Um comando SQL termina por ; o que implica
sua execuo.
Tal caractere definido na varivel do ambiente
36
O start f far que cf1, cf2, e cf3 sero executados na seqncia. Podemos usar tambm os comandos @ e @@ para startar um command file.
Prof. Dr. Sofiane Labidi 37
@@
Por default um script procurado em c:/Orawin95/Bin O @@
equivalente ao @ e ao Start, s que ele procure o arquivo no diretrio atual: Exemplo: Seja o seguinte script arq no bin e dois scripts teste1 e teste2 na raiz): c:/orawin95/bin/arq: start c:\teste1; @arq ; arq procurado em c:/orawin95/bin @@teste2 ; teste2 ser procurado no diretrio atual, ; que o diretrio do scrpit test1 ou ; seja c:/ e no no c:/orawin95/bin. @teste1 ; neste caso o arquivo teste1 procurado ; em c:/orawin95/bin (o diretrio por ; default) e no vai ser encontrado.
Prof. Dr. Sofiane Labidi 38
Criao de Usurios
User um tipo de objeto Oracle que pode ser criado
Criao de Usurios
No SQL*Plus os usurios so criados pelo
40
Criao de Usurios
Para atribuir direitos:
dba TO Joo; select ON Dept TO Joo; select, Insert ON Dept TO Joo; select any table TO Joo; connect TO Joo; References ON Dept TO Joo;
Para Retirar Direitos: Revoke delete ON Dept FROM joo; Revoke dba FROM joo;
Prof. Dr. Sofiane Labidi 41
Criao de Usurios
Para remover um Usurio:
42
Objeto User
(em SQL)
Objeto User
desc dba_users;
(em SQL)
Name
Null?
Type
USERNAME
USER_ID PASSWORD DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
Tabelas
ndices
Delete
Comandos de pesquisa:
Select
46
, ,
execute desc[ribe] produto para visualizar a estrutura da tabela criada. e Select * from tab para listar as tabelas criadas
47
Consulta
Select From Select From
Where
* User_Objects; * User_Objects
Object_Type = 'TABLE';
48
Inserir Tuplas
insert into produto values(2,'Celular',438.25); select * from produto;
49
Inserir Tuplas
Podemos tambm fazer um insert interativo,
50
Inserir Tuplas
possvel tambm fazer insero a partir de outras
tabelas: insert into Produtos2 Select * From Produtos Where Preco>=100; insert into Pessoas (nome,fone) Select nome,fone From Alunos;
Prof. Dr. Sofiane Labidi 51
Select MINUS
Nome
From
Pessoa
Select
Nome
From
Alunos;
52
Order By
Podemos ordenar uma tabela indicando o nmero da
coluna de ordenao:
SQL> Select * From Scott.Dept Order By 2;
DEPTNO ----------10 40 20 30 DNAME LOC -------------------- -----------------ACCOUNTING NEW YORK OPERATIONS BOSTON RESEARCH DALLAS SALES CHICAGO
Prof. Dr. Sofiane Labidi 53
Varchar(n)
54
CLOB/NCLOB Armazena cadeia longa de caracteres no estruturados como texto, som, imagens, vdeo, etc. podendo chegar at alguns GB.
Prof. Dr. Sofiane Labidi 55
56
57
Tipo Date
Date Data/Hora no formato padro mm-dd-aaaa hh-mm-ss.
Representao em 7 bytes, [4712AC, 4712DC]. A hora por default 12:00:00 e a data por default
58
Tipo Data
Para visualizar a data:
59
Formato da Data
O formato da data especificado no parmetro de
inicializao:
NLS_DATE_FORMAT
(National
ALTER SESSION.
60
Formato da Data
SQL> ALTER SESSION SET
NLS_DATE_FORMAT = 'DD-MONTH-YYYY'; Session altered. SQL> SELECT SYSDATE FROM DUAL; SYSDATE -----------------------08-JUNE -2002
61
Formato da Data
ALTER SESSION Set NLS_DATE_LANGUAGE='FRENCH'; ALTER SESSION Set NLS_DATE_LANGUAGE='PORTUGUESE';
62
Formatao de Datas
DD DY DAY MM MON MONTH YY YYYY RR RRRR HH HH24 MI MIAM SS O dia do ms em dois dgitos: 6, 25. As trs primeiras letras do dia: QUI. O dia da semana em completo: Quinta-feira. O ms em nmero de 1 a 12. O ms em trs letras: JAN, ABR. O ms em inteiro: Janeiro, Abril. O ano em dois dgitos: 02. O Ano em completo: 2002. O ano em dois dgitos levando em conta o milnio. O ano em completo levando em conta o milnio. A hora no formato AM/PM. A hora no formato 24h. Os minutos em dois dgitos. Os minutos em dois dgitos no formato AM/PM. Os segundos em dois dgitos: 59.
Prof. Dr. Sofiane Labidi 63
Criao de Tabelas
CREATE TABLE nome-tabela
(
nome-coluna Tipo [(Tamanho)] [restries] [, .... nome-coluna Tipo [(Tamanho)] [restries] ] [, RESTRIES] )
64
Criao de Tabelas
Preo
)
NUMBER(10,2)
65
Criao de Tabelas
CREATE TABLE CLIENTE ( Cdigo NUMBER(10) Nome VARCHAR2(30) Aniversrio DATE )
, ,
Obs. No h virgula aps a definio do ltimo atributo da tabela (s se tiver restries). insert into cliente values(20,'Carlos','12-10-80'); insert into cliente values(12, 'Roberta', '05/10/65');
Prof. Dr. Sofiane Labidi 66
Criao de Tabelas
Criao a partir da consulta de outras Tabelas:
67
Criao de Tabelas
Create Table PRODUTOS_CAROS
Renomear Tabelas
possvel modificar o nome de ume tabela:
outras tabelas.
69
Restries
As Restries (Constraint) so as regras
Campo Obrigatrio: Campo nico: Chave Primria: Chave Estrangeira: Campos Restritos:
Not Null; Unique (U); Primary Key (P); Foreign Key (R); Chek (C).
70
Campos Obrigatrios
CREATE TABLE PRODUTO ( Cdigo Nome NUMBER(10) VARCHAR2(30) Not Null, Not Null,
Preo
)
NUMBER(5,2)
(
CODIGO NOME DATA ESTATO ESTADO TIPO NUMBER(10) VARCHAR2(30) DATE Not Null, Not Null, Not Null, Not Null, Not Null,
CGC
)
VARCHAR2(14)
Not Null
72
Observao
Os campos (atributos) em uma tabela podem ser
vazios ou nulos (NULL). Usa-se a funo nvl() para retornar valores mais significativos (em lugar de um campo vazio).
Exemplo:
Select Nome, NVL(Nota,0) From Alunos; Update Alunos Set Media = (nvl(Nota1,0) + nvl(Nota2,0)) / 2;
Prof. Dr. Sofiane Labidi 73
Colocar o tipo da restrio aps o campo em questo e antes da vrgula; No final, antes de fechar a parntese do create.
restries nomeadas quando se quer a manipular. Por exemplo uma restrio poderia ser removida, etc. usando o seu nome.
Prof. Dr. Sofiane Labidi 74
Campos nicos
O unique Defina uma ou mais colunas que no
75
Campos nicos
CREATE TABLE PESSOA ( CPF NOME ) VARCHAR2(15) Not Null,
IDENTIDADE NUMBER(10)
UNIQUE,
76
Campos nicos
CREATE TABLE PESSOA ( CPF NOME ) VARCHAR2(15) Not Null, , VARCHAR2(30) Not Null IDENTIDADE NUMBER(10) UNIQUE(Identidade)
77
Campos nicos
CREATE TABLE PESSOA (
CPF VARCHAR2(15) Not Null, Constraint Id_Un UNIQUE, IDENTIDADE NUMBER(10) NOME )
78
Campos nicos
CREATE TABLE PESSOA (
CPF VARCHAR2(15) Not Null, , Not Null, IDENTIDADE NUMBER(10) NOME ) VARCHAR2(30) Id_Un Constraint
UNIQUE(Identidade)
79
Campos nicos
CREATE TABLE PESSOA (
CPF IDENTIDADE NOME )
Alter Table PESSOA Add UNIQUE(Identidade)
80
Campos nicos
CREATE TABLE PESSOA (
CPF IDENTIDADE NOME )
Alter Table PESSOA Add Constraint Id_Um
UNIQUE(Identidade)
81
Chave Primria
Create Table Aluno ( CPD char(6) Primary Key, Nome varchar2(10) , Nota number(2,1) default 0 , Estado varchar(2) default 'MA' )
/* soluo vlida apenas no caso de uma chave mono-atributo */
82
Chave Primria
Create Table Voo ( VooNum char(6) Data Tipo date varchar(2)
, , ,
83
Chave Primria
Create Table Voo ( VooNum char(6) Data Tipo )
A chave aqui composta por dois atributos
, , ,
date varchar(2)
84
Chave Primria
Create Table Aluno ( CPD char(6) Constraint AlPK Primary Key, Nome varchar2(10) , Nota number(2) default 0 , Estado varchar(2) default 'MA' , )
/* soluo vlida apenas no caso de uma chave
mono-atributo */
85
Chave Primria
Create Table Voo ( VooNum char(6) Data Tipo varchar(2)
,
,
varchar2(10) ,
Chave Primria
Alter Table Cliente Add (Primary Key (CodCli)); Alter Table Cliente Add Constraint Cli_PK (Primary Key (CodCli)); Alter Table Aluno Add (Primary Key (Nome,SobreNome));
(Nome,SobreNome));
Prof. Dr. Sofiane Labidi 87
Vinculando Tabelas
Create Table Pilotos ( PilNum number(3) , Nome VarChar2(20) , Salrio number(7,2) , Primary Key (PilNum) ) Create Table Vos ( VooNum char(6) , Data Date , Tipo varchar(2) , Pil number(3) , Primary Key (VooNum,Data) , Foreign Key (Pil) REFERENCES Pilotos)
Prof. Dr. Sofiane Labidi 88
Vinculando Tabelas
Alter Table Alunos Add (Foreign Key (CodCur)
References Cursos); Alter Table Vos Add (Foreign Key (Pil) References Pilotos); Alter Table Vos Add (Constraint C_FK Foreign Key (Pil)
References Pilotos);
89
Observaes
As
chaves
primrias
estrangeiras
so
vinculada j tem uma Primary Key e que temos compatibilidade de tipos. Seno teremos erros de tipo: ORA-02270 e ORA-02256.
90
Vinculando Tabelas
Isto implica a verificao automtica da integridade
referncial.
Precisa prestar ateno correspondncia de tipos
91
Campos Restritos
Create Table ( CPD Nome Nota Alunos
char(6) , varchar2(10) , number(2) default 0 , check (nota >=0 and nota <=10), /* ou check (nota between 0 and 10), */ Estado varchar(2) default 'MA' , check (estado in ('MA','CE', 'PA')) Data_Aniv Date , check (Data_Aniv between
'01-01-01' and '31-12-01')
)
Prof. Dr. Sofiane Labidi 92
Campos Restritos
Create Table Alunos ( CPD char(6) , Nome varchar2(10) , Sexo char , Constraint RestSexo check (Sexo in ('F', 'M')) Nota number(2) default 0 , Estado varchar(2) default 'MA' , Data_Aniv Date ,
Constraint Rest1 check (nota between 0 and 10),
)
Prof. Dr. Sofiane Labidi 93
Campos Restritos
Alter Table Alunos Add (Constraint Nt_Aluno Check (Nota between 0 and 10)); Alter Table Produtos Add (Constraint Preo_Prod
94
Manipulao de Constraints
Removendo Restries:
Alter Table Alunos Drop Constraint NomeRest;
95
Observao
possvel acrescentar restries sobre campos preenchidos desde que os valores preenchidos nesses campos obedecem restrio:
Create table Clientes (CodCli Number(3), NomeCli Varchar(30)); Table created Insert into Clientes Values (1,'Joo'); 1 Row created Insert into Clientes Values (2,'Carmem'); 1 Row created Alter Table Add Primary Key (CodCli) Table Altered
Prof. Dr. Sofiane Labidi 96
Observao
Create table Clientes (CodCli Number(3), NomeCli Varchar(30));
Table created
Insert into Clientes Values (1,'Joo'); 1 Row created
97
Observao
Create table Clientes (CodCli Number(3), NomeCli Varchar(30));
Table created
Insert into Clientes Values (1,'Joo'); 1 Row created
restries
Status
podem
ser
recuperadas
na
tabela
User_Cons_Columns:
(Enabled ou no)
Owner Constraint_Name Table_Name Column_Name Position - seqncia (ordem) do atributo dentro da restrio.
Prof. Dr. Sofiane Labidi 100
Atualizao
Aumenta de 10% o salrio dos funcionrios
Remoo
Deletar o produto nmero 15:
Delete From
Where
Observao:
Produto
CodProd=15;
Delete From
Produto;
103
Ambiente SQL*Plus
Set Set Set Set Set Set Set Set Set LineSize Pagesize pause pause space Feedback Feedback SQLPrompt Echo 100 12 ON "aperta uma tecla para continuar..." 2 ON / OFF 5 SQL> On/OFF
C:\ORAHOME\PLUS33\glogin.sql
Prof. Dr. Sofiane Labidi 104
ndices
Um ndice uma estrutura de Oracle que permite uma
Criao de ndices
Create Index Id_Nome ON Clientes (Nome)
CodCli DESC)
ASC a ordem por default. Um ndice nico um ndice que no permita repities.
Prof. Dr. Sofiane Labidi 106
Remoo de ndices
No possvel alterar um ndice:
107
User_Indexes:
Index_Name
Table_Owner Table_Name
Select Column_Name
From User_Ind_Columns Where Table_Name = NOME;
109
Seqncias
Uma seqncia um objeto Oracle independente,
usado para gerar novos valores numricos sob forma de uma seqncia.
Esses objetos so geralmente usados para construir o
1.
Usa-se as funes nextval e currval para gerao do
112
113
Alterao de Seqncias
Alterar uma Seqncia:
114
Views
Uma viso uma tabela virtual podendo ser
115
AS Select ...
116
Funcionrio2
aqui
uma
viso
da
tabela
117
uma viso.
Observe-se que as operaes sobre as vises so
119
Group By Categoria;
Neste caso, no possvel inserir, etc. por causa do group
by e do alias.
120
Where
Venda.CodVenda=Cliente.CodCli and
Venda.CodPord=Produto.CodProd;
usurio:
View_Name Text_Length Text nome da viso tamanho do comando que originou a viso. o comando select que originou a viso.
122
User_Objects
A tabela User_Objects armazena os objetos de BD do
usurio.
SQL > DESCRIBE USER_OBJECTS ;
Name _______________
OBJECT_NAME
Null? ____
Type ___________
VARCHAR2(28)
OBJECT_ID
OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS
Prof. Dr. Sofiane Labidi
NUMBER
VARCHAR2(13) DATE DATE VARCHAR2(75) VARCHAR2(7)
123
User_Objects
Para apresentar apenas os nomes das tabelas:
User Variables
Podemos trabalhar com variveis em SQL*Plus:
Definio:
define nome [= Carlos]
Verificao do contedo: define nome define nome = "carlos" (char) Listar todas as variveis definidas:
define
Remover uma varivel: undefine nome
Prof. Dr. Sofiane Labidi 125
Substitution variables
Para usar uma varivel em uma query,
precisa
Substitution variables
Quando, em uma consulta, a varivel no foi definida
Substitution variables
SQL> @teste Informe o valor para grupo_de_colunas: cod,nome,preo antigo 1: select & grupo_de_colunas novo 1: select cod, nome, preo Informe o valor para tabs: produto antigo 2: from &tabs novo 2: from produto Informe o valor para conds: cod=10 antigo 3: where &conds novo 3: where cod=10 COD NOME PREO --------- ------------------- --------10 Mouse 20 SQL>
Prof. Dr. Sofiane Labidi 128
Substitution variables
Para concatenar algum texto com a varivel, usa-se
um ponto:
SQL> SELECT * FROM EMP WHERE EMPNO='&X.01'; Informe o valor para x: 123 Ser interpretado como:
129
& e &&
Uma varivel com um & ter seu valor pedido a
cada execuo,
Porm uma varivel com && ser pedido seu valor apenas na primeira vez.
Podemos usar o comando do ambiente:
SET VERIFY OFF para no ficar mostrando a cada vez o antigo e o novo valor j que o valor no muda para os variveis com &&.
Prof. Dr. Sofiane Labidi 130
Defina um novo caractere de substituio. Por default : & ativa ou desativa as substituies.
set Escape car defina o caractere que deve preceder o caractere de substituies quando queremos que este seja interpretado normalmente e no como caractere de substituio, por exemplo /. set escape on/off ativa ou desativa o escapamento. set Concat .
defina o caractere separador de concatenao. Por default : . ativa ou desativa a concatenao.
Prof. Dr. Sofiane Labidi 131
indicando o rank deste parmetro na chamada do start e @ (mas no do Run e /): (aceita at 9 params)
Exemplo: Seja o seguinte script teste:
select * from produto where nome like '&1%' and salrio >= &2; A chamada: @teste M 10 equivalente a: select * from produto
PROMPT e ACCEPT
Observe que todas as variveis criadas com define
so de tipo char. Usa-se o comando ACCEPT para definir variveis de outros tipos de dados (number, date, etc.).
133
Accept e Prompt
Formato:
Accept nome Accept nome Tipo Accept nome Tipo Nome Tipo Texto HIDE
Nome da varivel Tipo da varivel Mensagem a aparecer antes da digitao Oculta o valor digitado (colocando *)
SQL> accept idade prompt 'informa a sua idade: ' informa a sua idade: 28 SQL> define idade DEFINE ID = 28 (NUMBER) SQL>
Prof. Dr. Sofiane Labidi 135
136
Variveis do Sistema
SQL> define _o_release DEFINE _O_RELEASE SQL> define _o_version DEFINE _O_VERSION = "Personal Oracle7 Release 7.3.2.2.0 - Production Release With the distributed and replication options PL/SQL Release 2.3.2.0.0 - Production" (CHAR) = "703020200" (CHAR)
137
Transaes
Uma transao (unidade lgica de tratamento)
Uma
transao
geralmente
composta
por
comandos SQL que alteram vrias tabelas da base como Insert, Update, e Delete.
Por razes de segurana, os dados so alterados em
138
Classes de Transao
H
duas
classes
de
transaes:
DML
(Data
trabalho.
TRANSAES DDL so comandos nicos DDL.
139
140
COMMIT / ROLLBACK
Para que as modificaes teriam efeitos, (sobre a
base) usa-se o comando COMMIT. O Commit encerra a transao (sesso) gravando o contedo pendente (que est no Buffer) na BD.
O
comando ROLLBACK desfaz as aes da transao (at o ltimo COMMIT). O Rollback finaliza a transao ignorando as mudanas efetuadas e armazenadas no Buffer.
141
Observao
Os
comandos
DDL
so
automaticamente
TABLE, DROP TABLE, ETC. Uma sesso DDL composta por apenas um comando SQL.
142
portanto de alterar os registros envolvidos at que o usurio gerador da transao efetue o comando Commit.
Prof. Dr. Sofiane Labidi 143
144
145
Aplicao Parcial
O Rollback pode ser aplicado parcialmente.
nos comandos. Assim, o Rollback pode ser parcial indicando o ponto que delimita o trecho vlido do trecho no desejado.
146
Exemplo
Insert Into Produto Values (10, 'Mouse',20); Insert Into Produto Values (11, 'Caneta',5); SavePoint A; Insert Into Produto Values (12, 'Caderno',8); SavePoint B; Insert Into Produto Values (13, 'Teclado',25); Rollback To SavePoint A; Insert Into Produto Values (14, 'Monitor',450); Commit; Select * From Produto;
/* Apenas os produtos 10, 11, e 14 so inseridos */
Prof. Dr. Sofiane Labidi 147
AutoCommit
Usando a varivel do ambiente autocommit podemos
(ON ou IMM[ediate])
SET AUTOCOMMIT 10
Comita a cada dez operaes
Prof. Dr. Sofiane Labidi 148
Transao ReadOnly
Existe tambm, a possibilidade de se realizar
uma transao apenas para consulta em tabelas, evitando que a mesma seja presa pela transao. Basta colocar o seguinte comando no incio da transao:
SET TRANSACTION READ ONLY
Controle de Concorrncia
Oracle
LOCK
para
controle
de
Controlar o acesso concorrente em um ambiente multiusurios; Preveno de mesmo dado; atualizaes simultneas do
Garantir que a estrutura da tabela no seja alterada enquanto houver atualizaes de dados.
A tabela liberada ao final de cada transao.
Prof. Dr. Sofiane Labidi 150
Criao de Lock
H duas maneiras de criao de Lock:
Lock Implcito
Gerado automaticamente pelo Banco.
Lock Explicito
Criado pelo usurio usando um comando SQL.
151
Criao de Lock
H dois nveis de restrio de Lock:
SHARE
Permite que vrios usurios manipulam a tabela simultaneamente.
EXCUSIVE
A tabela liberada para consulta por todos, mas
apenas um pode fazer atualizaes at liberar o Lock.
Prof. Dr. Sofiane Labidi 152
Gerao de Lock
O Lock explcito pode ser realizado usando:
Lock Table ou
153
Lock Table
LOCK TABLE nome da tabela IN
ROW SHARE / ROW EXCLUSIVE
Lock Table
Row Share
Garantira que nenhum outro usurio bloquear exclusivamente a tabela. Row Exclusive Permite acesso concorrente s tabelas, bloqueando apenas as linhas individuais da tabela e permitindo assim aos outros usurios de acessar os dados para consulta. Share Update o bloqueio de linhas para uma futura atualizao. Os demais podem consultar os dados. Share Os demais s podem fazer consulta na tabela.
Prof. Dr. Sofiane Labidi 155
Lock Table
Share Row Exsclusive Somento um usurio poder
fazer o COMMIT.
Exclusive Apenas o usurio que gerou o bloqueio
processo ser liberado se no for possvel efetuar o bloqueio. Quando esta opo no for utilizada, o O Oracle aguardar at conseguir efetuar o bloqueio.
Prof. Dr. Sofiane Labidi 156
157
SELECT CodCli, NomeCli FROM Clientes WHERE CodCli<10 For Update Of CodCli;
Observao:
Esta opo s suportada por comandos SQL que incluem Distinct, Group By, Union, Intersection, Minus e as funes agregadas.
Prof. Dr. Sofiane Labidi 158
For Update Of
O Banco de Dados permite dois tipos de acesso:
LEITURA (Read Operations - SELECT) GRAVAO (Write Operations - INSERT, UPDATE e DELETE)
podemos
configurar,
podemos
definir
do SQL*Plus.
160
Formatao da Exibio
FEEDBACK
Habilita ou desabilita as mensagem que SQL*Plus retorna logo aps de cada mensagem. Set Feedback ON/OFF Set Feedback 5 (a mensagem mostrada apenas quando as tuplas retornadas na resposta no passam do 5)
SPACE
Permite de definir o nmero de espaos em branco entre as colunas (atributos) de uma seleo.
Set
SPACE 2
Prof. Dr. Sofiane Labidi 161
Formatao da Exibio
LineSize
Por default, o valor padro de uma linha 80. Os chars excedentes vo ser truncados ou apresentados na linha seguinte, dependendo da configurao da varivel WRAP.
Set LineSize 60
PageSize Defina o comprimento de uma pgina: nmero de linhas mostradas antes do ttulo ser exibido uma outra vez (incluindo ttulo, separador, linhas brancas, e tuplas).
Set PageSize 10
Prof. Dr. Sofiane Labidi 162
Formatao da Exibio
WRAP Quando a resposta ( seleo) ultrapassa o tamanho especificado no LineSize, os caracteres restantes aparecero na linha seguinte caso WRAP ON ou trancado caso contrrio. Set WRAP ON -- o resto passa outra linha Set WRAP OF -- tranca (corta o resto) PAUSE
Defina a quantidade de linhas a serem mostradas antes do ttulo seja executado de novo (contando o sublinhado). Set Pause 10 Set Pause "Tecla Enter"
Prof. Dr. Sofiane Labidi 163
Formatao da Exibio
ECHO
til no caso da execuo de um command file para dizer se mostra ou no cada comando executado (alm
do resultados).
Set ECHO ON
SQLPrompt
Permite de alterar o prompt do ambiente SQL*Plus. Set SQLPrompt SQL>; ( o valor por default)
164
Formatao da Exibio
ArraySize
Especifica o nmero de linhas [1,5000] que ser trazido do BD de uma vez. Quanto maior esta varivel, maior a eficincia. Mas preciso para isso mais memria. Set ArraySize 2000
Null
Especifica o String que ser apresentado ao usurio quando o valor de um atributo for nulo. O padro . Set NULL "nada"
Prof. Dr. Sofiane Labidi 165
Formatao da Exibio
BlockTerminator
Especifica qual caractere encerra um bloco PL/SQL (por default o ponto .).
Set Blo[ckTerminator] *;
Formatao da Exibio
ServerOutput
Permite o controle das sadas, usando o comando DBMS_OUTPUT.PUT_LINE de dentro de procedures ou blocos PL/SQL. Se estiver configurado como OFF as sadas no sero mostradas. Set ServerOutput ON/OFF [SIZE n] [FORMAT f]
A opo SIZE especifica o nmero de bytes [2M,1G] (padro 2000) que pode ser buferizado. A opo FORMAT especifica como sero apresentadas as linhas geradas no servidor de sadas (cada linha do servidor de sada comea em uma nova linha):
WRAPPED quebra a linha WORD_WRAPPED a quebra realizada no limite das palavras, com alinhamento esquerda.
167
Formatao da Exibio
Heading
Toda coluna tem um ttulo. Caso a varivel do ambiente Heading OFF, o ttulo no vai aparecer.
Underline c/ON/OFF
Indica que o ttulo vai ser sublinhado ou no, ou indica o caractere de sublinha (por default -).
Exemplo
Set Underline *
Prof. Dr. Sofiane Labidi 168
visualizao:
Ttlulo (Heading);
Formato; (Numricos, Data, etc.) Alinhamento; e Wrapping (poder continuar na linha seguinte).
Os atributos de uma coluna e seus valores podem ser
COLUMN
Column o comando bsico para formatao de relatrios: COL[UMN]
nome da coluna
CLE[AR] FOR[MAT] HEA[DING] JUS[TIFY] LIKE nome da coluna FOLD_AFTER | FOLD_BEFORE NUL[L] valor | Texto PRI[NT] | NOPRI[NT] WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED] ON/OFF
Prof. Dr. Sofiane Labidi 170
Opo do Column
limpa a definio da coluna Formata a coluna (tamanho, etc.) Defina o ttulo da coluna Defina o alinhamento do ttulo Copia (herda) a definio de uma outra coluna. Fold_Afer | Fold_Before inicia uma nova linha Null especifica o valor a ser exibido quando o campo nulo. Print | Noprint imprime ou no a coluna On | Off Habilita ou no a coluna. Uma coluna desabilitada mostra o ttulo e no o contedo. Wrapped Caracteres excedentes na linha seguinte. Word_Wrapped O corte preserva as palavras. Truncated Trunca os caracteres excedentes.
Prof. Dr. Sofiane Labidi 171
Headings
Para modificar o ttulo na visualizao;
COLUMN COD HEADING 'Cdigo do Produto'; COLUMN COD HEADING "Cdigo do Produto "; COLUMN COD HEADING Cdigo do | Produto; O | permite de escreve o heading em vrias linhas. O caractere de quebra | pode ser substitudo por um outro usando HEADSEP: Set HEADSEP car
Podemos modificar o sublinhado dos headings: SET UNDERLINE * ou SET UNDERLINE = SET UNDERLINE '-' ; aqui os apstrofos so obrigatrios
; seno ele interpreta como hfen
Prof. Dr. Sofiane Labidi 172
Formatao de Strings
COLUMN
COLUMN
COD
Nome
FORMAT
FORMAT
A4;
A30;
tamanho especificado para o campo, ento dependendo do valor da varivel do ambiente WRAP o dado pode ser truncado (se WRAP OFF) ou passado linha seguinte (se WRAP ON).
SET WRAP ON/OFF ; por default ON
Mas podemos ter formatao especfica para cada coluna usando: WRAPPED, WORD_WRAPPED, e TRUNCATED.
Prof. Dr. Sofiane Labidi 173
Formatao de Numricos
O formato mnimo por default dos nmeros definido
L: moeda local
174
Formatao de Nmeros
9 9999 exibe um numero na posio 0 0099 exibe um zero se no tiver outro $ $999 Coloca um dlar B B999 exibe valores 0 como branco Mi 9999MI exibe apos um valor negativo S 9999S exibe + apos um valor positivo PR 999PR exibe valor negativo entre <> D 99d99 separa a parte decimal da parte inteira G 99G99 exibe o separador de grupo na posio L L999 exibe o smbolo da moeda local , . 99.99,9 exibe virgula e ponto nas posies V 99V99 multiplica o valor por 10n (valor apos o V) EEEE 9.99EEEE exibe o numero o notao cientifica RN RN exibe o numero em romano (ate 3999) DATE DATE exibe a data no formato MM/DD/YY
Prof. Dr. Sofiane Labidi 175
Formatao de Datas
Para formatar as datas:
Formatao de Datas
SQL> alter session set nls_date_format = 'dd-mmyyyy hh12:mi:ss'; Session altered.
coluna
para
outra
pode
ser
uma
soluo
mesmo tipo:
COLUMN TOT LIKE SAL; mas preciso mudar o heading COLUMN TOT LIKE SAL HEADING Total;
178
Alinhamento
Podemos modificar o tipo da visualizao dos
ttulos:
COLUMN SAL JUSTIFY Center; COLUMN SAL JUSTIFY Left COLUMN SAL JUSTIFY Right;
179
Exemplos
COLUMN NomePil FORMAT A30 HEADING 'NOME
180
Resetar os Atributos
Para mostrar os atributos atuais de uma coluna:
COLUMN nome_coluna
Para mostrar todas as colunas e seus atributos: COLUMN
Para resetar os atributos de uma coluna (voltar
relatrio.
BTitle permite a formatao de um rodap para
o relatrio.
Break permite a criao de quebras no relatrio.
TTitle
Relao dos Alunos
do Curso de Informtica Pgina: 1
CPD -----10 11 12
183
TTitle
Exemplo:
Ttitle CENTER 'Relao dos Alunos' SKIP 1CENTER 'do Curso de Informtica' RIGHT'Pgina: ' FORMAT 99 SQL.PNO SKIP 2 Select CPD, NomeAl
From
Alunos
184
TTitle
Parmetros do Ttitle:
Coluna em que comea a exibio. Alinha a esquerda. Alinha a direita. Centraliza o texto. Salta uma Tabulao. Formato do valor/texto a exibir. Escapa n linhas linhas. Habilita ou no, temporariamente, a exibio do Ttulo.
Prof. Dr. Sofiane Labidi 185
PROMPT e ACCEPT
Exemplo:
SQL> INPUT 1 PROMPT Entre com o Ttulo do Relatrio. 2 ACCEPT MeuTtulo PROMPT 'Ttulo: ' 3 TTITLE 5 SQL> SAVE arq1 arquivo criado arq1 Este significa que o resultado do comando select teria um ttulo especificado pelo usurio.
Prof. Dr. Sofiane Labidi 186
variveis de substituies. Mas este pode ser personalizado combinando o prompt e accept: Exemplo: 1 PROMPT Entre o cdigo do produto 2 PROMPT (entre 100 e 999) 3 ACCEPT cod NUMBER PROMPT 'Cdigo: ' 4 SELECT * 5 FROM Produto 6 WHERE CodProd = &cod; Observe que o comando Accept executado vrias vezes at que o usurio entre com um valor do tipo indicado. Par ler um String, este deve ser entrado entre aspas simples.
Prof. Dr. Sofiane Labidi 187
BTitle
O BTitle defina um ttulo de rodap. Ele tem os
Categria = 'Informtica'
188
Break
Permite criar quebra de linhas baseada em um
BRE[AK] ON [nome coluna] coluna para quebrar SKI[P] n nmero de linhas para saltar SKI[P] PAGE indica o salto de uma pgina NODUP[LICATES] | DUP[LICATES] defina se a coluna de quebra seja ou no repetida.
Prof. Dr. Sofiane Labidi 189
Break
BREAK ON CodCurso SKIP 1 SELECT CPD, NomeAl, CodCur FROM CLIENTES;
CodCur INF
COM
DIR
190
Compute
Permite fazer clculo na quebra aplicando um
operador agregado encima de cada grupo (cada quebra). Funciona junto com o Break. Os dois devem ter o mesmo parmetro ON.
COMP[UTE] funo agregada
Compute
Exemplo:
Break ON CodDept Skip 1 Compute SUM OF Salario ON CodDept Select * From Funcionarios; Acrescentaria uma coluna SUM onde vai ser mostrado a soma dos salrios por departamento (por cada grupo da quebra).
Prof. Dr. Sofiane Labidi 192
Compute
Funes usadas:
AVG COUNT
MIN
MAX SUM
NUMBER
VARIANCE
193
Clear
Limpa o contedo de opes como Break,
Columns, Compute.
Exemplo:
Clear Clear
194
Clear Screen
Para cancelar os formatos anteriores:
Clear Columns
Prof. Dr. Sofiane Labidi 196
Funes Comuns
Dual uma tabela especial do Oracle, usada para
permitir operaes matemticas e outras sobre dados selecionados a partir de uma tabela e/ou nmeros e datas.
A Tabela Dual composta por uma linha e uma
197
15
199
Select To_Char (data[,formato]) From Dual; Select To_Char (nmero[,formato]) From Dual; Select To_Char (dt_nascimento,'"Nascido em: "
200
To_Date('20/01/2001', 'dd-mm-yyyy'),2541)
To_Number(char)
202
203
O concat s aceita dois argumentos. Select Ename || ' trabalha ' || ' como ' || Job From Emp;
Ename || ' trabalha ' || ' como ' || Job -------------------------SMITH trabalha como CLERK ALLEN trabalha como SALESMAN ...
Prof. Dr. Sofiane Labidi 204
Select Lower('carlos albErTo') "Nome Completo" From Dual; Nome Completo ----------------------carlos alberto
Prof. Dr. Sofiane Labidi 206
Select RTRIM(' Carlos Alberto ') "Nome" From Dual; Nome ------------------Carlos Alberto
RTRIM('AABBCC', 'CC') -> AABB LTRIM('AABBCC', 'AA') -> BBCC
Prof. Dr. Sofiane Labidi 208
209
211
USER ----------labidi
212
213
Caso a gratificao nula (no informada), ela ser considerada como zero e a soma vai sempre puder ser feita.
12JOAO
14CARLOS 15ROBERTA 16MARIA 20DJALMA
215
217
Retorna a data de hoje (sistema). Retorna uma nova data adicionando y meses data x. Retorna a data do ltimo dia do ms x. Retorna o nmero de meses entre x e y (x-y). Valor decimal. Retorna a data e tempo correspondente data x na zona de tempo y como deveria ser na zona z. Retorna a data do prximo dia y aps a data x.
219
Next_day(x,y)
221
Diferencia ------------15
Prof. Dr. Sofiane Labidi 222
223
Obs. Por default o horrio no modo 12h. Seno precisa colocar 24 logo colado HH no formato (24).
Prof. Dr. Sofiane Labidi 227
Observao
J que Oracle grava data e tempo juntos. Isto pode
causar
problemas,
principalmente,
quando
to_char() e to_date().
229
Select ename || ' um ' || decode(Job,'ANALYST', 'Analista', 'SALESMAN', 'Vendedor', 'Outro') AS "Em Portugus" From Emp;
Em Portugus ---------------------------------
SMITH um Outro ALLEN um Vendedor WARD um Vendedor JONES um Outro MARTIN um Vendedor CLARK um Outro SCOTT um Analista KING um Outro
Prof. Dr. Sofiane Labidi 230
231
to_char(x) to_number(x)
to_date(x[,y])
to_multi_byte(x) Converte um string x single byte caracteres to_single_byte(x) Converte multibyte caracteres x para single byte caracteres. chartorowid(x) Converte strings para rowid. rowidtochar(x) Converte rowid para strings.
Prof. Dr. Sofiane Labidi 232
HexToRaw(x) RawToHex(x)
Converte hexadecimal (base 16) para raw format (binrio). Converte um valor binrio x para um hexadecimal.
convert(x[,y[,z]]) Executa uma converso de um string alfanumrico dor formato z para o formato y. translate(x,y,z) Converte um string, numrico, ou caracteres x por outro trocando os caracteres de y pelos correspondentes no z.
233
Exemplos
Select to_char(234567.89,'999,999.99') From Dual;
TO_CHAR(234 -------------------234,567.89 Select Translate(To_Char(23456.89, '999,999.99'), '., ', ',. ') From Dual;
TRANSLATE(T
-------------------23.456,89
Prof. Dr. Sofiane Labidi 234
SQL Oracle
BETWEEN .... AND .... Intervalo entre dois valores (inclusive) IN (lista) LIKE string ou string IS NULL NOT nome da coluna = NOT nome da coluna > NOT BETWEEN NOT IN (lista) NOT LIKE string IS NOT NULL trabalho com nmeros, datas, e caracteres. Dentro do intervalo apresentado em (lista) Procura um string igual ao informado Valores Nulos Se a coluna for diferente de Se a coluna no for maior que No estiver no intervalo No estiver na lista fornecida No for igual ao string informado No for nulo
Prof. Dr. Sofiane Labidi 235
SQL Oracle
A and B A or B SOUNDEX A e B devem ser True A ou B devem ser True Funo especial usada para introduzir a Lgica Fuzzy quando se trata de uma comparao de strings permitindo uma comparao baseada sobre a similaridade de pronuncia das palavras.
ou != ou ^=
Interao
Permite ao usurio criar e passar variveis a comandos SQL, imprime variveis e imprime mensagens na tela Diversos Comandos diversos para conectar-se ao banco de dados, manipular o ambiente SQL*Plus e mostrar definies de colunas
Prof. Dr. Sofiane Labidi 238
Oracle Navigator
239
Personal Oracle
Personal Oracle uma verso desktop do SGBD
Oracle.
Ele tem as mesmas funcionalidades que o Oracle
para sistemas de grande porte (mainframe, etc.) s que ele no funciona no modo cliente/servidor.
As aplicaes desenvolvidas com o Personal Oracle
navegar, criar, e modificar os objetos do BD. SQL*Plus uma interface de linguagem de comandos que permite de usar SQL e PL/SQL em um ambiente Windows. SQL*Net - (Oracle network interface) Permite s aplicaes front-end de rodar em uma estao cliente para acessar, modificar, compartilhar, e armazenar dados nos servidores Oracle usando basicamente os protocolos TCP/IP, SPX, e Named Pipes.
Prof. Dr. Sofiane Labidi 241
exportao, e o SQL*Loader. A Exportao copia dados de um BD Oracle para um arquivo do sistema operacional para arquivamento, transferncia, ou restaurao de dados. A Importao leia dados a partir de arquivos exportados para o DBMS Oracle. O SQL*Loader permite de carregar dados a partir de fontes de dados externas (ASCII, etc.) para o Oracle.
Call Interface (OCI) uma API (applications programming
Oracle Navigator
uma ferramenta, do Personal Oracle, permitindo de criar
Oracle Navigator
244
Personal Oracle
O Personal Oracle caracterizado por: Dados de Demo
So os objetos do BD que so accessveis ao se conectar ao BD Local. Tais objetos podem ser copiados e usados em aplicaes especficas. Para se conectar ao BD Local e os acessar clicar duas vezes em Local Data Base.
Projetos
Os projetos permitem de agrupar os objetos de um BD para uma aplicao ou uso especficos. Por exemplo, um projeto Clientes pode agrupar, por copia ou criao, vrios objetos relacionados aos clientes (tabelas, views, snapshops, forms, ponteiros a arquios externos, etc.).
Prof. Dr. Sofiane Labidi 245
Oracle Navigator
Projects
Correspondendo ao n onde so agrupados, para efeito de transferncia (exportao para um Servidor Oracle), todos os objetos de banco de dados.
Local Database
Starter Database
o BD por defaut, ao qual podemos se conectar ao
BD remotos.
247
Startar/Fechar o BD
Por default, o BD aberto na hora do acesso ao BD
BD: Startar o BD ao Startar o Windows; Startar e fechar Manualmente; Habilitando e desabilitando o StartUp Automtico.
248
ao Start DataBase.
3. Voltar pasta de Programas do Windows e Colar a
se conectar no Windows.
249
Startar o BD Manualmente
O BD pode ser iniciado ou parado manualmente:
A partir do menu: Iniciar / Programas / Personal Oracle / Start Database Iniciar / Programas / Personal Oracle / Stop Database
250
oraautos on Para habilitar o startup automtico, e oraautos off Para desabilitar o startup automtico
251
Objetos Oracle
Tabelas Vises ndices Seqncias Usurios / Roles Sinnimos
Snapshots
DB Link Stored Program Units
252
nome nico (max de 30 chars) associado a uma senha (30 chars max). Privileges o direito de acessar objetos e/ou executar comandos SQL. Os privilgios so de dois tipos: system e objects Roles
Os roles so: criados, atribudos direitos, e associados aos usurios. Existe roles por default: Connect, DBA, etc. Account: {User name, Password, Role}
Prof. Dr. Sofiane Labidi 253
Usurios DEFAULT
User Name Password
SCOTT SYSTEM SYS TIGER MANAGER SYS [Windows ID]
Roles
CONNECT, RESOURCE DBA CONNECT, RESOURCE, EXP_FULL_DATABASE, IMP_FULL_DATABASE DBA SYS CHANGE_ON_INSTALL (Para Windows NT) DEMO DEMO CONNECT, RESOURCE PO7 PO7 DBA O Windows ID pode ser obtido usando: Iniciar/Configuraes/Painel de Controle/Sistema
Prof. Dr. Sofiane Labidi 254
Default Roles
Role CONNECT RESOURCE Privilege Acessar o BD. Acessar o BD, criar tabelas, seqncias, procedures, triggers, indexadores, e clusters. Todos os privilgios do sistema, pode atribuir direitos a outros usurios (Grant).
Prof. Dr. Sofiane Labidi 255
DBA
Criao de Projetos
Os objetos do BD podem ser melhor organizados
usando os Projetos. Para criar um novo projeto, clicar em Projects e New a partir do boto direito do mouse. Os objetos do BD podem ser criados diretamente dentro de um Projeto, ou copiados a partir do BD Local. Optando por deletar um objeto de um projeto, o usurio ser perguntado se ele quer deletar o objeto apenas do projeto ou de todo o BD. Os projetos so gravados em arquivos ZPJ.
Prof. Dr. Sofiane Labidi 256
Objeto User
Para criar um usurio e ilhe atribuir privilgios. Por default, um novo usurio tem apenas o role CONNECT.
Objeto User
(em SQL)
Create User Carlos Identified By carlos; cria o usurio Carlos/carlos mas sem nem um privilgio. Grant Select On Scott.Emp To Carlos; O Manager atribui para Carlos o direito de acessar a tabela Emp de Scott. Se o Scott que atribui o direito ento no preciso preceder Emp pelo nome de Scott. Grant Select Any Table To Carlos; Atribuir o direito de acessar todas as tabelas. Grant Connect, Resource To Carlos; Atribui os Roles Connect e Resources ao usurio Carlos, podendo assim se conectar ao BD, acessar e criar tabelas.
Prof. Dr. Sofiane Labidi 258
Objeto User
(em SQL)
259
Objeto User
(em SQL)
Objeto User
desc dba_users;
Name
(em SQL)
Null?
Type
USERNAME
USER_ID PASSWORD DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
Objeto Role
Este objeto, agrega vrios privilgios em um nome s, para que possamos os atribuir, posteriormente, aos usurios. Geralmente, tais objetos definam papeis especficos (predefinidos)
263
Objeto Table
Criao de tabelas, no modo Wizard ou Manual, de forma bastante prtica: temos uma viso de toda estrutura da tabela. Para inserir uma chave externa, colocar no campo Foreign Key: Labidi.Produtos(Cod Prod).
Prof. Dr. Sofiane Labidi 264
Objeto View
265
Objeto Sinnimo
Um sinnimo uma referncia a um objeto Oracle (tabela,
Objeto Sinnimo
267
268
Objeto SnapShot
Um Snapshot uma copia de uma tabela, ou parte
partir do BD Remoto dentro do Snapshot Folder. Deve usar vrios refresh para atualizar o snapshot.
Existe dois tipos de snapshots:
Updatable ReadOnly
269
Objeto DB Link
Um DBLink defina um
caminho entre dois BD: um Local e um Remoto. Uma Database Connection usa um DB Link. Para que o link funciona preciso que o SQL*Net seja instalado nas duas mquinas.
Prof. Dr. Sofiane Labidi 270
Criao de DB Connection
271
Conexo ao BD
Para se conectar ao BD por default usamos o
comando connect:
Disc[onnect]
272
Conexo a um BD Remoto
Um BD Remoto todo BD que no o BD default.
utilizado.
Uma vez conectado, o BD remoto torna-se o BD
SQL*Net.
Para conhecer quem o usurio atual:
Observaes
O Connect aplica um Commit antes de se
banco remoto.
A especificao do BD normalmente um alias
Aplicao
Cria um novo Projeto NossoProjeto e Cria nele: Dois usurios:
Clientes (CodCli, Nome, Bairro, Fone) Pedidos (CodPed, CodCli, Data, Vendedor)
Insira alguns clientes e cinco pedidos. Cria uma viso CliSF que mostra apenas os clientes de So
Francisco.
Cria um novo Role e o associa aos users Carlos e Roberta. Cria uma Sinnimo. Cria uma Conexo.
Prof. Dr. Sofiane Labidi 277
PL/SQL
278
SQL
SQL uma linguagem interativa,
simples,
muito poderosa.
Porm, sua maior limitao de submeter uma linha de cada vez para processamento.
Alguns SGBDs propem a incorporao de SQL
279
PL/SQL
(Procedure Language SQL)
PL/SQL uma linguagem desenvolvida pela Oracle
facilidades
das
linguagens
de
programao:
280
C/S: a linguagem visual dispara as ordens, e os stored procedures, no Banco, fazem o seu trabalho (sem gerar trfego na rede, e sem precisar de algoritmos
complexos).
Prof. Dr. Sofiane Labidi 281
Caractersticas
Um bloco PL/SQL pode ser executado a partir do
SQL*Plus,
PRO*Oracle
Forms,
(PRO*C,
ou
qualquer
linguagem
PRO*Pascal,
PRO*Java,
282
tambm
incorporada
ao
Oracle
283
Caractersticas
Alm de poder executar comandos SQL, um bloco
Vantagens
Portabilidade:
Estrutura da Linguagem
Smbolos da Linguagem:
Todo alfabeto maisculo ou minsculo; Algarismos: 0 a 9; Smbolos especiais (30): ( ) + - * / < > = ! ~ ; : ' @
% , " # $ ^ & _ | { } ? [ ] .
286
Estrutura da Linguagem
Operadores Aritmticos:
**
Operadores Relacionais:
<> =
^= >
!= >=
Operadores Lgicos:
AND
OR
NOT
Prof. Dr. Sofiane Labidi 287
Estrutura da Linguagem
Os outros Smbolos:
:= Atribuio || Concatenao ( ) Delimitador de lista/Expr. ; Separador de instrues . Separador de item ' Delimitador de string -- Comentrio em linha /* e */ Nome_Cidade := 'So Luis' 'Cdigo Cliente: ' || Cliente.Cdigo Estado IN ('MA', 'CE', 'SP') COMMIT WORK; Toda instruo termina por um ; Clientes.Cdigo 'Carlos Otvio' Begin -- Incio da execuo Delimitadores de comentrios abrangendo vrias linhas.
288
Estrutura da Linguagem
Variveis:
Os nomes das variveis devem iniciar sempre com uma letra (AZ), seguida de uma ou mais letras, nmeros ou caracteres (especiais $, # ou _). Devem ter no mximo 30 caracteres. No podem conter espaos em branco. No podem ser palavras reservadas do PL/SQL.
289
Estrutura da Linguagem
Tipos de Dados:
VARCHAR2
NUMBER
DATE BOOLEAN
290
Estruturas de Controle: IF
If Var1 > 10 then Var2 := Var1 + 20 ;
If Var1 > 10 then If Var2 < Var1 then Var2 := Var1 + 20 ; End If ; End If ;
End If ;
Var2 := Var1 + 20 ;
End If ;
If Var1 > 10 then Var2 := Var1 + 20 ; Else Var2 := Var1 * Var1 ; End If ;
291
Var2 := Var1 + 20 ;
ElseIf Var1 between 7 and 8 then
292
.....
Contador : = Contador + 1 ; Exit when Contador > 100 ; End Loop;
293
Loop
...... Contador : = Contador + 1 ;
End Loop ;
294
Estrutura: Labels
Os labels so usados para nomear blocos ou parte deles.
nomear um bloco basta colocar o label imediatamente antes do incio do bloco. Para nomear um comando basta colocar o label imediatamente antes do comando:
<<Nome_label>> declare declaraes begin comandos <<nome_label>> comandos end;
296
Estrutura: Goto
Os labels s teriam utilidades com o uso do comando
297
Estrutura: Goto
Desvio:
Declare ...
Begin
.... GOTO L1; -- o desvio pode ser condicional ou no .... <<L1>> .... End;
Prof. Dr. Sofiane Labidi 298
Estrutura: Goto
Sada do bloco (desvio at o fim do programa):
Declare ... Begin .... GOTO FIM; -- o desvio aqui pode ser condicional ou no
....
<<FIM>> NULL; End;
Prof. Dr. Sofiane Labidi 299
-- comando nulo
Estrutura: Goto
Loop:
declare i number default 0; begin <<volta>> insert into TAB2 values (i,sysdate); i:=i+1; if i<=10 then goto volta; end if; Dbms_Output.Put_Line('Fim no '||i); end;
Prof. Dr. Sofiane Labidi 300
Estrutura: Goto
H situaes em que o GOTO no se aplica:
301
do outro.
302
Estrutura de um Bloco
DECLARE -- rea de declarao -- Declarao de tipos, subtipos, variveis, constantes, -- cursores, etc. BEGIN -- rea de Comandos -- Corpo do Bloco EXCEPTION -- rea de Excees -- Contm as aes (comandos) que sero -- executadas se ocorrer algum erro na execuo -- do Bloco.
END;
Prof. Dr. Sofiane Labidi 303
Exemplo
-- C:/exemplo.sql
DECLARE i int := 0; BEGIN FOR i IN 1..10 LOOP IF MOD(I,3) = 0 THEN INSERT INTO TAB1 VALUES (i,SYSDATE); END IF; END LOOP; END ; SQL> @C:/exemplo.sql (para executar este bloco)
Prof. Dr. Sofiane Labidi 304
Exemplo
DECLARE Preo BEGIN Select Preo Into Preo From Produtos Where CodProd = &Cod; If Preo IS NOT NULL Then Dbms_Output.Put_Line('Custa: ',Preo); End if; END ;
Prof. Dr. Sofiane Labidi 305
Produtos.Preo%TYPE;
Exemplo
DECLARE NM P BEGIN Select NomeProd,Preo INTO NM,P From Produtos Where CodProd = &Cod; If Preo IS NOT NULL Then Dbms_Output.Put_Line('('||NomeProd||','||Preco||')'); End if; END ;
Prof. Dr. Sofiane Labidi 306
Produtos.NomeProd%TYPE; Produtos.Preo%TYPE;
Exemplo
DECLARE QTD_ATUAL NUMBER(5); BEGIN
SELECT FROM
WHERE
QUANTIDADE INVENTRIO
INTO
QTD_ATUAL
PRODUTO = 'MONITOR';
IF
QTD_ATUAL > 0
UPDATE SET WHERE
THEN
INVENTRIO QUANTIDADE = QUANTIDADE - 1 PRODUTO = 'MONITOR';
307
Exemplo
INSERT INTO AVISO_DE_COMPRAS VALUES ('MONITOR VENDIDO', SYSDATE);
COMMIT;
EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO TABELA_DE_ERRO VALUES ('PRODUTO MONITOR NO ENCONTRADO'); END;
Prof. Dr. Sofiane Labidi 308
Variveis;
Constantes; Cursores;
Tabelas; e
Registros (estruturas).
309
Declaraes: Variveis
-- Declarao simples
Nome I
VARHAR(30); Number(10,2);
Nome Casado I
CHAR(25)
DEFAULT
'Joo';
Declaraes: constantes
Para definir constantes:
PI CONSTANT PI CONSTANT
Observao:
311
O conceito de Tipo especifica um conjunto de valores e operaes aplicveis sobre objetos desse tipo.
Noo de Subtipos:
mesmos operadores.
312
TIPOS COMPOSTOS
TIPOS SIMPLES
Record
Table
TIPOS REFERENCIADOS
Ref Cursor
313
Alfanumrico com tamanho mximo de 2000 caracteres que ocupa apenas o que for preciso de memria.
So subtipos de varchar2 utilizados para manter compatibilidade com verses diferentes do SQL. tamanho no pode ser informado. S pode existir um na tabela. No pode ser usado na clausula Where.
Prof. Dr. Sofiane Labidi 314
Varchar e String
Utilizado para armazenar valores de ROW ID selecionados de linhas de tabelas. Este tipo usado quando se quer alterar ou remover linhas de BD pois fornece uma forma rpida de acessar um registro em uma tabela. O formato do RowID BBBBBBBB.RRRR.FFFF (onde FFFF o nmero do arquivo no BD, BBBBBBBB o nmero do bloco no arquivo de BD, e RRRR o nmero da linha no bloco FFFF).
mximo de 2K. Usado geralmente para armazenar imagens. Long Raw Mesma coisa com tamanho mximo de 2G.
Number Numrico com tamanho mximo de 38 caracteres.
316
negativos, entre -2147483647 e 2147483647. Este tipo de varivel requer menos espao do que Number e possui quatro subtipos derivados:
Natural pode armazenar valores entre 0 e 2147483647. NaturalN pode armazenar valores entre 1 e 2147483647. No pode receber valores nulos.
Positive pode armazenar valores entre 1 e 2147483647. PositiveN pode armazenar valores entre 1 e 2147483647. No pode receber valores nulos.
Date Data e hora no formato padro DD-MON-YY. Boolean Armazena os valores True, False, ou Null.
Prof. Dr. Sofiane Labidi 317
318
DECLARE
SUBTYPE SUBTYPE SUBTYPE BEGIN NULL; END;
Prof. Dr. Sofiane Labidi 319
IS IS IS
SUBTYPE NomePessoa
SUBTYPE NomeCidade
IS
Nome%TYPE;
320
Num5
:= 28;
TYPE Nome_Tabela IS TABLE OF Tipo_Elementos index by binary_integer; Observaes: Para usar vetores preciso primeiro criar um tipo deste (i.e. no dar para declarar diretamente uma varivel de tipo vetor).
No caso de vetor de caracteres o tamanho do elemento deve ser especificado. Isto no necessrio no caso de vetores numricos.
O tamanho do vetor no especificado. Os vetores PL/SQL so indexados por binary integer.
Prof. Dr. Sofiane Labidi 322
VetNomes IS TABLE OF VARCHAR2(30) index by binary_integer; Nomes VetNomes; N5 Number(5); Subtype Num5 IS N5%TYPE; Type VetNum1 IS TABLE OF Num5 index by binary_integer; Type VetNum2 IS TABLE OF Number(10,2) index by binary_integer; Num1 VetNum1; Num2 VetNum2; Subtype SubVetNume1 IS VetNum1; begin Nomes(-5):='Sandra'; Nomes(-5):='Mario'; Num1(1):=11.156; Num2(1):=21.235; Dbms_Output.Put_Line(Nomes(-5)||' '||Num1(1)||' '||Num2(1)); -- Mario 11 21.24 end; Prof. Dr. Sofiane Labidi 323
Criao de Subtipos
Criao baseada em um tipo Registro:
tipo}+);
324
end;
/
Prof. Dr. Sofiane Labidi 325
Criao de Subtipos
Criao baseada no tipo de uma Varivel:
SUBTYPE Nome_SubTipo IS
Nome_Varivel%TYPE;
Exemplo:
SUBTYPE
A TipoX;
TipoX
IS
X%TYPE;
326
Criao de Subtipos
Criao baseada no tipo de uma Coluna:
SUBTYPE Nome_SubTipo IS
Exemplo:
Tabela.Coluna%TYPE;
A TpCodProd;
327
Criao de Subtipos
Criao baseada no tipo de registro de um Cursor:
SUBTYPE Nome_SubTipo IS
Exemplo:
Nome_Cursor%TYPE;
328
Criao de Subtipos
Resumindo, um subtipo pode receber uma das
seguintes definies:
Nome_Tipo Nome_Tabela%ROWTYPE; Nome_Coluna%TYPE; Nome_Vetor%TYPE;
Nome_Registro%TYPE;
Nome_campo%TYPE; Nome_Cursor%ROWTYPE; Nome_Varivel_Cursor%ROWTYPE.
329
manutenes
resultando
das
alteraes
nas
330
331
Hierarquia de Variveis
As variveis declaradas dentro de um bloco tm um
Hierarquia de Variveis
declare NP Produtos.NomeProd%TYPE; Pr Produtos.Preco%TYPE; begin -- esto disponveis aqui as variveis NP e Pr declare NP Varchar(40); X Number(10); begin -- esto disponveis aqui as variveis NP e X end -- esto disponveis aqui as variveis NP e Pr end; /
Prof. Dr. Sofiane Labidi 335
Variveis do Host
As variveis declaradas no HOST (ambiente que
solicita a execuo dos blocos de comando PL/SQL)
podem
ser
referenciadas
dentro
do
bloco,
336
bem como as funes (de manipulao de strings, datas, nmeros e funes genricas) podem ser usadas dentro de um bloco PL/SQL.
O comando SELECT deve ter necessariamente a clausula
INTO pois o resultado no pode ser visualizado imediatamente como no SQL*Plus mas se armazenado em variveis ou estruturas.
Alm disso o SELECT deve ser elaborado de forma tal que
retorna uma e somente uma tupla seno teremos erros de tipo no_data_found ou too_many_rows.
Prof. Dr. Sofiane Labidi 337
338
Procedimentos e Funes
So agrupamentos de comandos PL/SQL em
procedure no retorna.
Funes
podem
ter
339
Stored Procedures
um grupo de comandos SQL e PL/SQL que
Stored Procedure
Aplicativo ...
NomeProd(); ...
Aplicativo
END
...
NomeProd(); ...
Aplicativo
...
NomeProd(); ...
Prof. Dr. Sofiane Labidi
BD
341
Especificao, e o Corpo.
O corpo pode ser definido em PL/SQL ou em
342
Stored Procedure
Create or Replace Procedure Aumento IS Begin Update Pilotos Set Sal = Sal * 1.2; End Aumento;
O nome do procedure (aumento) colocado depois do End opcional.
O ; obrigatrio.
343
realizada:
Diretamente no Terminal SQL*Plus; Em um Edtior de Texto; ou No DB Navigator.
344
6 /
Warning: Procedure created with compilation errors.
Prof. Dr. Sofiane Labidi 345
Depurando Erros
SQL> show errors Errors for PROCEDURE Aumento:
LINE/COL ERROR -------- ----------------------------------------------------------------2/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: ( ; is with as compress compiled wrapped The symbol "is" was substituted for "BEGIN" to continue.
346
4 set
6 /
5 end Aumento;
349
se coloca parntesis.
Prof. Dr. Sofiane Labidi 350
Begin
Delete From Livro Where codliv=parmcodliv; End RemLiv; SQL> @c:\arq2.sql -- compila o procedure SQL> exec RemLiv(15) ; -- executa o procedure -- deletando o livro 15
Prof. Dr. Sofiane Labidi 351
Criao no Navigator
352
Update Livro
Set Where End AltLiv; Livro.preco=parmpreco Livro.CodLiv=parmcodliv;
Observao
355
356
Removendo Procedures
358
Funes
Create or Replace Function MaiorPreco RETURN Number IS valor Number(10,2); Begin Select Max(preco) Into From Livro; Return valor; End MaiorPreco;
Prof. Dr. Sofiane Labidi 359
valor
A partir da classe de funes Dual: SQL> Select MaiorPreco from Dual; maiorpreco --------------150 Seno, usando o exec que retorna um valor em uma varivel que precisamos declarar: SQL> var mp number SQL> exec :mp := maiorpreco SQL> print mp mp --------150
Prof. Dr. Sofiane Labidi 360
Create or Replace Function PrecoLiv (parmcodliv IN livro.codliv%TYPE) RETURN Number IS valor Number(10,2); Begin Select preco Into valor From Livro Where Livro.CodLiv=parmcodliv; Return valor; End PrecoLiv;
Prof. Dr. Sofiane Labidi 361
Clusula OUT
Usa-se a clusula OUT para as variveis de
362
precoliv
Begin
OUT livro.preco%TYPE) IS
Select preco Into precoliv From Livro Where Livro.CodLiv=parmcodliv; End PrecoLiv;
Prof. Dr. Sofiane Labidi 363
PR --------450
Prof. Dr. Sofiane Labidi 364
Grant Execute On Proc1 To Scott; Atribui o direito de execuo do procedure Proc1 para o usurio Scott. Grant Execute On Proc1 To Public; Atribui o direito de execuo do procedure Proc1 para todos.
365
366
Triggers
Um Trigger (gatilho), ou Gatilho, uma Stored
Procedure que se executa automaticamente a cada vez que uma tabela (ou viso) modificada.
Os Triggers constituem uma das maneiras mais
Controle das modificaes em uma tabela (criao de mecanismos de validao); Clculo automtico de um atributo de uma tabela (derivada de outras); Reforar algumas regras de negcios; Implementar regras de segurana; Manter um bom espelhamento (replicao); Criar logs registrando a utilizao da tabela.
Prof. Dr. Sofiane Labidi 368
Triggers
BD
Aplicativos
INSERT INTO T ...
...
369
Triggers
Os triggers so definidos para serem executados
tabela,
nas
verses
subseqentes,
no
limitao.
370
Definio de Triggers
Um Trigger tem trs partes:
Triggering event: Insert, Update, ou Delete. Triggering constraint: Defina as condies para controlar a execuo do trigger. Triggering action: Os comandos PL/SQL que definam a ao (corpo) do trigger.
Prof. Dr. Sofiane Labidi 371
Observaes
Um trigger pode, em seu corpo, acionar um
stored procedure.
Quando uma tarefa poderia ser implementada
tabela
base
so
372
Observaes
Um trigger no pode executar os comandos
COMMIT, ROLLBAK, ou SAVE POINTS e nem chamar funes ou precedure que usam esses
comandos.
O comando Select s pode ser usado junto com
a clusula INTO.
O texto do trigger tem um limite de tamanho de
32 KB.
Prof. Dr. Sofiane Labidi 373
Tipos de Triggers
8i possui quatro tipos de triggers:
1. Triggers de Tabelas (triggers normais) aplicados aos comandos do tipo DML. 2. INSTEAD-OF aplicados em vises. 3. SYSTEM_EVENT aplicados no BD nas operaes de startup , shutdown, e erro. 4. USER EVENT aplicados a um usurio ou
objeto de esquema.
Prof. Dr. Sofiane Labidi 374
Exemplo
Podemos definir um trigger que segura que o
375
Exemplo
Create or Replace TRIGGER TrigInsAltLiv Before Insert OR Update On Livro For Each Row Begin if :New.preco < 40 then
:New.preco := 40;
end if; End TrigInsAltLiv;
Prof. Dr. Sofiane Labidi 376
Or Replace
O uso da opo OR REPLACE permite de
recriar um trigger j existente: a definio de um trigger pode ser alterada sem a necessidade de elimin-lo.
377
Statment-Level Trigger:
Executado apenas uma vez. Por exemplo a cada atualizao de 10 tuplas o trigger ser executado uma vez e no em cada tupla processada.
Row-Level Trigger
O trigger executado para toda tupla afetada pelo comando (evento) que gera o acionamento do trigger.
Prof. Dr. Sofiane Labidi 378
tupla e a tupla anterior. Estas, podem ser renomeados dentro do trigger, por exemplo para Novo e Anterior.
Operao INSERT Valor OLD NULL Valor NEW valor inserido
UPDATE
DELETE
380
Criao no Navigator
381
When
O When defina uma condio necessria para
execuo do trigger.
Exemplo:
Create or Replace TRIGGER TrigInsAltLiv Before Insert OR Update On Livro For Each Row When (New.CodLiv > 1) Begin ..... End TrigInsAltLiv;
Prof. Dr. Sofiane Labidi 382
Eventos
Before Insert Before Insert For Each Row After Insert After Insert For Each Row Before Update Before Update For Each Row After Update After Update For Each Row
Before Delete Before Delete For Each Row After Delete After Delete For Each Row
Os trs eventos so:
INSERT, UPDATE, e DELETE. A ao do trigger pode ser realizada antes (BEFORE) ou depois (AFTER) do evento. A ao pode ser realizada apenas uma vez, ou a cada novo registro inserido, apagado, ou modificado (FOR EACH ROW)
383
um livro para que este no seja maior a 10% do valor inicial do livro.
Create or Replace TRIGGER TrigAumentPreco Before Update On Livro For Each Row Begin if :Novo.Preco > :Anterior.Preco then if :Novo.Preco - :Anterior.Preco > :Anterior.Preco * 0.1 then :Novo.Preco := :Anterior.Preco; end if; end if; End TrigAumentPreco;
Prof. Dr. Sofiane Labidi 384
Criao no Navigator
385
386
Create or Replace TRIGGER TotalVenda Before Update Or Insert On Venda For Each Row DECLARE p Produto.Preco%Type; BEGIN Select Preco into p From Produto Where Produto.CodProd=:Novo.CodProd; :Novo.Total:=p*:Novo.Quantidade; END;
Prof. Dr. Sofiane Labidi 387
Ativar/Desativar Triggers
Podemos desabilitar/habilitar triggers:
uma tabela especfica: alter table Produto disable all triggers; alter table Produto enable all triggers;
388
disponveis
na
viso
DBA_TRIGGERS
no
DBA.
H uma outra viso que armazena os triggers
de usurios USER_TRIGGERS.
389
391
Pacotes
Um Pacote um objeto do banco de dados que
Stored
Procedure,
funes,
variveis,
constantes, e cursores.
Esses, podem ser executados como se fazem parte
de uma biblioteca:
NomeDoPacote.NomeDaFuno NomeDoPacote.NomeDoProcedure
Prof. Dr. Sofiane Labidi 392
Pacotes
Este recurso muito interessante pois:
componentes de um sistema;
Facilita a administrao de privilgios; Aumenta o desempenho pois transfere para memria vrios objetos de uma vez.
393
Estrutura de um Pacote
Um pacote tem duas partes: A especificao (ou interface):
(procedimentos
qualquer ambiente Oracle, enquanto um construtor privado pode ser referenciado apenas dentro do seu package.
Prof. Dr. Sofiane Labidi 394
Exemplo
funes locais.
395
Exemplo
Create or Replace PACKAGE NOSSOPACOTE IS Procedure AltLiv (parmcodliv NUMBER, parmpreco NUMBER);
396
Exemplo
Create or Replace PACKAGE BODY NOSSOPACOTE IS Procedure AltLiv (parmcodliv Number, parmpreco Number) IS Begin Update Livro Set Livro.preco=parmpreco Where Livro.Codigo_do_Livro=parmcodliv; End AltLiv; Function MaiorPreco RETURN Number IS valor Number(10,2); Begin Select Max(preco) Into valor From Livro; Return valor; End MaiorPreco; End;
Prof. Dr. Sofiane Labidi 397
Invocar o Pacote
SQL> exec NOSSOPACOTE.AltLiv(10,30)
-- altera para 30 o preo do livro 10
Recompilar um Pacakge
aconselhado recompilar um pacote para ter um
399
Recompilar um Pacakge
Para recompilar o corpo de um pacote:
Package altered
400
Excluir um Pacakge
Esta operao perigosa pois quando um package
401
Observao
Portanto, o package pode ser substitudo e
referncias
ao
pacote
no
precisam
ser
402
Pacotes Predefinidos
Oracle
403
Pacotes Predefinidos
DBMS_OUTPUT um exemplo de pacotes predefinidos que atravs da Procedure PUT_LINE, envia as mensagens aps uma Transao efetuada com sucesso.
404
Pacotes Predefinidos
PUT e PUT_LINE permitem de colocar uma
linha no buffer.
NEW_LINE termina a linha criada com PUT. GET_LINE e GET LINES Recupera a
405
Pacotes Predefinidos
Exemplo de uso do package DBMS_OUTPUT, para
imprimir uma mensagem a cada operao sobre uma tabela: Create or replace trigger BefIns before insert on livro for each row begin DBMS_OUTPUT.PUT_LINE('Antes da insero'); end;
Create or replace trigger AftIns after insert on livro for each row begin DBMS_OUTPUT.PUT_LINE('Depois da insero'); end;
Prof. Dr. Sofiane Labidi 406
Cursores
Para processar um comando SQL, o Oracle
abre um zona especial de memria chamada Private SQL Area. Esta rea armazena informaes sobre o comando que est sendo executado.
Um cursor um identificador desta rea.
407
comandos (DDL, DML) que retornam apenas uma linha (uma tupla).
Declarados implicitamente por qualquer comando DML e pelos processamentos de query (SELECT). juntamente com seus identificadores, em blocos PL/SQL e manipulados atravs de comandos especficos, durante a execuo de um bloco de comandos. Esses somente em queries CURSORES so utilizados processadas pelo query.
Prof. Dr. Sofiane Labidi 409
Atributos de Cursores
Sempre que um bloco de comandos SQL
Atributos de Cursores
SQL%FOUND
Fornece o valor booleano: TRUE se pelo menos uma linha foi lida pelo comando, e FALSE se nenhuma linha foi lida.
Fornece o valor booleano: TRUE se pelo menos uma linha foi
SQL%NOTFOUND
de
linhas
411
412
Observaes
Assim como os cursores explcitos, as variveis
linha
seja
retornada.
Portanto,
preciso
413
Num_Linhas_Removidas := SQL%ROWCOUNT;
INSERT INTO HISTORICO_DE_REMOCAO VALUES ('ALUNOS', Num_Linhas_Removida, SYSDATE); END;
Prof. Dr. Sofiane Labidi 414
Clausula TYPE
Existe uma forma simples de fazer com que variveis
que venham ser associadas a colunas de tabelas, assumam as mesmas caractersticas dessas colunas. Isto , atravs da clusula %TYPE:
DECLARE V_DEPTNUM V_LOC BEGIN SELECT FROM DEPT DEPT.DEPTNUM%TYPE; DEPT.LOC%TYPE; DEPTNUM, LOC INTO V_DEPTNO, V_LOC
416
Clausula ROWTYPE
Podemos definir uma varivel que ira assumir
em uma varivel.
417
EMP
WHERE
END
EMPNUM = 2845;
418
Observao
O conjunto de linhas retornadas por um cursor
chamado result set. A linha que est sendo processada em um dado momento chamada current row. O resultado permite de identificar a linha atual dentro do result set e de permitir um processamento individual de cada uma. O cursor pode ser visto como um arquivo temporrio que armazena e controla as linhas retornadas por um comando Select.
Prof. Dr. Sofiane Labidi 419
Declarao de um Cursor
Formato:
Cursor Nome [(parametros)] [RETURN Tipo]
IS
Comando-SQL;
421
422
423
Abertura de um Cursor
a operao que executa a consulta e cria o
primeira (1).
Exemplo: OPEN Mostrar(8);
424
atribuindo cada atributo da linha (tupla) a uma varivel (um pouco parecido com o comando Slecet Into).
Aps cada Fetch, o Cursor avana para prxima linha do result set.
425
Exemplo de Acesso
Loop Fetch C1 Into Cpd1, Nome1, Fone1; Exit When C1%NotFound; DBMS_OUTPUT.PUT_LINE(To_Char(Cpd1)|| ' ' || Nom1 || ' ' || Fone1); End Loop;
426
Fechando um Cursor
Quando um cursor no estiver sendo executado, ele deve
ser fechado para liberar os recursos que o Oracle aloca para ele: CLOSE C;
Esta operao realizado automaticamente quando o
427
FETCH deve ser igual ao nmero de campos definidos na declarao SELECT quando da declarao do cursor.
O sucesso ou insucesso (retorna ou no valores) do
cursor determinado pelo teste %FOUND% ou %NOTFOUND%. Este teste deve ser feito antes que o cursor seja fechado.
429
Select Clientes.Nome, Sum(Itens.Quantidade * Itens.Precovenda) as Total From Clientes,Itens,Pedido Where Clientes.Codigo = Pedido.Codcliente and Pedido.Numero = Itens.Numeroped and Pedido.Dataped >= Parmdata Group By Clientes.Nome Having Total >= Parmvalor Order By Clientes.Nome;
Prof. Dr. Sofiane Labidi 430
BEGIN Delete Vendascli; Open CursorVendasCli; If SQL%Notfound Then Raise_Application_Error(-20000,'No H Vendas Neste Periodo.'); End If; Loop Fetch Cursorvendascli Into Varnome,Vartotal; Exit When Cursorvendascli%Notfound; Insert Into Vendascli Values (Varnome, Vartotal); End Loop; Close Cursorvendascli; END;
Prof. Dr. Sofiane Labidi 431
Excees
NO_DATA_FOUND TOO_MANY_ROWS Nenhuma linha satisfaz os critrios. Detecta a existncia de mais de uma linha.
DUP_VAL_ON_INDEX Detecta uma tentativa de criar uma entrada em um ndice cujos valores de coluna chave j existem VALUE_ERROR O campo de destino no suficientemente grande para conter o valor que est sendo colocado.
432
Soluo
FUNCTION FUNC1 (parmcodprod produto.codprod%type) RETURN NUMBER IS valor produto.preco%type; BEGIN select preco into valor from produto where codprod=parmcodprod; return valor; EXCEPTION when no_data_found then rollback work; return 0; when others then rollback work; return 0; END; SQL> exec :v:=func1(999); -- no daria erro
Prof. Dr. Sofiane Labidi 434
Exemplo 2
FUNCTION DIVIDIR (parm1 number,
Exemplo 2
FUNCTION DIVIDIR (parm1 number, parm2 number) RETURN number IS res number; BEGIN res:=parm1/parm2; return res; EXCEPTION when zero_divide then return 0; END; SQL> exec :v:=dividir(10,2); -- retorna 5 SQL> exec :v:=dividir(10,0); -- retorna 0
Prof. Dr. Sofiane Labidi 436
Excees
ACCESS_INTO_NULL
CURSOR_ALREADY_OPEN INVALID_CURSOS DUP_VAL_ON_INDEX INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND
NOT_LOGGED_ON
PROGRAM_ERROR ROWTYPE_MISMATCH
Excees
STORAGE_ERROR falta espao memria
TIMEOUT_ON_RESOURCES tempo limite ultrapassado TOO_MANY_ROWS solicitao retornou mais e uma linha valor maior que a cap. de armazenamento da varivel diviso por zero
VALUE_ERROR
ZERO_DIVIDE
438
Mensagens de Erros
Create or Replace TRIGGER TrigInsAltLiv Before Insert OR Update On Livro For Each Row Begin if to_char(sysdate, 'DY') in ('SAB', 'DOM') then raise_application_error(-20000,' Volte segunda!'); elsif :New.preco < 40 then :New.preco := 40; end if; End TrigInsAltLiv;
Prof. Dr. Sofiane Labidi 439
PL/SQL: Concluso
Aceita entrada de comandos ad hoc Aceita entrada de comandos SQL e PL/SQL
diretamente de arquivos-texto. Pode editar os comandos SQL com o editor de linhas Controla os ajustes do ambiente Formata o resultado de queries em relatrios bsicos Interage com usurio final Acessa bancos de dados remotos
Prof. Dr. Sofiane Labidi 440
Obrigado!
441