Banco de Dados I
Capítulo 6: Uso de SQL em
Aplicações
UFPB/DSC
Bacharelado em Ciência da
Computação
Cláudio Baptista
SQL Embutido
– Até então vinhamos usando o SQL
interativo, mas como fazer para usarmos
SQL numa aplicação?
– Porquê SQL não é uma linguagem
completa?
– Várias formas de conexão a BD: SQL
Estático, SQL Dinâmico e Call Level
Interface (SQL/CLI, ODBC/JDBC)
– Nesta seção estudaremos SQL embutido
em linguagem hospedeira.
- SQL + Linguagem Hospedeira (Pascal, C,
Cobol, Java …)
– Geralmente, diferentes sistemas seguem
diferentes convenções para embutir
comandos SQL
SQL Embutido
Linguagem Hospedeira (C, Pascal, Fortran,
Cobol, Ada, SQLJ (java)) + SQL Embutido
Pré-processador
Linguagem hospedeira +
Chamadas de funções
Compilador
Linguagem
Hospedeira
Código objeto
SQL Embutido
• 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.
– Obs.: Java tem um tratamento diferenciado
pelo fato de se tirar proveito de suas
características Orientada a Objetos Existe
um padrão para isto SQL/OLB (Object
Language Bindings)
SQL Embutido
• Tratamento de exceções
• é realizado pelo comando WHENEVER que
permite indicar as ações que devem ser
realizadas quando um erro ocorrer.
• Isto evita de fazer verificação de exceção
para cada comando SQL produzido
• Pode-se usar o SQLSTATE,
SQLEXCEPTION, SQLWARNING, NOT
FOUND (02000) dentre outros.
• SQLSTATE é um array de 5 caracteres,
cada vez que um comando SQL é
executado o SGBD retorna no SQLSTATE
informações sobre erros, através de
códigos (SQL-89 chamava de SQLCODE)
• Ex.: Código ‘00000’ => não houve erro
• Código ‘02000’=> tupla não encontrada
SQL Embutido
• A seção DECLARE
– Qualquer declaração SQL (por
exemplo a definição de variáveis
host) é feita entre os comandos:
• EXEC SQL BEGIN DECLARE SECTION
• EXEC SQL END DECLARE SECTION
• Ex.: Em C
EXEC SQL BEGIN DECLARE SECTION;
char nomeStudio[50],
endStudio[256];
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
SQL Embutido
• Variáveis host:
– são compartilhadas entre a aplicação
e o BD
– são definidas na aplicação e
precedidas por dois_pontos (:)
• o comando EXEC SQL
– Quando queremos invocar um
comando SQL de dentro de uma
aplicação simplesmente usamos o
comando EXEC SQL ou outra
diretiva(por exemplo $ ou #)
SQL Embutido
Exemplo de cadastro de cliente em C:
void cadastraCliente() {
EXEC SQL BEGIN DECLARE SECTION;
char nome[50], endereco[256];
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
printf(“Entre o nome e endereço\n”);
gets(nome);
gets(endereco);
insert into Cliente (nome, endereco);
values (:nome, :endereco);
}
SQL Embutido
Exemplo de consulta que retorna uma única tupla:
void printCliente() {
EXEC SQL BEGIN DECLARE SECTION;
char nome[25], endereco[256];
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
printf(“Entre com o nome do cliente\n”);
gets(nome);
EXEC SQL
select nome, endereco
into :nome, :endereco
from Cliente
where nome = :nome
if (strcmp(SQLSTATE, “00000”))
printf (“Nome = %s\tendereco = %s\n”, nome, endereco);
else printf (“ERRO no ACESSO AO BD = %s”, SQLSTATE);
}
Ex.: Escreva um programa Pascal que leia a matricula de um empregado e imprima as
informações sobre este empregado.
program Imprima;
var loop: boolean;
matricula: integer;
E: record of
nome: string[15];
endereco: string[30];
funcao: string[10];
salario: real;
end;
begin
loop := true;
while (loop = true) 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.
SQL Embutido
– 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.
SQL Embutido
– Um cursor pode ser visto como um
ponteiro que aponta para uma única
tupla(linha) do resultado da query.
– Cada cursor possui uma pesquisa
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.
SQL Embutido
– 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.
SQL Embutido
- 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 curso
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 se nã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;
}
SQL Embutido
• 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;
SQL Embutido
• 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)
SQL Embutido
– Exemplo em SQLJ:
• #sql { CREATE TABLE EMPREGADO ( matricula
int not null, nome varchar(30), Primary
key(matricula) )};
SQL Dinâmico
Motivação:
• SQL em uma linguagem hospedeira é
bom em aplicações estáticas, p.e., um
programa de reserva de passagens
aéreas.
• Não serve para escrever um programa
como sqlplus, porque não se sabe de
antemão qual o código que segue um
sinal de prontidão SQL>.
• Para resolver, precisamos de dois
comandos:
– PREPARE converte uma cadeia de caracteres
em um comando SQL.
– EXECUTE executa o comando.
Exemplo: Versão Simplificada do Sqlplus
EXEC SQL BEGIN DECLARE SECTION;
char query[MAX_QUERY_LENGTH];
EXEC SQL END DECLARE SECTION;
/* issue SQL> prompt */
/* read user's text into array query */
EXEC SQL PREPARE q FROM :query;
EXEC SQL EXECUTE q;
/* go back to reissue prompt */
• Uma vez preparada, uma consulta pode ser
executada muitas vezes.
– “Prepare” = otimiza a consulta, i.e., encontra um
meio de executá-la com um mínimo de I/O’s.
• Alternativamente, PREPARE e EXECUTE podem ser
combinadas em:
EXEC SQL EXECUTE IMMEDIATE :query;
Desvantagens da Abordagem
'Linguagens Hospedeira'
• Nem é C e nem é SQL, nem é Java e
nem é SQL, …
• O programa é executado no lado
cliente da arquitetura clienteservidor
– Problema de desempenho
Interfaces “Call-Level” (SQL/CLI)
Nesta abordagem, C (ou outra
linguagem) cria comandos SQL
como cadeias de caracteres
passados como argumentos de
funções que são parte de uma
biblioteca.
• SQL/CLI (ODBC = open database
connectivity) e JDBC (Java
database connectivity).
• Grande vantagem em relação ao
enfoque Linguagem Hospedeira: o
programa continua C puro
SQL-CLI
• Em C, necessita-se do include sqlcli.h
• O programa pode então usar 4 tipos de estruturas:
– 1. Environment: prepara para conexão
– 2. Connections: conecta a aplicação ao
SGBD
– 3. Statements: contém os comandos a serem
processados
– 4. Descriptions: metadados
SQL-CLI
•Em C, a biblioteca permite que você crie um statement
handle = estrutura em que você coloca um comando SQL.
Os handles são:
1. SQLHENV: para setar environment
2. SQLHDBC: para conexão
3. SQLHSTMT: para statements
4. SQLHDESC: para descriptions
SQL-CLI
Exemplo:
#include <sqlcli.h>
SQLHENV ambiente;
SQLHDBC conexao;
SQLHSTMT comando;
SQLHRETURN erro1, erro2, erro3;
erro1 = SQLAllocHandle(SQL_HANDLE_ENV,
SQL_NULL_HANDLE, &ambiente);
if (!erro1) {
erro2 = SQLAllocHandle(SQL_HANDLE_DBC,
ambiente, &conexao);
if(!erro2)
erro3 = SQLAllocHandle(SQL_HANDLE_SMT,
conexao, &comando);
}
SQL-CLI
• Use SQLPrepare(myHandle, <statement>,length) para
fazer myHandle representar o comando SQL no segundo
argumento.
– Length é o tamanho do string. Pode ser usado SQL_NTS
(Null Terminating String) que o próprio SQLPrepare
descobre automaticamente.
• Use SQLExecute(myHandle) para executar o comando.
Exemplo
SQLPrepare(comando, "SELECT nome, salario
FROM Empregado
WHERE depto = 10", SQL_NTS);
SQLExecute(comando);
Podemos combinar estes dois comandos num único:
SQLExecDirect(comando, "SELECT nome, salario
FROM Empregado WHERE depto = 10", SQL_NTS);
Recuperando Dados
Para obter os dados retornados por uma consulta, usa-se:
•
Variáveis de ligação (“bind”) para os componentes das
tuplas retornadas.
–
•
SQLBindCol aplica-se a um handle, o número da coluna, e a
variável de ligação, mais outros argumentos.
Comando “Fetch”, usando o handle.
–
SQLFetch aplica-se a um handle.
Exemplo
SQLBindCol(handle1, 1, SQL_CHAR, &coluna1, size(coluna1),
&col1Info)
SQLBindCol(handle1, 2, SQL_REAL, &coluna2, size(coluna2),
&col2Info)
SQLExecute(handle1);
...
while(SQLFetch(handle1) != SQL_NO_DATA) {
...
}
SQL-CLI – Passando
Parâmetros para Queries
• Use:
– 1. SQLPrepare(Comando)
– 2. SQLBindParameter()
– 3. SQLExecute()
Exemplo:
SQLPrepare(comando, “insert into projeto(codigo, nome)
values (?,?)”, SQL_NTS);
SQLBindParameter(comando, 1,..., codProj, ...);
SQLBindParameter(comando, 2, ..., nomeProj, ...)~;
SQLExecute(comando);
ODBC
• ODBC tem as mesmas idéias de
SQL/CLI, entretando com
pequenas modificações
• A seguir veremos um exemplo de
ODBC
int ODBCexample() {
RETCODE error;
HENV env; /* environment */
HDBC conn; /* database connection */
SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, “buchada.dsc.ufcg.edu.br”, SQL_NTS,“baptista”, “password”, SQL_N
{
char banco[80];
float saldo;
int lenOut1, lenOut2;
HSTMT stmt;
SQLAllocStmt(conn, &stmt);
char * query = “select banco, sum(saldo) from contas
group by banco”
error = SQLExecDirect(stmt, query, SQL_NTS);
if (error == SQL_SUCCESS) {
SQLBindCol(stmt, 1, SQL_C_CHAR, banco, 80, &lenOut1);
SQLBindCol(stmt, 2, SQL_C_FLOAT, &saldo, 0, &lenOut2);
while (SQLFetch(stmt) == SQL_SUCCESS) {
printf(“%s %g\n”, banco, saldo);
}
}
}
SQLFreeStmt(stmt, SQL_DROP);
SQLDisconnect(conn);
SQLFreeConnect(conn);
SQLFreeEnv(env); }
JDBC
• JDBC é uma Call-level Interface que
permite acesso externo a banco de
dados SQL
• Difere de SQL Embutido, pois possui
uma API que é chamada na própria
linguagem de progração para
acessar o BD
• Implementa o modelo clienteservidor
JDBC Estabelecendo uma
Conexão
• 1) Precisa-se carregar um driver JDBC
para o SGBD que se está usando.
• No Oracle, isto pode ser feito da
seguinte forma:
• Class.forName("oracle.jdbc.driver.OracleDriver"
)
• 2) Fazer uma conexão
• Connection con =
DriverManager.getConnection(
"jdbc:oracle:thin:@oracle-prod:1521:OPROD",
username, passwd);
Criando JDBC Statement
• JDBC Statement é um objeto usado para mandar um
comando SQL para o SGBD
• Está associado a uma conexão aberta
• O método createStatement() retorna um objeto da classe
Statement (se não houver argumento) ou
PreparedStatement se houver um comando SQL como
argumento ("overload" de métodos).
Exemplo
Statement stat1 = myCon.createStatement();
PreparedStatement stat2 =
myCon.createStatement(
"SELECT nome, salario" +
"FROM Empregado" +
"WHERE salario > 5000'"
);
•
myCon é uma conexão, stat1 é um objeto “vazio” da classe Statement, e
stat2 é um objeto da classe PreparedStatement que tem um comando
SQL associado.
Executando Comandos
• JDBC distingue consultas (comandos que retornam
dados) de updates (comandos que somente afetam o
BD).
• Os métodos executeQuery() e executeUpdate() são
usados para executar essas duas espécies de
comandos.
– Eles devem ter um argumento se aplicados a
Statement, nunca se aplicados a PreparedStatement.
• Quando uma consulta é executada, retorna um
objeto da classe ResultSet.
Exemplo
stat1.executeUpdate(
"INSERT INTO Empregado" +
"VALUES(‘’Ana Maria, ‘Engenheira', 3000.00)"
);
ResultSet rs = stat2.executeQuery();
Executando comandos
• Comandos DDL (criar tabelas) e updates são
realizados com o método: executeUpdate()
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE Sells " +
"(bar VARCHAR2(40), beer VARCHAR2(40), price REAL)" );
stmt.executeUpdate("INSERT INTO Sells " +
"VALUES ('Bar', 'BudLite', 2.00)" );
String sqlString = "CREATE TABLE Bars " +
"(name VARCHAR2(40), address VARCHAR2(80), license INT)" ;
stmt.executeUpdate(sqlString);
Obtendo as Tuplas de um ResultSet
• O método next() se aplica a um ResultSet e
move um
“cursor” para a próxima tupla do conjunto.
– Aplique next() para obter a primeira tupla.
– next() returna FALSE se não houver mais tuplas.
• Para a tupla corrente do cursor, você pode
obter seu i-ésimo componente aplicando o
método getX(i), onde X é o nome para o tipo
do argumento.
Exemplo
while(rs.next()) {
nome = rs.getString(1);
salario = rs.getFloat(2);
...
}
Executando SELECT
• Usa o método executeQuery() que retorna
um objeto ResultSet
String bar, beer ; float price ;
ResultSet rs = stmt.executeQuery("SELECT * FROM Sells");
while ( rs.next() ) {
bar = rs.getString("bar");
beer = rs.getString("beer");
price = rs.getFloat("price");
System.out.println(bar + " sells " + beer + " for " + price +
" Dollars.");
}
Executando SELECT
• Alternativamente, podemos usar
bar = rs.getString(1);
price = rs.getFloat(3);
beer = rs.getString(2);
ResultSet
• Contém métodos getRow, isFirst,
isBeforeFirst, isLast, isAfterLast que indicam
aonde o cursor está
• Pode-se criar cursores scrollable que podem
se movimentar em qualquer sentido no
ResultSet
• Com isso podem ser usados:
• rs.absolute(3); // move para a terceira
tupla rs.previous(); // move para trás 1
tupla rs.relative(2); // move para frente 2
tuplas rs.relative(-3); // move para trás 3
tuplas
Prepared Statement
• Usado quando se quer usar a mesma query com
diferentes parâmetros várias vezes
• o comando é compilado e otimizado pelo SGBD apenas
uma vez
• PreparedStatement prepareUpdatePrice =
con.prepareStatement( "UPDATE Sells SET
price = ? WHERE bar = ? AND beer = ?");
• Então precisamos preencher os parâmetros:
• prepareUpdatePrice.setInt(1, 3);
prepareUpdatePrice.setString(2, "Bar Of
Foo"); prepareUpdatePrice.setString(3,
"BudLite");
Desvantagens de ODBC/JDBC
• Os programas C, Java, … , ainda são
executados no lado cliente
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
6.3 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).
6.3 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
6.3 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
6.3 Stored Procedures
• 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.
6.3 Stored Procedures
• 3. Segurança, como veremos no
próximo capítulo, 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 - Persistent Stored
Modules
• 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
defunçõ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-nometipo (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 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 FUNCION 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 execeçã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.
– Usada via a ferramenta Sqlplus.
• Um compromisso entre uma linguagem de programação
totalmente `procedural´ e a linguagem declarativa SQL.
• 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;
.
run;
• A parte DECLARE é opcional.
• `Dot and run´ finalizam o comando e o executam.
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
nome-modo-tipo.
– Modo: IN, OUT, ou IN OUT para readonly, 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-a-tupla.
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.
• É erro se o select-from-where retorna mais de uma
tupla; neste caso, é preciso usar um cursor.
Exemplo
• Encontrar o preço da cerveja Schincarioll 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´
END;
AND cerveja = `Schincarioll´;
Cursores
Declarados por:
CURSOR <nome> IS
comando select-from-where
• O cursor aponta para cada tupla por vez da relaçãoresultado 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.
Exemplo
Uma procedure que examina o menu
do bar Tricolor e aumenta de 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.
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
sequencia 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;
Exemplo
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
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 racle.jdbc.*;
public class BookDML {
public static void insertBook (String title, String publisher)
throws SQLException {
String sql = “INSERT INTO Livros VALUES (?, ?)”;
try {
Connection com = DriverManager.getConnection(“jdbc:default:c
PreparedStatement pstmt = conn.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 carchar(), publisher varchar)
As
Language java
Name ‘BookDML.insertBook(java.lang.String, java.lang.String)’;
Executando do SQLPlus:
CALL insertBookJava(´Meulivro´, ´LMV´);
SQLServer:Exemplos de SP
2. Usando parâmetros de saída (transact-SQL)
CREATE PROCEDURE DBO.Calc
@x int,
@y int,
@res int OUTPUT
AS
SET @res = @x + @y
Possível chamada:
DECLARE @resposta int
EXEC Calc 1,2 @resposta OUTPUT
Select ‘Resposta = ‘, @resposta
SQLServer:Exemplos de SP
3. CREATE PROCEDURE InsereEmpregado
@mat int,
@nomeE varchar(30),
@endereco varchar(60),
@salario float,
@depto int
AS
INSERT INTO Empregado
VALUES (@mat, @nomeE, @endereco,
@salario,
@depto)
SQLServer:Exemplos de SP
4.Alguns Stored Procedures do SQL-Server
sp_tables: mostra todos os nomes das tabelas do catálogo
sp_store_procedures: mostra todos os stored procedures
sp_server_info: mostra configuração do SQL-Server
sp_databases: mostra os BD disponíveis
sp_monitor: mostra como o SQL-server está executando
(memória, grau de atividade do processador, etc)
sp_who: informa quem está usando o BD num dado
instante
sp_help: dá informações sobre qualquer objeto do BD