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
Anúncios
