Estude fácil! Tem muito documento disponível na Docsity
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Prepare-se para as provas com trabalhos de outros alunos como você, aqui na Docsity
Encontra documentos específicos para os exames da tua universidade
Prepare-se com as videoaulas e exercícios resolvidos criados a partir da grade da sua Universidade
Responda perguntas de provas passadas e avalie sua preparação.
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Apostila PL-SQL
Tipologia: Notas de estudo
1 / 55
Esta página não é visível na pré-visualização
Não perca as partes importantes!
Apostila elaborada pelo Professor Antonio Cesar de Barros Munari Colaboração: Prof.ª Maria Angélica Calixto de Andrade Cardieri
Ago/
Como vimos, o PL/SQL é uma extensão da linguagem SQL, que permite a construção de blocos de comandos SQL para acesso e manipulação das bases de dados. Estes blocos podem estar estruturados de duas formas:
A programação através da linguagem procedural (PL/SQL) apresenta diversas vantagens destacando-se:
Permite a construção de módulos : A construção em blocos quebra a complexidade de problemas gerando um conjunto de módulos lógicos, facilitando o gerenciamento. É possível a construção de blocos aninhados.
Declaração de variáveis : Declara variáveis, constantes e exceções que podem ser usadas em declarações procedurais e SQL.
Fluxo de Controle : Permite a utilização de comandos de desvio condicionais e incondicionais e também loops para seqüência de declarações.
Tratamento de erros : Processa erros do servidor com rotinas de tratamento de exceções, declara condições de erro definidas pelo usuário e as processa pelas rotinas de tratamento de exceções.
Portabilidade: Pode ser usado em qualquer plataforma que acesse um banco de dados Oracle.
Peformance: Pode aumentar o desempenho de aplicações críticas por ser uma ferramenta integrada ao Oracle.
Declare: Área opcional, destinada a declaração de variáveis, constantes cursores e exceções. Begin: Área destinada a seqüência de comandos a serem executados. Contém declarações SQL para manipular dados no banco de dados e declarações PL/SQL. Exception : Área destinada ao tratamento de exceções, isto é erros e condições anormais que ocorrerem na seção de execução (begin).
Declare wcarga_horaria number(5); Begin select carga_horaria into wcarga_horaria from curso where cod_curso = 5; if wcarga_horaria is null then update curso set carga_horaria = 72; commit; end if ; exception when no_data_found then insert into tab_erro values ( ‘ valor não encontrado’); end;
Observações:
Observe que a subrotina é executada pelo SGBD, na máquina servidora, a pedido da estação cliente.
Existem 3 tipos básicos de subrotinas de banco de dados: os procedimentos, as funções e os gatilhos.
Também chamados de procedimentos armazenados ( stored procedures ), representam porções de código SQL e não SQL que ficam armazenados de forma compilada no catálogo do SGBD e são ativados explicitamente por aplicações, triggers ou outras rotinas. Como nas linguagens tradicionais de programação, um procedimento realiza um processamento qualquer e não devolve valor algum em seu final.
Exemplo:
Ativação Procedimento
EXEC CrDep(‘D8’,‘COMPRAS’) CREATE PROCEDURE CrDep( p1 IN VARCHAR2, p2 IN VARCHAR2 ) AS BEGIN INSERT INTO Depto VALUES ( p1, p2 ) ; END ;
Neste material utilizaremos o termo Procedure para referenciar os procedimentos armazenados.
A exemplo das procedures, as funções contêm porções de código SQL e não SQL; ficam armazenadas de forma compilada no catálogo do SGBD e são ativadas explicitamente por aplicações, triggers ou outras rotinas. A diferença básica entre o procedimento e a função é que uma função, ao realizar um processamento, produz um valor que é devolvido (retornado) em seu final.
Exemplo:
Ativação Função
INSERT INTO teste VALUES (1, CalcDobro(1));
Ou
SELECT NmFunc, CalcDobro( VrSalario ) FROM Funcionario ;
CREATE FUNCTION CalcDobro ( p1 IN NUMBER ) RETURN NUMBER IS
p2 NUMBER ; BEGIN p2 := p1 * 2 ; RETURN p2 ; END ;
023 raise_application_error ( 0002, ‘Salario invalido’ ) ; 024 WHEN OTHERS THEN 025 raise_application_error ( 9999, ‘Erro geral’ ) ; 026 END ;
Esse exemplo representa um módulo PL / SQL típico (uma procedure, no caso), e permite visualizar 4 regiões básicas, conforme o esquema:
Assinatura da rotina (opcional) Linha 001
Área de declarações (opcional) Linhas 003 e 004
Área de instruções (obrigatória) Linhas 006 a 017
Área de exceções (opcional) Linhas 019 a 025
A primeira área é da a Assinatura da rotina , que contém a declaração do nome e do tipo da subrotina, ou seja, se é uma procedure, função ou trigger, por exemplo. Também indica a lista de parâmetros que o módulo recebe (se houver algum) e, caso seja uma função, indica também a variável que conterá o seu valor de retorno. A rigor, a assinatura seria apenas esse conjunto de informações, mas na linha inicial aparece também o comando CREATE necessário para gerar a rotina no SGBD. Esta área é opcional, pois podemos ter rotinas sem nome ( blocos anônimos) executadas apenas uma vez via linha de comando. A segunda parte é a Área de declarações gerais, onde são definidas as variáveis, constantes, cursores e exceções utilizadas pelo módulo. É opcional e, conforme o tipo da rotina, pode requerer seu início com a palavra reservada DECLARE, como nos casos dos blocos anônimos e triggers, por exemplo.
Em seguida, temos a única área obrigatória de um módulo, a Área de instruções (ou comandos). É nela que colocamos a lógica da rotina. Inicia-se com uma declaração BEGIN.
Finalmente, temos uma área opcional chamada Área de exceções , onde são colocadas instruções para o tratamento de erros na rotina, de acordo com um mecanismo interno disponível no SGBD e que pode sofrer também algumas adaptações por parte do programador.
Toda rotina termina com um END, de maneira a fechar o BEGIN inicial da área de instruções. Comentários iniciam-se com os caracteres “--” (dois hifens seguidos) e prosseguem até o fim da linha atual, conforme pode ser observado nas linhas 4 e 18 do exemplo anterior.
Com base no modelo da vídeo-locadora definido em aulas anteriores, calcular o valor da multa na devolução da locação, considerando R$2,00 reais por dia de atraso. Atualizar a tabela de locação (campo valorloc acrescido do valor da multa). O número da locação deve ser passado como parâmetro. Usar exception para testar se o número da locação não existe gravando mensagem de erro (tab_erro).
O conceito de variável e de constante do PL / SQL é o mesmo das outras linguagens estruturadas: são regiões de memória que possuem um nome definido pelo programador e podem conter valores de um determinado tipo de dado. O conteúdo de uma variável pode se modificar durante a execução da rotina, enquanto que o valor da constante permanece fixo. Os tipos de dados permitidos são os seguintes:
Tipo do Dado Subtipos Descrição
NUMBER DEC, DECIMAL, DOUBLE, PRECISION, FLOAT, INT, INTEGER, NUMERIC, REAL, SMALLINT
Permite criar variáveis ou constantes que armazenem números fixos ou de ponto flutuante com precisão de até 38 dígitos.
BINARY_INTEGER NATURAL, POSITIVE Indicado para a criação de variáveis ou constantes que armazenem números inteiros com sinal, de forma a acelerar algumas operações com inteiros no Oracle. A faixa de valores válidos vai de -2 31 -1 até 2^31 -1.
CHAR CHARACTER, STRING Permite texto de tamanho fixo até 32Kb.
VARCHAR2 VARCHAR Permite texto de tamanho variável de até 32Kb.
DATE Utilizado para armazenar datas, horas, minutos e segundos.
BOOLEAN Indicado para valores lógicos TRUE e FALSE.
RECORD Utilizado para construir tipos complexos estruturados, como imagens de registros de tabelas, por exemplo.
TABLE Utilizado para conter conjuntos de dados com várias ocorrências.
É possível também utilizar os atributos especiais %TYPE e %ROWTYPE para fazer com que uma variável ou constante herde o tipo de dado de um campo ou registro de tabela. Observe os exemplos a seguir.
Exemplo 1:
NmUser VARCHAR2( 30 ) ; TxUnica CONSTANT INTEGER := 2 ; DtSist DATE := SYSDATE ; QtFunc NUMBER( 5, 0 ) ; VrSalario Funcionario.VrSalario%TYPE ; QtCaixas QtFunc%TYPE ;
Exemplo 2:
DECLARE TYPE RegCargo IS RECORD ( CdCargo CHAR( 2 ) NOT NULL := 0 , NmCargo CHAR( 30 ));
vCargo RegCargo ;
Variável Tipo de Dado Conteúdo USER Caracter Nome do usuário corrente SYSDATE Date Data e hora atuais do sistema UID Caracter / Number Número de identificação do usuário corrente
Quando for necessário obter o valor de uma dessas variáveis a partir de um comando SELECT que não referencia diretamente nenhuma tabela ou view normal do banco de dados, deve-se indicar a tabela especial DUAL na cláusula FROM do comando, conforme o exemplo a seguir.
SELECT SYSDATE, USER FROM Dual ;
São as estruturas básicas que permitem compor uma lógica procedural em uma rotina, como os desvios e repetições.
É utilizado para permitir que o fluxo do programa seja desviado para um determinado ponto da lógica, conforme o resultado de uma comparação feita. Existem 3 variações desta estrutura, cobrindo desde o caso mais simples até possibilidades de desvio múltiplo.
Variação 1: Desvio simples, sem cláusula ELSE
IF
Variação 2: Desvio simples, com cláusula ELSE
IF
Variação 3: Desvio múltiplo
IF
<operação1> ; vConceito := ‘E’ ; ... ELSIF vNota > 8 THEN <operaçãoN> ; vConceito := ‘A’ ; ELSIF
É uma estrutura de repetição simples, cuja saída é indicada pela instrução EXIT. É possível testar a condição de saída através de uma estrutura IF / END IF conforme o exemplo abaixo ou, como é mais recomendável, através da cláusula opcional WHEN na própria instrução EXIT, conforme mostra o segundo exemplo.
Sintaxe básica: Exemplo 1:
<operação1> ; LOOP ... ... <operaçãoN> ; vCont := vCont + 1 ; EXIT [ WHEN
Exemplo 2:
vCont := vCont + 1 ; EXIT WHEN vCont > 10 ; ... END LOOP ; ...
O cursor explícito é uma área de armazenamento de dados que tem por finalidade armazenar uma coleção de linhas recuperadas por um select.
Existem dois tipos de cursores: Implícito: Criado pelo PL/SQL para todos os comandos DML e consultas. Explícito: São os declarados pelo usuário. São utilizados em consultas e permitem processar múltiplas linhas de uma query.
Cursores implícitos:
Um cursor deste tipo é implementado através da colocação da cláusula INTO no SELECT, conforme pode ser visto no exemplo a seguir.
001 CREATE OR REPLACE FUNCTION AchaFunc ( pNrMatric IN CHAR ) RETURN CHAR IS 002 003 vResult CHAR( 4 ) ; 004 005 BEGIN 006 SELECT Funcionario.NrMatric 007 INTO vResult 008 FROM Funcionario 009 WHERE Funcionario.NrMatric = pNrMatric ; 010 RETURN vResult ; 011 012 EXCEPTION 013 WHEN NO_DATA_FOUND THEN 014 RETURN ‘9999’ ; 015 END ;
Observe na linha 007 a presença da cláusula INTO, indicando que o valor retornado pelo comando será colocado na variável vResult, declarada na rotina. Caso houvesse mais de um valor a ser colocado em variáveis, a cláusula INTO deveria conter uma lista com os nomes de todas as variáveis, separadas por vírgula. Essas variáveis deveriam aparecer em uma ordem compatível com os campos projetados pelo SELECT.
Dois cuidados básicos devem ser tomados ao utilizar-se cursores implícitos:
a) as variáveis que receberão os dados obtidos pelo SELECT deverão ser declaradas com tipo igual ao do campo correspondente na tabela, o que torna bastante indicado nesses casos utilizar o atributo %TYPE ao declarar a variável, para evitar problemas de incompatibilidade; b) o comando deve retornar no máximo uma única linha, senão uma exceção TOO_MANY_ROWS será gerada.
Não há uma declaração formal do cursor, apenas das variáveis a serem atualizadas pelo comando.
Os cursores explícitos são chamados dessa forma porque são declarados formalmente na Área de declarações do módulo, ao contrário do que ocorre com os cursores implícitos. São tipicamente mais flexíveis e poderosos que os cursores implícitos, podendo substituí-los em qualquer situação.
Para sua utilização são necessários alguns passos básicos:
O exemplo a seguir ilustra a utilização de um cursor explícito equivalente àquele utilizado para demonstrar o cursor implícito. Analise as principais diferenças entre essa solução e a anterior.
001 CREATE OR REPLACE FUNCTION AchaFunc ( pNrMatric IN CHAR ) RETURN CHAR IS 002 003 vResult CHAR( 4 ) ; 004 CURSOR vCursor IS 005 SELECT Funcionario.NrMatric 006 FROM Funcionario 007 WHERE Funcionario.NrMatric = pNrMatric ; 008 009 BEGIN 010 OPEN vCursor ; 011 FETCH vCursor INTO vResult ; 012 IF vCursor%FOUND THEN 013 RETURN vResult ; 014 ELSE 015 RETURN ‘9999’ ; 016 END IF ; 017 CLOSE vCursor ; 018 END ;
Inicialmente, existe a declaração do cursor nas linhas 004 a 007, onde não aparece a cláusula INTO. O nome dado ao cursor segue as mesmas regras para os nomes de variáveis. A sintaxe básica dessa declaração é a seguinte:
CURSOR
Ao fazermos a declaração, apenas foi definida uma estrutura que será utilizada posteriormente, quando o SELECT for executado e a recuperação das linhas for realizada. A primeira instrução realmente executável relativa ao cursor é a sua abertura, feita na área de instruções através do comando OPEN. É no momento da abertura que o SELECT é executado e as linhas recuperadas tornam-se disponíveis para uso.
Sintaxe dos comandos:
Open: Executa a query e deixa disponível os valores trazidos para serem trabalhados. Open <nome-do-cursor>;
Fetch: Para processar as linhas armazenadas no cursor, é preciso carregar os valores da linha corrente em variáveis. A linha corrente é aquela em que o cursor está posicionado. Cada fetch funciona como um “read” pois faz com que o cursor mude a linha corrente para a próxima.
Fetch <nome-do-cursor> into <área-de-trabalho>;
Close: Limpa a área de trabalho do cursor liberando a memória.
Close < nome-do-cursor>;
Exemplo: Declare wcodsocio socio.codsocio%type
Neste caso a variável wcodsocio herda o tipo da coluna codsocio da tabela socio.
Atributo %ROWTYPE => Semelhante ao %type, porém declara uma variável herdando uma estrutura de dados do banco de dados Exemplo: Declare socio-reg socio%rowtype
Reg-sócio herda toda a estrutura da tabela sócio.
Além dos recursos básicos ligados aos cursores, existem três outros que merecem atenção: a possibilidade da passagem de parâmetros para os cursores; os chamados atributos de cursor e cursores explícitos via looping FOR.
Geralmente o comando SELECT de um cursor possui uma cláusula WHERE que especifica uma seleção de linhas a serem retornadas. Muitas vezes, temos necessidade de variar um dado a ser comparado nessa cláusula, e isso pode ser feito através de uma espécie de parâmetro passado para o cursor no momento de sua abertura. Observe o exemplo a seguir:
001 DECLARE 002 vMatr CHAR( 4 ) ; 003 vNome VARCHAR2( 30 ) ; 004 CURSOR LstFunc ( pSexo CHAR ) IS 005 SELECT NrMatric, NmFunc 006 FROM Funcionario 007 WHERE Sexo = pSexo ; 008 009 BEGIN 010 OPEN LstFunc( ‘F’ ) ; 011 FETCH LstFunc INTO vMatr, vNome ; 012 CLOSE LstFunc ; 013 014 OPEN LstFunc( ‘M’ ) ; 015 FETCH LstFunc INTO vMatr, vNome ; 016 CLOSE LstFunc ; 017 018 END ;
Ao abrirmos pela primeira vez, na linha 10, o cursor assume ‘F’ como sendo o conteúdo da variável pSexo, e assim, recupera apenas os funcionários do sexo feminino. Na segunda vez que é aberto, o parâmetro passado é ‘M’, e apenas os funcionários de sexo masculino são processados.
Durante a utilização de um cursor em uma rotina, uma série de valores podem ser testados, de maneira a permitir a monitoração do estado corrente do processamento. Esses valores são obtidos através de variáveis especiais mantidas pelo sistema, chamadas de Atributos do cursor. Todos eles têm seu nome começando com o símbolo “%” (sinal de porcentagem) e são referenciados colocando-se o nome do cursor imediatamente antes do “%”. A seguir um pequeno resumo com esses atributos e suas características principais.
