Introdução ao PL/SQL:

A História do PL/SQL

PL/SQL significa "Procedural Language extensions to SQL". Ele é muito similar à linguagem SQL, mas acrescenta construções de programação similares a outras linguagens, resultando em uma linguagem estrutural que é mais poderosa do que SQL.

É a linguagem básica para criar programas complexos e poderosos, não só no banco de dados, mas também em diversas ferramentas Oracle.
Antes de 1991 a única forma de usar construções procedurais com o SQL era usar PRO*C. Foi onde as instruções SQL do Oracle foram embutidas em código C. O código C era pré-compilado para converter as instruções SQL em chamadas de bibliotecas.

Em 1991 o PL/SQL 1.0 foi lançado com o Oracle Versão 6.0. Ele era muito limitado nas suas capacidades.

Já a versão 2.0 era uma atualização maior, que suportava stored packages, procedures, funções, tabelas PL/SQL, registros definidos pelo programador e package extensions. Esta versão foi lançada com o Oracle Versão 7.0.

O PL/SQL Versão 2.1 foi liberado com a Versão 7.1 do Oracle. Isto permitiu o uso de stored functions dentro de instruções SQL e a criação de SQL dinâmico pelo uso do pacote DBMS_SQL. Foi também possível executar instruções de Linguagens de Definição de Dados de programas PL/SQL.

A Versão 2.2 PL/SQL foi lançada com a Versão 7.2 do Oracle. Ele implementava uma proteção do código para programas PL/SQL e também o agendamento de trabalhos do banco de dados com o pacote DBMS_JOB.

A Versão 2.3 do PL/SQL foi lançado com a Versão 7.3 do Oracle. Esta versão aumentou as capacidades das tabelas PL/SQL e adicionou funcionalidades de E/S de arquivos.

A Versão 2.4 do PL/SQL foi liberada com a Versão 8.0 do Oracle. Esta versão suporta os melhoramentos do Oracle 8, incluindo Large Objects, projeto orientado a objetos, tabelas aninhadas e Oracle advanced queuing.

Estrutura Básica do PL/SQL
A unidade básica em PL/SQL é um bloco. Todos os programas em PL/SQL são compostos por blocos, que podem estar localizados uns dentro dos outros. Geralmente, cada bloco efetua uma ação lógica no programa. Um bloco tem basicamente a seguinte estrutura:


DECLARE

/* Seção para declaração de variáveis, */
/* tipos e subprogramas locais. */

BEGIN

/* Seção Executável: nesta seção ficam */
/* as instruções procedurais e SQL. */
/* Esta é a única seção do bloco que é indispensável. */

EXCEPTION

/* Seção onde ficam as instruções de tratamento de erro. */

END;


Somente a seção executável é obrigatória, as outras seções são opcionais. As únicas instruções SQL permitidas em um programa PL/SQL são SELECT, INSERT, UPDATE, DELETE , várias outras instruções de manipulação de dados e mais algum controle de transação. Contudo, a instrução SELECT tem uma forma especial na qual uma única tupla é colocada em variáveis.

As instruções de definição de dados como CREATE, DROP ou ALTER não são permitidas. A seção executável também contém construções tais como atribuições, desvios, loops, chamadas a procedimentos e triggers.

Para executar um programa PL/SQL, podemos digitá-lo no sqlplus ou então colocar o código em um arquivo e executar o arquivo nas várias formas possíveis.

Variáveis e Tipos
As informações são transmitidas entre um programa PL/SQL e o banco de dados através de variáveis. Toda variável tem um tipo específico associado a ela. Este tipo pode ser:

• Um dos tipos usados pelo SQL para colunas do banco de dados;
  

• Um tipo genérico usado no PL/SQL tal como NUMBER;
  
• Declarado para ser o mesmo tipo de alguma coluna do banco de dados.
   

O tipo genérico mais comumente usado é o NUMBER, nesse tipo de Variável pode-se armazenar números inteiros e reais.

O tipo string de caracteres mais usado é o VARCHAR(n), onde n é o tamanho máximo da string em bytes. É obrigatório que este tamanho seja informado, pois não há um valor default. Por exemplo, podemos declarar:

DECLARE
idade NUMBER;
nome VARCHAR(20);

O PL/SQL permite variáveis do tipo BOOLEAN, mesmo o Oracle não suportando BOOLEAN como um tipo para colunas dos bancos de dados.

Em muitos casos, uma variável do PL/SQL será usada para manipular dados armazenados em uma relação existente. Neste caso, é essencial que a variável tenha o mesmo tipo da coluna da relação. Se houver algum tipo incompatível, atribuições de/para variáveis e comparações podem não funcionar da forma que você espera. Para evitar isso, Você pode usar o operador %TYPE, que faz com que a variável assuma o tipo que tenha sido declarado para a coluna desejada. Por exemplo:

DECLARE
nome clientes.nome%TYPE;

/* dá a variável nome do PL/SQL qualquer tipo que tenha sido declarado para a coluna */
/* nome na relação clientes. */

A variável também pode ter um tipo que é um registro com muitos campos. A forma mais simples de declarar tal variável é usar %ROWTYPE no nome da relação. O resultado é um tipo registro no qual os campos têm os mesmos nomes e tipos que os atributos da relação. Por exemplo:

DECLARE
Tupla_nome nome%ROWTYPE;

/* faz a variável Tupla_nome ser um registro com campos nome e telefone, */
/* assumindo que a relação tem o esquema clientes(nome, telefone). */
 

O valor inicial de qualquer variável, independente de seu tipo, é NULL. Podemos atribuir valores às variáveis, usando o operador ":=". A atribuição pode ocorrer logo depois do tipo da variável ser declarado, ou em qualquer lugar da seção executável do programa. Por exemplo:

DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;

run;

     
Este programa não tem efeito quando executado, porque não ocorrem alterações no banco de dados.


Programas em PL/SQL
A instrução SELECT no PL/SQL funciona apenas se o resultado da consulta contém uma única tupla. Se a consulta retorna mais do que uma tupla, você precisa usar um cursor, como descrito na próxima seção. Aqui está um exemplo:

CREATE TABLE T1(
e INTEGER,
f INTEGER);

DELETE FROM T1;
INSERT INTO T1 VALUES(1, 3);
INSERT INTO T1 VALUES(2, 4);

/* Acima está o SQL comum; */
/* abaixo está o programa PL/SQL. */

DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
INSERT INTO T1 VALUES(b,a);
END;

run;

Acidentalmente, há apenas uma tupla de T1 que possui o primeiro componente maior do que 1, ou seja (2,4). A instrução INSERT portanto insere (4,2) em T1.

Fluxo de Controle no PL/SQL
O PL/SQL permite que você crie ramificações (ifs) e laços de uma forma bastante familiar.

Uma instrução IF se parece com:
IF <condition> THEN <statement_list> ELSE <statement_list> END IF;

A seguir está um exemplo, levemente modificado a partir daquele anterior, onde agora só fazemos a inserção se o segundo componente for 1. Se não, primeiro adicionamos 10 a cada componente e então inserimos:

DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
IF b=1 THEN
INSERT INTO T1 VALUES(b,a);
ELSE
INSERT INTO T1 VALUES(b+10,a+10);
END IF;
END;

run;


Laços são criados com o seguinte:

LOOP
<loop_body> /* Uma lista de instruções. */
END LOOP;

Pelo menos uma das instruções em <loop_body> deve ser uma instrução EXIT da forma EXIT WHEN <condition>;
O laço termina se <condition> for verdadeiro. Por exemplo:

DECLARE
i NUMBER := 1;
BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;
.
run;


Procedures
Procedures PL/SQL comportam-se muito semelhantemente aos procedures em outras linguagens de programação. Aqui está um exemplo de um procedure PL/SQL addtuple1 que, dado um inteiro i, insere a tupla (i, 'xxx') dentro da seguinte relação exemplo:

CREATE TABLE T2 (
a INTEGER,
b CHAR(10)
);

CREATE PROCEDURE addtuple1(i IN NUMBER) AS
BEGIN
INSERT INTO T2 VALUES(i, 'xxx');
END addtuple1;
.
run;

Um procedure é introduzido pelas palavras-reservadas CREATE PROCEDURE seguidas por um nome de procedimento e seus parâmetros.
Pode existir qualquer número de parâmetros, cada um seguido por um modO e um tipo. Os modes possíveis são IN (somente-leitura), OUT (somente escrita) e INOUT (leitura e escrita).

Nota: Diferente do especificador de tipo em uma declaração de variável PL/SQL, o especificador de tipo em uma declaração de parâmetro não deve ter restrições. Por exemplo, CHAR(10) e VARCHAR(20) são ilegais; CHAR ou VARCHAR deve ser usadas em seus lugares. O tamanho real de um parâmetro depende do argumento correspondente que é passado quando o procedure é invocado.

Seguindo os argumentos está a palavra-reservada AS (IS é um sinônimo). Então vem o corpo, que é essencialmente um bloco PL/SQL. Nós repetimos o nome do procedure depois do END, mas isto é opcional. Contudo, a seção DECLARE não deve começar com a palavra-reservada DECLARE. Em vez disso, seguindo AS nós temos:

... AS
<local_var_declarations>
BEGIN
<procedure_body>
END;
.
run;

O run no final executa a instrução que cria o procedure; ele não executa o procedure. Para executá-lo use uma outra instrução PL/SQL, na qual o procedure é invocado como uma instrução executável. Por exemplo:

BEGIN addtuple1(99); END;
.
run;

Também podemos escrever funções em vez de procedures. Em uma declaração de função, seguimos a lista de parâmetros por RETURN e o tipo do valor de retorno:

CREATE FUNCTION <func_name>(<param_list>) RETURN <return_type> AS ...

No corpo da definição da função, "RETURN <expression>;" sai da função e retorna o valor de <expression>

Triggers

Triggers são construções especiais do PL/SQL similares a procedures. Contudo, um procedure é executado explicitamente de um outro bloco via uma chamada de procedimento, enquanto um trigger é executado implicitamente sempre que um evento engatilhado acontece. O evento engatilhado é um comando INSERT, DELETE ou UPDATE. O momento pode ser BEFORE ou AFTER. O trigger pode ser row- level (do nível de uma linha) ou statement-level (do nível de uma instrução), onde o primeiro dispara uma vez para cada linha afetada pela instrução que disparou o trigger e o segundo dispara uma vez para toda a instrução.

Abaixo está a sintaxe para a criação de um trigger no Oracle (esta sintaxe foi simplificada; para a versão completa tente HELP CREATE TRIGGER no sqlplus):

CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[FOR EACH ROW [WHEN (<trigger_condition>)]]
<trigger_body>

Ilustramos a sintaxe do Oracle para criação de um trigger através de um exemplo baseado nas duas tabelas a seguir:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

Criamos um trigger que deve inserir uma tupla em T5 quando uma tupla for inserida em T4. Especificamente, o trigger checa se a nova tupla tem um primeiro componente menor ou igual a 10, e se sim insere a tupla oposta em T5:

CREATE TRIGGER trig1
AFTER INSERT ON T4
FOR EACH ROW
WHEN (NEW.a <= 10)
BEGIN
INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
END trig1;
.
run;

As variáveis especiais NEW e OLD estão disponíveis para fazerem referência às tuplas nova e velha respectivamente.
Nota: No corpo do trigger, NEW e OLD deve ser precedidas por um dois pontos (":"), mas na cláusula WHEN, elas não seguem esta regra.

A instrução CREATE TRIGGER apenas cria o trigger; ela não executa o trigger. Apenas um evento engatilhado, tal como uma inserção em T4 neste exemplo, faz com que o trigger seja disparado.


Descobrindo Erros
O PL/SQL nem sempre comunica você sobre erros de compilação. Em vez disso, ele dá a você uma mensagem oculta tal como "procedure created with compilation errors". Se você não vê o que está errado imediatamente, tente usar o comando show errors procedure <procedure_name>;
Da mesma forma, você pode obter os erros associados a um trigger criado por show errors trigger <trigger_name>;
Além disso, "SHO ERR" é uma abreviação para "SHOW ERRORS", e você pode omitir "PROCEDURE ..." ou "TRIGGER ..." se você quiser ver apenas o erro de compilação mais recente.

Por Adriana Ferreira
adriana@sqlmagazine.com.br