Banco de dados i cap tulo 6 uso de sql em aplica es
Download
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


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


SQL Dinâmico

Motivação:

  • SQL em uma linguagem hospedeira é bom em aplicações estáticas, p.e., um programa de reserva de passagens aéreas.

  • Não serve para escrever um programa como sqlplus, porque não se sabe de antemão qual o código que segue um sinal de prontidão SQL>.

  • Para resolver, precisamos de dois comandos:

    • PREPARE converte uma cadeia de caracteres em um comando SQL.

    • EXECUTE executa o comando.


Exemplo: Versão Simplificada do Sqlplus

EXEC SQL BEGIN DECLARE SECTION;

char query[MAX_QUERY_LENGTH];

EXEC SQL END DECLARE SECTION;

/* issue SQL> prompt */

/* read user's text into array query */

EXEC SQL PREPARE q FROM :query;

EXEC SQL EXECUTE q;

/* go back to reissue prompt */

  • Uma vez preparada, uma consulta pode ser executada muitas vezes.

    • “Prepare” = otimiza a consulta, i.e., encontra um meio de executá-la com um mínimo de I/O’s.

  • Alternativamente, PREPARE e EXECUTE podem ser combinadas em:

    EXEC SQL EXECUTE IMMEDIATE :query;


Desvantagens da Abordagem 'Linguagens Hospedeira'

  • Nem é C e nem é SQL, nem é Java e nem é SQL, …

  • O programa é executado no lado cliente da arquitetura 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


SQL-CLI

  • Em C, necessita-se do include sqlcli.h

  • O programa pode então usar 4 tipos de estruturas:

    • 1. Environment: prepara para conexão

    • 2. Connections: conecta a aplicação ao SGBD

    • 3. Statements: contém os comandos a serem processados

    • 4. Descriptions: metadados


SQL-CLI

  • Em C, a biblioteca permite que você crie um statement

  • handle = estrutura em que você coloca um comando SQL.

  • Os handles são:

  • 1. SQLHENV: para setar environment

  • 2. SQLHDBC: para conexão

  • 3. SQLHSTMT: para statements

  • 4. SQLHDESC: para descriptions


SQL-CLI

Exemplo:

#include <sqlcli.h>

SQLHENV ambiente;

SQLHDBC conexao;

SQLHSTMT comando;

SQLHRETURN erro1, erro2, erro3;

erro1 = SQLAllocHandle(SQL_HANDLE_ENV,

SQL_NULL_HANDLE, &ambiente);

if (!erro1) {

erro2 = SQLAllocHandle(SQL_HANDLE_DBC,

ambiente, &conexao);

if(!erro2)

erro3 = SQLAllocHandle(SQL_HANDLE_SMT,

conexao, &comando);

}


SQL-CLI

  • Use SQLPrepare(myHandle, <statement>,length) para fazer myHandle representar o comando SQL no segundo argumento.

    • Length é o tamanho do string. Pode ser usado SQL_NTS (Null Terminating String) que o próprio SQLPrepare descobre automaticamente.

  • Use SQLExecute(myHandle) para executar o comando.

    Exemplo

    SQLPrepare(comando, "SELECT nome, salario

    FROM Empregado

    WHERE depto = 10", SQL_NTS);

    SQLExecute(comando);

  • Podemos combinar estes dois comandos num único:

  • SQLExecDirect(comando, "SELECT nome, salario

    • FROM Empregado WHERE depto = 10", SQL_NTS);


Recuperando Dados

Para obter os dados retornados por uma consulta, usa-se:

  • Variáveis de ligação (“bind”) para os componentes das tuplas retornadas.

    • SQLBindCol aplica-se a um handle, o número da coluna, e a variável de ligação, mais outros argumentos.

  • Comando “Fetch”, usando o handle.

    • SQLFetch aplica-se a um handle.

      Exemplo

      SQLBindCol(handle1, 1, SQL_CHAR, &coluna1, size(coluna1), &col1Info)

      SQLBindCol(handle1, 2, SQL_REAL, &coluna2, size(coluna2), &col2Info)

      SQLExecute(handle1);

      ...

      while(SQLFetch(handle1) != SQL_NO_DATA) {

      ...

      }


Sql cli passando par metros para queries
SQL-CLI – Passando Parâmetros para Queries

  • Use:

    • 1. SQLPrepare(Comando)

    • 2. SQLBindParameter()

    • 3. SQLExecute()

Exemplo:

SQLPrepare(comando, “insert into projeto(codigo, nome)

values (?,?)”, SQL_NTS);

SQLBindParameter(comando, 1,..., codProj, ...);

SQLBindParameter(comando, 2, ..., nomeProj, ...)~;

SQLExecute(comando);


ODBC

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

  • A seguir veremos um exemplo de ODBC


int ODBCexample() {

RETCODE error;

HENV env; /* environment */

HDBC conn; /* database connection */

SQLAllocEnv(&env);

SQLAllocConnect(env, &conn);

SQLConnect(conn, “buchada.dsc.ufcg.edu.br”, SQL_NTS,“baptista”, “password”, SQL_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); }


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


JDBC Estabelecendo uma Conexão

  • 1) Precisa-se carregar um driver JDBC para o SGBD que se está usando.

  • No Oracle, isto pode ser feito da seguinte forma:

    • Class.forName("oracle.jdbc.driver.OracleDriver")

  • 2) Fazer uma conexão

    • Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@oracle-prod:1521:OPROD", username, passwd);


  • Criando JDBC Statement

    • JDBC Statement é um objeto usado para mandar um comando SQL para o SGBD

    • Está associado a uma conexão aberta

    • O método createStatement() retorna um objeto da classe Statement (se não houver argumento) ou PreparedStatement se houver um comando SQL como argumento ("overload" de métodos).

      Exemplo

      Statement stat1 = myCon.createStatement();

      PreparedStatement stat2 =

      myCon.createStatement(

      "SELECT nome, salario" +

      "FROM Empregado" +

      "WHERE salario > 5000'"

      );

    • myCon é uma conexão, stat1 é um objeto “vazio” da classe Statement, e stat2 é um objeto da classe PreparedStatement que tem um comando SQL associado.


    Executando Comandos

    • JDBC distingue consultas (comandos que retornam dados) de updates (comandos que somente afetam o BD).

    • Os métodos executeQuery() e executeUpdate() são usados para executar essas duas espécies de comandos.

      • Eles devem ter um argumento se aplicados a Statement, nunca se aplicados a PreparedStatement.

    • Quando uma consulta é executada, retorna um objeto da classe ResultSet.

      Exemplo

      stat1.executeUpdate(

      "INSERT INTO Empregado" +

      "VALUES(‘’Ana Maria, ‘Engenheira', 3000.00)"

      );

      ResultSet rs = stat2.executeQuery();


    Executando comandos

    • Comandos DDL (criar tabelas) e updates são realizados com o método: executeUpdate()

    Statement stmt = con.createStatement();

    stmt.executeUpdate("CREATE TABLE Sells " +

    "(bar VARCHAR2(40), beer VARCHAR2(40), price REAL)" );

    stmt.executeUpdate("INSERT INTO Sells " +

    "VALUES ('Bar', 'BudLite', 2.00)" );

    String sqlString = "CREATE TABLE Bars " +

    "(name VARCHAR2(40), address VARCHAR2(80), license INT)" ;

    stmt.executeUpdate(sqlString);


    Obtendo as Tuplas de um ResultSet

    • O método next() se aplica a um ResultSet e move um “cursor” para a próxima tupla do conjunto.

      • Aplique next() para obter a primeira tupla.

      • next() returna FALSE se não houver mais tuplas.

    • Para a tupla corrente do cursor, você pode obter seu i-ésimo componente aplicando o método getX(i), onde X é o nome para o tipo do argumento.

      Exemplo

      while(rs.next()) {

      nome = rs.getString(1);

      salario = rs.getFloat(2);

      ...

      }


    Executando SELECT

    • Usa o método executeQuery() que retorna um objeto ResultSet

    String bar, beer ; float price ;

    ResultSet rs = stmt.executeQuery("SELECT * FROM Sells");

    while ( rs.next() ) {

    bar = rs.getString("bar");

    beer = rs.getString("beer");

    price = rs.getFloat("price");

    System.out.println(bar + " sells " + beer + " for " + price +

    " Dollars.");

    }


    Executando SELECT

    • Alternativamente, podemos usar

    bar = rs.getString(1);

    price = rs.getFloat(3);

    beer = rs.getString(2);


    ResultSet

    • Contém métodos getRow, isFirst, isBeforeFirst, isLast, isAfterLast que indicam aonde o cursor está

    • Pode-se criar cursores scrollable que podem se movimentar em qualquer sentido no ResultSet

    • Com isso podem ser usados:

    • rs.absolute(3); // move para a terceira tupla rs.previous(); // move para trás 1 tupla rs.relative(2); // move para frente 2 tuplas rs.relative(-3); // move para trás 3 tuplas


    Prepared Statement

    • Usado quando se quer usar a mesma query com diferentes parâmetros várias vezes

    • o comando é compilado e otimizado pelo SGBD apenas uma vez

    • PreparedStatement prepareUpdatePrice = con.prepareStatement( "UPDATE Sells SET price = ? WHERE bar = ? AND beer = ?");

    • Então precisamos preencher os parâmetros:

    • prepareUpdatePrice.setInt(1, 3); prepareUpdatePrice.setString(2, "Bar Of Foo"); prepareUpdatePrice.setString(3, "BudLite");


    Desvantagens de ODBC/JDBC

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


    Integração Estreita com SGBDs

    • O uso de SQL/PSM (Stored Procedures) tais como PL/SQL, SQLJ, TransactSQL, … , são extensões da SQL

      • Processadas no lado servidor da arquitetura cliente - servidor

        • Isto é muito bom para o desempenho


    6 3 stored procedures
    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.


    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.


    Oracle: Exemplo

    Uma procedure que inclui uma nova cerveja e seu preço no menu do bar RubroNegro.

    Vende(bar, cerveja, preço)

    CREATE PROCEDURE MenuRubroNegro(

    c IN Vende.cerveja %TYPE,

    p IN Vende.preço %TYPE

    ) AS

    BEGIN

    INSERT INTO Vende

    VALUES(`RubroNegro´´, c, p);

    END;

    .

    run;

    • Note “run” somente armazena a procedure, não a executando.


    Oracle: Invocando Procedures

    Uma chamada a uma procedure pode aparecer no corpo de um comando PL/SQL.

    • Exemplo:

      BEGIN

      MenuRubroNegro('Bud', 2,50);

      MenuRubroNegro(‘Carlsberg', 5,00);

      END;


    ORACLE PSM

    Atribuição de valores a variáveis é denotada por :=.

    Desvio

    IF <condição> THEN

    <comando(s)>

    ELSE

    <comando(s)>

    END IF;

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

      Laço

      LOOP

      . . .

      EXIT WHEN <condição>

      . . .

      END LOOP;


    Oracle: Consultas em PL/SQL

    • Single-row selects permitem atribuir a uma variável o resultado de uma consulta que produz uma única tupla.

    • Cursors permitem a recuperação de muitas tuplas, com o cursor e um laço sendo usados para processar tupla-a-tupla.


    Single-Row Select

    • Um select-from-where em PL/SQL deve ter uma cláusula INTO listando as variáveis que recebem os resultados da consulta.

    • É erro se o select-from-where retorna mais de uma tupla; neste caso, é preciso usar um cursor.

      Exemplo

    • Encontrar o preço da cerveja Schincarioll no bar Tricolor.

      Vende(bar, cerveja, preço)

      DECLARE

      p Vende.preço %TYPE;

      BEGIN

      SELECT preço

      INTO p

      FROM Vende

      WHERE bar = `Tricolor´ 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() AS

    aCerveja Vende.cerveja%TYPE;

    oPreço Vende.preço%TYPE;

    CURSOR c IS

    SELECT cerveja, preço

    FROM Vende

    WHERE bar =`Tricolor´;

    BEGIN

    OPEN c;

    LOOP

    FETCH c INTO aCerveja, oPreço;

    EXIT WHEN c%NOTFOUND;

    IF oPreço < 3.00 THEN

    UDPATE Vende

    SET preço = oPreço + 1.00

    WHERE bar = `Tricolor´

    AND cerveja = aCerveja;

    END IF;

    END LOOP;

    CLOSE c;

    END;


    Tipo ROWTYPE

    Qualquer coisa (i.e., cursores, nomes de tabela) que tem um tipo tupla pode ter seu tipo capturado com %ROWTYPE.

    • Pode-se criar variáveis temporárias tipo tupla e acessar seus componentes como variável.componente (“dot notation”).

    • Muito útil, principalmente se a tupla tem muitos componentes.


    Exemplo

    A mesma procedure com a variável tupla cp.

    CREATE PROCEDURE Aumento() AS

    CURSOR c IS

    SELECT cerveja, preço

    FROM Vende

    WHERE bar = `Tricolor´;

    cp c %ROWTYPE;

    BEGIN

    OPEN c;

    LOOP

    FETCH c INTO cp;

    EXIT WHEN c %NOTFOUND;

    IF cp.preço < 3.00 THEN

    UDPATE Vende

    SET preço= cp.preço + 1.00

    WHERE bar = `Tricolor´

    AND cerveja = cp.cerveja;

    END IF;

    END LOOP;

    CLOSE c;

    END;


    Defini o de function
    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