SQL em PL / SQL

Neste tutorial, vamos aprender como usar o SQL em PL / SQL. O SQL é o componente atual que cuida da busca e atualização de dados no banco de dados, ao passo que PL / SQL é o componente que processa esses dados. Além disso, neste artigo, também discutiremos como combinar o SQL no bloco PL / SQL.

 

Neste tutorial, você aprenderá –

Transações DML em PL / SQL

CURSOR Concept em PL / SQL

Atributos do cursor

FOR Loop Cursor statement

BULK COLLECT em PL / SQL

Declarações TCL em PL / SQL

Transação Autônoma

Transações DML em PL / SQL

DML significa Idioma de Manipulação de Dados . Essas declarações são usadas principalmente para realizar a atividade de manipulação. Trata-se basicamente das operações abaixo.

Inserção de dados

Atualização de dados

Eliminação de dados

Projeção de dados / busca

Em PL / SQL, podemos fazer a manipulação de dados somente usando os comandos SQL.

Inserção de dados

Em PL / SQL, podemos inserir os dados em qualquer tabela usando o comando SQL INSERT INTO. Este comando levará o nome da tabela, a coluna da tabela e os valores das colunas como a entrada e insira o valor na tabela base.

O comando INSERT também pode levar os valores diretamente de outra tabela usando a instrução ‘SELECT’ ao invés de dar os valores para cada coluna. Através da instrução “SELECT”, podemos inserir tantas linhas como a tabela base contém.

SQL em PL / SQL

Sintaxe Explicação:

A sintaxe acima mostra o comando INSERT INTO. O nome e os valores da tabela são campos obrigatórios, enquanto os nomes das colunas não são obrigatórios se as instruções do inserto tiverem valores para toda a coluna da tabela.

A palavra-chave ‘VALORES’ é obrigatória se os valores forem dados separadamente, como mostrado acima.

SQL em PL / SQL

Sintaxe Explicação:

A sintaxe acima mostra o comando INSERT INTO que leva os valores diretamente do <table_name2> usando o comando SELECT.

A palavra-chave “VALORES” não deve estar presente neste caso, pois os valores não são fornecidos separadamente.

Atualização de dados

Atualização de dados simplesmente significa uma atualização do valor de qualquer coluna na tabela. Isso pode ser feito usando a instrução ‘UPDATE’. Esta declaração leva o nome da tabela, o nome da coluna e o valor como entrada e atualiza os dados.

SQL em PL / SQL

Sintaxe Explicação:

A sintaxe acima mostra a UPDATE. A palavra-chave ‘SET’ instrui esse mecanismo PL / SQL para atualizar o valor da coluna com o valor fornecido.

A cláusula “WHERE” é opcional. Se esta cláusula não for fornecida, o valor da coluna mencionada em toda a tabela será atualizado.

Eliminação de dados

A eliminação de dados significa excluir um registro completo da tabela do banco de dados. O comando ‘DELETE’ é usado para este propósito.

SQL em PL / SQL

Sintaxe Explicação:

A sintaxe acima mostra o comando DELETE. A palavra-chave ‘FROM’ é opcional e com ou sem a cláusula ‘FROM’ o comando se comporta da mesma forma.

A cláusula “WHERE” é opcional. Se esta cláusula não for fornecida, a tabela inteira será excluída.

Projeção de dados / busca

A projeção / busca de dados significa para recuperar os dados necessários da tabela do banco de dados. Isso pode ser alcançado usando o comando ‘SELECIONAR’ com a cláusula ‘INTO’. O comando ‘SELECT’ vai buscar os valores do banco de dados e cláusula de ‘INTO’ irá atribuir esses valores para a variável local do bloco PL / SQL.

Abaixo estão os pontos que precisam ser considerados na indicação “SELECIONAR”.

A instrução “SELECT” deve retornar apenas um registro enquanto usa a cláusula ‘INTO’, uma vez que uma variável pode conter apenas um valor. Se o ‘SELECT’ instrução retorna mais de um valor de exceção ‘TOO_MANY_ROWS’ será gerado.

A instrução “SELECT” atribuirá o valor à variável na cláusula ‘INTO’, por isso precisa obter pelo menos um registro da tabela para preencher o valor. Se não obteve nenhum registro, então a exceção ‘NO_DATA_FOUND’ é gerada.

O número de colunas e seu tipo de dados na cláusula ‘SELECIONAR’ devem corresponder com o número de variáveis ​​e seus tipos de dados na cláusula ‘INTO’.

Os valores são obtidos e preenchidos na mesma ordem mencionada na declaração.

A cláusula “WHERE” é opcional que permite ter mais restrição nos registros que serão obtidos.

A instrução “SELECT” pode ser usada na condição ‘WHERE’ de outras instruções DML para definir os valores das condições.

A instrução ‘SELECT’ ao usar as instruções ‘INSERT’, ‘UPDATE’, ‘DELETE’ não deve ter a cláusula ‘INTO’, uma vez que não irá preencher nenhuma variável nesses casos.

SQL em PL / SQL

Sintaxe Explicação:

A sintaxe acima mostra o comando SELECT-INTO. A palavra-chave ‘FROM’ é obrigatória que identifica o nome da tabela a partir do qual os dados precisam ser obtidos.

A cláusula “WHERE” é opcional. Se esta cláusula não for fornecida, os dados de toda a tabela serão obtidos.

Exemplo 1 : neste exemplo, vamos ver como executar operações DML em PL / SQL. Vamos inserir os seguintes 4 registros na tabela emp.

EMP_NAME EMP_NO SALÁRIO GERENTE
BBB 1000 25000 AAA
XXX 1001 10000 BBB
AAAA 1002 10000 BBB
ZZZ 1003 7500 BBB

Então, vamos atualizar o salário de ‘XXX’ para 15000, e vamos excluir o registro do funcionário ‘ZZZ’. Finalmente, vamos projetar os detalhes do “XXX” do empregado.

SQL em PL / SQL

SQL em PL / SQL

Explicação do código:

Código linha 2-5 : Declarando a variável.

Código linha 7-14 : Inserindo os registros na tabela emp.

Código linha 15 : Cometer as transações inserir.

Código linha 17-19 : atualizando o salário do empregado ‘XXX’ para 15000

Código linha 20 : Cometer a transação de atualização.

Código linha 22 : Excluindo o registro de ‘ZZZ’

Código linha 23 : Cometer a transação de exclusão.

  • Linha de código 25-27 : Selecionando a gravação de ‘XXX’ e preenchendo na variável l_emp_name, l_emp_no, l_salary, l_manager.

 

Linha de código 28-32 : exibindo o valor de registros obtidos.

CURSOR Concept em PL / SQL

O Oracle cria uma área de memória, conhecida como área de contexto, para processar uma instrução SQL, que contém todas as informações sobre a declaração, por exemplo, número de linhas processadas, etc. O cursor não é senão um ponteiro para essa área de contexto.

O PL / SQL permite ao programador controlar a área de contexto através do cursor. Um cursor contém as linhas retornadas pela instrução SQL. O conjunto de linhas que o cursor mantém é referido como conjunto ativo. Esses cursores também podem ser nomeados para que possam ser encaminhados a partir de outro local do código.

O cursor é de dois tipos.

Cursor implícito

Cursor explícito

Cursor implícito

Sempre que ocorrem operações DML no banco de dados, é criado um cursor implícito que contém as linhas afetadas, naquela operação específica. Esses cursores não podem ser nomeados e, portanto, não podem ser controlados ou encaminhados a partir de outro local do código. Podemos referir-se apenas ao cursor mais recente através dos atributos do cursor.

Cursor explícito

Os programadores podem criar área de contexto nomeada para executar suas operações DML para obter mais controle sobre isso. O cursor explícito deve ser definido na seção de declaração do bloco PL / SQL e é criado para a instrução ‘SELECT’ que precisa ser usada no código.

Abaixo estão as etapas que envolvem o trabalho com cursores explícitos.

Declarando o cursorDeclarar o cursor significa simplesmente criar uma área de contexto nomeada para a instrução ‘SELECIONAR’ que está definida na parte da declaração. O nome dessa área de contexto é o mesmo que o nome do cursor.

Cursor de aberturaAbrir o cursor instruirá o PL / SQL a alocar a memória para este cursor. Ele irá preparar o cursor para buscar os registros.

Obtendo Dados do CursorNeste processo, a instrução ‘SELECT’ é executada e as linhas obtidas são armazenadas na memória alocada. Estes são agora chamados de conjuntos ativos. Obter dados do cursor é uma atividade de nível recorde, o que significa que podemos acessar os dados de maneira recorde por gravação.Cada instrução fetch buscará um conjunto ativo e contém as informações desse registro específico. Esta declaração é a mesma que a instrução “SELECIONAR” que obtém a gravação e atribua à variável na cláusula ‘INTO’, mas não lançará nenhuma exceção.

Fechar o cursorUma vez que todos os registros são buscados, precisamos fechar o cursor para que a memória alocada para essa área de contexto seja lançada.

SQL em PL / SQL

Sintaxe Explicação:

  • Na sintaxe acima, a parte de declaração contém a declaração do cursor e a variável de cursor na qual os dados obtidos serão atribuídos.
  • O cursor é criado para a instrução ‘SELECT’ que é dada na declaração do cursor.
  • Na parte de execução, o cursor declarado é aberto, recuperado e fechado.

Atributos do cursor

Tanto o cursor implícito como o cursor explícito possuem certos atributos que podem ser acessados. Esses atributos fornecem mais informações sobre as operações do cursor. Abaixo estão os diferentes atributos do cursor e seu uso.

Atributo do Cursor Descrição
%ENCONTRADO Retorna o resultado booleano ‘VERDADEIRO’ se a operação de busca mais recente forçou uma gravação com sucesso, caso contrário, ela retornará FALSA
%NÃO ENCONTRADO Isso funciona da maneira oposta ao% FOUND retornará ‘TRUE’ se a operação de busca mais recente não puder obter qualquer registro.
%ESTÁ ABERTO Retorna o resultado booleano ‘TRUE’ se o cursor já estiver aberto, caso contrário ele retorna ‘FALSE’
%CONTAGEM DE LINHAS Ele retorna o valor numérico. Ele fornece a contagem real de registros que foram afetados pela atividade DML.

Exemplo 1 : neste exemplo, vamos ver como declarar, abrir, buscar e fechar o cursor explícito.

Vamos projetar todos os funcionários nome da tabela emp usando um cursor. Nós também usaremos o atributo do cursor para definir o loop para buscar toda a gravação a partir do cursor.

SQL em PL / SQL

Explicação do código:

  • Código linha 2 : Declarando o cursor guru99_det para a instrução ‘SELECT emp_name FROM emp’.
  • Código linha 3 : Declaração variável lv_emp_name.
  • Código linha 5 : abrindo o cursor guru99_det.
  • Código linha 6: Configurando a instrução do loop básico para buscar todos os registros na tabela ‘emp’.
  • Código linha 7: Obtém os dados guru99_det e atribua o valor a lv_emp_name.
  • Código linha 9: Usando o atributo do cursor ‘% NOTFOUND’ para descobrir se toda a gravação no cursor é buscada. Se for obtido, ele retornará ‘VERDADEIRO’ e o controle sairá do loop, senão o controle continuará comprando os dados do cursor e imprimindo os dados.
  • Código linha 11: condição EXIT para a instrução loop.
  • Linha de código 12: Imprima o nome do funcionário buscado.
  • Linha de código 14: Usando o atributo de cursor ‘% ROWCOUNT’ para encontrar o número total de registros que foram afetados / recuperados no cursor.
  • Código linha 15: Depois de sair do loop, o cursor é fechado e a memória alocada está livre.

FOR Loop Cursor statement

A indicação “PARA LOOP” pode ser usada para trabalhar com cursores. Podemos dar o nome do cursor em vez do limite de intervalo na instrução FOR loop para que o loop funcione a partir da primeira gravação do cursor para a última gravação do cursor. A variável do cursor, a abertura do cursor, a busca e o fechamento do cursor serão realizadas de forma implícita pelo loop FOR.

SQL em PL / SQL

Sintaxe Explicação:

  • Na sintaxe acima, a parte de declaração contém a declaração do cursor.
  • O cursor é criado para a instrução ‘SELECT’ que é dada na declaração do cursor.
  • Na parte de execução, o cursor declarado é configurado no loop FOR e a variável de loop ‘I’ se comportará como variável de cursor neste caso.

Exemplo 1 : neste exemplo, vamos projetar todo o nome do empregado da tabela emp usando um cursor-FOR loop.

SQL em PL / SQL

Explicação do código:

  • Código linha 2 : Declarando o cursor guru99_det para a instrução ‘SELECT emp_name FROM emp’.
  • Linha de código 4 : Construindo o loop ‘FOR’ para o cursor com a variável de loop lv_emp_name.
  • Código linha 5: Imprimir o nome do funcionário em cada iteração do loop.
  • Código linha 8: Sair do loop

Nota: No cursor Cursor-FOR, os atributos do cursor não podem ser usados, pois a abertura, a busca eo fechamento do cursor são feitos de forma implícita pelo loop FOR.

BULK COLLECT em PL / SQL

O Oracle PL / SQL fornece a funcionalidade de buscar os registros em massa em vez de buscar um a um. Este BULK COLLECT pode ser usado na instrução ‘SELECT’ para preencher os registros em massa ou em busca do cursor em massa. Uma vez que BULK COLLECT obtém o registro em BULK, a cláusula INTO deve sempre conter uma variável de tipo de coleção. A principal vantagem de usar BULK COLLECT é aumentar o desempenho, reduzindo a interação entre o banco de dados eo mecanismo PL / SQL.

SQL em PL / SQL

Sintaxe Explicação:

  • Na sintaxe acima, BULK COLLECT é usado para coletar os dados da indicação ‘SELECT’ e ‘FETCH’.

Cláusula FORALL

O FORALL permite executar as operações DML em dados a granel. É semelhante ao da declaração FOR loop, exceto no FOR loop coisas acontecem no nível recorde, enquanto que no FORALL não existe um conceito LOOP. Em vez disso, todos os dados presentes no intervalo especificado são processados ​​ao mesmo tempo.

SQL em PL / SQL

Sintaxe Explicação:

  • Na sintaxe acima, a operação DML dada será executada para todos os dados que estão presentes entre um intervalo mais baixo e mais alto.

Cláusula de Limite

O conceito de coleta em massa carrega todos os dados na variável de coleta de destino como um volume, ou seja, todos os dados serão preenchidos na variável de coleta em uma única etapa. Mas isso não é aconselhável quando o registro total que precisa ser carregado é muito grande, porque quando o PL / SQL tenta carregar todos os dados, ele consome mais memória de sessão. Por isso, é sempre bom limitar o tamanho desta operação de coleta em massa.

No entanto, esse limite de tamanho pode ser facilmente alcançado através da introdução da condição ROWNUM na instrução ‘SELECT’, enquanto que no caso do cursor isso não é possível.

Para superar este Oracle forneceu uma cláusula ‘LIMIT’ que define o número de registros que precisam ser incluídos no volume.

SQL em PL / SQL

Sintaxe Explicação:

  • Na sintaxe acima, a instrução de busca do cursor usa a instrução BULK COLLECT juntamente com a cláusula LIMIT.

BULK COLLECT Atributos

Semelhante aos atributos do cursor BULK COLLECT tem% BULK_ROWCOUNT (n) que retorna o número de linhas afetadas na n ª declaração DML da instrução FORALL, ou seja, dará a contagem de registros afetados na declaração FORALL para cada valor da coleção variável. O termo ‘n’ indica a seqüência de valor na coleção, para a qual a contagem de linhas é necessária.

Exemplo 1 : neste exemplo, vamos projetar todo o nome do empregado da tabela emp usando BULK COLLECT e também aumentaremos o salário de todos os funcionários em 5000 usando o FORALL.

SQL em PL / SQL

Explicação do código:

  • Código linha 2 : Declarando o cursor guru99_det para a instrução ‘SELECT emp_name FROM emp’.
  • Código linha 3 : Declarando lv_emp_name_tbl como tipo de tabela do VARCHAR2 (50)
  • Código linha 4 : Declarando lv_emp_name como lv_emp_name_tbl tipo.
  • Código linha 6: abrindo o cursor.
  • Código linha 7: Preenchendo o cursor usando BULK COLLECT com o tamanho LIMIT como 5000 intl lv_emp_name variável.
  • Código da linha 8-11: Configurando FOR loop para imprimir toda a gravação na coleção lv_emp_name.
  • Código linha 12: Usando FORALL atualizando o salário de todo o empregado em 5000.
  • Linha de código 14: Comprometer a transação.

Declarações TCL em PL / SQL

O TCL significa declarações de controle de transações. Ele irá salvar as transações pendentes ou reverter a transação pendente. Essas declarações desempenham o papel vital porque, a menos que a transação seja salva, as alterações por meio de instruções DML não serão salvas no banco de dados. Abaixo estão as diferentes declarações TCL.

COMMIT Salva toda a transação pendente
ROLLBACK Descarte toda a transação pendente
SAVEPOINT Cria um ponto na transação até que o retorno pode ser feito mais tarde
ROLLBACK TO Descarte toda a transação pendente até o especificado <save point>

A transação será completa nos seguintes cenários.

  • Quando qualquer uma das declarações acima é emitida (exceto SAVEPOINT)
  • Quando as declarações DDL são emitidas. (DML são declarações de confirmação automática)
  • QUANDO as declarações DCL são emitidas. (DCL são declarações de confirmação automática)

Transação Autônoma

Em PL / SQL, todas as modificações feitas em dados serão denominadas como uma transação. Uma transação é considerada completa quando o save / descarte é aplicado a ele. Se não for salvo / descartar, a transação não será considerada como completa e as modificações feitas nos dados não serão tornadas permanentes no servidor.

Independentemente de uma série de modificações feitas durante uma sessão, PL / SQL tratará toda a modificação como uma única transação e salvar / descartar essa transação afeta todas as mudanças pendentes naquela sessão. A Transação Autônoma fornece uma funcionalidade para o desenvolvedor em que permite fazer alterações em uma transação separada e salvar / descartar essa transação específica sem afetar a transação da sessão principal.

  • Essa transação autônoma pode ser especificada no nível do subprograma.
  • Para fazer qualquer subprograma para funcionar em uma transação diferente, a palavra-chave ‘PRAGMA AUTONOMOUS_TRANSATION’ deve ser fornecida na seção declarativa desse bloco.
  • Ele instruirá esse compilador a tratar isso como a transação separada e salvar / descartar dentro deste bloco não refletirá na transação principal.
  • Emitir COMMIT ou ROLLBACK é obrigatório antes de sair desta transação autônoma para a transação principal porque, em qualquer momento, apenas uma transação pode estar ativa.
  • Então, uma vez que fizemos uma transação autônoma, precisamos salvá-la e concluir a transação, então só podemos voltar para a transação principal.

SQL em PL / SQL

Sintaxe Explicação:

  • Na sintaxe acima, o bloco foi feito como uma transação autônoma.

Exemplo 1 : neste exemplo, vamos entender como a transação autônoma está funcionando.

SQL em PL / SQL

SQL em PL / SQL

Explicação do código:

  • Código linha 2 : declarando l_salary como NUMBER.
  • Código linha 3 : declarando procedimento de bloqueio aninhado
  • Código linha 4 : Fazendo o procedimento nested_block como ‘AUTONOMOUS_TRANSACTION’.
  • Código linha 7-9: aumentando o salário para o empregado número 1002 em 15000.
  • Código linha 10: Comprometer a transação.
  • Código linha 13-16: Imprimir os detalhes salariais dos funcionários 1001 e 1002 antes das alterações.
  • Código linha 17-19: aumentando o salário para o empregado número 1001 em 5000.
  • Código linha 20: Chamando o procedimento aninhado_block;
  • Código linha 21: descartar a transação principal.
  • Código linha 22-25: Imprimindo os detalhes do salário dos empregados 1001 e 1002 após as mudanças.
  • O aumento de salário para o empregado número 1001 não é refletido porque a principal transação foi descartada. O aumento de salário para o empregado número 1002 é refletido porque esse bloco foi feito como uma transação separada e salvo no final.
  • Portanto, independentemente da gravação / descarte na transação principal, as alterações na transação autônoma foram salvas sem afetar as principais alterações de transação.

Resumo

Neste tutorial, aprendemos a combinar o SQL em PL / SQL para manipulação de dados, Cursor Concepts, Cursor-FOR loop e Bulk collect usages. Nós também discutimos as declarações da TCL e como salvar / descartar transações separadamente.

Fonte

https://www.guru99.com/sql-pl-sql.html

Anúncios

Transaction Autonomous – O Que, Quando, Onde e Por Que

Do que se trata o artigo:

Neste artigo será apresentada a utilização do pragma autonomous Transaction para escrita de códigos autônomos em rotinas PL/SQL, as quais são executadas e commitadas na base independentemente do resultado da transação master que invocou essa rotina.


Em que situação o tema é útil:

Esta funcionalidade é bastante utilizada para geração de logs de erro de transação, onde a transação master sofre rollback depois de uma falha, mas é possível gerar um log contento os dados da transação que resultaram no erro.

Um subprograma normalmente tem suas operações salvas ou não no banco de dados de acordo com o que acontece com o programa principal onde ele está inserido. Isso quer dizer que, se uma procedure chama uma função e a procedure falha, nem as alterações feitas pela procedure nem as alterações feitas pela função são salvas na base, afinal trata-se da mesma transação, a qual é atômica (indivisível, ou seja, ou ela toda é commitada ou ela toda sofre rollback). No entanto, se a função contiver o pragma autonomous_transaction, ela se comporta como uma segunda transação, que é isolada e independente, e suas alterações na base podem ser salvas ou não independentemente da transação master que a originou.

A diretiva AUTONOMOUS_TRANSACTION altera a forma com que a transação trata a um subprograma. Um pragma na verdade é uma diretiva de compilação e os subprogramas marcados com este pragma são processados em tempo de compilação e não em tempo de execução, e passam informações diretamente ao compilador.

O termo AUTONOMOUS_TRANSACTION se refere à habilidade do PL/SQL temporariamente suspender a transação corrente e iniciar uma nova transação, totalmente independente, que funciona de forma autônoma com relação à transação original.

Imagine a seguinte situação: Para fins de auditoria, criamos uma tabela de log que contém os dados referentes a todas as alterações feitas nos dados de 5 tabelas críticas do banco de dados da empresa, armazenando o IP da máquina que originou a transação, qual tabela sofreu alteração, o que foi feito, etc, e um trigger é responsável por inserir tais dados nessa tabela. Pois bem, vamos supor então que por um motivo ou outro essa transação tenha falhado. Não desejamos que os logs de auditoria também sofram rollback, pois estaremos perdendo dados preciosos de tentativas frustradas de acesso não autorizado.

Neste caso, o trigger deve disparar um subprograma autônomo, cujo sucesso da transação não dependa do resultado da transação principal que o originou.

A utilização em tratativas de logs de auditoria são comuns e não causam nenhum tipo de problema ao banco de dados, uma vez que não está lidando com tabelas de negócio, ou seja, que guardam dados essenciais para o negócio. Por este motivo a transação autônoma é segura, pois a integridade do banco de dados está resguardada.

Digo isso porque já vi muitos códigos resolvendo regras de negócio com transações autônomas, o que pode gerar um problema grande para o banco de dados. Imagine que a alteração de um valor em uma tabela deve causar alterações em outras tabelas. Se a primeira alteração falha, o ideal é que as demais alterações não ocorram… neste caso o procedimento que faria as demais alterações não poderia jamais ser autônomo!

Qualquer subprograma, como procedures, funções ou até mesmo blocos anônimos PL/SQL podem conter este pragma. No entanto, se for utilizado dentro de pacotes, o pragma deve ser declarado para as funções e procedures que fazem parte do pacote, e não para o pacote em si.

Exemplo de Utilização

Como um exemplo de utilização da transação autônoma, vamos assumir que precisamos gravar logs de erro em uma tabela do banco de dados. Precisamos fazer rollback da transação principal porque ela resultaria em um erro, mas não queremos perder o log do que aconteceu nessa transação. A tabela que conterá os logs de erro possui a seguinte estrutura:

CREATE TABLE tb_log_erros(

  codigo integer,

  msg varchar2(2000),

  data date,

  usuario varchar2(50),

  nm_mach varchar2(100),

  prog varchar2(100)

);

O procedimento que deve ser invocado para inserir o log do erro na tabela é:

CREATE OR REPLACE PROCEDURE grava_log_erros(

   log_codigo IN INTEGER,

   log_msg IN VARCHAR2) IS

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR cur_erro IS

SELECT machine, program

FROM v$session

WHERE audsid = USERENV(‘SESSIONID’);

PT = Parent Transaction;

CT = Child Autonomous Transaction;

rec cur_erro%ROWTYPE;

BEGIN

   —

   OPEN cur_erro;

   FETCH cur_erro INTO rec;

   CLOSE cur_erro;

   —

   INSERT INTO tb_log_erros values (

       log_codigo,

       log_msg,

       SYSDATE,

       USER,

       rec.machine,

       rec.program

   );

   COMMIT;

EXCEPTION

   WHEN OTHERS THEN

       ROLLBAACK;

END;

/

Para testar o código acima, podemos executar o seguinte bloco anônimo PL/SQL:

BEGIN

    INSERT INTO HR.EMPLOYEES (first_name) VALUES (‘Maria’);

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

         grava_log_erros(SQLCODE,SQLERRM);

    ROLLBACK;

    RAISE;

END;

Ao executar o código acima, basta verificar nas tabelas EMPLOYEES e TB_LOG_ERROS as linhas inseridas, como segue:

SQL> select * from employees where first_name = ‘Maria’;

no rows selected.

SQL> select codigo, msg from tb_log_erros;

CODIGOMSG

——————————————————————————————————–

-1400ORA-01400: cannot insert NULL into (“HR”.”EMPLOYEES”.”EMPLOYEE_ID”)

Referências

BURLESON CONSULTING. PL/SQL Autonomous Transaction Tips. Burleson Consulting, 2015. Disponivel em: http://www.dba-oracle.com/t_autonomous_transaction.htm

ORACLE HELP CENTER. Autonomous_transaction Pragma. Database PL/SQL User’s Guide and Reference, 2017. Disponivel em: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

Sistemas de Banco de Dados 6ª Edição – Elmasri

 

sistemas-de-banco-de-dados-6

Download Sistemas de Banco de Dados 6ª Edição Elmasris

Referência acadêmica tanto teórica como prática, Sistemas de banco de dados apresenta os aspectos mais importantes não apenas dos sistemas, mas também das aplicações de banco de dados, além de diversas tecnologias relacionadas ao assunto.

Atualizada, a obra aborda:
– Conceitos fundamentais para projetar e usar os sistemas de banco de dados.
– Fundamentos de modelagem e de projeto de banco de dados.
– Linguagens e modelos fornecidos pelos sistemas de gerenciamento de banco de dados.
– Técnicas de implementação do sistema de banco de dados, com exemplos práticos.
Indicado para os cursos de ciências da computação, desenvolvimento de sistemas, sistemas de informação e engenharia da computação, este livro é também bibliografia básica para cursos de análise de redes, análise de sistemas e processamento de dados.

Sumário
Parte 1. Introdução aos bancos de dados.
1. Bancos de dados e usuários de banco de dados.
2. Conceitos e arquitetura do sistema de banco de dados.

Parte 2. Modelo de dados relacional e SQL.
3. O modelo de dados relacional e as restrições em bancos de dados relacionais.
4. SQL básica.
5. Mais SQL: Consultas complexas, triggers, views e modificação de esquema.
6. Álgebra e cálculo relacional.

Parte 3. Modelagem conceitual e projeto de banco de dados.
7. Modelagem de dados usando o modelo de Entidade-Relacionamento (ER).
8. O modelo de entidade-relacionamento estendido (EER).
9. Projeto de banco de dados relacional por mapeamento de ER e EER para relacional.
10. Metodologia prática de projeto de banco de dados e uso de diagramas UML.

Parte 4. Objeto, objeto-relacional e XML: conceitos, modelos, linguagens e padrões.
11. Bancos de dados de objeto e objeto-relacional.
12. XML: Extensible Markup Language.

Parte 5. Técnicas de programação de banco de dados.
13. Introdução às técnicas de programação SQL.
14. Programação de banco de dados Web usando PHP.

Parte 6. Teoria e normalização de projeto de banco de dados.
15. Fundamentos de dependências funcionais e normalização para bancos de dados relacionais.
16. Algoritmos de projeto de banco de dados relacional e demais dependências.

Parte 7. Estruturas de arquivo, indexação e hashing.
17. Armazenamento de disco, estruturas de arquivo básicas e hashing.
18. Estruturas de indexação para arquivos.

Parte 8. Processamento de consulta, otimização e ajuste de banco de dados; 19. Algoritmos para processamento e otimização de consulta.
20. Projeto físico e ajuste de banco de dados.

Parte 9. Processamento de transações, controle de concorrência e recuperação; 21. Introdução aos conceitos e teoria de processamento de transações.
22. Técnicas de controle de concorrência.
23. Técnicas de recuperação de banco de dados.

Parte 10. Tópicos adicionais de banco de dados: segurança e distribuição
24.Segurança de banco de dados
25. Bancos de dados distribuídos.

Parte 11. Modelos, sistemas e aplicações de bancos de dados avançados.
26. Modelos de dados avançados para aplicações avançadas.
27. Introdução à recuperação de informações e busca na Web.
28. Conceitos de data mining.
29. Visão geral de data warehousing e OLAP; Apêndices; Bibliografia; Índice remissivo.

Estilo: Curso
Tamanho: 131 mb
Ano de Lançamento: 2011
Idioma: Português – BR
Formato: Rar / Pdf
Hospedagem: Depositfiles / Freakshare / Bitshare

Depositfiles Download
Freakshare Download

Freakshare Bitshare

 

Fonte: http://www.sempredownloadfull.net/sistemas-de-banco-de-dados-6-edico-elmasri.html

Tipos de índice – PostgreSQL

O PostgreSQL disponibiliza vários tipos de índice: B-tree (árvore B), R-tree (árvore R), hash [1] e GiST. Cada tipo de índice utiliza um algoritmo diferente, mais apropriado para tipos diferentes de consulta. Por padrão, o comando CREATE INDEX cria um índice B-tree, adequado para a maioria das situações comuns.

Os índices B-tree podem tratar consultas de igualdade e de faixa, em dados que podem ser classificados em alguma ordem. Em particular, o planejador de comandos do PostgreSQL leva em consideração utilizar um índice B-tree sempre que uma coluna indexada está envolvida em uma comparação utilizando um dos seguintes operadores:

<
<=
=
>=
>

As construções equivalentes a combinações destes operadores, tais como BETWEEN e IN, também podem ser implementadas com procura de índice B-tree (Mas deve ser observado que IS NULL não é equivalente a = e não é indexável).

O otimizador também pode utilizar um índice B-tree nos comandos envolvendo os operadores de correspondência com padrão LIKE, ILIKE, ~ e ~*, se o padrão estiver ancorado ao início da cadeia de caracteres como, por exemplo, em col LIKE ‘foo%’ ou col ~ ‘^foo’, mas não em col LIKE ‘%bar’. Entretanto, se o servidor não utilizar o idioma C, será necessário criar um índice com uma classe de operadores especial, para dar suporte a indexação de consultas com correspondência com padrão. Consulte a Seção 11.6 adiante.

A consulta abaixo mostra o idioma. [2]

=> \pset title Idioma
=> SELECT name, setting FROM pg_settings WHERE name LIKE 'lc%';

            Idioma
    name     |    setting
-------------+----------------
 lc_collate  | pt_BR.iso88591
 lc_ctype    | pt_BR.iso88591
 lc_messages | pt_BR.iso88591
 lc_monetary | pt_BR.iso88591
 lc_numeric  | pt_BR.iso88591
 lc_time     | pt_BR.iso88591
(6 linhas)

Os índices R-tree são adequados para consultas a dados espaciais. Para criar um índice R-tree deve ser utilizado um comando da forma:

CREATE INDEX nome ON tabela USING RTREE (coluna);

O planejador de comandos do PostgreSQL considera utilizar um índice R-tree sempre que a coluna indexada está envolvida em uma comparação utilizando um dos seguintes operadores:

<<
&<
&>
>>
@
~=
&&

(Consulte a Seção 9.10 para conhecer o significado destes operadores).

Os índices hash podem tratar apenas comparações de igualdade simples. O planejador de comandos do PostgreSQL considera utilizar um índice hash sempre que a coluna indexada está envolvida em uma comparação utilizando o operador =. O seguinte comando é utilizado para criar um índice hash:

CREATE INDEX nome ON tabela USING HASH (coluna);

Nota: Os testes mostraram que os índices hash do PostgreSQL não têm desempenho melhor do que os índices B-tree, e que o tamanho e o tempo de construção dos índices hash são muito piores. Por estas razões, desencoraja-se a utilização dos índices hash.

Os índices GiST não são um único tipo de índice, mas em vez disto uma infraestrutura dentro da qual podem sem implementadas muitas estratégias de indexação diferentes. Assim sendo, os operadores em particular com os quais o índice GiST pode ser utilizado variam dependendo da estratégia de indexação (a classe de operadores). Para obter mais informações consulte a Capítulo 49.

O método de índice B-tree é uma implementação das árvores B de alta-simultaneidade de Lehman-Yao. O método de índice R-tree implementa árvores R padrão utilizando o algoritmo de partição quadrática de Guttman. O método de índice hash é uma uma implementação do hashing linear de Litwin. São mencionados os algoritmos utilizados somente para indicar que todos estes métodos de índice são inteiramente dinâmicos, não necessitando de otimização periódica (como é o caso, por exemplo, dos métodos de acesso hash estáticos).

Notas

[1] hashing — valor de identificação produzido através da execução de uma operação numérica, denominada função de hashing, em um item de dado. O valor identifica de forma exclusiva o item de dado, mas exige um espaço de armazenamento bem menor. Por isso, o computador pode localizar mais rapidamente os valores de hashing que os itens de dado, que são mais extensos. Uma tabela de hashing associa cada valor a um item de dado exclusivo. Webster’s New World Dicionário de Informática, Brian Pfaffenberger, Editora Campus, 1999. (N. do T.)
[2] Exemplo escrito pelo tradutor, não fazendo parte do manual original.

Fonte: http://pgdocptbr.sourceforge.net/pg80/indexes-types.html

Restaurando backup com pg_restore

Restaurando backup com pg_restore

pg_restore —  restaura um banco de dados do PostgreSQL a partir de um arquivo gerado pelo pg_dump

Synopsis

pg_restore [ -a ] [ -c ] [ -C ] [ -d nome_bd ] [ -f arquivo_de_saída ] [ -F formato ] [ -i índice ] [ -l ] [ -L arquivo_da_listagem ] [ -N | -o | -r ] [ -O ] [ -P nome_da_função ] [ -R ] [ -s ] [ -S ] [ -t tabela ] [ -T gatilho ] [ -v ] [ -x ] [ -X palavra_chave] [ -h hospedeiro ] [ -p porta ] [ -U nome_do_usuário ] [ -W ] [ arquivo_de_exportação ]

Descrição:

O pg_restore é um utilitário para restaurar um banco de dados do PostgreSQL a partir de um arquivo gerado pelo pg_dump em um dos formatos não-texto-puro. São executados os comandos necessários para criar novamente todos os tipos, funções, tabelas, índices, agregações e operadores definidos pelo usuário, assim como os dados das tabelas.

Os arquivos de exportação contêm informações para o pg_restore reconstruir o banco de dados, mas também permitem ao pg_restore selecionar o que deve ser restaurado, ou mesmo reordenar a restauraração dos itens. Os arquivos de exportação são projetados para serem portáveis entre arquiteturas.

O pg_restore pode operar de dois modos: Se um nome de banco de dados for especificado, o arquivo de exportação é restaurado diretamente no banco de dados. Senão, um script contendo os comandos SQL necessários para reconstruir o banco de dados é criado (e escrito em um arquivo ou na saída padrão), semelhante aos scripts criados pelo pg_dump no formato texto-puro. Algumas das opções que controlam a criação do script são, portanto, análogas às opções do pg_dump.

Obviamente, o pg_restore não pode restaurar informações que não estejam presentes no arquivo de exportação; por exemplo, se o arquivo de exportação foi gerado usando a opção “exportar dados como INSERT”, o pg_restore não poderá importar os dados usando o comando COPY.
Opções

O pg_restore aceita os seguintes argumentos de linha de comando (As formas longas das opções estão disponíveis em algumas plataformas apenas).

nome_do_arquivo_exportado
Especifica a localização do arquivo de exportação a ser restaurado. Se não for especificado, a entrada padrão é usada.

-a
–data-only

Importa somente os dados, não o esquema (definições dos dados).

-c
–clean

Exclui (drop) os objetos do banco de dados antes de criá-los..

-C
–create

Cria o banco de dados antes de restaurá-lo (Quando esta opção está presente, o banco de dados designado por -d é usado apenas para executar o comando CREATE DATABASE inicial. Todos os dados são restaurados no banco de dados cujo nome aparece no arquivo de exportação).

-d nome_bd
–dbname=nome_bd

Conecta ao nome_bd e restaura diretamente no banco de dados. Os objetos grandes somente podem ser restaurados usando uma conexão direta ao banco de dados.

-f arquivo_de_saída
–file=arquivo_de_saída

Especifica o nome do arquivo contendo o script gerado, ou a listagem quando for utilizado com a opção -l. Por padrão a saída padrão.

-F formato
–format=formato

Especifica o formato do arquivo de exportação. Não é necessário especificar o formato, porque o pg_restore reconhece o formato automaticamente. Se for especificado, poderá ser um dos seguintes:

t

O arquivo de exportação está no formato tar. Este formato de arquivo de exportação permite reordenar e/ou excluir elementos do esquema durante a importação. Também permite limitar quais dados são recarregados durante a importação.

c

O arquivo de exportação está no formato personalizado do pg_dump. Este é o formato mais flexível porque permite a reordenação da importação dos dados e dos elementos do esquema. Este formato também é comprimido por padrão.

-i índice
–index=índice

Restaura a definição do índice para o índice especificado apenas.

-l
–list

Lista o conteúdo do arquivo de exportação. A saída deste comando pode ser usada com a opção -L para restringir e reordenar os itens que são restaurados.

-L arquivo_da_listagem
–use-list=arquivo_da_listagem

Restaura apenas os elementos presentes no arquivo_da_listagem, e na ordem em que aparecem neste arquivo. As linhas podem ser movidas e, também, podem virar comentário colocando-se um ; no seu início.

-N
–orig-order

Restaura os itens na ordem original de exportação. Por padrão, o pg_dump irá exportar os itens em uma ordem conveniente para o pg_dump, e depois salvar o arquivo de exportação em uma ordem de OID modificada. Esta opção substitui a da ordem de OID.

-o
–oid-order

Restaura os itens na ordem de OID. Por padrão o pg_dump irá exportar exporta os itens em uma ordem conveniente para o pg_dump, e depois salvar o arquivo de exportação em uma ordem de OID modificada. Esta opção impõe a estrita ordem de OID.

-O
–no-owner

Impede qualquer tentativa de restaurar o dono original do objeto. O dono dos objetos será o usuário conectado ao banco de dados.

-P nome_da_função
–function=nome_da_função

Especifica o procedimento ou a função a ser restaurada.

-r
–rearrange

Restaura os itens na ordem modificada de OID. Por padrão, o pg_dump irá exportar os itens em uma ordem conveniente para o pg_dump, e depois salvar o arquivo de exportação em uma ordem de OID modificada. A maior parte dos objetos é restaurada na ordem de OID, mas alguns elementos (por exemplo, regras e índices) são restaurados no fim do processo sem respeitar os OIDs. Esta é a opção padrão.

-R
–no-reconnect

Durante a restauração do arquivo de exportação, o pg_restore usualmente necessita reconectar ao banco de dados vária vezes com nomes de usuário diferentes, para definir o dono correto dos objetos criados. Se isto não for desejável (por exemplo, se a intervenção manual for necessária para cada reconexão), esta opção proíbe o pg_restore requisitar reconexões (uma requisição de conexão em modo texto-puro, não conectado ao banco de dados, é feita emitindo o comando \connect do psql). Entretanto, esta opção é um instrumento bastante rudimentar, porque faz o pg_restore perder a informação sobre o dono, a menos que seja usada a opção -X use-set-session-authorization.

-s
–schema-only

Restaura somente o esquema (definições dos dados), sem os dados. Os valores das seqüências são substituídos.

-S nome_do_usuário
–superuser=nome_do_usuário

Especifica o nome do superusuário a ser usado para desativar os gatilhos e/ou definir o dono dos elementos do esquema. Por padrão, o pg_restore usa o nome do usuário corrente se este for um superusuário.

-t tabela
–table=tabela

Restaurar o esquema/dados da tabela apenas.

-T gatilho
–trigger=gatilho

Restaurar a definição do gatilho apenas.

-v
–verbose

Especifica o modo verboso.

-x
–no-privileges
–no-acl

Proíbe a restauração dos privilégios de acesso (comandos GRANT/REVOKE).

-X use-set-session-authorization
–use-set-session-authorization

Normalmente, se ao restaurar um arquivo de exportação for necessário trocar o usuário corrente do banco de dados (por exemplo, para definir o dono correto do objeto), uma nova conexão ao banco de dados deve ser aberta, o que poderá requerer intervenção manual (por exemplo, senhas). Se for usada a opção -X use-set-session-authorization, então o pg_restore vai usar o comando SET SESSION AUTHORIZATION. Embora produza o mesmo efeito, requer que o usuário que for fazer a importação do banco de dados a partir do arquivo de exportação gerado seja um superusuário. Esta opção substitui a opção -R.

O pg_restore também aceita os seguintes argumentos de linha de comando para os parâmetros de conexão:

-h hospedeiro
–host=hospedeiro

Especifica o nome da máquina onde o servidor está executando. Se o nome iniciar por uma barra (/), é considerado como sendo o diretório do soquete do domínio Unix.

-p porta
–port=porta

Especifica a porta Internet TCP/IP, ou o soquete do domínio local Unix, onde o servidor está aguardando as conexões. O padrão para o número da porta é 5432, ou o valor da variável de ambiente PGPORT (se estiver definida).

-U nome_do_usuário

Nome do usuário para se conectar.

-W

Força a solicitação da senha. Deve acontecer automaticamente se o servidor requerer autenticação por senha.


Exemplos:

$ pg_restore -d bd_novo bd.tar

http://www.postgresql.org/docs/9.2/static/app-pgrestore.html