banco de dados i cap tulo 6 uso de sql em aplica es
Download
Skip this Video
Download Presentation
Banco de Dados I Capítulo 6: Uso de SQL em Aplicações

Loading in 2 Seconds...

play fullscreen
1 / 88

Banco de Dados I Cap tulo 6: Uso de SQL em Aplica es - PowerPoint PPT Presentation


  • 94 Views
  • Uploaded on

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?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Banco de Dados I Cap tulo 6: Uso de SQL em Aplica es' - elon


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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/DSC

Bacharelado em Ciência da Computação

Cláudio Baptista

sql embutido
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 embutido1
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 embutido2
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 embutido3
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 embutido4
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 embutido5
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 embutido6
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 embutido7
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);

}

sql embutido8
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 embutido9
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 embutido10
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 embutido11
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
slide16

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 embutido12
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 embutido13
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 embutido14
SQL Embutido
  • Exemplo em SQLJ:
    • #sql { CREATE TABLE EMPREGADO ( matricula int not null, nome varchar(30), Primary key(matricula) )};
slide20

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

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;

slide22

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
slide23

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
slide24

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
slide25

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
slide26

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

}

slide27

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

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

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

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

slide32

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
slide33

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

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

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

slide36

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

slide37

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

...

}

slide38

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

}

slide39

Executando SELECT

  • Alternativamente, podemos usar

bar = rs.getString(1);

price = rs.getFloat(3);

beer = rs.getString(2);

slide40

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
slide41

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");
slide42

Desvantagens de ODBC/JDBC

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

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
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 procedures1
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 procedures2
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 procedures3
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 procedures4
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
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
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 psm1
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 psm2
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 psm3
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 psm4
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 psm5
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 psm6
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 psm7
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 psm8
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 psm9
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
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.
slide61

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 procedures1
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 %TYPE

para capturar o tipo corretamente.

slide63

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

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;

slide65

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;

slide66

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

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;

slide68

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

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

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;

slide71

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

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
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
Removendo Procedures/Functions

Para remover uma stored procedure/function:

drop procedure <procedure_name>;

drop function <function_name>;

outras formas de la os
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 os1
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 os2
Outras formas de Laços
  • Comando while

Sintaxe:

WHILE condição LOOP

Comandos

END LOOP;

Exemplo

manipulando exce es
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 es1
Manipulando Exceções

Sintaxe:

EXCEPTION

WHEN nomeExceção 1 THEN

Comandos

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;

exece es pr definidas
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
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
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

java stored procedure no oracle
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:connection:”);

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 oracle1
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´);

ad