Entendendo e Utilizando os atributos SQL%BULK_ROWCOUNT e SQL%BULK_EXCEPTIONS
Por Tércio Costa,
Postado em Fevereiro 2016
Revisado por Marcelo Pivovar - Solution Architect
Trabalhar com cursores é algo extremamente comum em blocos PL/SQL entre os desenvolvedores. E todos conhecemos muito bem alguns atributos dos cursores, explícitos como implícitos, como o %ROWCOUNT que nos retorna a quantidade de linhas retornada por um SELECT ou a quantidade de linhas modificadas por outra operação DML.
E como todo bom desenvolvedor, utilizamos operações em BULK para melhorar a performance das instruções SQL e usar o mínimo possível do famoso Context Switch. Mas ao utilizar o %ROWCOUNT em operações com bulk o resultado retornado talvez não seja o esperando, pois ele irá retornar a quantidade total de linhas que foram retornadas ou modificadas e não a quantidade por cada uma das operações realizadas. Para isto, as operações em bulk possui alguns atributos a mais, como o %BULK_ROWCOUNT que exibe a quantidade por cada uma das operações realizadas em um bulk. Vejamos como fazer uso desse atributo. Para este exemplo, utilizei a tabela employees do esquema HR, veja na Listagem 1.
Listagem 1: Uso do %BULK_ROWCOUNT.
01 DECLARE
02 TYPE salarios IS TABLE OF NUMBER;
03 lv_sal SALARIOS := salarios(2100, 2200, 2500, 2600);
04 BEGIN
05 FORALL i IN lv_sal.FIRST..lv_sal.LAST
06 UPDATE employees
07 SET salary = salary*2
08 WHERE salary = lv_sal(i);
09
10 dbms_output.put_line('Quantidade total de linhas modificadas: '||SQL%ROWCOUNT);
11 FOR i IN lv_sal.FIRST..lv_sal.LAST LOOP
12 dbms_output.put('Quantidade de linhas afetadas com salário de ');
13 dbms_output.put(lv_sal(i));
14 dbms_output.put_line(' foram: '||SQL%BULK_ROWCOUNT(i));
15 END LOOP;
16 END;
Nas linhas de 5 à 8 foi feito um FORALL, uma operação em BULK, em que foi atualizado a coluna salary com base nas linhas que possuíam um salário igual à algum dos 4 elementos da variável lv_sal. Foi feito então 4 updates. Se utilizarmos o %ROWCOUNT, a quantidade retornada será a total modificado nas 4 instruções feitas no FORALL, isto foi realizado na linha 10. A partir da linha 11 foi feito uso do atributo %BULK_ROWCOUNT, que armazena a quantidade de linhas retornadas ou modificadas em uma operação em BULK. Foi utilizado um FOR com os mesmos índices do FORALL, lv_sal.FIRST e lv_sal.LAST, para imprimir cada um dos resultados conforme mostra a Listagem 2.
Listagem 2. Resultado obtido com o %BULK_ROWCOUNT.
Quantidade total de linhas modificadas: 13
Quantidade de linhas afetadas com salário de 2100 foram: 1
Quantidade de linhas afetadas com salário de 2200 foram: 2
Quantidade de linhas afetadas com salário de 2500 foram: 6
Quantidade de linhas afetadas com salário de 2600 foram: 4
Com o FORALL, podemos realizar operações em BULK utilizando instruções DML, mas infelizmente se uma dessas instruções resultar em uma exception, tudo que foi feito pela operação em bulk sofrerá um rollback caso não tenha um tratamento de exceções, ou seja, o bloco exception. Caso exista um tratamento para a exceção, tudo que foi feito antes será mantido, mas nada depois da exceção será executado. Existe um outro atributo de operações em bulk que pode contornar essa situação, o %BULK_EXCEPTIONS, que armazena informações sobre as exceções para consulta e permitindo assim que a operação em bulk continue mesmo após um erro. Para isto esse atributo conta com 3 campos. o COUNT que contem a quantidade de exceções. O SQL_CODE que é o código do erro, mas positivo, então deverá ser multiplicado por -1 para associar a um erro existente no Oracle e assim, utilizar com a função SQLERRM que retorna a mensagem de erro associado ao código. E por último o ERROR_INDEX, que é o index em que o erro foi gerado.
Para demonstrar um exemplo simples, crie a seguinte tabela, com uma restrição UNIQUE conforme a Listagem 3.
Listagem 3. CREATE TABLE
CREATE TABLE bulk_ex(
numero NUMBER UNIQUE
);
Agora vamos testar algumas inserções nessa tabela violando a constraint UNIQUE. Para isto, repetimos dois números na variável do tipo table na linha 3, os números 3 e 6. Ao tentar inserir tudo no FORALL da linha 7 e 8 será gerado um erro para cada número repetido. Para que a operação não pare e continue mesmo com um erro, devemos colocar no final do FORALL a seguinte instrução: SAVE EXCEPTIONS conforme a Listagem 4.
Listagem 4. Utilizando o SQL%BULK_EXCEPTIONS
01 DECLARE
02 TYPE tabela IS TABLE OF NUMBER;
03 lv_numeros TABELA := tabela(1,2,3,3,5,6,6);
04 lv_exception EXCEPTION;
05 PRAGMA EXCEPTION_INIT(lv_exception, -24381);
06 BEGIN
07 FORALL i IN lv_numeros.FIRST..lv_numeros.LAST SAVE EXCEPTIONS
08 INSERT INTO bulk_ex VALUES(lv_numeros(i));
09 EXCEPTION
10 WHEN lv_exception THEN
11 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
12 dbms_output.put('Erro número '||i);
13 dbms_output.put(' no index: '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
14 dbms_output.put_line(' com a seguinte mensagem: '||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
16 END LOOP;
17 END;
No tratamento da exceção, utilizamos todos os 3 atributos do BULK_EXCEPTIONS. O COUNT foi utilizado no index do FOR para imprimir todos os erros. O ERROR_INDEX para exibir em qual index ocorreu o erro e por último, o ERROR_CODE. Tornamos ele negativo e utilizamos a função SQLERRM para exibir informações sobre o erro ocorrido. Tudo isto pode ser visto no retorno gerado na Listagem 5.
Listagem 5:
Erro número 1 No index: 4 Com a seguinte mensagem: ORA-00001: restrição exclusiva (.) violada
Erro número 2 No index: 7 Com a seguinte mensagem: ORA-00001: restrição exclusiva (.) violada
Apesar de ocorrer dois erros, tudo fora os erros, até mesmo após o erro, foi adicionado na tabela. A tabela no final desse bloco em PL/SQL ficou com 5 linhas com os seguintes números: 1, 2, 3, 5 e 6.
Tércio Costa Formado em Ciências da Computação pela UFPB com experiência em Servidores Windows Server e Linux e banco de dados Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado Oracle Certified SQL Expert, mantendo o blog https://oraclepress.wordpress.com/ reconhecido pela OTN e articulista no portal http://www.profissionaloracle.com.br/gpo
Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.