PL / SQL - PL/SQL

PL / SQL ( Linguagem Procedural para SQL) é Oracle Corporation 's processual extensão para SQL e o banco de dados relacional da Oracle . PL / SQL está disponível no banco de dados Oracle (desde a versão 6 - procedimentos / funções / pacotes / gatilhos PL / SQL armazenados desde a versão 7), banco de dados em memória Times Ten (desde a versão 11.2.1) e IBM DB 2 (desde a versão 9,7). A Oracle Corporation geralmente estende a funcionalidade PL / SQL com cada lançamento sucessivo do banco de dados Oracle.

PL / SQL inclui elementos de linguagem procedural, como condições e loops . Ele permite a declaração de constantes e variáveis , procedimentos e funções, tipos e variáveis ​​desses tipos e gatilhos. Ele pode lidar com exceções (erros em tempo de execução). Arrays são suportados envolvendo o uso de coleções PL / SQL. As implementações da versão 8 do banco de dados Oracle em diante incluem recursos associados à orientação a objetos . É possível criar unidades PL / SQL, como procedimentos, funções, pacotes, tipos e gatilhos, que são armazenados no banco de dados para reutilização por aplicativos que usam qualquer uma das interfaces programáticas do banco de dados Oracle.

Historicamente, a primeira versão pública da definição PL / SQL foi em 1995 e o ano de início do Oracle ~ 1992. Ele implementa o padrão ISO SQL / PSM .

Unidade de programa PL / SQL

A principal característica do SQL (não procedural) também é uma desvantagem do SQL: não se pode usar instruções de controle ( tomada de decisão ou controle iterativo ) se apenas o SQL for usado. PL / SQL é basicamente uma linguagem procedural, que fornece a funcionalidade de tomada de decisão, iteração e muitos outros recursos como outras linguagens de programação procedural. Uma unidade de programa de PL / SQL é um dos seguintes: bloco PL / SQL anónimo, procedimento , função , pacote especificação, corpo de embalagem, gatilho, tipo especificação, o tipo de corpo, biblioteca. As unidades de programa são o código-fonte PL / SQL que é compilado, desenvolvido e, finalmente, executado no banco de dados.

Bloco anônimo PL / SQL

A unidade básica de um programa de origem PL / SQL é o bloco, que agrupa declarações e instruções relacionadas. Um bloco PL / SQL é definido pelas palavras-chave DECLARE, BEGIN, EXCEPTION e END. Essas palavras-chave dividem o bloco em uma parte declarativa, uma parte executável e uma parte de tratamento de exceções. A seção de declaração é opcional e pode ser usada para definir e inicializar constantes e variáveis. Se uma variável não for inicializada, o valor padrão é NULL . A parte opcional de tratamento de exceções é usada para tratar erros de tempo de execução. Apenas a parte executável é necessária. Um bloco pode ter um rótulo.

Por exemplo:

<<label>>   -- this is optional
DECLARE
-- this section is optional
  number1 NUMBER(2);
  number2 number1%TYPE := 17;             -- value default
  text1   VARCHAR2(12) := 'Hello world';
  text2   DATE         := SYSDATE;        -- current date and time
BEGIN
-- this section is mandatory, must contain at least one executable statement
  SELECT street_number
    INTO number1
    FROM address
    WHERE name = 'INU';
EXCEPTION
-- this section is optional
   WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('Error Code is ' || TO_CHAR(sqlcode));
     DBMS_OUTPUT.PUT_LINE('Error Message is ' || sqlerrm);
END;

O símbolo :=funciona como um operador de atribuição para armazenar um valor em uma variável.

Os blocos podem ser aninhados - ou seja, porque um bloco é uma instrução executável, ele pode aparecer em outro bloco sempre que uma instrução executável for permitida. Um bloco pode ser enviado a uma ferramenta interativa (como SQL * Plus) ou embutido em um Oracle Pré-compilador ou programa OCI . A ferramenta ou programa interativo executa o bloco uma vez. O bloco não é armazenado no banco de dados, por isso é denominado bloco anônimo (mesmo que possua rótulo).

Função

A finalidade de uma função PL / SQL é geralmente usada para calcular e retornar um único valor. Este valor retornado pode ser um único valor escalar (como um número, data ou cadeia de caracteres) ou uma única coleção (como uma tabela ou matriz aninhada). As funções definidas pelo usuário complementam as funções integradas fornecidas pela Oracle Corporation.

A função PL / SQL tem a forma:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] <IS|AS>   -- heading part
amount number;   -- declaration block
BEGIN   -- executable part
	<PL/SQL block with return statement>
        RETURN <return_value>;
[Exception
	none]
        RETURN <return_value>;
END;

As funções de tabela com linhas de tubulação retornam coleções e assumem a forma:

CREATE OR REPLACE FUNCTION <function_name> [(input/output variable declarations)] RETURN return_type
[AUTHID <CURRENT_USER | DEFINER>] [<AGGREGATE | PIPELINED>] <IS|USING>
	[declaration block]
BEGIN
	<PL/SQL block with return statement>
        PIPE ROW <return type>;
        RETURN;
[Exception
	exception block]
        PIPE ROW <return type>;
        RETURN;
END;

Uma função deve usar apenas o tipo de parâmetro IN padrão. O único valor de saída da função deve ser o valor que ela retorna.

Procedimento

Os procedimentos se assemelham às funções no sentido de que são denominadas unidades de programa que podem ser invocadas repetidamente. A principal diferença é que as funções podem ser usadas em uma instrução SQL, enquanto os procedimentos não . Outra diferença é que o procedimento pode retornar vários valores, enquanto uma função deve retornar apenas um único valor.

O procedimento começa com uma parte do título obrigatório para conter o nome do procedimento e, opcionalmente, a lista de parâmetros do procedimento. Em seguida, vêm as partes declarativas, executáveis ​​e de tratamento de exceções, como no Bloco Anônimo PL / SQL. Um procedimento simples pode ser assim:

CREATE PROCEDURE create_email_address ( -- Procedure heading part begins
    name1 VARCHAR2,
    name2 VARCHAR2,
    company VARCHAR2,
    email OUT VARCHAR2
) -- Procedure heading part ends
AS
-- Declarative part begins (optional)
error_message VARCHAR2(30) := 'Email address is too long.';
BEGIN -- Executable part begins (mandatory)
    email := name1 || '.' || name2 || '@' || company;
EXCEPTION -- Exception-handling part begins (optional)
WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(error_message);
END create_email_address;

O exemplo acima mostra um procedimento independente - esse tipo de procedimento é criado e armazenado em um esquema de banco de dados usando a instrução CREATE PROCEDURE. Um procedimento também pode ser criado em um pacote PL / SQL - isso é chamado de Procedimento de pacote. Um procedimento criado em um bloco anônimo PL / SQL é chamado de procedimento aninhado. Os procedimentos autônomos ou de pacote, armazenados no banco de dados, são chamados de " procedimentos armazenados ".

Os procedimentos podem ter três tipos de parâmetros: IN, OUT e IN OUT.

  1. Um parâmetro IN é usado apenas como entrada. Um parâmetro IN é passado por referência, embora possa ser alterado pelo programa inativo.
  2. Um parâmetro OUT é inicialmente NULL. O programa atribui o valor do parâmetro e esse valor é retornado ao programa de chamada.
  3. Um parâmetro IN OUT pode ou não ter um valor inicial. Esse valor inicial pode ou não ser modificado pelo programa chamado. Quaisquer alterações feitas no parâmetro são retornadas ao programa de chamada por padrão por meio de cópia, mas - com a dica NO-COPY - podem ser passadas por referência .

A PL / SQL também oferece suporte a procedimentos externos por meio do ext-procprocesso padrão do banco de dados Oracle .

Pacote

Pacotes são grupos de funções, procedimentos, variáveis, tabela PL / SQL e instruções TYPE de registro, constantes, cursores, etc. conceitualmente vinculados, etc. O uso de pacotes promove a reutilização de código. Os pacotes são compostos da especificação do pacote e de um corpo de pacote opcional. A especificação é a interface para o aplicativo; declara os tipos, variáveis, constantes, exceções, cursores e subprogramas disponíveis. O corpo define totalmente os cursores e subprogramas e, portanto, implementa a especificação. Duas vantagens dos pacotes são:

  1. Abordagem modular, encapsulamento / ocultação de lógica de negócios, segurança, melhoria de desempenho, reutilização. Eles oferecem suporte a recursos de programação orientada a objetos , como sobrecarga de função e encapsulamento.
  2. Usando variáveis ​​de pacote, pode-se declarar variáveis ​​de nível de sessão (com escopo), pois as variáveis ​​declaradas na especificação de pacote têm um escopo de sessão.

Desencadear

Um acionador de banco de dados é como um procedimento armazenado que o banco de dados Oracle invoca automaticamente sempre que ocorre um evento especificado. É uma unidade PL / SQL nomeada que é armazenada no banco de dados e pode ser chamada repetidamente. Ao contrário de um procedimento armazenado, você pode ativar e desativar um gatilho, mas não pode invocá-lo explicitamente. Enquanto um gatilho está ativado, o banco de dados o invoca automaticamente - ou seja, o gatilho é disparado - sempre que seu evento de gatilho ocorre. Enquanto um gatilho está desativado, ele não dispara.

Você cria um gatilho com a instrução CREATE TRIGGER. Você especifica o evento de disparo em termos de instruções de disparo e o item em que atuam. Diz-se que o gatilho é criado ou definido no item - que pode ser uma tabela, uma visão , um esquema ou o banco de dados. Você também especifica o ponto de controle, que determina se o gatilho é acionado antes ou depois da execução da instrução de acionamento e se ele é acionado para cada linha afetada pela instrução de acionamento.

Se o gatilho for criado em uma tabela ou exibição, o evento de gatilho será composto de instruções DML e o gatilho será chamado de gatilho DML. Se o gatilho for criado em um esquema ou banco de dados, o evento de gatilho será composto de DDL ou instruções de operação do banco de dados e o gatilho será chamado de gatilho do sistema.

Um gatilho INSTEAD OF é: Um gatilho DML criado em uma visualização ou um gatilho do sistema definido em uma instrução CREATE. O banco de dados dispara o gatilho INSTEAD OF em vez de executar a instrução de gatilho.

Objetivo dos gatilhos

Os gatilhos podem ser escritos para os seguintes propósitos:

  • Gerando alguns valores de coluna derivados automaticamente
  • Impondo integridade referencial
  • Registro de eventos e armazenamento de informações sobre o acesso à mesa
  • Auditoria
  • Replicação síncrona de tabelas
  • Impondo autorizações de segurança
  • Prevenir transações inválidas

Tipos de dados

Os principais tipos de dados em PL / SQL incluem NUMBER, CHAR, VARCHAR2, DATE e TIMESTAMP.

Variáveis ​​numéricas

variable_name number([P, S]) := 0;

Para definir uma variável numérica, o programador anexa o tipo de variável NUMBER à definição do nome. Para especificar a precisão (opcional) (P) e a escala (opcional) (S), pode-se acrescentá-los entre colchetes, separados por vírgula. ("Precisão", neste contexto, refere-se ao número de dígitos que a variável pode conter e "escala" refere-se ao número de dígitos que podem seguir o ponto decimal.)

Uma seleção de outros tipos de dados para variáveis ​​numéricas incluiria: binary_float, binary_double, dec, decimal, precisão dupla, float, inteiro, int, numérico, real, small-int, binary_integer.

Variáveis ​​de caráter

variable_name varchar2(20) := 'Text';

-- e.g.: 
address varchar2(20) := 'lake view road';

Para definir uma variável de caractere, o programador normalmente anexa o tipo de variável VARCHAR2 à definição do nome. Segue entre colchetes o número máximo de caracteres que a variável pode armazenar.

Outros tipos de dados para variáveis ​​de caractere incluem: varchar, char, long, raw, long raw, nchar, nchar2, clob, blob e bfile.

Variáveis ​​de data

variable_name date := to_date('01-01-2005 14:20:23', 'DD-MM-YYYY hh24:mi:ss');

Variáveis ​​de data podem conter data e hora. O tempo pode ficar de fora, mas não há como definir uma variável que contenha apenas o tempo. Não há tipo DATETIME. E há um tipo TIME. Mas não há nenhum tipo TIMESTAMP que possa conter um carimbo de data / hora refinado de até milissegundos ou nanossegundos. Tipos de dados Oracle

A TO_DATEfunção pode ser usada para converter strings em valores de data. A função converte a primeira string entre aspas em uma data, usando como definição a segunda string entre aspas, por exemplo:

 to_date('31-12-2004', 'dd-mm-yyyy')

ou

 to_date ('31-Dec-2004', 'dd-mon-yyyy', 'NLS_DATE_LANGUAGE = American')

Para converter as datas em strings, usa-se a função TO_CHAR (date_string, format_string).

A PL / SQL também suporta o uso de literais ANSI de data e intervalo. A cláusula a seguir fornece um intervalo de 18 meses:

WHERE dateField BETWEEN DATE '2004-12-30' - INTERVAL '1-6' YEAR TO MONTH
    AND DATE '2004-12-30'

Exceções

As exceções - erros durante a execução do código - são de dois tipos: definidas pelo usuário e predefinidas.

As exceções definidas pelo usuário são sempre levantadas explicitamente pelos programadores, usando os comandos RAISEou RAISE_APPLICATION_ERROR, em qualquer situação em que eles determinem que é impossível para a execução normal continuar. O RAISEcomando tem a sintaxe:

RAISE <exception name>;

Oracle Corporation tem predefinidos várias exceções, como NO_DATA_FOUND, TOO_MANY_ROWS, etc. Cada exceção tem um número de mensagem de erro SQL e erro SQL associado a ele. Os programadores podem acessá-los usando as funções SQLCODEe SQLERRM.

Tipos de dados para colunas específicas

Variable_name Table_name.Column_name%type;

Esta sintaxe define uma variável do tipo da coluna referenciada nas tabelas referenciadas.

Os programadores especificam tipos de dados definidos pelo usuário com a sintaxe:

type data_type is record (field_1 type_1 := xyz, field_2 type_2 := xyz, ..., field_n type_n := xyz);

Por exemplo:

declare
    type t_address is record (
        name address.name%type,
        street address.street%type,
        street_number address.street_number%type,
        postcode address.postcode%type);
    v_address t_address;
begin
    select name, street, street_number, postcode into v_address from address where rownum = 1;
end;

Este programa de amostra define seu próprio tipo de dados, chamado t_address , que contém os campos nome, rua, street_number e código postal .

Portanto, de acordo com o exemplo, podemos copiar os dados do banco de dados para os campos do programa.

Usando este tipo de dados, o programador definiu uma variável chamada v_address e carregou-a com os dados da tabela ADDRESS.

Os programadores podem endereçar atributos individuais em tal estrutura por meio da notação de ponto, assim:

v_address.street := 'High Street';

Declarações condicionais

O segmento de código a seguir mostra a construção IF-THEN-ELSIF-ELSE. As partes ELSIF e ELSE são opcionais, portanto, é possível criar construções IF-THEN ou IF-THEN-ELSE mais simples.

IF x = 1 THEN
   sequence_of_statements_1;
ELSIF x = 2 THEN
   sequence_of_statements_2;
ELSIF x = 3 THEN
   sequence_of_statements_3;
ELSIF x = 4 THEN
   sequence_of_statements_4;
ELSIF x = 5 THEN
   sequence_of_statements_5;
ELSE
   sequence_of_statements_N;
END IF;

A instrução CASE simplifica algumas estruturas IF-THEN-ELSIF-ELSE grandes.

CASE
   WHEN x = 1 THEN sequence_of_statements_1;
   WHEN x = 2 THEN sequence_of_statements_2;
   WHEN x = 3 THEN sequence_of_statements_3;
   WHEN x = 4 THEN sequence_of_statements_4;
   WHEN x = 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;

A instrução CASE pode ser usada com o seletor predefinido:

CASE x
   WHEN 1 THEN sequence_of_statements_1;
   WHEN 2 THEN sequence_of_statements_2;
   WHEN 3 THEN sequence_of_statements_3;
   WHEN 4 THEN sequence_of_statements_4;
   WHEN 5 THEN sequence_of_statements_5;
   ELSE sequence_of_statements_N;
END CASE;

Manipulação de matriz

PL / SQL se refere a matrizes como "coleções". A linguagem oferece três tipos de coleções:

  1. Matrizes associativas (tabelas index-by)
  2. Tabelas aninhadas
  3. Varrays (matrizes de tamanho variável)

Os programadores devem especificar um limite superior para varrays, mas não precisam para tabelas index-by ou para tabelas aninhadas. A linguagem inclui vários métodos de coleção usados ​​para manipular elementos de coleção: por exemplo FIRST, LAST, NEXT, PRIOR, EXTEND, TRIM, DELETE, etc. As tabelas index-by podem ser usadas para simular matrizes associativas, como neste exemplo de uma função memo para a função de Ackermann em PL / SQL .

Matrizes associativas (tabelas index-by)

Com tabelas index-by, a matriz pode ser indexada por números ou strings. Ele é paralelo a um mapa Java , que compreende pares de valores-chave. Existe apenas uma dimensão e ela é ilimitada.

Tabelas aninhadas

Com tabelas aninhadas, o programador precisa entender o que está aninhado. Aqui, é criado um novo tipo que pode ser composto de vários componentes. Esse tipo pode então ser usado para criar uma coluna em uma tabela e aninhados nessa coluna estão esses componentes.

Varrays (matrizes de tamanho variável)

Com Varrays, você precisa entender que a palavra "variável" na frase "matrizes de tamanho variável" não se aplica ao tamanho da matriz da maneira que você poderia imaginar. O tamanho com o qual a matriz é declarada é de fato fixo. O número de elementos na matriz é variável até o tamanho declarado. Portanto, os arrays de tamanho variável não são tão variáveis ​​em tamanho.

Cursores

Um cursor é um mecanismo, um ponteiro para uma área SQL privada que armazena informações provenientes de uma instrução SELECT ou DML (linguagem de manipulação de dados) (INSERT, UPDATE, DELETE ou MERGE). Um cursor contém as linhas (uma ou mais) retornadas por uma instrução SQL. O conjunto de linhas que o cursor mantém é referido como o conjunto ativo.

Um cursor pode ser explícito ou implícito. Em um loop FOR, um cursor explícito deve ser usado se a consulta for reutilizada, caso contrário, um cursor implícito é preferido. Se estiver usando um cursor dentro de um loop, use um FETCH é recomendado quando precisar coletar em massa ou quando precisar de SQL dinâmico.

Looping

Como uma linguagem procedural por definição, a PL / SQL fornece várias construções de iteração , incluindo instruções LOOP básicas, loops WHILE , loops FOR e loops FOR de cursor. Desde o Oracle 7.3, o tipo REF CURSOR foi introduzido para permitir que os conjuntos de registros sejam retornados de procedimentos e funções armazenados. O Oracle 9i introduziu o tipo predefinido SYS_REFCURSOR, o que significa que não precisamos mais definir nossos próprios tipos REF CURSOR.

Declarações LOOP

<<parent_loop>>
LOOP
	statements

	<<child_loop>>
	loop
		statements
		exit parent_loop when <condition>; -- Terminates both loops
		exit when <condition>; -- Returns control to parent_loop
	end loop child_loop;
        if <condition> then
           continue; -- continue to next iteration
        end if;

	exit when <condition>;
END LOOP parent_loop;

Os loops podem ser encerrados usando a EXIT palavra - chave ou levantando uma exceção .

Loops FOR

DECLARE
    var NUMBER;
BEGIN
    /* N.B. for loop variables in PL/SQL are new declarations, with scope only inside the loop */
    FOR var IN 0 .. 10 LOOP
        DBMS_OUTPUT.PUT_LINE(var);
    END LOOP;

    IF var IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('var is null');
    ELSE
        DBMS_OUTPUT.PUT_LINE('var is not null');
    END IF;
END;

Saída:

 0
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 var is null

Loops FOR do cursor

FOR RecordIndex IN (SELECT person_code FROM people_table)
LOOP
  DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code);
END LOOP;

Os loops cursor-for abrem automaticamente um cursor , lêem seus dados e fecham o cursor novamente.

Como alternativa, o programador PL / SQL pode predefinir a instrução SELECT do cursor com antecedência para (por exemplo) permitir a reutilização ou tornar o código mais compreensível (especialmente útil no caso de consultas longas ou complexas).

DECLARE
  CURSOR cursor_person IS
    SELECT person_code FROM people_table;
BEGIN
  FOR RecordIndex IN cursor_person
  LOOP
    DBMS_OUTPUT.PUT_LINE(recordIndex.person_code);
  END LOOP;
END;

O conceito de person_code dentro do loop FOR é expresso com a notação de ponto ("."):

RecordIndex.person_code

SQL Dinâmico

Enquanto os programadores podem incorporar prontamente instruções de Linguagem de Manipulação de Dados (DML) diretamente no código PL / SQL usando instruções SQL diretas, a Linguagem de Definição de Dados (DDL) requer instruções "SQL Dinâmicas" mais complexas no código PL / SQL. No entanto, as instruções DML sustentam a maioria do código PL / SQL em aplicativos de software típicos.

No caso do SQL dinâmico PL / SQL, as primeiras versões do banco de dados Oracle exigiam o uso de uma DBMS_SQLbiblioteca de pacotes Oracle complicada . Versões mais recentes, entretanto, introduziram um "SQL dinâmico nativo" mais simples, junto com uma EXECUTE IMMEDIATEsintaxe associada .

Linguagens semelhantes

A PL / SQL funciona de forma análoga às linguagens procedurais embutidas associadas a outros bancos de dados relacionais . Por exemplo, Sybase ASE e Microsoft SQL Server têm Transact-SQL , PostgreSQL tem PL / pgSQL (que emula PL / SQL até certo ponto), MariaDB inclui um analisador de compatibilidade PL / SQL e IBM DB2 inclui SQL Procedural Language, que está em conformidade com a ISO SQL ‘s SQL / PSM padrão.

Os designers da PL / SQL modelaram sua sintaxe na de Ada . Tanto Ada quanto PL / SQL têm Pascal como ancestral comum e, portanto, PL / SQL também se assemelha a Pascal em muitos aspectos. No entanto, a estrutura de um pacote PL / SQL não se assemelha à estrutura básica do programa Object Pascal implementada por uma unidade Borland Delphi ou Free Pascal . Os programadores podem definir tipos de dados globais públicos e privados, constantes e variáveis ​​estáticas em um pacote PL / SQL.

A PL / SQL também permite a definição de classes e instanciar essas classes como objetos no código PL / SQL. Isso se assemelha ao uso em linguagens de programação orientadas a objetos , como Object Pascal , C ++ e Java . PL / SQL se refere a uma classe como um "Tipo de dados abstratos" (ADT) ou "Tipo definido pelo usuário" (UDT) e a define como um tipo de dados Oracle SQL em oposição a um tipo definido pelo usuário PL / SQL, permitindo seu uso no mecanismo Oracle SQL e no mecanismo Oracle PL / SQL. O construtor e os métodos de um tipo de dados abstrato são escritos em PL / SQL. O tipo de dados abstrato resultante pode operar como uma classe de objeto em PL / SQL. Esses objetos também podem persistir como valores de coluna nas tabelas do banco de dados Oracle.

PL / SQL é fundamentalmente diferente do Transact-SQL , apesar das semelhanças superficiais. Portar código de um para o outro geralmente envolve um trabalho não trivial, não apenas devido às diferenças nos conjuntos de recursos das duas linguagens, mas também devido às diferenças muito significativas na forma como o Oracle e o SQL Server lidam com simultaneidade e bloqueio .

O produto StepSqlite é um compilador PL / SQL para o popular banco de dados SQLite pequeno que oferece suporte a um subconjunto da sintaxe PL / SQL. O lançamento do Berkeley DB 11g R2 da Oracle adicionou suporte para SQL baseado na popular API SQLite, incluindo uma versão do SQLite no Berkeley DB. Consequentemente, o StepSqlite também pode ser usado como uma ferramenta de terceiros para executar o código PL / SQL no Berkeley DB.

Veja também

Referências

Leitura adicional

links externos