Guia para Manutenção de Functions e Procedures do Totali Commerce | Portal de Documentação

Programação, Modelo de Tarefa, Banco de Dados

Referenciado por: Debug | Dicas Técnicas | Predefinição:Guia de Programação Banco de Dados | Guia para Atualização de Metadados do Totali Commerce | Guia para Exportação para Checkout do Totali Commerce | Guia para Manutenção de Estrutura do Totali Commerce | Guia para Manutenção de Packages do Totali Commerce | Guia para Manutenção de Triggers do Totali Commerce | Guia para Manutenção de Views do Totali Commerce | Instalação PostgreSQL para Totali Commerce |

Guia de programação para linguagem de banco
Introdução
Padrões Gerais
Estrutura
Exportação para Checkout
Metadados
Views
Functions
Triggers
Packages

Esse artigo reúne os conhecimentos necessários para se criar e atualizar uma função/procedure de banco para o Totali Commerce.

Tutoriais

Como Criar uma Função

1. Criar função ou procedure na pasta ansi\function dos fontes do totalicommerce.
Se essa função tiver dependência de alguma outra, ela deverá ser criada em um nível mais alto em relação às suas dependências. Ex.: se a função "A" depende da função "B" que está no nível 1, então a função "A" deverá ser criada no nível 2.

Nota: Não há necessidade de conceder nenhum tipo de permissão para utilizar a nova função. Isso já é feito automaticamente na migração.

Nota: Se a função depender de uma view, ela deve ser criada nos diretórios de views, em hierarquia superior a da view necessária.

Alterar uma Função

1. Editar o fonte da função.

2. Em caso de alteração nos parâmetros, deve-se criar um passo de migração para PostgreSQL dropando a assinatura do comando original. Ex.: ao mudar a assinatura da função TrataCaracter de

%%<BEGINFUNCTION('TrataCaracter','VARCHAR2',['pCampo'],['VARCHAR2'],[''])>%%

para

%%<BEGINFUNCTION('TrataCaracter','VARCHAR2',['pCampo','pCampoNovo'],['VARCHAR2','VARCHAR2'],[''])>%%

É necessário criar um passo com drop antigo.

{$IFDEF PL/PGSQL} /*-- SEQ 0001 --*/ /*-- IGNORE ERRO --*/ /*-- Tarefa 80000 --*/ DROP FUNCTION TrataCaracter (VARCHAR) CASCADE; {$ENDIF}

3. Caso seja incluída uma nova dependência (função ou view), devemos rever a pasta que a função deve ficar. Se for necessário modificá-la devemos tomar cuidado para fazer da forma correta no SVN para não perdermos seu histórico.

Criar Job

Para criar um novo job (execução agendada) no Totali Commerce deve-se seguir as seguintes definições.

1. Criar uma função normal, mas que só execute se o sistema não estiver em manutenção [TT_IDF.MANUT="F"].

2. No mesmo arquivo, crie uma procedure sem parâmetros responsável por chamar a função e registrar o job.

3. No Oracle, é necessário incluir a chamada para a função no arquivo CRIAJOB.SQL.

SVN://totalicommerce/beta/ansi/view/job/CRIAJOB.SQL

Esse arquivo é executado na migração e agenda os jobs de acordo com um horário.

a) Incluir job no array c_Nome.

b) Incluir data de execução c_Hora de acordo com a posição da função em c_Nome.
Essa hora pode ser repetida. Podemos incluir duas funções leves para serem executadas na mesma hora.
Deve ser um período entre 0 (00:00) e 7 (07:00).

c) Incrementar looping FOR idx IN 1..13 LOOP.

4. No PostgreSQL, no artigo Instalação_PostgreSQL_para_Totali_Commerce existe uma referência para o anexo postgres_totali_82.zip.
Nesse anexo existe um arquivo exemplificando como devem ser os jobs.sh do sistema.
Nele, deve-se registrar a chamada para nova função após a chamada para as demais funções.

echo "select JobExemplo();" >> /usr/local/pgsql/bin/jobs.sql

5. Criar passo de migração para registrar o job na TT_JOB para garantir que caso seja esquecido de ser implantado, sistema acuse que o job não está rodando.
Segue abaixo um exemplo do passo de migração:

/*-- SEQ 0001 --*/ -- Tarefa 99999 -- %%<DISABLETRIGGER('TT_JOB')>%% /*-- SEQ 0001 --*/ -- Tarefa 99999 -- INSERT INTO TT_JOB (NOMJOB, DESJOB, ULTEXE, QTDERR, ULTERR) SELECT 'JobExemplo', 'Descrição da rotina do job', Agora(), 0, NULL FROM DUAL WHERE NOT EXISTS ( SELECT 1 AS OK FROM TT_JOB WHERE NOMJOB = 'JobExemplo' ); /*-- SEQ 0001 --*/ -- Tarefa 99999 -- %%<ENABLETRIGGER('TT_JOB')>%%

Modelos

Exemplo de função simples:
SVN://totalicommerce/modelos/ansi/Criar_Function.mac

Exemplo de procedure simples:
SVN://totalicommerce/modelos/ansi/Criar_Procedure.mac

Exemplo de função para job:
SVN://totalicommerce/modelos/ansi/Criar_Job.mac

Padrões

Seguir os padrões gerais definidos em Padrões Gerais para Programação de Banco do Totali Commerce e os específicos definidos neste artigo.

Nomenclatura

Usar <ação principal da função>:

AtualizaFinanceiro
ResgataItens
CalculaIPIItem
ProcessaRegistro
ValidaBase
Agora
AjustaTT_CLI

Para escolher um bom nome de função deve-se levar em consideração:

Usar nome claro, simples e objetivo.
Utilizar padrão CamelCase, ou seja, letras com iniciais maiúsculas e sem underline.

Nome do Arquivo

O nome do arquivo deve ser o nome da função:

CalculaImposto.mac
FormataTT_CLI.mac
CalculaIPIMercadoria.mac

Por muito tempo o padrão para o nome do arquivo foi usar uma abreviatura iniciada com FN para function e PR para procedure. Essa abreviatura continuava com as 3 primeiras letras das partes que compunham o nome do artefato. Ex.: FNATUFIN.mac para AtualizaFinanceiro.

Agora esse padrão mudou para utilizarmos no nome do arquivo o verdadeiro nome da função. Ex.: CFOPGeradorCredito.mac para CFOPGeradorCredito.

Conteúdo do Arquivo

Se a função for muito complexa e precisar de uma função auxiliar que só será utilizada por ela, podemos também incluí-la no arquivo. Nesse caso ela ficaria acima da função principal. É uma boa prática que todas as funções do arquivo tenha um prefixo igual para que fiquem melhor identificadas.

Cursores

O nome do cursor deve seguir padrão CamelCase.
Devemos declará-lo depois das variáveis.
O cursor deve obrigatoriamente ser fechado após utilização.
O fonte a ser processado dentro do LOOP do cursor não pode alterar registros trazidos pelo próprio cursor.

Mensagens de Exceção

Caso seja necessário fazer um tratamento de exceção que finalize a execução da rotina, utilizamos a macro RAISE.
Nela colocamos um código e uma mensagem.

%%<RAISE('-20000','Argumento inválido.', '')>%%

Código: Ele serve apenas para encontrarmos mais facilmente a mensagem. Como o Oracle só permite que usemos apenas valores na faixa de -20000 a -20999, devemos começar por -20000, depois -20001 e assim por diante.

Mensagem: Utilizar acentos e pontuações nas mensagens para escrever com ortografia correta.

Quando a mensagem estiver dentro de uma trigger ou função que manipula dados (que executam comandos DML como UPDATE, INSERT ou DELETE), devemos colocar no final da mensagem a constante COMPLEMENTO. Ela possui um texto padrão que indica que o processo foi interrompido.

Exemplo da declaração:

COMPLEMENTO CONSTANT VARCHAR2(254) := Constante('COMPLEMENTO');

Exemplo de utilização:

%%<RAISE('-20000','Base em manutenção. %', ['COMPLEMENTO'])>%%

Mensagens de Aviso: No PostgreSQL é possível gerar mensagens de aviso para poderem ser visualizados pelo PSQL através do comando RAISE NOTICE.
No artigo Debug de Objetos de Banco essa técnica é melhor explicada.

Técnicas

Estrutura Básica

BEGIN-END

O código-fonte que escrevemos para PL, seja uma função, uma procedure, uma trigger ou uma package, possui uma área para documentarmos o objetivo daquele objeto, opcionalmente parâmetros, declações de variáveis e cursores.

Em seguida vem o comando BEGIN que indica onde a função realmente será executada.
Todo comando BEGIN é encerrado com o comando END, mas que no nosso caso é criado automaticamente pelas macros que finalizam o objeto.

Podemos criar blocos BEGIN-END dentro das funções e triggers. A principal utilidade disso é utilizar o comando EXCEPTION que será abordado logo adiante.

RETURN

A execução das funções são encerradas pelo comando RETURN, não importa onde ele esteja no código.
Como não existem procedures no PostgreSQL, a macro ENDPROCEDURE criar um retorno falso fixo.

O RETURN em uma função pode ser uma boa saída para deixar o código PL mais limpo - com menos IFs aninhados.

Condições

IF

IF <condição> THEN [...] END IF;

Se a condição tiver uma comparação não requer parênteses. Exemplo:

IF i = 0 THEN [...] END IF;

Pode-se utilizar AND, OR, IN, NOT IN e NOT para incrementar as condições.

IF i = 0 AND x = 0 THEN [...] END IF;

IF cStr IN ('A','B','C') THEN [...] END IF;

IF cStr NOT IN ('A','B','C') THEN [...] END IF;

Ou até outras funções:

IF SUBSTR(pCODCFO,1,4) IN '5102' THEN [...] ELSIF SUBSTR(pCODCFO,1,4) IN '5403' THEN [...] ELSE [...] END IF;

CASE

Com o CASE podemos fazer testes a partir de uma variável.

CASE selector WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2; ... WHEN expressionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE;

Ou podemos programar condições independentes, onde a primeira válida será executada.

CASE WHEN search_condition1 THEN sequence_of_statements1; WHEN search_condition2 THEN sequence_of_statements2; ... WHEN search_conditionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE;

Repetições

LOOP

O LOOP é o comando mais básico para se fazer um looping em PL.

LOOP sequence_of_statements END LOOP;

Mas para que ele não fique executando para sempre devemos combinar seu uso com algumas condições para encerrar o laço.

EXIT

O comando EXIT faz sair do looping.

LOOP IF <condição> THEN EXIT; END IF; END LOOP;

FOR

Executa o LOOP por uma determinada quantidade de vezes.

FOR <contador> IN <INício>..<fim> LOOP sequence_of_statements END LOOP;

Cursores

Não existe o conceito do FOR EACH, atribuindo a uma variável a posição atual de uma lista ou select.
Mas podemos utilizar cursores que são bem parecidos.

Sem parâmetros de entrada

-- Declaração de variáveis e cursores %%<CURSORFOR('CursorExemplo')>%% SELECT COLUNA1, COLUNA2, COLUNA3 FROM TABELA; -- Início da função BEGIN
OPEN CursorExemplo; LOOP FETCH CursorExemplo INTO cCOLUNA1, cCOLUNA2, cCOLUNA3; EXIT WHEN %%<NOTFOUND('CursorExemplo')>%%; [...] END LOOP; CLOSE CursorExemplo;

Com parâmetros de entrada

-- Declaração de variáveis e cursores %%<CURSOR('CursorExemploComParametros', ['pCONDICAO1','pCONDICAO2'], ['TABELA.CONDICAO1%TYPE','TABELA.CONDICAO2%TYPE'])>%% SELECT COLUNA1, COLUNA2, COLUNA3 FROM TABELA WHERE CONDICAO1 = pCONDICAO1 AND CONDICAO2 = pCONDICAO2; -- Início da função BEGIN
OPEN CursorExemploComParametros(cCONDICAO1, cCONDICAO2); LOOP FETCH CursorExemploComParametros INTO cCOLUNA1, cCOLUNA2, cCOLUNA3; EXIT WHEN %%<NOTFOUND('CursorExemploComParametros')>%%; [...] END LOOP; CLOSE CursorExemploComParametros;

DICAProcure nos fontes pelas macros CURSOR ou CURSORFOR para mais exemplos.

Usar Variável de Registro como Retorno do Cursor

Uma informação muito útil para quando se está lidando com cursores é que podemos mover seu resultado para uma variável do tipo registro, não precisando declarar cada uma das variáveis de retorno do cursor.

-- Declaração de variáveis e cursores %%<CURSORFOR('Movto')>%% SELECT COLUNA1, COLUNA2, COLUNAn FROM TABELA; {$IFDEF PL/SQL} rMovto Movto%ROWTYPE; {$ENDIF} {$IFDEF PL/PGSQL} rMovto RECORD; {$ENDIF} -- Início da função BEGIN
OPEN Movto; LOOP FETCH Movto INTO rMovto; EXIT WHEN %%<NOTFOUND('Movto')>%%; -- cCOLUNA1 := rMovto.COLUNA1; -- cCOLUNA2 := rMovto.COLUNA2; -- cCOLUNAn := rMovto.COLUNAn; END LOOP; CLOSE Movto;

DICAA variável de registro precisa ser declarada depois do cursor. E as colunas do select do cursor precisam ter nome definidos.

Arrays

Em algumas circunstâncias poderemos precisar de arrays.
Temos alguns conjuntos de MACROS que servem para trabalharmos com arrays mais facilmente nas duas linguagens, já que a sintaxe é bem diferente nelas.

Array com vários atributos

Quando precisamos guardar uma lista que tenha mais de uma variável podemos utilizar as MACROS:

ARRAY_DECLARE: Declara o array.
ARRAY_ADD: Adiciona um registro no array.
ARRAY_FOREACH: Inicia o comando FOR para percorrer o array.
ARRAY_ENDFOR: Finaliza o comando FOR.
ARRAY_GET: Pega o valor na posição atual do array.
ARRAY_POS: Posiciona o array comparando o valor de uma de suas variáveis - ele para na primeira.
ARRAY_POSINDEX: Posiciona o array na posição indicada. Se for uma posição inválida, ao buscar com ARRAY_GET retornará NULL. A posição é iniciada em 1.

Precisamos inicialmente declarar o array na área das variáveis.
Por limitação da linguagem não podemos usar tipos baseados em colunas ou registros [XPTO%TYPE ou XPTO%ROWTYPE].

%%<ARRAY_DECLARE('aPrecos', ['cProduto','nPrecoV'], ['VARCHAR(40)','NUMBER(12,4)'])>%%

Podemos incluir valores fixos no array.

%%<ARRAY_ADD('aPrecos', ['cProduto','nPrecoV'], [STR('NOTEBOOK'),'2400'])>%%

Podemos incluir valores a partir de variáveis.

%%<ARRAY_ADD('aPrecos', ['cProduto','nPrecoV'], ['cDESMAT','nPRECOV'])>%%

Para percorrer o array combinamos o uso do FOREACH com ENDFOR.

%%<ARRAY_FOREACH('aPrecos')>%% [...] %%<ARRAY_ENDFOR('aPrecos')>%%

Também podemos posicionar por atributo.

%%<ARRAY_POS('aPrecos','cProduto',STR('NOTEBOOK'))>%%

Ou posicionar por índice.

%%<ARRAY_POSINDEX('aPrecos','1')>%%

Após posicionar o array, podemos pegar o valor de uma de suas variáveis.

%%<ARRAY_FOREACH('aPrecos')>%% nPRECOV := %%<ARRAY_GET('aPrecos','nPrecoV')>%%; %%<ARRAY_ENDFOR('aPrecos')>%%

DICAProcure nos fontes por "ARRAY_" para mais exemplos.

Array com um atributo

Antes que as macros definidas acima existissem, criávamos um array para cada variável que precisava ser guardada.
Usávamos para isso as macros INITARRAY e VALARRAY.
Agora seu uso é desencorajado, mesmo se o array só tenha uma variável, é preferível utilizar as macros acima porque tratam a declaração da variável.

Funções Básicas

Todas essas funções estão disponíveis para serem utilizadas tanto em Oracle como em PostgreSQL.

Conversão

Função	Sintaxe	Retorno	Propósito
TO_CHAR	TO_CHAR(NUMBER) TO_CHAR(NUMBER, VARCHAR) TO_CHAR(DATE) TO_CHAR(DATE, VARCHAR)	VARCHAR	Converte um número ou data em texto. Opcionalmente, pode-se utilizar máscara para definir como deve ficar o texto. Máscaras para conversão de números: 9 para números opcionais, D para casa decimal e 0 para números obrigatórios '99999' '990D00' Máscaras para data: 'DD/MM/YYYY' 'DD/MM/YYYY HH24:MI:SS' Documentação para PostgreSQL Documentação para Oracle
TO_DATE	TO_DATE(VARCHAR, VARCHAR)	DATE	Converte um texto para data de acordo com a máscara informada. Máscaras para data: 'DD/MM/YYYY' 'DD/MM/YYYY HH24:MI:SS' Documentação para PostgreSQL Documentação para Oracle
TO_NUMBER	TO_NUMBER(VARCHAR) TO_NUMBER(VARCHAR)	NUMBER	Converte texto em número. Máscaras para conversão de números: 9 para números opcionais, D para casa decimal e 0 para números obrigatórios '99999' '990D00' Função gera exceção se o texto passado por parâmetro for vazio ou não tiver números. Quando aplicamos essa conversão pelo SQLPLUS, o client Oracle leva em consideração dados regionais da máquina. É necessário cautela para saber qual será a marcação da casa decimal. Os seguintes comandos podem ajudar nisso. <span class="co1">-- Identificar qual e o caracter</span> <span class="kw1">SELECT</span> <span class="kw2">VALUE</span> <span class="kw1">FROM</span> nls_session_parameters <span class="kw1">WHERE</span> parameter <span class="sy0">=</span> <span class="st0">'NLS_NUMERIC_CHARACTERS'</span> <span class="sy0">/</span> <span class="co1">-- Mudar caracter para sessão</span> <span class="kw1">ALTER</span> session <span class="kw1">SET</span> NLS_NUMERIC_CHARACTERS<span class="sy0">=</span><span class="st0">'.,'</span><span class="sy0">;</span> <span class="co1">-- Mudar caracter apenas para a conversão</span> <span class="kw1">SELECT</span> <span class="kw2">TO_NUMBER</span><span class="br0">(</span><span class="st0">'24.99'</span><span class="sy0">,</span><span class="st0">'99D99'</span><span class="sy0">,</span><span class="st0">'nls_numeric_characters=.,'</span><span class="br0">)</span> <span class="kw2">VALUE</span> <span class="kw1">FROM</span> dual<span class="sy0">;</span> Documentação para PostgreSQL Documentação para Oracle

Comparação

Função	Sintaxe	Retorno	Propósito
COALESCE antiga NVL	COALESCE(NUMBER, NUMBER) COALESCE(VARCHAR, VARCHAR) COALESCE(DATE, DATE)	NUMBER, VARCHAR ou DATE respectivamente.	Retorna o segundo parâmetro caso o primeiro seja nulo. Não usamos mais NVL. Ela é convertida para COALESCE automaticamente na compilação da macro.
ISNOTNULL	ISNOTNULL(VARCHAR)	CHAR	Função retorna "T" ou "F" para indicar se o valor passado como parâmetro não é nulo. Serve para tratar a possibilidade do valor estar com '', que no PostgreSQL não é nulo, mas após a gravação, as triggers de nulo transformam o valor em nulo.
ISNULL	ISNULL(VARCHAR)	CHAR	Função retorna "T" ou "F" para indicar se o valor passado como parâmetro é nulo. Serve para tratar a possibilidade do valor estar com '', que no PostgreSQL não é nulo, mas após a gravação, as triggers de nulo transformam o valor em nulo.
NVLZ	NVLZ(NUMBER, NUMBER)	NUMBER	Retorna o segundo parâmetro caso o primeiro seja nulo ou 0.
PCTOTALI.Equal	PCTOTALI.Equal(NUMBER, NUMBER) PCTOTALI.Equal(VARCHAR, VARCHAR) PCTOTALI.Equal(DATE, DATE)	NUMBER	Compara variáveis NUMBER, VARCHAR ou DATE. Função trata a possibilidade das variáveis estarem nulas. No caso da comparação do VARCHAR, a função ainda faz TRIM dos valores. Retorna 1 se valores foram iguais, e 0 se forem diferentes.
PCTOTALI.MaxValue	PCTOTALI.MaxValue(NUMBER, NUMBER)	NUMBER	Compara qual é o maior entre dois números. Função NÃO trata a possibilidade das variáveis estarem nulas. Retorna -1 se os parâmetros forem iguais. Retorna 0 se primeiro for maior. Retorna 1 se segundo for maior.
PCTOTALI.MinValue	PCTOTALI.MinValue(NUMBER, NUMBER)	NUMBER	Compara qual é o menor entre dois números. Função NÃO trata a possibilidade das variáveis estarem nulas. Retorna -1 se os parâmetros forem iguais. Retorna 0 se primeiro for menor. Retorna 1 se segundo for menor.

Datas

Função	Sintaxe	Retorno	Propósito
ADD_DAY	ADD_DAY(DATE, NUMBER)	DATE	Soma dias a uma data. Se o segundo parâmetro for negativo, os dias serão subtraídos. O retorno é a data modificada.
ADD_SECOND	ADD_SECOND(DATE, NUMBER)	DATE	Soma segundos a uma data. Se o segundo parâmetro for negativo, os segundos serão subtraídos. O retorno é a data modificada.
Agora	Agora()	DATE	Retorna data e hora do banco.

Dados Agregados

Função	Sintaxe	Retorno	Propósito
AGREGATXT	AGREGATXT([VARCHAR])	VARCHAR	Função agrupadora que concatena o parâmetro de cada registro em uma string única. <span class="kw1">SELECT</span> AGREGATXT<span class="br0">(</span>LINHA<span class="br0">)</span> <span class="kw1">FROM</span> <span class="br0">(</span> <span class="kw1">SELECT</span> <span class="st0">'1'</span> <span class="kw1">AS</span> LINHA <span class="kw1">FROM</span> DUAL <span class="kw1">UNION</span> <span class="kw1">ALL</span> <span class="kw1">SELECT</span> <span class="st0">'2'</span> <span class="kw1">AS</span> LINHA <span class="kw1">FROM</span> DUAL<span class="br0">)</span> TAB
AVG	AVG([NUMBER])	NUMBER	Retorna a média dos valores avaliados.
MAX	MAX([NUMBER]) MAX([VARCHAR]) MAX([DATE])	NUMBER, VARCHAR ou DATE, respectivamente.	Retorna o maior valor dentre os valores avaliados. Ignora registros nulos. Mas pode retornar null se nenhum registro tiver valor. A comparação de VARCHAR se dá pelo código ASCII de cada letra.
MIN	MIN([NUMBER]) MIN([VARCHAR]) MIN([DATE])	NUMBER, VARCHAR ou DATE, respectivamente.	Retorna o menor valor dentre os valores avaliados. Ignora registros nulos. Mas pode retornar null se nenhum registro tiver valor. A comparação de VARCHAR se dá pelo código ASCII de cada letra.

Texto

Função	Sintaxe	Retorno	Propósito
CapsLock	CapsLock(VARCHAR)	VARCHAR	Retorna o parâmetro passado em caixa alta quando o banco for Oracle, e em caixa baixa quando o banco for PostgreSQL. Deve ser utilizada quando comparamos nomes de objetos do banco (tabela, colunas, funções) com um texto.
DescricaoDominio	DescricaoDominio(TT_DOM.CODARQ%TYPE, TT_DOM.NOMCPO%TYPE, VARCHAR)	TT_DOM.DESDOM%TYPE	Retorna a descrição de um domínio do metadados.
INITCAP	INITCAP(texto VARCHAR)	VARCHAR	Converte texto para que cada palavra inicie com caixa alta e as demais letras fiquem com caixa baixa.
limpa_especiais	limpa_especiais(VARCHAR)	VARCHAR	Remove acentos e outros caracteres especiais do texto.
LOWER	LOWER(texto VARCHAR)	VARCHAR	Converte texto para caixa baixa.
LPAD	LPAD(texto VARCHAR, tamanho NUMBER, preenchimento VARCHAR)	VARCHAR	Preenche o espaço a esquerda do texto para que o resultado fique do tamanho definido.
LTRIM	LTRIM(texto VARCHAR)	VARCHAR	Elimina os espaços a esquerda do texto.
RPAD	RPAD(texto VARCHAR, tamanho NUMBER, preenchimento VARCHAR)	VARCHAR	Preenche o espaço a direita do texto para que o resultado fique do tamanho definido.
Rtf2txt	Rtf2txt(VARCHAR)	VARCHAR	Limpa as marcações de RTF do texto.
RTRIM	RTRIM(texto VARCHAR)	VARCHAR	Elimina os espaços a direita do texto.
split_part	split_part(VARCHAR, VARCHAR, NUMBER)	VARCHAR	Função que divide a lista passada no primeiro parâmetro usando como delimitador o segundo parâmetro e retorna somente o item da lista informado no terceiro parâmetro. <span class="kw1">SELECT</span> split_part<span class="br0">(</span><span class="st0">'ABC;DEF;GHI'</span><span class="sy0">,</span><span class="st0">';'</span><span class="sy0">,</span><span class="nu0">2</span><span class="br0">)</span> <span class="kw1">FROM</span> dual <span class="co1">-- Retorna DEF</span>
split_part_null	split_part_null(VARCHAR, VARCHAR, NUMBER)	VARCHAR	Retorna split_part do texto e converte para nulo o retorno que esteja vazio.
Split_Part_Number	Split_Part_Number(VARCHAR, VARCHAR, NUMBER)	NUMBER	Retorna split_part do texto convertendo o resultado para numérico.
TRIM	TRIM(texto VARCHAR)	VARCHAR	Elimina os espaços em volta do texto.
UPPER	UPPER(texto VARCHAR)	VARCHAR	Converte texto para caixa alta.

Outras Funções

Função	Sintaxe	Retorno	Propósito
Constante	Constante(VARCHAR)	VARCHAR	Funções possui definição (chave e valor) das constantes utilizadas em funções e triggers. Retorna valor de constante.
TemFun	TemFun(TT_FUN.NOMFUN%TYPE)	CHAR	Função retorna "T" ou "F" para indicar se existe uma determinada funcionalidade na TT_FUN.

Exceções

Lançar uma Exceção

As regras para se lançar uma exceção estão definidas em Padrões_para_Programação_PL/SQL_e_PL/PGSQL#Mensagens_de_Exceção.

%%<RAISE('-20000','Argumento inválido.', '')>%%

Ser Específico nas Exceções

Ao usarmos exceções, devemos ser mais específicos possíveis para que a exceção não maquie algum erro não esperado.
Por isso, devemos evitar EXCEPTION no BEGIN principal da função.

-- Início da função
BEGIN [...] {$IFDEF PL/SQL} EXCEPTION WHEN OTHERS THEN RETURN 0; {$ENDIF}

Mensagem da Exceção

Nós podemos ter acesso a mensagem da exceção se for do nosso interesse apresentá-la ao usuário ao gravá-la em uma tabela.

BEGIN UPDATE TT_CLI SET CODEXT = cCODEXT WHERE CODEXT = cOldCODEXT; EXCEPTION WHEN OTHERS THEN cErro := SQLERRM; END;

Nesses casos, não precisamos mais utilizar tipos específicos de erros no PostgreSQL como era feito antigamente:

{$IFDEF PL/PGSQL} WHEN restrict_violation THEN [...]; WHEN not_null_violation THEN [...]; WHEN foreign_key_violation THEN [...]; WHEN unique_violation THEN [...]; WHEN check_violation THEN [...]; WHEN integrity_constraint_violation THEN [...]; WHEN plpgsql_error THEN [...]; {$ENDIF} {$IFDEF PL/SQL} WHEN OTHERS THEN [...]; {$ENDIF} END;

Exceção para Select Vazio em Oracle

Essa é a exceção mais comum. É tratada no item Dados Vazios Em Oracle logo adiante neste mesmo artigo.

Exceção para Division By Zero

Se precisarmos tratar a possibilidade de uma divisão por zero, mas o uso de COALESCE e NVLZ não ficarão bons, podemos usar essa exceção.

BEGIN nResultado := nValor / 0; EXCEPTION
{$IFDEF PL/SQL} WHEN ZERO_DIVIDE THEN nResultado := 0; {$ENDIF} {$IFDEF PL/PGSQL} WHEN DIVISION_BY_ZERO THEN nResultado := 0; {$ENDIF} END;

Tratamentos Programáticos

Função Simples

Exemplo completo de uma função bem simples.

%%<BEGINFUNCTION('CFOPGeradorCredito','VARCHAR', ['pCODCFO'], ['TD_CFO.CODCFO%TYPE'],[''])>%% -- Objetivo: -- 1. Verifica se o CFOP informado gera crédito. -- Declaração de variáveis e cursores nResultado VARCHAR(1); -- Início da função BEGIN IF SUBSTR(pCODCFO,1,4) IN ('1102','1113') THEN nResultado := 'T';
ELSE nResultado := 'F'; END IF; RETURN nResultado; %%<ENDFUNCTION('CFOPGeradorCredito','VARCHAR',[''],[''])>%%

Dados Vazios em Oracle

No Oracle existe uma situação que sempre precisamos tratar.
Se um SELECT dentro de um código PL/SQL retornar vazio, o Oracle gera uma exceção.

Temos duas formas de tratá-la. A primeira é a mais tradicional:

{$IFDEF PL/SQL} BEGIN {$ENDIF} SELECT '1', '2' INTO cCampo1, cCampo2 FROM DUAL WHERE 1=0; {$IFDEF PL/SQL} EXCEPTION WHEN NO_DATA_FOUND THEN cCampo1 := NULL; cCampo2 := NULL; END; {$ENDIF}

Mas ela pode causar a dúvida de que valor que o PostgreSQL vai atribuir para as variáveis não encontradas.
Além de poluir um pouco o código-fonte.

Existe uma alternativa, mas ela é limitada a SELECTs que retornam somente uma coluna:

SELECT (SELECT '1' FROM DUAL WHERE 1=0) INTO cCampo1 FROM DUAL;

Parâmetros com Valores Default (Sobrecarga de Métodos)

As vezes precisamos lidar com situação de que uma função é utilizada em diversos pontos do sistema, e precisamos criar um parâmetro a mais nela. Nós podemos fazer isso, mas precisamos entender alguns conceitos relacionados a isso.

Em Oracle existe valor default para um parâmetro. Então podemos simplesmente criar um novo parâmetro com valor default.

Em PostgreSQL não existe valor default, mas existe sobrecarga de métodos. Então criamos um parâmetro a mais na função, e criamos uma nova função que tenha os parâmetros originais, e que chame a nova função passam um valor fixo no novo parâmetro.

Temos macros para lidar com essa situação. A modificação entre os dois bancos é implementado através das macros BEGINFUNCTION-ENDFUNCTION e BEGINPROCEDURE-ENDPROCEDURE.

Função Simples:

%%<BEGINFUNCTION('InsertTR_SUG','VARCHAR', ['pDataIni','pDataFim','pAnoMes'], ['DATE','DATE','TR_SUG.ANO_MES%TYPE'],'')>%% %%<ENDFUNCTION('InsertTR_SUG','VARCHAR','','')>%%

Função com Parâmetros Opcionais:

%%<BEGINFUNCTION('TesteParametros','VARCHAR', ['pVarchar','pNull','pNumber'], ['VARCHAR','VARCHAR','NUMBER'], [STR('1'),'NULL',11])>%% BEGIN RETURN 'OK'; %%<ENDFUNCTION('TesteParametros','VARCHAR',[''],[STR('1'),'NULL',11])>%%

A conversão dessa macro gera uma função com a seguinte assinatura em Oracle:

CREATE OR REPLACE FUNCTION TesteParametros (pVarchar IN VARCHAR2 :='1', pNull IN VARCHAR2 :=NULL, pNumber IN NUMBER :=11)

E duas assinaturas diferentes em PostgreSQL:

CREATE OR REPLACE FUNCTION TesteParametros (VARCHAR,VARCHAR,Numeric) CREATE OR REPLACE FUNCTION TesteParametros ()

Variações de Parâmetros Opcionais:
Se for necessário criar novos parâmetros opcionais para uma função que já tenha parâmetros opcionais, o processo pode ficar um pouco confuso. Existem algumas variações da macro ENDFUNCTION que permitem fazer isso.

A seguinte tabela ajuda a entender melhor essas variações.

Macro	Chamadas em Oracle	Chamadas em PostgreSQL
<span class="co1">--Função sem parâmetros</span> <span class="sy0">%%<</span>BEGINFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">''</span><span class="sy0">,</span><span class="st0">''</span><span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="sy0">>%%</span> <span class="kw1">BEGIN</span> <span class="kw1">RETURN</span> <span class="st0">'OK'</span><span class="sy0">;</span> <span class="sy0">%%<</span>ENDFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">''</span><span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="sy0">>%%</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">--Função com parâmetro obrigatório</span> <span class="sy0">%%<</span>BEGINFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="br0">[</span><span class="st0">'pVarchar'</span><span class="br0">]</span><span class="sy0">,</span><span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="br0">]</span><span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="sy0">>%%</span> <span class="kw1">BEGIN</span> <span class="kw1">RETURN</span> <span class="st0">'OK'</span><span class="sy0">;</span> <span class="sy0">%%<</span>ENDFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">''</span><span class="sy0">,</span><span class="st0">''</span><span class="br0">)</span><span class="sy0">>%%</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">--Função com parâmetro opcional</span> <span class="sy0">%%<</span>BEGINFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="br0">[</span><span class="st0">'pVarchar'</span><span class="br0">]</span><span class="sy0">,</span><span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="br0">]</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="br0">)</span><span class="sy0">>%%</span> <span class="kw1">BEGIN</span> <span class="kw1">RETURN</span> <span class="st0">'OK'</span><span class="sy0">;</span> <span class="sy0">%%<</span>ENDFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">''</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="br0">)</span><span class="sy0">>%%</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">--Função com duas variações de parâmetros opcionais</span> <span class="sy0">%%<</span>BEGINFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'pVarchar'</span><span class="sy0">,</span><span class="st0">'pVarchar2'</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span>STR<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'B'</span><span class="br0">)</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">>%%</span> <span class="kw1">BEGIN</span> <span class="kw1">RETURN</span> <span class="st0">'OK'</span><span class="sy0">;</span> <span class="sy0">%%<</span>ENDFUNCTION2<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">''</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'$1'</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'B'</span><span class="br0">)</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">' '</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'$1'</span><span class="sy0">,</span><span class="st0">'$2'</span><span class="br0">]</span> <span class="br0">)</span><span class="sy0">>%%</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="sy0">,</span><span class="st0">'B'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="sy0">,</span><span class="st0">'B'</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>
<span class="co1">--Função com três variações de parâmetros opcionais</span> <span class="sy0">%%<</span>BEGINFUNCTION<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'pVarchar'</span><span class="sy0">,</span><span class="st0">'pVarchar2'</span><span class="sy0">,</span><span class="st0">'pVarchar3'</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span>STR<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'B'</span><span class="br0">)</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'C'</span><span class="br0">)</span><span class="br0">]</span><span class="br0">)</span><span class="sy0">>%%</span> <span class="kw1">BEGIN</span> <span class="kw1">RETURN</span> <span class="st0">'OK'</span><span class="sy0">;</span> <span class="sy0">%%<</span>ENDFUNCTION3<span class="br0">(</span><span class="st0">'Teste'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="sy0">,</span><span class="st0">'VARCHAR'</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'$1'</span><span class="sy0">,</span><span class="st0">'$2'</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'C'</span><span class="br0">)</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'VARCHAR'</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'$1'</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'B'</span><span class="br0">)</span><span class="sy0">,</span>STR<span class="br0">(</span><span class="st0">'C'</span><span class="br0">)</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">' '</span><span class="br0">]</span><span class="sy0">,</span> <span class="br0">[</span><span class="st0">'$1'</span><span class="sy0">,</span><span class="st0">'$2'</span><span class="sy0">,</span><span class="st0">'$3'</span><span class="br0">]</span> <span class="br0">)</span><span class="sy0">>%%</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="sy0">,</span><span class="st0">'B'</span><span class="sy0">,</span><span class="st0">'C'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="sy0">,</span><span class="st0">'B'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span> <span class="kw1">FROM</span> DUAL<span class="sy0">;</span>	<span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="sy0">,</span><span class="st0">'B'</span><span class="sy0">,</span><span class="st0">'C'</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="sy0">,</span><span class="st0">'B'</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="st0">'A'</span><span class="br0">)</span><span class="sy0">;</span> <span class="kw1">SELECT</span> TESTE<span class="br0">(</span><span class="br0">)</span><span class="sy0">;</span>

DICAÉ importante o detalhe do espaço " " passado na macro ENDFUNCTION2 para gerar a função sem parâmetros. Sem ele, o conversor não gera a função.

Função Estática

Função que retorna sempre o mesmo resultado se passado o mesmo parâmetro de entrada.
Esse tipo de função melhora o desempenho dos SELECTs, mas para uma função poder ser estática, ela realmente precisa sempre retornar o mesmo resultado.
Ou seja, não pode fazer consulta de dados.

É uma característica apenas do PostgreSQL.

%%<ENDFUNCTION_IMMUTABLE('FuncaoEstatica','DATE','','')>%%

Função Imutável

É possível criar uma função para índice, embora não seja muito recomendado.
Os efeitos colaterais podem ser problemas quando os dados relacionados ao índice são alterados.
De qualquer forma, fica documentada aqui a forma de fazê-lo.

%%<BEGINFUNCTION_INDEX('AnoMesTeste','CHAR',['pData'],['DATE'],[''])>%% BEGIN RETURN TO_CHAR(TRUNC(pData),'YYYY-MM'); %%<ENDFUNCTION_INDEX('AnoMesTeste','CHAR','','')>%%

E depois a criação do índice ficaria assim:

CREATE INDEX I_LC_FUN_ANO_MES ON TT_FUN (AnoMesTeste(DATFUN)) TABLESPACE TOTALI_INDEX;

LOOP alterando os dados

Nós não podemos percorrer os dados de um cursor e fazer edições no registro.
Se isso não puder ser feito com um simples UPDATE FROM SELECT, podemos fazer de duas formas:

Fazendo LOOP registro a registro (método lento, mas usado nas rotinas de importação).
Usando a macro ARRAY documentada anteriormente (método experimental).

LOOP registro a registro:

-- Declaração de variáveis e cursores %%<CURSOR('RegistrosParaEdicao', ['pSEQUEN'] ['TABELA.SEQUEN%TYPE'])>%% SELECT TAB.* FROM ( SELECT COLUNA1, COLUNA2, COLUNA3 FROM TABELA ORDER BY SEQUEN ) TAB {$IFDEF PL/SQL} WHERE ROWNUM = 1; {$ENDIF} {$IFDEF PL/PGSQL} LIMIT 1; {$ENDIF}

-- Início da função BEGIN LOOP SELECT MIN(SEQUEN) INTO cSEQUEN FROM TABELA;
OPEN RegistrosParaEdicao(cSEQUEN); FETCH RegistrosParaEdicao INTO cCOLUNA1, cCOLUNA2, cCOLUNA3; IF %%<NOTFOUND('RegistrosParaEdicao')>%% THEN CLOSE RegistrosParaEdicao; EXIT; END IF; [...] CLOSE RegistrosParaEdicao; END LOOP;

É interessante nessa técnica passar um parâmetro para o cursor para indicar qual será o próximo registro. Esse select deve ter índice para que a execução não fique muito lenta.

Usando macro ARRAY:

%%<ARRAY_DECLARE('aPrecos', ['cFilPre','cSequen','dDatInc','nPrecoV','nUltEla','iDiaEla'], ['CHAR(3)','CHAR(10)','DATE','NUMBER(12,4)','NUMBER(12,2)','NUMBER(4)'])>%% %%<CURSOR('LogPrecos', ['pDatIni'], ['TT_PRE.ATU_EM%TYPE'])>%% SELECT DISTINCT PRE.FILPRE, -- PK PRE.SEQUEN, -- PK PRE.DATINC, PRE.PRECOV, PRE.ULTELA, PRE.DIAELA FROM TT_PRE PRE INNER JOIN TL_PRE LPRE ON PRE.FILPRE = LPRE.FILPRE AND PRE.SEQUEN = LPRE.SEQPRE AND LPRE.PREANT IS NOT NULL WHERE PRE.ATU_EM >= pDatIni;

OPEN LogPrecos(dDatIni); LOOP FETCH LogPrecos INTO cFilPre, cSequen, dDatInc, nPrecoV, nUltEla, iDiaEla; EXIT WHEN %%<NOTFOUND('LogPrecos')>%%; %%<ARRAY_ADD('aPrecos', ['cFilPre','cSequen','dDatInc','nPrecoV','nUltEla','iDiaEla'], ['cFilPre','cSequen','dDatInc','nPrecoV','nUltEla','iDiaEla'])>%% END LOOP; CLOSE LogPrecos; %%<ARRAY_FOREACH('aPrecos')>%% cFilPre := %%<ARRAY_GET('aPrecos','cFilPre')>%%; cSequen := %%<ARRAY_GET('aPrecos','cSequen')>%%; dDatInc := %%<ARRAY_GET('aPrecos','dDatInc')>%%; nPrecoV := %%<ARRAY_GET('aPrecos','nPrecoV')>%%; nUltEla := %%<ARRAY_GET('aPrecos','nUltEla')>%%; iDiaEla := %%<ARRAY_GET('aPrecos','iDiaEla')>%%; UPDATE TT_PRE SET ULTELA = nUltEla, DIAELA = iDiaEla, ATU_EM = dAgora WHERE FILPRE = cFilPre AND SEQUEN = cSequen; {$IFDEF PL/SQL} COMMIT; {$ENDIF} %%<ARRAY_ENDFOR('aPrecos')>%%

Procedure

Devemos criar function sempre que for possível por questão de compatibilidade entre Oracle e PostgreSQL. Mas caso seja realmente necessário criar uma procedure ela seria assim:

%%<BEGINPROCEDURE('ProcedureSimples', ['pColuna'], ['TABELA.COLUNA%TYPE'],[''])>%% -- Objetivos: -- 1. Explicar o objetivo 1 aqui... -- 2. Explicar o objetivo 2 aqui... -- Declaração de variáveis e cursores -- Início BEGIN
%%<ENDPROCEDURE('ProcedureSimples',[''],[''])>%%

Parâmetros OUT

Por necessidade de utilização no Genexus e para facilitar a vida de parceiros, tentamos utilizar parâmetros de saída em procedures. No final das contas verificamos que só poderíamos fazer da seguinte forma:

Em Oracle podemos ter uma procedure que tenha algum dos seus parâmetros OUT.
Em PostgreSQL somente o retorno da função (último parâmetro) é OUT.

Não criamos uma MACRO específica para isso. Trtamos com IFDEF que cria procedure manualmente em Oracle e uma function em PostgreSQL, que poderia ser criada com a MACRO BEGINFUNCTION.

Exemplos:

ENVIA_DAV
GXSessionID

A forma mais clara para demonstrar a intenção seria fazer da seguinte forma:

{$IFDEF PL/SQL} /*-- SEQ 0001 --*/ CREATE OR REPLACE PROCEDURE GXSessionID (pRetorno OUT VARCHAR) IS BEGIN SELECT USERENV('SESSIONID') INTO pRetorno FROM DUAL; END; / {$ENDIF} {$IFDEF PL/PGSQL} /*-- SEQ 0001 --*/ /*-- IGNORE ERRO --*/ DROP FUNCTION GXSessionID(pRetorno OUT VARCHAR); /*-- SEQ 0001 --*/ CREATE FUNCTION GXSessionID(pRetorno OUT VARCHAR) AS $$ BEGIN SELECT USERENV('SESSIONID') INTO pRetorno FROM DUAL; END; $$ LANGUAGE plpgsql; {$ENDIF}

Updates Dinâmicos

É possível executar comandos DMLs dinâmicos dentro das funções e triggers. Para isso, temos o comando EXECUTE, que é demonstrado logo abaixo.

cComando := 'INSERT INTO TR_FIN '|| '(ORIGEM, CODFIL, SEQUEN, NUMPAR, SUBPAR, DATLAN, VLRLAN, VLRCRT, '|| ' FILCLI, CODCLI, FLGFLX, TIPLAN, FILCTA, SEQCTA, TIPCTA, ORICTA, '|| ' CODCTA, FILCRT, SEQCRT, TIPCRT, ORICRT, CODCRT, FILUSU, CODUSU, FILORI, FILDES, ATU_EM) '|| ' (SELECT RFIN.ORIGEM, RFIN.CODFIL, RFIN.SEQUEN, RFIN.NUMPAR, RFIN.SUBPAR, TRUNC(RFIN.DATLAN), RFIN.VLRLAN, RFIN.VLRCRT, '|| ' RFIN.FILCLI, RFIN.CODCLI, RFIN.FLGFLX, RFIN.TIPLAN, RFIN.FILCTA, RFIN.SEQCTA, RFIN.TIPCTA, RFIN.ORICTA, '|| ' RFIN.CODCTA, RFIN.FILCRT, RFIN.SEQCRT, RFIN.TIPCRT, RFIN.ORICRT, RFIN.CODCRT, RFIN.FILUSU, RFIN.CODUSU, '|| ' COALESCE((SELECT ORI.FILNUM FROM TT_CTA ORI WHERE ORI.CODFIL=RFIN.FILCTA AND ORI.SEQUEN=RFIN.SEQCTA AND ORI.TIPCTA=RFIN.TIPCTA),RFIN.FILORI) AS FILORI, '|| ' COALESCE((SELECT DES.FILNUM FROM TT_CTA DES WHERE DES.CODFIL=RFIN.FILCRT AND DES.SEQUEN=RFIN.SEQCRT AND DES.TIPCTA=RFIN.TIPCRT),RFIN.FILDES) AS FILDES, '|| ' RFIN.ATU_EM '|| ' FROM TV_RFIN_'||cOrigem||' RFIN '|| ' WHERE rfin.CODFIL = '||CHR(39)||cFilOri||CHR(39)|| ' AND rfin.SEQUEN = '||CHR(39)||cSeqOri||CHR(39)|| ' AND rfin.NUMPAR = '||CHR(39)||cParOri||CHR(39)|| ' AND RFIN.TIPLAN<>'||CHR(39)||'NAO'||CHR(39)|| ' AND RFIN.VLRLAN<>0'|| ' AND RFIN.FILCRT IS NOT NULL'|| ' )'; {$IFDEF PL/SQL} EXECUTE IMMEDIATE cComando; {$ENDIF} {$IFDEF PL/PGSQL} EXECUTE cComando; {$ENDIF}

DICAFunção AtualizaFinanceiro utiliza isso muito bem.

Execução Exclusiva (Singleton)

Quando é necessário que uma função execute de maneira exclusiva podemos fazer lock em um registro da TT_FUN.
Escolhemos a TT_FUN porque é uma tabela na maior parte do tempo usamos apenas para consulta, então esse processo não terá interferência com outras rotinas.
É comum precisarmos rodar a função de maneira exclusiva quando ela é um processo chamado várias vezes e que dependa de um conjunto de objetos para atualizar outros.
O objetivo é que duas instâncias da função concorram pelos mesmos registros e fiquem em DEAD LOCK.

O padrão é criarmos uma nova funcionalidade para cada lock:

Funcionalidade	Função
LOCK_ROM	VINCULA_ROMANEIO
LOCK_SAL	AjustaEstoqueLegal
LOCK_SEP	CRIA_SEPARACAO_FULL

cLock TT_FUN.NOMFUN%TYPE; [...] -- Garantia de exclusividade durante o processo SELECT NOMFUN INTO cLock FROM TT_FUN WHERE NOMFUN = 'LOCK_NAME' FOR UPDATE{$IFDEF PL/SQL} OF NOMFUN{$ENDIF};