Banco de Dados I Cap

1
Banco de Dados ICapítulo 6 Uso de SQL em
Aplicações

• UFPB/DSC
• Bacharelado em Ciência da Computação
• Cláudio Baptista

2
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

3
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
4
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)

5
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 gt não houve erro
• Código 02000gt tupla não encontrada

6
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 nomeStudio50, endStudio256
  char SQLSTATE6 EXEC SQL END DECLARE
  SECTION

7
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 )

8
SQL Embutido
Exemplo de cadastro de cliente em C void
cadastraCliente() EXEC SQL BEGIN DECLARE
SECTION char nome50, endereco256 char
SQLSTATE6 EXEC SQL END DECLARE
SECTION printf(Entre o nome e
endereço\n) gets(nome) gets(endereco) inser
t into Cliente (nome, endereco) values (nome,
endereco)
9
SQL Embutido
Exemplo de consulta que retorna uma única
tupla void printCliente() EXEC SQL BEGIN
DECLARE SECTION char nome25,
endereco256 char SQLSTATE6 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)
10
(No Transcript)
11
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.

12
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.

13
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.

14
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

15
(No Transcript)
16
Exemplo usando Delete e Update // Se empregado
ganha mais de 10000 é demitido se não tem seu //
salário reduzido em 20 void reducaodeFolhadePagam
ento() EXEC SQL BEGIN DECLARE SECTION char
SQLSTATE6 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
gt 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
17
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

18
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)

19
SQL Embutido

• Exemplo em SQLJ
• sql CREATE TABLE EMPREGADO ( matricula int not
  null, nome varchar(30), Primary key(matricula)
  )

20
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 SQLgt.
• Para resolver, precisamos de dois comandos
• PREPARE converte uma cadeia de caracteres em um
  comando SQL.
• EXECUTE executa o comando.

21
Exemplo Versão Simplificada do Sqlplus

• EXEC SQL BEGIN DECLARE SECTION
• char queryMAX_QUERY_LENGTH
• EXEC SQL END DECLARE SECTION
• / issue SQLgt 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/Os.
• Alternativamente, PREPARE e EXECUTE podem ser
  combinadas em
• EXEC SQL EXECUTE IMMEDIATE query

22
Desvantagens da Abordagem 'Linguagens Hospedeira'

• Nem é C e nem é SQL, nem é Java e nem é SQL,
• O programa é executado no lado cliente da
  arquitetura cliente-servidor
• Problema de desempenho

23
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

24
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

25
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

26
SQL-CLI
Exemplo include ltsqlcli.hgt 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)
27
SQL-CLI

• Use SQLPrepare(myHandle, ltstatementgt,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)
28
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)
• ...

29
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)
30
ODBC

• ODBC tem as mesmas idéias de SQL/CLI, entretando
  com pequenas modificações
• A seguir veremos um exemplo de ODBC

31
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_NTS) char banco80 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(c
onn) SQLFreeEnv(env)
32
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 cliente-servidor

33
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(
  "jdbcoraclethin_at_oracle-prod1521OPROD",
  username, passwd)

34
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 gt 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.

35
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()

36
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)
37
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)
• ...

38
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(ba
r " sells " beer " for " price "
Dollars.")
39
Executando SELECT

• Alternativamente, podemos usar

bar rs.getString(1) price rs.getFloat(3)
beer rs.getString(2)
40
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

41
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")

42
Desvantagens de ODBC/JDBC

• Os programas C, Java, , ainda são executados no
  lado cliente

43
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

44
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).

45
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

46
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

47
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.

48
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.

49
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.

50
Stored Procedures -SQL/PSM

• Criando Funções e Procedimentos
• CREATE PROCEDURE ltNOMEgt (ltparâmetrosgt) declara
  ções locais corpo do procedimento
• CREATE FUNCTION ltNOMEgt RETURNS lttipogt 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

51
Stored Procedures -SQL/PSM
Exemplo CREATE PROCEDURE MudaEndereco ( IN
endAntigo VARCHAR(255), IN endNovo
VARCHAR(255) ) UPDATE Empregado SET endereco
endNovo WHERE endereco endAntigo
52
Stored Procedures -SQL/PSM
Alguns Comandos 1) Chamada a um
procedimento CALL ltnome proceduregt (ltlista
argumentosgt) 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 ltexpressãogt
(OBS este comando não encerra a função)
53
Stored Procedures -SQL/PSM
3) Declaração de variáveis locais DECLARE
ltnomegt lttipogt 4) Comando de atribuição SET
ltvariávelgt ltexpressãogt 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 ltcondiçãogt THEN LOOP ltcomandosgt
ltComandosgt ELSEIF ltcondiçãogt THEN END
LOOP ltcomandosgt ELSE ltcomandosgt END IF
54
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 char15)
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 lt (select count()
from Filme where ano a and nomeStudio nome
and NOT emcores) THEN RETURN TRUE ELSE RETURN
FALSE END IF
55
Stored Procedures -SQL/PSM
Exemplo Procedimento que calcula a média e
variância de um estúdio CREATE PROCEDURE MeanVar
( IN s char15, 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
56
Stored Procedures -SQL/PSM
- For-Loops usado para fazer iterator num
cursor FOR ltnome laçogt AS ltnome cursorgt CURSOR
FOR ltquerygt DO ltcomandosgt END FOR Veja
exemplo no próximo slide! - WHILE ltcondiçãogt
DO ltcomandosgt END WHILE -
REPEAT ltcomandosgt UNTIL ltcondiçãogt END
REPEAT
57
Stored Procedures -SQL/PSM
Exemplo Mesmo procedimento de média e variância
de estúdios, usando FOR-Loops CREATE PROCEDURE
MeanVar ( IN s char15, 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
58
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 ltonde irgt HANDLER FOR
ltcondiçõesgt ltcomandogt AS escolhas de ltonde irgt
são - CONTINUE - EXIT (sai do bloco BEGIN ..
END) - UNDO
59
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
60
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.

61
Procedures
Objetos armazenados no BD, que usam comandos
PL/SQL em seus corpos. Declarações de
Procedure CREATE OR REPLACE PROCEDURE ltnomegt(ltli
sta_argumentosgt) AS ltdeclaraçõesgt BEGIN
ltcomandos PL/SQLgt END
62
PL-SQL Oracle Stored Procedures

• ltLista_argumentosgt tem triplas nome-modo-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.

63
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.

64
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

65
ORACLE PSM

• Atribuição de valores a variáveis é denotada por
  .
• Desvio
• IF ltcondiçãogt THEN
• ltcomando(s)gt
• ELSE
• ltcomando(s)gt
• END IF
• Em ninhos de IFs, use ELSIF em lugar de ELSE
  IF.
• Laço
• LOOP
• . . .
• EXIT WHEN ltcondiçãogt
• . . .
• END LOOP

66
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.

67
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 AND cerveja
  Schincarioll
• END

68
Cursores

• Declarados por
• CURSOR ltnomegt 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 ltnome_cursorgt INTO
• lista_variáveis
• Um laço é interrompido por
• EXIT WHEN ltnome_cursorgt NOTFOUND
• O valor é Verdade se não houver mais tupla a
  apontar.
• OPEN e CLOSE abrem e fecham um cursor,
  respectivamente.

69
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.

70
CREATE PROCEDURE Aumento() AS aCerveja
Vende.cervejaTYPE oPreço Vende.preçoTYPE C
URSOR c IS SELECT cerveja, preço FROM
Vende WHERE bar Tricolor BEGIN OPEN
c LOOP FETCH c INTO aCerveja,
oPreço EXIT WHEN cNOTFOUND IF oPreço lt
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
71
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.

72
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 lt 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
73
Definição de Function
- Podemos definir uma função CREATE FUNCTION
ltfunc_namegt(ltparam_listgt) RETURN ltreturn_typegt
AS ... No corpo da função, "RETURN ltexpressiongt"
sai (retorna) da função e retorna o valor
da ltexpressiongt. - 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'
74
Removendo Procedures/Functions
Para remover uma stored procedure/function drop
procedure ltprocedure_namegt drop function
ltfunction_namegt
75
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
76
Outras formas de Laços

• Comando For - Exemplo

Create procedure VerificaEstoque Declare codInici
al Produto.codProdutoType codFinal
CodInicialType estoque Produto.estoqueType Be
gin 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
estoquegt0 Dbms_Output.Put_Line(O codigo c
tem em estoque) end loop End
77
Outras formas de Laços

• Comando while

Sintaxe WHILE condição LOOP Comandos END
LOOP Exemplo
78
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

79
Manipulando Exceções
Sintaxe EXCEPTION WHEN nomeExceção 1 THEN Com
andos WHEN nomeExceção2 THEN Comandos Exemplo
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
80
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

81
Exemplo de Exceção
Declare Aux_X number(1) Subtype TipoX
is Aux_XType -- 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
82
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 Excep
tion When NomeExceção then Comandos End
83
Exemplos de SP em SQL Server
84
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(jdbcdefaultconnecti
on) PreparedStatement pstmt
conn.prepareStatement(sql)
pstmt.setString(1, title)
pstmt.setString(2, publisher)
pstmt.close() catch (SQLException e)
system.err.println(e.getMessage())
85
Java Stored Procedure no Oracle

• Carregando a Classe no Banco de dados
• gt 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)

86
SQLServerExemplos de SP
87
SQLServerExemplos de SP
88
SQLServerExemplos de SP