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, cobriremos as principais diferenças entre esses dois subprogramas. Além disso, discutiremos as funções integradas do Oracle.
Neste tutorial do Oracle Stored Procedure, você aprenderá-
- Terminologias em subprogramas PL / SQL
- O que é procedimento em PL / SQL?
- O que é função?
- Semelhanças entre procedimento e função
- Procedimento vs. Função: Principais diferenças
- Funções integradas em PL / SQL
Terminologias em subprogramas PL / SQL
Antes de aprendermos sobre os subprogramas PL / SQL, discutiremos as várias terminologias que fazem parte desses subprogramas. Abaixo estão as terminologias que iremos discutir.
Parâmetro:
O parâmetro é uma variável ou espaço reservado para qualquer tipo de dados PL / SQL válido por meio do qual o subprograma PL / SQL troca os valores com o código principal. Este parâmetro permite fornecer dados aos subprogramas e extrair deles.
- Esses parâmetros devem ser definidos junto 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 os mesmos.
- 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 seus parâmetros de propósito são classificados como
- Parâmetro IN
- Parâmetro OUT
- Parâmetro IN OUT
Parâmetro IN:
- Este parâmetro é usado para fornecer entrada para os 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 a 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 OUT:
- Este parâmetro é usado para obter a 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 dados quanto para obter resultados 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.
Este tipo de parâmetro deve ser mencionado no momento da criação dos subprogramas.
RETORNA
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, em seguida, o controle mudará desses blocos pai para os subprogramas chamados. RETURN no subprograma retornará o controle de volta ao bloco pai. No caso das 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?
Um procedimento em PL / SQL é uma unidade de subprograma que consiste em um grupo de instruções PL / SQL que podem ser chamadas por nome. Cada procedimento em PL / SQL tem seu próprio nome exclusivo pelo qual pode ser referido e chamado. Esta unidade de subprograma no banco de dados Oracle é armazenada como um objeto de banco de dados.
Observação: o subprograma nada mais é do que um procedimento e precisa ser criado manualmente de acordo com o requisito. Depois de criados, eles serão armazenados como objetos de banco de dados.
Abaixo estão as características da unidade de subprograma de procedimento em PL / SQL:
- Os procedimentos são blocos autônomos 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.
- Ele pode ter blocos aninhados ou pode ser definido e aninhado dentro de outros blocos ou pacotes.
- Ele contém parte da declaração (opcional), parte da execução, parte do tratamento de exceções (opcional).
- Os valores podem ser passados para o procedimento Oracle ou obtidos 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 de instruções SELECT. Eles podem ser chamados de outro bloco ou por meio da palavra-chave EXEC.
Sintaxe:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE instrui o compilador a criar um novo procedimento no 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 no Oracle 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 procedimento e chamando-o usando EXEC
Neste exemplo, vamos criar um procedimento Oracle que leva o nome como entrada e imprime a mensagem de boas-vindas como saída. Vamos usar o comando EXEC para chamar o procedimento.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_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 o nome 'welcome_msg' e com um parâmetro 'p_name' do tipo 'IN'.
- Linha de código 4 : imprimir a mensagem de boas-vindas concatenando o nome de entrada.
- O procedimento foi compilado com sucesso.
- Linha de código 7 : Chamar o procedimento usando o comando EXEC com o parâmetro 'Guru99'. O procedimento é executado e a mensagem é impressa como "Welcome Guru99".
O que é função?
Funções é um subprograma PL / SQL autônomo. 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.
- As funções são um bloco autônomo usado principalmente para fins de cálculo.
- A função usa a palavra-chave RETURN para retornar o valor, e o tipo de dados disso é 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.
- Ele pode ter blocos aninhados ou pode ser definido e aninhado dentro de outros blocos ou pacotes.
- Ele contém parte da declaração (opcional), parte da execução, parte do tratamento de exceções (opcional).
- Os valores podem ser passados para a função ou buscados no procedimento por meio 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 diferentes de RETURN.
- Uma vez que sempre retornará o valor, na instrução de chamada, ele sempre acompanha o operador de atribuição para preencher as variáveis.
Sintaxe
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION 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 for 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. Vamos usar o bloco anônimo e a instrução select para chamar a função.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 a função Oracle com o 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 'Welcome' 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 o mesmo tipo de dados que o tipo de dados de retorno da função.
- Linha de código 11 : Chamar a função e preencher o valor de retorno para a variável 'lv_msg'.
- Linha de código 12 : Imprimindo o valor da variável. A saída que você obterá aqui é "Welcome 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 gerada no subprograma não for tratada na seção de tratamento de exceções do subprograma, ela se propagará 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 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Funções integradas em PL / SQL
PL / SQL contém várias funções integradas para trabalhar com strings e tipo 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 para a data. A string deve corresponder ao formato. | TO_DATE ('2015-JAN-15', 'AAAA-MON-DD'); Saída: 15/01/2015 |
| TO_NUMBER (texto, formato) | Converte o texto em tipo de número 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; Produto: 1234 |
Funções de String
Essas são as funções usadas no tipo de dados do caractere.
| Nome da Função | Uso | Exemplo |
|---|---|---|
| INSTR (texto, string, início, ocorrência) | Dá a posição de um texto específico na string fornecida.
| Selecione INSTR ('AVIÃO', 'E', 2,1) da Saída dupla : 2 Selecione INSTR ('AVIÃO', 'E', 2,2) da Saída dupla: 9 ( 2ª ocorrência de E) |
| SUBSTR (texto, início, comprimento) | Fornece o valor da substring da string principal.
| selecione substr ('avião', 1,7) de saída dupla : 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 ('avião') de duplo; Saída : avião |
| INITCAP (texto) | Retorna o texto fornecido com a letra inicial em maiúsculas. | Selecione ('guru99') da Saída dupla : Guru99 Selecione ('minha história') da Saída dupla : Minha história |
| LENGTH (texto) | Retorna o comprimento da string dada | Selecione LENGTH ('guru99') de dual; Produto : 6 |
| LPAD (texto, comprimento, pad_char) | Preenche a string no lado esquerdo para o comprimento fornecido (string total) com o caractere fornecido | Selecione LPAD ('guru99', 10, '$') de dual; Resultado : $$$$ guru99 |
| RPAD (texto, comprimento, pad_char) | Preenche a string no lado direito para o comprimento fornecido (string total) com o caractere fornecido | Selecione RPAD ('guru99', 10, '-') da Saída dupla : guru99 ---- |
| LTRIM (texto) | Corta o espaço em branco à esquerda do texto | Selecione LTRIM ('Guru99') de dual; Resultado : Guru99 |
| RTRIM (texto) | Corta o espaço em branco à direita do texto | Selecione RTRIM ('Guru99') de dual; Saída ; Guru99 |
Funções de data
Essas são funções usadas para manipular datas.
| Nome da Função | Uso | Exemplo |
|---|---|---|
| ADD_MONTHS (data, nº de meses) | Adiciona os meses indicados à data | ADD_MONTH ('2015-01-01', 5); Saída : 01/05/2015 |
| SYSDATE | Retorna a data e hora atuais do servidor | Selecione SYSDATE em dual; Saída : 04/10/2015 14:11:43 PM |
| TRUNC | Arredondar a variável de data para o menor valor possível | selecione sysdate, TRUNC (sysdate) de dual; Saída : 04/10/2015 2:12:39 PM 04/10/2015 |
| REDONDO | Arredonda a data para o limite mais próximo, superior ou inferior | Selecione sysdate, ROUND (sysdate) da saída dupla : 04/10/2015 14:14:34 PM 05/10/2015 |
| MONTHS_BETWEEN | Retorna o número de meses entre duas datas | Selecione MONTHS_BETWEEN (sysdate + 60, sysdate) da Saída dupla : 2 |
Resumo
Neste capítulo, aprendemos o seguinte.
- Como criar Procedimento e diferentes formas de chamá-lo
- Como criar Função e diferentes maneiras de chamá-la
- Semelhanças e diferenças entre procedimento e função
- Parâmetros e terminologias comuns RETURN em subprogramas PL / SQL
- Funções integradas comuns no Oracle PL / SQL