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

Por: Fiona Brown Fiona Brown
Hours Atualização do

Neste tutorial, você verá a descrição detalhada de como criar e executar os blocos nomeados (procedimentos e funções).

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

Além disso, abordaremos as principais diferenças entre esses dois subprogramas. Além disso, vamos discutir o Oracle funções integradas.

Terminologias em subprogramas PL/SQL

Antes de aprendermos sobre os subprogramas PL/SQL, discutiremos as diversas terminologias que fazem parte desses subprogramas. Abaixo estão as terminologias que iremos discutir.

Parâmetro

O parâmetro é variável ou espaço reservado para qualquer valor válido Tipo de dados PL/SQL através do qual o subprograma PL/SQL troca os valores com o código principal. Este parâmetro permite dar entrada aos subprogramas e extrair destes subprogramas.

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

Com base em 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

  • Este parâmetro é usado para fornecer informações aos subprogramas.
  • É uma variável somente leitura dentro dos subprogramas. Seus valores não podem ser alterados dentro do subprograma.
  • Na instrução de chamada, esses parâmetros podem ser uma variável ou um valor literal ou uma expressão, por exemplo, pode ser uma expressão aritmética como '5*8' ou 'a/b' onde 'a' e 'b' são variáveis .
  • Por padrão, os parâmetros são do tipo IN.

Parâmetro FORA

  • Este parâmetro é usado para obter saída dos subprogramas.
  • É uma variável de leitura e gravação dentro dos subprogramas. Seus valores podem ser alterados dentro dos subprogramas.
  • Na instrução de chamada, esses parâmetros devem ser sempre uma variável para conter o valor dos subprogramas atuais.

Parâmetro IN OUT

  • Este parâmetro é usado tanto para fornecer entrada quanto para obter saída dos subprogramas.
  • É uma variável de leitura e gravação dentro dos subprogramas. Seus valores podem ser alterados dentro dos subprogramas.
  • Na instrução de chamada, esses parâmetros devem ser sempre uma variável para conter o valor dos subprogramas.

Estes tipos de parâmetros devem ser mencionados no momento da criação dos subprogramas.

RETORNO

RETURN é a palavra-chave que instrui o compilador a mudar o controle do subprograma para a instrução de chamada. No subprograma RETURN significa simplesmente que o controle precisa sair do subprograma. Assim que o controlador encontrar a palavra-chave RETURN no subprograma, o código após isso será ignorado.

Normalmente, o bloco pai ou principal chamará os subprogramas e então o controle mudará desses blocos pai para os subprogramas chamados. RETURN no subprograma retornará o controle ao bloco pai. No caso de funções, a instrução RETURN também retorna o valor. O tipo de dados deste valor é sempre mencionado no momento da declaração da função. O tipo de dados pode ser de qualquer tipo de dados PL/SQL válido.

O que é procedimento em PL/SQL?

A Procedimento em PL/SQL é uma unidade de subprograma que consiste em um grupo de instruções PL/SQL que podem ser chamadas pelo nome. Cada procedimento em PL/SQL tem seu próprio nome exclusivo pelo qual pode ser referido e chamado. Esta unidade de subprograma no Oracle banco de dados é armazenado como um objeto de banco de dados.

Nota: O subprograma nada mais é do que um procedimento e precisa ser criado manualmente conforme a necessidade. Uma vez criados, eles serão armazenados como objetos de banco de dados.

Abaixo estão as características da unidade do subprograma Procedure em PL/SQL:

  • Procedimentos são blocos independentes de um programa que podem ser armazenados no banco de dados.
  • A chamada a esses procedimentos PLSQL pode ser feita referindo-se ao seu nome, para executar as instruções PL/SQL.
  • É usado principalmente para executar um processo em PL/SQL.
  • Pode ter blocos aninhados ou pode ser definido e aninhado dentro de outros blocos ou pacotes.
  • Contém parte de declaração (opcional), parte de execução, parte de tratamento de exceções (opcional).
  • Os valores podem ser passados ​​para Oracle procedimento ou obtido do procedimento por meio de parâmetros.
  • Esses parâmetros devem ser incluídos na instrução de chamada.
  • Um procedimento em SQL pode ter uma instrução RETURN para retornar o controle ao bloco de chamada, mas não pode retornar nenhum valor por meio da instrução RETURN.
  • Os procedimentos não podem ser chamados diretamente das instruções SELECT. Eles podem ser chamados de outro bloco ou através da palavra-chave EXEC.

Sintaxe

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE instrui o compilador a criar um novo procedimento em Oracle. A palavra-chave 'OR REPLACE' instrui a compilação a substituir o procedimento existente (se houver) pelo atual.
  • O nome do procedimento deve ser exclusivo.
  • A palavra-chave 'IS' será usada quando o procedimento armazenado em Oracle está aninhado em alguns outros blocos. Se o procedimento for independente, 'AS' será usado. Além deste padrão de codificação, ambos têm o mesmo significado.

Exemplo 1: Criando Procedimento e chamando-o usando EXEC

Neste exemplo, vamos criar um Oracle procedimento que recebe o nome como entrada e imprime a mensagem de boas-vindas como saída. Usaremos o comando EXEC para chamar o procedimento.

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

Explicação do código:

  • Linha de código 1: Criando o procedimento com nome 'welcome_msg' e com um parâmetro 'p_name' do tipo 'IN'.
  • Linha de código 4: Imprimindo a mensagem de boas-vindas concatenando o nome de entrada.
  • O procedimento foi compilado com sucesso.
  • Linha de código 7: Chamando o procedimento usando o comando EXEC com o parâmetro 'Guru99'. O procedimento é executado e a mensagem é impressa como “Welcome Guru99”.

ARTIGOS RELACIONADOS

O que é Função?

Functions é um subprograma PL/SQL independente. Assim como o procedimento PL/SQL, as funções têm um nome exclusivo pelo qual podem ser referenciadas. Eles são armazenados como objetos de banco de dados PL/SQL. Abaixo estão algumas das características das funções.

  • Funções são blocos independentes usados ​​​​principalmente para fins de cálculo.
  • A função usa a palavra-chave RETURN para retornar o valor, e o tipo de dados deste é definido no momento da criação.
  • Uma Função deve retornar um valor ou gerar a exceção, ou seja, o retorno é obrigatório em funções.
  • A função sem instruções DML pode ser chamada diretamente na consulta SELECT, enquanto a função com operação DML só pode ser chamada a partir de outros blocos PL/SQL.
  • Pode ter blocos aninhados ou pode ser definido e aninhado dentro de outros blocos ou pacotes.
  • Contém parte de declaração (opcional), parte de execução, parte de tratamento de exceções (opcional).
  • Os valores podem ser passados ​​para a função ou obtidos do procedimento através dos parâmetros.
  • Esses parâmetros devem ser incluídos na instrução de chamada.
  • Uma função PLSQL também pode retornar o valor por meio de parâmetros OUT, exceto por meio de RETURN.
  • Como sempre retornará o valor, na instrução de chamada ele sempre acompanha o operador de atribuição para preencher as variáveis.

Funções em PL/SQL

Sintaxe

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl 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. A palavra-chave 'OR REPLACE' instrui o compilador a substituir a função existente (se houver) pela atual.
  • O nome da função deve ser exclusivo.
  • O tipo de dados RETURN deve ser mencionado.
  • A palavra-chave 'IS' será usada quando o procedimento estiver aninhado em alguns outros blocos. Se o procedimento for independente, 'AS' será usado. Além deste padrão de codificação, ambos têm o mesmo significado.

Exemplo 1: Criando Função e chamando-a usando Bloco Anônimo

Neste programa, vamos criar uma função que recebe o nome como entrada e retorna a mensagem de boas-vindas como saída. Usaremos bloco anônimo e instrução select para chamar a função.

Funções em PL/SQL

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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;

Explicação do código:

  • Linha de código 1: Criando o Oracle função com nome 'welcome_msg_func' e com um parâmetro 'p_name' do tipo 'IN'.
  • Linha de código 2: declarando o tipo de retorno como VARCHAR2
  • Linha de código 5: Retornando o valor concatenado 'Bem-vindo' e o valor do parâmetro.
  • Linha de código 8: Bloco anônimo para chamar a função acima.
  • Linha de código 9: Declarando a variável com tipo de dados igual ao tipo de dados de retorno da função.
  • Linha de código 11: Chamando a função e preenchendo o valor de retorno para a variável 'lv_msg'.
  • Linha de código 12: Imprimindo o valor da variável. O resultado que você obterá aqui é “Bem-vindo Guru99”
  • Linha de código 14: Chamando a mesma função por meio da instrução SELECT. O valor de retorno é direcionado diretamente para a saída padrão.

Semelhanças entre Procedimento e Função

  • Ambos podem ser chamados de outros blocos PL/SQL.
  • Se a exceção levantada no subprograma não for tratada no subprograma manipulação de exceção seção, então ele será propagado 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 vs. Função: Principais diferenças

Procedimento função
Usado principalmente para executar determinado processo Usado principalmente para realizar alguns cálculos
Não é possível chamar na instrução SELECT Uma função que não contém instruções DML pode ser chamada na instrução SELECT
Use o parâmetro OUT para retornar o valor Use RETURN para retornar o valor
Não é obrigatório devolver o valor É obrigatório devolver o valor
RETURN simplesmente sairá do controle do subprograma. RETURN sairá do controle do subprograma e também retornará o valor
O tipo de dados de retorno não será especificado no momento da criação O tipo de dados de retorno é obrigatório no momento da criação

Funções integradas em PL/SQL

PL/SQL contém várias funções integradas para trabalhar com strings e tipos de dados de data. Aqui veremos as funções comumente usadas e seu uso.

Funções de conversão

Essas funções integradas são usadas para converter um tipo de dados em outro tipo de dados.

Nome da Função Uso Exemplo
TO_CHAR Converte o outro tipo de dados em tipo de dados de caractere TO_CHAR(123);
TO_DATE (string, formato) Converte a string fornecida até a data. A string deve corresponder ao formato.

TO_DATE('2015-JAN-15', 'AAAA-SEG-DD');

saída: 1 / 15 / 2015
TO_NUMBER (texto, formato)

Converte o texto para o tipo numérico do formato fornecido.

Informat '9' denota o número de dígitos

 Selecione TO_NUMBER('1234′,'9999') de dual;

saída 1234

Selecione TO_NUMBER('1,234.45′,'9,999.99') de dual;

saída 1234

Funções de String

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

Nome da Função Uso Exemplo
INSTR(texto, string, início, ocorrência) Fornece a posição de um texto específico na string fornecida.

  • texto – String principal
  • string – texto que precisa ser pesquisado
  • start – posição inicial da pesquisa (opcional)
  • acordo – ocorrência da string pesquisada (opcional)
 Selecione INSTR('AEROPLANE','E',2,1) de dual

saída 2

Selecione INSTR('AEROPLANE','E',2,2) de dual

saída: 9 (2ºnd ocorrência de E)
SUBSTR (texto, início, comprimento) Fornece o valor da substring da string principal.

  • texto – string principal
  • início – posição inicial
  • comprimento – comprimento a ser substring
 selecione substr('avião',1,7) de dual

saída: aeropla
SUPERIOR (texto) Retorna as letras maiúsculas do texto fornecido Selecione superior('guru99') de dual;

saída:GURU99
INFERIOR (texto) Retorna as letras minúsculas do texto fornecido Selecione inferior ('AerOpLane') de dual;

saída: avião
INITCAP (texto) Retorna o texto fornecido com a letra inicial em maiúscula. Selecione ('guru99') de dual

saída: Guru99

Selecione ('minha história') em dual

saída: Minha história
COMPRIMENTO (texto) Retorna o comprimento da string fornecida Selecione COMPRIMENTO ('guru99') em dual;

saída 6
LPAD (texto, comprimento, pad_char) Preenche a string no lado esquerdo para o comprimento determinado (string total) com o caractere fornecido Selecione LPAD('guru99', 10, '$') de dual;

saída:$$$$guru99
RPAD (texto, comprimento, pad_char) Preenche a string no lado direito para o comprimento determinado (string total) com o caractere fornecido Selecione RPAD('guru99′,10,'-') de dual

saída:guru99—-
LTRIM (texto) Apara o espaço em branco inicial do texto Selecione LTRIM('Guru99') de dual;

saída: Guru99
RTRIM (texto) Apara o espaço em branco à direita do texto Selecione RTRIM('Guru99 ') de dual;

saída; Guru99

Funções de Data

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

Nome da Função Uso Exemplo
ADD_MONTHS (data, número de meses) Adiciona os meses fornecidos à data ADD_MONTH('2015-01-01',5);

saída: 05 / 01 / 2015
SISTEMA Retorna a data e hora atuais do servidor Selecione SYSDATE de duplo;

saída: 10/4/2015 2:11:43
TRUNC Arredondando a variável de data para o menor valor possível selecione sysdate, TRUNC(sysdate) de dual;

saída: 10/4/2015 2:12:39 10/4/2015
ROUND Arredonda a data para o limite mais próximo, superior ou inferior Selecione sysdate, ROUND(sysdate) de dual

saída: 10/4/2015 2:14:34 10/5/2015
MESES_BETWEEN Retorna o número de meses entre duas datas Selecione MONTHS_BETWEEN (sysdate+60, sysdate) de dual

saída 2

Resumo

Neste capítulo, aprendemos o seguinte.

  • Como criar Procedure e diferentes formas de chamá-la
  • Como criar Function e diferentes formas de chamá-la
  • Semelhanças e diferenças entre Procedimento e Função
  • Parâmetros e terminologias comuns de RETURN em subprogramas PL/SQL
  • Funções integradas comuns em Oracle PL/SQL

Você pode gostar: