Rotina de Armazenamento SQLPSM PLPGSQL Funções e Procedimentos no Banco de Dados

Aula 8 JÉSSICA GUIMARÃES INSTITUTO FEDERAL Goiano Campus Avançado Catalão Rotina de Armazenamento Trecho de código armazenado no banco de dados e executado pelo SGBD Linguagem própria Padrão SQLPSM Persistent Stored Module Cada SGBD usa uma diferente Rotina pode ser Função retorna valor não altera BD Procedimento não retorna valor altera BD Rotina de Armazenamento createFunctionOrProcedure CREATE PROCEDURE FUNCTION nomeRotina nomeParametro tipoParametro RETURNS tipoRetorno corpoRotina comando de SQL ou rotina PLPGSQL Linguagem de programação procedural para sistemas de banco de dados Popularmente conhecida como PLSQL dada a popularidade desta em empresas que utilizam SGBD Oracle Instalada por padrão em distribuições PostgreSQL a partir da versão 90 Referência httpswwwpostgresqlorgdocscurrentxfuncsqlhtml Objetivos da Linguagem Criar funções e procedimentos Gatilhos triggers Adicionar estruturas de controle à linguagem SQL Realizar operações computacionais complexas Fácil de implementar Relevância Cada sentença SQL deve ser executada de modo individual pelo servidor Implementamos várias buscas selects mas enviamos uma a uma para o servidor Para cada busca fazemos um processamento que pode ser usado para uma busca futura Consequentemente se tem muito trabalho braçal ou sentenças SQL muito complexas CREATE OR REPLACE FUNCTION funcNamevarName1 varType1 RETURNS returnVarType AS DECLARE optional All Variables Declared Here BEGIN mandatory Executable statements what the block DOES EXCEPTION optional Exception handling END mandatory language plpgsql DECLARE userid integer preco numeric url varchar minhalinha nometabelaROWTYPE meucampo nometabelanomecolunaTYPE quantidade integer DEFAULT 32 uri varchar httpmysitecom k CONSTANT integer 10 CREATE FUNCTION primeirafuncao RETURNS INTEGER AS SELECT 53 2 LANGUAGE SQL SELECT primeirafuncao Funções Nós não podemos alterar uma função informando tipos diferentes em seus parâmetros ou retornos Para atingir este objetivo precisamos excluir e criar esta função PLPgSQL não é sensitive case Parâmetros de Funções Os parâmetros são as variáveis importantes para a chamada da função Sintaxe nome tipo de cada parâmetro separados por vírgula Não precisam ser declarados por nomes O PostgreSQL identifica por 1 2 etc na ordem em que aparecem Diminui a legibilidade Exemplos DROP FUNCTION nometabela CREATE OR REPLACE FUNCTION CRIAANOME VARCHAR RETURNS VARCHAR AS INSERT INTO A NOME VALUES Maria Helena LANGUAGE SQL CREATE FUNCTION SEMRETORNONOME VARCHAR RETURNS VOID AS INSERT INTO A NOME VALUESSEMRETORNONOME LANGUAGE SQL SELECT SEMRETORNOJosé Vitor Função com Parâmetros Compostos É possível passar como parâmetro os tipos primitivos e tipos compostos ou seja um conjunto de tipos inclusive o próprio nome da tabela A linha de dados pode ser passada como parâmetro e usando a sintaxe nometabela sem o também funcionaria Exemplo CREATE TABLE INSTRUTOR ID SERIAL PRIMARY KEY NOME VARCHAR255 NOT NULL SALARIO DECIMAL102 INSERT INTO INSTRUTOR NOME SALARIO VALUES Mariana Silva 300 INSERT INTO INSTRUTOR NOME SALARIO VALUES João Henrique 400 INSERT INTO INSTRUTOR NOME SALARIO VALUES Marcelo Castro 500 INSERT INTO INSTRUTOR NOME SALARIO VALUES Tatiana Silva 600 RETORNA DOBRO DO SALÁRIO CREATE FUNCTION DOBROINSTRUTOR RETURNS DECIMAL AS SELECT 1SALARIO 2 AS DOBRO LANGUAGE SQL SELECT NOME DOBROINSTRUTOR FROM INSTRUTOR Exemplo RETORNO COMPOSTO CREATE FUNCTION INSTRUTORFALSO RETURNS INSTRUTOR AS SELECT 22 Nome Falso 200DECIMAL LANGUAGE SQL SELECT INSTRUTORFALSO SELECT FROM INSTRUTORFALSO RETORNAR UM CONJUNTO DE INSTRUTORES CREATE FUNCTION INSTRUTORESBEMPAGOSVALORSALARIO DECIMAL RETURNS TABLE id INTEGER NOME VARCHAR SALARIO DECIMAL AS CREATE FUNCTION INSTRUTORESBEMPAGOSVALORSALARIO DECIMAL RETURNS SETOF INSTRUTOR AS SELECT FROM INSTRUTOR WHERE SALARIO VALORSALARIO LANGUAGE SQL SELECT FROM INSTRUTORESBEMPAGOS300 Parâmetros de Saída Quando queremos retornar mais de um valor podemos fazêlo utilizando parâmetros de saída Os parâmetros de saída são identificados pela palavra reservada OUT É possível especificar todos os tipos de parâmetros IN opcional OUT INOUT Exemplo CREATE FUNCTION SOMAEPRODUTO IN N1 INTEGER IN N2 INTEGER OUT SOMA INTEGER OUT PRODUTO INTEGER AS SELECT N1 N2 AS SOMA N1 N2 AS PRODUTO LANGUAGE SQL CREATE TYPE DOISVALORES AS SOMA INTEGER PRODUTO INTEGER CREATE FUNCTION SOMAEPRODUTO IN N1 INTEGER IN N2 INTEGER RETURNS DOISVALORES AS SELECT N1 N2 AS SOMA N1 N2 AS PRODUTO LANGUAGE SQL SELECT FROM SOMAEPRODUTO34 Exemplos Criar uma função armazenada que recebe dois nomes e retorna uma cadeia de caracteres contendo os nomes em minúsculas e separados por um hífen CREATE FUNCTION concatnomesmin nome1 VARCHAR255 nome2 VARCHAR255 RETURNS VARCHAR255 RETURN LOWERnome1 LOWERnome2 Exemplos Para cada disciplina obter seu nome seguido do nome de seu departamento Os nomes devem aparecer em minúsculas e separados por um hífen SELECT concatnomesmin nomedisc nomedepto FROM disciplina depto WHERE disciplinacoddepto deptocoddepto uso da função dentro de SQL Procedimento CREATE OR REPLACE PROCEDURE insereProdutodescProd VARCHAR qtde INT AS DECLARE MAXIMO INT BEGIN SELECT MAXcodprod FROM PRODUTO INTO MAXIMO INSERT INTO PRODUTO VALUES MAXIMO 1 descProd qtde END LANGUAGE PLPGSQL CALL insereProdutoleite 12 Sobrecarga de Função CREATE OR REPLACE FUNCTION insereProdutoqtde INT descProd VARCHAR RETURNS VOID AS DECLARE MAXIMO INT BEGIN SELECT MAXcodprod FROM PRODUTO INTO MAXIMO INSERT INTO PRODUTO VALUES MAXIMO 1 descpROD qtde END LANGUAGE PLPGSQL SELECT insereProduto14 soja Comandos Condicionais IFELSE Para mais de um comando dentro do IF é importante manter a identação correta IF condição THEN lista de instruções ELSEIF condição THEN lista de instruções ELSEIF condição THEN lista de instruções ELSE lista de instruções END IF Comandos Condicionais IFELSE CREATE OR REPLACE FUNCTION acimaMedia IN cod NUMERIC RETURNS BOOLEAN AS DECLARE media REAL salarioprof REAL BEGIN SELECT AVGsalario FROM professor INTO media SELECT salario FROM professor WHERE id cod INTO salarioprof IF salarioprof media THEN RETURN TRUE ELSE RETURN FALSE END IF END LANGUAGE PLPGSQL SELECT FROM acimaMedia1 SELECT FROM acimaMedia6 Exemplo No início de cada anosemestre todas ofertas do anosemestre anterior são copiadas para o novo anosemestre para a partir daí serem adaptadas às necessidades do novo anosemestre Criar um procedimento armazenado que copie as ofertas de um anosemestre para outro O procedimento deve copiar as linhas da tabela oferta bem como as linhas da tabela periodo Como parâmetros o procedimento recebe o anosemestre origem da cópia e o anosemestre destino O procedimento somente deve ser executado caso nenhuma oferta exista para o anosemestre destino INSERT INTO periodo anosem coddepto numdisc siglaofer diasem horainicio codpred numsala numhoras SELECT anosemdestino coddepto numdisc siglaofer diasem horainicio codpred numsala numhoras FROM periodo WHERE anosem anosemorigem END IF END LANGUAGE PLPGSQL CALL abreanosem 20022 20031 CREATE OR REPLACE FUNCTION factorialN INTEGER RETURNS INTEGER AS DECLARE fat INTEGER 1 i INTEGER 2 BEGIN WHILE i NLOOP fat fat i i i 1 END LOOP RETURN fat END LANGUAGE PLPGSQL SELECT factorial5 CREATE PROCEDURE ABREANOSEM ANOSEMORIGEM INTEGER ANOSEMDESTINO INTEGER AS Begin IF NOT EXISTS SELECT FROM OFERTA WHERE ANOSEM ANOSEMDESTINO THEN INSERT INTO OFERTA ANOSEM CODDEPTO NUMDISC SIGLAOFER CAPACIDADEOFER SELECT ANOSEMDESTINO CODDEPTO NUMDISC SIGLAOFER CAPACIDADEOFER FROM OFERTA WHERE ANOSEM ANOSEMORIGEM Exemplo FOR CREATE OR REPLACE FUNCTION fatorialN INTEGER RETURNS INTEGER AS DECLARE fat INTEGER 1 i INTEGER BEGIN IF N 2 THEN RETURN fat END IF FOR i IN 2N LOOP fat fati END LOOP RETURN fat END LANGUAGE PLPGSQL SELECT fatorial5