Banco de Dados II
Capítulo 1: SQL-PSM
UFCG/DSC
Bacharelado em Ciência da Computação
Cláudio Baptista, Ph.D.
Ex.: Escreva um programa Pascal que leia a matricula de um empregado e imprima
as informações sobre este empregado.
program Imprima;
var loop: char;
matricula: integer;
E: record of
nome: string[15];
endereco: string[30];
funcao: string[10];
salario: real; end;
begin
loop := ‘S’;
while (loop = ‘S’) do
begin
writeln(‘Entre com a matricula:’);
readln(matricula);
$ select nome, endereço, função, salario
into :E.nome, E.endereco, :E.funcao, :E.salario
where matricula = :matricula;
writeln(E.nome, :E.endereco, E.funcao, E.salario);
writeln(‘Deseja ler mais empregados(S/N)?’);
readln(loop);
end;
end.
Cursor

No programa anterior uma única tupla é
selecionada pelo SQL embutido. Geralmente,
uma query resulta em várias tuplas.

Problema: SQL processa um conjunto de tuplas,
enquanto que C e Pascal (ou outra linguagem
host) processa um registro por vez.
Solução: Introduziu-se o conceito de cursor para
permitir processar uma tupla por vez nas
linguagens hospedeiras.

Cursores

Problema: “Impedance Mismatch”




SQL trabalha com relações
Linguagens de programação trabalham orientada a
registro
Como fazer para ler os dados de um conjunto
retornado pelo SQL numa linguagem de
programação?
Precisamos ter um mecanismos para associar os
valores retornados pelo SGBD em variáveis da
aplicação. Faz-se isso usando variáveis hospedeiras.
Cursor




Um cursor pode ser visto como um ponteiro que aponta
para uma única tupla(linha) do resultado da query.
Cada cursor possui uma busca associada, especificada
como parte da operação que define o cursor.
A pesquisa é executada quando o cursor for aberto.
Numa mesma transação, um cursor pode ser aberto ou
fechado qualquer número de vezes. Pode-se ter vários
cursores abertos ao mesmo tempo.
Cursor


Sintaxe da especificação de um cursor:
EXEC SQL DECLARE nome-cursor
CURSOR FOR cláusula-select
Um cursor possui as seguintes operações:
-
-
-
OPEN: executa a query especificada e pões o cursos
para apontar para uma posição anterior a primeira tupla
do resultado da consulta
FETCH: move o cursor para apontar para próxima linha
no resultado da consulta. Tornando-a a tupla corrente e
copiando todos os valores dos atributos para as
variáveis da linguagem hospedeira usada.
CLOSE: fecha o cursor.
Cursor
-
UPDATE … CURRENT OF: realiza a atualização dos
atributos da tupla que está sendo apontada pelo cursor
(linha corrente). Sintaxe:



-
UPDATE tabela
SET lista de atribuições
WHERE CURRENT OF cursor
DELETE ... CURRENT OF: elimina a tupla que está
sendo apontada pelo cursor. Sintaxe:



DELETE
FROM tabela
WHERE CURRENT OF cursor
Um Exemplo em C:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
char titulo[101];
char ano[5];
EXEC SQL DECLARE filme_cursor CURSOR FOR
SELECT titulo
FROM filmes
WHERE ano = :ano;
void main () {
EXEC SQL WHENEVER SQLERROR GOTO erro;
strcpy(ano,”1998”);
EXEC SQL OPEN filme_cursor;
while (strcmp(SQLSTATE, “02000”) != 0) {
EXEC SQL FETCH filme_cursor
INTO :titulo;
printf(“%s\n”, titulo);
};
EXEC SQL CLOSE filme_cursor;
return;
erro:
printf(“Um Erro ocorreu!\n”);
};
Exemplo usando Delete e Update
// Se empregado ganha mais de 10000 é demitido; senão tem seu
// salário reduzido em 20%
void reducaodeFolhadePagamento() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
float salario;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE salCursor CURSOR FOR
SELECT salario FROM Empregado ;
EXEC SQL OPEN salCursor;
while(1) {
EXEC SQL FETCH FROM salCursor
INTO :salario;
// Verifica se não há mais tuplas
if (strcmp(SQLSTATE, “02000”)) break;
if (salario > 10000)
EXEC SQL DELETE FROM CLIENTE
WHERE CURRENT OF salCursor;
else EXEC SQL UPDATE CLIENTE
SET salario = salario - salario * 0.2;
WHERE CURRENT OF salCursor;
}
EXEC SQL CLOSE salCursor;
}
Cursor

Scrolling cursors




cursores movem-se por default do inicio do result
set para frente (forward)
podemos, entretanto, movê-los também para trás
e/ou para qualquer posição no result set,
para tanto, devemos acrescentar SCROLL na
definição do cursor
EX. EXEC DECLARE meuCursor SCROLL
CURSOR FOR Empregado;
Cursor

Scrolling cursors

Num FETCH, podemos adicionar as seguintes
opções:




NEXT ou PRIOR: pega o próximo ou anterior
FIRST ou LAST: obtém o primeiro ou último
RELATIVE seguido de um inteiro: indica quantas tuplas
mover para frente (se positivo) ou para trás (se
negativo)
ABSOLUTE seguido de um inteiro: indica a posição da
tupla contando do início (se positivo) ou do final (se
negativo)
Integração Estreita com SGBDs

O uso de SQL/PSM (Stored Procedures)
tais como PL/SQL, SQLJ, TransactSQL,
… , são extensões da SQL

Processadas no lado servidor da arquitetura
cliente - servidor

Isto é muito bom para o desempenho
Stored Procedures

É um conjunto de comandos SQL definidos pelo
usuário que ficam armazenados num BD como
um procedimento/função, para eventuais
processamentos.

São processamentos de tarefas da aplicação
que residem no SGBD ao invés de no código
da aplicação (cliente).
Stored Procedures

Vantagens:

1.Desempenho

Ex.: Seja a consulta
SELECT codigop, nome, COUNT(*)
FROM Projeto p, Alocacao a
WHERE p.codproj = a.codigop
GROUP BY p.codproj, p.nome
Stored Procedures



Se vários usuários realizarem esta consulta o
tráfego de rede será alto.
se criarmos uma stored procedure para executar
esta consulta, os usuários necessitarão apenas de
um comando para executar a consulta anterior:
EXEC nomeProcedimento;
Outro ponto é a compilação, a consulta anterior
seria compilada a cada chamada, enquanto o
procedimento contendo a consulta seria compilado
uma única vez
Stored Procedures - Vantagens

2. Facilita o gerenciamento do BD, pois a
consulta é escrita em um único lugar,
portanto a manutenção desta torna-se mais
eficaz e segura.
Stored Procedures - Vantagens

3. Segurança: podemos usar stored
procedures para limitar o acesso de alguns
usuários ao BD. Desta forma, a maneira em
que o BD pode ser modificado é estritamente
definida.
Stored Procedures: SQL/PSM



SQL/PSM - Persistent Stored Modules: parte
do padrão SQL relativo às Stored Procedures
No momento cada SGBD oferece sua própria
linguagem (Oracle PL/SQL, Microsoft
Transact/SQL, etc)
Em PSM, definimos módulos que são
coleções de definições de funções ou
procedimentos, declarações de tabelas
temporárias, dentre outros.
Stored Procedures -SQL/PSM

Criando Funções e Procedimentos


CREATE PROCEDURE <NOME>
(<parâmetros>)
declarações locais
corpo do procedimento;
CREATE FUNCTION <NOME> RETURNS
<tipo>
declarações locais
corpo da função;


obs.: parâmetros são do tipo modo-nome-tipo
(onde modo indica IN, OUT ou INOUT)
Parâmetros em funções devem ter modo IN
Stored Procedures -SQL/PSM
Exemplo:
CREATE PROCEDURE MudaEndereco (
IN endAntigo VARCHAR(255),
IN endNovo VARCHAR(255)
)
UPDATE Empregado
SET endereco = endNovo
WHERE endereco = endAntigo;
Stored Procedures -SQL/PSM
Alguns Comandos:
1) Chamada a um procedimento:
CALL <nome procedure> (<lista argumentos>);
Obs.: CALL é aplicado apenas a Procedures (não a Function)
Esta chamada pode ser realizada de vários lugares:
- Programa com SQL embutido
EXEC SQL CALL calcula(:x, 3);
- Como comando em outro procedimento ou função PSM:
CALL calcula (10);
2) Comando de Retorno (usado apenas em funções)
RETURN <expressão>; (OBS este comando não encerra
a função)
Stored Procedures -SQL/PSM
3) Declaração de variáveis locais:
DECLARE <nome> <tipo>;
4) Comando de atribuição
SET <variável> = <expressão>;
5) Grupo de comandos:
delimitados por BEGIN e END
6) Labels: colocamos labels em comandos precedendo estes
pelo nome do label e dois pontos.
7) Comandos condicionais
8) Laços
IF <condição> THEN
LOOP
<comandos>
<Comandos>
ELSEIF <condição> THEN
END LOOP;
<comandos>
…
ELSE <comandos>
END IF;
Stored Procedures -SQL/PSM
Exemplo: Função sobre o esquema Filmes que recebe um ano
e nome de estúdio e retorna TRUE se aquele estúdio produziu
apenas um filme preto e branco naquele ano ou nada produziu.
CREATE FUNCTION PretoeBranco( a int, studio char[15])
RETURNS BOOLEAN
IF not exists (
select * from Filme where ano = a and
nomeStudio = studio)
THEN RETURN TRUE; -- não faz a função retornar agora
ELSEIF 1 <=
(select count(*) from Filme where ano = a and
nomeStudio = nome and NOT emcores)
THEN RETURN TRUE;
ELSE RETURN FALSE;
END IF;
Stored Procedures -SQL/PSM
Exemplo: Procedimento que calcula a média e variância de um
estúdio
CREATE PROCEDURE MeanVar ( IN s char[15],
OUT mean REAL, OUT variance REAL)
DECLARE NotFound FOR SQLSTATE ‘02000’;
DECLARE filmeCursor CURSOR FOR
select duracao from Filme where nomeStudio = s;
DECLARE novaDuracao INTEGER;
DECLARE contaFilmes INTEGER;
BEGIN
SET mean = 0.0;
SET variance = 0.0;
SET contaFilmes = 0;
OPEN filmeCursor;
filmeLOOP: LOOP
FETCH filmeCursor INTO novaDuracao;
IF NotFound THEN LEAVE filmeCurdor END IF;
SET contaFilmes = contaFilmes + 1;
SET mean = mean + novaDuracao;
SET variance = variance + novaDuracao * novaDuracao;
END LOOP;
SET mean = mean / contaFilmes;
SET variance = variance/contaFilmes - mean * mean;
CLOSE filmeCursor;
END;
Stored Procedures -SQL/PSM
- For-Loops
usado para fazer iterator num cursor
FOR <nome laço> AS <nome cursor> CURSOR FOR
<query>
DO
<comandos>
END FOR;
Veja exemplo no próximo slide!
- WHILE <condição> DO
<comandos>
END WHILE;
- REPEAT
<comandos>
UNTIL <condição>
END REPEAT;
Stored Procedures -SQL/PSM
Exemplo: Mesmo procedimento de média e variância de estúdios,
usando FOR-Loops
CREATE PROCEDURE MeanVar ( IN s char[15],
OUT mean REAL, OUT variance REAL)
DECLARE contaFilmes INTEGER;
BEGIN
SET mean = 0.0;
SET variance = 0.0;
SET contaFilmes = 0;
FOR filmeLOOP AS filmeCursor CURSOR FOR
select duracao from Filme where nomeStudio = s;
DO
SET contaFilmes = contaFilmes + 1;
SET mean = mean + novaDuracao;
SET variance = variance + novaDuracao * novaDuracao;
END FOR;
SET mean = mean / contaFilmes;
SET variance = variance/contaFilmes - mean * mean;
END;
OBS.Veja que não é necessário OPEN, FETCH e CLOSE do cursor
Stored Procedures -SQL/PSM
Exceções em PSM:
É possível testar o SQLSTATE para verificar a ocorrência
de erros e tomar uma decisão, quando erros ocorram
Isto é feito através do EXCEPTION HANDLER que é
associado a blocos BEGIN END (o handler aparece dentro do bloco)
Os componentes do handler são:
1) Lista de exceções a serem tratadas
2) Código a ser executado quando exceção ocorrer
3) Indicação para onde ir depois que o handler concluir
SINTAXE: DECLARE <onde ir> HANDLER FOR <condições>
<comando>
As escolhas de <onde ir> são:
- CONTINUE
- EXIT (sai do bloco BEGIN .. END)
- UNDO
Stored Procedures -SQL/PSM
Exemplo de exceções em PSM:
CREATE FUNCTION getSalario (mat integer) RETURNS FLOAT
DECLARE NotFound CONDITION FOR SQLSTATE ‘02000’;
DECLARE TooMany CONDITION FOR SQLSTATE ‘21000’;
BEGIN
DECLARE EXIT HANDLER FOR NotFound, TooMany
RETURN NULL;
RETURN ( select salario
from Empregado where
where matricula = mat);
END;
PL-SQL : Oracle Stored Procedures




Linguagem de desenvolvimento do Oracle que implementa
SQL/PSM
Permite variáveis locais, laços, procedures, consulta a relações
“one tuple at a time”.
Forma geral:
DECLARE
declarações
BEGIN
comandos executáveis;
EXCEPTION
Comandos para manipular erros (optativo)
END;
A parte DECLARE é opcional.
Oracle Stored Procedures
Código PL/SQL é feito de blocos com uma única
estrutura
Existem dois tipos de blocos em PL/SQL:


1.
Anonymous Blocks: não possuem nomes (como
scripts)


Podem ser escritas e executadas imediatamento no
iSQLPLUS
Podem ser usadas num trigger
2. Named Blocks:


Procedures
Functions
Blocos Anônimos
DECLARE
(optional)
/* aqui se declaram as variáveis que serão usadas no
bloco */
BEGIN
(mandatory)
/* define-se os comandos que dizem o que o bloco faz*/
EXCEPTION
(optional)
/* define-se as ações que acontecem se uma exceção
for lançado durante a execução deste bloco */
END;
/
(mandatory)
DECLARE
Sintaxe
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
Exemplos
Note que PL/SQL inclui
todos tipos SQL, e mais…
Declare
birthday
age
name
magic
valid
DATE;
NUMBER(2) NOT NULL := 27;
VARCHAR2(13) := 'Levi';
CONSTANT NUMBER := 77;
BOOLEAN NOT NULL := TRUE;
Declarando Variáveis com %TYPE
Exemplos
DECLARE
sname
fav_boat
my_fav_boat
...
Acessando coluna sname
na tabela Sailors
Sailors.sname%TYPE;
VARCHAR2(30);
fav_boat%TYPE := 'Pinta';
Acessando outra
variável
Criando um PL/SQL Record
Um record é um tipo de variável que podemos
definir (como ‘struct’ em C ou ‘object’ em Java)
DECLARE
TYPE sailor_record_type IS RECORD
(sname
VARCHAR2(10),
sid
VARCHAR2(9),
age
NUMBER(3),
rating
NUMBER(3));
sailor_record
sailor_record_type;
...
BEGIN
Sailor_record.sname:=‘peter’;
Sailor_record.age:=45;
…
Procedures
Objetos armazenados no BD, que usam
comandos PL/SQL em seus corpos.
Declarações de Procedure
CREATE OR REPLACE PROCEDURE
<nome>(<lista_argumentos>) AS
<declarações>
BEGIN
<comandos PL/SQL>
END;
PL-SQL : Oracle Stored Procedures

<Lista_argumentos> tem triplas nomemodo-tipo.



Modo: IN, OUT, ou IN OUT para read-only,
write-only, read/write, respectivamente.
Tipos: padrão SQL + tipos genéricos como
NUMBER = qualquer tipo inteiro ou real.
Desde que tipos nas procedures devem casar
com tipos no esquema do BD, pode-se usar
uma expressão da forma
relação.atributo %TYPE
para capturar o tipo corretamente.
Oracle: Exemplo
Uma procedure que inclui uma nova cerveja e seu preço no menu do
bar RubroNegro.
Vende(bar, cerveja, preço)

CREATE PROCEDURE MenuRubroNegro(
c IN Vende.cerveja %TYPE,
p IN Vende.preço %TYPE
) AS
BEGIN
INSERT INTO Vende
VALUES(`RubroNegro´´, c, p);
END;
.
run;
Note “run” somente armazena a procedure, não a executando.
Oracle: Invocando Procedures
Uma chamada a uma procedure pode
aparecer no corpo de um comando
PL/SQL.
 Exemplo:
BEGIN
MenuRubroNegro('Bud', 2,50);
MenuRubroNegro(‘Carlsberg', 5,00);
END;
ORACLE PSM
Atribuição de valores a variáveis é denotada por :=.
Desvio

IF <condição> THEN
<comando(s)>
ELSE
<comando(s)>
END IF;
Em `ninhos´ de IFs, use ELSIF em lugar de ELSE IF.
Laço
LOOP
...
EXIT WHEN <condição>
...
END LOOP;
Oracle: Consultas em PL/SQL


Single-row selects permitem atribuir a
uma variável o resultado de uma
consulta que produz uma única tupla.
Cursors permitem a recuperação de
muitas tuplas, com o cursor e um laço
sendo usados para processar tupla-atupla.
Single-Row Select


Um select-from-where em PL/SQL deve ter uma cláusula INTO
listando as variáveis que recebem os resultados
da consulta.
Ocorre erro se o select-from-where retorna mais de uma tupla;
neste caso, é preciso usar um cursor.
Exemplo

Encontrar o preço da cerveja Schincariol no bar Tricolor.
Vende(bar, cerveja, preço)
DECLARE
p Vende.preço %TYPE;
BEGIN
SELECT preço
INTO p
FROM Vende
WHERE bar = `Tricolor´ AND cerveja =
`Schincariol´;
END;
Cursores
Declarados por:
CURSOR <nome> IS
comando select-from-where

O cursor aponta para cada tupla por vez da relação-resultado da
consulta select-from-where, usando um fetch statement dentro de
um laço.

Fetch statement:
FETCH <nome_cursor> INTO
lista_variáveis;

Um laço é interrompido por:
EXIT WHEN <nome_cursor> %NOTFOUND;


O valor é Verdade se não houver mais tupla a apontar.
OPEN e CLOSE abrem e fecham um cursor,
respectivamente.
Criando um Cursor

Examplo:
DECLARE
cursor c is select * from sailors;
sailorData sailors%ROWTYPE;
BEGIN
open c;
fetch c into sailorData;
sailorData é
uma variável
que pode
receber uma
tupla da tabela
sailors
Exemplo
RAD_VALS
radius
Rad_cursor
f
e
t
c
h
3
6
8
Rad_val
AREAS
Radius
Area
3
28.27
DECLARE
Pi constant NUMBER(8,7) := 3.1415926;
area NUMBER(14,2);
cursor rad_cursor is select * from RAD_VALS;
rad_value rad_cursor%ROWTYPE;
BEGIN
open rad_cursor;
fetch rad_cursor into rad_val;
area:=pi*power(rad_val.radius,2);
insert into AREAS values (rad_val.radius, area);
close rad_cursor;
END;
/
Explicit Cursor Attributes
Obtém informação de status sobre um
cursor.
Atributo
Tipo
Descrição
%ISOPEN
Boolean
Retorna TRUE is o cursor
is open.
%NOTFOUND
Boolean
Retorna TRUE se o fetch mais
recente não retorna uma tupla
%FOUND
Boolean
Retorna TRUE se o fetch mais
recente retorna uma tupla
complemento de %NOTFOUND
%ROWCOUNT
Number
Retorna o total de tuplas
recuperadas.
Exemplo
Uma procedure que examina o menu do bar
Tricolor e aumenta em 1,00 todos os
preços que são menores que 3,00.
Vende(bar, cerveja, preço)

Um simples UPDATE podia resolver o
problema, mas mudanças mais
complicadas podiam exigir uma
procedure.
CREATE PROCEDURE Aumento() AS
aCerveja Vende.cerveja%TYPE;
oPreço Vende.preço%TYPE;
CURSOR c IS
SELECT cerveja, preço
FROM Vende
WHERE bar =`Tricolor´;
BEGIN
OPEN c;
LOOP
FETCH c INTO aCerveja, oPreço;
EXIT WHEN c%NOTFOUND;
IF oPreço < 3.00 THEN
UDPATE Vende
SET preço = oPreço + 1.00
WHERE bar = `Tricolor´
AND cerveja = aCerveja;
END IF;
END LOOP;
CLOSE c;
END;
Tipo ROWTYPE
Qualquer coisa (i.e., cursores, nomes de
tabela) que tem um tipo tupla pode ter seu
tipo capturado com %ROWTYPE.


Pode-se criar variáveis temporárias tipo
tupla e acessar seus componentes como
variável.componente (“dot notation”).
Muito útil, principalmente se a tupla tem
muitos componentes.
Declarando Variáveis com
%ROWTYPE
Declare uma variável com o tipo de uma
Acessando
linha de uma tabela.
tabela
Reserves
reserves_record
Reserves%ROWTYPE;
E como acessar oa campos de
reserves_record?
reserves_record.sid:=9;
Reserves_record.bid:=877;
Exemplo
A mesma procedure com a variável tupla cp.
CREATE PROCEDURE Aumento() AS
CURSOR c IS
SELECT cerveja, preço
FROM Vende
WHERE bar = `Tricolor´;
cp c
%ROWTYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO cp;
EXIT WHEN c %NOTFOUND;
IF cp.preço < 3.00 THEN
UDPATE Vende
SET preço= cp.preço + 1.00
WHERE bar = `Tricolor´
AND cerveja = cp.cerveja;
END IF;
END LOOP;
CLOSE c;
END;
Definição de Function
- Podemos definir uma função:
CREATE FUNCTION <func_name>(<param_list>)
RETURN <return_type> AS ...
No corpo da função, "RETURN <expression>;"
sai (retorna) da função e retorna o valor da
<expression>.
- Para descobrir quais procedures e functions você já criou:
select object_type, object_name
from user_objects
where object_type = 'PROCEDURE'
or object_type = 'FUNCTION';
Removendo Procedures/Functions
Para remover uma stored procedure/function:
drop procedure <procedure_name>;
drop function <function_name>;
Outras formas de Laços

Comando For

permite que uma determinada sequencia de
comandos seja executada n vezes
FOR contador IN [REVERSE] valorInicial .. valorFinal
LOOP
sequência de comandos
END LOOP
Outras formas de Laços

Comando For - Exemplo
Create procedure VerificaEstoque
Declare
codInicial Produto.codProduto%Type;
codFinal CodInicial%Type;
estoque Produto.estoque%Type;
Begin
select Min(CodProduto), Max(codProduto)
into codInicial, codFinal
from Produto
for c in codInicial..codFinal
loop
select estoque into estoque
from produto
where codProd = c and estoque>0
Dbms_Output.Put_Line(‘O codigo ’|| c || ‘ tem em estoque’);
end loop;
End
Outras formas de Laços

Comando while
Sintaxe:
WHILE condição LOOP
Comandos
END LOOP;
Loops: WHILE Loop
DECLARE
TEN number:=10;
i
number_table.num%TYPE:=1;
BEGIN
WHILE i <= TEN LOOP
INSERT INTO number_table
VALUES(i);
i := i + 1;
END LOOP;
END;
Manipulando Exceções



Exceções são todos os erros e imprevistos
que podem ocorrer durante a execução de
um bloco PL/SQL
Quando uma exceção ocorre o Oracle aborta
a execução e procura a área de exceções
(EXCEPTION) para tratar a falha.
As exceções podem ser


Pré-definidas
Definidas pelo usuário
Manipulando Exceções
Sintaxe:
EXCEPTION
WHEN
nomeExceção 1
Comandos
WHEN
nomeExceção2
Comandos
Exemplo
THEN
THEN
begin
insert into Pais values (100, ‘Brasil’);
Commit;
Dbms_Output.Put_Line(‘Inserção realizada com sucesso’);
Exception
when Dup_Val_On_Index Then
Dbms_Output.Put_Line(‘País já cadastrado!’);
when Others then
Dbms_Output.Put_Line(‘Erro ao cadastrar país’);
end;
Execeções Pré-definidas













Cursor_Already_Open
DUP_Val_On_INDEX
INVALID_CURSOR
Invalid_Number
Login_Denied
No_Data_Found
Not_Logged_On
RowType_Mismatch
Storage_Error
Too_Many_Rows
Value_Error
Zero_Divide
Others
Exemplo de Exceção
Declare
Aux_X number(1);
Subtype TipoX is Aux_X%Type;
-- Limitado entre -9 e 9
x TipoX;
y TipoX;
Begin
x := 10;
Exception
when value_error then
Dbms_Output.Put_Line(‘Valor fora do limite’);
End;
Exceção definida pelo Usuário
Devem ser declaradas na área de declarações de um bloco ou
procedure/function ou package
Comando:
Declare
nomeExceção EXCEPTION;
Begin
Sequencia de comandos
If … then
RAISE nomeExceção;
End If;
Comandos
Exception
When NomeExceção then Comandos
End
Exemplo: suponha a seguinte tabela:
create table mylog(
who varchar2(30),
logon_num number
);

Queremos armazenar
quantas vezes alguém se
loga ao BD
mylog
who
logon_num
Peter
3
John
4
Moshe
2
Solução
DECLARE
cnt NUMBER;
BEGIN
select count(*)
into cnt
from mylog
where who = user;
if cnt > 0 then
update mylog
set logon_num = logon_num + 1
where who = user;
else
insert into mylog values(user, 1);
end if;
commit;
end;
/
Solução (2)
BEGIN
update mylog
set logon_num = logon_num + 1
where who = user;
if SQL%ROWCOUNT = 0 then
insert into mylog values(user, 1);
end if;
commit;
END;
/
Exemplo- o que faz a SP abaixo?
Table mylog
who
logon_
num
Pete
3
John
4
Joe
2
create or replace procedure
num_logged
(person IN mylog.who%TYPE,
num OUT mylog.logon_num%TYPE)
IS
BEGIN
select logon_num
into num
from mylog
where who = person;
END;
/
Chamando uma Procedure
declare
howmany mylog.logon_num%TYPE;
begin
num_logged(‘John',howmany);
dbms_output.put_line(howmany);
end;
/
Erros numa Procedure/Function


Quando se cria uma procedure, se houver erros
na sua definição, estes não serão mostrados
Para ver os erros de procedure chamada
myProcedure, digite:
SHOW ERRORS PROCEDURE myProcedure

no iSQLPLUS prompt
Para funções, digitre:
SHOW ERRORS FUNCTION myFunction
Exemplo de Function
create or replace function
rating_message(rating IN NUMBER)
return VARCHAR2
NOTE que não
AS
especifica o tamanho
BEGIN
IF rating > 7 THEN
return 'You are great';
ELSIF rating >= 5 THEN
return 'Not bad';
ELSE
return 'Pretty bad';
END IF;
END;
/
Chamando uma function
declare
paulRate:=9;
Begin
dbms_output.put_line(ratingMessage(paulRate));
end;
/
Exemplos de SP em SQL Server
1. CREATE PROCEDURE DBO.MostraEmpregadosDep
@nomeDep varchar(50) = ‘Pessoal’ - - Dep Default
AS
SELECT e.mat, e.nome, e.endereco, e.salario
FROM Empregados e, Departamento d
WHERE d.nomeD = @nomeDep
Uma chamada a este procedimento seria:
USE Empresa -- BB Empresa
EXEC MostraEmpregadosDep ‘Informatica’
Java Stored Procedure no Oracle
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
public class BookDML {
public static void insertBook (String title, String publisher)
throws SQLException {
String sql = “INSERT INTO Livros VALUES (?, ?)”;
try {
Connection con =
DriverManager.getConnection(“jdbc:default:connection:”);
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, title);
pstmt.setString(2, publisher);
pstmt.close();
} catch (SQLException e)
{system.err.println(e.getMessage()); }
Java Stored Procedure no Oracle
Carregando a Classe no Banco de dados:
> loadjava –u baptista BookDML.java
Acessando a classe:
create or replace procedure InsertBookJava
(title varchar(), publisher varchar)
As
Language java
Name ‘BookDML.insertBook(java.lang.String, java.lang.String)’;
Executando do SQLPlus:
CALL insertBookJava(´Meulivro´, ´LMV´);