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.
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.
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.
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:
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.
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.
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.
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:
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".
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:
CLIENTE, PRODUTO_EXERCICIO
, PRODUTO_VENDA_EXERCICIO
e SEGMERCADO
;CATEGORIA_CLIENTE
, OBTER_DESCRICAO_SEGMERCADO
, RETORNA_CATEGORIA
e RETORNA_IMPOSTO
;ALTERNANDO_CATEGORIA_PRODUTO
, EXCLUINDO_PRODUTO
, INCLUINDO_DADOS_VENDA
, INCLUIR_CLIENTE
e INCLUIR_SEGMERCADO
.Pronto. Estamos preparados para começar o treinamento.
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:
Mais de 1500 cursos completamente atualizados, com novos lançamentos todas as semanas, emProgramação, Front-end, UX & Design, Data Science, Mobile, DevOps e Inovação & Gestão.
Desafios temáticos para você turbinar seu portfólio. Você aprende na prática, com exercícios e projetos que simulam o dia a dia profissional.
Webséries exclusivas com discussões avançadas sobre arquitetura de sistemas com profissionais de grandes corporações e startups.
Emitimos certificados para atestar que você finalizou nossos cursos e formações.
Mais de 1500 cursos completamente atualizados, com novos lançamentos todas as semanas, emProgramação, Front-end, UX & Design, Data Science, Mobile, DevOps e Inovação & Gestão.
Desafios temáticos para você turbinar seu portfólio. Você aprende na prática, com exercícios e projetos que simulam o dia a dia profissional.
Webséries exclusivas com discussões avançadas sobre arquitetura de sistemas com profissionais de grandes corporações e startups.
Emitimos certificados para atestar que você finalizou nossos cursos e formações.
Luri é nossa inteligência artificial que tira dúvidas, dá exemplos práticos e ajuda a mergulhar ainda mais durante as aulas. Você pode conversar com Luri até 100 mensagens por semana.
Estude a língua inglesa com um curso 100% focado em tecnologia e expanda seus horizontes profissionais.
Acesso completo
durante 1 ano
Estude 24h/dia
onde e quando quiser
Novos cursos
todas as semanas