Exercícios

6 de abril de 2021

Exercícios Programação PL/SQL ORACLE (com resolução)

Lista de Exercícios propostos para auto desenvolvimento do aprendizado em programação, Exercícios Programação PL/SQL ORACLE (com resolução).

Para aplicação deste estudo de caso é possível usar o ORACLE Live SQL, que é um serviço da Oracle destinado ao aprendizado de SQL e PL/SQL.

É uma ferramenta que dá  acesso a seus usuários a uma instância de uma base de dados Oracle (sem necessidade de instalação), inclusive com o modelo de exemplo HR (Human Resource) para testes.

Para usar o Oracle Live SQL é necessário criar uma conta no site da Oracle, leia mais sobre clicando aqui.

Entretanto, requer conhecimento prévio em PL/SQL, para exercitar os Exercícios Programação PL/SQL ORACLE.

Exercício 1

Criar uma procedure que receba um código de pessoa como parâmetro de entrada e através de um parâmetro de saída retorne o nome da pessoa. Caso, o código seja inexistente, retorne: Pessoa não encontrada.

Script para criar a tabela e inserir dados para testes:

CREATE TABLE PESSOA(
   ID NUMBER(4) PRIMARY KEY, 
   NOME VARCHAR2(120) NOT NULL,
   CONSTRAINT UQ_NOME_PESSOA UNIQUE (NOME)
);
INSERT INTO PESSOA (ID, NOME) VALUES (1, 'João Silva');
INSERT INTO PESSOA (ID, NOME) VALUES (2, 'Maria Silva');
INSERT INTO PESSOA (ID, NOME) VALUES (3, 'Claudia Souza');
INSERT INTO PESSOA (ID, NOME) VALUES (4, 'Jurema Paschoal');
INSERT INTO PESSOA (ID, NOME) VALUES (5, 'André Paulo');
COMMIT;

Quatro casos de teste devem ser exercitados na execução da procedure criada.

  • Primeiramente, teste com o valor 4 para o parâmetro de entrada, que deve ter o retorno Jurema Paschoal.
  • Posteriormente, faça o teste com o valor 9 para o parâmetro de entrada, que deve ter o retorno Pessoa não encontrada.
  • Continue os testes com o valor NULL para o parâmetro de entrada, que deve ter o retorno Pessoa não encontrada.
  • Por fim, teste com o valor -15 para o parâmetro de entrada, que deve ter o retorno Pessoa não encontrada.

Resolução Exercício 1: 

CREATE TABLE PESSOA(
   ID NUMBER(4) PRIMARY KEY, 
   NOME VARCHAR2(120) NOT NULL,
   CONSTRAINT UQ_NOME_PESSOA UNIQUE (NOME)
);
INSERT INTO PESSOA (ID, NOME) VALUES (1, 'João Silva');
INSERT INTO PESSOA (ID, NOME) VALUES (2, 'Maria Silva');
INSERT INTO PESSOA (ID, NOME) VALUES (3, 'Claudia Souza');
INSERT INTO PESSOA (ID, NOME) VALUES (4, 'Jurema Paschoal');
INSERT INTO PESSOA (ID, NOME) VALUES (5, 'André Paulo');
COMMIT;


CREATE OR REPLACE PROCEDURE PROC_PEGARNOMEPESSOA (P_ID IN NUMBER, P_SAIDA OUT VARCHAR2)
IS
   V_QTDE NUMBER(1);
   V_NOME PESSOA.NOME%TYPE;
BEGIN
   IF P_ID > 0 AND P_ID <= 9999 THEN
      SELECT COUNT(*) INTO V_QTDE FROM PESSOA WHERE ID = P_ID;
      IF V_QTDE = 1 THEN
         SELECT NOME INTO V_NOME FROM PESSOA WHERE ID = P_ID;
         P_SAIDA := V_NOME;
      ELSE
         P_SAIDA := 'Pessoa não encontrada'; 
      END IF;
   ELSE
      P_SAIDA := 'Pessoa não encontrada'; 
   END IF;
END;




--CASO DE TESTE 1
DECLARE
   RETORNO VARCHAR2(120);
BEGIN
   PROC_PEGARNOMEPESSOA(4, RETORNO);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA PROCEDURE: ' || RETORNO);
END;

---CASO DE TESTE 2
DECLARE
   RETORNO VARCHAR2(120);
BEGIN
   PROC_PEGARNOMEPESSOA(9, RETORNO);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA PROCEDURE: ' || RETORNO);
END;

---CASO DE TESTE 3
DECLARE
   RETORNO VARCHAR2(120);
BEGIN
   PROC_PEGARNOMEPESSOA(NULL, RETORNO);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA PROCEDURE: ' || RETORNO);
END;

---CASO DE TESTE 4
DECLARE
   RETORNO VARCHAR2(120);
BEGIN
   PROC_PEGARNOMEPESSOA(-15, RETORNO);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA PROCEDURE: ' || RETORNO);
END;

 

Exercício 2

Criar uma função que receba como parâmetro de entrada o código do produto e o número que representa o percentual de aumento do produto em questão.

O script abaixo representa a tabela de PRODUTO e os registros que devem ser inseridos:

CREATE TABLE PRODUTO( 
   ID NUMBER(4) PRIMARY KEY, 
   NOME VARCHAR2(120) NOT NULL, 
   PRECO NUMBER(6,2) NOT NULL,
   CONSTRAINT UQ_NOME_PRODUTO UNIQUE (NOME) 
); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (1, 'Caneta', 2.50); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (2, 'Papel', 12.30); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (3, 'Borracha', 0.45); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (4, 'Lápis', 1.50); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (5, 'Pincel', 6); 
COMMIT;

Algumas checagens devem ser realizadas:

  • Caso, o código do produto passado não seja encontrado deve retornar o valor -999, que representará produto não encontrado.
  • O produto na execução da função, só pode sofrer aumentos de valores de percentual maiores que zero e menor ou igual a 15. Se essa regra for infringida, deve retornar o código -998. 
  • Caso todas as checagem sejam atendidas o produto deve ter seu preço aumentado no percentual passado (UPDATE) e a função deve retornar o código 0 para indicar que deu certo a execução.

Quatro casos de teste devem ser exercitados na execução da Função criada.

  • Primeiramente, teste a função passando os seguintes valores para os parâmetros de entrada, código do produto igual a 4 e percentual igual a 12.
  • Posteriormente, teste a função passando os seguintes valores para os parâmetros de entrada, código do produto igual a 3 e percentual igual a 2,32.
  • Realizar o teste a função passando os seguintes valores para os parâmetros de entrada, código do produto igual a 2 e percentual igual a 20.
  • Por fim, teste a função passando os seguintes valores para os parâmetros de entrada, código do produto igual a 9 e percentual igual a 10.

Considere o Desafio: Implemente o mínimo de um tratamento de exceções, leia esta postagem para entender: Tratamento de Exceções.

Resolução Exercício 2: 

CREATE TABLE PRODUTO( 
   ID NUMBER(4) PRIMARY KEY, 
   NOME VARCHAR2(120) NOT NULL, 
   PRECO NUMBER(6,2) NOT NULL,
   CONSTRAINT UQ_NOME_PRODUTO UNIQUE (NOME) 
); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (1, 'Caneta', 2.50); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (2, 'Papel', 12.30); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (3, 'Borracha', 0.45); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (4, 'Lápis', 1.50); 
INSERT INTO PRODUTO (ID, NOME, PRECO) VALUES (5, 'Pincel', 6); 
COMMIT;

CREATE OR REPLACE FUNCTION FUNC_AUMENTOPRECOPRODUTO (P_IDPROD IN NUMBER, P_PERC IN NUMBER)
RETURN NUMBER
IS
   RET NUMBER(3) := 0;
   V_QTDE NUMBER(1);
BEGIN
   IF P_IDPROD > 0 AND P_IDPROD <= 9999 THEN
      IF P_PERC > 0 AND P_PERC <= 15 THEN
         SELECT COUNT(*) INTO V_QTDE FROM PRODUTO WHERE ID = P_IDPROD;
         IF V_QTDE = 1 THEN
            UPDATE PRODUTO SET PRECO = PRECO + (PRECO * (P_PERC/100))
                WHERE ID = P_IDPROD;
            RET := 0;
         ELSE
            RET := -999; -- ID PRODUTO FORA DA FAIXA, ENTÃO NÃO ENCONTRADO
         END IF;
      ELSE
         RET := -998; -- PERCENTUAL FORA DA FAIXA DE VALOR PERMITIDO
      END IF;
   ELSE
   RET := -999; -- ID PRODUTO FORA DA FAIXA, ENTÃO NÃO ENCONTRADO
   END IF;
   COMMIT;
   RETURN RET;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('CODIGO DO ERRO: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('DESCRICAO DO ERRO: ' || SQLERRM);
      RETURN SQLCODE;
END;




---CASO DE TESTE 1
DECLARE
   X NUMBER(4);
BEGIN
   X := FUNC_AUMENTOPRECOPRODUTO(4, 12);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA FUNÇÃO: ' || X);
END;

---CASO DE TESTE 2
DECLARE
   X NUMBER(4);
BEGIN
   X := FUNC_AUMENTOPRECOPRODUTO(3, 2.32);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA FUNÇÃO: ' || X);
END;

---CASO DE TESTE 3
DECLARE
   X NUMBER(4);
BEGIN
   X := FUNC_AUMENTOPRECOPRODUTO(2, 20);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA FUNÇÃO: ' || X);
END;

---CASO DE TESTE 4
DECLARE
   X NUMBER(4);
BEGIN
   X := FUNC_AUMENTOPRECOPRODUTO(9, 10);
   DBMS_OUTPUT.PUT_LINE('RESULTADO DA EXECUÇÃO DA FUNÇÃO: ' || X);
END;


SELECT * FROM PRODUTO;




Para acessar mais artigos relacionados a banco de dados, clique aqui.
Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *