1 / 93

Capítulo 1 : SQL-PSM (Persistent Stored Modules)

Capítulo 1 : SQL-PSM (Persistent Stored Modules). Banco de Dados II Prof. Carlos Eduardo Pires cesp@dsc.ufcg.edu.br. Motivação. Ex.: Escreva um programa Pascal que leia a matricula de um empregado e imprima as informações sobre este empregado. program Imprima; var loop: char;

iliana-house
Download Presentation

Capítulo 1 : SQL-PSM (Persistent Stored Modules)

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Capítulo 1: SQL-PSM (Persistent Stored Modules) Banco de Dados II Prof. Carlos Eduardo Pires cesp@dsc.ufcg.edu.br

  2. Motivação Ex.: Escreva um programa Pascal que leia a matricula de um empregado e imprima as informações sobre este empregado. program Imprima; var loop: char; matricula: integer; E: record of nome: string[15]; endereco: string[30]; funcao: string[10]; salario: real; end; begin loop := ‘S’; while (loop = ‘S’) do begin writeln(‘Entre com a matricula:’); readln(matricula); $ select nome, endereço, função, salario into :E.nome, E.endereco, :E.funcao, :E.salario where matricula = :matricula; writeln(E.nome, :E.endereco, E.funcao, E.salario); writeln(‘Deseja ler mais empregados(S/N)?’); readln(loop); end; end. Uma consulta é executada a cada iteração: E o desempenho? Capítulo 1: SQL-PSM

  3. Cursor • No programa anterior uma única tupla (ou linha) é selecionada pelo SQL embutido • Em geral, uma consulta resulta em várias tuplas • Problema • SQL processa um conjunto de tuplas, enquanto que C e Pascal (ou outra linguagem host) processam uma tupla por vez • Solução • Introduziu-se o conceito de cursor para permitir processar uma tupla por vez nas linguagens hospedeiras Capítulo 1: SQL-PSM

  4. Cursor • Problema: “Impedance Mismatch” • SQL trabalha com relações • Linguagens de programação trabalham de forma orientada a registro • Como fazer para ler os dados de um conjunto retornado pelo SQL em uma 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 Capítulo 1: SQL-PSM

  5. Cursor • Ponteiro para uma única tupla do resultado da consulta (result set) • Cada cursor possui uma consulta associada, especificada como parte da operação que define o cursor • A consulta é executada quando o cursor for aberto • Em uma 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 Capítulo 1: SQL-PSM

  6. Cursor • Sintaxe para 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 consulta especificada e põe o cursor para apontar para uma posição anterior à 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 Capítulo 1: SQL-PSM

  7. Banco de Dados SELECT ename FROM employee Cursor • OPEN • FETCH • CLOSE KING SMITH JOHN ... Cursor KING SMITH JOHN ... KING SMITH JOHN ... Cursor Capítulo 1: SQL-PSM

  8. Cursor • 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 (linha corrente). Sintaxe: DELETE FROM tabela WHERE CURRENT OF cursor Capítulo 1: SQL-PSM

  9. Um Exemplo de Uso de Cursor em C EXEC SQL BEGIN DECLARE SECTION; char SQLSTATE[6]; char titulo[101]; char ano[5]; EXEC SQL DECLARE filme_cursor CURSOR FOR SELECT titulo FROM filmes WHERE ano = :ano; void main () { EXEC SQL WHENEVER SQLERROR GOTO erro; strcpy(ano,”1998”); EXEC SQL OPEN filme_cursor; while (strcmp(SQLSTATE, “02000”) != 0) { EXEC SQL FETCH filme_cursor INTO :titulo; printf(“%s\n”, titulo); }; EXEC SQL CLOSE filme_cursor; return; erro: printf(“Um Erro ocorreu!\n”); }; Capítulo 1: SQL-PSM

  10. 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 tuplas if (strcmp(SQLSTATE, “02000”)) break; if (salario > 10000) EXEC SQL DELETE FROM Empregado WHERE CURRENT OF salCursor; else EXEC SQL UPDATE Empregado SET salario = salario - salario * 0.2; WHERE CURRENT OF salCursor; } EXEC SQL CLOSE salCursor; } Capítulo 1: SQL-PSM

  11. Scrolling Cursors • Por default, cursores movem-se do início do result set para frente (forward) • Podemos movê-los também para trás e/ou para qualquer posição no result set • Devemos acrescentar SCROLL na definição do cursor • Exemplo • EXEC DECLARE meuCursor SCROLL CURSOR FOR Empregado; Capítulo 1: SQL-PSM

  12. 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) Capítulo 1: SQL-PSM

  13. Exercício de Sala • Sem usar a sintaxe de uma linguagem de programação (ou seja, usando o bom e velho português) escrever um código fictício para recuperar e imprimir o nome dos alunos matriculados na turma de BD2, período 2010.2 Capítulo 1: SQL-PSM

  14. Motivação • Vimos anteriormente que código SQL pode ser “misturado” com o código de uma linguagem de programação • Porém, é perfeitamente possível separar • Código da LP na aplicação • Código SQL no BD, nesse caso precisamos dar um “nome” ao código SQL • O código da LP faz chamadas ao código SQL através desses nomes Capítulo 1: SQL-PSM

  15. Integração Estreita com SGBDs • O uso de SQL/PSM (Stored Procedures) como PL/SQL, SQLJ, TransactSQL,…, são extensões de SQL • Processadas no lado servidor da arquitetura cliente-servidor: muito bom para o desempenho Capítulo 1: SQL-PSM

  16. Stored Procedures (SP) • Em português, procedimentos armazenados • Conjuntos 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) Capítulo 1: SQL-PSM

  17. Vantagens das Stored Procedures • Desempenho • Ex.: Seja a consulta • SELECT codigop, nome • FROM Projeto p, Alocacao a • WHERE p.codproj = a.codigop • Se vários usuários realizarem esta consulta o tráfego de rede (dados e texto SQL) será alto • Se criarmos uma SP para executar a consulta, os usuários necessitarão apenas de um comando para executar a consulta anterior: • EXEC nomeProcedimento; • A consulta anterior seria compilada a cada chamada, enquanto o SP contendo a consulta seria compilado uma única vez Capítulo 1: SQL-PSM

  18. Vantagens das Stored Procedures • Manutenção • Facilita o gerenciamento do BD, pois a consulta é escrita em um único lugar • A manutenção torna-se mais eficaz e segura • Segurança • Podemos usar SPs para limitar o acesso de alguns usuários ao BD • A maneira em que o BD pode ser modificado é estritamente definida Capítulo 1: SQL-PSM

  19. Stored Procedures: SQL/PSM • SQL/PSM (Persistent Stored Modules) • Parte do padrão SQL relativo às Stored Procedures • Cada SGBD oferece sua própria linguagem (PL/SQL, Transact/SQL, PL/pgSQL, etc.) • Em PSM, definimos módulos que são coleções de definições de funções ou procedimentos, declarações de tabelas temporárias, dentre outros Capítulo 1: SQL-PSM

  20. Stored Procedures: SQL/PSM • Criando Funções e Procedimentos • CREATE PROCEDURE <NOME> (<parâmetros>) declarações locais corpo do procedimento; • CREATE FUNCTION <NOME> (<parâmetros>) 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 Capítulo 1: SQL-PSM

  21. Stored Procedures: SQL/PSM • Exemplo CREATE PROCEDURE MudaEndereco ( IN endAntigo VARCHAR(255), IN endNovo VARCHAR(255) ) UPDATE Empregado SET endereco = endNovo WHERE endereco = endAntigo; Capítulo 1: SQL-PSM

  22. Stored Procedures: SQL/PSM • Alguns comandos • 1) Chamada a um procedimento: • CALL <nome procedure> (<lista argumentos>); • Obs.: CALL é aplicado apenas a Procedures (não a 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 Capítulo 1: SQL-PSM

  23. 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) Label • Colocamos labels em comandos precedendo estes pelo nome do label e dois pontos Capítulo 1: SQL-PSM

  24. Stored Procedures: SQL/PSM • 7) Comandos condicionais IF <condição> THEN <comandos> ELSEIF <condição> THEN <comandos> … ELSE <comandos> END IF; • 8) Laços LOOP <Comandos> END; Capítulo 1: SQL-PSM

  25. Stored Procedures: SQL/PSM • Exemplo: função que recebe um ano e nome de estúdio e retorna TRUE se não produziu nenhum filme naquele ano ou se aquele estúdio produziu apenas um filme preto e branco CREATE FUNCTION PretoeBranco (p_ano int, studio char[15]) RETURNS BOOLEAN IF NOT EXISTS (SELECT * FROM Filme WHERE ano = p_ano AND nomeStudio = studio) THEN RETURN TRUE; ELSEIF 1 = (SELECT COUNT(*) FROM Filme WHERE ano = p_ano AND nomeStudio = studio AND NOT emcores) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; Capítulo 1: SQL-PSM

  26. Stored Procedures: SQL/PSM Exemplo: Procedimento que calcula a duração média dos filmes de um estúdio CREATE PROCEDURE MeanVar (IN s char[15], OUT mean 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 contaFilmes = 0; OPEN filmeCursor; filmeLOOP: LOOP FETCH filmeCursor INTO novaDuracao; IF NotFound THEN LEAVE filmeLOOP END IF; SET contaFilmes = contaFilmes + 1; SET mean = mean + novaDuracao; END LOOP; SET mean = mean / contaFilmes; CLOSE filmeCursor; END; Capítulo 1: SQL-PSM

  27. Laços em PSM • Laços FOR FOR <nome laço> AS <nome cursor> CURSOR FOR <query> DO <comandos> END FOR; • Exemplo FOR alunoLOOP AS alunoCursor CURSOR FOR SELECT nome, idade FROM Aluno WHERE periodo = ‘2010.2’; DO ... END FOR; Capítulo 1: SQL-PSM

  28. Exemplo do Laço FOR • Exemplo: Mesmo procedimento anterior, usando Laço FOR CREATE PROCEDURE MeanVar (IN s char[15], OUT mean REAL) DECLARE contaFilmes INTEGER; BEGIN SET mean = 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 + duracao; END FOR; SET mean = mean / contaFilmes; END; • Obs.: veja que não precisa de OPEN, FETCH e CLOSE do cursor Capítulo 1: SQL-PSM

  29. Outros Tipos de Laços em PSM • Laço WHILE WHILE <condição> DO <comandos> END WHILE; • REPEAT REPEAT <comandos> UNTIL <condição> END REPEAT; Capítulo 1: SQL-PSM

  30. 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 exceçã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 Capítulo 1: SQL-PSM

  31. 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 matricula = mat); END; • TooMany muitas linhas retornadas pelo SELECT • NotFound  nenhuma linha retornada pelo SELECT Capítulo 1: SQL-PSM

  32. PL/SQL – Oracle Stored Procedures • Linguagem de desenvolvimento do SGBD Oracle que “implementa” SQL/PSM (não é fiel ao padrão) • Significa “Procedural Language extensions to SQL” • Permite variáveis locais, laços, condições, procedures, consulta à relações “one tuple at a time”, etc • Forma geral DECLARE declarações (optativo) BEGIN comandos executáveis; (obrigatórios) EXCEPTION comandos para manipular erros (optativo) END; Capítulo 1: SQL-PSM

  33. Oracle Stored Procedures • Código PL/SQL é feito de blocos com uma única estrutura • Existem dois tipos de blocos em PL/SQL: • Blocos Anônimos: não possuem nomes (são como scripts) • Podem ser escritos e executados imediatamente no iSQL*Plus • Podem ser usados em um trigger (gatilho) • Blocos Nomeados: são armazenados no banco de dados • Procedures • Functions • Pacotes Capítulo 1: SQL-PSM

  34. Blocos Anônimos DECLARE (opcional) /* aqui se declaram as variáveis que serão usadas no bloco */ BEGIN (obrigatório) /* define-se os comandos que dizem o que o bloco faz*/ EXCEPTION (opcional) /* define-se as ações que acontecem se uma exceção for lançado durante a execução deste bloco */ END;(obrigatório) / Capítulo 1: SQL-PSM

  35. DECLARE Sintaxe Exemplos identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Note que PL/SQL inclui todos tipos SQL, e outros mais… DECLARE birthday DATE; age NUMBER(2) NOT NULL := 27; name VARCHAR2(13) := 'Levi'; magic CONSTANT NUMBER := 77; valid BOOLEAN NOT NULL := TRUE; Capítulo 1: SQL-PSM

  36. Declarando Variáveis com %TYPE Exemplos Acessando coluna sname na tabela Sailors DECLARE sname sailors.sname%TYPE; fav_boat VARCHAR2(30); my_fav_boat fav_boat%TYPE := ‘GrafSpee'; ... Acessando outra variável Capítulo 1: SQL-PSM

  37. Criando um PL/SQL Record (Registro) • Um record é um tipo de variável que podemos definir (como ‘struct’ em C ou ‘object’ em Java) DECLARE TYPEsailor_record_typeIS RECORD (sname VARCHAR2(10), sid VARCHAR2(9), age NUMBER(3), rating NUMBER(3)); sailor_record sailor_record_type; ... BEGIN sailor_record.sname:=‘peter’; sailor_record.age:=45; … Capítulo 1: SQL-PSM

  38. Oracle Stored Procedures • São objetos armazenados no BD que usam comandos PL/SQL e SQL em seus corpos • Sintaxe CREATE OR REPLACE PROCEDURE <nome> (<lista_argumentos>) AS / IS <declarações> BEGIN <comandos PL/SQL e SQL> END; Atenção: AS ou IS substituem o DECLARE Capítulo 1: SQL-PSM

  39. 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 de Dados • Padrão SQL + tipos genéricos como NUMBER = qualquer tipo inteiro ou real • Como tipos nas procedures devem casar com tipos no esquema do BD, pode-se usar uma expressão da forma tabela.campo%TYPE para capturar o tipo corretamente Capítulo 1: SQL-PSM

  40. Exemplo de Oracle Stored Procedure • Uma procedure que inclui uma nova cerveja e seu preço no menu do bar AlviRubro • Vende (bar, cerveja, preço) CREATE OR REPLACE PROCEDURE MenuAlviRubro ( p_cerva IN Vende.cerveja%TYPE, p_preco IN Vende.preço%TYPE) AS BEGIN INSERT INTO Vende VALUES(‘AlviRubro’, p_cerva, p_preco); COMMIT; END; / Capítulo 1: SQL-PSM

  41. Executando Stored Procedures • Uma chamada a uma procedure pode aparecer no corpo de um comando PL/SQL BEGIN MenuAlviRubro('AlviRubro','Bud', 2.50); MenuAlviRubro('AlviRubro','Carlsberg', 5.00); END; • Uma chamada a uma procedure pode aparecer no prompt da ferramenta (iSQL*Plus) • EXEC(UTE) MenuAlviRubro ('AlviRubro','Sol', 1.15); Capítulo 1: SQL-PSM

  42. Um Segundo Exemplo CREATE OR REPLACE PROCEDURE p1 (p_empid IN NUMBER, p_sal OUT NUMBER) IS BEGIN SELECT salary INTO p_sal FROM employees WHERE employee_id = p_empid; END; / CREATE OR REPLACE PROCEDURE p2 IS v_sal NUMBER; v_empid NUMBER := 101; BEGIN p1(v_empid,v_sal); DBMS_OUTPUT.PUT_LINE('O empregado '||TO_CHAR(v_empid)||' recebe '||TO_CHAR(v_sal)); END; / SQL> SET SERVEROUTPUT ON SQL> EXECUTE p2 O empregado 101 recebe 17000 Atenção: Não é necessário informar o tamanho dos parâmetros Capítulo 1: SQL-PSM

  43. Oracle Stored Procedure • Atribuição de valores a variáveis é denotada por := • Desvio IF <condição> THEN <comando(s)> ELSIF <condição> THEN <comandos(s)> ELSE <comando(s)> END IF; • Laço LOOP . . . EXIT WHEN <condição> . . . END LOOP; Capítulo 1: SQL-PSM

  44. Consultas em PL/SQL • Single-row selects permitem atribuir a uma variável o resultado de uma consulta que produz uma única tupla • Um select-from-where em PL/SQL deve ter uma cláusula INTO listando as variáveis que recebem os resultados da consulta • Ocorre erro se o select-from-where retornar mais de uma tupla; neste caso, é preciso usar um cursor • Ocorre erro também se o select-from-where retornar zero tupla Capítulo 1: SQL-PSM

  45. Single-Row SELECT • Exemplo: Encontrar o preço da cerveja Schincariol no bar AlviRubro • Vende(bar, cerveja, preço) DECLARE p Vende.preço %TYPE; BEGIN SELECT preço INTO p FROM Vende WHERE bar = ‘AlviRubro’ AND cerveja = ‘Schincariol’; END; Capítulo 1: SQL-PSM

  46. Cursores • Declaração 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 é TRUE se não houver mais tupla a apontar • OPEN e CLOSE abrem e fecham um cursor Capítulo 1: SQL-PSM

  47. Criando um Cursor • Exemplo DECLARE CURSOR c IS SELECT name, salary FROM customer; v_name customer.name%TYPE; v_salary customer.salary%TYPE; BEGIN open c; fetch c into v_name, v_salary; … Capítulo 1: SQL-PSM

  48. Exemplo O código completo do exemplo está disponível no Google Groups RAD_VALS • DECLARE • pi constant NUMBER(8,7) := 3.1415926; • area NUMBER(14,2); • CURSORrad_cursorIS SELECT * FROM rad_vals; • rad_valuerad_cursor%ROWTYPE; • BEGIN • OPEN rad_cursor; • LOOP • FETCH rad_cursorINTO rad_value; • EXIT WHEN rad_cursor%NOTFOUND; • area := pi * power(rad_value.radius,2); • INSERT INTO areas VALUES (rad_value.radius, area); • END LOOP; • CLOSE rad_cursor; • COMMIT; • END; Rad_cursor Fetch Rad_val AREAS Radius Area 3 28.27 113.1 6 201.06 8 Capítulo 1: SQL-PSM

  49. Atributos Explícitos de Cursor • Obtém informação de status sobre um cursor Atributo Tipo Descrição %ISOPEN Boolean Retorna TRUE se o cursor estiver aberto %NOTFOUND Boolean Retorna TRUE se o fetch mais recente não retorna uma tupla %FOUND Boolean Retorna TRUE se o fetch mais recente retorna uma tupla (complemento de %NOTFOUND) %ROWCOUNT Number Retorna o total de tuplas acessadas até o momento Capítulo 1: SQL-PSM

  50. Exemplo • Uma procedure que examina o menu do bar AlviRubro e aumenta em 1.00 todos os preços que são menores que 3.00 • Vende(bar, cerveja, preço) • Um simples UPDATE poderia resolver esse problema, mas mudanças mais complicadas podiam exigir uma procedure Capítulo 1: SQL-PSM

More Related