Potencialize seu banco de dados Oracle com PL/SQL
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.