Oracle – PL/SQL

Stored Procedures

Grupo de comandos SQL e PL/SQL que executam uma determinada tarefa. Uma stored procedure precisa ser chamada a partir de um programa ou ser executada manualmente pelo usuário. Uma stored procedure é totalmente portátil dentro das plataformas nas quais o Oracle é executado.

Uma stored procedure pode ser executada através das seguintes formas:
1- Através de uma ferramenta Oracle, como o SQL*PLUS;
2- Através de uma chamada explícita no código de uma aplicação de banco de dados, como o Oracle Forms;
3- Através de uma chamada explícita a partir de uma outra procedure ou trigger.

Uma stored procedure possui duas partes:
- Seção de especificação;
- Corpo da procedure.

Exemplo:

CREATE OR REPLACE PROCEDURE aumenta_sal (p_cod_fun number) IS
BEGIN
   UPDATE func SET vl_sal = vl_sal * 1.10 WHERE cod_fun = p_cod_fun;
   commit;
END aumenta_sal;

Cursor

Quando o Oracle processa um comando SQL, ele abre uma área de memória chamada Private SQL Area, que armazena informações responsáveis pela execução do comando. Um identificador para essa área – Chamado Cursor – é criado. No SQL*Plus para selecionar registros do banco de dados, basta um comando SELECT para que as linhas das tabelas ou visualizações que satisfaçam os critérios especificados sejam exibidas na tela.

Quando você usa o PL/SQL, um comando SELECT que retorna mais de uma linha, o Oracle exibe uma mensagem de erro que invoca a exceção TOO_MANY_ROWS. Para resolver esse problema, o Oracle usa um mecanismo chamado CURSOR.

Tipos de Cursor

Implícitos: O Oracle declara implicitamente um cursor para todos os comandos DDL e DML que retornam apenas uma linha.

Explícito: Para consultas que retornam múltiplas linhas e que são criados pelos usuários.

O conjunto de linhas retornadas por um cursor se chama result set (Conjunto de resultado). A linha que está sendo processada em um determinado momento é chamada current row(linha atual). Um cursor identifica a linha atual dentro do result set e permite o processamento individual de cada uma delas.

O Oracle usa quatro comandos para definir, abrir, acessar linhas e fechar um cursor. São eles: DECLARE, OPEN, FETCH E CLOSE. Um cursor pode ser visto como um arquivo temporário que armazena e controla as linhas retornadas por um comando SELECT. O SQL*PLUS gera automaticamente cursores para as consultas executadas. Já o PL/SQL, é necessário que o usuário crie cursores explícitos.

Criando um cursor

A criação de um cursor envolve quatro etapas:
1- Declaração do cursor. Nesta etapa, é criado um nome para o cursor e atribuído um comando SELECT a ele.
2- Abertura do cursor. Nessa etapa, a consulta é executada e é determinado o número de linhas que será retornado.
3- FETCHING. Na terceira etapa, as linhas encontradas são enviadas para o programa PL/SQL.

4- Fechamento do cursor. A última etapa libera os recursos do Oracle alocados para o cursor.

Exemplo prático:
create or replace procedure niva is
   c_nome_fun func.nome_fun%type;
   c_cod_fun func.cod_fun%type;
   c_vl_sal func.vl_sal%type;

   cursor teste1 is
      select nome_fun, cod_fun, vl_sal from func where vl_sal between 500 and 900 order by vl_sal;
      begin
         dbms_output.enable;
         open teste1;
         loop

            fetch teste1 into c_nome_fun,c_cod_fun,c_vl_sal;

            exit when teste1%notfound;
            dbms_output.put_line (c_nome_fun || '-' || to_char(c_cod_fun) || '-' || to_char(c_vl_sal));
         end loop;
   close teste1;
end;

Triggers

São rotinas ou procedures que são utilizados quando um comando INSERT, UPDATE ou DELETE é executado em uma tabela ou até mesmo numa visão. Um trigger é executado automaticamente sem a interferência do usuário. A principal aplicação de um trigger é a criação de consistências e restrições de acesso ao banco de dados, assim como rotinas de segurança.

Algumas Aplicações:
Criar o conteúdo de uma coluna derivada de outras colunas.
Criar mecanismos de validação que envolvam pesquisas em múltiplas tabelas.
Criar LOGS para registrar a utilização de uma tabela.
Atualizar outras tabelas em função de inclusão ou alteração da tabela atual.

Um trigger pode executar comandos contidos em seu bloco ou acionar as stored procedures para executar determinadas tarefas. Ele está associado a uma tabela, e em alguns casos, a uma visão. Quando uma visão é utilizada, os triggers da tabela base são normalmente acionados. Se uma tarefa puder ser feita tanto com triggers como com constraints, use a constraint como primeira opção.

Restrição de uso
Uma trigger não pode executar comandos COMMIT, ROLLBACK ou SAVEPOINT, nem tampouco chamar procedures ou funções que executam essas tarefas.
O comando SELECT pode ser usado apenas com a cláusula INTO.
Um trigger do tipo row-level não pode ler ou modificar o conteúdo de uma tabela em mutação.

Uma tabela mutante é aquela na qual seu conteúdo está sendo alterado por um comando INSERT, DELETE ou UPDATE e o comando não foi concluído, ou seja, ainda não foram gravados com COMMIT. O tamanho do trigger, ou seja, o texto dos comandos, não pode exceder 32 Kb. 

 

Tipos de trigger
1- Triggers de tabela aplicados aos comandos do tipo DML.
2- INSTEAD-OFF aplicados à visões.
3- SYSTEM EVENT aplicados no banco de dados nas operações de startup, shutdown e erro.
4- USER EVENT aplicado a um usuário ou objeto de esquema.
5- BEFORE e AFTER triggers.

Componentes de um trigger- Comando SQL que aciona o trigger: o disparo do trigger pode ser ocasionado pelo comando SQL ou por um evento do usuário.
- Limitador de Ação do Trigger: representado pela cláusula WHEN, especifica qual condição deve ser verdadeira para que o trigger seja disparado.

- Ação Executada pelo Trigger: é o bloco PL/SQL, Java ou rotina C que é executado pelo trigger.

Trigger de Tabela
Existem dois tipos distintos de triggers que podem ser usados em uma tabela. Um é executado apenas uma vez quando ocorre o evento de disparo e o outro é para cada linha da tabela processada pelo comando SQL.

- Statement-level Trigger: esse trigger é disparado apenas uma vez.
Por exemplo: Se um comando UPDATE atualizar 15 linhas, os comandos contidos no trigger serão executados uma única vez, e não em cada linha processada.
- Row-Level Trigger: esse trigger tem os seus comandos executados para todas as linhas que sejam afetadas pelo comando que gerou o acionamento do trigger.

Trigger do tipo INSTEAD-OFF
Oferecem uma maneira clara de alterar visões que não podem ser alteradas diretamente através de instruções SQL DML(INSERT, DELETE e UPDATE). 

Trigger de Eventos de Sistema

São disparados durante o startup ou shutdown do banco de dados e também por mensagens de erro.

Podem ser:
Startup – Disparados quando o banco de dados abre uma instância.

Shutdown – Disparados imediatamente antes do banco de dados iniciar o processo de shutdown.
Servererror – Disparados quando um erro específico ou um erro qualquer ocorre. 

Trigger de Eventos de Usuário

Os triggers de evento de usuário ocorrem quando o usuário faz seu logon, logoff ou executa comandos DML ou DDL.
LOGON – Dispara após a conexão do usuário.
LOGOFF – Dispara quando o usuário inicia sua desconexão.

Referências a Colunas
Dentro de um trigger do tipo row-level é possível acessar o valor de um campo de uma linha. Dependendo da operação que está sendo executada, é necessário preceder o nome da coluna com o sufixo :new ou :old, pois em um determinado instante pode-se obter tanto o valor antigo como o novo valor do campo.

Para um comando INSERT, os valores dos campos que  serão gravados devem ser precedidos pelo sufixo new.

Para um comando DELETE, os valores dos campos da linha que  está  processada devem ser precedidos pelo sufixo old.
Para um comando UPDATE, o valor original que está gravado é acessado com sufixo old. Os novos valores que serão gravados devem ser precedidos do sufixo new.
Os valores old e new estão disponíveis nos triggers do tipo BEFORE e AFTER.

Exemplos:

CREATE OR REPLACE TRIGGER t_pedidos BEFORE INSERT ON pedidos
Declare t_val char(30);
BEGIN
   t_val:= 'Parabéns Pelo Pedido';
   dbms_output.enable;
   dbms_output.put_line(t_val);
END;

CREATE OR REPLACE TRIGGER t_reduz_sal BEFORE UPDATE OF vl_sal ON func FOR EACH ROW when (new.vl_sal < old.vl_sal * 0.75)
BEGIN
   dbms_output.put_line('id = ' || :old.cod_fun);
   dbms_output.put_line('Old vl_sal = ' || :old.vl_sal);
   dbms_output.put_line('New vl_sal = ' || :new.vl_sal);
   dbms_output.put_line('A redução salarial é maior 25%');
   INSERT INTO auditoria (cod_fun, vl_sal_a, vl_sal_b) VALUES (:old.cod_fun, :old.vl_sal, :new.vl_sal);
END;

Packages
Um package é uma coleção  de objetos de banco de dados, como stored procedures, funções, variáveis, constantes e cursores. Ele contém subprogramas que podem ser chamados a partir de um trigger, uma procedure ou uma função. É um aliado do desenvolvedor, pois permite organizar melhor os componentes de um sistema em módulos. Além disso, a administração de privilégios é facilitada. Um package também melhora o desempenho da máquina, já que ele transfere para a memória diversos objetos de uma só vez.

Um package possui duas seções:
Especificação – Funciona como uma espécie de sumário do conteúdo do corpo do package. Nessa seção, são declarados os nomes de funções e stored procedures juntamente com os nomes de variáveis e constantes, incluindo  nesse último sua inicialização. A seção de especificação é criada com o comando CREATE PACKAGE.
Corpo – Nesta seção são declarados os objetos que farão parte do package e é criada com o comando CREATE PACKAGE BODY
Exemplo:

create package pk_teste is
   procedure aumenta_sal (p_cod_fun number);
   function f_func(f_vl_sal func.vl_sal%type) return number;

end;

create or replace package body pk_teste is

   PROCEDURE aumenta_sal (p_cod_fun number) IS
   BEGIN
      UPDATE func SET vl_sal = vl_sal * 1.10 WHERE cod_fun = p_cod_fun;
      commit;
   END aumenta_sal;

   function f_func (f_vl_sal func.vl_sal%type) return number is
      tot_ped number;
   begin
      select count(*) into tot_ped from func where vl_sal = f_vl_sal;
      return tot_ped; 

   end; 

end;