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

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s