Treinamento PLSQL Oracle

Prévia do material em texto

Treinamento
Carlos J. R. Silva
E-mail: cjrsilva@gmail.com
PL/SQL Oracle
Introdução ao PL/SQL
Estruturas PL/SQL
Stored Procedures
Exercícios Práticos
Material de Apoio
Site oficial da Oracle:
Oracle Database 11g XE
http://www.oracle.com/technetwork/database/database-
technologies/express-edition/downloads/index.html
Oracle SQL Developer 4
http://www.oracle.com/technetwork/developer-tools/sql-
developer/downloads/index.html
Oracle SQL Developer Data Modeler 4
http://www.oracle.com/technetwork/developer-
tools/datamodeler/downloads/index.html
Introdução ao PL/SQL
Conceitos 
básicos
Declaração de 
variáveis
Tratamento 
de exceções
Conceitos básicos
 O que é PL/SQL?
PL/SQL (Procedural Language/Structured Query
Language) é uma extensão de linguagem de
procedimentos desenvolvida pela Oracle para a SQL
Padrão, para fornecer um modo de executar a lógica de
procedimentos no banco de dados.
 Por que aprender a PL/SQL?
Independente da ferramenta front-end, você pode
usar a PL/SQL para executar o processamento no
servidor em vez de executá-lo no cliente.
 Blocos PL/SQL
A PL/SQL é chamada de linguagem estruturada em blocos.
Um bloco é uma unidade sintática que pode conter código
de programa, declarações de variáveis, handlers de erro,
procedimentos, funções e até mesmo outros blocos PL/SQL.
Um bloco é formado por três sessões:
1. Declarativa (opcional);
2. Executável;
3. Exceções (também opcional).
Figura 01: Estrutura de bloco anônimo do PL/SQL.
Conceitos básicos
 Possui dois componentes essenciais:
1) Motor PL/SQL:
• É parte do Oracle Database;
2) Client do Oracle: 
• Qualquer ferramenta que permita o 
desenvolvimento de código PL/SQL e 
execução de declarações SQL.
Ambiente de Execução
Desenvolvimento PL/SQL
 SQL*Plus: É uma ferramenta nativa da Oracle que
acompanha todas as versões do Database e está
disponível na versão gráfica (Windows) e na versão
textual (prompt);
Figura 02: Ambiente de prompt do MS-DOS do SQL*Plus.
Desenvolvimento PL/SQL
 iSQL: Versão web do SQL*Plus implantado na versão
9i. Nas versões 11g ou superior, a Oracle oferece O
Oracle SQL Developer para manipulação do seu BD.
Fonte: https://docs.oracle.com/html/A88826_01/ch12.htm
Desenvolvimento PL/SQL
 Exemplos de ambientes de desenvolvimento integrado
(IDE) que foram especialmente destinados ao
desenvolvimento de programas armazenados em
bancos de dados Oracle.
 PL/SQL Developer: 
Fabricante: Allround Automations
(www.allroundautomations.com)
 TOAD:
Fabricante: Quest (www.quest.com)
Desenvolvimento PL/SQL
 Oracle SQL Developer: 
• Desenvolvida em Java;
• Ferramenta nativa da Oracle;
• Disponibiliza além dos recursos do PL/SQL 
Developer e do TOAD, como debugger, 
wizards para montagem de querys e object
browser;
• Alguns recursos de destaque, como: Ativar
mais de uma conexão por vez e carregar 
janelas em forma de abas, para cada conexão.
Desenvolvimento PL/SQL
Figura 03: Várias conexões abertas, visualizadas na tela principal.
Desenvolvimento PL/SQL
Figura 04: Resultado da instrução, apresentada de várias maneiras em abas.
Criação do usuário Desenv.
1. Executar o Oracle SQL Developer;
2. Criar uma nova conexão:
• Usuário system senha soufabra e Alias da Rede XE;
3. Clique em Testar conexão e logo após Conectar.
Criação de usuário
Criação de usuário
4. Digitar o comando mostrado:
CREATE USER DESENV
IDENTIFIED BY DESENV
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
5. Dê poderes de DBA (Administrador de banco de Dados)
para o usuário Desenv:
• GRANT CONNECT , DBA TO DESENV;
6. Após cada comando clique em executar instrução.
7. Estabeleça a conexão como desenv.
Criar arquivo de script em diretório:
• Com editor de texto (C:\fabra\treinamento\SQL).
Gerando a base de Dados
Figura 06: Script criado com Notepad++ (https://notepad-plus-plus.org/).
Executando Script para carga de dados:
• Digite @ com o caminho e o nome do arquivo. Exemplo:
• @C:\fabra\treinamento\SQL\L01_05.sql
• @C:\fabra\treinamento\SQL\L01_11.sql
• Clique em executar script (F5) logo após Commit (F11)
Gerando a base de Dados
Criar diretamente no Oracle SQL Developer.
• Clique em tabelas com botão direto;
• Clique em nova tabela com botão esquerdo.
Gerando a base de Dados
• Deve ser declarada na Sessão Declarativa.
• Pode ser atribuído um valor inicial para a variável, um 
valor que será constante e como NOT NULL.
Sintaxe:
Identificador [CONSTANT] Tipo de Dado [NOT NULL] 
[ := | DEFAULT expressão]
Ao declarar variáveis, procure:
a) Adotar padrões para dar nomes as variáveis. 
b) Variável NOT NULL deve inicializar com um valor válido;
c) Não utilizar uma variável com o mesmo nome de uma 
coluna de tabela referenciada no bloco PL/SQL;
Declaração de variáveis
DECLARE
vNmDpto depto.NM_DEPTO%type;
vCd_depto depto.CD_DEPTO%type :='B01';
vCD_GERENTE NUMBER :=0;
BEGIN
-- Recuperar Departamento e código do Gerente
null;
END;
Declaração de variáveis
DECLARE
vNmDpto depto.NM_DEPTO%type;
vCd_depto depto.CD_DEPTO%type :='B01';
vCd_Gerente NUMBER :=0;
BEGIN
-- Recuperar Departamento e código do Gerente
select nm_depto, cd_gerente
into vNmDpto, vCd_Gerente
from depto
where cd_depto = vCd_depto;
dbms_output.put_line(vNmDpto||’ – ‘||vCd_Gerente);
END;
Declaração de variáveis
Capturando Exceção
 Uma exceção que é invocada na área de execução
do bloco e manipulada na área de exceção com
sucesso, não propagará para o próximo bloco ou
para o ambiente e o bloco termina com sucesso.
 Qualquer erro incluindo comandos dentro da área
de exceção de um bloco pode ser capturado.
 A cláusula WHEN que especifica uma exceção,
seguida por uma sequência de comandos que serão
executados quando a exceção for invocada.
Capturando Exceção
BEGIN
. . .
EXCEPTION
WHEN NO_DATA_FOUND THEN
Comando1;
. . .
WHEN TOO_MANY_ROWS THEN
Comando1;
. . .
WHEN OTHERS THEN
Comando1;
. . .
END;
Capturando Exceção
DECLARE
vNmDpto depto.NM_DEPTO%type;
vCd_depto depto.CD_DEPTO%type :='X01';
vCD_GERENTE NUMBER :=0;
BEGIN
-- Recuperar Departamento e código do Gerente
SELECT nm_depto, cd_gerente
INTO vNmDpto, vCd_Gerente
FROM depto
WHERE cd_depto = vCd_depto;
dbms_output.put_line(vNmDpto||' – '||vCd_Gerente);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Departamento '||vCd_depto||' não encontrado.');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('Departamento '||vCd_depto||' com cadastro 
duplicado.');
WHEN OTHERS THEN
dbms_output.put_line('Erro inesperado: '||sqlerrm);
END;
Procedimentos e Funções
Podem ser implementadas de três formas:
1. Como subrotina de um bloco anônimo;
2. Como procedimento independente e armazenado 
no banco de dados;
3. Como uma rotina de um pacote armazenado no 
banco de dados.
Procedimentos e Funções
CREATE OR REPLACE PROCEDURE PR_DEPARTAMENTO (pCd_depto in varchar2, 
pNmDpto out varchar2, pCD_GERENTE out number) is
BEGIN
-- Recuperar Departamento e código do Gerente
SELECT nm_depto, cd_gerente
INTO pNmDpto, pCd_Gerente
FROM depto
WHERE cd_depto = pCd_depto;
EXCEPTION
WHEN NO_DATA_FOUND THEN
pNmDpto := 'Departamento '||pCd_depto||' não encontrado.';
WHEN TOO_MANY_ROWS THEN
pNmDpto := 'Departamento '||pCd_depto||' com cadastro duplicado.';
WHEN OTHERS THEN
pNmDpto := 'Erro inesperado: '||sqlerrm;
END;
Criando uma procedure
declare
vNmDpto depto.NM_DEPTO%type;
vCD_GERENTE NUMBER :=0;
begin
pr_departamento('X01', vNmDpto, vCd_gerente); 
dbms_output.put_line(vNmDpto||' – '||vCd_Gerente);
end;
Executando uma procedure
Dúvidas!
Exercícios• Criar a executar uma função para retornar o 
departamento e a gerência.