PL SQL: Um guia completo

A linguagem SQL (Structured Query Language) é amplamente utilizada no gerenciamento e manipulação de dados em sistemas de banco de dados. No entanto, em situações onde é necessária a realização de tarefas mais complexas. Como, por exemplo, a manipulação de dados em larga escala, geração de relatórios personalizados. Ou também a integração com aplicações externas, a linguagem SQL pode se mostrar limitada.

Nesse caso, é comum recorrer à utilização de linguagens de programação procedurais. Como o PL no SQL, que permitem uma maior flexibilidade e controle sobre as operações executadas.

O PL (Procedural Language) no SQL é uma combinação dessas duas linguagens, proporcionando os benefícios de ambos os mundos. A sintaxe e a semântica do SQL são mantidas, facilitando a manipulação e a consulta de dados. Por outro lado, o PL na SQL adiciona estruturas de controle de fluxo, sub-rotinas e funções. Isso permite o desenvolvimento de procedimentos mais sofisticados e eficientes.

O PL SQL é compatível com bancos de dados Oracle. No momento, o MySQL não possui compatibilidade.

Este artigo visa apresentar os fundamentos do PL na SQL, abordando aspectos como a sintaxe e semântica. Além disso, tipos de dados, estruturas de controle de fluxo, subrotinas e funções. E por fim, tratamento de exceções, interação com o banco de dados, tabela de verificação, escopos e performance.

Assim, ao final deste artigo, você estará preparado para empregar o PL na SQL em suas atividades de gerenciamento de dados. Proporcionando soluções mais eficientes e escaláveis.

Sintaxe e semântica do PL no SQL

O PL/SQL (Procedural Language/Structured Query Language) é um dialeto de SQL (Structured Query Language). Ele permite a escrita de procedimentos além de consultas e atualizações de dados. Ele é usado principalmente em sistemas de gerenciamento de banco de dados relacional, como o Oracle Database.

Sintaxe do PL/SQL

A sintaxe do PL/SQL é semelhante à do SQL padrão. Porém, inclui elementos de programação orientada a objetos, como variáveis, tipos de dados, controladores de fluxo de programa, loops e funções. Alguns exemplos de sintaxe PL/SQL incluem:

• Declaração de variáveis e tipos de dados: DECLARE v_name VARCHAR2(50) := 'John';
• Condicional if-then-else: IF v_age > 18 THEN v_status := 'Adulto'; ELSIF v_age > 65 THEN v_status := 'Idoso'; END IF;
• Loop while: WHILE v_count > 0 LOOP
• Funções: CREATE OR REPLACE FUNCTION my_function(p_param1 IN VARCHAR2, p_param2 OUT VARCHAR2) RETURN VARCHAR2 IS BEGIN ... END;

A sintaxe do PL/SQL permite que os desenvolvedores criem procedimentos. Tudo além das consultas e atualizações de dados, tornando-os mais eficientes e flexíveis.

Além disso, o PL/SQL suporta programação orientada a objetos. Isso permite que os desenvolvedores criem bibliotecas de funções reutilizáveis e modos de programação mais complexos.

A semântica do PL/SQL é a maneira como o PL/SQL interpreta e executa o código. Dessa forma, o PL no SQL é interpretado e executado pelo motor de PL/SQL do Oracle Database. Ele lê o código e gera o código de execução correspondente.

Semântica do PL/SQL

A semântica do PL/SQL inclui:

• Tipos de dados: O PL/SQL suporta muitos tipos de dados, como números, strings, dates, booleans, etc.
• Variáveis: As variáveis são usadas para armazenar dados temporariamente durante a execução do código.
• Controladores de fluxo de programa: O PL/SQL suporta controladores de fluxo de programa. Por exemplo, como if-then-else, while, for e case, para controlar a execução do código.
• Funções: As funções são subrotinas que podem ser chamadas dentro do código principal. Dessa forma, elas podem receber parâmetros e retornar valores.
• Procedimentos: Podemos chamar os procedimentos dentro do bloco principal, pois são subrotinas. Nesse sentido, eles podem receber parâmetros e executar uma série de instruções.
• Triggers: Os triggers são subrotinas que são acionados automaticamente em resposta a certas ações no banco de dados. Como inserir, atualizar ou excluir registros.

Assim, a sintaxe e semântica do PL/SQL permitem que criarmos procedimentos e funções complexos, que podemos reutiliza-los e otimizados, tornando o desenvolvimento de aplicações mais eficiente e eficaz.

Variáveis e tipos de dados em PL no SQL

O PL/SQL suporta uma ampla variedade de tipos de dados, cada um com sua própria semântica e uso. Assim, alguns exemplos de tipos de dados suportados incluem:

• Números inteiros: representam números inteiros sem precisão. Exemplo: NUMBER(5), NUMBER(10)
• Números decimais: representam números com precisão. Exemplo: NUMBER(5,2), NUMBER(10,2)
• Strings: representam sequências de caracteres. Exemplo: VARCHAR2(50), VARCHAR2(100)
• Dates: representam datas e horas. Exemplo: DATE, TIMESTAMP
• Booleans: representam valores booleanos (verdadeiro ou falso). Exemplo: BOOLEAN, NUMBER(1)
• Tipos de dados de tipo PL/SQL: representam tipos de dados específicos do PL/SQL. Como cursores, tipos de dados genéricos e tipos de dados de intervalo.

Para definir uma variável em PL/SQL, precisamos especificar o tipo de dados e uma variável. A sintaxe para definir uma variável é:

DECLARE
   v_nome_varialvel VARCHAR2(tamanho_maximo) := 'valor_inicial';
BEGIN
   -- código PL/SQL
END;

Por exemplo, para definir uma variável chamada “v_idade” do tipo INTEGER com um tamanho máximo de 5, você usaria o seguinte código:

DECLARE
   v_idade INTEGER(5) := 25;
BEGIN
   -- código PL/SQL
END;

Para usar a variável em uma instrução SQL ou PL/SQL, você pode acessá-la diretamente, como no exemplo abaixo:

SELECT v_idade FROM pessoas;

ou

IF v_idade > 18 THEN
   dbms_output.put_line('Esta pessoa é adulta');
END IF;

Dessa forma, é importante lembrar que os tipos de dados suportados podem variar dependendo do banco de dados que você está usando. Mas a sintaxe para definir e usar variáveis é geralmente semelhante.

Estruturas de controle de fluxo

O PL/SQL suporta várias estruturas de controle de fluxo, que permitem controlar a execução do código de acordo com diferentes condições. Assim, algumas das principais estruturas de controle de fluxo do PL/SQL incluem:

• IF-THEN-ELSE: permite executar um bloco de código se uma condição for verdadeira, outro bloco de código se a condição for falsa. A sintaxe é a seguir:

IF condicao THEN
   -- código a ser executado se condição for verdadeira
ELSE
   -- código a ser executado se condição for falsa
END IF;

• WHILE: permite executar um bloco de código repetidamente enquanto uma condição for verdadeira. A sintaxe é a seguir:

WHILE condicao LOOP
   -- código a ser executado
END LOOP;

• FOR: permite executar um bloco de código para cada valor de uma sequência. A sintaxe é a seguir:

FOR i IN sequencia LOOP
   -- código a ser executado para cada valor de sequência
END LOOP;

• SWITCH: permite escolher entre várias opções com base em um valor. A sintaxe é a seguir:

DECLARE
   v_opcao NUMBER;
BEGIN
   -- código para cada opção
   CASE v_opcao
      WHEN 1 THEN
         -- código para opção 1
      WHEN 2 THEN
         -- código para opção 2
      WHEN 3 THEN
         -- código para opção 3
      ELSE
         -- código para opção inválida
   END CASE;
END;

Essas são algumas das principais estruturas de controle de fluxo do PL/SQL. Elas permitem controlar a execução do código de acordo com diferentes condições. Portanto, elas são extremamente úteis para criar código reutilizável e modular, e podem ser combinadas para criar lógicas mais complexas.

Subrotinas e funções em PL no SQL

Em PL/SQL, subrotinas e funções são elementos importantes de programação, que permitem reutilizar código e simplificar a lógica do programa. Assim, a principal diferença entre subrotinas e funções é que as subrotinas não retornam valores, enquanto as funções retornam valores.

Subrotinas são blocos de código que são chamados dentro de outros blocos de código. Dessa forma, são usadas para dividir o código em partes menores e mais gerenciáveis. E podem ser chamadas várias vezes dentro do mesmo programa. Assim, a sintaxe para criar uma subrotina é a seguir:

PROCEDURE nome_da_subrotina IS
BEGIN
   -- código da subrotina
END;

Funções são subrotinas que retornam valores. Assim, são usadas para calcular e retornar valores a partir de dados de entrada. A sintaxe para criar uma função é a seguir:

FUNCTION nome_da_funcao RETURN tipo_de_retorno IS
BEGIN
   -- código da função
END;

Para chamar uma subrotina ou função, você usa a palavra-chave “CALL” seguida pelo nome da subrotina ou função. A sintaxe para chamar uma subrotina é a seguir:

CALL nome_da_subrotina;

E a sintaxe para chamar uma função é a seguir:

DECLARE
   v_resultado tipo_de_retorno;
BEGIN
   v_resultado := nome_da_funcao(param1, param2, ...);
END;

Em resumo, subrotinas e funções são elementos importantes em PL/SQL, que permitem reutilizar código e simplificar a lógica do programa. Dessa forma, usamos os subrotinas para dividir o código em partes menores e mais gerenciáveis. Isso ocorre, enquanto utilizamos as funções para calcular e retornar valores a partir de dados de entrada.

Lidando com erros e exceções no PL

PL/SQL suporta a lidar com erros e exceções usando a estrutura “try-catch-finally”. Assim, a estrutura “try-catch-finally” permite que você execute um bloco de código. Além disso, permite retornar um valor de erro específico, ou executar um bloco de código independentemente do resultado da tentativa anterior.

A sintaxe básica da estrutura “try-catch-finally” é a seguir:

BEGIN
   -- código a ser executado
EXCEPTION
   -- código a ser executado se ocorrer uma exceção
   -- isso é executado apenas se ocorrer uma exceção
END;

A estrutura “try-catch-finally” é composta por três partes:

• “try”: contém o código que será executado.
• “catch”: contém o código que será executado se ocorrer uma exceção.
• “finally”: contém o código que será executado independentemente do resultado da tentativa anterior.

A estrutura “try-catch-finally” é útil para lidar com erros e exceções. Permitindo que você execute código independentemente do resultado da tentativa anterior e retornar um valor de erro específico.

Por exemplo, aqui está um exemplo de como usar a estrutura “try-catch-finally”. Serve para lidar com erros ao tentar acessar um registro em um banco de dados:

DECLARE
   v_id NUMBER;
BEGIN
   -- Tentar acessar um registro
   SELECT id FROM tabela WHERE id = 1 INTO v_id;
   IF v_id IS NULL THEN
      -- Exceção de registro não encontrado
      RAISE_APPLICATION_ERROR(-20001, 'Registro não encontrado');
   END IF;
   -- Código a ser executado com sucesso
   dbms_output.put_line(v_id);
END;

Neste exemplo, primeiro a estrutura “try-catch-finally” é usada para tentar acessar um registro no banco de dados. Dessa forma, se o registro não for encontrado, a exceção “Registro não encontrado” é levantada e o código dentro do bloco “catch” é executado. Nesse sentido, se o registro for encontrado, o código dentro do bloco “finally” é executado.

Em resumo, a estrutura “try-catch-finally” é uma forma eficaz de lidar com erros e exceções em PL/SQL. Ela permite que você execute código independentemente do resultado da tentativa anterior e retornar um valor de erro específico.

Interação com o banco de dados

O PL/SQL é uma linguagem de programação orientada a objetos que é amplamente utilizada para interagir com bancos de dados. Dessa forma, ele fornece várias maneiras de manipular tabelas, realizar consultas e atualizar dados.

Manipulação de dados

Para manipular tabelas, você pode usar a sintaxe SQL padrão para inserir, atualizar e excluir registros. Por exemplo:

• Adicionar novos dados em uma tabela

Dessa forma, Para inserir um novo registro em uma tabela utilize a seguinte sintaxe:

INSERT INTO tabela (coluna1, coluna2, coluna3) VALUES (valor1, valor2, valor3);

• Atualizar dados em uma tabela

Para atualizar um registro existente, você pode usar a seguinte sintaxe:

UPDATE tabela SET coluna1 = valor1 WHERE id = 1;

• Adicionar dados existente em uma tabela

Portanto, podemos excluir um registro existente usando a sintaxe DELETE SQL:

DELETE FROM tabela WHERE id = 1;

Realizar consultas

Para realizar consultas, você pode usar a sintaxe SQL padrão para selecionar, ordenar e filtrar registros.

• Todos os dados de uma tabela

Por exemplo, para selecionar todos os registros de uma tabela, você pode usar a seguinte sintaxe:

SELECT * FROM tabela;

• Ordenar dados em uma tabela

Para ordenar os registros por uma coluna específica, você pode usar a seguinte sintaxe:

SELECT * FROM tabela ORDER BY coluna1 ASC;

• Filtrar dados em uma tabela

Para filtrar os registros por uma condição específica, você pode usar a seguinte sintaxe:

SELECT * FROM tabela WHERE coluna1 = 'valor';

Atualizar dados

Para atualizar dados, você pode usar a sintaxe UPADATE SQL padrão para atualizar valores de colunas específicas. Por exemplo, para atualizar o valor de uma coluna para todos os registros de uma tabela, você pode usar a seguinte sintaxe:

UPDATE tabela SET coluna1 = 'novo valor';

Além disso, o PL/SQL permite que você crie procedimentos além de consultas e atualizações de dados. Isso torna o código mais eficiente e flexível. Dessa forma, o PL/SQL suporta programação orientada a objetos. Isso permite que você crie bibliotecas de funções reutilizáveis e modos de programação mais complexos.

Compreendendo blocos e escopos em PL no SQL

Em PL/SQL, blocos e escopos são conceitos importantes na organização e gerenciamento de recursos.

Blocos

Um bloco é uma região de código que é executada como um único conjunto de instruções. Assim, os blocos são usados para agrupar várias instruções relacionadas em um único lugar. E também para definir a especificação de um procedimento ou função.

• Exemplo:

Suponha que tenhamos uma tabela de clientes com as seguintes colunas: ID, nome, email e endereço. Queremos criar um procedimento que permita inserir um novo cliente na tabela. Dessa forma, neste procedimento, precisamos obter o ID do cliente mais recente. E também, incrementá-lo em 1 para criar um novo ID para o novo cliente.

Aqui está o código para implementar esse procedimento, utilizando blocos:

CREATE OR REPLACE PROCEDURE insert_customer(
   p_name IN VARCHAR,
   p_email IN VARCHAR,
   p_address IN VARCHAR
)
AS
BEGIN
   -- Bloco de declaração de variáveis
   DECLARE
      l_id customer_id_type;
      l_new_id customer_id_type;
   BEGIN
      -- Bloco principal
      SELECT MAX(id) INTO l_id
      FROM customers;
      l_new_id := l_id + 1;

      -- Inserir o novo cliente na tabela
      INSERT INTO customers (name, email, address, id)
      VALUES (p_name, p_email, p_address, l_new_id);
   END;
END;

Neste exemplo, utilizamos dois blocos: o bloco de declaração de variáveis e o bloco principal. Dessa forma, utilizamos o bloco de declaração de variáveis para declarar as variáveis necessárias para o procedimento. Enquanto isso, usamos o bloco principal para executar as instruções do procedimento.

Dentro do bloco principal, usamos um SELECT para obter o ID do cliente mais recente. E também um INSERT para inserir o novo cliente na tabela. Além disso, criamos uma variável l_new_id para armazenar o novo ID gerado para o cliente inserido.

Escopo

Já o escopo é a região de código em que uma variável é ativa e acessada. O escopo é importante para garantir que as variáveis estejam acessíveis apenas em certas partes do código. E também para evitar conflitos de nomeação de variáveis.

• Exemplo:

Suponha que tenhamos uma tabela de vendas com as seguintes colunas: ID, produto, preço e data. Queremos criar um procedimento que permita calcular o valor total de vendas para um determinado produto em uma data específica. Neste procedimento, precisamos criar uma variável para armazenar o valor total de vendas e restringir o acesso a essa variável apenas dentro do bloco principal.

Aqui está o código para implementar esse procedimento, utilizando escopo:

CREATE OR REPLACE PROCEDURE total_sales_by_product_on_date(
   p_product_id IN product_id_type,
   p_date IN date_type,
   p_total_sales OUT sales_total_type
)
AS
BEGIN
   -- Bloco de declaração de variáveis
   DECLARE
      l_total_sales_on_date sales_total_type;
   BEGIN
      -- Bloco principal
      SELECT SUM(price) INTO l_total_sales_on_date
      FROM sales_history
      WHERE product_id = p_product_id AND date = p_date;
      
      -- Restringir o acesso à variável l_total_sales_on_date apenas dentro do bloco principal
      p_total_sales := l_total_sales_on_date;
   END;
END;

Neste exemplo, utilizamos um bloco de declaração de variáveis e um bloco principal. Assim, o bloco de declaração de variáveis é usado para declarar a variável l_total_sales_on_date, que armazena o valor total de vendas para o produto especificado em data específica.

A combinação de blocos e escopos permite que o código seja organizado de maneira clara e eficiente. Cada bloco pode ter seu próprio escopo, o que permite que as variáveis esteja declaradas e usadas apenas dentro do bloco. Dessa forma, isso aumenta a legibilidade e a manutenibilidade do código, tornando-o mais fácil de entender e de ser atualizado.

Além disso, a utilização de blocos e escopos pode ajudar a evitar erros e a garantir que as variáveis estejam usadas corretamente. Por exemplo, se uma variável for declarada com um escopo muito amplo, ela pode ser acessada em partes do código que não deveriam ter acesso a ela, o que pode levar a erros e bugs.