Banco de Dados II – Aula 12

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

  1. 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;
  1. 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;
  1. 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”;
  1. 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

 

 

 

 

Anúncios

Deixe um comentário

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

Logotipo do WordPress.com

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

Foto do Google+

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

Imagem do Twitter

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

Foto do Facebook

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

Conectando a %s

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect

%d blogueiros gostam disto: