Oracle Procedimento armazenado e funções PL/SQL com exemplos

O que são subprogramas PL/SQL?

Neste tutorial, você encontrará uma descrição detalhada de como criar e executar blocos nomeados, procedimentos e funções.

Procedimentos e funções são subprogramas que podem ser criados e salvos no banco de dados como objetos de banco de dados. Eles também podem ser chamados ou referenciados dentro de outros blocos.

Abordamos também as principais diferenças entre esses dois subprogramas e discutimos o Oracle funções integradas.

Terminologias em subprogramas PL/SQL

Antes de aprendermos sobre subprogramas PL/SQL, discutiremos as várias terminologias que fazem parte desses subprogramas.

Parâmetro

Um parâmetro é uma variável ou marcador de posição para qualquer valor válido. Tipo de dados PL/SQL por meio do qual o subprograma PL/SQL troca valores com o código principal. Este parâmetro permite a entrada de dados nos subprogramas e a execução de comandos.tracção de valores deles.

• Estes parâmetros deverão ser definidos juntamente com os subprogramas no momento da criação.
• Eles são incluídos na instrução de chamada para interagir com os subprogramas.
• O tipo de dados do parâmetro no subprograma e na instrução de chamada deve ser o mesmo.
• O tamanho do tipo de dados não deve ser mencionado no momento da declaração do parâmetro, pois o tamanho é dinâmico.

De acordo com sua finalidade, os parâmetros são classificados como:

1. Parâmetro IN
2. Parâmetro FORA
3. Parâmetro IN OUT

Parâmetro IN

• Utilizado para fornecer dados de entrada aos subprogramas.
• Trata-se de uma variável somente leitura dentro dos subprogramas; seu valor não pode ser alterado dentro do subprograma.
• Na instrução de chamada, pode ser uma variável, um valor literal ou uma expressão, como '5*8' ou 'a/b'.
• Por padrão, os parâmetros são do tipo IN.

Parâmetro FORA

• Utilizado para obter a saída dos subprogramas.
• É uma variável de leitura e escrita dentro dos subprogramas; seu valor pode ser alterado dentro deles.
• Na instrução de chamada, deve sempre ser uma variável para armazenar o valor do subprograma.

Parâmetro IN OUT

• Utilizado tanto para fornecer dados de entrada quanto para obter dados de saída dos subprogramas.
• É uma variável de leitura e escrita dentro dos subprogramas; seu valor pode ser alterado dentro deles.
• Na instrução de chamada, deve sempre ser uma variável para armazenar o valor do subprograma.

O tipo de parâmetro deve ser mencionado no momento da criação dos subprogramas.

RETORNO

RETURN é a palavra-chave que instrui o compilador a transferir o controle do subprograma para a instrução que o chamou. Em um subprograma, RETURN simplesmente significa que o controle precisa sair do subprograma; assim que o controlador encontra RETURN, o código subsequente é ignorado.

Normalmente, o bloco pai ou principal chama os subprogramas, e o controle passa do bloco pai para o subprograma chamado. A instrução RETURN no subprograma retorna o controle para o bloco pai. No caso de funções, a instrução RETURN também retorna um valor, cujo tipo de dados é especificado no momento da declaração da função.

O que é um procedimento em PL/SQL?

A Procedimento Em PL/SQL, um procedimento é uma unidade de subprograma que consiste em um grupo de instruções PL/SQL que podem ser chamadas por nome. Cada procedimento tem seu próprio nome exclusivo e é armazenado no arquivo de configuração. Oracle banco de dados como um objeto de banco de dados.

Observação: Um subprograma nada mais é do que um procedimento, e precisa ser criado manualmente conforme a necessidade. Uma vez criado, ele é armazenado como um objeto de banco de dados.

As características de uma unidade de subprograma de procedimento em PL/SQL são:

• Os procedimentos são blocos independentes que podem ser armazenados no banco de dados.
• Eles podem ser chamados pelo nome para executar as instruções PL/SQL.
• Eles são usados ​​principalmente para executar um processo.
• Eles podem ter blocos aninhados ou estar aninhados dentro de outros blocos ou pacotes.
• Elas contêm uma parte de declaração (opcional), uma parte de execução e uma parte de tratamento de exceções (opcional).
• Os valores podem ser passados ​​para um procedimento ou obtidos dele por meio de parâmetros.
• Esses parâmetros devem ser incluídos na instrução de chamada.
• Um procedimento pode ter uma instrução RETURN para retornar o controle ao bloco que o chamou, mas não pode retornar nenhum valor através de RETURN.
• Os procedimentos não podem ser chamados diretamente de instruções SELECT; eles podem ser chamados de outro bloco ou através da palavra-chave EXEC.

Sintaxe

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• O comando CREATE PROCEDURE instrui o compilador a criar um novo procedimento. A palavra-chave 'OR REPLACE' instrui-o a substituir o procedimento existente (se houver) pelo procedimento atual.
• O nome do procedimento deve ser único.
• A palavra-chave 'IS' é usada quando o procedimento armazenado está aninhado dentro de outro bloco. Se o procedimento for independente, usa-se 'AS'. Além dessa norma de codificação, ambas têm o mesmo significado.

Exemplo 1: Criando um procedimento e chamando-o usando EXEC. Neste exemplo, criamos um Oracle Procedimento que recebe um nome como entrada e imprime uma mensagem de boas-vindas como saída, usando o comando EXEC para chamá-lo.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Explicação:

• Code linha 1: Criando o procedimento com o nome 'welcome_msg' e um parâmetro 'p_name' do tipo 'IN'.
• Code linha 4: Imprimindo a mensagem de boas-vindas concatenando o nome inserido.
• O procedimento foi compilado com sucesso.
• Code linha 7: Chamando o procedimento usando EXEC com o parâmetro 'Guru99'. O procedimento é executado e imprime “Bem-vindo(a) Guru99".

O que é uma Função?

Uma função é um subprograma PL/SQL independente. Assim como um procedimento, uma função possui um nome único e é armazenada como um objeto de banco de dados PL/SQL. Suas características são:

• As funções são blocos independentes usados ​​principalmente para cálculos.
• Uma função utiliza a palavra-chave RETURN para retornar um valor, cujo tipo de dados é definido no momento da sua criação.
• Uma função deve retornar um valor ou lançar uma exceção; o retorno é obrigatório em funções.
• Uma função sem instruções DML pode ser chamada diretamente em uma consulta SELECT, enquanto uma função com DML só pode ser chamada a partir de outros blocos PL/SQL.
• Pode conter blocos aninhados ou estar aninhado dentro de outros blocos ou pacotes.
• Contém uma parte de declaração (opcional), uma parte de execução e uma parte de tratamento de exceções (opcional).
• Os valores podem ser passados ​​para a função ou obtidos dela através de parâmetros.
• Esses parâmetros devem ser incluídos na instrução de chamada.
• Uma função também pode retornar um valor através de parâmetros OUT, além de usar RETURN.
• Como sempre retorna um valor, a instrução que a chama sempre usa um operador de atribuição para preencher uma variável.

Sintaxe

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION instrui o compilador a criar uma nova função. 'OR REPLACE' instrui-o a substituir a função existente (se houver) pela função atual.
• O nome da função deve ser único.
• O tipo de dados RETURN deve ser mencionado.
• A palavra-chave 'IS' é usada quando a função está aninhada dentro de outro bloco. Se a função for independente, usa-se 'AS'.

Exemplo 1: Criando uma função e chamando-a usando um bloco anônimo. Neste programa, criamos uma função que recebe um nome como entrada e retorna uma mensagem de boas-vindas, utilizando um bloco anônimo e uma instrução SELECT para chamá-la.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Explicação:

• Code linha 1: Criando a função com o nome 'welcome_msg_func' e um parâmetro 'p_name' do tipo 'IN'.
• Code linha 2: Declarando o tipo de retorno como VARCHAR2.
• Code linha 5: Retorna o valor concatenado de 'Welcome' e o valor do parâmetro.
• Code linha 8: Bloco anônimo para chamar a função acima.
• Code linha 9: Declarar a variável com o mesmo tipo de dados que o tipo de retorno da função.
• Code linha 11: Chamando a função e armazenando o valor de retorno na variável 'lv_msg'.
• Code linha 12: Imprimindo o valor da variável. A saída é “Welcome” (Bem-vindo). Guru99".
• Code linha 14: Chamando a mesma função através de uma instrução SELECT. O valor de retorno é direcionado para a saída padrão.

Semelhanças entre um procedimento e uma função

• Ambos podem ser chamados de outros blocos PL/SQL.
• Se uma exceção lançada no subprograma não for tratada em seu tratamento de exceções na seção, ela se propaga para o bloco de chamada.
• Ambos podem ter quantos parâmetros forem necessários.
• Ambos são tratados como objetos de banco de dados em PL/SQL.

Procedimento versus função: principais diferenças

Funções integradas em PL/SQL

PL/SQL Contém diversas funções integradas para trabalhar com tipos de dados de string e data. Aqui, veremos as funções mais usadas e como utilizá-las.

Funções de conversão

Essas funções integradas convertem um tipo de dado em outro.

Funções de String

Essas funções são usadas no tipo de dados caractere.

Funções de Data

Essas funções são usadas para manipular datas.

Perguntas Frequentes