Stored Procedures no Informix

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
7. Comandos e funções uteis em SPLs
8. Exemplos
9. Rotinas básicas
10. Rotinas com acesso a dados
11. Cuidados a serem tomados
12. Operador CURRENT
13. Permissões e owners
14. Permissões e DBA
15. Permissões e DDLs
16. PDQPRIORITY
17. 
    

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 functionnestes 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 omanual
  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]  (  ,    ,  ,...) [RETURNING  [AS ] [,  ...] ]   END PROCEDURE[DOCUMENT ][WITH LISTING IN ]; 

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 somaCREATE FUNCTION soma (pValor1 DECIMAL, pValor2 DECIMAL) RETURNING DECIMAL   RETURN pValor1 + pValor2; END FUNCTIONDOCUMENT   '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,000000000000001 row(s) retrieved.# Criação de uma rotina que possui um loop e uma pequena lógicaCREATE 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           21 row(s) retrieved.# Rotina utilizando um SQL embeddedCREATE 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.51 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 FUNCTIONRoutine 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ávelCREATE 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 rotinaexecute function teste_p1('%colum%') ;tabname              syscolumns          sysproccolumns      2 row(s) retrieved.# Testo a 2a rotinaexecute 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.sqlcreate procedure teste_time()   returning datetime year to fraction(5) as sysdate  , datetime year to fraction(5) as current  , datetime year to fraction(5) as utcdefine 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 teste4Database selected.sysdate                   current                   utc                       2010-08-03 16:11:11.17440 2010-08-03 16:11:11.17400 2010-08-03 16:11:11.000002010-08-03 16:11:11.17440 2010-08-03 16:11:11.17400 2010-08-03 16:11:12.000002010-08-03 16:11:11.17440 2010-08-03 16:11:11.17400 2010-08-03 16:11:13.000003 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 ^USEOSTIMEUSEOSTIME 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.

Autor: Cesar Inacio Martins