Stored Procedures no Informix

Versão para impressãoEnviar para amigoVersão PDF

A linguagem de Stored Procedures no Informix é chamada de SPL - Stored Procedure Language e é uma linguagem bem facil de trabalhar, principalmente para quem já trabalhou com o Informix 4GL pois são muito parecidas.
Será a linguagem SPL que irei demonstrar neste artigo, digo isto porque no Informix uma rotina também pode ser criada apartir de um código C ou Java.

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.

  1. Procedure x Functions
  2. Compilando uma rotina
  3. Linguagens utilizadas
  4. SPL - Regras básicas
  5. Recursos disponíveis
  6. Sintaxe básica
    1. Comandos e funções uteis em SPLs
  7. Exemplos
    1. Rotinas básicas
    2. Rotinas com acesso a dados
  8. Cuidados a serem tomados
    1. Operador CURRENT
    2. Permissões e owners
    3. Permissões e DBA
    4. Permissões e DDLs
    5. PDQPRIORITY


Procedure x Functions


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.


Compilando uma rotina


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


Linguagens utilizadas


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.


SPL - Regras básicas


  • 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.


Recursos disponíveis


  • 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
    Para maiores informações consulte este artigo
  • É 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');
    


Sintaxe básica


    CREATE [DBA] [PROCEDURE|FUNCTION] <nome_rotina> ( <parametro1> <tipo>, 
      <parametro2> <tipo>, <parametroN> <tipo>,...) 
    [RETURNING <tipo1> [AS <nome>] [, <tipo2> ...] ]
      <codigo> 
    END PROCEDURE
    [DOCUMENT <texto>]
    [WITH LISTING IN <arquivo>]; 
    


Comandos e funções uteis em SPLs


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
  • LOOP : Loop
  • 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 .

Comandos para debug

  • SET DEBUG FILE : Define onde será gerado o arquivo de debug
  • TRACE : Inicia/para e gera informações de DEBUG

Comandos uteis

  • 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)


Exemplos


Rotinas básicas


# Criação de uma rotina simples que faz apenas uma soma
CREATE FUNCTION soma (pValor1 DECIMAL, pValor2 DECIMAL) 
RETURNING DECIMAL 
  RETURN pValor1 + pValor2; 
END FUNCTION
DOCUMENT 
  'Esta rotina faz a soma de dois valores ', 
  'e foi criada apenas para demonstracao ', 
  'by Cesar Inacio Martins';

Routine created.

EXECUTE FUNCTION soma(1,2);

    (expression) 
3,00000000000000

1 row(s) retrieved.


# 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 i INT; 
 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;
   END IF
 END FOR
 RETURN vCountL, vCountU;
END FUNCTION;

Routine created.

EXECUTE FUNCTION conta_char('laLeLilolu', 'l');

 lower_char  upper_char 
          3           2

1 row(s) retrieved.

# 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;;
END FUNCTION
;
Routine created.

EXECUTE FUNCTION nome_arquivo();

arquivo  GLSAPI_4.5
1 row(s) retrieved.



Rotinas com acesso a dados


# 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
  LOOP 
    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;
  END LOOP; 
# Fecho o cursor e libero os recursos do cursor e do statement.
# ATENÇÃO: este procedimento é de extrema importancia!
  CLOSE cur1; 
  FREE cur1;
  FREE sts1;
END FUNCTION

Routine created.

# 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
  DEFINE vSql CHAR(500);
  DEFINE vTab CHAR(20);
  FOREACH select tabname into vTab
    from systables where tabname like pParam
    RETURN vTab WITH RESUME;
  END FOREACH; 
END FUNCTION;

Routine created.

# Testo a 1a rotina
execute function teste_p1('%colum%') ;

tabname              
syscolumns          
sysproccolumns      

2 row(s) retrieved.

# Testo a 2a rotina
execute function teste_p2('%colum%') ;

tabname              
syscolumns          
sysproccolumns      

2 row(s) retrieved.


Cuidados a serem tomados


Operador CURRENT


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
      from sysmaster:sysdual; 
      return vSys, vCur, vUtc with resume ;
      system 'sleep 1.3';
    
      select  sysdate, current, 
        ( SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) FROM sysmaster:sysshmvals) 
        into vSys, vCur, vUtc
      from sysmaster:sysdual; 
      return vSys, vCur, vUtc with resume ;
      system 'sleep 1.25';
    
      select  sysdate, current, 
        ( SELECT DBINFO( 'UTC_TO_DATETIME', sh_curtime ) FROM sysmaster:sysshmvals) 
        into vSys, vCur, vUtc
      from sysmaster:sysdual; 
      return vSys, vCur, vUtc with resume ;
      system 'sleep 1.22';
    end procedure;
    Routine created.
    
    # Compare os segundos entre as 3 colunas , veja que apenas a UTC retornou
    # os segundos atualizados
    $ echo 'execute procedure teste_time()' | dbaccess teste4
    Database selected.
    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
    
    3 row(s) retrieved.
    Database closed.
    
    # 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
    USEOSTIME 1
    

Permissões e owners


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.

Permissões e DBA


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)

Permissões e DDLs


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.

PDQPRIORITY


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.

4.166665
Média: 4.2 (6 votos)
Sua avaliação: Nenhum

Criar function retornando um table no Informix?

Olá, Existe a possibilidade de criar uma function retornando um table, no Informix? Similar ao sql server. Agradeço, André Milke

RES: Criar...

Olá André ,
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?

No foreach se o select vier vazio como posso testar valor da variavel da clausula into? Att. David

RE: Como testar atributo...

Olá David,

Utilize a variável SQLCODE, leia mais no item "Comandos e funcoes uteis" acima...
César

Posso Postar o esse artigo no meu Blog

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

RE: Posso Postar o esse artigo no meu Blog

Olá David,
Pode sim!
Fico feliz por isso !
Abraço
César

Valeu!!! Cesar

Obrigado! Um abraço.

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.