Banco de Dados II Capítulo 1: SQL-PSM

1. Banco de Dados IICapítulo 1: SQL-PSM UFCG/DSC Bacharelado em Ciência da Computação Cláudio Baptista, Ph.D.

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

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

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

5. Cursor • Um cursor pode ser visto como um ponteiro que aponta para uma única tupla(linha) do resultado da query. • Cada cursor possui uma busca 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.

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

7. 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. Sintaxe: • DELETE • FROM tabela • WHERE CURRENT OF cursor

8. Um Exemplo 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”); };

9. 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 CLIENTE WHERE CURRENT OF salCursor; else EXEC SQL UPDATE CLIENTE SET salario = salario - salario * 0.2; WHERE CURRENT OF salCursor; } EXEC SQL CLOSE salCursor; }

10. Cursor • 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;

11. Cursor • 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)

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

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

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

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

16. Stored Procedures - Vantagens • 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.

17. Stored Procedures - Vantagens • 3. Segurança: 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.

18. Stored Procedures: SQL/PSM • SQL/PSM - Persistent Stored Modules: parte do padrão SQL relativo às Stored Procedures • 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 de funções ou procedimentos, declarações de tabelas temporárias, dentre outros.

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

20. Stored Procedures -SQL/PSM Exemplo: CREATE PROCEDURE MudaEndereco ( IN endAntigo VARCHAR(255), IN endNovo VARCHAR(255) ) UPDATE Empregado SET endereco = endNovo WHERE endereco = endAntigo;

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

22. 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;

23. 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 FUNCTION 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;

24. 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;

25. 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;

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

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

28. 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;

29. PL-SQL : Oracle Stored Procedures • Linguagem de desenvolvimento do Oracle que implementa SQL/PSM • 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; • A parte DECLARE é opcional.

30. Oracle Stored Procedures • Código PL/SQL é feito de blocos com umaúnicaestrutura • Existemdoistipos de blocosem PL/SQL: • Anonymous Blocks:nãopossuemnomes (como scripts) • Podem ser escritas e executadasimediatamento no iSQLPLUS • Podem ser usadas num trigger 2. Named Blocks: • Procedures • Functions

31. Blocos Anônimos DECLARE (optional) /* aqui se declaram as variáveis que serão usadas no bloco */ BEGIN (mandatory) /* define-se os comandos que dizem o que o bloco faz*/ EXCEPTION (optional) /* define-se as ações que acontecem se uma exceção for lançado durante a execução deste bloco */ END;(mandatory) /

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

34. DeclarandoVariá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 := 'Pinta'; ... Acessando outra variável

35. Criando um PL/SQL Record Um record é um tipo de variávelquepodemosdefinir (como ‘struct’ em C ou ‘object’ em Java) DECLARE TYPE sailor_record_type IS 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; …

36. 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;

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

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

39. 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;

40. 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;

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

42. 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. • Ocorre erro se o select-from-where retorna mais de uma tupla; neste caso, é preciso usar um cursor. Exemplo • Encontrar o preço da cerveja Schincariol 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 = `Schincariol´; END;

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

44. Criando um Cursor • Examplo: • DECLARE • cursor c is select * from sailors; • sailorDatasailors%ROWTYPE; • BEGIN • open c; • fetch c intosailorData; sailorData é uma variável que pode receber uma tupla da tabela sailors

45. Exemplo DECLARE Pi constant NUMBER(8,7) := 3.1415926; area NUMBER(14,2); cursor rad_cursor is select * from RAD_VALS; rad_value rad_cursor%ROWTYPE; BEGIN open rad_cursor; fetch rad_cursorinto rad_val; area:=pi*power(rad_val.radius,2); insert into AREAS values (rad_val.radius, area); close rad_cursor; END; / RAD_VALS Rad_cursor fetch Rad_val AREAS Radius Area 3 28.27

46. Explicit Cursor Attributes Obtéminformação de status sobre um cursor. Atributo Tipo Descrição %ISOPEN Boolean Retorna TRUE is o cursor is open. %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 recuperadas.

47. Exemplo Uma procedure que examina o menu do bar Tricolor e aumenta em 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.

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

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

50. DeclarandoVariáveis com %ROWTYPE Declare umavariável com o tipo de umalinha de umatabela. E comoacessaroacampos de reserves_record? Acessando tabela Reserves reserves_record Reserves%ROWTYPE; reserves_record.sid:=9; Reserves_record.bid:=877;