Hoje vim trazer um início do novo mundo que estou estudando nas últimas semanas, o monstro chamado PL/SQL. Espero que aproveitem a leitura.

PL/SQL: Procedural Language SQL

"PL/SQL é uma das tecnologias core da Oracle, sendo essencial para tirar o máximo potencial do banco de dados Oracle."

O que é PL/SQL?

Basicamente, o PL/SQL pega o SQL e o torna ainda mais potente, adicionando a capacidade de incluir lógica de negócios junto com as funcionalidades do SQL. Ou seja, você pode usar uma linguagem procedural que conta com estruturas de decisão e loops, assim como a possibilidade de criar variáveis e constantes para auxílio, além de outras muitas ferramentas.

A princípio, veremos os tipos de dados suportados pelo PL/SQL:

VARCHAR2(n): Conjunto de caracteres (string) de tamanho variável. n varia entre 1 e 4000 caracteres.

NUMBER(p, e): Representa um número com uma precisão de p e uma escala de e.

LONG: Conjunto de caracteres de tamanho variável até 2 gigabytes.

BOOLEAN: Valor binário.

DATE: Data.

CHAR(n): Conjunto de caracteres de tamanho fixo. O n máximo é de 255 bytes e o comprimento por omissão é de 1 byte.

BLOB, CLOB, NCLOB e BFILE: Tipos de dados para conteúdos binários até 4 gigabytes internos ou externos (BFILE) à base de dados.

RAW(n): Dados binários em bruto de comprimento variável. n máximo é de 255 bytes.

LONG RAW: Dados binários em bruto com um comprimento variável e de tamanho máximo igual a 2 gigabytes.

ROWID: String hexadecimal que representa o endereço único de uma linha numa tabela.

Agora veja como é feita uma declaração de variáveis:

identificador tipo_de_dados [(precisão, escala)] [NOT NULL] [:= expressão];

peso NUMBER := 20;

Seguindo com a declaração de constantes:

identificador CONSTANT tipo_de_dados [(precisão, escala)] := expressão;

pessoas CONSTANT NUMBER := 5;

Comentários suportados pelo PL/SQL: /*comentário*/ ou -- comentário

Os operadores seguem bem como os padrões de BD, porém os menos conhecidos e que são muito utilizados são: := para atribuição e => para associação.

Sabendo tudo isso, como posso fazer meu primeiro código em PL/SQL?

PL/SQL é uma linguagem estruturada por blocos, bloco esse definido por: DECLARE, BEGIN, EXCEPTION e END. Sendo somente obrigatórios BEGIN e END, finalizados com ; (ponto e vírgula). Onde tudo dentro de BEGIN será executado. Tudo que precisa ser declarado fica em DECLARE (variáveis, constantes e outros elementos como CURSORES).

Exemplo de bloco:

BEGIN
  DBMS_OUTPUT.put_line('HELLO WORLD!');
END;

Agora uma melhoria desse primeiro exemplo:

DECLARE
  l_message VARCHAR2(100) := 'Hello';
BEGIN
  DECLARE
    l_message2 VARCHAR2(100) :=
      l_message || ' World!'; 
  BEGIN
    DBMS_OUTPUT.put_line(l_message2);
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
END;

Note que dentro do bloco temos DECLARE e também EXCEPTION, ambos opcionais, mas que podem ser bem úteis. Temos também ||, que é usado para concatenação.

Nesses primeiros blocos de exemplos, eles não possuem nomes, logo são conhecidos como blocos anônimos. No entanto, blocos anônimos só funcionam no início e não escalam bem.

Para nomear um bloco, o ideal é fazer uma procedure ou uma função, para que então o bloco seja construído dentro de algum deles. O que basicamente cada um faz:

Procedure -> Usada para realizar uma ação.

Function -> Usada para calcular e retornar um valor.

Usando o primeiro exemplo dentro de uma procedure, ficaria assim:

CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
  DBMS_OUTPUT.put_line('HELLO WORLD!');
END;

Repare que agora, com a procedure, temos um nome para esse bloco. Veja que para a função ficaria bem parecido:

CREATE OR REPLACE FUNCTION hello_world RETURN VOID AS
BEGIN
  DBMS_OUTPUT.put_line('HELLO WORLD!');
END;

Ainda é possível lançar um parâmetro dentro de uma PROCEDURE. Faça isso após o nome da PROCEDURE, seguindo a seguinte ordem: parameter_name parameter_mode data_type.

EX:

CREATE OR REPLACE PROCEDURE nome_procedure (nome_parametro IN VARCHAR2)

A PROCEDURE deve ser chamada dentro de outro bloco, como se estivesse chamando uma função, por EX:

BEGIN
  nome_procedure('Parametro Varchar = String');
END;

SOBRE NOMES NO PL/SQL

Dentro do PL/SQL não existe case-sensitive, logo, não se faz diferenciação entre maiúsculas e minúsculas. Sendo assim, 'hello_world', 'Hello_World', 'HELLO_WORLD' são a mesma coisa para o PL/SQL.

Nomes de tabelas, views e procedures podem ser chamados de modos diferentes, mas são guardados da seguinte forma: NOME_PROCEDURE, ou seja, tudo em uppercase, podendo ser chamado das seguintes formas: nome_procedure, NOME_PROCEDURE, "NOME_PROCEDURE".

PL/SQL + SQL

PL/SQL é uma linguagem de programação de banco de dados usada em bancos de dados Oracle. Logo, em sua maioria, seus códigos são usados em conjunto com SQL. Você apenas escreve o que precisa em SQL dentro do PL/SQL. Temos como exemplo uma tabela de funcionários, com uma PK sendo o employee_id e outra coluna sendo o last_name. Eu posso buscar o último nome do funcionário que tenha o id 138 da seguinte forma:

SELECT last_name
  FROM employees
 WHERE employee_id = 138;

Agora, como eu faria a mesma busca usando PL/SQL, copiando o nome do resultado e colocando numa variável local, usando a cláusula INTO, ficaria assim:

DECLARE
  l_name employees.last_name%TYPE;
BEGIN
  SELECT last_name
    INTO l_name
    FROM employees
   WHERE employee_id = 138;
  DBMS_OUTPUT.put_line(l_name);
END;

Veja que temos algo novo nesse bloco, sendo ele o %TYPE. Ele é uma funcionalidade muito interessante que funciona de âncora, transformando o tipo da coluna SQL na variável do PL/SQL. Ou seja, como last_name é VARCHAR, logo, l_name também é VARCHAR.

Voltando ao bloco: de início, eu declaro a variável local (l_name) e então uso a âncora de tipo de dado (%TYPE). Em seguida, é executado tudo, guardando o resultado na variável l_name.

Claro que é perfeitamente possível fazer também DELETE, UPDATE e INSERT usando PL/SQL. Mostrarei agora alguns exemplos básicos deles.

Em um cenário que é necessário excluir todos os funcionários do departamento 10 e, após isso, mostrar a quantidade de linhas que foram afetadas com essa execução:

DECLARE
  l_dept_id employees.department_id%TYPE := 10;
BEGIN
  DELETE FROM employees
       WHERE department_id = l_dept_id;

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;

Note o comando SQL%ROWCOUNT; ele é usado para mostrar a quantidade de linhas afetadas. Ele entra na categoria de cursores, mas é um tipo especial. Depois, vale a pena um texto só sobre cursores, os especiais e como criar os seus próprios.

Atualizando todos os funcionários do departamento 10 com 20% a mais de salário:

DECLARE
  l_dept_id employees.department_id%TYPE := 10;
BEGIN
  UPDATE employees
    SET salary = salary * 1.2
   WHERE department_id = l_dept_id;

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;

Inserindo um novo funcionário na tabela:

BEGIN
  INSERT INTO employees (employee_id
                       , last_name
                       , department_id
                       , salary)
       VALUES (100
             , 'Feuerstein'
             , 10
             , 200000);

  DBMS_OUTPUT.put_line(SQL%ROWCOUNT);
END;

Por aqui, finalizo a introdução sobre PL/SQL. Caso tenha se interessado, não deixe de ver os links das fontes: Oracle e Medium, creditado ao @alexjosesilva.