Aula 05 - PL-SQL - Oracle

SAVE OFFLINE

Aula 05 SGBDs para Concursos - Curso Regular Professor: Thiago Rodrigues Cavalcanti

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

AULA 05: PL/SQL Sumário PL/SQL – Introdução ....................................................................................................................... 2 1. O que é PL/SQL? ........................................................................................................... 2 2. Vantagens ....................................................................................................................... 3 3. PL/SQL Deployment .................................................................................................... 4 4. PL/SQL Engine ............................................................................................................... 4 5. Trabalhando com as ferramentas do Oracle Database. ................................ 5 6. Vejamos um rápido exemplo de PL/SQL ............................................................. 7 7. Blocos PL/SQL ................................................................................................................ 9 7.1. Regras sintáticas do PL/SQL ................................................................................ 9 7.2. Visão geral dos blocos .......................................................................................... 10 7.2.1. DECLARE ................................................................................................................ 10 7.2.2. Escopo de identificadores ................................................................................ 18 7.2.3. BEGIN...................................................................................................................... 20 7.2.4. DML e Transações com PL/SQL..................................................................... 23 7.2.4.1. Usando o SELECT ........................................................................................... 26 8. Estruturas de controle, Cursores e Exceções .................................................. 26 8.1. Estruturas de controle .......................................................................................... 26 8.1.1. Visão geral das estruturas de controle ...................................................... 27 8.1.2. Instruções de seleção condicional ............................................................... 27 8.1.2.1. Os comandos IF .............................................................................................. 27 8.1.2.2. O comando CASE ............................................................................................ 33 8.1.2.3. Expressão CASE .............................................................................................. 36 8.1.2.4. Funções NULLIF e COALESCE .................................................................... 36 8.1.3. Instruções de loop ............................................................................................. 38 8.2. Gerenciando erros e exceções nativas ........................................................... 44 8.2.1. Gerenciando erros e exceções nativas ....................................................... 44 8.2.2. Exceções criadas pelo usuário ....................................................................... 48 8.3. Introdução a cursores........................................................................................... 50 8.3.1. Tipos de cursores ............................................................................................... 50 9. Triggers .......................................................................................................................... 55 9.1.1. Vamos definir Triggers!.................................................................................... 55 9.1.2. Before Triggers .................................................................................................... 59 9.1.3. AFTER Triggers .................................................................................................... 61 9.1.4. Transações autônomas .................................................................................... 62 9.1.5. Row e Statement Triggers .............................................................................. 64 9.1.6. Gatilhos de “Instead Of” .................................................................................. 65 9.2. Procedures ................................................................................................................ 67 9.2.1. Benefícios do Código Modular........................................................................ 67 9.2.2. Criando um Procedure ...................................................................................... 67 9.2.3. Usando os parâmetros IN e OUT.................................................................. 69 9.3. Functions ................................................................................................................... 71 9.3.1. Criando Functions ............................................................................................... 71 9.3.2. Invocando Funções ............................................................................................ 72 9.3.3. Definindo funções com a cláusula WITH ................................................... 73 9.3.4. Definindo funções com o Pragma UDF ....................................................... 74 16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

1 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 9.4. Packages .................................................................................................................... 74 9.4.1. Criando Packages ............................................................................................... 75 Questões Comentadas.......................................................................................................... 81 Considerações finais .................................................................................................................... 116 Referências ..................................................................................................................................... 116

PL/SQL – Introdução Nesta aula nosso estudo é sobre PL/SQL. O objetivo é conhecer PL/SQL e suas vantagens. Vamos, então, começar definindo o conceito de PL/SQL. Em seguida passaremos por todo o escopo definido acima. Começaremos com uma introdução ao código PL/SQL, trataremos de variáveis de identificadores, por fim, mostraremos os blocos e as instruções executáveis.

1. O que é PL/SQL? Para começarmos a entender PL/SQL precisamos primeiramente relembrar alguns conceitos sobre SQL. Uma das suas características principais, que devemos levar em consideração para nosso entendimento, é o fato de SQL ser uma linguagem declarativa. Quando escrevemos algum código em SQL estamos dizendo ao banco de dados quais as ações, ou, o que nós queremos executar. Não estamos preocupados em entender como o banco de dados vai executar a ação. Isso tonar SQL muito fácil de usar. Contudo, possui a desvantagem de não conseguirmos especificar como vamos, por exemplo, executar uma consulta sobre o banco de dados. Neste momento é que aparece o PL/SQL. Ele vem complementar a natureza declarativa da linguagem SQL oferecendo funcionalidades lógicas por meio de uma linguagem procedural (PL). Assim, conseguimos ter o melhor dos dois mundos. Podemos agora traçar uma definição para PL/SQL que é a extensão processual da Oracle para SQL. PL/SQL é uma forma natural, eficiente e segura para estender SQL. Seu ponto forte é o fornecimento de uma linguagem procedural executada no lado do servidor que é fácil de usar, integrada com SQL, robusta, portátil e segura. Assim, oferece uma plataforma robusta e de alto desempenho para aplicativos empresariais. 16712855225

Após o conhecimento da definição do termo PL/SQL vamos fazer uma rápida questão sobre o assunto.

01. BANCA: FGV ANO: 2015 ÓRGÃO: TCE-SE PROVA: ANALISTA DE TECNOLOGIA DA INFORMAÇÃO - DESENVOLVIMENTO Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

2 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 No Oracle, a linguagem procedural que permite estreito acoplamento com o SQL é conhecida como: A Data Pump; B PL/SQL; C SQL Explorer; D SQL*Loader; E Transact SQL. Gabarito B. Para não perdermos o hábito vamos comentar cada uma das alternativas com exceção da B, cuja definição nós acabamos de mostrar acima. A tecnologia Oracle Data Pump permite a movimentação em alta velocidade de dados e metadados de um banco de dados para outro. Oracle Data Pump está disponível somente a partir da versão no Oracle Database 10g. Eclipse SQL Explorer é um cliente SQL que permite consultar e navegar em qualquer banco de dados compatível com JDBC. Ele suporta plug-ins com funcionalidades especializadas para bancos de dados individuais (Oracle, DB2 e MySQL) e pode ser estendido para incluir suporte especializado para outros bancos de dados. O SQL*Loader carrega dados a partir de arquivos externos em tabelas de banco de dados Oracle. Ele tem um poderoso mecanismo de análise de dados que impõe pouca limitação sobre o formato dos dados no arquivo de entrada. T-SQL (Transact-SQL) é um conjunto de extensões de programação da Sybase e Microsoft que adiciona vários recursos a Structured Query Language (SQL), incluindo controle de transações, exceção e tratamento de erros, processamento de fila, e declaração de variáveis.

2. Vantagens A primeira vantagem que podemos listar é a portabilidade entre os sistemas operacionais. PL/SQL se preocupa apenas com o SGBD que tem que ser ORACLE. Um fato interessante é que PL/SQL é uma linguagem de texto, sendo assim, você pode utilizar qualquer editor para construir seus programas. Ao receber o código, o Oracle vai compilar e executar o mesmo quando solicitado. 16712855225

Uma segunda vantagem é a simplicidade. A sintaxe não possui elementos que gerem regras gramaticais complexas. Ou seja, depois que você aprende as estruturas básicas, a escrita de código é simples e direta. PL/SQL consiste em construções de linguagem processual, tais como declarações condicionais (se e else) e loops como (for).

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

3 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Outra vantagem é a melhoria de desempenho. O motor PL/SQL processa várias instruções SQL simultaneamente como um único bloco, reduzindo assim o tráfego de rede. Podemos citar ainda como ponto positivo a estrutura de blocos. PL/SQL consiste em blocos de código, que podem ser encaixados uns dentro dos outros. Cada unidade de bloco é constituída por uma tarefa ou por um módulo lógico. Blocos PL/SQL podem ser armazenados na base de dados e reutilizados. Por fim temos o tratamento de erros. PL/SQL manipula erros ou exceções de forma eficaz durante a execução de um programa de PL/SQL. Uma vez que uma exceção é detectada, podem ser tomadas medidas específicas, dependendo do tipo de exceção ou pode ser exibida para o usuário com uma mensagem.

3. PL/SQL Deployment Existem diferentes situações que você pode encontrar programas PL/SQL. Podemos citar os scripts, programas armazenados, objetos Oracle e embutidos em um programa. O script é basicamente um conjunto de instruções PL/SQL que podem ser executadas usando o SQL*Plus. Uma sessão do aplicativo que roda no cliente envia o script para execução no lado do servidor. O script pode estar embutido em algum código de aplicação. Suponha que você está desenvolvendo um aplicativo em Java e coloca dentro do código um conjunto de instruções PL/SQL. Esse código será enviado para processamento no servidor Oracle por meio de uma conexão com o banco dados toda vez que o código for executado. O risco de se utilizar deste mecanismo é que mudanças feitas no banco de dados podem gerar a necessidade de alterações no código do programa. Você pode ter programas armazenados como Triggers ou Stored Procedures. Isso não é nada além de um código que é armazenado no servidor de banco de dados Oracle. É considerado extremamente rápido. E uma vez escrito pode ser chamado ou executado diversas vezes por usuários diferentes. 16712855225

Unidades de programas sobre os objetos Oracle também podem ser definidas. Com a adição de tipos de objetos e outros tipos de SQL, PL/SQL foi aprimorado para operar sobre novos tipos de objetos sem problemas. Assim, os desenvolvedores de aplicativos podem usar PL/SQL para implementar a lógica e operações em tipos definidos pelo usuário (UDF) que são executados no servidor de banco de dados.

4. PL/SQL Engine

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

4 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 O Motor PL/SQL é o sistema de compilação e execução das unidades de código PL/SQL. O motor pode ser instalado no banco de dados ou em uma ferramenta de desenvolvimento de aplicações, como Oracle Forms. Em qualquer ambiente, o motor PL/SQL aceita como entrada qualquer unidade válida de código PL/SQL. O motor executa as instruções processuais, mas envia instruções SQL para o mecanismo do SQL no banco de dados, como mostrado na figura abaixo.

Normalmente, o banco de dados processa as unidades de instruções PL/SQL. Quando uma ferramenta de desenvolvimento de aplicativo processa unidades de PL/SQL, ela passa para o seu motor PL/SQL local. Se um script PL/SQL não contém instruções SQL, o mecanismo processa localmente todo código do script PL/SQL. Isso é útil se a ferramenta de desenvolvimento de aplicações puder se beneficiar do controle condicional e iterativo. Por exemplo, as aplicações desenvolvidas em Oracle Forms, frequentemente, usam instruções SQL para testar os valores de entradas de campo e fazer cálculos simples. Usando PL/SQL em vez de SQL, estas aplicações podem evitar chamadas para o banco de dados. 16712855225

5. Trabalhando com as ferramentas do Oracle Database. Para desenvolvermos algo dentro dos bancos de dados Oracle precisamos conhecer as interfaces. Existe uma variedade de ferramentas para acessar o banco de dados que oferendem funcionalidades distintas. Podemos citar: Oracle SQL Developer, Oracle Enterprise Manager SQL Worksheet, Oracle SQL Plus, Oracle Application Express, Oracle JDeveloper Studio. Conexões com o banco de dados

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

5 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Para o Oracle permitir que você comece a trabalhar com qualquer ferramenta é preciso passar por um mecanismo de autenticação. Ele é a primeira barreira de segurança que vai garantir o acesso ao SGBD. Uma autenticação de banco de dados é composta dos seguintes elementos: nome do usuário, senha e papeis. Por default, você deve informar um nome de uma conta do banco de dados. Essa conta é geralmente criada pelo DBA. Associada ao nome você deve fornecer a senha de segurança. Na maioria dos casos esse nome deve ser o mesmo do seu login. Os papeis ou roles definem quais as suas intenções ou os privilégios que você terá. Quando um DBA se conecta ele pode usar SYSOPER, SYSDBA ou SYSASM. Outro ponto que você precisa entender é a conexão ao banco de dados. Usuários são autenticados contra instâncias de banco de dados. Essa instância pode ser identificada pelas definições de conexão ao banco de dados. São basicamente três definições geralmente armazenadas no arquivo TNSnames.ora. Primeiramente temos o nome do host, que é o endereço IP do host do servidor de banco de dados. Temos também a porta TCP que define onde o banco de dados recebe requisições no host. Por fim temos o SID, que define o identificador de sistema, que deve ser um nome único para o servidor. A primeira coisa que devemos fazer é criar uma conexão ao banco de dados. É possível estar conectado com várias máquinas ao mesmo tempo. Bind de Variáveis. O bind ou ligação de variáveis é um método para fazer o código SQL ou PL/SQL dinâmico, fazendo nossos scripts mais dinâmicos e flexíveis. Caso você não queira definir valores para variáveis de forma estática. Usamos dois pontos (:) para identificar uma variável que deve ser definida pela pessoa que chamou o código, ou seja, em tempo de execução. Conhecendo o SQL Developer

16712855225

SQL Developer é uma ferramenta interface gráfica. Possui as mesmas funcionalidades do SQL*Plus, contudo de forma melhor apresentável. Apresentamos abaixo a interface da ferramenta.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

6 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

6. Vejamos um rápido exemplo de PL/SQL Vamos usar o SQL*Plus para executar alguns comandos. Quando você instala o ORACLE você já tem o SQL*Plus. Primeiramente vamos abrir o programa por meio da interface de linha de comando. Você deve passar como parâmetro a conta ou nome do usuário que vai fazer o acesso. Logo em seguida é necessário informar a senha para ter acesso à conexão com o banco de dados. Em seguida, nós queremos executar algum código ou comando na interface. Para isso vamos nos utilizar de um arquivo que já possui instruções PL/SQL e do operador ‘@’. No exemplo abaixo utilizamos o seguinte comando: @ c:\test\code.sql. Após pressionar a tecla enter, o conteúdo do arquivo será executado. Em seguida, no prompt de comando, será exibida uma mensagem solicitando que você informe o número do ssn. Quando você digitar um valor e apertar novamente no enter o código será processado com sucesso. Observem a sequência de passo que acabamos de descrever na figura abaixo:

16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

7 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Agora vamos supor que queremos analisar o código que acabamos de executar. Para isso usaremos o comando list. Observem que enter_ssn é um parâmetro de entrada, caracterizado pelo ‘&’ antes do termo.

O exemplo acima mostra um script PL/SQL. Veja que contamos com a presença da estrutura de comandos DECLARE, BEGIN, EXCEPTION e END. Entre o BEGIN e o END está inserido o código do nosso programa. DECLARE e BEGIN informam ao SQL*Plus que um bloco de programa será iniciado. A última linha da figura apresenta o comando: ‘set serveroutput on;’. Ele basicamente avisa ao SQL*Plus que ele deve exibir o resultado da consulta ao servidor no prompt de comando. Para desligar esse opção basta usar o comando ‘set serveroutput off;’. Observem que ao final de cada linha utilizamos o ‘;’ para indicar o final do comando. O ponto e vírgula não aloca o código inserido no buffer, quem faz isso é o ‘.’. Usamos o ‘.’ para indicar que terminamos de digitar nosso código. Por fim, temos que conhecer os comandos RUN e ‘/’ que servem para compilar e executar o código. Se quisermos modificar o comando do arquivo code.sql, podemos fazê-lo, usando o comando edit. Esse comando, no Windows, vai abrir uma nova janela do notepad para que possamos editar e salvar as mudanças. 16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

8 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 A substituição de variáveis é suportada por meio do ‘&’, vimos isso quando inserimos o valor para a variável enter_ssn. Comandos PL/SQL podem escrever no buffer de saída usando o comando DBMS_OUTPUT() para que isso aconteça. Observem na figura acima o uso da ‘/’. Quando colocamos uma barra invertida queremos dizer para o Oracle executar o código.

7. Blocos PL/SQL As instruções PL/SQL são organizadas em unidades conhecidas como blocos. Vamos falar agora das estruturas de blocos PL/SQL. Alguns desses blocos são obrigatórios e outros opcionais. Veja a tabela abaixo para conhecer a descrição de cada uma desses blocos. Seção

Obrigatório Descrição

DECLARE

Não

BEGIN Lógica do programa EXCEPTION END

Sim Sim Não Sim

Declara os objetos internos do programa, por exemplo, as variáveis. Marca o início da lógica do programa. Essa parte contém o código PL/SQL e os comandos SQL. Marca o início do contexto de exceções. Marca o fim da lógica do programa.

Vejam que se o BEGIN é mandatório, por lógica o END também deve ser. Antes de continuarmos mostrando detalhes de cada um desses blocos vamos trazer algumas informações a respeito da sintaxe da linguagem.

7.1. Regras sintáticas do PL/SQL Vamos entender primeiramente como fazer comentários dentro do código. Semelhante a todas as outras linguagens de programação você deve ter condições de comentar seu código. Os comentários no PL/SQL podem ser feitos de duas formas. Para comentar todo o conteúdo de uma linha você usa o '--'. Usando essa sintaxe você informa ao compilador do Oracle que todo o texto daquela linha após o '--' não deve ser considerado. Essas informações representam apenas comentários e devem ser usadas para o entendimento humano do código. 16712855225

Se você quiser fazer um comentário em múltiplas linhas você deve usar os caracteres ‘/*’ como delimitador de início e ‘*/’ como delimitador de fim do comentário. Esse tipo de comentário é definido exatamente da mesma forma para outras linguagens como C, C++ e Java. Você pode colocar a quantidade de conteúdo texto que você quiser dentro de um bloco de comentário de múltiplas linhas. Esse tipo de comentário é útil se você quiser desabilitar uma parte do código. Vejam abaixo um exemplo de comentário.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

9 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Quando você estiver escrevendo um código PL/SQL deve ter em mente algumas regras. A primeira delas é que cada uma das linhas deve ter apenas um comando PL/SQL. Todo comando de execução deve terminar com um ponto e vírgula. Mesmo que você tenha um ponto e vírgula, você não pode escrever outra instrução executável na mesma linha. Observem o comando abaixo.

Ai você deve estar se perguntando: por que as duas primeiras linhas não possuem o `;`? Comandos que representam apenas uma label para uma parte do código não terminam com ponto e vírgula. É o caso do BEGIN e do FOR, presentes na figura acima. Se colocarmos uma ‘/’ (slash) dentro do código PL/SQL estamos pedindo para executar o código. Então, supondo que você tenha um código PL/SQL e a última linha deste código seja uma barra, quando você carregar o código no SQL*Plus ele irá imediatamente executar.

7.2. Visão geral dos blocos Já vimos que a construção de código na linguagem PL/SQL depende da existência de blocos. Após apresentarmos os blocos opcionais e obrigatórios, vamos tentar entender o que podemos ou devemos colocar em cada parte do nosso código. 16712855225

7.2.1. DECLARE Vamos agora falar rapidamente sobre cada um dos blocos. O primeiro deles seria o bloco DECLARE. Sabemos que ele é opcional, mas sugerimos que você use para manter a completude. É importante para facilitar o entendimento do código.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

10 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Quando digitamos DECLARE na interface do SQL*Plus, o programa entende que vamos começar a digitar um novo bloco de comando. Desta forma ele entra no modo de edição, passa para a próxima linha incluindo o numeral 2 no início. Você pode então começar a digitar seu código, declarando variáveis, por exemplo.

Objetos na cláusula DECLARE A cláusula DECLARE é utilizada para definir ou declarar os objetos internos dos programas, como as variáveis. PL/SQL permite basicamente os seguintes objetos: Variable, Boolean, Constant, Record, Table, User-defined type. Uma variável é um elemento interno do programa que tem a capacidade de armazenar e modificar seu valor. Boolean é um tipo simples que permite apenas os valores TRUE, FALSE e NULL. Uma constante é similar a uma variável, mas seu valor não pode ser modificado. Um Record ou registro é um objeto complexo que se baseia na estrutura de uma tabela, o registro é basicamente uma linha de uma tabela com os valores para cada coluna. Temos ainda o objeto tabela que é composta basicamente por uma lista de registros. Sua estrutura deve ter a mesma definição da tabela armazenada no banco de dados. O último tipo de objeto que podemos criar é o tipo definido pelo usuário. Ele combina alguns tipos predefinidos para criar um novo tipo específico para o programa. Um exemplo interessante para entender esses tipos é o Bônus que algumas empresas pagam ao funcionário após o balanço. Alguns podem receber seu Bônus em dinheiro, outros podem receber em viagens, ações, dias de folga, etc. Um tipo definido pelo usuário poderia ser capaz de representar essas diferentes categorias de Bônus. Regras para nomes de objetos 16712855225

O tamanho máximo deve ser 30 caracteres. O primeiro caractere dever ser uma letra. Alguns caracteres especiais são considerados legais, os principais são $, # e _. Outros são considerados ilegais, os mais relevantes são &, - (dash), /. Não devemos, ainda, usar espaço na definição de nomes. Observe que os caracteres não permitidos citados acima são reservados para outras funções do PL/SQL. Também não podemos usar as palavras reservadas, por exemplo, as que representam tipos de dados, number, char ...

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

11 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 02. BANCA: FCC ANO: 2014 ÓRGÃO: TCE-RS PROVA: AUDITOR PÚBLICO EXTERNO - TÉCNICO EM PROCESSAMENTO DE DADOS O PL/SQL representa a linguagem de consulta de dados utilizada pelo sistema gerenciador de bancos de dados Oracle. Nessa linguagem há o uso de identificadores. Há apenas identificadores válidos do PL/SQL em: A meu prog, grande_programa, inicio&fim, gerador. B a&b, resta&1, ultimo-teste, entre2. C a/b, hoje, base$10, data12. D teste, primeiro_passo, abc$123, caso#1. E uma-vez, passo#3, um/dois, fase 8. Gabarito D. Variáveis devem ter tamanho máximo de 30 caracteres, começar com letra e podem conter números, underline( _ ), cifrão($) ou sustenido(#). Por outro lado, não devem conter os caracteres: ‘&’ , ‘/’, além de espaços em branco. Tipos de dados disponíveis Vamos agora apresentar os tipos disponíveis de variáveis que podemos utilizar dentro do nosso código. Quando uma variável é declarada ela deve ter associada a ela um tipo de dados. Alguns tipos são considerados escalares, vamos começar apresentando esses tipos que tem como principal característica possuírem apenas um valor para cada registro. Os primeiros tipos escalares são os tipos inteiros. Esses tipos permitem apenas o armazenamento de números, eles não possuem casas decimais. Os tipos inteiros são BINARY_INTERGER, PLS_INTERGER e SIMPLE_INTEGER. Os dois primeiros são sinônimos. SIMPLE_INTERGER está disponível apenas nas versões 11g ou superior. Todos possuem o range variando entre -2G e +2G. Os tipos inteiros têm o que chamamos de subtipos. Ele muda dinamicamente o overflow de um tipo. Ele pode ser NATURAL que só admite valores entre zero e 2G ou POSITIVE que admite valores entre um e 2G. 16712855225

Os próximos tipos numéricos que vamos apresentar são os tipos de dados de ponto flutuante (floating point data types). Eles permitem casas decimais. São caracterizado dentro deste tipo BINARY_FLOAT, BINARY_DOUBLE e NUMBER (precision,scale). O tipo BINARY_FLOAT armazena um valor com precisão de 32 bits, ele oferece uma precisão maior do que o NUMBER, além de suportar infinity e NaN (Not a number). BINARY_DOUBLE fornece uma precisão de 64 bits e é perfeito para aplicações cientificas que exigem uma alta precisão computacional. O tipo de dados NUMBER armazena números fixos e de ponto flutuante. Números de Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

12 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 qualquer magnitude podem ser armazenados e possuem portabilidade garantida entre diferentes sistemas de banco de dados Oracle que operam, até 38 dígitos de precisão. Você também pode especificar uma precisão (número total de dígitos) e a escala (número de dígitos à direita do ponto decimal).

03. BANCA: FCC ANO: 2014 ÓRGÃO: JUDICIÁRIO - BANCO DE DADOS - DBA

TJ-AP

PROVA:

ANALISTA

Na linguagem PL/SQL, deseja-se especificar um atributo numérico com 5 dígitos no total, sendo 2 desses dígitos após a vírgula. A forma para representar tal atributo é A NUMBER (3, 2). B NUMBER (5, 3, 2). C NUMBER (5, 2, 3). D NUMBER (5, 2). E NUMBER (2, 3). Gabarito D. Para resolver essa questão basta ter o entendimento dos conceitos de precisão e escala que acabamos de comentar. Eles são parâmetros passados na definição de uma variável do tipo Number. Desta forma, podemos construir o comando que define o tipo para o atributo solicitado no enunciado: NUMBER(5,2). Observamos a resposta na alternativa D que é o gabarito da questão. Agora vamos analisar os tipos de dados de caracteres. Esse tipo permite o armazenamento de números e texto. Nesta categoria temos o char(n) e varchar2(n). CHAR(n) representa uma string de caracteres de tamanho fixo que pode variar entre 1 e 32767. Ele possui um tamanho definido. Caso você declare uma variável do tipo CHAR com comprimento (n) igual a 15 e insira um dado nesta variável com tamanho menor que 15, o Oracle vai completar com espaços até atingir o tamanho da variável. 16712855225

VARCHAR2(n) representa uma string de caracteres de tamanho variável. Neste caso n é o tamanho máximo da string que pode ser inserida nesta variável. Os próximos tipos de dados que vamos considerar são os tipos de dados predefinidos que permitem diferentes tipos de dados e um maior nível de controle. Podemos listar os tipos: ROWID (obsoleto), UROWID, BOOLEAN, DATE. ROWID representa o ID do objeto da linha do banco de dados e identifica uma linha física na tabela. ROWID foi substituído pelo UROWID. UROWID armazena o ID lógico ou físico para uma determinada linha. BOOLEAN possui apenas os valore TRUE, FALSE e NULL, são usados para testes Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

13 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 condicionais. DATE registra a data e a hora. É importante armazenar suas datas em objetos do tipo DATE, pois ele faz validações dos dados, não permitindo que você armazene 31 de novembro, por exemplo. DATE ainda possui operadores que permite que você manipule suas datas. Existe dentro do ORACLE uma lista de sinônimos para os tipos de dados do ORACLE. Eles são importantes para compatibilidade com outros bancos de dados não Oracle, por exemplo, DB2 e SQL Server. Eles não têm seu uso recomendado, ao menos que você esteja interessado em acessar um banco de dados não ORACLE. Vejam a tabela com a lista dos principais sinônimos abaixo.

Declarando tipos de dados simples Quando declaramos uma variável a primeira coisa que nós fazemos é atribuir um nome para ela. Em seguida definimos um tipo, ou qual tipo de dados essa variável vai armazenar. Ao final da linha de comando colocamos um ‘;’ indicando o final da linha. Além do nome e tipo da variável, é possível definir outras características. Podemos atribuir um valor default, garantir que uma variável é NOT NULL ou ainda declarar um valor constante. Quando queremos definir um valor padrão para uma variável podemos usar o operador de atribuição ‘:=’. Se a atribuição de valor default não for feita, a variável terá seu valor padrão NULL. Outra maneira de atribuir um valor padrão é usar a cláusula DEFAULT conforme apresentado abaixo. 16712855225

Outra coisa que podemos fazer quando estamos declarando uma variável é indicar para o sistema que não queremos que valores nulos sejam atribuídos a mesma. Semelhantemente a uma restrição de banco de dados, você pode aplicar os termos NOT NULL para restringir o domínio da variável. Quando fazemos isso, caso o script tente atribuir um valor nulo para a variável, uma exceção será lançada. Ao utilizar o NOT NULL é importante que você defina o valor inicial para a variável para evitar problemas. Veja o exemplo abaixo:

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

14 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Vamos agora aprender como declarar uma constante. Constantes são similares a variáveis, exceto pelo fato de que elas não podem ter seus valores modificados. Para declarar uma constante, basta colocar a palavra chave CONSTANT antes da declaração do tipo. Vejamos um exemplo.

Uma boa pratica de programação PL/SQL é definir as constantes antes de definir as variáveis no bloco DECLARE. Declarando tipos de dados complexos Os tipos complexos permitem uma integração mais genuína entre o PL/SQL e o banco de dados ORACLE. Vamos começar apresentando o funcionamento do %TYPE e do %ROWTYPE. O comando %TYPE pode ser utilizado de duas formas. Na primeira você informa o nome_da_tabela.nome_da_coluna antes do comando. Isso faz com que a variável seja associada à coluna da tabela e o tipo desta coluna seja atribuído a variável. Outra forma de utilizar o %TYPE é informando o nome de outra variável dentro do bloco DECLARE. Isso serve para manter a compatibilidade e a consistência entre variáveis de um determinado bloco. O que acontece de fato quando definimos o %TYPE é que o compilador vai procurar no dicionário de dados qual o tipo que devemos atribuir a variável. Veja a figura abaixo para melhorar seu entendimento.

16712855225

Você tem a possibilidade de acessar dados em outro esquema e se basear neles para criação e atributos por meio do comando %TYPE. Para isso antes de identificarmos o nome da tabela e o nome da coluna, nós devemos inserir o nome do esquema. O %ROWTYPE funciona de forma bastante parecida com o %TYPE. A diferença é que quando utilizamos o %ROWTYPE não estamos nos referindo a uma coluna especifica da tabela, mas a todas as colunas da tabela. Basicamente o %ROWTYPE cria um tipo com os mesmos atributos da tabela ou da visão. Esse comando nos ajuda bastante quando queremos criar uma variável que possui todos os campos de uma tabela. Veja que ao invés de criarmos uma variável Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

15 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 para cada atributo podemos criar apenas uma variável que referencia toda a tabela. Quando vamos fazer uso dessa variável é possível acessar um atributo específico conforme exemplificado na figura abaixo:

O PL/SQL também suporta estrutura de programação avançada e orientada a objetos por meio dos comandos TYPE ... TABLE, TYPE ... RECORD. O TYPE ... TABLE é usado para criar uma tabela, ele é útil para armazenar valores de listas (ou arrays) retornados de uma consulta à base de dados. Para criar um tipo TABLE temos que seguir um processo de duas etapas. Uma estrutura de dados do tipo tabela deve ser definida pelo programador. Em seguida podemos referenciar esse tipo definido para criação de variáveis. Vejam o exemplo abaixo: DECLARE -- Associative array indexed by string: TYPE population IS TABLE OF NUMBER -- Associative array type INDEX BY VARCHAR2(64); -- indexed by string city_population population; i VARCHAR2(64);

-- Associative array variable -- Scalar variable 16712855225

Quando definimos a estrutura de uma TABLE definimos um novo tipo de dados que deve ser indexado por uma coluna na tabela. Utilizamos o INDEX BY para determinar qual o tipo de dados será usado para indexar o tipo TABLE. O outro tipo de dado complexo que vamos comentar é o RECORD. Ele é um tipo de dado definido pelo usuário que permite maior flexibilidade do que o %ROWTYPE. Enquanto o %ROWTYPE exige uma relação direta com as colunas de uma tabela específica, o RECORD permite qualquer combinação de tipos de dados. Para definirmos um RECORD precisamos basicamente de duas etapas. Primeiramente, precisamos declarar o registro como um tipo de dados definido pelo usuário. Vamos definir os atributos que fazem parte do nosso RECORD. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

16 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Num segundo momento podemos usar o tipo associado a uma variável. Desta forma, referenciando o tipo, podemos criar uma variável do tipo RECORD. Vejamos o exemplo abaixo: DECLARE TYPE DeptRecTyp IS RECORD ( dept_id NUMBER(4) NOT NULL := 10, dept_name VARCHAR2(30) NOT NULL := 'Administration', mgr_id NUMBER(6) := 200, loc_id NUMBER(4) ); dept_rec DeptRecTyp; dept_rec_2 dept_rec%TYPE;

Observem que após a definição de cada atributo temos uma vírgula, exceto na definição do último atributo. Vejam também que os atributos estão dispostos entre parênteses após a declaração IS RECORD. Tipos definidos pelo usuário. Os tipos definidos pelo usuário podem ser definidos com vários níveis de hierarquia encadeados. Por exemplo, quando tratamos de bônus de empresa a funcionários podemos descrever o mesmo com um atributo dinheiro, mas também com outras possíveis formas de bônus como dias de folga, direito ao uso do carro da empresa, viagens e etc. Vejamos um exemplo para podermos nos familiarizar com esses tipos. Observe no exemplo abaixo uma hierarquia de tipos. BonusCompensation faz parta do tipo EmpRecord. Vejam que estamos definindo tipos ou TYPE.

16712855225

PL/SQL também permite definir seus próprios subtipos. O tipo base pode ser qualquer tipo de dados PL/SQL especificado, seja escalar ou definida pelo usuário, como CHAR, DATE, ou RECORD. Os subtipos podem: fornecer compatibilidade com tipos de dados ANSI/ISO, mostrar o uso pretendido de Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

17 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 itens de dados desse tipo e ainda, detectar valores fora da faixa préestabelecida. Um subtipo pode ser irrestrito ou unconstrained. Ele tem o mesmo conjunto de valores do seu tipo básico, por isso, é apenas outro nome para o tipo de base. Portanto, subtipos unconstrained possuem o mesmo tipo básico definido e são permutáveis um com o outro. Nenhuma conversão de tipo de dados ocorre. Para definir um subtipo sem restrições, use esta sintaxe: SUBTYPE subtype_name IS base_type; Outra possibilidade é termos um subtipo restrito. Ele possui como domínio apenas um subconjunto dos valores do seu tipo base. Se o tipo de base permite que você especifique o tamanho, precisão e escala, ou um intervalo de valores, então você pode especificá-los para seus subtipos. A sintaxe de definição de subtipo é: SUBTYPE subtype_name IS base_type { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]; Para finalizar veja a lista completa de variáveis do PL/SQL abaixo:

16712855225

7.2.2. Escopo de identificadores As referências a um identificador são resolvidas de acordo com o seu escopo e visibilidade. O escopo de um identificador é a região de uma unidade de programa (bloco, subprograma, ou pacote) a partir do qual você pode referenciar o identificador. Um identificador é visível apenas nas regiões a partir Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

18 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 da qual você pode fazer referência ao identificador usando um nome não qualificado. A Figura abaixo mostra o alcance e visibilidade de uma variável chamada x, que é declarada em um bloco de externo, então declarada novamente em uma sub-bloco interno.

Identificadores declarados em um bloco PL/SQL são considerados locais para esse bloco e globais a todos os seus sub-blocos. Se um identificador global está declarado novamente em uma sub-bloco, ambos os identificadores permaneçam no escopo. Dentro do sub-bloco, no entanto, apenas o identificador local é visível. Você deve usar um nome qualificado para fazer referência ao identificador global. Veja no exemplo abaixo como definir um nome qualificado para o bloco por meio da sintaxe . 16712855225

Embora você não possa declarar um identificador duas vezes no mesmo bloco, você pode declarar o mesmo identificador em dois blocos diferentes. Os Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

19 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 dois itens representados pelo identificador são distintos, e qualquer mudança em um, não afeta o outro. No entanto, um bloco não pode referenciar identificadores declarados em outros blocos, por exemplo , no mesmo nível, porque esses identificadores não são nem local, nem global para o bloco .

7.2.3. BEGIN O bloco seguinte ao DECLARE é o BEGIN. Vejamos o que podemos inserir nesta parte do nosso código PL/SQL. A cláusula BEGIN é a parte do código onde o processamento é feito. Podemos usar as variáveis e outros objetos que definimos na parte anterior. Dentro do bloco de BEGIN é possível manipular dados internos do programa, manipular elementos do banco de dados, usar expressões regulares, trabalhar com valores literais, acessar objetos do banco de dados e ainda incluir uma estrutura lógica e suas ramificações. Podemos ainda acessar os dados de ambiente. Quando utilizamos a ferramenta SQL*Plus é possível acessar as variáveis de ambiente, por exemplo. Esse mesmo princípio pode ser aplicado a outras ferramentas da Oracle. Quando você está trabalhando no ambiente do Oracle Developer Forms ou Reports, você tem acesso a essas variáveis também. Uma das primeiras possibilidades dentro do rol de comandos é o acesso aos dados por meio de SQL. Podemos usar a linguagem de manipulação de dados para fazer consultas ou modificações nas bases de dados. O acesso ao servidor de banco de dados, que é considerado a fonte primária de dados, é tipicamente feito por meio do comando SELECT ... INTO. Trabalhar a manipulação dos dados é, talvez, o principal aspecto de um programa ou script PL/SQL. Existem algumas técnicas que devemos levar em consideração. A primeira considera as referências feitas a variáveis básicas, escalares ou de tipos compostos. Outra possibilidade quando estamos tratando da manipulação de dados é lidar com strings literais. A concatenação de strings e outros tipos de manipulações contribuem para entregar valores ou resultados mais acessíveis pelos programas. 16712855225

Existe um conjunto de situações especiais que devemos nos preocupar quando estamos trabalhando com PL/SQL. Além de entender como manipular strings literais, podemos usar comparadores para avaliar variáveis e funções SQL para ajudar a controlar a lógica do programa. É possível ainda fazer uso de expressões regulares para encontrar um conjunto de caracteres em determinado conjunto de informações.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

20 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Vamos entender um pouco como podemos trabalhar com strings. Você deve lembrar que o operador de atribuição usado pelo PL/SQL é o ‘:=’. Outro operador importante é o ‘||’, ele vai concatenar os valores de duas. É possível, encadear vários operados para montar strings. Vejamos um exemplo:

É importante observar que as aspas simples que cercam a vírgula e um espaço na figura acima servem para definir uma string baseada em um valor literal. Suponha que você queira atribuir um valor de nome a uma variável. Você pode usar o comando: ProfNome := ‘Thiago Cavalcanti’;. Outra consideração importante que deve ser feita está relacionada ao uso de caracteres especiais dentro das aspas simples. Um exemplo que podemos citar seria o uso da própria aspa simples, como o compilar consegue diferenciar se estamos terminando a string ou se queremos incluir uma aspa no nosso texto? Pra resolver este problemas podemos usar o comando q`!...!`. Esse comando afirma que todo texto entre as duas exclamações é considerado, mesmo que possua caracteres especiais. Veja o exemplo abaixo.

Falamos sobre o uso de caracteres. Vamos concentrar nossa atenção agora em dados numéricos e sua atribuição. Para atribuir um valor numérico a uma variável utilizamos o operador de atribuição. Devemos ter a preocupação do valor passado para a variável ser consistente com o tipo que definimos anteriormente. Você não pode atribuir uma string a um atributo numérico. Você deve considerar o uso de valores numéricos quando tiver a necessidades de fazer operações matemáticas sobre o mesmo.

Quando você trabalha com datas é importante saber o que acontece. O Oracle armazena fisicamente um valor numérico para o tipo Date e possui várias funções internas que nos ajudam na manipulação. Uma das funções é a SYSDATE que retorna a data atual do sistema. É possível somar dias a uma determinada data, basta passar o valor numérico da quantidade de dias que o Oracle executa a operação. Vejamos um exemplo. 16712855225

Para finalizar nossa apresentação de variáveis falta tratarmos do tipo boolean. Boolean pode ser utilizado de diferentes formas no seu código. Lembrese que ele pode assumir os valores TRUE, FALSE e NULL. Colocamos abaixo um exemplo de código que mostra como é possível atribuir um valor para uma

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

21 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 variável Boolean e como podemos utilizar essa variável em uma verificação dentro da sintaxe do comando lógico IF.

Observem que se o valor da variável HighPaid for maior do que 40 mil então o comando vai imprimir “Yes, this salary is high”. Vejam, mais uma vez, o uso da função dbms_output.put_line() para exibir o valor na console. Usando operadores de comparação Vamos aprender agora como usar os operadores de comparação dentro do código PL/SQL. Devido a integração com Oracle e SQL podemos usar todos os operadores presentes nos dois ecossistemas. Alguns dos mais conhecidos operadores de comparação são igual (=), diferente (!=,,~=, ^=), IS NULL, LIKE, IN (lista), BETWEEN ‘string01’ AND ‘string01’. Vejam o uso deles abaixo:

16712855225

Utilizando funções SQL e Oracle O Oracle possui uma infinidade de funções internas que podem ser utilizadas para facilitar a implementação do código PL/SQL. Algumas dessas funções podem modificar os valores de dados de uma determinada coluna, por exemplo. Para entender melhor o que estamos querendo dizer, pense na função UPPER() que modifica todos os caracteres da string passadas como parâmetro para maiúsculo. Veja na figura abaixo um exemplo de função SQL e dois de Oracle.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

22 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

O outro exemplo da figura acima é a função ROUND que faz o arredondamento do valor numérico passado como parâmetro. Quando passamos um valor negativo para a função ele vai fazer o arredondamento na parte inteira do valor, neste caso nos três últimos dígitos. Supondo que temo o valor 5779, ao aplicarmos a função ROUND com o parâmetro -3, o valor final seria 6000.

7.2.4. DML e Transações com PL/SQL Uma das tarefas mais comuns que executamos por meio dos comandos PL/SQL é acessar os dados que já existem em um determinado banco de dados. Esse é certamente um dos pontos positivos de usar PL/SQL. Qualquer comando SQL DML válido ou qualquer instrução de controle de transação pode ser executado dentro do código PL/SQL. Podemos citar como exemplos de comandos DML o INSERT, UPDATE, DELETE e LOCK TABLE. O comando LOCK TABLE não permite que outro usuário faça acesso a mesma tabela. O controle de transações pode ser feito por meio das instruções COMMIT, ROLLBACK, SAVEPOINT e SET TRANSACION. Vamos aproveitar para conceituar essas instruções. A instrução COMMIT termina a transação corrente, fazendo suas mudanças permanentes e visíveis a outros usuários. A cláusula WRITE da instrução COMMIT especifica a prioridade com que o Oracle Database escreve para o redo log as informações que a operação de confirmação gera. 16712855225

A declaração ROLLBACK termina a transação atual e desfaz todas as mudanças feitas durante essa transação. Se você cometer um erro, como excluir a linha errada de uma tabela, uma reversão restaura os dados originais. Se você não consegue terminar uma transação porque uma instrução SQL falhou ou código PL/SQL gera uma exceção, o rollback permite que você tome uma ação corretiva e, talvez, comece de novo a transação. Se uma instrução INSERT tenta armazenar um número duplicado para uma chave primária, o PL/SQL levanta a exceção predefinida DUP_VAL_ON_INDEX. Para garantir que mudanças em todas as tabelas são desfeitas, o gerenciador de exceção executa um ROLLBACK. Antes de

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

23 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 continuarmos vamos fazer uma questão recente que cobrou justamente o conhecimento sobre essa exceção.

04. BANCA: CESPE ANO: 2015 ÓRGÃO: TCU PROVA: AUDITOR FEDERAL DE CONTROLE EXTERNO - TECNOLOGIA DA INFORMAÇÃO Julgue os itens subsecutivos, a respeito de sistemas de bancos de dados. 125 Como parte de um conjunto de instruções, o comando Oracle PL/SQL a seguir significa que, quando se tentar armazenar um valor duplicado em uma coluna que seja chave primária de uma tabela, será disparada uma exceção predefinida na linguagem; e serão desfeitas apenas as instruções escritas no contexto do SAVEPOINT nome_point. EXCEPTION WHEN DUP_VAL_ON_INDEX THEN ROLLBACK TO nome_point; Comentário. Vamos aos comentários da questão. Em Oracle existe o que chamamos de exceções nomeadas. São exceções que possuem nomes no contexto de PL/SQL. Eles são nomeados no pacote padrão em PL/SQL e não precisam ser definidos pelo programador. O erro DUP_VAL_ON_INDEX, cujo código de erro é ORA-00001, é responsável por lançar uma exceção quando você tentar executar uma instrução INSERT ou UPDATE que cria um valor duplicado em um campo com restrição de índice exclusivo. A sintaxe do comando PL/SQL deve ser: EXCEPTION WHEN exception_name1 THEN [statements] No Oracle, a instrução de ROLLBACK é usada para desfazer o trabalho realizado pela transação atual ou uma transação que está em dúvida (in doubt). A sintaxe do ROLLBAK pode ser vista abaixo: ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name | FORCE 'string' ]; 16712855225

Para cada um dos argumentos acima temos um conjunto de descrição. Todos, porém, são opcionais. WORK - Foi acrescentado pela Oracle para ser compatível com SQL. Emitir o ROLLBACK com ou sem o parâmetro WORK resultará no mesmo resultado. TO SAVEPOINT - savepoint_name - A declaração ROLLBACK desfaz todas as alterações para a sessão atual até o savepoint especificado pelo savepoint_name. Se esta cláusula for omitida, então todas as alterações são desfeitas. FORCE 'string' - Ele é usado para forçar o rollback de uma transação que pode estar corrompido ou em dúvida. Com esta cláusula, você especifica o ID da transação entre aspas simples como string. Você pode encontrar o ID de transação na exibição sistema chamado DBA_2PC_PENDING. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

24 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Veja que pela explicação acima a alternativa se apresenta correta. Gabarito C Os comandos SAVEPOINT marcam um ponto no processamento de uma transação. Os savepoints permite fazer rollback de apenas parte de uma transação em vez de toda a transação. O número de pontos de salvamento ativos para cada sessão é ilimitado. Quando você reverter para um savepoint, quaisquer pontos de salvamento marcado depois que savepoint são apagados. O savepoint para o qual você reverteu não é apagado. Uma reversão simples ou um COMMIT apaga todos os pontos de salvamento. Você também pode usar a instrução SET TRANSACTION para iniciar uma transação no modo read-only ou leitura e gravação. Ela estabelece um nível de isolamento, ou atribui sua transação atual para um segmento de rollback especificado. Transações read-only são úteis para a execução de várias consultas enquanto outros usuários atualizam as mesmas tabelas. Durante uma transação de apenas leitura, todas as consultas se referem ao mesmo instantâneo do banco de dados, fornecendo uma visão multi-table, multi-query e de leitura consistente. Outros usuários podem continuar a consultar ou atualizar dados como de costume. A confirmação ou anulação termina a transação. Vejamos um exemplo abaixo:

16712855225

A declaração SET TRANSACTION deve ser a primeira instrução SQL em uma transação de somente leitura e pode aparecer apenas uma vez em uma transação. Se você definir uma transação para READ ONLY, consultas subsequentes vão ver apenas as modificações efetivadas antes do início da transação. O uso do Read Only não afeta outros usuários ou transações. Somente o SELECT, OPEN, FECTH, CLOSE, LOCK TABLE, COMMIT, e ROLLBACK são permitidos em uma transação apenas de leitura. Consultas não podem ser FOR UPDATE.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

25 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

7.2.4.1.

Usando o SELECT

Quando utilizamos o comando SELECT precisamos ter em mente que a manipulação do resultado vai depender do número de linhas que nossa consulta retorna. Caso o comando retorne uma única linha vamos usar a cláusula INTO para associarmos os valores da consulta a variáveis do nosso programa. Quando o resultado devolvido pela consulta possui múltiplas linhas precisamos usar um curso explícito. Falaremos mais sobre cursores na próxima aula do nosso curso. Quando usamos a cláusula INTO podemos usar qualquer tipo válido, nossa preocupação é com o fato que os tipos devem bater exatamente com os tipos dos valores retornados na consulta. Veja o exemplo abaixo que relaciona uma consulta a um tipo definido pelo usuário:

8. Estruturas de controle, Cursores e Exceções Nessa aula vamos tratar das estruturas de controles que podemos ter dentro do código, trataremos ainda de cursores e exceções. Sem perder tempo vamos ao assunto! 16712855225

8.1. Estruturas de controle A primeira parte desta nossa aula mostra como estruturar o fluxo de controle através de um programa PL/SQL. Vamos aprender como declarações e comandos podem ser conectados por estruturas de controle simples, mas poderosas, que possuem pontos únicos de entrada e de saída. Coletivamente, essas estruturas podem lidar com qualquer situação. Seu uso adequado leva naturalmente a um programa bem estruturado. Vamos apresentar então a sintaxe de cada um dos comandos.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

26 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

8.1.1. Visão geral das estruturas de controle As estruturas de controle são a extensão PL/SQL mais importante para SQL. Não só porque permitem PL/SQL manipular os dados do banco de dados, mas porque permitem processar os dados usando declarações de fluxo de controle. PL / SQL tem três categorias de instruções de controle: 1. Instruções de seleção condicional, que executam diferentes comandos para diferentes valores de dados. As instruções de seleção condicional são o IF e o CASE. 2. Instruções de loop, que executam as mesmas instruções ou comandos para uma quantidade diferente de valores de dados. As instruções de loop são o LOOP básico, FOR ... LOOP, e WHILE ... LOOP. A instrução de transferências EXIT transfere o controle para o fim do LOOP. A instrução CONTINUE sai da iteração atual de um ciclo e transfere o controle para a próxima iteração. Ambos, EXIT e CONTINUE, têm uma cláusula opcional WHEN, onde você pode especificar uma condição para a execução. 3. Instruções de controle sequenciais, que não são cruciais para a programação PL/SQL. As instruções de controle sequenciais são GOTO, que vai levar a execução do código para uma instrução especifica, e NULL, que não faz nada.

8.1.2. Instruções de seleção condicional As instruções de seleção condicional, IF e CASE, executam instruções diferentes dependendo dos valores de dados. A declaração IF executa ou pula uma sequência de uma ou mais declarações, dependendo de uma condição. A declaração IF tem as seguintes formas: IF THEN; IF THEN ELSE; IF THEN ELSIF. A instrução CASE escolhe uma condição a partir de uma sequência de opções, e executa a instrução correspondente. A instrução CASE tem duas formas possíveis: Simples, que avalia uma única expressão e compara com vários valores potenciais. Searched, que avalia várias condições e escolhe a primeira que é verdadeira. A instrução CASE é apropriado quando uma ação diferente deve ser tomada para cada alternativa. 16712855225

8.1.2.1.

Os comandos IF

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

27 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

IF CONDITION THEN STATEMENT 1; ... STATEMENT N; END IF; Vamos agora analisar a sintaxe de cada uma das possibilidades das instruções. Acima temos o comando IF-THEN. A palavra reservada IF marca o início da instrução IF. As instruções (statements) de 1 a n são uma sequência de comandos executáveis que consistem em uma ou mais das estruturas de programação padrão. A condição entre as palavras-chave IF e THEN determina se estas declarações são executadas. END IF é uma palavra reservada que indica o final da construção IF-THEN. Este fluxo da lógica é ilustrado na figura.

16712855225

Quando uma instrução IF-THEN é executada, uma condição é avaliada como TRUE ou FALSE. Se a condição for avaliada como TRUE, o controle passa para a primeira instrução executável do IF-THEN. Se a condição for avaliada como FALSE, o controle passa para a primeira instrução executável após a instrução END IF. Considere o seguinte exemplo. Você tem dois valores numéricos armazenados nas variáveis v_num1 e v_num2. Você precisa organizar estes valores para que o menor valor seja sempre armazenado em v_num1, e o maior valor armazenado no v_num2.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

28 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

No exemplo, a condição v_num1 > v_num2 é avaliada como verdadeiro porque 5 é maior que 3. A seguir, os valores são rearranjados de modo a que 3 seja atribuído a v_num1 e 5 seja atribuído a v_num2. Este passo é realizado com a ajuda da terceira variável, v_temp, que é usada para o armazenamento temporário. Este exemplo produz a seguinte saída: v_num1 = 3 v_num2 = 5 Uma instrução IF-THEN especifica a sequência de instruções para executar somente se a condição for avaliada como TRUE. Quando esta condição for avaliada como FALSE ou NULL, não há nenhuma ação especial para tomar a não ser prosseguir com a execução do programa. Uma instrução IF-THEN-ELSE permite que você especifique dois grupos de instruções. Um grupo é executado quando a condição for avaliada como TRUE. Outro grupo de instruções é executado quando a condição for avaliada como FALSE ou NULL. Essa estrutura é mostrada a seguir. 16712855225

IF CONDITION THEN STATEMENT 1; ELSE STATEMENT 2; END IF; STATEMENT 3; Quando condição é TRUE, o controle passa para o STATEMENT 1. Quando a condição for avaliada como FALSE ou NULL, o controle é passado para o Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

29 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 STATEMENT 2. Após a construção IF-THEN-ELSE ser concluída, o STATEMENT 3 é executado. Este fluxo da lógica é ilustrado na Figura abaixo.

A construção IF-THEN-ELSE deve ser usada quando você escolher entre duas ações mutuamente exclusivas. Considere o seguinte exemplo:

Para qualquer execução do código acima o dbms_output.put_line, será executado apenas uma vez. Por isso, a construção IF-THEN-ELSE permite que você especifique duas e só duas ações mutuamente exclusivas. Quando executado, este exemplo produz a seguinte saída: 16712855225

24 is even number Em alguns casos, uma condição usada em uma instrução IF ser avaliada como NULL em vez de VERDADEIRO ou FALSO. Para a construção IF-THEN, as instruções associadas não serão executadas se uma condição for avaliada como NULL. Em vez disso, o controle da execução passará para a primeira instrução executável após END IF. Para a construção IF-THEN-ELSE, as instruções especificadas após a palavra-chave ELSE serão executadas se a condição associada for avaliada como NULL. Agora vamos tratar da instrução ELSIF, vejam que a sintaxe da linguagem não coloca o E da palavra ELSE. O comando ELSIF aparece dentro de um código PL/SQL conforme descrito abaixo. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

30 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

IF CONDITION 1 THEN STATEMENT 1; ELSIF CONDITION 2 THEN STATEMENT 2; ELSIF CONDITION 3 THEN STATEMENT 3; ... ELSE STATEMENT N; END IF; A palavra reservada IF marca o início de uma construção ELSIF. As condições 1 a N são avaliadas em sequência como TRUE ou FALSE. Estas condições são mutuamente exclusivas. Em outras palavras, se a condição 1 é avaliada como TRUE, o STATEMENT 1 é executado e controle passa para a primeira instrução executável após o END IF. O resto da construção ELSIF é ignorado. Quando a condição 1 é avaliada como FALSE, o controle passa para a parte do ELSIF seguinte e a condição 2 é avaliada, e assim por diante. Se nenhuma das condições especificadas for avalia como TRUE, o controle passa para o ELSE da construção ELSIF. Uma declaração ELSIF pode conter qualquer número de cláusulas ELSIF. O fluxograma da lógica é ilustrado abaixo.

16712855225

A figura mostra que, se uma condição é avaliada como TRUE, o STATEMENT 1 é executado e controle passa para a primeira declaração após END IF. Se a condição for avaliada como FALSE, o controle passa a condição 2. Se a condição Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

31 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 2 é avaliada como TRUE, a declaração 2 é executado. Caso contrário, o controle passa para a instrução seguinte END IF, e assim por diante. Para obter uma declaração ELSIF, o IF deve ser sempre combinado com END IF. Deve haver um espaço entre o END e o IF. Quando o espaço for omitido, o compilador produz o seguinte erro:

Você deve ter notado que, para todos os exemplos da instrução IF as apalavras reservadas, IF, ELSIF e END IF são inseridos em uma linha separada e alinhado com a palavra IF. Além disso, todas as instruções executáveis na construção IF são recuadas. O formato da construção IF não faz diferença para o compilador, mas o significado da formatação nos ajuda no entendimento da construção. Você conheceu os diferentes tipos de controles condicionais: IF-THEN, IFTHEN-ELSE e ELSIF. Estes tipos de controles condicionais podem ser aninhado dentro um do outro, por exemplo, uma instrução IF podem ser aninhada dentro de um ELSIF, e vice-versa. Considere o seguinte exemplo:

16712855225

A declaração IF-THEN-ELSE é chamada de instrução externa, pois abrange a instrução IF-THEN (mostradas em negrito). A declaração IF-THEN é chamada interna porque é cercada pelo corpo da instrução IF-THEN-ELSE. Você pode aninhar instruções IF em qualquer nível de profundidade até comprimento máximo de um bloco PL/SQL, e os blocos de si podem ser aninhados até 255 níveis de profundidade. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

32 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

8.1.2.2.

O comando CASE

A instrução CASE tem duas formas: CASE simples e CASE pesquisada. A instrução CASE permite que você especifique um seletor que determina que grupo de ações seja executado. A instrução CASE pesquisada não tem um seletor; em vez disso, ele tem condições de pesquisa que são avaliadas para determinar qual grupo de ações serão tomadas. O CASE simples possui a seguinte estrutura.

A palavra reservada CASE marca o início da instrução. Um seletor é um valor que determina quando cláusula deve ser executada. Cada cláusula WHEN contém uma expressão e uma ou mais instruções executáveis associadas. A cláusula ELSE é opcional e funciona de forma semelhante à cláusula ELSE usado na instrução IF-THEN-ELSE. END CASE é uma frase reservada que indica o fim da instrução CASE. O fluxo lógico da estrutura anterior é ilustrado na figura abaixo.

16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

33 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Note-se que o seletor é avaliado somente uma vez, e as cláusulas WHEN são avaliadas sequencialmente. O valor de uma expressão é comparado com o valor do seletor. Se eles forem iguais, a instrução associada a uma determinada cláusula WHEN é executada, e todas as outras cláusulas WHEN não são avaliadas. Se nenhuma expressão corresponde ao valor do seletor, a cláusula ELSE é executada. Vejamos um exemplo.

Neste exemplo, uma variável, v_num_flag, é usada como um seletor para a instrução CASE. Se a função MOD retorna 0, o número é par; caso contrário, é impar. Se for atribuído a v_num o valor de 7 em tempo de execução, este exemplo produz a seguinte saída: 7 is odd number. A instrução CASE pesquisada tem condições de pesquisa que produzem valores booleanos: TRUE, FALSE, ou NULL. Quando uma condição de pesquisa específica é avaliada como TRUE, o grupo de instruções associadas a esta condição é executado. Essa estrutura é mostrada na listagem abaixo.

16712855225

Quando uma condição de busca é avaliada como TRUE, o controle passa para a execução das instruções associadas a ela. Se nenhuma condição de pesquisa for avalia como TRUE, então as instruções associadas à cláusula ELSE são executadas. Note que a cláusula ELSE é opcional. É importante notar as diferenças entre as instruções CASE simples e CASE pesquisada. Você viu que a instrução CASE pesquisada não tem um seletor. Além disso, sua cláusula WHEN contem condições de pesquisa que produzem um valor booleano semelhante à instrução IF, não expressões que podem produzir

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

34 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 um valor de um tipo qualquer. Observem a figura abaixo com a comparação entre as duas instruções:

No fragmento de código da esquerda (declaração CASE simples), v_num_flag é o seletor. É uma variável PL/SQL que foi definida como um NUMBER. Uma vez que o valor da expressão é comparado com o valor do seletor, a expressão deve retornar um tipo de dados semelhante. A expressão '0' contém um número, de modo que seu tipo de dados também é numérico. No fragmento de código do lado direito (instrução CASE pesquisada), não há necessidade do seletor, ele é substituído pela expressão MOD (v_num, 2) = 0. Esta expressão é avaliada como TRUE ou FALSE assim como de uma instrução IF. Vamos agora analisar um exemplo da instrução CASE que gera um erro de sintaxe, porque o tipo de dados retornado pelas expressões não coincide com o tipo de dados atribuído ao seletor.

16712855225

Neste exemplo, a variável v_num_flag foi definida como um número. No entanto, o resultado da expressão avaliada pela cláusula WHEN produz o resultado com tipo de dados booleano. Como resultado, este exemplo produz o seguinte erro de sintaxe:

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

35 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

8.1.2.3.

Expressão CASE

A expressão CASE tem uma estrutura quase idêntica a uma instrução CASE. Contudo, ao invés de realizar um sequência de comandos, essa expressão retorna um valor que pode ser atribuído a uma variável. Veja o exemplo abaixo:

Uma expressão CASE retorna um único valor que é atribuído a uma variável. No exemplo que você viu anteriormente, esta operação de atribuição foi realizada através do operador de atribuição, :=. Você deve lembrar que existe outra maneira de atribuir um valor a uma variável PL/SQL, que é por meio de uma instrução SELECT INTO.

05. BANCA: CESPE ANO: 2005 ÓRGÃO: TRT - 16ª REGIÃO (MA) PROVA: ANALISTA JUDICIÁRIO - TECNOLOGIA DA INFORMAÇÃO A respeito da sintaxe e da semântica do SQL, julgue os itens a seguir. O comando SELECT ... INTO, quando usado na linguagem PL/SQL, permite ao SELECT atribuir valores a variáveis. Comentário. Comentamos isso no parágrafo acima. Vamos seguir em frente que temos muito assunto para ver ainda nesta aula. 16712855225

Gabarito C

8.1.2.4.

Funções NULLIF e COALESCE

As funções NULLIF e COALESCE são definidas pela norma ANSI 1999 para serem abreviaturas do comando CASE. Ambas as funções podem ser usadas como variações da expressão CASE. Função NULLIF

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

36 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 A função NULLIF compara duas expressões. Se elas são iguais, então a função retorna NULL; caso contrário, retorna o valor da primeira expressão. A função NULLIF tem a seguinte estrutura: NULLIF (EXPRESSÃO 1, EXPRESSÃO 2). Se EXPRESSÃO 1 é igual a EXPRESSÃO 2, a função NULLIF retorna NULL. Se EXPRESSÃO 1 não é igual a EXPRESSÃO 2, a função NULLIF retorna EXPRESSÃO 1. Note que a função NULLIF faz o oposto da função NVL. Se a primeira expressão é NULL, então a função NVL retorna a segunda expressão. Se a primeira expressão não é NULL, então a função NVL retorna a primeira expressão. Podemos ver um exemplo do NULLIF na construção abaixo:

Um valor é atribuído a variável v_num em tempo de execução. Em seguida, este valor é dividido por dois, e o seu resto é comparado com 0 através da função NULLIF. Se o resto for igual a 0, a função NULLIF retorna NULL; caso contrário, ele retorna o resto. O valor retornado pela função NULLIF é armazenado na variável v_remainder e exibido na tela através da declaração DBMS_OUTPUT.PUT_LINE. Função COALESCE A função COALESCE compara cada expressão com NULL a partir da lista de expressões e retorna o valor da primeira expressão com valor não nulo. A função COALESCE tem a estrutura mostrada na listagem abaixo. COALESCE (EXPRESSÃO 1, EXPRESSÃO 2, ..., EXPRESSÃO N) Se a EXPRESSÃO 1 for avaliada como NULL, em seguida, a EXPRESSÃO 2 é avaliada. Se EXPRESSÃO 2 não avalia para NULL, então a função retorna EXPRESSÃO 2. Se EXPRESSÃO 2 também é avaliada como NULL, então a próxima expressão é avaliada. Se todas as expressões forem avaliadas como NULL, a função retorna NULL. Note que a função COALESCE é como uma função NVL aninhada. 16712855225

06. BANCA: FCC ANO: 2014 ÓRGÃO: TRF 3ª REGIÃO (SP MS) PROVA: ANALISTA JUDICIÁRIO - INFORMÁTICA (BANCO DE DADOS) Em PL/SQL, COALESCE (expr1, expr2) é equivalente a A CASE WHEN expr1 = expr2 AND expr1 IS NOT NULL END Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

37 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 B SUBSTR (expr1, expr2) C MAX ( expr1, expr2) D CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END E WHERE expr1 IN expr2 Comentário: Após a explicação acima podemos definir a instrução CASE que seja equivalente ao comando COALESCE (exp1, expr2). Primeiramente verificamos se o primeiro elemento é diferente de nulo, caso seja, retornamos ele, caso contrário retornamos o elemento seguinte. O comando na sintaxe correta pode ser escrito da seguinte forma: CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END O gabarito, portanto, encontra-se na alternativa D. Gabarito D

8.1.3.

Instruções de loop

Geralmente, os programas de computador são escritos porque certas tarefas devem ser executadas várias vezes. Por exemplo, muitas empresas necessitam processar transações em uma base mensal. Um programa permite a conclusão desta tarefa será executado no final de cada mês. Do mesmo modo, os programas devem incorporar instruções que precisam ser executadas repedidas vezes. Por exemplo, um programa pode precisar escrever um número de registros a uma tabela. Através da utilização de um ciclo, o programa pode escrever o número desejado de registos a uma tabela. Em outras palavras, os loops são facilidades que permitem que um conjunto de instruções seja executado repetidamente. 16712855225

Em PL/SQL, existem quatro tipos de loops: LOOPs simples, WHILE loops, numeric FOR loops, e cursor FOR loops. Nesta parte da aula, você irá explorar LOOP simples, WHILE loops, e FOR loops numéricos. LOOP Simples Um loop simples, como você pode inferir a partir de seu nome, é o tipo mais básico de loop e tem a estrutura mostrada na abaixo.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

38 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

LOOP STATEMENT 1; STATEMENT 2; ... STATEMENT N; END LOOP; A palavra reservada LOOP marca o início do ciclo simples. As instruções de 1 a N são uma sequência de comandos que é executada repetidamente. Estas indicações consistem em uma ou mais das estruturas de programação padrão. END LOOP é uma frase reservada que indica o final da construção de loop. Toda vez que o loop simples é iterativo, uma sequência de instruções é executada, e em seguida o controle passa de volta para o topo do loop. A sequência de declarações irá executar um número infinito de vezes, porque não há nenhuma declaração especificando quando o loop deve terminar. Assim, um simples circuito é chamado uma quantidade infinita de vezes, pois não há meios para sair do ciclo. Um ciclo precisa ter uma condição de saída que determina quando o ciclo está completo. Esta condição de saída tem duas formas de ser especificada, utilizando o EXIT ou EXIT WHEN. A instrução EXIT faz com que um loop termine quando a condição de saída é avaliada como TRUE. A condição de saída é avaliada com a ajuda de uma instrução IF. Quando a condição de saída é avaliada como TRUE, o controle passa para a primeira instrução executável após a instrução END LOOP. A instrução EXIT é válida somente quando colocado dentro de um loop. Quando colocado fora de um loop, ele irá causar um erro de sintaxe. Para evitar esse erro, use a instrução RETURN quando você quiser terminar um bloco PL/SQL antes do seu término normal. 16712855225

A instrução EXIT WHEN faz com que um loop termine somente se a condição especificada na cláusula WHEN for avaliada como TRUE. O controle passa então para a primeira instrução executável após a instrução END LOOP. Vejam um exemplo do uso do EXIT WHEN:

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

39 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Usando Loops WHILE A estrutura do LOOP WHILE é apresentada abaixo:

WHILE TEST CONDITION LOOP STATEMENT 1; STATEMENT 2; ... STATEMENT N; END LOOP; A palavra reservada WHILE marca o começo de uma construção de loop. A condição TEST é a condição de teste do laço que é avaliada como VERDADEIRO ou FALSO. O resultado desta avaliação determina se o laço é executado ou não. Instruções de 1 a N são uma sequência de instruções que é executada repetidamente. END LOOP é uma frase reservada que indicar o final da construção de loop. A condição de teste deve ser avaliada como VERDADEIRO pelo menos uma vez para as instruções no loop executarem. No entanto, é também importante garantir que a condição de teste irá eventualmente se avaliada como FALSO. Caso contrário, o loop while será executado continuamente. 16712855225

As declarações EXIT e EXIT WHEN podem ser usadas dentro do corpo de um loop while. Se a condição de saída é avaliada como TRUE antes da condição de teste ser avaliada como FALSE, o loop é encerrado prematuramente. Se a condição de teste é avaliada como FALSE antes da condição de saída ser avaliada como TRUE, não há interrupção prematura do loop. Vejamos um exemplo do LOOP While. Neste exemplo, a condição do teste, eventualmente, será avaliada com falsa porque o valor de v_Counter é incrementado em 1 a cada ciclo. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

40 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

FOR LOOP numérico Um laço for numérico é chamado numérico porque requer um inteiro como seu valor de terminação. A estrutura deste tipo de loop é mostrada na listagem a seguir.

FOR loop_counter IN [REVERSE]lower_limit..upper_limit LOOP STATEMENT 1; STATEMENT 2; ... STATEMENT N; END LOOP; A palavra reservada FOR marca o início da construção de loop. A variável loop_counter é definida implicitamente. Não há necessidade de definir o contador de loop na seção de declaração do bloco PL/SQL; em vez disso, esta variável é definida na construção de loop. O limite inferior e limite superior são números inteiros ou expressões que avaliam para valores inteiros em tempo de execução, e o ponto duplo (..) serve como o operador de intervalo. O limite inferior e limite superior definem o número de iterações ou ciclos, e seus valores são avaliados uma vez, durante a primeira iteração do loop. Neste ponto determina-se quantas vezes o loop vai iterar. As instruções de 1 a N são uma sequência de comandos que é executada repetidamente. END LOOP é uma frase reservada que marca o fim da construção de loop. 16712855225

Uma das palavras reservadas IN ou IN REVERSE deve estar presente quando da definição do loop. Quando a palavra-chave REVERSE é usada, o contador de loop vai fazer uma iteração do limite superior ao limite inferior. No entanto, a sintaxe para a especificação limite não muda. O limite inferior é sempre referenciado primeiro. Vejamos um exemplo do comando abaixo:

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

41 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Algumas considerações importantes. Você não pode incrementar ou decrementar a variável v_counter, uma tentativa de manipular a variável dentro do ciclo vai gerar um erro. Você também não pode acessar essa variável fora do escopo do loop. Neste caso o Oracle vai achar que a variável não foi declarada. Por fim, as instruções EXIT e EXIT WHEN apresentadas anteriormente podem ser utilizadas aqui da mesma forma para término prematuro da execução. Comandos CONTINUE e CONTINUE WHEN A condição de continuar tem duas formas: CONTINUE e CONTINUE WHEN. A instrução CONTINUE faz com que um loop termine a sua iteração atual e passe o controle para a próxima iteração do loop quando a condição for avaliada como TRUE. A condição de continuar é avaliada com a ajuda de uma instrução IF. Quando a condição de continuar for avaliada como TRUE, o controle passa para a primeira instrução executável no corpo do loop. Veja a sintaxe do comando:

Assim que a condição for avaliada como TRUE, o controle passa voltar à DECLARAÇÃO 1 (STATEMENT 1), que é a primeira instrução executável dentro do corpo do loop. Neste caso, ele faz a execução parcial do ciclo, portanto as afirmações seguintes após a condição de continuar dentro do corpo do loop não são executadas. 16712855225

O CONTINUE e CONTINUE WHEN são instruções que podem ser usadas em todos os tipos de loops. A diferença entre as condições de EXIT e de CONTINUE é que a condição de saída termina loop, ao passo que a condição de continuar termina a iteração corrente do ciclo. Quando a instrução CONTINUE é usado sem uma condição de continuar, a iteração atual do loop terminará incondicionalmente e controle da execução passará para a primeira instrução executável no corpo do loop. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

42 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 A instrução CONTINUE WHEN faz com que um loop termine sua iteração atual e passe o controle para a próxima iteração do loop apenas quando a condição de continuar for avaliada como TRUE. O controle passa então para a primeira instrução executável no interior do corpo do ciclo. As declarações CONTINUE e COTINUE WHEN são válidas somente quando colocadas dentro de um loop. Quando colocado fora de um loop, eles vão causar um erro de sintaxe. Usando loops aninhados Nós exploramos os três tipos de loops: laços simples, WHILE loops, e FOR loops numéricos. Qualquer um destes três tipos de loops pode ser encaixado um dentro do outro. Por exemplo, um loop simples pode ser aninhado dentro de um loop while, e vice-versa. Considere o seguinte exemplo:

Neste exemplo, o loop while é chamado um loop externo porque abrange o laço simples. O laço simples (em negrito) é chamado um loop interno, pois é cercado pelo corpo do loop while. 16712855225

O loop externo é controlado pelo contador de ciclo, v_counter1, e isso irá executar fornecendo o valor de v_counter1 que deve ser inferior a 3. Com cada iteração do circuito, o valor de v_counter1 é exibido na tela. Em seguida, o valor de v_counter2 é inicializado para 0. Note que v_counter2 não é inicializado no momento da declaração. O laço simples é colocado no interior do corpo do loop while, então o valor de v_counter2 deve ser inicializado toda vez antes que o controle passar para o loop simples. Uma vez que o controle passa para o loop interno, o valor de v_counter2 é apresentado na tela e incrementado de 1. Em seguida, a condição de saída é avaliada. Se essa condição for avaliada como FALSE, o controle passa de volta para o topo do loop simples. Se for avaliada como TRUE, o controle passa para a primeira instrução executável fora do loop. No nosso caso, o controle passa de Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

43 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 volta para o loop externo, o valor de v_counter1 é incrementado em 1, e a condição de teste do loop while é avaliada novamente. Você aprendeu na aula passada sobre rotulagem ou label de blocos PL/SQL. Os laços podem ser marcados de um modo semelhante, tal como ilustrado abaixo:

O rótulo deve aparecer imediatamente antes do início do ciclo. A sintaxe mostra que o rótulo pode ser opcionalmente utilizado no final da indicação do laço. É muito útil rotular laços-aninhados, tais rótulos melhoraram a legibilidade do script.

8.2. Gerenciando erros e exceções nativas Em PL/SQL, existem dois tipos de exceções: exceções built-in e exceções definidas pelo usuário. Nesta parte da aula, você vai aprender como lidar com certos tipos de erros de execução com a ajuda de exceções internas. Logo em seguida falaremos um pouco sobre exceções definidas pelo usuário.

8.2.1. Gerenciando erros e exceções nativas Para começarmos a entender o que seria uma exceção em tempo de execução vamos olhar o código abaixo:

16712855225

Agora vamos executar o comando e supor que passaremos como parâmetro para v_num1 e v_num2, respectivamente, os valores 10 e 0. Note que vamos forçar nosso código a tentar executar uma divisão por zero. Sabemos que isso não é possível. Logo o Oracle vai retornar uma mensagem de erro, qual seja:

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

44 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Mesmo que este exemplo não contém erros de sintaxe, o script terminou prematuramente porque o valor inserido para v_num2, o divisor, era zero. A divisão por 0 é indefinida, assim, esta operação conduz a um erro. Este exemplo ilustra um erro de execução que não pode ser detectado pelo compilador. Em outras palavras, para alguns dos valores especificados para as variáveis v_num1 v_num2, o código é executado com sucesso. Quando outros valores são associados a v_num1 e v_num2 o programa não consegue executar. Como resultado, ocorre um erro de execução. Lembre-se que o compilador não pode detectar erros de execução. Neste caso, um erro de execução ocorre porque o compilador não sabe o resultado da divisão de v_num1 por v_num2. Este resultado só pode ser determinado em tempo de execução, daí esse erro é referido como um erro de execução. Para lidar com esse tipo de erro no programa, deve ser adicionado um manipulador de exceção. A seção de tratamento de exceção tem a estrutura mostrada na listagem abaixo:

EXCEPTION WHEN EXCEPTION_NAME THEN ERROR-PROCESSING STATEMENTS; Nota-se que a seção de tratamento de exceção aparece após a seção executável do bloco. Portanto, o exemplo anterior pode ser reescrito da seguinte maneira (declarações adicionadas são mostradas em negrito):

16712855225

Uma seção de tratamento de exceção permite que um programa execute até a conclusão, em vez de terminar prematuramente. Ela também fornece o isolamento de rotinas de tratamento de erros. Em outras palavras, todos os códigos de processamento de erro para um bloco específico podem ser colocados dentro de uma única seção. Como resultado, torna-se mais fácil de seguir e compreender a lógica do programa. Finalmente, acrescentando uma seção de tratamento de exceção permitimos o processamento orientado a eventos de Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

45 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 erros. Tal como no exemplo mostrado anteriormente, quando ocorre um evento de exceção específico, tais como a divisão por 0, a seção de tratamento de exceções executa, e a mensagem de erro especificada para a instrução é exibida no tela por meio do comando DBMS_OUTPUT.PUT_LINE. Quando ocorre um erro que gera uma exceção built-in, a exceção é lançada implicitamente. Em outras palavras, se um programa rompe uma regra do Oracle, o controle passa para a seção de tratamento de exceção do bloco. Neste ponto, as declarações de processamento de erro são executadas. Após a seção de tratamento de exceção do bloco ser executada, o bloco termina; ou seja, o controle não retornar para a seção executável do bloco. Alguns erros comuns de tempo de execução do Oracle são predefinidos no PL/SQL como exceções. A lista a seguir identifica algumas dessas exceções predefinidas e explica como elas são geradas: NO_DATA_FOUND: Esta exceção é gerada quando uma instrução SELECT INTO que não faz chamadas para funções de agrupamento, como SUM ou COUNT, não retorna nenhuma linha. Por exemplo, suponha que você emita uma instrução SELECT INTO contra a tabela aluno onde a carteira de estudante é igual a 101. Se nenhum registro na tabela de ESTUDANTE satisfaz este critério (carteira de estudante é igual a 101), a exceção NO_DATA_FOUND é levantada. Quando uma instrução SELECT INTO chama uma função de agrupamento, como COUNT, o resultado nunca é vazio. Quando usado em uma instrução SELECT INTO contra a tabela aluno, a função COUNT retornará 0 para o valor da carteira de estudante 123. Assim, uma instrução SELECT INTO que chama uma função de agrupamento nunca irá levantar a exceção NO_DATA_FOUND. TOO_MANY_ROWS: Esta exceção é gerada quando uma instrução SELECT INTO retorna mais de uma linha. Por definição, um SELECT INTO pode retornar apenas uma única linha. Se uma instrução SELECT INTO retorna mais de uma linha, a definição da instrução SELECT INTO é violada. Isto provoca a exceção TOO_MANY_ROWS que é levantada. 16712855225

Por exemplo, você emitir uma instrução SELECT INTO contra a tabela aluno para um CEP específico. É altamente provável que esta instrução SELECT INTO irá retornar mais de uma linha, porque muitos estudantes podem residir na mesma área de CEP. ZERO_DIVIDE: Esta exceção é gerada quando uma operação de divisão é realizada no programa e um divisor é igual a zero. O exemplo mostrado anteriormente ilustra esse caso. LOGIN_DENIED: Esta exceção é gerada quando um usuário está tentando fazer logon no Oracle com um nome de usuário ou senha inválida.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

46 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 PROGRAM_ERROR: Esta exceção é gerada quando um programa PL/SQL tem um problema interno. VALUE_ERROR: Esta exceção é gerada quando ocorre um erro de conversão de tipo ou de tamanho incorreto. Por exemplo, suponha que você selecione o sobrenome de um estudante em uma variável que tem sido definido como VARCHAR2(5). Se o sobrenome do estudante contém mais de cinco caracteres, a exceção VALUE_ERROR é levantada. DUP_VALUE_ON_INDEX: Esta exceção é gerada quando um programa tenta armazenar um valor duplicado em uma coluna ou colunas que tenham um índice exclusivo definido sobre elas. Por exemplo, suponha que você está tentando inserir um registro na tabela da Seção Especializada do número de curso 25, seção 1. Se um registo para o curso e seção de número dado já existe na tabela, a exceção DUP_VAL_ON_INDEX é levantada porque estas colunas têm um índice único definido em cima delas. Abaixo apresentamos um código com dois exemplos de tratamento de exceções.

16712855225

É possível definir uma cláusula WHEN OTHERS dentro do bloco de exceção que vai tratar todas as exceções que forem lançadas e não forem listadas especificamente dentro de outra cláusula WHEN.

07. BANCA: CESPE ANO: 2010 ÓRGÃO: MPU PROVA: ANALISTA DO MPU ANALISTA DE INFORMÁTICA - BANCO DE DADOS A respeito de PL/SQL, julgue os itens a seguir. O trecho de programa em PL/SQL a seguir possibilita remover os efeitos de determinada transação no banco de dado, sempre que um erro ocorrer durante a execução. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

47 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Comentário: A resposta dessa questão foi alterada de certo para errado após recurso Segue a justificativa da Banca: “Há erro na sintaxe do comando EXCEPTION WHEN OTHERS THEN dbms_output.put_line (‘ Erro na atualização do Salário’), o que prejudicou seu julgamento. Desta forma observamos que o comando estaria correto se a sintaxe utilizada no dbms_output.put_line() estivesse correta. Gabarito E

8.2.2. Exceções criadas pelo usuário Muitas vezes, em seus programas, pode ser necessário lidar com problemas que são específicos do programa que você está escrevendo. Por exemplo, suponha que seu programa pede que um usuário digite um valor para a identificação do aluno. Este valor é então atribuído à variável v_student_id, que é usada mais tarde no programa. Geralmente, você quer um número positivo para um ID. Por engano, entretanto, o usuário digita um número negativo. No entanto, não ocorreu nenhum erro porque o v_student_id variável definida como um número, e o usuário forneceu um valor numérico legítimo. Para resolver esse problema, você pode implementar sua própria exceção para lidar com esta situação. Este tipo de uma exceção é chamado uma exceção definida pelo usuário, pois é definido pelo programador. Antes de tal exceção pode ser usada, ela deve ser declarada. A exceção definida pelo usuário é declarada na parte declarativa de um bloco PL/SQL, como mostrado na listagem.

DECLARE exception_name EXCEPTION; 16712855225

Uma vez que uma exceção tenha sido declarada, as instruções executáveis associadas a essa exceção são especificadas na seção de tratamento de exceção do bloco. O formato da seção de tratamento de exceções é o mesmo que para exceções embutidas. Considere o seguinte fragmento de código:

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

48 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Você já sabe que exceções built-in são levantadas implicitamente. Em outras palavras, quando ocorre um erro, uma exceção embutida associada com este erro é gerada. Claro, você está assumindo que você tenha incluído essa exceção na seção de tratamento de exceções do seu programa. Por exemplo, uma exceção TOO_MANY_ROWS é gerada quando uma instrução SELECT INTO retorna várias linhas. A exceção definida pelo usuário deve ser levantada explicitamente. Em outras palavras, você precisa especificar no seu programa em que circunstâncias uma exceção deve ser levantada. Veja um exemplo abaixo:

A instrução RAISE deve ser usada em conjunção com uma instrução IF. Caso contrário, o controle irá ser transferido para a seção de tratamento de exceções do bloco em cada execução. Vamos fazer uma questão sobre exceções para fixar o conteúdo.

08. BANCA: IDECAN ANO: 2014 ÓRGÃO: DETRAN-RO PROVA: ANALISTA EM TRÂNSITO - SISTEMA DE INFORMÁTICA As exceções são usadas para tratar de erros em tempo de execução no código PL/SQL. Algumas exceções são predefinidas. Observe a descrição de uma das exceções predefinidas. “Uma instrução SELECT INTO não retornou linhas ou foi feita tentativa de acessar um elemento excluído em uma tabela aninhada ou um elemento não inicializado em uma tabela de ‘index by’.” Assinale a alternativa que apresenta a exceção descrita anteriormente. 16712855225

A NO_DATA_FOUND. B NOT_LOGGED_ON. C CASE_NOT_FOUND. D DUP_VAL_ON_INDEX. E ROWTYPE_MISMATCH. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

49 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Comentário: Veja que a nossa reposta é NO_DATA_FOUND. É um erro que é lançado automaticamente quando não encontramos nenhuma linha. Comentamos sobre ele. Vamos analisar as demais alternativas. O erro ORA-01012, NOT_LOGGED_ON, ocorre devido a uma carga muito pesada no banco de dados. Se há um número máximo de sessões ligadas ao banco de dados, que é definido pelo parâmetro e dados PROCESSES, o ORACLE não permite que o usuário sysdba, bem como os outros utilizadores se conectem ao banco de dados. Se o usuário privilegiado sysdba tenta se conectar ao banco de dados, em seguida, acima de erro ORA-01012 é lançado. Em uma instrução CASE, se a cláusula ELSE for omitida, o sistema substitui por uma ação padrão. Para uma instrução CASE, o padrão quando nenhuma das condições for aceita é levantar uma exceção CASE_NOT_FOUND. Para uma expressão CASE, o padrão é retornar NULL. O erro DUP_VAL_ON_INDEX, cujo código de erro é ORA-00001, é responsável por lançar uma exceção quando você tentar executar uma instrução INSERT ou UPDATE que cria um valor duplicado em um campo com restrição de índice exclusivo. A exceção ROWTYPE_MISMATCH (ORA-06504) ocorre quando a variável de cursor do host e variável de cursor PL/SQL envolvidas em uma atribuição têm tipos de retorno incompatíveis. Por exemplo, quando uma variável de cursor aberta no host é passada para um procedimento armazenado, os tipos de retorno dos parâmetros devem ser compatíveis. Gabarito: A

8.3. Introdução a cursores Cursores são áreas de memória onde a plataforma do Oracle executa instruções SQL. Na programação de banco de dados, os cursores são estruturas de dados internos que permitem o processamento de resultados de consulta SQL. Por exemplo, você usa um cursor para operar em todas as linhas da tabela de aluno para aqueles estudantes que fazem um curso particular (com entradas associadas na tabela a inscrição). Nesta parte da aula, você aprenderá a declarar um cursor explícito que permite que um usuário processe muitas linhas devolvidas por uma consulta e escreva o código que irá processar cada linha de uma vez. 16712855225

8.3.1. Tipos de cursores Para a plataforma Oracle processar uma instrução SQL, ele precisa criar um espaço de memória conhecido como a área de contexto. Este irá conter as informações necessárias para processar a instrução. Essas informações incluem o número de linhas processadas e um ponteiro para a representação analisada da instrução (análise de uma instrução SQL é o processo pelo qual a informação é transferida para o servidor, nele a instrução SQL é avaliada como sendo Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

50 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 válida). Em uma consulta, o conjunto ativo refere-se às linhas que serão retornadas. Um cursor é um identificador, ou ponteiro, para a área de contexto. Através do cursor, um programa PL/SQL pode controlar a área de contexto e o que acontece nela enquanto a instrução é processada. Duas características importantes sobre o cursor são: 1. Os cursores permitem buscar e processar as linhas retornadas por uma instrução SELECT, uma linha de cada vez. 2. Um cursor é possui um nome de modo que possa ser referenciado. Existem dois tipos de cursores:  Um cursor implícito é automaticamente definido pelo Oracle cada vez que uma instrução SQL é executada. O usuário não estará ciente de que isso acontece e não será capaz de controlar ou processar a informação em um cursor implícito.  Um cursor explícito é definido pelo programador para qualquer consulta que retorna mais de uma linha de dados. Isso significa que se o programador declarar um cursor dentro do bloco de código PL/SQL, esta declaração permite que o aplicativo processe sequencialmente cada linha de dados a medida que forem retornados pelo cursor. Para entender melhor as capacidades de um cursor explícito, primeiro você precisa entender o processo seguido por um cursor implícito. Esse processo segue os seguintes passos:  Qualquer bloco PL/SQL emite um cursor implícito sempre que uma instrução SQL é executada, desde que um cursor explícito não exista para essa instrução SQL.  Um cursor é automaticamente associado a cada instrução DML (Data Manipulation Language) UPDATE, DELETE e INSERT. 16712855225

 Todos os UPDATEs e DELETEs ter cursores que identificam o conjunto de linhas que serão afetadas pela operação.  Uma instrução INSERT necessita de um local para receber os dados que serão inseridos na base de dados; o cursor implícito preenche esta necessidade.  O cursor aberto mais recentemente é chamado o cursor "SQL". O cursor implícito é usado para processar INSERT, UPDATE, DELETE e SELECT INTO. Durante o processamento de um cursor implícito, a plataforma do Oracle executa automaticamente as operações de OPEN, FETCH e CLOSE.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

51 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Um cursor implícito pode dizer quantas linhas foram afetadas por uma atualização. Cursores têm atributos como ROWCOUNT. SQL%ROWCOUNT, por exemplo, devolve o número de linhas atualizadas. Ele pode ser usado como se segue:

Ao contrário de um cursor implícito, um cursor explícito é definido pelo programa para qualquer consulta que retorna mais de uma linha de dados. Assim, você precisa processar um cursor explícito da seguinte forma: Primeiro você declarar um cursor. Em segundo lugar, você abre o cursor declarado. Em seguida, você faz o FECTH no cursor anteriormente declarado e aberto. Finalmente, você fecha o cursor. O único meio de gerar um cursor explícito é criando um cursor nomeado na seção de declaração do bloco PL/SQL. A vantagem de declarar um cursor explícito sobre o uso de um cursor implícito é que o cursor explícito dá mais controle ao programador. Cursores implícitos são menos eficientes do que cursores explícitos, o que torna mais difícil o tratamento e identificação de erros. O processo para trabalhar com um cursor explícito consiste nos seguintes passos: 1. Declarar o cursor. Isso inicializa o cursor na memória. 2. A abertura do cursor. O cursor declarado anteriormente pode agora ser aberto e a memória é alocada. 3. Fecthing o cursor. O cursor declarado anteriormente e devidamente aberto agora pode recuperar dados; este é o processo de FETCH do cursor. 16712855225

4. Fechando o cursor. O cursor declarado anteriormente, aberto, e fetched deve agora ser fechado para liberar alocação de memória. Declarar um cursor define o nome do cursor e associa com uma instrução SELECT. O primeiro passo é declarar o cursor com a seguinte sintaxe:

CURSOR c_cursor_name IS select statement; O exemplo que se segue é um fragmento de PL/SQL que demonstra o primeiro passo de declarar um cursor. Um cursor nomeado C_MyCursor é declarado como uma instrução SELECT de todas as linhas na tabela de zipcode que tem o estado do item igual ao "NY". Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

52 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Nomes de cursor seguem as mesmas regras de escopo e visibilidade que se aplicam aos identificadores PL/SQL. Porque o nome do cursor é um identificador PL/SQL, deve ser declarado antes de ser referenciado. Qualquer instrução SELECT válida pode ser usada para definir um cursor, incluindo joins e declarações com a UNION ou MINUS. O próximo passo no controle de um cursor explícito é para abri-lo. Quando a declaração de cursor OPEN é processada, 4 ações ocorrem automaticamente: 1. As variáveis (incluindo variáveis de ligação) na cláusula WHERE são examinadas. 2. Com base nos valores das variáveis, o conjunto ativo é determinado e o motor PL/SQL executa a consulta para esse cursor. As variáveis são examinadas pelo cursor apenas quando ele é aberto. 3. O motor de PL/SQL identifica o conjunto de dados ativo e as linhas de todas as tabelas envolvidas que cumpram os critérios de cláusula WHERE. 4. Um ponteiro para o conjunto ativo é definido para a primeira linha. A sintaxe para abrir um cursor é:

OPEN cursor_name; Um ponteiro para o conjunto ativo também é estabelecido para o cursor quando ele é aberto. O ponteiro determina qual linha é a próxima a ser buscada pelo cursor. Mais do que um cursor pode estar aberto em determinado momento. 16712855225

Depois que o cursor foi declarado e aberto, você pode recuperar dados do cursor. O processo de obtenção dos dados a partir do cursor é referido como FETCH do cursor. Existem dois métodos de FECTH em um cursor, que utilizam os seguintes comandos:

FETCH cursor_name INTO PL/SQL variables; FETCH cursor_name INTO PL/SQL record; Quando o cursor recebe o comando de FETCH, ocorre o seguinte: Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

53 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 1. O comando FETCH é usado para recuperar uma linha de cada vez a partir do conjunto ativo. Isto é feito geralmente dentro de um loop. Os valores de cada linha no conjunto ativo podem ser armazenados nas variáveis correspondentes ou em um registro PL/SQL de cada vez, executando operações para cada linha sucessivamente. 2. Após cada comando FETCH, o ponteiro sobre conjunto ativo é movido para a próxima linha. Assim, cada FETCH irá retornar as linhas sucessivas do conjunto ativo, até que todo o conjunto seja retornado. O último comando FETCH não irá atribuir valores para as variáveis de saída. Assim, eles contêm ainda os seus valores anteriores. Depois de todas as linhas do cursor serem processadas (e recuperadas), o cursor deverá ser fechado. Isso diz ao motor de PL/SQL que o programa foi concluído, e os recursos associados podem ser liberados. A sintaxe para fechar o cursor é:

CLOSE cursor_name; Uma vez que um cursor seja fechado, ele não é mais válido para buscar ou fazer FECTH a partir dele. Da mesma forma, não é possível fechar um cursor já fechado. Qualquer tentativa de executar uma dessas ações resultará em um erro Oracle. Encerramos nossa explicação sobre o processo de utilização de um cursor mostrando um exemplo da utilização.

16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

54 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 09. BANCA: CESPE ANO: 2012 ÓRGÃO: TRE-RJ PROVA: TÉCNICO DO JUDICIÁRIO - PROGRAMAÇÃO DE SISTEMAS Julgue os itens a seguir, relativos a conceitos de PL/SQL. 51 PL/SQL é a linguagem para consulta de banco de dados padrão ANSI (American National Standards Institute) utilizado pelos principais sistemas gerenciadores de banco de dados (SGBDs) do mercado. 52 A unidade básica em PL/SQL é um bloco com a seguinte estrutura: DECLARE, que é a seção para declaração de variáveis, tipos e subprogramas locais; BEGIN — única seção do bloco que é indispensável e obrigatória —, que é a seção executável, na qual ficam as instruções procedimentais e SQL; EXCEPTION, que é a seção/setor onde ficam as instruções de tratamento de erro; e END. 53 CURSOR é uma área de trabalho temporária criada na memória do sistema quando um comando SQL é executado Comentário. A alternativa 51 está incorreta, sabemos que PL/SQL é uma extensão da ORACLE para a linguagem SQL, essa sim é definida em um padrão ANSI. Para termos um bloco PL/SQL é necessária a presença do BEGIN e do END, além de alguma instrução ou comando para ser executado entre os dois. Os blocos DECLARE e EXCEPTION são opcionais. A alternativa 52 está correta. Acabamos de falar sobre a temporariedade da existência do CURSOR, ele ocupa uma região de memórias apenas enquanto mesmo está aberto, logo após o seu fechamento a memória é liberada. Gabarito: E C C.

9. Triggers Nesta parte da aula, você aprenderá sobre um tipo de bloco PL/SQL chamado de gatilho de banco de dados ou TRIGGER. Você também vai aprender sobre as diferentes características dos gatilhos e suas formas de uso no banco de dados. 16712855225

9.1.1. Vamos definir Triggers! Um gatilho de banco de dados é um bloco PL/SQL nomeado que é armazenado em um banco de dados e executado implicitamente quando ocorre um evento de disparo. O ato de execução de um gatilho é referido como disparo do gatilho. Um evento de disparo pode ser qualquer um dos seguintes procedimentos: 1. Uma instrução DML (por exemplo, INSERT, UPDATE ou DELETE) executada contra uma tabela de banco de dados. Tal gatilho pode disparar antes ou depois de um evento de disparo. Por exemplo, se você tiver definido um Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

55 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 gatilho para disparar antes de uma instrução INSERT na tabela de estudante, o gatilho é acionado toda vez antes de inserir uma linha na tabela. 2. Uma instrução DDL (por exemplo, CREATE ou ALTER) executada por um usuário em particular contra um esquema. Esses gatilhos são muitas vezes utilizados para fins de auditoria e são especificamente úteis para administradores de banco de dados Oracle. Eles podem gravar várias alterações do esquema, inclusive quando essas alterações foram feitas armazenar qual usuário fez a mudança. 3. Um evento do sistema, como a inicialização ou desligamento do banco de dados. 4. Um evento de usuário, como o login e logoff. Por exemplo, você pode definir um gatilho que dispara depois de um início de sessão em um banco de dados e que registra o nome de usuário e tempo de login. A sintaxe geral para a criação de um gatilho é mostrada na listagem abaixo (as palavras e frases reservadas entre colchetes são opcionais).

A palavra reservada CREATE especifica que você está criando um novo gatilho. A palavra reservada REPLACE Especifica que você está modificando um gatilho existente. REPLACE é opcional. Note, no entanto, que o REPLACE está incluído na maioria dos casos de uso do comando. 16712855225

Suponha que em poucos dias após a criação, você decide modificar este gatilho. Se você não incluir a palavra reservada REPLACE na cláusula CREATE do gatilho, uma mensagem de erro será gerada quando você compilar o gatilho. A mensagem de erro indica que o nome do seu gatilho já está sendo usado por outro objeto. Uma vez incluída na cláusula CREATE do gatilho a expressão REPLACE, há menos chance de um erro, porque se tratar de um novo gatilho, ele é criado, e se ele estiver referenciando um velho gatilho, ele será substituído.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

56 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 No entanto, você deve estar atento ao usar a palavra reservada REPLACE por várias razões. Primeiro, se acontecer de você usar REPLACE com o nome de uma função armazenada, procedimento ou pacote existente, você vai acabar com diferentes objetos de banco de dados que têm o mesmo nome. Isto ocorre porque os gatilhos têm um espaço de nomenclatura separado na base de dados. Enquanto compartilham do mesmo nome um gatilho e um procedimento, função ou pacote não provocam erros, mas potencialmente pode tornar confusa a interpretação, portanto, não é considerada uma boa prática de programação. Em segundo lugar, quando você usa a palavra reservada REPLACE e decide associar uma tabela diferente com o seu gatilho, uma mensagem de erro é gerada. Por exemplo, suponha que você criou um trigger STUDENT_BI para a tabela aluno. Em seguida, você decidir alterar o presente gatilho e associá-lo com a tabela de MATRÍCULA. Como resultado, a seguinte mensagem de erro é gerada:

ORA-04095: trigger 'STUDENT_BI' another table, cannot replace it

already

exists

on

As palavras opcionais reservadas EDITIONABLE e NONEDITIONABLE são especificadas se o gatilho é um objeto editável ou não editável. Note-se que esta designação se aplica somente se editável for habilitado para o tipo de objeto TRIGGER. Novidade! Dica do professor! Oracle apresenta uma característica muito importante chamada redefinição baseada na edição a partir da versão 11g, release 2. Esta funcionalidade permite aplicar as alterações a vários objetos de banco de dados sem invalidar todo o sistema, permitindo assim que o tempo de inatividade seja perto de zero. Por exemplo, anteriormente ao fazer mudanças estruturais em uma tabela iriamos invalidar inúmeras funções, procedimentos e pacotes dependentes dessa tabela. Como resultado, você precisa verificar e recompilar todos os objetos de banco de dados invalidados, potencialmente exigiria um tempo de inatividade do banco de dados. Com a redefinição baseada edição, você pode implementar todas essas mudanças sem problemas e migrar os usuários da versão antiga do sistema para a nova versão, sem incorrer em tempo de inatividade. 16712855225

O trigger_name refere-se ao nome do gatilho. BEFORE ou AFTER especifica quando o gatilho é acionado (antes ou depois do fato gerador). O triggering_event faz referência a uma declaração DML emitida contra a tabela. A table_name é o nome da tabela associada ao gatilho. A cláusula FOR EACH ROW especifica que o gatilho é acionado no nível de linha e dispara uma vez para cada linha que seja inserida, atualizada ou excluída. Você vai encontrar gatilho no nível de linha e no nível de instrução (statement). A cláusula WHEN especifica uma condição que deve ser avaliada como TRUE para o gatilho ser acionado. Por exemplo, esta condição pode especificar uma restrição na coluna de uma tabela.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

57 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 As próximas duas opções, FOLLOWS/PRECEDES e ENABLE/DISABLE, foram adicionados à cláusula CREATE OR REPLACE TRIGGER no Oracle 11g. Antes da versão Oracle 11g, era necessário para emitir o comando ALTER TRIGGER para ativar ou desativar um gatilho, uma vez que tenha sido criado. A opção ENABLE/DISABLE especifica se um gatilho é criado no estado ativado ou desativado. Quando um trigger é ativado, ele é acionado quando ocorre o evento de disparo. Por outro lado, quando um gatilho é desativado, ele não dispara quando ocorre tal evento. Observe que quando um gatilho é criado pela primeira vez sem opção ENABLE/DISABLE, ele é ativado por padrão. Para desativar o gatilho, você precisa emitir o comando ALTER TRIGGER, como mostrado na listagem abaixo.

ALTER TRIGGER trigger_name DISABLE; Da mesma forma, para ativar um gatilho que foi desativado anteriormente, você emitir o comando ALTER TRIGGER, como mostrado a seguir.

ALTER TRIGGER trigger_name ENABLE; A opção FOLLOWS/PRECEDES permite que você especifique a ordem na qual se desencadeia o trigger. Aplica-se aos gatilhos que são definidos na mesma tabela e disparados ao mesmo tempo, pela mesma ação. Por exemplo, se você definiu dois gatilhos na tabela de ESTUDANTE que são disparados antes da operação de inserção ser realizada, o Oracle não garante a ordem em que esses gatilhos irão executar a menos que você explicitamente especifique por meio da cláusula FOLLOWS/PRECEDES. Note-se que o gatilho referido na cláusula FOLLOWS/PRECEDES já deve existir e ter sido compilado com sucesso. A porção do gatilho descrito até este ponto é muitas vezes referida como o cabeçalho gatilho. Em seguida, vamos definir o corpo gatilho. O corpo de um gatilho tem a mesma estrutura como um bloco PL/SQL anónimo. Semelhante ao bloco PL/SQL, as seções de declaração e de exceção são opcionais. Triggers são usados para diferentes propósitos, como os seguintes: 16712855225

1. Impor regras de negócios complexas que não podem ser definidas usando as restrições de integridade 2. Manter regras de segurança complexas 3. Gerar automaticamente valores para colunas derivadas 4. Coletar informações estatísticas sobre acessos a uma tabela 5. Previr transações inválidas 6. Fornecer valores para auditoria

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

58 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 O corpo de um gatilho é um bloco PL/SQL. No entanto, várias restrições se aplicam quando você decide criar um gatilho: 1. Um gatilho não pode emitir uma instrução de controle transacional, como COMMIT, SAVEPOINT, ou ROLLBACK. Quando o gatilho é acionado, todas as operações realizadas pelo gatilho tornam-se parte de uma transação. Quando a transação é confirmada ou revertida, as operações realizadas pelo gatilho também são confirmadas ou revertidas. Uma exceção a essa regra é um gatilho que contém uma transação autônoma. Transações autônomas são discutidas em detalhe mais adiante ainda nesta aula. 2. Qualquer função ou procedimento chamado por um disparador não pode emitir uma instrução de controle transacional a menos que contenha uma transação autônoma. 3. Não é permitida para declarar variáveis LONG ou LONG RAW no corpo de um gatilho.

Você sabia? Se você excluir uma tabela, gatilhos de banco de dados da tabela também são descartados.

9.1.2. Before Triggers Considere o seguinte exemplo de um disparo na tabela ESTUDANTE. Este dispara o gatilho antes da instrução INSERT na tabela de aluno e preenche as colunas STUDENT_ID, CREATED_DATE, MODIFIED_DATE, CREATED_BY, e MODIFIED_BY. A coluna STUDENT_ID é preenchida com o número gerado pela sequência STUDENT_ID_SEQ, e as colunas CREATED_DATE, MODIFIED_DATE, CREATED_USER, e MODIFIED_USER são preenchidos com a data atual e as informações de nome de usuário, respectivamente. 16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

59 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

CREATE OR REPLACE TRIGGER estudante_bi BEFORE INSERT ON ESTUDANTE FOR EACH ROW BEGIN :NEW.student_id := STUDENT_ID_SEQ.NEXTVAL; :NEW.created_by := USER; :NEW.created_date := SYSDATE; :NEW.modified_by := USER; :NEW.modified_date := SYSDATE; END;

Esse gatilho é acionado para cada linha antes da instrução INSERT na tabela de estudante. Observe que o nome do gatilho é ESTUDANTE_BI, onde "ESTUDANTE" faz referência ao nome da tabela na qual o gatilho está definido e as letras "BI" significa "antes da inserção (BEFORE INSERT)". Não há nenhuma exigência específica para nomear gatilhos. No entanto, esta abordagem para nomes de um gatilho é bastante descritiva porque o nome do gatilho contém o nome da tabela afetada pelo evento de gatilho, o momento do evento desencadeador (antes ou depois), e do próprio fato gerador, neste caso o INSERT. No corpo do gatilho, existe um pseudorecord, :NEW, que permite o acesso a uma linha que está sendo processada. Em outras palavras, uma linha é inserida na tabela de aluno. O pseudorecord :NEW é de um tipo TRIGGERING_TABLE%TYPE, por isso, neste caso, é do tipo ESTUDANTE%TYPE. Para ter acesso aos atributos individuais do pseudorecord :NEW, utiliza-se a notação de ponto. Em outras palavras, :NEW.CREATED_BY refere-se ao atributo CREATED_BY membro do registro :NEW, o nome do registro é separado pelo ponto do nome de seu membro. Além do pseudorecord :NEW, existe também o registro :OLD. Ele permite que você acesse as informações atuais do registro que está sendo atualizado ou excluído. Assim, o pseudorecord :OLD é indefinido para as instruções INSERT e o pseudorecord :NEW é indefinido para as instruções DELETE. No entanto, o compilador PL/SQL não gera erros de sintaxe quando: :OLD ou :NEW são usados em triggers onde o fato gerador é uma operação de INSERT ou DELETE, respectivamente. Neste caso, os valores de membro são definidos como NULL. 16712855225

Dê uma olhada mais de perto na instrução que atribui um valor de sequência para a coluna student_id. A capacidade de acessar uma sequência via expressões PL/SQL é um novo recurso adicionado no Oracle 11g. Antes da versão 11g, as sequências podem ser acessadas somente por meio de consultas, conforme mostrado no próximo exemplo. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

60 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Você deve usar gatilhos de BEFORE nas seguintes situações:  Quando um gatilho fornece valores para colunas derivadas antes de uma instrução INSERT ou UPDATE ser concluída. Por exemplo, o gatilho pode fornecer colunas orientadas para a auditoria, como CREATED_DATE e MODIFIED_DATE.  Quando um gatilho determina se um INSERT, UPDATE, ou DELETE devem ser autorizados para completar suas ações. Por exemplo, quando você insere um registro na tabela de instrutor, um gatilho pode verificar se o valor fornecido para a coluna de CEP é válido - em outras palavras, se há um registro na tabela de CEP correspondente ao valor que foi fornecido.

9.1.3. AFTER Triggers Suponha existe uma tabela denominada AUDIT_TRAIL tendo a estrutura mostrada na figura a seguir. Esta tabela é usada para coletar informações de acesso do usuário em diferentes tabelas no esquema ESTUDANTE. Por exemplo, você pode gravar que os registros excluídos da tabela de instrutor e quando eles foram excluídos.

16712855225

Para conseguir isso, você precisa criar um gatilho na tabela instrutor, como mostrado no exemplo a seguir.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

61 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Esse gatilho é acionado depois de um UPDATE ou DELETE ser emitido sobre a tabela de instrutor. O corpo do gatilho contém duas funções booleanas, UPDATING e DELETING. A função UPDATING é avaliada como TRUE se um UPDATE instrução é emitido sobre a tabela, e a função DELETING é avaliada como TRUE se uma instrução DELETE é emitida sobre a tabela. Outra função booleana, INSERTING, também é avaliada como TRUE quando uma instrução INSERT é emitido contra a tabela. Essa última não aparece no exemplo acima. O gatilho insere um registro na tabela de AUDIT_TRAIL quando uma operação UPDATE ou DELETE é emitida contra a tabela INSTRUCTOR. Primeiro, ele determina qual operação foi emitida em relação à tabela INSTRUTOR via a instrução CASE. O resultado desta avaliação é então atribuído à variável v_trans_type. Em seguida, o gatilho adiciona um novo registro à tabela AUDIT_TRAIL. Uma vez que este gatilho é criado na tabela instrutor, qualquer operação UPDATE ou DELETE faz com que novos registros sejam criados na tabela AUDIT_TRAIL. Além disso, o gatilho pode ser melhorado através do cálculo quantas linhas foram atualizadas ou excluídas da tabela instrutor. Você deve usar gatilhos AFTER nas seguintes situações:  Quando um gatilho deve disparar após uma instrução DML ser executada 16712855225

 Quando um gatilho executa ações não especificadas em um gatilho de BEFORE

9.1.4. Transações autônomas Como referido anteriormente, quando um gatilho é disparado, todas as operações realizadas pelo gatilho tornar-se parte de uma transação. Quando esta transação é confirmada ou revertida, as operações realizadas pelo gatilho são confirmadas ou revertidas também. Considere uma instrução UPDATE contra a tabela de INSTRUTOR como mostrado abaixo.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

62 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

UPDATE instructor SET phone = '7181234567' WHERE instructor_id = 101;

Quando esta instrução UPDATE é executada, o gatilho INSTRUCTOR_AUD é acionado e adiciona um registro para a tabela de AUDIT_TRAIL como mostrado a seguir.

Em seguida, considere reverter a instrução UPDATE. Neste caso, o registro inserido na tabela de AUDIT_TRAIL também é revertido. Veja a figura abaixo:

Como você pode ver a tabela de AUDIT_TRAIL já não contém quaisquer registros. Para contornar esse comportamento, você pode optar por utilizar transações autônomas. Uma transação autónoma é uma transação independente inicializada por outra transação que é normalmente referida como a transação principal. Em outras palavras, uma transação autônoma pode emitir várias instruções DML e fazer commit ou revertê-las, sem confirmar ou reverter às instruções DML emitidas pela transação principal. 16712855225

Para definir uma transação autônoma, você empregar o pragma AUTONOMOUS_TRANSACTION. Um pragma é uma instrução especial para o compilador PL/SQL que é processada no momento da compilação. O pragma AUTONOMOUS_TRANSACTION aparece na seção de declaração de um bloco, como mostrado na listagem abaixo.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

63 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Nesta versão do gatilho, você adicionou o pragma AUTONOMOUS_TRANSACTION ao bloco de declaração e o COMMIT na parte executável do gatilho. Agora se você tentar emitir uma instrução de UPDATE e, em seguida, fazer rollback da mesma e consultar a tabela AUDIT_TRAIL. Mesmo que as alterações na tabela de INSTRUTOR forem revertidas, a tabela de AUDIT_TRAIL continuará a conter um registro da tentativa da operação UPDATE.

9.1.5. Row e Statement Triggers Nas páginas anteriores, você pode conhecer o gatilho de linha (ROW). Um gatilho de linha é acionado todas as vezes que alguma linha for afetada pela instrução. Quando a instrução FOR EACH ROW está presente na cláusula CREATE TRIGGER, o gatilho é de linha. Um gatilho declaração (statement) é disparado uma vez para a instrução de acionamento. Em outras palavras, um gatilho de statement é disparado apenas uma vez, independentemente do número de linhas afetadas pela instrução de acionamento. Para criar um gatilho de instrução, você pode omitir a instrução FOR EACH ROW na cláusula CREATE TRIGGER, como mostrado abaixo: 16712855225

CREATE OR REPLACE TRIGGER enrollment_ad AFTER DELETE ON ENROLLMENT ...; Esse gatilho especial é acionado uma vez, após uma instrução DELETE ser emitida contra a tabela. Se a instrução DELETE remove uma linha, cinco linhas ou 500 linhas da tabela de ENROLLMENT, este gatilho é acionado apenas uma vez.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

64 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Gatilhos de instrução devem ser usados quando as operações realizadas pelo gatilho não dependem dos dados nos registros individuais. Por exemplo, se você quiser limitar o acesso a uma tabela para o horário comercial, você pode usar um gatilho de instrução. Considere o seguinte exemplo.

Esse gatilho de declaração na tabela INSTRUCTOR dispara antes de um INSERT, UPDATE, ou DELETE ser emitido. Em primeiro lugar, o gatilho determina o dia da semana. Se o dia corrente for um sábado ou domingo, uma mensagem de erro é gerada. Por exemplo, se uma instrução de UPDATE na tabela de instructor for emitida no sábado ou domingo o trigger gerará a seguinte mensagem de erro:

Observe que este gatilho verificar apenas o dia específico da semana, mas não verifica a hora do dia. Você pode criar um gatilho mais sofisticado que verifica o dia da semana, se o tempo atual é entre 09:00 e 17:00. Se o dia cai na durante a semana e a hora do dia não é entre 09:00 e 17:00, o erro também é gerado.

9.1.6. Gatilhos de “Instead Of” 16712855225

Lembre-se que PL / SQL fornece um tipo especial de gatilho que podem ser definidas sobre visões do banco de dados. São os gatilhos de INSTEAD OF que são criados como um gatilho de linha. Um disparo de um gatilho de INSTEAD OF são disparados em vez de uma instrução DML (INSERT, UPDATE, DELETE) que tenha sido emitida contra uma visão e tenha modificado diretamente as tabelas subjacentes. Considere um gatilho INSTEAD OF definido sobre a visão INSTRUCTOR_SUMMARY_VIEW. Esse gatilho exclui um registro da tabela INSTRUCTOR referente ao valor correspondente ao ID do instrutor.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

65 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Observe o uso da cláusula INSTEAD OF no cabeçalho gatilho. Uma vez que o gatilho seja criado, o DELETE sobre a visão INSTRUCTOR_SUMMARY_VIEW não gera quaisquer erros. Quando esta instrução DELETE é emitida, o gatilho exclui um registro da tabela o instructor correspondente ao valor INSTRUCTOR_ID especificado. Agora considere a mesma instrução DELETE com um ID instructor diferente que não existe na tabela. Quando esta instrução de DELETE é emitida, vai gerar o seguinte erro:

Para assegurar que tal gatilho funcione como pretendido, você deve estar ciente de dois fatores importantes: as relações entre as tabelas no banco de dados e do efeito em cascata que um projeto particular pode apresentar. Vamos agora fazer uma questão para fixarmos o conhecimento a respeito de triggers.

10. BANCA: FCC ANO: 2015 ÓRGÃO: JUDICIÁRIO - ANÁLISE DE SISTEMAS

TRE-RR

PROVA:

ANALISTA

Em PL/SQL, triggers DML podem ser criadas usando a instrução CREATE TRIGGER, cuja sintaxe é apresentada a seguir: 16712855225

CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON object_name [REFERENCING OLD AS old | NEW AS new] FOR EACH ROW WHEN (condition)]] DECLARE] BEGIN ... trigger_body [EXCEPTION . . .] END [trigger_name]; O timing da sintaxe do trigger indica quando o trigger será disparado em relação ao evento de trigger. Os valores permitidos são BEFORE, AFTER e Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

66 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 A NOW. B ONCE. C AFTER NOW. D BEFORE NOW. E INSTEAD OF. Comentário. Existem basicamente três formas de definir o momento ou timing no qual um determinado gatilho será executado. Além dos conhecidos BEFORE e AFTER listados no enunciado, temos também o gatilho de INSTEAD OF, que é executo ao invés de uma determinada operação ou comando. Gabarito E

9.2. Procedures Todo o código PL/SQL que apresentamos até este ponto foi constituído por blocos anônimos que foram executados como scripts e compilados pelo servidor de banco de dados em tempo de execução. Agora você vai começar a usar o código modular. Código modular é uma metodologia para construir um programa de partes distintas (módulos), cada um dos quais realiza uma função ou tarefa específica em direção ao objetivo final do programa. Uma vez que o código modular é armazenado no servidor de banco de dados, torna-se um objeto de banco de dados, ou subprograma, que está disponível para outras unidades de programa. Para guardar o código no banco de dados, o código de origem precisa de ser enviado para o servidor de modo que seja compilado e armazenado na base de dados. Este processo será abordado em três seções seguintes. Esta primeira que introduz os procedimentos armazenados. A próxima que aborda os conceitos básicos de funções armazenadas, e uma última que trata dos pacotes.

9.2.1. Benefícios do Código Modular Um módulo de PL/SQL é qualquer unidade lógica completa de trabalho. Existem cinco tipos de módulos em PL/SQL: (1) blocos anônimos que são executados com um script de texto, (2) os procedimentos, (3) funções, (4) pacotes, e (5) triggers. Existem duas principais vantagens de se utilizar o código modular: (1) É mais reutilizável e (2) é mais administrável. 16712855225

Você pode criar um procedimento utilizando o SQL*Plus ou em uma das muitas ferramentas para a criação e depuração de código armazenado PL/SQL. Se você estiver utilizando o SQL*Plus, você terá que escrever seu código em um editor de texto e, em seguida, executá-lo na janela do SQL*Plus.

9.2.2. Criando um Procedure

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

67 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Um procedimento ou procedure é um módulo de execução de uma ou mais ações; ele não precisa retornar nenhum valor. A sintaxe para criar um procedimento é o seguinte:

Um procedimento pode ter zero ou muitos parâmetros. Cada procedimento tem duas partes: (1) a parte de cabeçalho, que vem antes da palavra chave AS (ou às vezes IS - elas são intercambiáveis) e contém o nome do procedimento e a lista de parâmetros, e (2) o corpo, que é tudo após o AS (IS). A palavra REPLACE é opcional. Quando essa palavra-chave não está incluída no cabeçalho do procedimento, para mudar o código no procedimento, você deve primeiro remover o procedimento e, em seguida, recriá-lo. Devido ao fato de ser muito comum para alterar o código de um procedimento, especialmente quando se está em desenvolvimento, é altamente recomendável que você use a opção OR REPLACE. O script a seguir demonstra a sintaxe para a criação de um procedimento. Quando esse script for executado, ele cria um procedimento chamado de Discount que é compilado em p-code e armazenado no banco de dados para execução posterior.

16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

68 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Para executar o procedimento armazenado Discount, a seguinte sintaxe é usada:

EXECUTE Procedure_name

Não há o comando COMMIT neste procedimento, o que significa que o procedimento não irá atualizar o banco de dados. Um comando COMMIT precisa ser emitido após o procedimento ser executado, se você quiser que as alterações a sejam feitas. Alternativamente, você pode digitar COMMIT, antes ou depois do END LOOP. Se você colocar a instrução COMMIT antes do END LOOP, então você está fazendo COMMIT depois de cada loop. Se você colocar a instrução COMMIT após o END LOOP, as alterações não serão confirmadas após o procedimento está concluído. É mais sábio para seguir a segunda opção, uma vez que deixa você mais bem preparados para os erros de manipulação. Duas visões principais no dicionário de dados fornecem informações sobre o código armazenado: a visão USER_OBJECTS, que dá informações sobre os objetos, e a vista USER_SOURCE, que dá ao texto do código fonte. O dicionário de dados também tem as versões ALL_ e DBA_ dessas views. A seguinte instrução SELECT recebe informação pertinente da visão USER_OBJECTS sobre o procedimento de desconto que escrevemos anteriormente:

Um procedimento pode se tornar inválido se a tabela na qual ele se baseia é excluída ou alterada. Você pode recompilar um procedimento inválido com o seguinte comando:

alter procedure procedure_name compile 16712855225

9.2.3. Usando os parâmetros IN e OUT Os parâmetros são um meio para passar valores a partir do ambiente de chamada para o servidor, e vice-versa. Estes valores são processados ou devolvidos através da execução do processo. Há três modos de parâmetros: IN, OUT e IN OUT. Os modos especificam se o parâmetro passado é lido ou age como um receptor para um objeto ou informação de saída. A figura abaixo ilustra a relação entre os parâmetros quando estão no cabeçalho procedimento versus quando o procedimento é executado. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

69 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Parâmetros formais são os nomes indicados entre parênteses, como parte de um cabeçalho de um módulo. Parâmetros reais são os valores das expressões especificadas entre parênteses como uma lista de parâmetros quando uma chamada é feita a um determinado módulo. O parâmetro formal e o parâmetro real relacionado devem ser do mesmo tipo ou de tipos de dados compatíveis. A tabela a seguir explica os três tipos de parâmetros de procedimentos.

Modo

IN

OUT

IN OUT

Descrição

Uso

Passa o valor para o programa Constantes, literais e expressões Não pode ser modificado pelo programa Modo default Passa valores de retorno do programa Deve ser uma variável Não pode receber valores default O valor é atribuído apenas se o programa executar com sucesso Tanto passa valores para o programa quanto envia valores de volta

Somente leitura

Somente escrita

Deve ser uma variável

Parâmetros formais não requerem restrições sobre o tipo de dados. Por exemplo, em vez de especificar uma restrição, tal como VARCHAR2 (60), você pode apenas definir o tipo VARCHAR2 para o nome do parâmetro na lista de parâmetros formal. A restrição é passada com o valor quando é feita uma chamada. 16712855225

11. BANCA: CESPE ANO: 2013 ÓRGÃO: MPU PROVA: ANALISTA DO MPU TECNOLOGIA DA INFORMAÇÃO - SUPORTE E INFRAESTRUTURA Com relação a linguagens de bancos de dados, julgue os itens subsecutivos. Em PL/SQL, parâmetros cujo tipo não esteja explicitamente declarado são considerados como do tipo IN.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

70 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Comentário. Observem que acabamos de comentar na tabela acima que o modo default para os parâmetros é o IN. Ou seja, caso não seja definido explicitamente, podemos considerar o parâmetro apenas de entrada. Gabarito C Aqui terminamos nosso assunto teórico relacionado a procedimentos, vamos agora continuar com a nossa ementa tratando de funções.

9.3. Functions Uma função que é armazenada na base de dados é muito parecida com um procedimento, nela um bloco PL/SQL nomeado pode receber parâmetros e ser invocado. Existem diferenças fundamentais tanto na forma como ela é criada e como ela é usada. Nesta breve seção, você vai aprender as noções básicas de como criar, usar e apagar uma função.

9.3.1. Criando Functions As funções são outro tipo de código armazenado muito semelhante aos procedimentos. A diferença significativa entre os dois é que uma função é um bloco PL/SQL que retorna um único valor. As funções podem aceitar um, muitos, ou nenhum parâmetro, mas elas devem ter uma cláusula de retorno em sua seção de execução. O tipo do valor de retorno de dados deve ser declarado no cabeçalho da função. Uma função não é um executável independente do mesmo modo que um procedimento é. A função deve ser sempre utilizada em alguns contextos. Uma função produz uma saída que tem de ser atribuída a uma variável, ou pode ser utilizada em uma instrução de SELECT. Esta seção aborda a sintaxe da função básica e demonstra como criar uma função. A sintaxe para criar uma função é a seguinte:

16712855225

A função não tem necessariamente qualquer parâmetro, mas deve ter um valor de retorno declarado no cabeçalho, e ela deve retornar valores para todos os possíveis fluxos de execução. A declaração RETURN não tem que aparecer como a última linha da seção de execução principal. Pode haver mais de uma instrução RETURN. A função pode ter parâmetros IN, OUT ou IN OUT, embora você raramente você vá encontrar, exceto parâmetros IN, porque é má prática

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

71 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 de programação usar os outros parâmetros (OUT e IN OUT). O exemplo a seguir mostra um script para a criação de uma função chamada show_description.

O cabeçalho da função do exemplo indica que a função tem um parâmetro numérico e retorna um valor do tipo VARCHAR2. A função faz uso de uma variável VARCHAR2(50) chamada v_description. Dá a esta variável o valor da descrição do curso, cujo número é passado para a função. O valor de retorno é a própria variável. Há duas exceções na função. A primeira é o quanto nenhum dado é encontrado, neste caso a exceção NO_DATA_FOUND deve ser lançada, que é o mais provável de ocorrer. A segunda exceção é lançada quando quaisquer outros erros acontecem. A cláusula WHEN OTHERS é utilizado como um catchall para qualquer outro erro que possa ocorrer. A cláusula de retorno é uma das últimas declarações na função. A razão para esse posicionamento é que o foco do programa irá retornar para o ambiente de chamada uma vez que a cláusula RETURN é emitida. 16712855225

9.3.2. Invocando Funções Funções retornam um valor único e podem ser muito úteis em uma instrução SELECT. Em particular, elas podem ajudá-lo a evitar instruções SQL complexas e repetidas dentro de uma instrução SELECT. A declaração a seguir demonstra o uso da função show_description em uma instrução SELECT para cada curso da tabela COURSE:

SELECT course_no, show_description(course_no) FROM course;

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

72 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 As funções também podem ser utilizadas em uma instrução SQL. Na verdade, você tem feito uso delas o tempo todo. Você simplesmente não pode ter percebido isso. Como um exemplo simples, imagine usando a função UPPER em uma instrução SELECT:

SELECT UPPER('bill') FROM DUAL; A função UPPER fornecida pelo Oracle retorna o valor em letras maiúscula do parâmetro que foi passado. Para uma função definida pelo usuário ser chamada em uma expressão SQL, ela deve ser uma função de linha (ROW), não uma função de grupo, e os tipos de dados devem ser tipos de dados SQL. Os tipos de dados não podem ser tipos de dados PL/SQL como um valor booleano, tabela (TABLE) ou registro (RECORD). Além disso, em uma função não é permitida a inclusão de quaisquer declarações DML (INSERT, UPDATE, DELETE). Para usar uma função em uma instrução SQL SELECT, a função deve ter certo nível de pureza, que é adquirido com a cláusula PRAGMA RESTRICT_REFERENCES.

9.3.3. Definindo funções com a cláusula WITH A partir do Oracle Database 12.1, você pode definir funções, bem como os procedimentos dentro da mesma instrução SQL em que a instrução SELECT aparece. Isto alivia a troca de contexto entre o código PL/SQL e os motores SQL, permitindo que ambas as etapas tenham lugar no motor de SQL e, por sua vez, prevê um ganho de desempenho. A função ou procedimento precisa ser definido utilizando a cláusula WITH. Nas versões anteriores da plataforma Oracle, apenas subconsultas poderia ser definido na cláusula WITH. O exemplo seguinte demonstra como a função show_description, que foi desenvolvido anteriormente, pode ser utilizada na cláusula WITH. A função foi renomeada para show_descript para garantir que ela não se confunda com a versão anterior, show_description. 16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

73 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

O recurso WITH FUNCTION é útil em muitas situações. A principal desvantagem deste recurso é que você perde os benefícios de uma função reutilizável em favor de obter um melhor desempenho através da redução das trocas de contexto entre os motores SQL e PL/SQL. Antes de decidir qual abordagem usar, é aconselhável fazer uma análise de custo e pesar os benefícios contra a eventual necessidade de reutilizar a função em outros contextos.

9.3.4. Definindo funções com o Pragma UDF As funções podem ser criadas acrescentando a sintaxe do pragma UDF, que notifica o compilador que uma função definida pelo usuário será utilizada em instruções SQL. Um pragma é basicamente uma sugestão para o compilador que permite aperfeiçoar a função adequadamente. Quando a sintaxe UDF pragma é usada, a função terá um desempenho mais elevado quando usada no SQL. Muito pouco precisa ser feito para aplicar o pragma, basicamente adicionar a frase pragma UDF antes da declaração de variável, como mostrado em negrito no exemplo a seguir: 16712855225

9.4. Packages Um pacote é uma coleção de objetos PL/SQL agrupadas sob um mesmo nome do pacote. Os pacotes podem incluir procedimentos, funções, cursores, declarações, tipos e variáveis. Colecionar objetos em um pacote tem inúmeros Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

74 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 benefícios. Nesta seção, você vai aprender o que esses benefícios são e como aproveitá-los.

9.4.1. Criando Packages Existem numerosos benefícios para a utilização de pacotes como um método de agrupar suas funções e procedimentos, sendo o primeiro que um pacote bem projetado é um agrupamento lógico de objetos, tais como funções, procedimentos, variáveis globais e cursores. Todo o código (árvore de análise e pseudocode [p-code]) é carregado na memória (área global compartilhada [SGA] do servidor Oracle) na primeira chamada do pacote. Isto significa que a primeira chamada para o pacote é muito cara, pois envolve um monte de processamento no servidor, mas todas as chamadas subsequentes irão resultar em um desempenho melhor. Por esta razão, os pacotes são frequentemente utilizados em aplicações onde os procedimentos e funções são chamados repetidamente. Pacotes permitem que você faça uso de alguns dos conceitos envolvidos na programação orientada a objetos, apesar de PL/SQL não é uma "verdadeira" linguagem de programação orientada a objetos. Com o pacote de PL/SQL, você pode agrupar funções e procedimentos e fornecer-lhes um contexto. Devido ao fato de todo o código do pacote ser carregado na memória, você também pode escrever seu programa para que outros códigos semelhantes sejam colocados no pacote e permitir que vários procedimentos e funções possam chamá-los. Você pode fazer isso se a lógica de cálculo for bastante intensa e você quer mantê-la em um só lugar. Um nível adicional de segurança se aplica quando utilizamos pacotes. Quando um usuário executa um procedimento em um pacote (ou procedimentos armazenados e funções), o procedimento opera com as mesmas permissões que o seu proprietário. Pacotes permitem a criação de funções e procedimentos privados, que podem ser chamados apenas a partir de outras funções e procedimentos do pacote. Semelhante aos métodos privates de Java. Isso reforça a ocultação de informações. A estrutura do pacote incentiva, assim, projeto top-down. 16712855225

A especificação do pacote contém informações sobre o conteúdo do pacote, mas não o código para os procedimentos e funções. Ele também contém declarações de variáveis globais e públicas. Qualquer coisa colocada na seção de declaração de um bloco PL/SQL pode ser codificada em uma especificação do pacote. Todos os objetos colocados na especificação do pacote são chamados objetos públicos. Qualquer função ou procedimento que não esteja presentes na especificação do pacote, mas esteja no corpo do pacote é chamado de função ou procedimento privado.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

75 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Quando os procedimentos e funções públicas estão sendo chamados a partir de um pacote, o programador que escreve o processo de "chamada" precisa apenas das informações presentes na especificação do pacote, uma vez que fornece todas as informações necessárias para chamar um dos procedimentos ou funções dentro do pacote. A sintaxe para a especificação do pacote é a seguinte:

O corpo do pacote contém o código executável real para os objetos descritos na especificação do pacote. Ele contém o código para todos os procedimentos e funções descritas na especificação e pode conter, adicionalmente, a codificação para os objetos não declarados na especificação. O último tipo de objeto presente é invisível ao exterior do pacote e é referido como "ocultos". Ao criar pacotes armazenados, a especificação do pacote e o corpo podem ser compilados separadamente. Vejam a definição do corpo de um pacote abaixo:

Uma série de regras deve ser seguida no código do corpo do pacote. Primeiro, deve haver uma correspondência exata entre os cabeçalhos do cursor e do módulo e suas definições na especificação do pacote. Em segundo lugar, as declarações de variáveis, exceções, tipo, ou constantes na especificação não podem ser repetidas no corpo. Em terceiro lugar, qualquer elemento declarado na especificação pode ser referenciado no corpo. 16712855225

Você usar a seguinte notação ao chamar elementos internos de fora do pacote: package_name.element. Você não precisa qualificar os elementos quando eles são declarados e referenciados no interior do corpo do pacote ou quando são declarados na especificação e referenciados no interior do corpo do mesmo pacote.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

76 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 O exemplo a seguir mostra a especificação do pacote manage_students. Posteriormente, mostraremos a criação do corpo do mesmo pacote.

O exemplo a seguir cria uma especificação de pacote de um pacote chamado school_api. Vejam que neste caso fazemos referência, na definição do pacote, a uma função e uma procedure.

Agora vamos criar o corpo do pacote manage_students que foi especificado anteriormente.

16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

77 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Este script compila o pacote manage_students no banco de dados. A especificação para o pacote indica que existe um procedimento e uma função. O procedimento find_sname requer um parâmetro IN, a carteira de estudante ou algum outro student_id. Ele retorna dois parâmetros, o primeiro e o último nome do aluno. A função id_is_good recebe um único parâmetro e retorna um valor booleano (verdadeiro ou falso). Embora o corpo ainda não tenha sido inserido no banco de dados, o pacote já está disponível para outras aplicações. Por exemplo, se você incluiu uma chamada para um destes procedimentos em outro procedimento armazenado, esse procedimento irá compilar (mas não executar). 16712855225

É mais ou menos a mesma ideia de interface de Java. Você define as assinaturas dos métodos, neste caso para as funções, procedimentos e gatilhos. Contudo para serem executados é preciso que um objeto concreto seja criado, neste caso você precisa do corpo do pacote. Para concluir nosso estudo vamos observar o uso do pacote manage_students em outro bloco.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

78 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Este é um bloco PL/SQL para executar a função e o procedimento no pacote manage_students. Se um valor existente de student_id for inserido, o nome do aluno é exibido. Se o student_id não é válido, uma mensagem de erro é exibida. Aqui terminamos o conteúdo da nossa aula de PL/SQL. Concluímos o assunto relacionado a matéria. Vamos a seguir fazer um conjunto de questões comentadas a respeito do assunto.

16712855225

Vamos agora fazer uma questão para fixarmos melhor a matéria.

12. BANCA: CESPE ANO: 2012 ÓRGÃO: TRE-RJ PROVA: TÉCNICO DO JUDICIÁRIO - PROGRAMAÇÃO DE SISTEMAS Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

79 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Julgue os itens a seguir, relativos a conceitos de PL/SQL. 51 PL/SQL é a linguagem para consulta de banco de dados padrão ANSI (American National Standards Institute) utilizado pelos principais sistemas gerenciadores de banco de dados (SGBDs) do mercado. 52 A unidade básica em PL/SQL é um bloco com a seguinte estrutura: DECLARE, que é a seção para declaração de variáveis, tipos e subprogramas locais; BEGIN — única seção do bloco que é indispensável e obrigatória —, que é a seção executável, na qual ficam as instruções procedimentais e SQL; EXCEPTION, que é a seção/setor onde ficam as instruções de tratamento de erro; e END. 53 CURSOR é uma área de trabalho temporária criada na memória do sistema quando um comando SQL é executado Comentário. A alternativa 51 está incorreta, sabemos que PL/SQL é uma extensão da ORACLE para a linguagem SQL, essa sim é definida em um padrão ANSI. Para termos um bloco PL/SQL é necessária a presença do BEGIN e do END, além de alguma instrução ou comando para ser executado entre os dois. Os blocos DECLARE e EXCEPTION são opcionais. A alternativa 52 está correta. Acabamos de falar sobre a temporariedade da existência do CURSOR, ele ocupa uma região de memórias apenas enquanto mesmo está aberto, logo após o seu fechamento a memória é liberada. Gabarito E C C

16712855225

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

80 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05

Questões Comentadas Como de praxe apresentamos abaixo um conjunto de algumas questões comentadas. Essas questões complementam o conteúdo e contribuem de forma definitiva para o seu processo de aprendizagem. Esperamos você goste. Vamo que vamo!

13. BANCA: MP-RS ANO: 2015 ÓRGÃO: MP-RS PROVA: TÉCNICO EM INFORMÁTICA - SISTEMAS Considere as seguintes afirmações, relativas à estrutura básica de um bloco PL/SQL. I. A seção DECLARE é opcional e é utilizada para a declaração de variáveis, cursores, tipos e subprogramas, e outros elementos utilizados no programa. II. O conteúdo entre BEGIN e END é uma seção obrigatória onde ficam as instruções procedimentais. Esta seção deve conter pelo menos uma linha de código executável, que pode ser apenas o comando NULL para indicar que nada deve ser executado. III. A seção EXCEPTION é opcional e é onde ficam as instruções de tratamento de erro. Quais estão corretas? A Apenas I. B Apenas II. C Apenas III. D Apenas I e III. E I, II e III. 16712855225

Comentário. Vejam que o item I a ser analisado trata da opção de apresentarmos ou não a seção ou bloco de DECLARE dentro de um script PL/SQL. Sabemos que é justamente neste trecho de código que devemos declarar nossas variáveis e outros objetos que serão usados durante o processamento. Item pode ser confirmado como correto. O conteúdo entre o BEGIN e o END é obrigatório, se não colocarmos nenhum código neste trecho não teremos um programa para executar e o proposito da linguagem será perdido. Vejam que a questão coloca algo trivial e sem muito proposito: sugeriu colocar o comando NULL no corpo do comando. Mas vejam que esse não deixa de ser um comando executável, portanto, alternativa correta!.

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

81 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 Por fim, a seção EXCEPTION, que também é opcional, mas é importante para evitar que erros de banco sejam propagados para outras instâncias, principalmente para o usuário. Sendo assim temos os itens I, II e III como corretos. E a resposta para a questão presente na alternativa E. Gabarito E

14. BANCA: FCC ANO: 2015 ÓRGÃO: TRT - 15ª REGIÃO (CAMPINAS-SP) PROVA: TÉCNICO JUDICIÁRIO - TECNOLOGIA DA INFORMAÇÃO Considere o comando PL/SQL abaixo. SELECT nome, salario, comissao, (salario*12) + ___I___(comissao,0) FROM empregados; Esse comando tem como objetivo exibir o nome, o salário mensal, a comissão mensal que o funcionário recebe e a remuneração anual, que é calculada multiplicando-se o salário por 12 e adicionando a comissão mensal. Como o campo comissao pode conter valores nulos, para converter estes possíveis nulos para zero utiliza-se, na lacuna ___I___, a função A TO_NOTNULL. B AVG. C TO_NUMBER. D NVL. E TRUNC. Comentário. Nós vamos começar analisando a resposta da questão. A alternativa apresenta o termo NVL que significa NULL Value Substitution. Trata de um comando que substitui o valor null em uma determinada coluna de uma consulta por um valor default. Sua sintaxe básica é NVL(value,substitute). Observe a figura abaixo: 16712855225

O valor da variável WEIGHT para o cliente Tully Andover é nulo na base de dados. Veja que na consulta esse valor foi substituído por 43. Outro comando semelhante ao NVL é o NVL2. Neste caso a sintaxe é um pouco diferente, temos NVL2 (exp1, exp2, exp3) que funcionam da seguinte forma: se

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

82 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 a expressão exp1 não for nula retorne exp2, caso a exp1 seja nula retorna o valor da exp3. Analisando a alternativa E, temos a função TRUNC. ROUND e TRUNC são duas funções de valor único relacionadas. TRUNC trunca, ou corta, os dígitos de precisão de um número. ROUND permite o arredondamento para um determinado número de dígitos de precisão. Aqui estão os formatos da sintaxe para ROUND e TRUNC: ROUND (valor de precisão) TRUNC (valor, precisão) TO_NUMBER é uma função de conversão. Ela converte uma sequência de caracteres para um tipo de dados NUMBER. Ela exige que os caracteres sejam um número devidamente formatado apenas com os caracteres 0-9, -, +, e ‘.’. Esta função é, em grande medida, desnecessária devido à conversão automática de dados feita pelo Oracle, exceto quando usado para uma coluna de caracteres contendo números em um ORDER BY ou uma comparação. AVG é uma função de agregação que calcula a média dos valores passados como parâmetros. Por fim o TO_NOTNULL não existe dentro das referências de funções para o Oracle. Gabarito D

15. BANCA: FCC ANO: 2015 ÓRGÃO: JUDICIÁRIO - ANÁLISE DE SISTEMAS

TRE-RR

PROVA:

ANALISTA

Com relação aos subprogramas PL/SQL, considere: I. São blocos PL/SQL nomeados que podem ser chamados com um conjunto de parâmetros. II. Podem ser um procedure ou uma função. Em geral, a função é usada para realizar uma ação e o procedure para calcular e retornar um valor. 16712855225

III. Podem ser agrupados em pacotes PL/SQL. IV. Podem ser declarados e definidos de duas formas: ou em um bloco PL/SQL ou em outro subprograma. Está correto o que se afirma APENAS em: A II e IV. B I, III e IV. C I, II e III. D III e IV. Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

83 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 E I e III. Gabarito B. Analisando cada uma das alternativas. Um subprograma PL/SQL é um bloco nomeado que pode ser invocado repetidas vezes. Se o subprograma possui parâmetros, seus valores podem diferir a cada chamada ou execução. Confirmamos então o item I como correto. No item II temos um erro. Um subprograma pode ser um procedimento ou uma função. Tipicamente, um procedimento é usado para realizar uma ação e uma função para computar e retornar um valor. Subprogramas suportam o desenvolvimento e manutenção de código que permite o reuso e que seja confiável. Eles possuem também as seguintes características: >> Modularidade. >> Projeto de aplicação facilitado. >> Manutenibilidade. >> Empacotamento. >> Reusabilidade. >> Melhor performance. Um subprograma pode ser: 1. Nested (Aninhado): declarado em um bloco PL/SQL (que pode ser outro subprograma). 2. Package (Empacotado): criado dentro de um pacote. 3. Standalone: criado no nível de esquema. Essa última classificação confirma IV, que afirma que existem duas formas de declarar e definir um subprograma PL/SQL. Sabemos que se existem três formas de realizar essa operação, também existem duas. 16712855225

16. BANCA: FCC ANO: 2015 ÓRGÃO: TRT - 3ª REGIÃO (MG) PROVA: ANALISTA JUDICIÁRIO - TECNOLOGIA DA INFORMAÇÃO Considere uma tabela chamada emp em um banco de dados Oracle aberto e em condições ideais, que possui dentre os campos, nome (cadeia de caracteres), salario (valor real) e comissao (número real). Utilizando PL/SQL, neste caso, para exibir A o nome e o salário do funcionário somado à comissão, considerando que o conteúdo de alguns campos comissao estão nulos, utiliza-se a instrução SELECT nome, salario+NIL(comissao) FROM emp;

Prof. Thiago Rodrigues Cavalcanti

www.estrategiaconcursos.com.br

84 de 116

PL/SQL Prof. Thiago Rodrigues Cavalcanti Aula 05 B os dados de todos os funcionários cujos nomes possuam a letra a como segundo caractere, utiliza-se a instrução SELECT * FROM emp WHERE nome LIKE '_a%'; C os dados dos funcionários cujo nome seja 'Pedro' ou 'Ana' utiliza-se a instrução SELECT * FROM emp WHERE nome LIKE ('Pedro','Ana'); D os dados de todos os funcionários cujo conteúdo do campo salario esteja entre 1000 e 2000 utiliza-se a instrução SELECT * FROM emp WHERE salario BETWEEN (1000,2000); E os dados dos funcionários cujo conteúdo do campo nome tenha nos primeiros 4 caracteres a palavra 'Mari' utiliza-se a instrução SELECT * FROM emp WHERE SUBSTRING(nome,FIRST)= 'Mari'; Comentário. Seguimos nossa odisseia de analisar cada uma das alternativas de cada questão, procurando tecer comentário que agreguem conhecimento e ajudem na sua aprovação. Já vimos que a função definida pelo Oracle para verificar se uma variável é nula e atribuir a mesma um valor padrão é a função NVL. Vejam que a questão sugere que seja NIL, essa palavra não existe na documentação do SGBD. Sendo assim podemos considerar a alternativa incorreta. A alternativa B é a nossa resposta. Vale lembra que quando utilizamos o LIKE para comparar os valores de uma coluna com uma parte de um texto podemos usar o _ (underline) para dizer ao compilador que aquele espaço de um caractere pode adquirir qualquer valor do conjunto de caracteres. Podemos também utilizar o % para determinar que o mesmo representa qualquer quantidade de caracteres dentro do tamanho definido para a string. Vamos agora para a alternativa C. O erro desta alternativa está no uso incorreto da palavra LIKE, no seu lugar deveríamos usar o IN para verificarmos se o nome em cada coluna está ou não no conjunto ('Pedro','Ana'). O erro da alternativa seguinte está na sintaxe incorreta do BETWEEN. Quando usamos esta restrição devemos passar os limites que estamos interessados com a palavra AND entre os dois valores. No Oracle a expressão expr1 BETWEEN expr2 AND expr3 é semelhante a expr2