Stored Procedures no Informix | IMartins
Enviado por cesar em qua, 04/08/2010 - 00:36
Aqui irei demonstrar os recursos básicos de como criar e utilizar rotinas SPLs. Para uma refêrencia mais completa, consulte os manuais SQL Syntax, SQL Reference e SQL Tutorial.
Observação: Para aprender a utilizar SPL o melhor manual é o SQL Tutorial!
Sempre que eu mencionar SPL ou rotina aqui, estarei tratando de procedures e functions.
Procedure x Functions
Compilando uma rotina
SPL - Regras básicas
Comandos e funções uteis em SPLs
Rotinas com acesso a dados
Operador CURRENT
Permissões e owners
Permissões e DBA
Permissões e DDLs
Procedures e Functions também são conhecidas como UDR - User Defined Routine e UDF - User Defined Function no Informix.
A diferença entre estes dois tipos seguem o mesmo conceito de qualquer linguagem de programação, onde procedure não retorna valor e function retorna.
Porém apenas para manter compatibilidade com códigos escritos em versões anteriores do Informix uma procedure também retorna valores. Mas como boa prática, deve-se sempre declarar ela como function nestes casos.
Para quem já está acostumando a criar rotinas com o Informix deve estar estranhando este tópico, porque no Informix não existe um "processo de compilação" em si.
Toda rotina possui uma otimização de sua execução, esta otimização é nada mais que os planos de acessos de todos os Objetos e SQLs existentes em seu conteúdo. Quando falamos de compilar/recompilar uma rotina trata-se de recriar este plano de acesso.
Esta informação está na tabela de sistema chamada sysprocplan.
A otimização é automaticamente gerada na criação da rotina, execução de um UPDATE STATISTICS FOR PROCEDURE ou em sua própria execução se for detectado qualquer alteração na estrutura de algum objeto contido nela.
Existe um cuidado que se deve tomar durante uma compilação que é a configuração do PDQPRIORITY. Para maiores informações leia o tópico especifico sobre este assunto
Além da linguagem própria do Informix (SPL), uma procedure ou function pode ser escrita em C ou Java.
Quando uma rotina é escrita em C existe uma vantagem de performance em sua execução pois seu código já está compilado e otimizado.
Para escrever uma função em C ou Java é obrigatório utilizar as bibliotecas de API do Informix para vários recursos como alocar memória, acessar dados e etc. Tudo isso para manter o controle pelo engine do Informix.
Toda procedure começa com um CREATE PROCEDURE e finaliza com END PROCEDURE;
Toda function começa com um CREATE FUNCTION e finaliza com END FUNCTION;
Procedure pode retornar um ou mais tipo de dados, retornar mais de uma linha de dados (simulando uma tabela) ou não retornar nenhum dado.
Function segue a mesma regra da procedure, exceto pelo fato de que ela é obrigatória a retornar alguma informação, enquanto a procedure pode não retornar.
Todo comando deve finalizar com um ponto-e-virgula ";"
Todas as variáveis necessárias só podem ser declaradas no inicio de blocos, segundo o mesmo conceito da linguagem C .
A declaração explicita de bloco é através dos comandos BEGIN...END;
Na declaração de criação de uma rotina, já existe uma declaração implicita de bloco e não é necessário utilizar o BEGIN...END.
As variáveis são declaradas através do comando DEFINE
É possível declarar variáveis com o mesmo tipo de dado de uma coluna utilizando o operador LIKE.
É possível declarar variáveis globais que serão válidas durante toda a sessão do usuário e acessíveis por outras rotinas.
O nome das variáveis não são case-sensitive;
Os meios de definir o valor de variáveis é através do comando LET, SELECT...INTO, CALL, EXECUTE...INTO, FETCH ou FOREACH.
Toda variável deve ser inicializada antes de ser utilizada
Sempre que uma SPL é executada, ela é executada com a permissão do usuário que a executa.
É permitido a declaração de DML embedded no código (Select,Update,Delete,Insert,Merge), porém no caso de SELECTS eles devem retornar apenas uma unica linha.
Quando uma SPL é executada através de um comando DML (Select,Update,Delete,Insert,Merge), certos comandos são restritos em sua execucão. Para uma lista completa deles, consulte o manual
Para chamadas via EXECUTE PROCEDURE ou EXECUTE FUNCTION não há esta restrição.
Toda e qualquer refêrencia de nome de tabela, colunas e demais objetos são validados apenas no momento de execução da rotina e não em sua criação.
Pode-se utilizar o conceito de overload em rotinas, onde cria-se mais de uma rotina com o mesmo nome porém com diferente parâmetros.
Uma SPL pode ser acessada como se fosse uma tabela em um select.
Para maiores informações consulte este artigo
Uma SPL pode ser integrada com uma trigger facilitando o acesso aos dados alterados/acessados
É possível executar um comando de Sistema Operacional, porém este recurso deve ser utilizado com extremo cuidado para não afetar a estabilidade do banco de dados.
É possível tratar e gerar exceções através do comando EXCEPTION.
Os comandos específicos de SPL statement blocks são: << Label >> ,CALL ,CONTINUE ,EXIT ,FOR ,FOREACH ,GOTO ,IF ,LET ,LOOP ,RAISE EXCEPTION ,RETURN ,SYSTEM ,TRACE ,WHILE .
As rotinas com o nome SYSDBOPEN ou SYSDBCLOSE são especiais e executadas automaticamente quando um usuário abre e fecha uma sessão.
É possível criar rotinas especificas para o usuário, definindo o owner da rotina para ele, por exemplo: jsilva.sysdbopen, será executada apenas quando o usuário jsilva abrir uma sessão.
Para criar um rotina global, que vale para todos os usuários que nào possuem rotina definida, basta criar uma para o usuário public.
Em rotinas SPLs é possível tratar collections (SET, LIST, MULTISET) individualmentes, veja mais detalhes como neste artigo
Para execução de rotinas chamadas pelo comando EXECUTE é possível definir o nome da rotina dinamicamente.
let vRotina='sp_'||USER||'_abril';
execute procedure vRotina(param1, date, 'teste');
CREATE [DBA] [PROCEDURE|FUNCTION] <nome_rotina> ( <parametro1> <tipo>,
<parametro2> <tipo>, <parametroN> <tipo>,...)
[RETURNING <tipo1> [AS <nome>] [, <tipo2> ...] ]
[DOCUMENT <texto>]
[WITH LISTING IN <arquivo>];
Quase todos os comandos do Informix são executáveis em uma SPL, há alguns que são restritos para ESQL/C ou para outras ocasiões. Consulte o manual para identificar se o comando que você precisa é possível utilizar.
Aqui estou listando os comandos que consideros mais comuns e uteis.
Comando principais para tratamento de lógica
<< Label >> : Declaração de uma label
CALL : Executa uma rotina (similar a chamar o EXECUTE PROCEDURE ou EXECUTE FUNCTION)
CASE : Condição lógica CASE
CONTINUE : Pula para a próxima execução em comandos de loops (FOR, WHILE, LOOP, FOREACH)
DEFINE : Define uma variável
EXIT : Sai de um loop (FOR, WHILE, LOOP, FOREACH)
FOR : Loop FOR
GOTO : Muda o processamento para um << Label >>
IF : Condição IF
LET : Define o valor de uma variável
ON EXCEPTION : Tratamento de execeção
RAISE EXCEPTION : Gera uma execeção
RETURN : Finaliza a execução de uma rotina. Para que uma rotina retorne mais de uma linha deve-se utilizar a opção WITH RESUME neste comando.
SYSTEM : Executa um comando do Sistema Operacional
WHILE : Loop WHILE
Comandos para acesso a dados
PREPARE : Gera um statement para declarações de DML dinâmicos.
DECLARE : Declara um cursor apartir de um statement já preparado.
OPEN : Abre um curso já declarado.
Quando um SQL utiliza recurso de HOST VARIABLES (ponto de interrogação, "?"), o valor deste item deve ser especificado no comando OPEN .
FETCH : Faz o acesso aos dados apartir de um cursor declarado
CLOSE : Fecha um cursor
FREE : Libera os recursos de um cursor ou statement gerado por um declare ou prepare
FOREACH : Lê linha-a-linha o retorno de um SELECT processando o cada uma em seu loop.
Este comando é como um PREPARE+DECLEARE+OPEN+FECH+CLOSE+FREE integrado.
Opcionalmente pode-se declarar um nome de cursor para que internamente utilize o recurso UPDATE ... WHERE CURRENT OF.
EXECUTE IMMEDIATE: Execute um statement para declarações de DML dinâmicos .
SET DEBUG FILE : Define onde será gerado o arquivo de debug
TRACE : Inicia/para e gera informações de DEBUG
dbinfo('sqlca.sqlerrd2') : Retorna a quantidades de linhas que o ultimo DML executou
SQLCODE : Retorna o conteúdo do sqlca.sqlcode.
Util para identificar se não foi retornado mais nenhuam informação (0 = Sucess; 100 = Not Found; <0 = Error)
# Criação de uma rotina simples que faz apenas uma soma
CREATE FUNCTION soma (pValor1 DECIMAL, pValor2 DECIMAL)
RETURNING DECIMAL
RETURN pValor1 + pValor2;
'Esta rotina faz a soma de dois valores ',
'e foi criada apenas para demonstracao ',
'by Cesar Inacio Martins';
EXECUTE FUNCTION soma(1,2);
# Criação de uma rotina que possui um loop e uma pequena lógica
CREATE FUNCTION conta_char (pString CHAR(1000), pChar CHAR(1))
RETURNING INT as lower_char, INT as upper_char;
DEFINE vCountL INT;
DEFINE vCountU INT;
LET vCountL=0;
LET vCountU=0;
FOR i = 1 TO (length(pString))
IF substr(pString,i,1) = lower(pChar) THEN
LET vCountL = vCountl +1;
ELIF substr(pString,i,1) = upper(pChar) THEN
LET vCountU = vCountU +1;
RETURN vCountL, vCountU;
EXECUTE FUNCTION conta_char('laLeLilolu', 'l');
lower_char  upper_char
# Rotina utilizando um SQL embedded
CREATE FUNCTION Nome_Arquivo ()
RETURNING CHAR(100) AS arquivo
DEFINE vArq LIKE fs_full.nome_arquivo;;
select first 1 nome_arquivo INTO vArq
from fs_full;;
RETURN vArq;;
EXECUTE FUNCTION nome_arquivo();
arquivo  GLSAPI_4.5
# Aqui demonstro a utilização dos comandos de acesso aos dados
# onde criei duas funções que fazem exatamente o mesmo serviço
# porém utilizando diferente comandos
# Declara a função onde pode receber opcionalmente um parâmetro, caso
# o parâmetro não seja informado, será utilizado o valor padrão definido.
CREATE FUNCTION teste_p1( pParam CHAR(100) DEFAULT '%')
# Defino que função irá retornar um unico campo
RETURNING CHAR(20) AS tabname
# Declaro as variáveis logo no inicio
DEFINE vSql CHAR(500);
DEFINE vTab CHAR(20);
# Inicializo as variáveis, onde no caso da declaração do
# SQL estou definindo um HOST VARIABLE com o interrogação (?) que será
# subistiuido no momento da execução
LET vSql='select tabname from systables where tabname like ? ';
LET vTab='';
# Preparo o SQL e faço a declaração de cursor para o SQL preparado
PREPARE sts1 FROM vSql;
DECLARE cur1 CURSOR FOR sts1;
# Abro o cursor utilizando os parâmetro pParam como HOST VARIABLE
OPEN cur1 USING pParam;
# Inicio loop para o acesso ao dado e logo no primeiro comando
# já recupero valor da 1a linha do SQL com o comando FETCH, salvando o dado
# na variável vTab
FETCH cur1 INTO vTab ;
# Verifico através do SQLCODE se o FETCH anterior retornou algum dado.
# Se não retornar nenhum dado (=100) saio do loop.
IF SQLCODE = 100 THEN exit; END IF;
# Retorno o valor para a chamada da função com a opção WITH RESUME para que o
# processamento continue até o fim do SQL
RETURN vTab WITH RESUME;
# Fecho o cursor e libero os recursos do cursor e do statement.
# ATENÇÃO: este procedimento é de extrema importancia!
FREE cur1;
FREE sts1;
# Nesta rotina faço exatamente a mesma coisa que a rotina anterior
# porém de modo mais simplificado e sem o recurso de SQL DINAMICO porque com
# o comando FOREACH não é permitido especificar o SQL através de uma variável
CREATE FUNCTION teste_p2( pParam CHAR(100) DEFAULT '%') RETURNING CHAR(20) AS tabname
FOREACH select tabname into vTab
from systables where tabname like pParam
# Testo a 1a rotina
execute function teste_p1('%colum%') ;
# Testo a 2a rotina
execute function teste_p2('%colum%') ;
O operador CURRENT retorna um timestamp (data + hora) porém quando utilizado executado em SP ele retorna sempre o mesmo valor.
Este é um comportamento que sempre será assim porque sua implementação segue a a regra do ANSI/ISO que define justamente isso.
Como alternativa a esta limitação, pode-se utilizar o seguinte SQL :
SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) FROM sysmaster:sysshmvals
Dica: Para evitar escrever todo este SQL, pode-se criar uma rotina que retorne a data/hora atual.
Veja o exemplo do comportamento do CURRENT em uma rotina:
$ dbaccess teste4 proc1.sql
create procedure teste_time()
returning datetime year to fraction(5) as sysdate
, datetime year to fraction(5) as current
, datetime year to fraction(5) as utc
define vSys datetime year to fraction(5);
define vCur datetime year to fraction(5);
define vUtc datetime year to fraction(5);
select  sysdate, current,
( SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) FROM sysmaster:sysshmvals)
into vSys, vCur, vUtc
return vSys, vCur, vUtc with resume ;
system 'sleep 1.3';
system 'sleep 1.25';
system 'sleep 1.22';
# Compare os segundos entre as 3 colunas , veja que apenas a UTC retornou
# os segundos atualizados
$ echo 'execute procedure teste_time()' | dbaccess teste4
sysdate                   current                   utc
2010-08-03 16:11:11.17440 2010-08-03 16:11:11.17400 2010-08-03 16:11:11.00000
2010-08-03 16:11:11.17440 2010-08-03 16:11:11.17400 2010-08-03 16:11:12.00000
2010-08-03 16:11:11.17440 2010-08-03 16:11:11.17400 2010-08-03 16:11:13.00000
# Observação: Na configuração da instancia, o parâmetro USEOSTIME está ativado
# fazendo com que a fração de segundos seja retornada.
# Porém para a data/hora retornado pelo select não tem efeito.
$ onstat -c |grep ^USEOSTIME
Quando uma procedure é criada por um usuário ele é definido como o dono/owner desta rotina.
Quando um outro usuário executa esta rotina (supondo que ele tenha as devidas permissões de execução), as permissões que vale para acesso aos objetos é do usuário que está executando a rotina.
Sendo assim, se usuário A possui acesso apenas nas tabelas X e Y e ele execute uma rotina que acesse a tabela W irá ocorrer um erro de acesso a esta tabela W.
Na criação de rotinas é possível incluir o parâmetro DBA em sua declaração. Apenas usuários com permissões de DBA podem criar rotinas com este parâmetro.
Este parâmetro faz com que todo acesso feito internamente pela rotina seja feito com permissão de DBA, como se ele tivesse um grant de DBA.
Portanto, há um risco de segurança para rotinas criadas com esta declaração, principalmente porque por padrão o Informix cria as rotinas com permissão de execução para public, então se um DBA descuidado cria uma rotina com permissão de DBA e mantém as permissões padrões, qualquer usuário poderá executa-la. (para solucionar este problema, pesquise sobre NODEFDAC)
Há uma situação de segurança que pessoalmente considero um BUG (v11.50 xC6W2). Quando o usuário owner da rotina tem ou ganha grant de DBA todas rotinas que ele é owner passa a ter o comportamento de como se tivessem sido declarados com a palavra chave DBA. (pelo menos este comportamento não consta na documentação)
Quando uma rotina que não foi criada com a declaração DBA executa DDLs, ou seja, CREATE TABLES, CREATE INDEXES e etc, o owner destes objetos é o owner da rotina executada e não o usuário que está executando.
Sempre que uma rotina é recompilada, nela fica fixado o PDQPRIORITY em sua execução automaticamente.
Isto é um comportamento muito perigoso, pois se uma rotina muito pesada é recompilada com PDQPRIORITY igual a 100 ou outro valor alto, em sua execução ela pode afetar a performance do banco para os outros usuários.
É recomendado sempre zerar o PDQPRIORITY logo no inicio do código da rotina e então posteriormente defini-lo para um valor desejado, internamente no código, assim é sobrescrito qualquer definição feita no momento da recompilação.
Criar function retornando um table no Informix?
Enviado por Andre Milke em ter, 09/10/2012 - 11:51.
Olá, Existe a possibilidade de criar uma function retornando um table, no Informix? Similar ao sql server. Agradeço, André Milke
RES: Criar...
Enviado por cesar em qui, 11/10/2012 - 07:48.
Não sei como é sintaxe em especifico do comando no Sql Server , mas se entendi o que você quer a opção WITH RESUME deve lhe atender. Provavelmente não na mesma sintaxe.
Como testar atributo into?
Enviado por Anônimo em qua, 25/08/2010 - 16:11.
No foreach se o select vier vazio como posso testar valor da variavel da clausula into? Att. David
RE: Como testar atributo...
Enviado por cesar em qui, 26/08/2010 - 13:56.
Utilize a variável SQLCODE, leia mais no item "Comandos e funcoes uteis" acima...
Posso Postar o esse artigo no meu Blog
Enviado por David Vieira em seg, 16/08/2010 - 10:08.
Olá Cesar Inacio Martins, posso postar esse artigo no meu blog cursaki, fazendo colocando os créditos para você e referenciando o esse site? Um abraço, David Vieira