teorico_III

Prévia do material em texto

Inserir Título Aqui 
Inserir Título Aqui
Linguagens de 
Banco de Dados
Programando em SQL I
Responsável pelo Conteúdo:
Prof. Me. Alexander Gobbato Paulino Albuquerque
Revisão Textual:
Prof. Esp. Claudio Pereira do Nascimento
Nesta unidade, trabalharemos os seguintes tópicos:
• Introdução;
• O que é PL/SQL?
• Estrutura de Repetição WHILE LOOP;
• Instrução de Repetição FOR LOOP;
• Instrução Exit;
• Cursores.
Fonte: iStock/Getty Im
ages
Objetivos
• Abordar o conceito de programação para banco de dados PL/SQL;
• Demonstrar que os comandos de banco de dados vão além de montar estrutura para o 
armazenamento de informações e de manipulação de informações;
• Demonstrar o funcionamento das variáveis, a utilização de estrutura condicionais e 
estrutura de repetição e por fim introduziremos o conceito de cursor para armazenarmos 
os resultados em memória.
Caro Aluno(a)!
Normalmente, com a correria do dia a dia, não nos organizamos e deixamos para o 
último momento o acesso ao estudo, o que implicará o não aprofundamento no material 
trabalhado ou, ainda, a perda dos prazos para o lançamento das atividades solicitadas.
Assim, organize seus estudos de maneira que entrem na sua rotina. Por exemplo, você 
poderá escolher um dia ao longo da semana ou um determinado horário todos ou alguns 
dias e determinar como o seu “momento do estudo”.
No material de cada Unidade, há videoaulas e leituras indicadas, assim como sugestões 
de materiais complementares, elementos didáticos que ampliarão sua interpretação e 
auxiliarão o pleno entendimento dos temas abordados.
Após o contato com o conteúdo proposto, participe dos debates mediados em fóruns de 
discussão, pois estes ajudarão a verificar o quanto você absorveu do conteúdo, além de 
propiciar o contato com seus colegas e tutores, o que se apresenta como rico espaço de 
troca de ideias e aprendizagem.
Bons Estudos!
Programando em SQL I
UNIDADE 
Programando em SQL I
Introdução
Olá pessoal, nesta unidade continuaremos usando o ambiente que foi criado ante-
riormente. Caso necessário, reveja o material complementar e realize todos os proce-
dimentos novamente para ter um ambiente onde possa efetuar os testes.
Vamos relembrar, além de comando de DDL (Data Definition Language) e DML 
(Data Manipulation Language), podemos trabalhar com programação para banco 
de dados. A programação que iremos estudar se aplica nas criações de Cursores, 
Triggers, Procedimentos e Funções.
Iremos abordar os seguintes itens dos comandos PL/SQL como criação de variá-
veis, estrutura de repetições, estrutura condicional para então criarmos os cursores e 
entender então o que é um bloco de programação PL/SQL
O que é PL/SQL?
Linguagem PL/SQL (Procedural Language extensions to SQL) foi introduzida no 
ano de 1988 como parte do conjunto de tecnologias que compunha a versão 6.0 do 
SGBD Oracle. Permite a criação e desenvolvimento de programas que são armazenados, 
compilados e executados no servidor de banco de dados Oracle.
Quando Utilizar PL/SQL?
Uma das vantagens de utilizar PL/SQL é o fato de a linguagem tornar possível a cons-
trução de aplicações eficientes para a manipulação de grandes volumes de dados. Como 
o bloco PL/SQL é executado no SGDB, os dados manipulados não precisam entrar ou 
sair do SGBD, portanto, não há a necessidade de se trafegar pela rede. A eficiência da 
PL/SQL também é garantida através da sua forte integração com a linguagem. É possível 
executar comandos de SQL diretamente de um bloco PL/SQL, sem a necessidade da 
utilização de APIs intermediárias (como ODBC ou JDBC).
Resumindo, com os comandos PL/SQL podemos alterar, excluir, inserir, selecionar, 
criar variáveis, constantes, cursores, realizar tratamentos de erros/exceções e trabalhar 
com estrutura de repetições e decisões.
Este link lhe dará uma ideia do que é API, vale a pena a leitura. 
Disponível em: https://goo.gl/hSc8Sh.
Você sabia que um código escrito em PL/SQL pode ser utilizado em diferentes ver-
sões do SGBD, ou seja, na maioria dos casos, os códigos são compatíveis mesmo entre 
versões diferentes. Isso é muito interessante, não concordam?
Agora que já falamos um pouquinho sobre bloco PL/SQL, vamos começar então 
em um item muito importante, como iniciar um bloco PL/SQL?
6
7
Figura 1
Vamos ver um exemplo práticos substituindo os elementos por comando de verdade.
Figura 2
O resultado após a execução do código é:
Figura 3
Vejamos entende esse trecho de código.
Na linha 1 declaramos o início do bloco PL/SQL, na linha 2 criamos as declara-
ções, nesse caso criamos uma variável do tipo texto e atribuímos a essa variável o 
valor ‘Alexander Gobbato P. Albuquerque’.
Na linha seguinte (3) criamos o bloco onde será inserido os comandos, o bloco é ini-
ciado com o comando BEGIN e é finalizado com o comando END (linha 5).
Toda a programação será inserida entre esses comandos.
O comando dbms_output.put_line imprime o resultado na tela, ou seja, exibe 
alguma informação. 
E agora, o que é essa variável que o professor mencionou acima?
As variáveis podem auxiliar os programadores no armazenamento de dados tem-
porariamente enquanto um determinado código está em execução, vejamos então a 
sintaxe do comando para a criação de uma variável.
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_
value]
7
UNIDADE 
Programando em SQL I
Parâmetros ou Argumentos
• variable_name
O nome a atribuir à variável.
• [CONSTANT] 
Opcional. Se especificado, o valor da variável é constante e não pode ser alterado.
• datatype
O tipo de dados a atribuir à variável.
Vamos relembrar um pouquinho, vocês já viram que na criação de uma estrutura 
de tabelas devemos definir tipos de dados para as colunas.
Para as variáveis temos o mesmo conceito, ou seja, podemos criar tipos de dados 
texto, numérico, datas e booleanos.
• Texto (char, varchar2);
• Numérico (number);
• Data (Date, TimeStamp);
• Booleanos (Boolean).
Figura 4
Resultado:
Figura 5
A cláusula INTO atribui o resultado das colunas escritas no comando SELECT para as 
respectivas variáveis, ou seja, o valor da coluna first_name será armazenada na variável 
nome, o valor da coluna last_name será armazenada na variável sobrenome.
Temos também o elemento %Type.
8
9
%TYPE
É muito comum a utilização do atributo %type. O atributo %type permite declarar 
um item de dados do mesmo tipo de dados como uma variável ou coluna declarada 
anteriormente (sem saber qual é esse tipo). Se a declaração do item referenciado for 
alterada, a declaração do item de referência será alterada de acordo.
O atributo %TYPE é particularmente útil ao declarar variáveis para manter os valores 
do banco de dados. A sintaxe para declarar uma variável do mesmo tipo que uma coluna é:
variable_name table_name.column_name%TYPE;
Vamos por em prática esse exemplo:
Observem no bloco PL/SQL abaixo a variável nome e sobre estão definidas com 
o mesmo tipo das colunas da tabela employees.
Figura 6
Resultado:
Figura 7
Agora que já sabemos para que serve e como utilizar uma variável, já podemos 
então começar a falar de estrutura de decisão.
A instrução IF-THEN-ELSE é utilizada para que se possa oferecer condições para 
que o sistema execute o código quando a condição for verdadeira (TRUE) ou quando 
a condição verificada seja falsa (FALSE).
Existem algumas formas de utilizarmos o comando de declaração IF-THEN-ELSE.
9
UNIDADE 
Programando em SQL I
Sintaxe (IF-THEN)
IF condition THEN
 {...statements to execute when condition is TRUE...}
END IF;
A sintaxe IF-THEN é utilizada apenas quando queremos que o programa execute a 
condição quando for VERDADEIRA.
Sintaxe (IF-THEN-ELSE)
IF condition THEN
 {...statements to execute when condition is TRUE...}
ELSE
 {...statements to execute when condition is FALSE...}
END IF;
A sintaxe IF-THEN-ELSE é utilizada quando temos instruções diferentes a serem 
executadas quando a condição for VERDADEIRA das instruções quando a condição 
for FALSA.
Sintaxe (IF-THEN-ELSIF)
IFcondition1 THEN
 {...statements to execute when condition1 is TRUE...}
ELSIF condition2 THEN
 {...statements to execute when condition2 is TRUE...}
END IF;
A sintaxe IF-THEN-ELSIF é utilizada quando você tem mais de 2 condições para 
testar e para cada condição de instruções diferentes.
Sintaxe (IF-THEN-ELSIF-ELSE)
IF condition1 THEN
 {...statements to execute when condition1 is TRUE...}
ELSIF condition2 THEN
 {...statements to execute when condition2 is TRUE...}
ELSE
 {...statements to execute when both condition1 and condition2 
 are FALSE...}
END IF;
10
11
A sintaxe IF-THEN-ELSIF-ELSE é utilizada quando você tem mais de 2 condições 
para testar e para cada condição de instruções diferentes. Caso as condições forem 
FALSAS, o programa irá executar as instruções que foram inseridas na opção ELSE.
Quando uma condição for (VERDADEIRO) TRUE, a instrução IF-THEN-ELSE somente execu-
tará a instrução correspondente e não será avaliada as demais instruções.
Se nenhuma condição for atendida, a parte ELSE da instrução IF-THEN-ELSE será executa-
da. É importante notar que as opções ELSIF e ELSE não são obrigatórias.
Após essas explicações da sintaxe, você deve estar se perguntando: “Como posso 
aplicar essa estrutura de decisão em um caso real?”
Para demonstrar a utilização, vamos utilizar a estrutura da base de dados HR.
Vamos imaginar a seguinte situação, se você pesquisar a tabela EMPLOYEES, irá 
ver que existe uma coluna chamada SALARY, se o valor nessa coluna for menor do 
3000, devemos exibir a informação ‘REAJUSTAR EM 25%’, se o valor for maior ou 
igual a 3000 e menor do que 7000, exibir ‘REAJUSTAR EM 10%’ e para qualquer 
outro valor exibir ‘REAJUSTAM EM 3%’. Para resolver esse problema, iremos mon-
tar um bloco PL/SQL com estrutura de decisão.
Figura 8
Resultado:
Figura 9
No exemplo acima vimos o funcionamento da estrutura de repetição, que aten-
de somente quando a variável tiver apenas um único valor, e se caso o retorno do 
SELECT retornasse uma quantidade maior de registros, como poderíamos resolver 
esse desafio? A resposta vem logo abaixo, utilizando a estrutura de repetição.
11
UNIDADE 
Programando em SQL I
Estrutura de Repetição WHILE LOOP
A estrutura de repetição WHILE LOOP é utilizando quando o programador não 
tem ao certo a quantidade de vezes que as instruções precisam ser executadas.
Sintaxe:
WHILE condition
LOOP
 {...statements...}
END LOOP;
Parâmetros ou Argumentos
• condition
Avaliar cada passagem pelo loop. Se a condição for avaliada como VERDADEI-
RA, a instrução da estrutura de repetição é executada. Se a condição for avaliada 
em FALSO, a estrutura de repetição é finalizada.
• statements
Instruções a serem executadas.
Como a instrução WHILE é avaliada antes de executar a estrutura de repetição, é possível 
que a instrução nunca seja executada caso o resultado da condição seja FALSO.
Exemplo:
WHILE monthly_value <= 4000
LOOP
 monthly_value:= daily_value * 31;
END LOOP;
Neste exemplo WHILE LOOP, a repetição se encerraria quando o valor mensal 
excedesse 4000 conforme especificado na condição abaixo:
WHILE monthly_value <= 4000
O WHILE LOOP continuará enquanto o valor mensal <= 4000. E uma vez que o 
valor mensal é> 4000, o ciclo terminará.
12
13
Instrução de Repetição FOR LOOP
A instrução FOR LOOP permite que você execute as instruções por uma quantidade 
de vezes definida.
Sintaxe:
FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
 {...statements...}
END LOOP;
Parâmetros ou Argumentos
• loop_counter
Variável controladora da repetição.
• [REVERSE]
Opcional. Se especificado, o contador de loop contará no sentido inverso.
• lowest_number
O valor inicial para loop_counter.
• highest_number
O valor final para loop_counter.
• statements
Instruções a serem executadas.
A instrução FOR LOOP é utilizada quando queremos executar as instruções e o número de 
vezes que será definido na estrutura. Se precisarmos inverter a contagem, ou seja, se o 
REVERSE for especificado, então o número mais alto será o valor inicial para loop_counter 
e o valor mais baixo será o valor final para loop_counter.
Exemplo:
FOR Lcntr IN 1..20
LOOP
 LCalc := Lcntr * 31;
END LOOP;
Este exemplo acima, o FOR LOOP será executado 20 vezes. O contador chamado 
Lcntr começará em 1 e terminará em 20.
13
UNIDADE 
Programando em SQL I
Você pode usar o modificador REVERSE para executar o FOR LOOP na or-
dem inversa.
Exemplo:
FOR Lcntr IN REVERSE 1..15
LOOP
 LCalc := Lcntr * 31;
END LOOP;
Este exemplo FOR LOOP será loop 15 vezes. No entanto, como REVERSE é es-
pecificado, o contador chamado Lcntr começará em 15 e terminará em 1, ou seja, um 
loop do final para o início.
Instrução Exit
A instrução EXIT é mais usada para encerrar instruções LOOP, ou seja, mesmo 
que o LOOP não chegou ao fim, é possível interromper o processo e sair da estru-
tura de repetição.
Sintaxe:
EXIT [WHEN boolean_condition];
Parâmetros ou Argumentos
• Boolean_condition
Opcional. É a condição para terminar o LOOP.
Exemplo:
LOOP
 montante := valor * 31;
 EXIT WHEN montante > 4000;
END LOOP;
Vamos ver na prática então como funciona os elementos de estrutura de repetição. 
Observe nos exemplos abaixo que mesmo utilizando comando de repetição diferente 
podemos obter o mesmo resultado.
Na estrutura que utiliza o WHILE, precisamos obrigatoriamente definir a variável 
que será a responsável por controlar a repetição e não podemos esquecer de incre-
mentá-la para que não fique em um loop infinito.
14
15
Figura 10
Resultado:
Figura 11
Já no exemplo utilizando a estrutura FOR, a variável de controle não precisa ser 
criada na declaração, a variável já estará criada implicitamente nessa estrutura.
Figura 12
Resultado:
Figura 13
15
UNIDADE 
Programando em SQL I
Figura 14
Resultado:
Figura 15
Deve ter ficado uma pergunta na mente de vocês, para que eu vi os tipos de estru-
turas de repetição? A resposta é: “Porque nós precisaremos verificar várias informa-
ções que estarão em memória!”.
Como assim? 
Vamos lá, quando a nossa instrução de seleção retornar mais de um registro e 
precisarmos analisar todos os registros, estes serão colocados em memória. Para 
percorremos essas informações na memória, precisamos saber como funciona a 
estrutura de repetição, pois bem, a esse conjunto de registro que ficou armazenado 
em memória iremos alocá-los em cursores. 
Cursores
Os cursores são recursos empregados na manipulação de informações. Por meio 
dos cursores podemos utilizar instruções SELECT, como também INSERÇÃO, AL-
TERAÇÃO e EXCLUSÃO.
Nos exemplos a seguir iremos utilizar a tabela chamada employees que pertence a 
base de dados HR. No momento da instalação do Oracle 11g, essa tabela foi criada 
e inserido valores nela, qualquer dúvida, consulte o material das unidades anteriores 
para a instalação e configuração.
16
17
Os cursores são utilizados para que se possa armazenar os valores de retorno da instrução 
SELECT, permitindo que acessemos o valor que está em memória.
Vamos ao passo a passo para a criação do cursor e suas funcionalidades.
No exemplo a seguir, a instrução SELECT armazena o retorno da instrução em uma 
variável de nome cur1.
CURSOR cur1
IS
 SELECT first_name
 FROM employees;
Para acessarmos os valores que foram armazenados no cursor, ou seja, colocados 
em memória, devemos abrir o cursor.
OPEN cur1;
A finalidade de usar um cursor, na maioria dos casos, é recuperar as linhas do cursor para 
que algum tipo de operação possa ser executado. Depois de declarar e abrir o cursor, a 
instrução FETCH irá buscar as linhas do cursor que foi aberto.
Sintaxe:
FETCH cursor_name INTO variable_list;
Parâmetros ou Argumentos
• cursor_name
O nome do cursor que você deseja buscar linhas.
• variable_list
A lista de variáveis, delimitada por vírgula, que você deseja armazenar o conjunto 
de resultados do cursor.
No exemplo a seguir, o valor que foi armazenado na memória está sendo repassadopara a variável cnumber.
FETCH cur1 into cnumber;
Podemos utilizar uma lista de variáveis para recuperamos informações dos cursores, para 
isso, a quantidade de campos do SELECT deve ser a mesma quantidade de variáveis que 
se deve indicar depois da instrução INTO. Inclusive os tipos dos campos e os tipos das 
variáveis devem ser iguais.
17
UNIDADE 
Programando em SQL I
Como estamos trabalhando com cursores e estes deixam valores em memória, é 
importante removermos os valores da memória fechando o cursor.
CLOSE c1;
Quando trabalhamos com cursores, é importante determinarmos o status dele. O cursor 
pode ter um status de Aberto/Fechado, por exemplo.
Veja as opções de possíveis status do cursor.
%ISOPEN
• Retorna TRUE se o cursor estiver aberto, FALSE se o cursor estiver fechado.
%FOUND ou %NOTFOUND
• Retorna INVALID_CURSOR se o cursor for declarado, mas não aberto; ou se o 
cursor foi fechado;
• Retorna NULL se o cursor estiver aberto, mas a busca não foi executada;
• Retorna TRUE se uma busca foi executada;
• Retorna FALSE se nenhuma linha foi retornada.
%ROWCOUNT
• Retorna INVALID_CURSOR se o cursor for declarado, mas não aberto; ou se o 
cursor foi fechado.
• Retorna o número de linhas buscadas.
• O atributo ROWCOUNT não fornece a contagem real de linhas até que você 
tenha percorrido todo o cursor. Em outras palavras, você não deve confiar nesse 
atributo para informar quantas linhas estão em um cursor depois de aberto.
O principal uso do DBMS_OUTPUT é na depuração de blocos PL/SQL, sua procedure prin-
cipal é a PUT_LINE, que adiciona a mensagem, passada como parâmetro, ao seu buffer 
e adiciona também um caractere de nova linha no final, para que uma nova mensagem 
adicionada ao buffer fique em outra linha quando for exibida.
18
19
Vamos analisar alguns exemplos agora:
No exemplo abaixo, o select armazena o resultado na variável cursor de nome cur1, 
o cursor.
DECLARE cnumber number;
 CURSOR cur1
 IS
 SELECT EMPLOYEE_ID
 FROM EMPLOYEES
 WHERE FIRST_NAME = ‘Alexander’;
BEGIN
 OPEN cur1;
 FETCH cur1 INTO cnumber;
 if cur1%notfound then
 cnumber := 9999;
 end if;
 DBMS_OUTPUT.PUT_LINE(‘Id = ‘ || cnumber );
 CLOSE cur1;
END;
Resultado:
Id = 103
No exemplo acima, o retorno armazenado corresponde a apenas um campo e 
também possui somente um registro, isso devido a cláusula de restrição imposta 
no WHERE.
Quando trabalhamos com cursores, é possível armazenar em memória várias in-
formações de vários campos. Para acessarmos os valores armazenados, existe a ne-
cessidade de percorrermos registro a registro do cursor para verificarmos os valores. 
No exemplo de cursor abaixo, o comando select retorna mais de um registro, todos 
os registros são salvos em memória e por meio do LOOP podemos ter acesso a todos 
os registros.
19
UNIDADE 
Programando em SQL I
DECLARE 
 cnumber number; 
 cnome varchar2(100);
 CURSOR cur1
 IS
 SELECT EMPLOYEE_ID, FIRST_NAME
 FROM EMPLOYEES
 WHERE EMPLOYEE_ID < 105 ;
BEGIN
 OPEN cur1;
 LOOP
 FETCH cur1 INTO cnumber, cnome;
 DBMS_OUTPUT.PUT_LINE(‘Id = ‘ || cnumber || ‘ Nome: ‘ || cnome );
 EXIT WHEN cur1%notfound;
 END LOOP;
 CLOSE cur1;
END;
Resultado:
Figura 16
O CURSOR FOR LOOP é utilizado quando se tem a necessidade de buscar e processar todos 
os registros em um cursor. O CURSOR FOR LOOP só terminará quando todos os registros 
no cursor forem lidos.
Sintaxe:
FOR record_index in cursor_name
LOOP
 {...statements...}
END LOOP;
20
21
Parâmetros ou Argumentos
• record_index
O índice do registro.
• cursor_name
O nome do cursor do qual você deseja buscar registros.
• statements
As instruções de código para executar cada passagem pelo CURSOR FOR LOOP.
Neste exemplo, iremos armazenar no cursor todos os registros da tabela employees, 
mas somente os campos first_name e salary.
A instrução FOR employee_rec in c1 tem como objetivo localizar os valores do 
cursor linha a linha e repassar para a variável de employee_rec. A variável employee 
possui os atributos que foram selecionados da instrução: 
cursor c1 is
 SELECT first_name,salary
 FROM employees;
Nesse caso todos os dados armazenados na tabela employees serão colocados 
em memória.
DECLARE total_val number(6);
cursor c1 is
 SELECT first_name,salary
 FROM employees;
BEGIN
 total_val := 0;
 FOR employee_rec in c1
 LOOP
 total_val := total_val + employee_rec.salary;
 DBMS_OUTPUT.PUT_LINE(‘Empregado = ‘ || employee_rec.first_name );
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(‘Total = ‘ || total_val );
END;
21
UNIDADE 
Programando em SQL I
Resultado:
Figura 17
Agora que chegamos ao final desta unidade, foi possível verificar e testar algumas 
instruções como criação e atribuição de variável, demostrar a funcionalidade da es-
trutura de decisão, entender o funcionamento da estrutura de repetição e, por fim, 
aplicar tudo na criação e utilização de cursores.
22
23
Material Complementar
Indicações para saber mais sobre os assuntos abordados nesta Unidade:
 Livros
Oracle Database 11g SQL: Domine SQ e PL/SQL no banco de dados Oracle
PRICE, Jason. Oracle Database 11g SQL: Domine SQ e PL/SQL no banco de 
dados Oracle. Porto Alegre: Editora Bookmam, 2009.
Oracle Database 11g SQL: Domine SQ e PL/SQL no banco de dados Oracle
ORACLE DATABASE 11G SQL. Porto Alegre: Editora Bookman, 2008. Ed.1. ISBN-
10: 857780335X. ISBN-13: 978-8577803354.
 Vídeos
PL/SQL tutorial 26: Introduction to PL/SQL Cursor in Oracle Database By Manish Sharma
https://youtu.be/_snAMqCBitg
PL/SQL tutorial 28: Cursor Parameter In Oracle Database By Manish Sharma
https://youtu.be/w1yeQq9DAYU
23
UNIDADE 
Programando em SQL I
Referências
PRICE, Jason. Oracle Database 11g SQL: Domine SQ e PL/SQL no banco de dados 
Oracle. Porto Alegre: Editora Bookmam, 2009.
PUGA, Sandra. Banco de dados: implementação em SQL, PL/SQL e oracle 11g. São 
Paulo: Pearson Education do Brasil, 2014. 328 p. ISBN 9788581435329.
24