LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 36

Instituto Municipal de Ensino Superior de Assis

PL/SQL
1. Introdução
O PL/SQL é uma linguagem da Oracle que tem por objetivo processar
informações do banco de dados. Um bloco PL/SQL pode ser executado a partir do
SQL*Plus, do Forms, do Reports ou de qualquer linguagem PRO*Oracle.
A linguagem PL/SQL é uma extensão da linguagem SQL, vista no módulo I. A
linguagem PL/SQL oferece recursos de engenharia de software modernos, como, por
exemplo, a encapsulação de dados, o tratamento de exceções, a ocultação de informações e
a orientação a objeto, etc., trazendo os recursos de programação mais modernos para o
Oracle Server e o ToolSet.
A linguagem PL/SQL incorpora muitos recursos avançados criados em
linguagens de programação projetadas durante as décadas de 70 e 80. Além de aceitar a
manipulação de dados, ele também permite que as instruções de consulta da linguagem
SQL sejam incluídas em unidades procedurais de código e estruturadas em blocos,
tornando a linguagem SQL uma linguagem avançada de processamento de transações.
Com a linguagem PL/SQL, você pode usar as instruções SQL para refinar os dados do
Oracle e as instruções de controle PL/SQL para processar os dados.

2. Benefícios da Linguagem PL/SQL

2.1. Integração
A linguagem PL/SQL desempenha um papel central tanto para o Oracle Server,
por meio de procedimentos armazenados, funções armazenadas, gatilhos de banco de dados
e pacotes, quanto para as ferramentas de desenvolvimento Oracle, por meio de gatilhos de
componentes da Oracle Develop (Forms, Reports e Graphics).
Os tipos de dados SQL também podem ser usados no código PL/SQL.
Combinados com o acesso direto que a linguagem SQL fornece, esses tipos de dados
compartilhados integram a linguagem PL/SQL com o dicionário de dados do Oracle Server.
A linguagem PL/SQL une o acesso conveniente à tecnologia de banco de dados com a
necessidade de capacidades de programação procedural.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 37

Instituto Municipal de Ensino Superior de Assis

2.2. Melhorar Desempenho

A linguagem PL/SQL pode ser usado para agrupar as instruções SQL em um
único bloco e enviar esse bloco inteiro para o servidor em uma única chamada, reduzindo
assim o tráfego da rede. Sem o código PL/SQL, as instruções SQL seriam enviadas ao
Oracle Server uma de cada vez. Cada instrução SQL resulta em uma outra chamada do
Oracle Server e uma maior sobrecarga de desempenho. Em um ambiente de rede, a
sobrecarga pode se tornar significativa. A figura a seguir ilustra a melhora do desempenho
acima descrito:

SQL

APLICAÇÃO SQL
OUTROS
DBMS
SQL

SQL
IF ... THEN
SQL ORACLE
APLICAÇÃO ELSE
SQL com PL/SQL
END IF;
SQL

A linguagem PL/SQL também pode cooperar com as ferramentas de

desenvolvimento de aplicação do Oracle Server como, por exemplo, Oracle Develop Forms
e Reports. Ao adicionar recursos de processamento procedural a essas ferramentas, a
linguagem PL/SQL aumenta o desempenho.

2.3. Características
Operações Permitidas
• Manipulação de Dados: alteração, eliminação, inclusão e seleção;
• Criar variáveis e constantes herdando o tipo de dados e o tamanho de outras variáveis e
constantes ou de colunas de tabelas;
• Criar cursores para tratar o resultado de uma query que retorna 0 ou mais linhas;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 38

Instituto Municipal de Ensino Superior de Assis

• Criar registros para guardar o resultado de um cursor ou campos de tabelas, herdando os

tipos de dados e tamanho das colunas;
• Tratar erros;
• Criar labels para controlar o fluxo de execução;
• Utilizar comando de repetição e comparação.
• Criar triggers (gatilhos) para garantia de integridade (restrições), segurança, etc.

3. Estrutura de um Bloco PL/SQL

A estrutura de um bloco PL/SQL é composta por uma área de declaração, uma
área de comandos e uma área de exceções:
Ilustrando:

DECLARE - opcional
Declarações – variáveis, cursores, constantes, estruturas, tabelas
exceções definidas pelo usuário
BEGIN - obrigatório
Estruturas executáveis (comandos)
Instruções SQL (manipular dados do banco de dados)
Instruções PL/SQL (manipular dados no bloco)
EXCEPTION - opcional
Tratamento de exceções (pode conter outros blocos)
Ações a serem desempenhadas qdo ocorrem erros ou condições anormais
END; - obrigatório

3.1. Executando instruções e blocos PL/SQL a partir do código SQL*Plus

DECLARE
v_variable VARCHAR2(5);
BEGIN
SELECT column_name
INTO v_variable
FROM table_name;
EXCEPTION - opcional
WHEN exception_name THEN
........
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 39

Instituto Municipal de Ensino Superior de Assis

• Coloque um ponto-e-vírgula (;) no final de uma instrução SQL ou instrução de controle

PL/SQL;
• Use uma barra (/) para executar o bloco anônimo PL/SQL no buffer de SQL*Plus.
Quando o bloco for executado corretamente, sem erros que não possam ser tratados, a
saída de mensagem deverá ser a seguinte:
PL/SQL procedure successfully completed
(procedimento PL/SQL concluído corretamente)
• Coloque um ponto (.) para fechar um buffer de SQL*Plus. Um bloco PL/SQL é tratado
como uma instrução contínua no buffer e os ponto-e-vírgulas no bloco não fecham ou
executam o buffer.
• Em PL/SQL, um erro é chamado de exceção.
• Para salvar um bloco PL/SQL pode-se, também, utilizar o comando SAVE. A PL poderá
ser posteriormente executada usando o START ou @.

4. Tipos de Bloco
Anônimo Procedimento Função

[DECLARE] PROCEDURE name FUNCTION name

IS RETURN datatype
IS
BEGIN BEGIN BEGIN
-- statements -- statements -- statements
RETURN value;
[EXCEPTION] [EXCEPTION] [EXCEPTION]

END; END; END;

Blocos Anônimos: são blocos sem nome. Eles são declarados em um ponto do aplicativo
onde eles devem ser executados e são passados para o mecanismo PL/SQL para serem
executados em tempo de execução. Você poderá incorporar um bloco anônimo em um
programa pré-compilador e em SQL*Plus ou Server Manager. Os gatilhos nos
componentes do Oracle Developer consistem nesses blocos.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 40

Instituto Municipal de Ensino Superior de Assis

Subprogramas (procedimentos ou funções): são blocos PL/SQL nomeados que podem

assumir parâmetros e podem ser chamados. Você pode declará-los como procedimentos ou
como funções. Geralmente, você deve usar um procedimento para desempenhar uma ação e
uma função para calcular um valor. Você poderá armazenar subprogramas no servidor ou
no nível de aplicação. Ao usar os componentes do Oracle Developer (Forms, Reports e
Graphics), você poderá declarar os procedimentos e funções como parte da aplicação (um
formulário ou relatório) e chamá-los a partir de outros procedimentos, funções e gatilhos na
mesma aplicação sempre que necessário.

OBS: este curso somente abordará blocos anônimos.

5. Uso de Variáveis
Com o código PL/SQL, você poderá declarar variáveis e depois usá-las em
instruções procedurais e SQL onde uma expressão possa ser usada.

• Armazenamento temporário de dados: os dados podem ser armazenados temporariamente

em uma ou mais variáveis para uso quando na validação da entrada de dados para
processamento posterior no processo de fluxo de dados;
• Manipulação de valores armazenados: as variáveis podem ser usadas para cálculo e
manipulação de outros dados sem acessar o banco de dados;
• Reutilização: quando declaradas, as variáveis podem ser usadas repetidamente em uma
aplicação simplesmente referenciando-as em outras instruções, incluindo outras instruções
declarativas;
• De fácil manutenção: ao usar %TYPE e %ROWTYPE, você declara variáveis, baseando
as declarações nas definições das colunas de banco de dados. As variáveis PL/SQL ou
variáveis de cursor anteriormente declaradas no escopo atual poderão usar também os
atributos %TYPE e %ROWTYPE como especificadores de tipos de dados. Se uma
definição subjacente for alterada, a declaração de variável se altera de acordo durante a
execução. Isso permite a independência dos dados, reduz custos de manutenção e permite

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 41

Instituto Municipal de Ensino Superior de Assis

que os programas se adaptem, conforme o banco de dados for alterado, para atender às
novas necessidades comerciais.

5.1. Tratando Variáveis em PL/SQL

• Declarar e inicializar as variáveis na seção de declaração;
• Atribuir novos valores às variáveis na seção executável;
• Passar valores aos subprogramas PL/SQL por meio de parâmetros: IN passa valores, OUT
retorna valores;
• Ver os resultados em um bloco PL/SQL por meio de variáveis de saída: usar variáveis de
referência.

5.2. Tipos de Variáveis PL/SQL

• Escalar: armazena um único valor.
• Composta: os registros, permitem que os grupos de campos sejam definidos e
manipulados nos blocos PL/SQL.
• Referência: armazenam valores chamados de indicadores, que designam outros itens de
programa.
• LOB: armazenam valores chamados de endereços, que especificam a localização de
objetos grandes (imagens gráficas) que são armazenados fora de linha.

5.3. Outros Tipos de Variáveis PL/SQL

• BOOLEANO: TRUE / FALSE;
• DATE: definição de data;
• BLOB: definição de imagens (fotografia);
• LONG RAW: definição de textos longos;
• BFILE: definição de imagens animadas.

5.4. Declarando Variáveis PL/SQL

Identificador [CONSTANT] tipo de dados [NOT NULL] [:= | DEFAULT expr];

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 42

Instituto Municipal de Ensino Superior de Assis

• Identificador : é nome da variável.

• CONSTANT: restringe as variáveis para que o seu valor não possa ser alterado.
• Tipo de dados: escalares, compostos, referenciais ou LOB.
• NOT NULL : restringe a variável para que ela contenha um valor.
• Expr : é uma expressão PL/SQL que pode ser uma literal, uma outra variável ou
uma expressão que envolve operadores e funções.

Exemplo de instruções:
Declare
v_nascimento DATE;
v_codigo NUMBER(5) NOT NULL := 10;
v_cidade VARCHAR2(35) := ‘Assis’;
v_numero CONSTANT NUMBER := 1234;

5.5. Atribuindo Valores à Variáveis

Identificador := expr;

• Identificador : é nome da variável.

• expr : é uma expressão PL/SQL que pode ser uma literal, uma outra variável ou
uma expressão que envolve operadores e funções.

Exemplo de instruções:
v_nascimento := ’30-AGO-01’

v_nome := ’Alex Poletto’

SQL> SELECT salario*0.20

2 INTO v_gratificacao
3 FROM professor
4 WHERE depto=2;
5.6. Tipos de Dados Escalares Básicos
Um tipo de dados escalares armazena um valor único e não possui componentes
internos. Os tipos de dados escalares podem ser classificados em quatro categorias: número,
caractere, data e booleano.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 43

Instituto Municipal de Ensino Superior de Assis

Tipo de dado Descrição

• VARCHAR2(maximum_length): Tamanho variável com até 32.767 bytes. Não há
tamanho default para as constantes e variáveis
VARCHAR2.
• NUMBER[(precisão, escala)] Tipo básico para números fixos e de ponto flutuante.
• DATE Inclui hora do dia em segundos desde a meia-noite.
Entre 4712 A.C. e 9999 D.C.
• CHAR[(maximum_length)] Tamanho fixo com até 32.767 bytes. Se você não
especificar um comprimento, o tamanho default será
definido como 1.
• LONG Tamanho variável com até 32.767 bytes. A largura
máxima de uma coluna de banco de dados LONG é de
2.147.483.647 bytes.
• LONG RAW Binaries e strings de byte de até 32.760 bytes. Não são
interpretados pelo código PL/SQL.
• BOOLEAN Lógicos: TRUE, FALSE ou NULL
• BINARY_INTEGER Inteiros entre –2.147.483.647/2.147.483.647
• PLS_INTEGER Inteiros entre –2.147.483.647/2.147.483.647. São mais
rápidos que os valores NUMBER e
BYNARY_INTEGER.

Exemplo de instruções:
v_descricao VARCHAR2(30);
v_contador BINARY_INTEGER := 0;
v_soma NUMBER(10,2) := 0;
v_reserva DATE := SYSDATE + 7;
c_taxa CONSTANT NUMBER(5,2) := 15.30;
v_filhos BOOLEAN NOT NULL := TRUE;
v_preco1 NUMBER(10,2) := 1500
v_preco2 NUMBER(10,2) := 2500
v_aumento BOOLEAN := (v_preco1 < v_preco2);

5.7. O Atributo %TYPE

Utilizado para declarar uma variável de acordo com uma definição de coluna de
banco de dados ou de acordo com outra variável anteriormente declarada.
Exemplo de instruções:
…..
v_descricao depto.descricao%TYPE
v_salario v_soma%TYPE := 0;
…..

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 44

Instituto Municipal de Ensino Superior de Assis

5.8. Variáveis de Tipo de Dados LOB

Utilizado para armazenar blocos de dados não estruturados (texto, imagens
gráficas, videoclipes e formatos de arquivo para armazenar sons) de até 4 gigabytes em
tamanho. Os tipos de dados LOB fornecem acesso eficiente, aleatório e em intervalos aos
dados, podendo ser atributos de um tipo de objeto.
Tipo de dado Descrição
• CLOB Armazenar blocos grandes de dados com caracteres de um único
byte no banco de dados.
• BLOB Armazenar objeto binários grandes no banco de dados em linha ou
fora de linha.
• BFILE Armazenar objetos grandes binários em arquivos do sistema
operacional fora do banco de dados.
• NCLOB Armazenar blocos grandes de dados NCHAR de byte único ou de
bytes múltiplos de largura fixa no banco de dados, dentro e fora de
linha.

5.9. Variáveis de Ligação

Uma variável de ligação é uma variável que você declara em um ambiente de
host e usa para passar valores de tempo de execução, número ou caractere, para ou de um
ou mais programas PL/SQL, os quais podem usá-la como usariam qualquer outra variável.

Exemplo de instruções para declarar

SQL> VARIABLE retorna_codigo NUMBER
SQL> VARIABLE retorna_mensagem VARCHAR2(35)

Exemplo de instruções para exibir

SQL> PRINT retorna_codigo

5.10. DBMS_OUTPUT.PUT_LINE
• Um procedimento de pacote fornecido pela Oracle;
• Uma alternativa para exibir dados a partir de um bloco PL/SQL;
• Deverá ser ativado em SQL*Plus com SET SERVEROUTPUT ON

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 45

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:

SET SERVEROUTPUT ON
ACCEPT v_anual PROMPT ‘Entre com o salário anual.:’
DECLARE
v_salario NUMBER(10,2) := &v_anual;
BEGIN
v_salario := v_salario/12;
DBMS_OUTPUT.PUT_LINE (‘O salário mensal é ‘ | | TO_CHAR(v_salario));
END;

6. Criando Instruções Executáveis

6.1. Diretrizes e Sintaxe de Bloco PL/SQL
• As instruções podem continuar por várias linhas;
• As unidades lexicais podem ser separadas por: espaços, delimitadores, identificadores,
literais e comentários.
Delimitadores
Símbolos Simples Símbolos Compostos
Símbolo Significado Símbolo Significado
+ Adicionar <> Relacional
- Subtrair != Relacional
* Multiplicar || Concatenação
/ Dividir -- Comentário de 1 linha
= Relacional /* Inicia comentário
@ Acesso remoto */ Finaliza comentário
: Finalizador instruções := Atribuição

Identificadores
• Podem conter até 30 caracteres;
• Não podem conter palavras reservadas, a não ser que estejam entre aspas duplas
(“SELECT”);
• Devem ser iniciados por um caractere alfabético;
• Não devem ter o mesmo nome de uma coluna de tabela de banco de dados.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 46

Instituto Municipal de Ensino Superior de Assis

Literais
• (é um valor booleano, um valor numérico explícito, um caractere ou uma string não
representados por um identificador.
o Caracteres e literais de data devem estar entre aspas simples;
o Os números poderão ser valores simples ou notações científicas;
• Um bloco PL/SQL é finalizado por uma barra (/) em uma linha sozinha.

6.2. Comentando Código

• Crie prefixos de dois hífens (--) para comentários de uma única linha;
• Coloque os comentários de várias linhas entre os símbolos /* e */.

…..
v_salario NUMBER(10,2);
BEGIN
/* computer o salário annual baseado nos salários mensais
para usá-lo em cálculos */
v_salário := &v_anual * 12;
END; -- Final do bloco

6.3. Funções SQL não disponíveis em PL/SQL

• Funções de grupo: AVG, MIN, MAX, COUNT, SUM, STDDEV e VARIANCE.
Somente funcionam em instruções SQL dentro de um bloco PL/SQL

Obs: Assim não funciona: v_total := SUM (salario);

6.4. Funções PL/SQL

• Criar uma lista de correspondência:
Exemplo de instruções:

v_lista_endereco := v_nome | | CHR(10) | |

v_endereço | | CHR(10) | | v_estado | |
CHR(10) | | v_cep;

• CH(10) = ENTER

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 47

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:

v_nome := LOWER(v_nome);

• Converte em letra minúscula

6.5. Conversão de Tipos de Dados

• As funções de conversão: TO_CHAR, TO_DATE e TO_NUMBER são utilizadas para
conversão de dados compatíveis.
TO_CHAR (valor, fmt)
TO_DATE (valor, fmt)
TO_NUMBER (valor, fmt)

• valor : é uma string de caractere, número ou data

• fmt : é o modelo de formato usado para converter o valor

Exemplo de instruções:

v_data := TO_DATE (‘September 20, 2001’, ‘Month DD, YYYY’);

6.6. Operadores em PL/SQL

Operador Operação
**, NOT Exponenciação, negação, lógica
+, - Identidade, negação
*, / Multiplicação, divisão
+, -, || Adição, subtração, concatenação
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparação
AND Conjunção
OR Disjunção

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 48

Instituto Municipal de Ensino Superior de Assis

7. Interagindo com o Oracle Server

7.1. Instruções SQL em PL/SQL
• Extrair uma linha de dados de um banco de dados usando o comando SELECT;
• Fazer alterações nas linhas no banco de dados usando os comandos DML;
• Controlar uma transação com o comando COMMIT, ROLLBACK ou SAVEPOINT;
• Determinar o resultado do DML com cursores implícitos.

Comparando os Tipos de Instruções SQL e PL/SQL

• Um bloco PL/SQL não é uma unidade de transação. Os comandos COMMIT,
SAVEPOINT e ROLLBACK são independentes dos blocos, mas você pode emitir esses
comandos em um bloco;
• O PL/SQL não suporta instruções em DDL como, por exemplo, CREATE TABLE,
ALTER TABLE ou DROP TABLE;
• O PL/SQL não suporta instruções em DCL como, por exemplo, GRANT ou REVOKE.

7.2. Instruções SELECT em PL/SQL – a cláusula INTO é necessária.

Exemplo de instruções:

DECLARE
v_codigo NUMBER(2);
v_descricao VARCHAR2(15);
BEGIN
SELECT codigo, descricao
INTO v_codigo, v_descricao
FROM depto
WHERE codigo=1;
END;
*/ devem retornar apenas um valor, caso contrário ocorrerá erros */

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 49

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:

DECLARE
v_codigo depto.codigo%TYPE;
v_descricao depto.descricao%TYPE;
BEGIN
SELECT codigo, descricao
INTO v_codigo, v_descricao
FROM depto
WHERE codigo=2;
END;
*/ devem retornar apenas um valor, caso contrário ocorrerá erros */

DECLARE
v_soma_salario professor.salario%TYPE;
v_codigo NUMBER NOT NULL := 2;
BEGIN
SELECT SUM (salario)
INTO v_soma_salario
FROM professor
WHEREInstruções INSERT em PL/SQL.
codigo=v_codigo;
END;
*/ devem retornar apenas um valor, caso contrário ocorrerá erros */

7.3. Instruções INSERT em PL/SQL.

Exemplo de instruções:

BEGIN
INSERT INTO professor(codigo, nome, salario, nascimento)
VALUES (codigo_sequence.NEXTVAL, ‘Douglas’, 1450, ’10-DEC-00’);
END;

7.4. Instruções UPDATE em PL/SQL.

Exemplo de instruções:

DECLARE
v_aumento professor.salario%TYPE := 245;
BEGIN
UPDATE professor
SET salario = salario + v_aumento
WHERE codigo=10;
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 50

Instituto Municipal de Ensino Superior de Assis

7.5. Instruções DELETE em PL/SQL.

Exemplo de instruções:

DECLARE
v_codigo professor.codigo%TYPE := 9;
BEGIN
DELETE FROM professor
WHERE codigo=v_codigo;
END;

7.6. Cursor SQL

• Um cursor é uma área de trabalho SQL particular;
• Há dois tipos de cursores: implícitos e explícitos;
• O Oracle Server usa cursores implícitos para analisar e executar as instruções SQL;
• Os cursores explícitos são declarados especificamente pelo programador.
• Sempre que você emitir uma instrução SQL, o Oracle Server abrirá uma área de memória
na qual o comando é analisado e executado. Essa área é chamada de cursor.

Atributos do Cursor SQL

• Ao usar os atributos do cursor SQL, você poderá testar os resultados das instruções SQL.

SQL%ROWCOUNT Número de linhas afetadas pela instrução SQL mais recente.

SQL%FOUND Atributo booleano avaliado para TRUE se a instrução SQL mais
recente afetar uma ou mais linhas.
SQL%NOTFOUND Atributo booleano avaliado para TRUE se a instrução SQL mais
recente não afetar uma ou mais linhas.
SQL%ISOPEN Sempre é avaliado para FALSE porque o PL/SQL fecha os
cursores implícitos imediatamente após a execução.

Deletar linhas que especificam um código de um professor a partir da tabela PROFESSOR.

Imprimir o número de linhas deletadas.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 51

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:

VARIABLE rows_deleted VARCHAR2(30)

DECLARE
v_codigo NUMBER := 4;
BEGIN
DELETE FROM professor
WHERE codigo=v_codigo;
: rows_deleted := (SQL%ROWCOUNT | | ‘ rows deleted.’);
END;
/
PRINT rows_deleted

8. Criando Estruturas para Controle

8.1. Controlando o Fluxo de Execução PL/SQL
Você pode alterar o fluxo lógico de instruções dentro do bloco PL/SQL com
diversas estruturas para controle. Esta lição aborda os dois tipos de estruturas para controle
do PL/SQL: construções condicionais com a instrução IF e estruturas para controle LOOP.
Existem três formatos de instruções IF:
• IF-THEN-END IF
• IF-THEN-ELSE-END IF
• IF-THEN-ELSIF-END IF

Instruções IF

IF condição THEN
instruções
[ELSIF condição THEN
instruções;]
[ELSE
Instruções;]
END IF
• condição : é uma expressão ou vairável Booleana (TRUE, FALSE ou NULL).
• Esta associada a uma seqüência de instruções, que será executada somente se a expressão
produzir TRUE.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 52

Instituto Municipal de Ensino Superior de Assis

• THEN : é uma cláusula que associa a expressão Booleana que a precede com a
seqüência de instruções posteriores.
• instruções : pode ser uma ou mais instruções SQL ou PL/SQL.
• ELSIF : é uma palavra-chave que introduz uma expressão Booleana.
• ELSE : é uma palavra-chave que se for atingida pelo controle, executará a
seqüência de instruções que segue a palavra-chave.

Instrução IF-THEN-END IF:

Definir o código do gerente como 10 se o nome do funcionário for Alex.

Exemplo de instruções:

IF nome = ‘Alex’ THEN

mensagem := 10;
END IF;

A estrutura da instrução IF do PL/SQL é semelhante à estrutura das instruções

IF em outras linguagens procedurais. Ela permite que o PL/SQL execute ações de modo
seletivo com base em condições.

Definir um aumento de 20% do salário atual se o nome for Alex.

Exemplo de instruções:

.....
IF nome = ‘Alex’ THEN
v_novo_salario := salario * 1.20;
END IF;
......

Instrução IF-THEN-ELSE-END IF
Definir um indicador aprovado quando a média for maior-igual a sete e reprovado caso
contrário.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 53

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:

.....
IF media >= 7 THEN
v_mensagem := ‘Aprovado’;
ELSE
v_mensagem := ‘Reprovado’;
END IF;
.....

Instrução IF-THEN-ELSIF-THEN-END IF
Definir aumento salarial de acordo com as faixas salariais: até 500.00 – 25%, de 500.01 até
1500.00 -15% e acima de 1500.01 – 10%.

Exemplo de instruções:

.....
IF salario < 500.00 THEN
v_novo_salario := salario * 1.25;
ELSIF salario < 1000.00 THEN
v_novo_salario := salario + (salario*15/100);
ELSE
v_novo_salario := salario * 1.10;
END IF;
.....

8.2. Controle Iterativo: instruções LOOP

O PL/SQL oferece diversos recursos para estruturar loops para repetirem uma
instrução ou seqüência de instruções várias vezes.
As construções em loop são o segundo tipo de estrutura para controle:
• Loop básico para fornecer ações repetitivas sem condições gerais;
• Loops FOR para fornecer controle iterativo para ações com base em uma contagem;
• Loops WHILE para fornecer controle iterativo para ações com base em uma condição;
• Instrução EXIT para terminar loops.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 54

Instituto Municipal de Ensino Superior de Assis

LOOP Básico

LOOP
Instrução1;
.....
EXIT [WHEN condição];
Instruções;]
END LOOP;

Exemplo de instruções:

DECLARE
v_codigo produto.codigo%TYPE := 21;
v_contador NUMBER(2) := 1;
BEGIN
LOOP
INSERT INTO produto (codigo,numero)
VALUES(v_codigo, v_contador);
v_contador := v_contador + 1;
EXIT WHEN v_contador > 10;
END LOOP;
END;

FOR LOOP

FOR contador IN [REVERSE] lower_bound..upper_bound LOOP

instrução1;
instrução2;
.....
END LOOP;

• Usar um FOR LOOP para desviar o teste para o número de iterações;

• Não declarar o contador; ele é declarado implicitamente como um inteiro.
Os FOR LOOPs têm a mesma estrutura geral do loop básico. Além disso, eles
têm uma instrução para controle no início da palavra-chave LOOP para determinar o
número de iterações que o PL/SQL executa.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 55

Instituto Municipal de Ensino Superior de Assis

• contador : é um inteiro declarado implicitamente cujo valor aumenta ou diminui

automaticamente em 1 cada iteração do loop até o limite superior ou inferior a ser
alcançado;
• instrução : pode ser uma ou mais instruções SQL ou PL/SQL;
• REVERSE : faz o contador decrescer a cada iteração a partir do limite superior até o
limite inferior;
• lower_bound: especifica o limite inferior da faixa de valores do contador;
• upper_bound: especifica o limite superior da faixa de valores do contador.
Exemplo de instruções com inferior e superior fixos:

FOR i IN 1..3 LOOP

instrução1;
END LOOP;

Exemplo de instruções com inferior e superior declarados como variáveis:

DECLARE
v_inferior := 1;
v_superior := 50;
BEGIN
FOR i IN v_inferior..v_superior LOOP
instrução1;
END LOOP;
END;

Inserir os 5 novos produtos para o número do pedido 10.

Exemplo de instruções

DECLARE
v_codigo pedido.codigo%TYPE := 10;
BEGIN
FOR i IN 1..5 LOOP
INSERT INTO pedido (pedido, produto)
VALUES (v_codigo, i);
END LOOP;
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 56

Instituto Municipal de Ensino Superior de Assis

WHILE LOOP

WHILE condição LOOP

instrução1;
instrução2;
.....
END LOOP;

• Usar um WHILE LOOP para repetir instruções enquanto um condição for TRUE;
Utiliza-se o WHILE LOOP para repetir uma seqüência de instruções até a
condição para controle não ser mais TRUE. A condição é avaliada ao início de cada
iteração. O LOOP terminará quando a condição for FALSE. Se a condição for FALSE no
início do loop, nenhuma iteração futura será executada.
• condição : é uma expressão ou variável booleana;
• instrução : pode ser uma ou mais instruções SQL ou PL/SQL.

Exemplo de instruções:

ACCEPT v_pedido PROMPT ‘Incluir o número do pedido:’

ACCEPT v_produto PROMPT ‘Incluir o número de produtos no pedido:’
DECLARE
v_contador NUMBER(2) := 1;
BEGIN
WHILE v_contador <= &v_produto LOOP
INSERT INTO pedido (pedido, produto)
VALUES (&v_pedido, v_contador);
v_contador := v_contador + 1;
END LOOP;
COMMIT;
END;

9. Trabalhando com Tipos de Dados Compostos

9.1. Tipos de Dados Compostos
• PL/SQL RECORDS
• PL/SQL TABLES
Uma vez definidos, as tabelas e registros podem ser reutilizados.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 57

Instituto Municipal de Ensino Superior de Assis

RECORDS PL/SQL - registros

Utilizado para manipular os dados relacionados, porém diferentes, como uma
unidade lógica. Um registro é um grupo de itens de dados relacionados armazenados em
campos, cada um com seu próprio nome e tipo de dados.

• Cada registro definido pode ter tantos campos quantos forem necessários;
• Os registros podem receber atribuição de valores iniciais e podem ser definidos como
NOT NULL;
• Os campos sem valores iniciais são inicializados para NULL;
• A palavra-chave DEFAULT também pode ser usada ao definir campos;
• Você pode definir tipos RECORD e declarar registros definidos pelo usuário na parte
declarativa de qualquer bloco, subprograma ou pacote.
• Pode-se declarar e fazer referência a registros aninhados. Um registro pode ser o
componente de outro registro.

Criando um RECORD PL/SQL

TYPE type_name IS RECORD

(field_declaration[, field_declaration] ...);
identifier type_name;

field_declaration é

field_name {field_type | variable%TYPE

| tabela.coluna%TYPE | tabela%ROWTYPE}
[ [NOT NULL] { := | DEFAULT} expr]

Definir um tipo RECORD e, em seguida, declare registros desse tipo:

• type_name: é o nome do tipo RECORD;
• field_name: é o nome de um campo dentro do registro;
• field_type : é o tipo de dados do campo;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 58

Instituto Municipal de Ensino Superior de Assis

• expr : é o tipo de dados ou um valor inicial

Exemplos de instruções:

…..
TYPE tipo_registro IS RECORD
(ra VARCHAR (10),
nome VARCHAR2 (30),
nascimento DATE);
aluno_registro tipo_registro;
…..

DECLARE
TYPE tipo_registro IS RECORD
(ra VARCHAR (10) NOT NULL := 0110610123,
nome professor.nome%TYPE,
nascimento professor.nascimento%TYPE);
aluno_registro tipo_registro;
…..

DECLARE
aluno_registro professor%ROWTYPE;
.....

Para fazer referência a um campo:

record_name.field_name ---Æ aluno_registro.nome

Atribuir valores:
aluno_registro.nome := ‘Alex Poletto’

Criar uma tabela que contém dados sobre professores demitidos, fornecendo o
código do professor

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 59

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:

ACCEPT codigo_vaisair PROMPT ‘Código do professor a ser demitido:’

DECLARE
professor_vaisair professor%ROWTYPE;
BEGIN
SELECT * INTO professor_vaisair FROM professor
WHERE codigo = &codigo_vaisair;
INSERT INTO demitidos (codigo, nome, nascimento, cargo, depto, demissao)
VALUES (professor_vaisair.codigo, professor_vaisair.nome,
professor_vaisair.nascimento, professor_vaisair.cargo,
professor_vaisair.depto, SYSDATE);
COMMIT;
END;

TABLES PL/SQL - tabelas

Utilizado para fazer referências e manipular conjuntos de dados como um
objeto inteiro. Uma tabela contém uma coluna e uma chave primária para fornecer a você
acesso a linhas semelhante a array. Elas são modeladas como tabelas de banco de dados.
• Primeiramente declare um tipo de dados TABLE;
• Em seguida declare uma variável desse tipo de dados.

Criando uma TABLE PL/SQL

TYPE type_name IS TABLE OF

{column_type | variavel%TYPE
| tabela.coluna%TYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
identifier type_name;

• type_name : é o nome do tipo TABLE;

• column_type: qualquer tipo de dados escalares;
• identifier : é o nome do identificador que representa uma tabela PL/SQL inteira

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 60

Instituto Municipal de Ensino Superior de Assis

Exemplo de instruções:
Declarar uma tabela PL/SQL para armazenar nomes.

DECLARE
TYPE tabela_tipo IS TABLE OF professor.nome%TYPE
INDEX BY BINARY_INTEGER;
nome_tabela tabela_tipo;

DECLARE
TYPE nome_tabela_tipo IS TABLE OF professor.nome%TYPE
INDEX BY BINARY_INTEGER;
TYPE data_tabela_tipo IS TABLE OF DATA
INDEX BY BINARY_INTEGER;
nome_tabela nome_tabela_tipo;
nascimento_tabela data_tabela_tipo;
BEGIN
nome_tabela(1) := ‘Alex Poletto’;
data_tabela(5) := SYSDATE + 7;
IF nome_tabela.EXISTS(1) THEN
INSERT INTO …..
…..
END;

Usando Métodos de Tabela PL/SQL

Os métodos a seguir facilitam o uso de tabelas PL/SQL:

table_name.method_name[ (parâmetros) ]
Método Descrição
EXISTS(n) Retornará TRUE se o enésimo elemento em uma tabela PL/SQL existir.
COUNT Retorna o número de elementos contidos em uma tabela PL/SQL
atualmente.
FIRST Retorna o primeiro e o último número de índice de uma tabela PL/SQL.
Retornará NULL se a tabela PL/SQL estiver vazia.
LAST
PRIOR(n) Retorna o número de índice que precede o índice n em uma tabela PL/SQL
NEXT(n) Retorna o número de índice que sucede o índice n em uma tabela PL/SQL
EXTEND(n,i) Aumenta o tamanho de uma tabela PL/SQL.
EXTEND anexa um elemento nulo a uma tabela PL/SQL.
EXTEND(n) anexa n elementos nulos a uma tabela PL/SQL.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 61

Instituto Municipal de Ensino Superior de Assis

EXTEND(n,i) anexa n cópias de elemento i a uma tabela PL/SQL.

TRIM(n) TRIM remove um elemento do final de uma tabela PL/SQL
TRIM(n) remove n elementos do final de uma tabela PL/SQL
DELETE(m,n) DELETE remove todos os elementos de uma tabela PL/SQL.
DELETE(n) remove o enésimo elemento de uma tabela PL/SQL.
DELETE(m,n) remove todos os elementos na faixa m .. n de uma tabela
PL/SQL.

10. Criando Cursores Implícitos e Explícitos

O Oracle Server usa áreas de trabalho chamadas áreas SQL particulares para
executar instruções SQL e para armazenar informações de processamento. Você pode usar
cursores do PL/SQL para nomear uma área SQL particular e acessar suas informações
armazenadas. O cursor orienta todas as fases do processamento.

10.1. Cursores Implícitos

• os cursores implícitos são declarados implicitamente pelo PL/SQL para todas as
instruções DML e PL/SQL SELECT, incluindo consultas que retornam somente uma
linha.

10.2. Cursores Explícitos

• são usados para consultas que retornam mais de uma linha. Os cursores explícitos são
declarados e nomeados pelo programador e manipulados por meio de instruções
específicas nas ações executáveis do bloco.

Funções do Cursor Explícito

Use cursores explícitos para processar individualmente cada linha retornada por
uma instrução SELECT de várias linhas.
O conjunto de linhas retornado por uma consulta de várias linhas é chamado
conjunto ativo. Seu tamanho é o número de linhas que atende aos critérios da pesquisa. O
cursor explícito aponta para a linha atual do conjunto ativo. Isso permite que o programa
processe as linhas uma de cada vez.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 62

Instituto Municipal de Ensino Superior de Assis

Um programa PL/SQL abre um cursor, processa linhas retornadas por uma

consulta e, em seguida, fecha o cursor. O cursor marca a posição atual no conjunto ativo.
• Pode processar além da primeira linha retornada pela consulta, linha por linha;
• Controla que linha está sendo processada no momento;
• Permite que o programador controle as linhas manualmente no bloco PL/SQL.

Controlando Cursores Explícitos

1. Cria uma área SQL nomeada;
2. Identifica o conjunto ativo;
3. Carrega a linha atual para variáveis;
4. Testa para linhas existentes;
5. Retorna para FETCH se encontrar linhas;
6. Libera o conjunto ativo.

Controlando Cursores Explícitos Usando Quatro Comandos

1. Declare o cursor nomeando-o e definindo a estrutura da consulta a ser executada dentro
dele;
2. Abra o cursor. A instrução OPEN executa a consulta e vincula as variáveis que
estiverem refenciadas. As linhas identificadas pela consulta são chamadas conjunto ativo
e estão agora disponíveis para extração;
3. Extraia dados do cursor. Após cada extração você testa o cursor para qualquer linha
existente. Se não existirem mais linhas para serem processadas, você precisará fechar o
cursor;
4. Feche o cursor. A instrução CLOSE libera o conjunto ativo de linhas. Agora é possível
reabrir o cursor e estabelecer um novo conjunto ativo.

Declarando o Cursor

CURSOR cursor_name IS
select statement;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 63

Instituto Municipal de Ensino Superior de Assis

• Não inclua a cláusula INTO na declaração do cursor;

• Caso seja necessário o processamento de linhas em uma seqüência específica, use a
cláusula ORDER BY na consulta.
• cursor_name : é um identificador do PL/SQL.
• select_statement : é uma instrução SELECT sem uma cláusula INTO.

Exemplos de instruções:
DECLARE
v_matricula professor.matricula%TYPE;
v_nome professor.nome%TYPE;
CURSOR professor_cursor IS
SELECT matricula, nome
FROM professor
CURSOR depto_cursor IS
SELECT * FROM depto
WHERE codigo=10;
BEGIN
.....

Abrir o Cursor

OPEN cursor_name;

Extraindo Dados do Cursor

FETCH cursor_name INTO [[variavel1, variavel2, ...] | record_name];

Exemplos de instruções:

.....
OPEN defined_cursor;
LOOP
FETCH defined_cursor INTO defined_variables
EXIT WHEN ...;
.....
-- Processo dos dados
.....
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 64

Instituto Municipal de Ensino Superior de Assis

Recupere os primeiros 5 professores, um por um.

DECLARE
v_codigo professor.codigo%TYPE;
v_nome professor.nome%TYPE;
CURSOR professor_cursor IS
SELECT codigo, nome
FROM professor
BEGIN
OPEN professor_cursor;
FOR i IN 1..5 LOOP
FETCH professor_cursor INTO v_codigo, v_nome;
.....
END LOOP;
CLOSE professor_cursor;
END;

Fechar o Cursor

CLOSE cursor_name;

Obs: o parâmetro OPEN_CURSORS = número, define o número de cursores que pode ser
abertos por um usuário.

Atributos do Cursor Explícito

Atributo Tipo Descrição

%ISOPEN Booleano Será avaliado para TRUE se o cursor estiver aberto.
%NOTFOUND Booleano Será avaliado para TRUE se a extração mais recente não
retornar uma linha.
%FOUND Booleano Será avaliado para TRUE se a extração mais recente não
retornar uma linha; complemento de %NOTFOUND
%ROWCOUNT Número Será avaliado para o número total de linhas retornadas
até o momento.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 65

Instituto Municipal de Ensino Superior de Assis

%ISOPEN

IF NOT professor_cursor%ISOPEN THEN

OPEN professor_cursor;
END IF;
LOOP
FETCH professor_cursor ...

%NOTFOUND
Recupere os primeiros 5 professores, um por um.

DECLARE
v_codigo professor.codigo%TYPE;
v_nome professor.nome%TYPE;
CURSOR professor_cursor IS
SELECT codigo, nome
FROM professor
BEGIN
OPEN professor_cursor;
LOOP
FETCH professor_cursor INTO v_codigo, v_nome;
EXIT WHEN professor_cursor%ROWCOUNT > 5
OR professor_cursor%NOTFOUND;
.....
END LOOP;
CLOSE professor_cursor;
END;

10.3. Cursores e Registros

Você já constatou que pode definir registros para usar a estrutura de colunas em
uma tabela. Você também pode definir um registro com base na lista selecionada de
colunas em um cursor explícito. Isso é conveniente para processar as linhas do conjunto
ativo, porque você pode simplesmente extrair para o registro. Assim, os valores das linhas
são carregados diretamente para os campos correspondentes do registro.
Use um cursor para recuperar código e nome de professores e preencher uma
tabela de banco de dados temporária com essas informações.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 66

Instituto Municipal de Ensino Superior de Assis

Exemplos de instruções:

DECLARE
CURSOR professor_cursor IS
SELECT matricula, nome
FROM professor
professor_temp professor_cursor%ROWTYPE;
BEGIN
OPEN professor_cursor;
LOOP
FETCH professor_cursor INTO professor_temp;
EXIT WHEN professor_cursor%NOTFOUND;
INSERT INTO temp_list (tempmat, tempnome)
VALUES (professor_temp.matricula, professor_temp.nome);
END LOOP;
COMMIT;
CLOSE professor_cursor;
END;

11. Conceitos de Cursor Explícito Avançado

11.1 Cursores com Parâmetros

CURSOR cursor_name
[(parameter_name tipo de dados, ...)]
IS
select_statement;

Parâmetros permitem que valores sejam passados para um cursor quando ele é
aberto e sejam usados na consulta quando ela é executada. Isso significa que você pode
abrir e fechar um cursor explícito várias vezes em um bloco, retornando um conjunto ativo
diferente em cada ocasião.
Cada parâmetro formal na declaração do cursor deve ter um parâmetro real
correspondente na instrução OPEN. Os tipos de dados de parâmetro são iguais aos das
variáveis escalares, porém você não define tamanho para eles. Os nomes de parâmetros são
para referência na expressão de consulta do cursor.
• cursor_name : é um identificador do PL/SQL.
• parameter_name : é o nome de um parâmetro.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 67

Instituto Municipal de Ensino Superior de Assis

• select_statement : é uma instrução SELECT sem uma cláusula INTO.

• tipos de dados : é um tipo de dados escalares do parâmetro.

Passar o código do depto e do cargo para a cláusula WHERE

DECLARE
CURSOR professor_cursor2
(v_depto NUMBER, v_cargo NUMBER)
IS
SELECT matricula, nome
FROM professor
WHERE depto = v_depto AND cargo = v_cargo;
BEGIN
OPEN professor_cursor2(1,’Alex’);
.....

A cláusula FOR UPDATE

SELECT ...
FROM
FOR UPDATE [OF column_reference] [NOWAIT];

Convém bloquear linhas antes de efetuar atualização ou exclusão de linhas.

Adicione a cláusula FOR UPDATE à consulta de cursor para bloquear as linhas afetadas
quando o cursor for aberto. Como o Oracle Server libera bloqueios ao final da transação,
você não deve efetuar commit entre extrações a partir de um cursor explícito se a cláusula
FOR UPDATE for usada.

• column_reference : é uma coluna na tabela na qual a consulta é realizada

• NOWAIT : retornará um erro do Oracle se as linhas estiverem bloqueadas por
outra sessão.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 68

Instituto Municipal de Ensino Superior de Assis

Recuperar os professores que trabalham no cargo 2.

DECLARE
CURSOR professor_cargo2 IS
SELECT matricula, nome
FROM professor
WHERE cargo = 2
FOR UPDATE OF salario NOWAIT

A cláusula WHERE CURRENT OF

WHERE CURRENT OF cursor;

Ao fazer referência à linha atual de um cursor explícito, use a cláusula WHERE

CURRENT OF. Isso permite que você aplique atualizações e deleções à linha que está
sendo tratada no momento, sem necessidade de fazer referência explícita a ROWD. Você
deve incluir a cláusula FOR UPDATE na consulta do cursor para que as linhas sejam
bloqueadas em OPEN.

Exemplos de instruções:

DECLARE
CURSOR salario_cursor IS
SELECT salario
FROM professor
WHERE cargo = 2
FOR UPDATE OF salario NOWAIT;
BEGIN
FOR professor_cursor IN salario_cursor LOOP
UPDATE professor
SET salario = professor_cursor.salario*1.15
WHERE CURRENT OF salario_cursor;
END LOOP;
COMMIT;
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 69

Instituto Municipal de Ensino Superior de Assis

12. Tratamento de Exceções

Exceção é um identificador em PL/SQL que é criado durante a execução. Ela é
criada quando ocorre um erro do Oracle ou quando você a cria explicitamente. Ela é tratada
capturando-a com um handler ou propagada para o ambiente de chamada.

Tipos de Exceção
• Predefinida pelo Oracle Server (20 erros)
• Não predefinida pelo Oracle Server
• Definida pelo usuário

Exceções Predefinidas
Nome da Exceção Número Descrição
ACCESS_INTO_NULL ORA-06530 Tentativa de atribuir valores aos
atributos de um objeto não
inicializado
COLLECTION_IS_NULL ORA-06531 Tentativa de aplicação de métodos de
conjunto diferentes de EXISTS para
um varray ou tabela aninhada não
inicializada
CURSOR_ALREADY_OPEN ORA-06511 Tentativa de abertura de um cursor já
aberto
DUP_VAL_ON_INDEX ORA-00001 Tentativa de inserção de um valor
duplicado
INVALID_CURSOR ORA-01001 Ocorreu operação ilegal de cursor
INVALID_NUMBER ORA-01722 Falha da conversão de string de
caracteres para número
LOGIN_DENIED ORA-01017 Estabelecendo login com o Oracle
com um nome de usuário ou senha
inválida
NO_DATA_FOUND ORA-01403 SELECT de linha única não retornou
dados
NOT_LOGGED_ON ORA-01012 O programa PL/SQL emite uma
chamada de banco de dados sem estar
conectado ao Oracle
PROGRAM_ERROR ORA-06501 O código PL/SQL tem um problema
interno
ROWTYPE_MISMATCH ORA-06504 Variável de cursor de host e variável
de cursor PL/SQL envolvidas em
uma atribuição têm tipos de retorno

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 70

Instituto Municipal de Ensino Superior de Assis

incompatíveis
STORAGE_ERROR ORA-06500 O PL/SQL esgotou a memória ou a
memória está corrompida
SUBSCRIPT_BEYOND_COUNT ORA-06533 Feita referência ao elemento de
varray ou tabela aninhada usando um
número de índice maior do que o
número de elementos no conjunto
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 Feita referência a um elemento
TIMEOUT_ON_RESOURCE ORA-00051 Ocorreu timeout enquanto o Oracle
está aguardando por um recurso
TOO_MANY_ROWS ORA-01422 SELECT de uma única linha retornou
mais de uma linha
VALUE_ERROR ORA-06502 Ocorreu erro aritmético, de
conversão, truncamento ou restrição
de tamanho
ZERO_DIVIDE 0RA-01476 Tentativa de divisão por zero

BEGIN
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
WHEN TOO_MANY_ROWS THEN
statement1;
WHEN NO_DATA_FOUND THEN
statement1;
statement2;
statement3;
END;

Funções para Capturar Exceções

DECLARE
v_error_code NUMBER;
v_error_message VARCHAR(255);
BEGIN
....
EXCEPTION
....
WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE;
v_error_message := SQLERRM;
INSERT INTO errors VALUES(v_error_code, v_error_message);
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 71

Instituto Municipal de Ensino Superior de Assis

13. Database Triggers

Database trigeers são blocos PL/SQL disparados automática e
implicitamente sempre que ocorrer um evento associado a uma tabela: insert, update ou
delete.

Estrutura

CREATE OR REPLACE TRIGGER nome_da_trigger

{BEFORE / AFTER}
DELETE OR INSERT OR UPDATE OF (nome_coluna1, nome_coluna2, ...)
ON nome_da_tabela
REFERENCING [OLD AS antigo] [NEW AS novo]
FOR EACH ROW
WHEN condição
DECLARE
Variáveis, constantes, etc.
BEGIN
.....
END {não colocar o nome_da_trigger};

Criar a tabela Tmp_Preco_Prod com a estrutura indicada a seguir:

Cd_Produto Number(5)
Vl_Anterior Number(10,2)
Vl_novo Number(10,2)

Exemplo de Instruções:

CREATE OR REPLACE TRIGGER Verifica_Produto

BEFORE UPDATE
OF Vl_Custo_Medio
ON PRODUTO
FOR EACH ROW
WHEN condição
BEGIN
INSERT INTO Tmp_Preco_Prod
VALUES (:OLD.Cd_Produto, :OLD.Vl_Custo_Medio, :NEW.Vl_Custo_Medio);
END;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 72

Instituto Municipal de Ensino Superior de Assis

SELECT Cd_Produto, Vl_Custo_Medio

FROM PRODUTO;

UPDATE PRODUTO
SET Vl_Custo_Medio = 2.93
WHERE Cd_Produto = 4;

SELECT Cd_Produto, Vl_Custo_Medio

FROM PRODUTO;

SELECT * FROM Tmp_Preco_Prod;

13.1. Tempo
O tempo da trigger indica quando a trigger será ativada em relação ao
disparo do evento. Os tempos de uma trigger podem ser:
• BEFORE : antes do evento
• AFTER : depois do evento

13.2. Evento de Disparo

O evento de disparo indica qual a operação de manipulação de dados
sobre uma tabela disparará a trigger. Os eventos de disparo podem ser:
• INSERT
• UPDATE
• DELETE
No caso de evento de disparo do tipo UPDATE, as colunas devem ser
especificadas após a palavra OF.

13.3. Tipo
O tipo da trigger indica quantas vezes a trigger poderá ser disparada.
Quanto ao tipo, uma trigger pode ser:

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 73

Instituto Municipal de Ensino Superior de Assis

• Comando : manipula um grupo de dados dentro de uma tabela e executa uma única
vez. Exemplo: DELETE para um grupo de linhas.
• Linha : manipula linhas de uma tabela e pode ser executada uma ou mais vezes.
Exemplo: INSERT e UPDATE para linhas específicas.
Quando o disparo de um comando de manipulação de dados afeta
múltiplas linhas, a trigger de comando dispara uma vez e a trigger de linha dispara uma vez
para cada linha afetada pelo comando.

13.4. Regras para Criação de Triggers

O número máximo de triggers possíveis em uma tabela é 12, ou seja,
todas as combinações possíveis entre tempos, eventos de disparo e tipos de trigger. Não é
permitida a criação de triggers com o mesmo tempo, evento de disparo e tipo de trigger
para uma mesma tabela. Em resumo, as triggers possíveis para uma tabela são:
• BEFORE UPDATE linha
• BEFORE UPDATE comando
• BEFORE DELETE linha
• BEFORE DELETE comando
• BEFORE INSERT linha
• BEFORE INSERT comando
• AFTER UPDATE linha
• AFTER UPDATE comando
• AFTER DELETE linha
• AFTER DELETE comando
• AFTER INSERT linha
• AFTER INSERT comando
O corpo de uma trigger não permite o uso de comandos COMMIT e
ROLLBACK, principalmente em procedimentos ou funções, chamados direta ou
indiretamente pela trigger. Além disso, não podem ser alteradas as chaves primárias, únicas
ou estrangeiras por meio do uso de triggers.

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 74

Instituto Municipal de Ensino Superior de Assis

Dentro das triggers são disponibilizados predicados para testar a

manutenção que está sendo efetuada na base de dados. Estes predicados são:
• Inserting
• Updating
• Deleting

Exemplo de Instruções:

CREATE OR REPLACE TRIGGER Exemplo2

BEFORE INSERT OR DELETE OR UPDATE ON ....
BEGIN
IF INSERTING THEN
Relação de comandos que será disparado em caso de inserção
ELSIF DELETING THEN
Relação de comandos que será disparado em caso de exclusão
ELSE
Relação de comandos que será disparado em caso de alteração
END IF
END;

Dentro de triggers de linha, pode ser feito o controle de valores antes e

depois da aplicação do evento. Estes qualificadores devem ser precedidos de (: ), exceto na
cláusula WHEN (esta cláusula somente pode ser utilizada em triggers de linha):

Evento OLD NEW

INSERT NULL Valor inserido
UPDATE Valor antes da alteração Valor após a alteração
DELETE Valor antes da exclusão NULL

Para eliminar uma trigger utiliza-se o comando DROP TRIGGER.

DROP TRIGGER nome_da_trigger

Para habilitar ou desabilitar utilize:

Habilitar uma trigger

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 75

Instituto Municipal de Ensino Superior de Assis

ALTER TRIGGER nome_da_trigger ENABLE

Habilitar todas as triggers de uma tabela

ALTER TABLE nome_da_tabela ENABLE ALL_TRIGGERS

Desabilitar uma trigger

ALTER TRIGGER nome_da_trigger DISABLE

Desabilitar todas as triggers de uma tabela

ALTER TABLE nome_da_tabela DISABLE ALL_TRIGGERS

As informações sobre as triggers de banco podem ser recuperadas a partir

da tabela USER_TRIGGERS, que possui a seguinte estrutura:

• TRIGGER_NAME : nome da trigger

• TRIGGER_TYPE : tempo e tipo da trigger
• TRIGGERING_EVENT : evento que disparará a trigger
• TABLE_OWNER : proprietário da tabela associada à trigger
• TABLE_NAME : nome da tabela associada à trigger
• WHEN_CLAUSE : condição da restrição
• STATUS : indica se está ativa (ENABLE) ou não (DISABLE)
• DESCRIPTION : declaração da trigger (nome, tempo, tipo e tabela)
• TRIGGER_BODY : todo o texto do bloco PL/SQL

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 76

Instituto Municipal de Ensino Superior de Assis

BIBLIOGRAFIA

Korth, Henry F.; Silberschatz, Abraham; Sudarschan, S. - Sistema de Banco de Dados –

Editora Makron Books – 3ª edição – 1999.

Fanderuff, Damaris - Oracle8i – utilizando SQL*Plus e PL/SQL - Editora Makron Books –

1ª edição – 2000.

Manuais da Oracle University – Introdução ao Oracle: SQL e SQL*Plus

Configurações

SERVIDOR: Pentium 500 com 64 de RAM e 800 Mega de disco. Windows NT 4.0 com
plataforma.

CLIENTES: Pentium 300 ou 500, com 64 de RAM e 250 Mega de espaço em disco.

FILOSOFIA: Client/Server

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 77

Instituto Municipal de Ensino Superior de Assis

SET SERVEROUTPUT ON [enter]

DECLARE
v_descricao VARCHAR2(35);
BEGIN
SELECT DESCRICAO
INTO v_descricao
FROM DEPTO
WHERE CODIGO=1;
DBMS_OUTPUT.PUT_LINE(v_descricao);
END;
/

Declare
begin
DBMS_OUTPUT.PUT_LINE(&v_preco1);
DBMS_OUTPUT.PUT_LINE(&v_preco2);
if &v_preco1 > &v_preco2 then
DBMS_OUTPUT.PUT_LINE('Preço 1 maior que preço 2');
else
DBMS_OUTPUT.PUT_LINE('Preço 2 maior que preço 1');
end if;
end;
/

SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER REAJUSTE_PRO
AFTER UPDATE OF PRECO ON TABPRO
FOR EACH ROW
BEGIN
IF :NEW.PRECO > :OLD.PRECO*1.20 THEN

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto
 LTP III – BANCO DE DADOS - Módulo Prático: PL/SQL e SQL*Plus 78

Instituto Municipal de Ensino Superior de Assis

DBMS_OUTPUT.PUT_LINE ('Aumento Inválido');

END IF;
END;
/
CARREGAR
START M:\ALEX\AULAS\ORACLE\SQL_PLSQL\GATI01.SQL

COMPILAR
ALTER TRIGGER REAJUSTE_PRO COMPILE

ATIVAR
ALTER TABLE TABPRO ENABLE ALL TRIGGERS;

Prof. Alex Poletto - [email protected]

www.femanet.com.br/~apoletto