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.
- Um parâmetro IN é usado apenas como entrada. Um parâmetro IN é passado por referência, embora possa ser alterado pelo programa inativo.
- Um parâmetro OUT é inicialmente NULL. O programa atribui o valor do parâmetro e esse valor é retornado ao programa de chamada.
- 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-proc
processo 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:
- 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.
- 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_DATE
funçã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 RAISE
ou RAISE_APPLICATION_ERROR
, em qualquer situação em que eles determinem que é impossível para a execução normal continuar. O RAISE
comando 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 SQLCODE
e 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:
- Matrizes associativas (tabelas index-by)
- Tabelas aninhadas
- 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_SQL
biblioteca de pacotes Oracle complicada . Versões mais recentes, entretanto, introduziram um "SQL dinâmico nativo" mais simples, junto com uma EXECUTE IMMEDIATE
sintaxe 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
- Feuerstein, Steven ; Bill Pribyl (2014). Programação Oracle PL / SQL (6ª ed.). O'Reilly & Associates . ISBN 978-1449324452.
- Naudé, Frank (9 de junho de 2005). "Oracle PL / SQL FAQ rev 2.08" .