MANIPULANDO EXCEÇÕES

Quando desenvolvemos procedures que são chamadas de outras procedures, devemos ter ciência dos processamentos e exceções de não processamento nas transações e chamadas de procedures;
Quando uma exceção é levantada na chamada de uma procedure, o controle vai imediatamente para a seção de exceção daquele bloco.
Uma exceção é considera processada se a exceção fornece um manipulador para a exceção levantada;
Quando uma exceção ocorre e é manipulada, o seguinte fluxo de código ocorre:
- A exceção é erguida/levantada;
- O controle é transferido para o manipulador de exceção;
- O bloco é terminado;
- O bloco/programa chamador continua a executar como se nada tivesse acontecido;

Sem título

EXEMPLO

CREATE OR REPLACE PROCEDURE add_department (p_name   VARCHAR2,
                                            p_mgr    NUMBER,
                                            p_loc    NUMBER) IS
BEGIN
  INSERT INTO departments(department_id,
                          department_name,
                          manager_id,
                          location_id)
  VALUES (departments_seq.NEXTVAL,
          p_name,
          p_mgr,
          p_loc);

  DBMS_OUTPUT.PUT_LINE('Departamento adicionado: ' || p_name);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Erro: adicionando departamento:' || p_name);
END add_department;
/

–CRIANDO UMA PROCEDURE PARA CHAMADA

CREATE OR REPLACE PROCEDURE CREATE_DEPARTMENTS IS
BEGIN
  add_department('Midia',100,1800);
  add_department('Edicao',99,1800);
  add_department('Propaganda',101,1800);
END;
/

–VERIFICANDO DADOS

SELECT * FROM DEPARTMENTS;

SET SERVEROUTPUT ON;

EXECUTE CREATE_DEPARTMENTS;

SELECT EMPLOYEE_ID
FROM   EMPLOYEES
WHERE  EMPLOYEE_ID IN (100,99,101);

ERRO GERADO DA CHAMADA DA PROCEDURE

Sem título2

MANIPULANDO EXCEÇÕES

Se uma exceção não é manipulada, então todos as declarações DML na chamada da procedure são feitas ROLLBACK, não efetivando nenhuma alteração em dados;
Declarações DML que não são afetadas são declarações que foram executadas antes da chamada do código PL/SQL a qual exceções não são manipuladas;

EXEMPLO

–EXEMPLO DE EXCEÇÕES NAO MANIPULADAS

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE add_department_noex (p_name VARCHAR2,
                                                 p_mgr  NUMBER,
                                                 p_loc  NUMBER ) IS
BEGIN
  INSERT INTO DEPARTMENTS (department_id,      
                           department_name,
                           manager_id,
                           location_id)
  VALUES(DEPARTMENTS_SEQ.NEXTVAL, p_name,
         p_mgr, p_loc);
  DBMS_OUTPUT.PUT_LINE('Departamento adicionado: ' || p_name);
END add_department_noex;
/

–CRIACAO DA PROCEDURE CHAMADORA

CREATE OR REPLACE PROCEDURE create_departments_noex IS
BEGIN
  add_department_noex('Midia 2',100,1800);
  add_department_noex('Edicao 2',99,1800);
  add_department_noex('Propaganda 2',101,1800);
END;

–CHAMADA DA PROCEDURE NOEX

EXECUTE create_departments_noex;

SELECT * FROM DEPARTMENTS;

Perceba que neste último exemplo, sem o tratamento de exceção, uma única linha não consistente não deixou que as demais linhas fossem incluídas na tabela;

Sem título3

EXCLUSÃO DE PROCEDURES

Para se fazer a exclusão de uma procedure, utilizamos o comando:
- DROP PROCEDURE
Verifique antes na documentação se essa procedure não está sendo utilizada por nenhuma aplicação;
Caso você apague uma procedure que é chamada por outros objetos, o ORACLE não irá alertá-lo sobre isso;
As procedures ou objetos que são dependentes da procedure apagada ficam em estado “pendentes”;

Sem título4

VISUALIZANDO INFORMAÇÕES DE PROCEDURES UTILIZANDO DICIONARIO DE DADOS DAS VIEWS

O código fonte de subprogramas PL/SQL é armazenado no dicionário de dados;
Para ver os códigos armazenados, execute select nas declarações seguidas das tabelas:
- Tabela USER_SOURCE mostra o código PL/SQL do seu dono;
- Tabela ALL_SOURCE mostra o código a qual você tem direito concedido de EXECUTE pelo dono do subprograma;

DESCRIBE USER_SOURCE;

select * from USER_SOURCE;

DESCRIBE ALL_SOURCE;

select * from ALL_SOURCE;

LOOPS (LAÇOS)

Loops são repetições de um determinado trecho de código, ou seja, as instruções são executadas por várias vezes;
Em PL/SQL existem três tipos de loops;
- LOOP BÁSICO (LAÇO);
- LOOP FOR (PARA);
- LOOP WHILE (ENQUANTO)

EXEMPLO DE LOOP BÁSICO

–LOOPS BASICO

DESCRIBE DEPARTMENTS;

DECLARE
  v_max_department_id  number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador           number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;
  
  v_novo_departamento := '&NovoDepto';

  LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);
    
    v_contador := v_contador + 1;
    
    EXIT WHEN v_contador > 4;
  END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

EXEMPLO DE LOOP WHILE

DECLARE
  v_max_department_id  number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador           number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;

  v_novo_departamento := '&NovoDepto';

  WHILE v_contador < 4 LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);
    v_contador := v_contador + 1;
END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

EXEMPLO DE LOOP FOR

DECLARE
  v_max_department_id   number;
  v_novo_departamento  departments.department_name%TYPE;
  v_contador            number := 1;
BEGIN
  SELECT MAX(DEPARTMENT_ID)
  INTO       v_max_department_id
  FROM      DEPARTMENTS;

  v_novo_departamento := '&NovoDepto';

  FOR I IN 1..3 LOOP
    v_novo_departamento := v_novo_departamento || v_contador;
    
    INSERT INTO DEPARTMENTS(DEPARTMENT_ID, DEPARTMENT_NAME)
    VALUES (v_max_department_id + v_contador, v_novo_departamento);

    v_contador := v_contador + 1;
  END LOOP;
END;
/

–VERIFIQUE OS RESULTADOS

select * from departments;

INSTRUÇÃO CONTINUE

É utilizado dentro de um dos tipos de loops;
Faz com que a condição do laço seja avaliada antes de terminar o bloco do loop;
Oferece aos programadores a habilidade de transferir o controle para a próxima interação;

EXEMPLO DE CONTINUE

SET SERVEROUTPUT ON;
DECLARE
  v_soma SIMPLE_INTEGER :=0;
BEGIN
  FOR i IN 1..10 LOOP
    v_soma := v_soma + 1;
    DBMS_OUTPUT.PUT_LINE('A soma é: ' || v_soma);
    CONTINUE WHEN i > 6;
    v_soma := v_soma +1;
    DBMS_OUTPUT.PUT_LINE('Sem entrar continue, soma é: ' || v_soma);
  END LOOP;
END;
/

DATATYPE

Datatypes são tipo de dados que possuem múltiplos valores, ou seja, pode-se armazenar vários dados em uma única variável, que ao contrário do tipos char, number, date, podem armazenar somente 1 por vez;
Podem ser do tipos RECORD e COLLECTION;

RECORDS e COLLECTIONS

RECORD é quando queremos armazenar um valor com diferentes tipos de dados em uma única ocorrência naquele momento;
COLLECTION é quando queremos armazenar alguns valores do mesmo tipo de dado (record);

Sem título5

RECORD contém um ou mais componentes, também conhecidos como campos de qualquer tipo escalar (number, char, date, etc);
São similares a estrutura da linguagem C;
Trata uma coleção de campos como uma unidade lógica;

ESTRUTURA DE UM RECORD

Sem título6

MANUPILAÇÃO DOS RECORDS

Notação de um campo individual:
- CLI_RECORD.NOME;
Atribuindo um valor ao record:
- CLI_RECORD.NOME := ‘JOAO DA SILVA’;

ATRIBUTO %ROWTYPE

Declara uma variável de acordo com uma coleção de colunas de uma tabela uma view;
Campos de um RECORD tem que ter mesmos nomes e tipos de colunas de uma tabela ou view;

EXEMPLO DE %ROWTYPE

SET SERVER_OUTPUT ON;
DECLARE
  TYPE EMP_REC IS RECORD (
  v_sal       number(8),
  v_minsal    number(8) default 1000,
  v_hire_date employees.hire_date%TYPE,
  v_emp1      employees%ROWTYPE);
  v_meurec EMP_REC;
BEGIN
  v_meurec.v_sal := v_meurec.v_minsal + 500;
  v_meurec.v_hire_date := sysdate;

  SELECT  *
  INTO    v_meurec.v_emp1
  FROM    employees
  WHERE   EMPLOYEE_ID = 100;

  DBMS_OUTPUT.PUT_LINE(v_meurec.v_emp1.last_name || ' ' ||
  to_char(v_meurec.v_hire_date) || ' ' ||
  to_char(v_meurec.v_sal));
END;
/

CURSORES

Toda instrução SQL que é executada no Servidor Oracle é associado um cursor individual;
Cursor implícito é declarado e gerenciado pelo PL/SQL;
Cursor explícito é declarado e gerenciado pelo programador;

CURSORES EXPLICITOS

Sem título7

CONTROLANDO CURSORES EXPLÍCITOS

Sem título8

DECLARANDO CURSORES

Não incluir INTO na declaração do cursor, pois o carregamento dos valores será feita após o FETCH;
Caso queira que seja processado em uma determinada sequencia, utilize ORDER BY;
Um cursor pode suportar SELECTs com JOINS;

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name
    FROM    employees
    WHERE   department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  OPEN c_emp_cursor;
  FETCH c_emp_cursor INTO v_empno, v_lname;
  DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
END;
/

CURSORES COM LOOP

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name    
    FROM    employees WHERE   department_id = 30;
    v_empno employees.employee_id%TYPE;
    v_lname employees.last_name%TYPE;
BEGIN
  OPEN c_emp_cursor;
  
  LOOP
    FETCH c_emp_cursor INTO v_empno, v_lname;

    EXIT WHEN c_emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
  END LOOP;

  CLOSE c_emp_cursor ;
END;
/

LOOP FOR COM CURSORES

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor IS
    SELECT employee_id, last_name FROM employees
    WHERE department_id = 30;
BEGIN
  FOR emp_record IN c_emp_cursor
  LOOP
    DBMS_OUTPUT.PUT_LINE(emp_record.employee_id ||' '||   emp_record.last_name);
  END LOOP;
END;
/

STATUS DE CURSORES

Sem título9

UTILIZAÇÃO DO %ISOPEN

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name
    FROM    employees
    WHERE   department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  IF NOT c_emp_cursor%ISOPEN THEN
    OPEN c_emp_cursor;
  END IF;
  
  LOOP
    FETCH c_emp_cursor INTO v_empno, v_lname;
    EXIT WHEN c_emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
  END LOOP;
END;
/

UTILIZAÇÃO DO %ROWCOUNT e %NOTFOUND

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp_cursor is
    SELECT  employee_id, last_name FROM    employees
    WHERE   department_id = &cod_departamento;
  v_emp_record c_emp_cursor%ROWTYPE;
BEGIN
  OPEN c_emp_cursor;
  LOOP
    FETCH c_emp_cursor INTO v_emp_record;
    EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR
    
    c_emp_cursor%NOTFOUND;
    
    DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id
    || ' ' || v_emp_record.last_name);
  END LOOP;
  CLOSE c_emp_cursor;
END;
/

FUNÇÕES

É um bloco PL/SQL nomeado que retorna um valor;
Pode ser armazenado no banco de dados como um objeto de schema para execuções repetidas;
É chamada como parte de uma expressão ou é usada para fornecer um valor de parâmetro para outro subprograma;
Pode ser agrupadas em pacotes do PL/SQL;
O bloco PL/SQL precisa ter ao menos uma declaração de retorno (RETURN);
É criado com o comando CREATE FUNCTION;
Apesar de parâmetros OUT e IN OUT serem modos que podem utilizar as funções, não é uma boa prática utilizá-las em funções;
No entanto, se você precisa retornar mais de um valor de uma função, considere retornar valores compostos de estrutura de dados como um registro ou tabela PL/SQL;

DIFERENÇAS ENTRE PROCEDURES E FUNÇÕES

PROCEDURES	FUNCTIONS
Executa como declaração PL/SQL	Chamado com uma parte de uma expressão
Não contém cláusula RETURN no cabeçalho	Precisa conter um RETURN no cabeçalho
Pode passar valores (se precisar) usando parâmetros output	Precisa retornar um valor simples
Pode conter a declaração RETURN sem um valor	Precisa conter ao menos uma declaração RETURN

VISÃO GERAL DA CRIAÇÃO E EXECUÇÃO DE FUNÇÕES

Sem título10

CRIANDO E CHAMANDO UMA FUNÇÃO

–CRIANDO A FUNÇÃO

CREATE OR REPLACE FUNCTION get_sal
  (p_id IN employees.employee_id%TYPE)
RETURN NUMBER IS
  v_sal employees.salary%TYPE := 0;
BEGIN
  SELECT salary
  INTO   v_sal
  FROM   employees
  WHERE  employee_id = p_id;
  
  RETURN v_sal;
END get_sal;
/

–CHAMANDO A FUNÇÃO EM UMA EXPRESSAO
–COM UM VALOR DE PARAMETRO

SET SERVEROUTPUT ON;

EXECUTE dbms_output.put_line(get_sal(100));

DIFERENTES MÉTODOS PARA SE EXECUTAR UMA FUNÇÃO

–EXEMPLO 1

VARIABLE b_salary NUMBER

EXECUTE :b_salary := get_sal(100);

PRINT b_salary;

–EXEMPLO 2

SET SERVEROUTPUT ON

DECLARE
  sal employees.salary%TYPE;
BEGIN
  sal := get_sal(100);
  DBMS_OUTPUT.PUT_LINE('o SALÁRIO É: ' || sal);
END;
/

–EXEMPLO 3

EXECUTE dbms_output.put_line   (get_sal(100));

–EXEMPLO 4

SELECT  JOB_ID, GET_SAL(EMPLOYEE_ID)
FROM    EMPLOYEES;

TABELA DO DICIONÁRIO DE DADOS

Podemos utilizar a tabela USER_OBJECTS para identificar os tipos de objetos que o usuário corrente tem acesso;

SELECT   object_name,
         object_type
FROM     user_objects;

EXEMPLO DE FUNÇÃO COM COMPARAÇÕES

CREATE OR REPLACE FUNCTION check_sal
  RETURN Boolean IS
  v_dept_id   employees.department_id%TYPE;
  v_empno     employees.employee_id%TYPE;
  v_sal       employees.salary%TYPE;
  v_avg_sal   employees.salary%TYPE;
BEGIN
  v_empno := 205;
  SELECT  salary, department_id
  INTO    v_sal, v_dept_id,
  FROM    employees
  WHERE   employee_id = v_empno;

  SELECT  avg(salary)
  INTO    v_avg_sal
  FROM    employees
  WHERE   department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

MOSTRANDO POSSÍVEIS ERROS

–mostrando onde estão os possíveis erros

SHOW ERRORS FUNCTION check_sal

--SERÁ MOSTRADO POSSIVELMENTE

LINE/COL ERROR
-------- ----------------------------------
9/3      PL/SQL: SQL Statement ignored
11/3     PL/SQL: ORA-00936: missing expression

AJUSTE DA FUNÇÃO

–ajuste a seguinte parte da função

SELECT  salary,
        department_id
INTO    v_sal,
        v_dept_id

CHAMADA DA FUNÇÃO

–chamada da função

SET SERVEROUTPUT ON;
BEGIN
  IF (check_sal IS NULL) THEN
  DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido a exceção');
  ELSIF (check_sal) THEN
    DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');
  END IF;
END;
/

PASSANDO PARAMETROS PARA UMA FUNÇÃO

CREATE OR REPLACE FUNCTION check_sal
  (p_empno employees.employee_id% TYPE)
RETURN Boolean IS
  v_dept_id   employees.department_id%TYPE;
  v_sal       employees.salary%TYPE;
  v_avg_sal   employees.salary%TYPE;
BEGIN
  SELECT  salary, department_id
  INTO    v_sal, v_dept_id
  FROM    employees
  WHERE   employee_id = p_empno;
  
  SELECT  avg(salary)
  INTO    v_avg_sal
  FROM    employees
  WHERE   department_id = v_dept_id;

  IF v_sal > v_avg_sal THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;
/

CHAMADA DA FUNÇÃO COM PARAMETROS

SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Checando o empregado 205');
  
  IF (check_sal(205) IS NULL) THEN
    DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido   a exceção');
  ELSIF (check_sal(205)) THEN
    DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');   END IF;   DBMS_OUTPUT.PUT_LINE('Checando o empregado 70');      IF (check_sal(70) IS NULL) THEN     DBMS_OUTPUT.PUT_LINE('A função retornou NULL devido     a exceção');   ELSIF (check_sal(70)) THEN     DBMS_OUTPUT.PUT_LINE('Salario > média');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salario < média');
  END IF;
END;
/

Exercícios

Crie uma função, que passado 2 parâmetros do tipo number, (o valor do primeiro parâmetro deve ser menor que o segundo) retorne o valor do maior salário encontrado. Os parâmetros passados são correspondentes aos códigos dos empregados. Essa função deve tratar erro, caso o primeiro parâmetro seja maior que o segundo parâmetro. No ambiente chamador, mostre o valor encontrado;

Crie uma função, que passado um parâmetro do tipo number e outro do tipo data, que retorne a quantidade de tempo que o empregado está na empresa em anos que o valor deve ser arredondado. Os parâmetros passados são correspondentes ao códigos do empregados e a data de admissão. No ambiente chamador, deve-se chamar a função através de um comando select;

Crie uma função, que passado um parâmetro do tipo number, retorne se o funcionário tem o job_id igual a “IT_PROG” ou “PU_CLERCK”. O parâmetro passado é correspondente ao código do funcionário. No ambiente chamador, deve ser impresso se o funcionário tem ou não tem o job_id igual a “IT_PROG” ou “PU_CLERCK”;

Crie uma função, que passado um parâmetro do tipo number, retorne a quantidade de funcionário que existem em um determinado departamento. O parâmetro passado é correspondente ao departamento. No ambiente chamador, é necessário estar impresso o código do departamento e a quantidade de funcionários;

Vídeo – Parte 1

Vídeo – Parte 2

Vídeo – Parte 3

Deixe um comentário Cancelar resposta

Digite seu comentário aqui...

Preencha os seus dados abaixo ou clique em um ícone para log in:

Email (obrigatório) (Nunca tornar endereço público)

Nome (obrigatório)

Site

Você está comentando utilizando sua conta WordPress.com. ( Sair / Alterar )

Você está comentando utilizando sua conta Google. ( Sair / Alterar )

Você está comentando utilizando sua conta Twitter. ( Sair / Alterar )

Você está comentando utilizando sua conta Facebook. ( Sair / Alterar )

Cancelar

Conectando a %s

Banco de Dados II – Aula 12

MANIPULANDO EXCEÇÕES

ERRO GERADO DA CHAMADA DA PROCEDURE

MANIPULANDO EXCEÇÕES

EXCLUSÃO DE PROCEDURES

VISUALIZANDO INFORMAÇÕES DE PROCEDURES UTILIZANDO DICIONARIO DE DADOS DAS VIEWS

LOOPS (LAÇOS)

LOOP WHILE (ENQUANTO)

EXEMPLO DE LOOP BÁSICO

INSTRUÇÃO CONTINUE

DATATYPE

RECORDS e COLLECTIONS

ESTRUTURA DE UM RECORD

MANUPILAÇÃO DOS RECORDS

ATRIBUTO %ROWTYPE

EXEMPLO DE %ROWTYPE

CURSORES

CURSORES EXPLICITOS

CONTROLANDO CURSORES EXPLÍCITOS

DECLARANDO CURSORES

CURSORES COM LOOP

LOOP FOR COM CURSORES

STATUS DE CURSORES

UTILIZAÇÃO DO %ISOPEN

UTILIZAÇÃO DO %ROWCOUNT e %NOTFOUND

FUNÇÕES

DIFERENÇAS ENTRE PROCEDURES E FUNÇÕES

VISÃO GERAL DA CRIAÇÃO E EXECUÇÃO DE FUNÇÕES

CRIANDO E CHAMANDO UMA FUNÇÃO

DIFERENTES MÉTODOS PARA SE EXECUTAR UMA FUNÇÃO

TABELA DO DICIONÁRIO DE DADOS

EXEMPLO DE FUNÇÃO COM COMPARAÇÕES

MOSTRANDO POSSÍVEIS ERROS

AJUSTE DA FUNÇÃO

CHAMADA DA FUNÇÃO

PASSANDO PARAMETROS PARA UMA FUNÇÃO

CHAMADA DA FUNÇÃO COM PARAMETROS

Exercícios

Compartilhe isso:

Curtir isso:

Relacionado

Deixe um comentário Cancelar resposta