Banco de Dados I Capítulo 6: Uso de SQL em Aplicações

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

UFPB/DSCBacharelado 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

Pré-processador

CompiladorLinguagemHospedeira

Linguagem Hospedeira (C, Pascal, Fortran,Cobol, Ada, SQLJ (java)) + SQL Embutido

Linguagem hospedeira +Chamadas de funções

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, SQLEXECPTION, 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, enderecointo :nome, enderecofrom Clientewhere 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 asinformaçõ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) dobegin 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 FORSELECT tituloFROM filmesWHERE ano = :ano;

void main () {EXEC SQL WHENEVER SQLERROR GOTO erro;ano = “1998”;EXEC SQL OPEN filme_cursor;while (strcmp(SQLSTATE, “02000”) != 0) {

EXEC SQL FETCH filme_cursorINTO :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 tuplasIF (strcmp(SQLSTATE, “02000”)) break;if (salario > 10000)

EXEC SQL DELETE FROM CLIENTEWHERE CURRENT OF salCursor;

else EXEC SQL UPDATE CLIENTESET 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ê-lo 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âmicoMotivaçã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 SqlplusEXEC 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 cliente-servidor– 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

ODBC

• 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

ODBC•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 o environment2. SQLHDBC: para conexão3. SQLHSTMT: para statements4. SQLHDESC: para descriptions

ODBCExemplo:#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);

}

ODBC

• 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 que

o próprio SQLPrepare descobre automaticamente.

• Use SQLExecute(myHandle) para executar o comando.

ExemploSQLPrepare(comando, "SELECT nome, salario

FROM Empregado

WHERE depto = 10";)

SQLExecute(comando);

Recuperando DadosPara 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.

ExemploSQLBindCol(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) {

...}

ODBC – 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);

• 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

JDBC

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

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

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

Exemplowhile(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).


• Vantagens:

• 1.Desempenho – Ex.: Seja a consulta

SELECT codigop, nome, COUNT(*)FROM Projeto p, Alocacao aWHERE p.codproj = a.codigopGROUP BY p.codproj, p.nome


• 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


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


• 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 locaiscorpo 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


Exemplo:

CREATE PROCEDURE MudaEndereco (IN endAntigo VARCHAR(255),IN endNovo VARCHAR(255)

)UPDATE EmpregadoSET endereco = endNovoWHERE endereco = endAntigo;


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 encerraa função)

Stored Procedures -SQL/PSM3) 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 END6) Labels: colocamos labels em comandos precedendo estespelo 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/PSMExemplo: 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 BOOLEANIF not exists (

select * from Filme where ano = a and nomeStudio = studio)

THEN RETURN TRUE; -- não faz a função retornar agoraELSEIF 1 <=

(select count(*) from Filme where ano = a andnomeStudio = nome and NOT emcores)

THEN RETURN TRUE;ELSE RETURN FALSE;END IF;

Stored Procedures -SQL/PSMExemplo: Procedimento que calcula a média e variância de um estúdioCREATE 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;


- For-Loopsusado para fazer iterator num cursorFOR <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;


Exemplo: Mesmo procedimento de média e variância de estúdios, usando FOR-LoopsCREATE 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


Exceções em PSM:É possível testar o SQLSTATE para verificar a ocorrência

de erros e tomar uma decisão, quando erros ocorramIsto é 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 tratadas2) Código a ser executado quando execeção ocorrer3) 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


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’;

BEGINDECLARE 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çõesBEGIN

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 ProcedureCREATE 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 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 %TYPEpara 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

) ASBEGIN

INSERT INTO VendeVALUES(`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 PSMAtribuição de valores a variáveis é denotada por :=.

DesvioIF <condição> THEN

<comando(s)>ELSE

<comando(s)>END IF;

• Em `ninhos´ de IFs, use ELSIF em lugar de ELSE IF.

LaçoLOOP

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

DECLAREp Vende.preço %TYPE;

BEGINSELECT preçoINTO pFROM VendeWHERE bar = `Tricolor´ AND cerveja =

`Schincarioll´;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.

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() ASaCerveja Vende.cerveja%TYPE;oPreço Vende.preço%TYPE;CURSOR c IS

SELECT cerveja, preçoFROM VendeWHERE bar =`Tricolor´;

BEGINOPEN c;LOOP

FETCH c INTO aCerveja, oPreço;EXIT WHEN c%NOTFOUND;IF oPreço < 3.00 THEN

UDPATE VendeSET preço = oPreço + 1.00WHERE 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.

ExemploA mesma procedure com a variável tupla cp.

CREATE PROCEDURE Aumento() ASCURSOR c IS

SELECT cerveja, preçoFROM VendeWHERE 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 VendeSET 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_namefrom user_objectswhere 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 .. valorFinalLOOP

sequencia de comandosEND LOOP


• Comando For - ExemploCreate procedure VerificaEstoqueDeclare

codInicial Produto.codProduto%Type;codFinal CodInicial%Type;estoque Produto.estoque%Type;

Beginselect Min(CodProduto), Max(codProduto)into codInicial, codFinalfrom Produtofor c in codInicial..codFinalloop

select estoque into estoquefrom produto where codProd = c and estoque>0Dbms_Output.Put_Line(‘O codigo ’|| c || ‘ tem em estoque’);

end loop;End


• Comando while

Sintaxe:WHILE condição LOOP

ComandosEND 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:EXCEPTIONWHEN nomeExceção 1 THEN

ComandosWHEN nomeExceção2 THEN

ComandosExemplo

begininsert into Pais values (100, ‘Brasil’);Commit;Dbms_Output.Put_Line(‘Inserção realizada com sucesso’);

Exceptionwhen 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çãoDeclare 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 comandosIf … then

RAISE nomeExceção;End If;Comandos

ExceptionWhen NomeExceção then Comandos

End

Exemplos de SP em SQL Server

1. CREATE PROCEDURE DBO.MostraEmpregadosDep@nomeDep varchar(50) = ‘Pessoal’ - - Dep DefaultASSELECT e.mat, e.nome, e.endereco, e.salarioFROM Empregados e, Departamento dWHERE d.nomeD = @nomeDep

Uma chamada a este procedimento seria:USE Empresa -- BB EmpresaEXEC MostraEmpregadosDep ‘Informatica’

SQLServer:Exemplos de SP

2. Usando parâmetros de saída (transact-SQL)

CREATE PROCEDURE DBO.Calc@x int,@y int,@res int OUTPUT

ASSET @res = @x + @y

Possível chamada:DECLARE @resposta intEXEC Calc 1,2 @resposta OUTPUTSelect ‘Resposta = ‘, @resposta


3. CREATE PROCEDURE InsereEmpregado@mat int,@nomeE varchar(30),@endereco varchar(60),@salario float,@depto int

ASINSERT INTO EmpregadoVALUES (@mat, @nomeE, @endereco,@salario,@depto)


4. Alguns Stored Procedures do SQL-Server

sp_tables: mostra todos os nomes das tabelas do catálogosp_store_procedures: mostra todos os stored proceduressp_server_info: mostra configuração do SQL-Serversp_databases: mostra os BD disponíveissp_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 dadoinstantesp_help: dá informações sobre qualquer objeto do BD

Documents

Banco de Dados I Capítulo 6: Uso de SQL em Aplicações