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;
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
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;
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”;
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);
- 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
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
CONTROLANDO CURSORES EXPLÍCITOS
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
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
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