Oracle PL/SQL: cursor e exceções

Formatando ambiente - Apresentação

Olá, tudo bem? Para quem não me conhece, meu nome é Victorino Vila e serei o instrutor deste treinamento em que vamos dar foco em cursores e exceções.

Victorino Vila é um homem de pele clara, olhos castanhos e cabelo e barba grisalhos. Está com uma camisa verde-escura. Ao fundo, uma parede azul-clara lisa sem decorações.

Pré-requisitos

Vamos começar este curso com a recuperação do ambiente de trabalho, onde vamos fazer a carga de toda a base que trabalhamos anteriormente no curso Oracle PL/SQL: procedures, funções e exceções.

Este curso é como uma continuação do curso anterior e faz parte da formação Consultas com Oracle Database.

Conteúdo

Agora, vamos abordar cursores e exceções. Mas, ainda vamos falar um pouco sobre procedures e funções na parte de passagem de parâmetros.

Assim, vamos entender que podemos passar parâmetros e dar características a forma com que esse parâmetro vai entrar na procedure, além da forma com que esse valor do parâmetro vai voltar para o programa que chama a mesma procedure.

Depois, vamos abordar um assunto muito interessante que é o diferencial do PL/SQL em relação ao SQL padrão: os comandos de repetição (LOOPs). Vamos abordar três tipos de LOOPs: o LOOP-END LOOP, o FOR LOOP e o WHILE LOOP. Cada um deles tem sua própria característica, como vamos aprender nesse treinamento.

Em seguida, vamos aprender como um cursor funciona. Cursor é um pedaço ou a totalidade de uma consulta (query com comando SELECT) que vamos trazer do banco de dados e colocar em memória. Esse resultado do comando SELECT vai para uma variável que podemos manipular dentro do programa PL/SQL.

Finalmente, vamos falar de exceções. As exceções são os erros que um programa pode ter. Vamos aprender a tratar esses erros e a transformá-los em textos mais amigáveis. Desse modo, as pessoas usuárias ou que utilizem nossas procedures, funções ou queiram fazer programas em PL/SQL possam entender bem o que está acontecendo. Assim, quando houver um erro, vão saber quais ações devem ser tomadas.

Esperamos que vocês gostem deste curso. Nos encontramos nos próximos vídeos.

Formatando ambiente - Instalando Oracle

Se você está começando a formação Consultas com Oracle Database por este curso ou está seguindo a formação, mas está em uma máquina limpa para este treinamento, gostaríamos de lembrá-lo que é preciso instalar o Oracle Database e o software Oracle SQL Developer para poder seguir este curso normalmente.

Se você quer instalar esses softwares, acesse o curso "Introdução ao SQL com Oracle: manipule e consulte dados".

Dirija-se diretamente à segunda aula que se chama "Instalando Oracle e conhecendo as entidades de um banco de dados". Entre nessa aula e assistir três vídeos em sequência:

1. "Instalando o Oracle Express Edition";
2. "Instalando o Oracle Developer";
3. "Criando a conexão".

Após seguir esses três vídeos, você pode voltar e seguir em frente com os próximos vídeos desse curso sobre "Oracle PL/SQL: cursor e exceções".

Se você vem seguindo o curso normalmente e está usando a mesma máquina dos outros cursos, você não precisa fazer nada. É só seguir em frente.

Um abraço e até o próximo vídeo.

Formatando ambiente - Preparando ambiente

Todas as pessoas devem seguir os passos que vamos mostrar neste vídeo, ainda que você tenha feito todos os exercícios e exemplos do curso anterior da formação Oracle ou esteja com a máquina limpa apenas com o Oracle Database e Oracle SQL Developer instalados.

O nosso objetivo é criar um novo ambiente para este curso e carregar os dados nesse novo ambiente para que todo mundo comece esse treinamento nivelado.

Primeiro, devemos fazer o download do script "ESQUEMA.SQL", também disponibilizado na atividade "Faça como eu fiz".

Mas, não vamos executar esse script completo de uma vez só. Por favor, abram o script com um notepad ou bloco de notas que você tenha no seu computador. Pode mantê-lo minimizado e abrir o Oracle SQL Developer para poder executar os comandos que estão no script. Porém, vamos executá-los em partes, isto é, alguns blocos por vez.

Criação do usuário

No ambiente do Oracle SQL Developer, vamos criar um script vazio usando aquela conexão que criamos no primeiro curso da formação. É a conexão que usa o usuário system, um usuário especial com privilégios elevados que pode administrar o ambiente.

Para isso, clicamos no dropdown do botão "Planilha SQL" da barra de ferramentas (ou "Alt + F10") e especificamos a conexão chamada "CONEXÃO MÁQUINA LOCAL".

Agora, voltamos arquivo ESQUEMA.SQL que baixamos para copiar as quatro primeiras linhas antes da linha pontilhada.

ALTER SESSION SET "_ORACLE_SCRIPT" = true;

CREATE USER cursoplsql2 IDENTIFIED BY cursoplsql2 DEFAULT TABLESPACE USERS;

GRANT connect, resource TO cursoplsql2;

ALTER USER cursoplsql2 QUOTA UNLIMITED ON USERS;

Na área vazia do script do usuário system, vamos colar os quatro comandos e executá-los. Para isso, selecionamos todas as linhas e apertamos o botão "Executar Instrução" que possui ícone de play no canto superior esquerdo do script (ou "Ctrl + Enter").

Saída do Script:

• Session alterado
• User CURSOPLSQL2 criado.
• Grant bem-sucedido.
• User CURSOPLSQL2 alterado.

Com isso, criamos um ambiente usando um usuário chamado cursoplsql2 cuja senha também é cursoplsql2 para poder trabalhar.

Criação da conexão

Após criar o usuário, vamos criar a conexão. No painel de "Conexões" à esquerda, vamos clicar no botão "Conexões" no canto superior esquerdo que possui um ícone de "+".

Na janela aberta "Novo/Selecionar Conexão do Banco de Dados", vamos escrever:

• Nome da nova conexão: CURSO PLSQL 2 (maiúsculas separado)
• Nome do usuário: cursoplsql2 (minúsculas tudo junto)
• Senha: cursoplsql2 (minúsculas tudo junto)

Clicamos no botão "Testar" do canto inferior direito para fazer um teste na conexão.

Status: Com Sucesso

Conexão feita com sucesso. Então, podemos clicar no botão "Salvar" e em seguida no botão "Conectar".

Na janela "Informações de Conexão" que se abre, vamos escrever a nova senha cursoplsql2 e apertar o botão "OK". Com isso, fizemos a conexão nesse ambiente "CURSO PLSQL 2".

Carregamento de dados

O que vamos fazer agora é criar um novo script utilizando essa nova conexão. Para isso, clicamos no dropdown do botão "SQL Worksheet" da barra de ferramentas (ou "Alt + F10") e especificamos a conexão chamada "CURSO PLSQL 2".

Voltamos ao bloco de notas do ESQUEMA.SQL para copiar desde a primeira linha abaixo do pontilhado até uma linha antes do próximo pontilhado. Ou seja, vou selecionar de CREATE TABLE SEGMERCADO até INSERT INTO PRODUTO_VENDA_EXERCICIO.

CREATE TABLE SEGMERCADO
(
ID NUMBER(5)
,DESCRICAO VARCHAR2(100)
);

CREATE TABLE CLIENTE
(
ID NUMBER(5)
,RAZAO_SOCIAL VARCHAR2(100)
,CNPJ VARCHAR2(20)
,SEGMERCADO_ID NUMBER(5)
,DATA_INCLUSAO DATE
,FATURAMENTO_PREVISTO NUMBER(10,2)
,CATEGORIA VARCHAR2(20)
);

ALTER TABLE SEGMERCADO ADD CONSTRAINT SEGMERCACO_ID_PK
PRIMARY KEY (ID);

ALTER TABLE CLIENTE ADD CONSTRAINT CLIENTE_ID_PK
PRIMARY KEY (ID);

ALTER TABLE CLIENTE ADD CONSTRAINT CLIENTE_SEGMERCADO_ID
FOREIGN KEY (SEGMERCADO_ID) REFERENCES SEGMERCADO (ID);


CREATE TABLE PRODUTO_EXERCICIO
(
COD VARCHAR2(5)
,DESCRICAO VARCHAR2(100)
,CATEGORIA VARCHAR2(100)
);

CREATE TABLE PRODUTO_VENDA_EXERCICIO
(
ID NUMBER(5)
,COD_PRODUTO VARCHAR2(5)
,DATA DATE
,QUANTIDADE FLOAT
,PRECO FLOAT
,VALOR_TOTAL FLOAT
,PERCENTUAL_IMPOSTO FLOAT
);

ALTER TABLE PRODUTO_EXERCICIO ADD CONSTRAINT PRODUTO_EXERCICIO_COD_PK
PRIMARY KEY (COD);

ALTER TABLE PRODUTO_VENDA_EXERCICIO ADD CONSTRAINT PRODUTO_VENDA_EXERCICIO_ID_PK
PRIMARY KEY (ID);

ALTER TABLE PRODUTO_VENDA_EXERCICIO ADD CONSTRAINT PRODUTO_VENDA_EXERCICIO_PRODUTO_EXERCICIO_COD
FOREIGN KEY (COD_PRODUTO) REFERENCES PRODUTO_EXERCICIO (COD);

Insert into SEGMERCADO (ID,DESCRICAO) values ('3','ATACADISTA');
Insert into SEGMERCADO (ID,DESCRICAO) values ('1','VAREJISTA');
Insert into SEGMERCADO (ID,DESCRICAO) values ('2','INDUSTRIAL');
Insert into SEGMERCADO (ID,DESCRICAO) values ('4','FARMACEUTICOS');

Insert into CLIENTE (ID,RAZAO_SOCIAL,CNPJ,SEGMERCADO_ID,DATA_INCLUSAO,FATURAMENTO_PREVISTO,CATEGORIA) values ('3','SUPERMERCADO CARIOCA','22222222222','1',to_date('13/06/22','DD/MM/RR'),'30000','MÉDIO');
Insert into CLIENTE (ID,RAZAO_SOCIAL,CNPJ,SEGMERCADO_ID,DATA_INCLUSAO,FATURAMENTO_PREVISTO,CATEGORIA) values ('1','SUPERMERCADOS CAMPEAO','1234567890','1',to_date('12/06/22','DD/MM/RR'),'90000','MEDIO GRANDE');
Insert into CLIENTE (ID,RAZAO_SOCIAL,CNPJ,SEGMERCADO_ID,DATA_INCLUSAO,FATURAMENTO_PREVISTO,CATEGORIA) values ('2','SUPERMERCADO DO VALE','11111111111','1',to_date('13/06/22','DD/MM/RR'),'90000','MÉDIO GRANDE');

Insert into PRODUTO_EXERCICIO (COD,DESCRICAO,CATEGORIA) values ('41232','Sabor de Verão > Laranja > 1 Litro','Sucos de Frutas');
Insert into PRODUTO_EXERCICIO (COD,DESCRICAO,CATEGORIA) values ('32223','Sabor de Verão > Uva > 1 Litro','Sucos de Frutas');
Insert into PRODUTO_EXERCICIO (COD,DESCRICAO,CATEGORIA) values ('67120','Frescor da Montanha > Aroma Limão > 1 Litro','Águas');
Insert into PRODUTO_EXERCICIO (COD,DESCRICAO,CATEGORIA) values ('92347','Aroma do Campo > Mate > 1 Litro','Mate');
Insert into PRODUTO_EXERCICIO (COD,DESCRICAO,CATEGORIA) values ('33854','Frescor da Montanha > Aroma Laranja > 1 Litro','Águas');

Insert into PRODUTO_VENDA_EXERCICIO (ID,COD_PRODUTO,DATA,QUANTIDADE,PRECO,VALOR_TOTAL,PERCENTUAL_IMPOSTO) values ('1','41232',to_date('01/01/22','DD/MM/RR'),'100','10','1000','100');
Insert into PRODUTO_VENDA_EXERCICIO (ID,COD_PRODUTO,DATA,QUANTIDADE,PRECO,VALOR_TOTAL,PERCENTUAL_IMPOSTO) values ('2','92347',to_date('01/01/22','DD/MM/RR'),'200','25','5000','15');

Voltamos para o Oracle SQL Developer e colamos o trecho no script vazio que usa a conexão "CURSO PLSQL 2".

Vamos selecionar toda a área do script e executá-lo com "Ctrl + Enter".

Saída do Script

• Table SEGMERCADO criado.
• Table CLIENTE criado.
• Table SEGMERCADO alterado.
• Table CLIENTE alterado.
• Table CLIENTE alterado.
• Table PRODUTO_EXERCICIO criado.
• Table PRODUTO_VENDA_EXERCICIO criado.
• Table PRODUTO_EXERCICIO alterado.
• Table PRODUTO_VENDA_EXERCICIO alterado.
• Table PRODUTO_VENDA_EXERCICIO alterado.
• 1 linha inserido. (x14)

Podemos conferir a saída rapidamente para verificar se teve algum erro.

Como não ocorreu nenhum erro, vamos criar um novo script usando a conexão "CURSO PLSQL 2" novamente.

Agora, vamos voltar ao bloco de notas e copiar a primeira linha que vem depois do segundo pontilhado até o final do arquivo. Ou seja, de create or replace FUNCTION até create or replace PROCEDURE. Vamos copiar e colar no novo script.

Esse script vai criar funções e procedures. Porém, não podemos executá-lo de uma vez só. Por quê?

Porque não conseguimos rodar vários comandos create simultaneamente.

Por isso, vamos selecionar e executar separadamente dez blocos de create.

Selecionamos do primeiro create or replace até END e executamos para compilar uma função.

create or replace FUNCTION categoria_cliente
(p_FATURAMENTO IN CLIENTE.FATURAMENTO_PREVISTO%type)
RETURN CLIENTE.CATEGORIA%type
IS
   v_CATEGORIA CLIENTE.CATEGORIA%type;
BEGIN
   IF p_FATURAMENTO <= 10000 THEN
     v_CATEGORIA := 'PEQUENO';
  ELSIF p_FATURAMENTO <= 50000 THEN
     v_CATEGORIA := 'MÉDIO';
  ELSIF p_FATURAMENTO <= 100000 THEN
     v_CATEGORIA := 'MÉDIO GRANDE';
  ELSE
     v_CATEGORIA := 'GRANDE';
  END IF;
  RETURN v_CATEGORIA;
END;

Saída do Script:

Function CATEGORIA_CLIENTE compilado

Depois, vamos fazer isso com o segundo create or replace até o próximo END e executar.

create or replace FUNCTION obter_descricao_segmercado
(p_ID IN SEGMERCADO.ID%type)
RETURN SEGMERCADO.DESCRICAO%type
IS
   v_DESCRICAO SEGMERCADO.DESCRICAO%type;
BEGIN
    SELECT DESCRICAO INTO v_DESCRICAO FROM SEGMERCADO WHERE ID = p_ID;
    RETURN v_DESCRICAO;
END;

Saída do Script:

Function OBTER_DESCRICAO_SEGMERCADO compilado

Vamos repetir esse processo até o final. O terceiro bloco create or replace:

create or replace FUNCTION RETORNA_CATEGORIA
(p_COD IN produto_exercicio.cod%type)
RETURN produto_exercicio.categoria%type
IS
   v_CATEGORIA produto_exercicio.categoria%type;
BEGIN
    SELECT CATEGORIA INTO v_CATEGORIA FROM PRODUTO_EXERCICIO WHERE COD = p_COD;
    RETURN v_CATEGORIA;
END;

Saída do Script:

Function RETORNA_CATEGORIA compilado

Em seguida, executamos o quarto bloco para criar outra função:

create or replace FUNCTION RETORNA_IMPOSTO 
(p_COD_PRODUTO produto_venda_exercicio.cod_produto%type)
RETURN produto_venda_exercicio.percentual_imposto%type
IS
   v_CATEGORIA produto_exercicio.categoria%type;
   v_IMPOSTO produto_venda_exercicio.percentual_imposto%type;
BEGIN
    v_CATEGORIA := retorna_categoria(p_COD_PRODUTO);
    IF TRIM(v_CATEGORIA) = 'Sucos de Frutas' THEN
        v_IMPOSTO := 10;
    ELSIF  TRIM(v_CATEGORIA) = 'Águas' THEN
        v_IMPOSTO := 20;
    ELSIF  TRIM(v_CATEGORIA) = 'Mate' THEN
        v_IMPOSTO := 15;
    END IF;
    RETURN v_IMPOSTO;
END;

Saída do Script:

Function RETORNA_IMPOSTO compilado

Já o quinto bloco de create or replace cria uma procedure:

create or replace PROCEDURE ALTERANDO_CATEGORIA_PRODUTO 
(p_COD produto_exercicio.cod%type
, p_CATEGORIA produto_exercicio.categoria%type)
IS
BEGIN
   UPDATE PRODUTO_EXERCICIO SET CATEGORIA = p_CATEGORIA WHERE COD = P_COD;
   COMMIT;
END;

Saída do Script:

Procedure ALTERANDO_CATEGORIA_PRODUTO compilado

O sexto bloco também cria uma procedure:

create or replace PROCEDURE EXCLUINDO_PRODUTO  
(p_COD produto_exercicio.cod%type)
IS
BEGIN
   DELETE FROM PRODUTO_EXERCICIO WHERE COD = P_COD;
   COMMIT;
END;

Saída do Script:

Procedure EXCLUINDO_PRODUTO compilado

O sétimo bloco é um pouco maior:

create or replace PROCEDURE INCLUINDO_DADOS_VENDA 
(
p_ID produto_venda_exercicio.id%type,
p_COD_PRODUTO produto_venda_exercicio.cod_produto%type,
p_DATA produto_venda_exercicio.data%type,
p_QUANTIDADE produto_venda_exercicio.quantidade%type,
p_PRECO produto_venda_exercicio.preco%type
)
IS
   v_VALOR produto_venda_exercicio.valor_total%type;
   v_PERCENTUAL produto_venda_exercicio.percentual_imposto%type;
BEGIN
   v_PERCENTUAL := retorna_imposto(p_COD_PRODUTO);
   v_VALOR := p_QUANTIDADE * p_PRECO;
   INSERT INTO PRODUTO_VENDA_EXERCICIO 
   (id, cod_produto, data, quantidade, preco, valor_total, percentual_imposto) 
   VALUES 
   (p_ID, p_COD_PRODUTO, p_DATA, p_QUANTIDADE, p_PRECO, v_VALOR, v_PERCENTUAL);
    COMMIT;
END; 

Saída do Script:

Procedure INCLUINDO_DADOS_VENDA compilado

O oitavo bloco cria a procedure INCLUINDO_PRODUTO.

create or replace PROCEDURE INCLUINDO_PRODUTO 
(p_COD produto_exercicio.cod%type
, p_DESCRICAO produto_exercicio.descricao%type
, p_CATEGORIA produto_exercicio.categoria%type)
IS
BEGIN
   INSERT INTO PRODUTO_EXERCICIO (COD, DESCRICAO, CATEGORIA) VALUES (p_COD, REPLACE(p_DESCRICAO,'-','>')
   , p_CATEGORIA);
   COMMIT;
END;

Saída do Script:

Procedure INCLUINDO_PRODUTO compilado

Em seguida, vamos selecionar e executar o nono bloco.

create or replace PROCEDURE incluir_cliente
(
p_ID CLIENTE.ID%type,
p_RAZAO CLIENTE.RAZAO_SOCIAL%type,
p_CNPJ CLIENTE.CNPJ%type,
p_SEGMERCADO CLIENTE.SEGMERCADO_ID%type,
p_FATURAMENTO CLIENTE.FATURAMENTO_PREVISTO%type
)
IS
   v_CATEGORIA CLIENTE.CATEGORIA%type;
BEGIN

   v_CATEGORIA := categoria_cliente(p_FATURAMENTO);

   INSERT INTO CLIENTE
   VALUES 
   (p_ID, p_RAZAO, p_CNPJ, p_SEGMERCADO, SYSDATE, p_FATURAMENTO, v_CATEGORIA);
   COMMIT;
END;

Saída do Script:

Procedure INCLUIR_CLIENTE compilado

Por fim, o último bloco é o décimo.

create or replace PROCEDURE incluir_segmercado
(p_ID IN SEGMERCADO.ID%type, p_DESCRICAO IN SEGMERCADO.DESCRICAO%type)
IS
BEGIN
   INSERT INTO SEGMERCADO (ID, DESCRICAO) VALUES (p_ID, UPPER(p_DESCRICAO));
   COMMIT;
END;

Saída do Script:

Procedure INCLUIR_SEGMERCADO compilado

Conferimos que a saída está sem erros.

Temos agora no painel "Conexões" a conexão "CURSO PLSQL 2". Se a expandimos, podemos visualizar:

• 4 tabelas: CLIENTE, PRODUTO_EXERCICIO, PRODUTO_VENDA_EXERCICIO e SEGMERCADO;
• 4 funções: CATEGORIA_CLIENTE, OBTER_DESCRICAO_SEGMERCADO, RETORNA_CATEGORIA e RETORNA_IMPOSTO;
• 6 procedures: ALTERNANDO_CATEGORIA_PRODUTO, EXCLUINDO_PRODUTO, INCLUINDO_DADOS_VENDA, INCLUIR_CLIENTE e INCLUIR_SEGMERCADO.

Pronto. Estamos preparados para começar o treinamento.

Sobre o curso Oracle PL/SQL: cursor e exceções

O curso Oracle PL/SQL: cursor e exceções possui 202 minutos de vídeos, em um total de 69 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Formatando ambiente
• Parametros IN e OUT
• LOOPs
• Cursor
• Exceções