Instrução CREATE PROCEDURE

As instruções CREATE FUNCTION e CREATE PROCEDURE definem uma função ou um procedimento que pode ser chamado, geralmente denominado rotina.

SINTAXE

Notas:
  1. Se o tipo de rotina for FUNCTION, o indicador de direção (IN, OUT, INOUT) será opcional para cada parâmetro. No entanto, é uma boa prática de programação especificar um indicador de direção para todas as novas rotinas de qualquer tipo para finalidades de documentação.
  2. Quando a cláusula NAMESPACE ou NAME é utilizada, seu valor é implicitamente constante e do tipo CHARACTER. Para obter informações sobre a utilização de variáveis CONSTANT, consulte Instrução DECLARE.
  3. Se o tipo de rotina for FUNCTION, não é possível especificar um LANGUAGE de DATABASE.

Visão Geral

As instruções CREATE FUNCTION e CREATE PROCEDURE definem uma função ou um procedimento que pode ser chamado, geralmente denominado rotina.

Nota: Em versões anteriores do produto, CREATE FUNCTION e CREATE PROCEDURE tinham diferentes utilizações e diferentes recursos. No entanto, eles foram aprimorados para o ponto em que permanecem apenas algumas diferenças. As únicas maneiras nas quais as funções se diferem de procedimentos estão listadas nas notas 1 e 3 abaixo do diagrama de sintaxe.

As rotinas são úteis para a criação de blocos reutilizáveis de código que podem ser executados, várias vezes, independentemente. Elas podem ser implementadas como uma série de instruções ESQL, um método Java ou um procedimento armazenado do banco de dados. Essa flexibilidade significa que algumas das cláusulas no diagrama de sintaxe não são aplicáveis (ou permitidas) para todos os tipos de rotina.

Cada rotina possui um nome, que deve ser exclusivo no esquema ao qual ela pertence. Isso significa que os nomes de rotinas não podem ser sobrecarregados; se o intermediário detectar que uma rotina foi sobrecarregada, ele emitirá uma exceção.

A cláusula LANGUAGE especifica a linguagem em que o corpo da rotina é escrito. As opções são:
DATABASE
O procedimento é chamado como um procedimento armazenado do banco de dados.
ESQL
O procedimento é chamado como uma rotina ESQL.
JAVA
O procedimento é chamado como um método estático em uma classe Java.
Não Especificado
Se você não especificar a cláusula LANGUAGE a linguagem padrão será ESQL, a menos que você especifique a cláusula EXTERNAL NAME, em cujo caso a linguagem padrão será DATABASE.
Existem restrições quanto ao uso da cláusula LANGUAGE. Não utilize:
  • A opção ESQL com uma cláusula EXTERNAL NAME
  • As opções DATABASE ou JAVA sem uma cláusula EXTERNAL NAME
  • A opção DATABASE com um tipo de rotina de FUNCTION

Especifique o nome da rotina utilizando a cláusula RoutineName e os parâmetros da rotina utilizando a cláusula ParameterList. Se a cláusula LANGUAGE especificar ESQL, a rotina deverá ser implementada utilizando uma única instrução ESQL. Essa instrução será mais útil se for uma instrução composta (BEGIN ... END), uma vez que pode conter quantas instruções ESQL forem necessárias para desempenhar sua função.

Alternativamente, em vez de fornecer um corpo ESQL para a rotina, você pode especificar uma cláusula LANGUAGE que não seja ESQL. Isso permite utilizar a cláusula EXTERNAL NAME para fornecer uma referência ao corpo real da rotina, onde quer que esteja localizada externamente, para o intermediário. Para obter detalhes adicionais sobre como utilizar a cláusula EXTERNAL NAME, consulte Chamando Procedimentos Armazenados e Chamando uma Rotina Java.

As rotinas de qualquer tipo de LANGUAGE pode ter parâmetros IN, OUT e INOUT. Isso permite que o responsável pela chamada transmita vários valores para a rotina e receba vários valores atualizados de volta. Isso é um adicionamento a qualquer cláusula RETURNS que a rotina possa ter. A cláusula RETURNS permite que a rotina transmita de volta um valor para o responsável pela chamada.

As rotinas implementadas em diferentes linguagens possuem suas próprias restrições, documentadas a seguir, sobre quais tipos de dados podem ser transmitidos ou retornados. O tipo de dados do valor retornado deve corresponder ao tipo de dados do valor definido para ser retornado da rotina. Além disso, se uma rotina for definida para ter um valor de retorno, o responsável pela chamada da rotina não poderá ignorá-lo. Para obter detalhes adicionais, consulte a Instrução CALL.

As rotinas podem ser definidas em um módulo ou esquema. As rotinas definidas em um módulo são locais em escopo para o nó atual, o que significa que apenas o código pertencente a esse mesmo módulo (ou nó) pode chamá-las. Entretanto, as rotinas definidas no escopo do esquema podem ser chamadas por um dos seguintes:
  • Código no mesmo esquema.
  • Código em qualquer outro esquema, se uma das situações a seguir for aplicável:
    1. A cláusula PATH do outro esquema contém o caminho para a rotina chamada, ou
    2. A rotina chamada é chamada utilizando seu nome completo (que é seu nome, prefixado por seu nome de esquema, separado por um ponto).
Portanto, se você precisar chamar a mesma rotina em mais de um nó, defina-a em um esquema.

Para qualquer tipo de linguagem ou rotina, o método de chamada da rotina deve corresponder ao modo de declaração da rotina. Se a rotina tiver uma cláusula RETURNS, utilize a sintaxe de chamada FUNCTION ou uma instrução CALL com uma cláusula INTO. Por outro lado, se uma rotina não tiver uma cláusula RETURNS, você deverá utilizar uma instrução CALL sem uma cláusula INTO.

Direções de Parâmetros

Os parâmetros transmitidos para rotinas sempre possuem uma direção associada a eles. Essa direção pode ser uma destas:
IN
O valor do parâmetro não pode ser alterado pela rotina. Um valor NULL para o parâmetro é permitido e pode ser transmitido para a rotina.
OUT
Quando recebido pela rotina chamada, o parâmetro transmitido para a rotina sempre possui um valor NULL do tipo de dados correto. Isso ocorre independentemente de seu valor antes da chamada da rotina. A rotina tem permissão para alterar o valor do parâmetro.
INOUT
INOUT é um parâmetro IN e OUT. Ele transmite um valor para a rotina que pode ser alterado por ela. Um valor NULL para o parâmetro é permitido e pode ser transmitido para dentro e para fora da rotina.

Se o tipo de rotina for FUNCTION, o indicador de direção (IN, OUT, INOUT) será opcional para cada parâmetro. No entanto, é uma boa prática de programação especificar um indicador de direção para todas as novas rotinas de qualquer tipo para finalidades de documentação.

As variáveis ESQL que são declaradas como CONSTANT (ou referências a variáveis declaradas como CONSTANT) não têm permissão para ter a direção OUT ou INOUT.

Rotinas ESQL

As rotinas ESQL são escritas em ESQL e possuem uma cláusula LANGUAGE de ESQL. O corpo de uma rotina ESQL é geralmente uma instrução composta da forma BEGIN … END, contendo várias instruções para processar os parâmetros transmitidos para a rotina.

Exemplo 1 de ESQL

O exemplo a seguir mostra o mesmo procedimento que em Exemplo 1 de Rotina de Banco de Dados, mas implementado como uma rotina ESQL em vez de um procedimento armazenado. A sintaxe e os resultados de CALL desta rotina são iguais àqueles no Restrições em Rotinas Java.
CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
    OUT parm2  CHARACTER,
    INOUT parm3 CHARACTER )
  BEGIN
      SET parm2 = parm3;
      SET parm3 = parm1;
       END;

Exemplo 2 de ESQL

Este procedimento de exemplo mostra o uso recursivo de uma rotina ESQL. Ele analisa uma árvore, visitando todos os locais no ponto inicial especificado, e abaixo dele, e relata o que foi localizado:

SET OutputRoot.MQMD = InputRoot.MQMD;

    DECLARE answer CHARACTER;
    SET     answer = '';

    CALL navigate(InputRoot.XML, answer);
    SET OutputRoot.XML.Data.FieldNames = answer;


    CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER)
    BEGIN
    SET answer = answer || 'Reached Field... Type:'
    || CAST(FIELDTYPE(root) AS CHAR)||
        ': Name:' || FIELDNAME(root) || ': Value :' || root || ': ';

        DECLARE cursor REFERENCE TO root;
        MOVE cursor FIRSTCHILD;
        IF LASTMOVE(cursor) THEN
            SET answer = answer || 'Field has children... drilling down ';
           ELSE
            SET answer = answer || 'Listing siblings... ';
    END IF;

        WHILE LASTMOVE(cursor) DO
            CALL navigate(cursor, answer);
            MOVE cursor NEXTSIBLING;
    END WHILE;

        SET answer = answer || 'Finished siblings... Popping up ';
    END;

Ao receber a seguinte mensagem de entrada:

<Person>
  <Nome>John Smith</Nome>
  <Salary period='monthly' taxable='yes'>-1200</Salary>
    </Person>

o procedimento produz a seguinte saída, que foi formatada manualmente:

  Reached Field... Type:16777232: Name:XML: Value :: Field has children...
  drilling down
  Reached Field... Type:16777216: Name:Person: Value :: Field has children...
  drilling down
  Reached Field... Type:16777216: Name:Name:
  Value :John Smith: Field has children... drilling down
  Reached Field... Type:33554432: Name::
  Value :John Smith: Listing siblings... Finished siblings... Popping up
  Finished siblings... Popping up
  Reached Field... Type:16777216: Name:Salary:
  Value :-1200: Field has children... drilling down
  Reached Field... Type:50331648: Name:period:
  Value :monthly: Listing siblings... Finished siblings... Popping up
  Reached Field... Type:50331648: Name:taxable:
  Value :yes: Listing siblings... Finished siblings... Popping up
  Reached Field... Type:33554432: Name::
  Value :-1200: Listing siblings... Finished siblings... Popping up
  Finished siblings... Popping up
  Finished siblings... Popping up
    Finished siblings... Popping up

Rotinas Java

Uma rotina Java é implementada como um método Java e possui uma cláusula LANGUAGE de JAVA. Para rotinas Java, o ExternalRoutineName deve conter o nome da classe e o nome do método Java a ser chamado. Especifique o ExternalRoutineName como este:
 >>--"-- className---.---methodName--"--------------><
em que className identifica a classe que contém o método e methodName identifica o método a ser chamado. Se a classe fizer parte de um pacote, a parte do identificador de classe deve incluir o prefixo completo do pacote; por exemplo, "com.ibm.broker.test.MyClass.myMethod".

Para localizar a classe Java, o intermediário procura conforme descrito em Implementando Classes Java.

Qualquer método Java que você deseje invocar deve ter a seguinte assinatura básica:
public static <return-type> <method-name> (< 0 - N parameters>)

em que <return-type> deve estar na lista dos tipos de dados IN Java na tabela no Mapeamento de Tipo de Dados ESQL para Java (excluindo o tipo REFERENCE, que não é permitido como um valor de retorno) ou o tipo de dados Java void. Os tipos de dados do parâmetro também devem estar na tabela Mapeamento de Tipo de Dados ESQL para Java. Além disso, não é permitido que o método Java tenha uma cláusula exception throws em sua assinatura.

A assinatura do método Java deve corresponder à declaração da rotina ESQL do método. Você também deve observar as seguintes regras:
  • Assegure-se de que o nome do método Java, incluindo o nome da classe e quaisquer qualificadores de pacotes, corresponda ao EXTERNAL NAME do procedimento.
  • Se o tipo de retorno Java for void, não coloque uma cláusula RETURNS na definição ESQL da rotina. Por outro lado, se o tipo de retorno Java for not void, você deverá colocar uma cláusula RETURNS na definição da rotina ESQL.
  • Assegure-se de que o tipo e a direção de cada parâmetro corresponda à declaração ESQL, de acordo com as regras listadas na tabela em Mapeamento de Tipo de Dados ESQL para Java.
  • Assegure-se de que o tipo de retorno do método corresponda ao tipo de dados da cláusula RETURNS.
  • Coloque EXTERNAL NAME entre aspas porque ele deve conter pelo menos "class.method".
  • Se quiser chamar um método Java sobrecarregado, você deverá criar uma definição ESQL separada para cada método sobrecarregado e fornecer a cada definição ESQL um nome de rotina exclusivo.

Você pode utilizar a API Java UDN (User Defined Node) em seu método Java, desde que observe as restrições documentadas em Restrições em Rotinas Java. Para obter informações adicionais sobre como utilizar a API UDN, consulte Compilando um Nó Java Definido pelo Usuário.

Exemplo de Rotina Java 1

Esta rotina contém três parâmetros de direções diversas e retorna um inteiro, que é mapeado para um tipo de retorno Java de java.lang.Long.

CREATE FUNCTION  myProc1( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER )
 RETURNS INTEGER
 LANGUAGE JAVA
 EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod1";

É possível utilizar o seguinte ESQL para chamar myProc1:

CALL myProc1( intVar1, intVar2, intVar3) INTO intReturnVar3;
-- or
SET intReturnVar3 = myProc1( intVar1, intVar2, intVar3);

Exemplo de Rotina Java 2

Esta rotina contém três parâmetros de direções diversas e possui um tipo de retorno Java de void.

CREATE PROCEDURE myProc2( IN P1 INTEGER, OUT P2 INTEGER, INOUT P3 INTEGER )
 LANGUAGE JAVA
 EXTERNAL NAME "com.ibm.broker.test.MyClass.myMethod2";

Você deve utilizar o seguinte ESQL para chamar myProc2:

CALL myProc2(intVar1, intVar2, intVar3);

A classe Java a seguir fornece um método para cada um dos exemplos de Java anteriores:

package com.ibm.broker.test;

class MyClass {
public static Long myMethod1( Long P1, Long[] P2 Long[] P3) { ... }
public static void myMethod2( Long P2, Long[] P2 Long[] P3) { ... }

 /*  Quando qualquer um desses métodos é chamado:
    P1 pode ou não ser NULL (dependendo do valor de intVar1).
    P2[0] é sempre NULL (qualquer que seja o valor de intVar2).
    P3[0] pode ou não ser NULL (dependendo do valor de intVar3).
    Isso é igual às rotinas ESQL LANGUAGE.
    Quando esses métodos retornam:
         intVar1 é inalterado
         intVar2 pode ainda ser NULL ou pode ter sido alterado
         intVar3 pode conter o mesmo valor ou pode ter sido alterado.
     Isso é igual às rotinas ESQL LANGUAGE.
     
    Quando myMethod1 retorna: intReturnVar3 é NULL (se o
    método retornar NULL) ou contém o valor retornado pelo
    método.
 */
}

Mapeamento de Tipo de Dados ESQL para Java

A tabela a seguir resume os mapeamentos de ESQL para Java.
Notes:
  • Somente os wrappers escalares Java são transmitidos ao Java.
  • Os tipos escalares ESQL são mapeados para tipos de dados Java como wrappers de objetos, ou matrizes de wrappers de objetos, dependendo da direção do parâmetro de procedimento. Cada matriz de wrapper contém exatamente um elemento.
  • Wrappers de objetos escalares são utilizados para permitir que valores NULL sejam transmitidos para e a partir de métodos Java.
Tipos de Dados ESQL 1 Tipos de Dados Java IN Tipos de Dados Java INOUT e OUT
INTEGER, INT java.lang.Long java.lang.Long []
FLOAT java.lang.Double java.lang.Double[]
DECIMAL java.math.BigDecimal java.math.BigDecimal[]
CHARACTER, CHAR java.lang.String java.lang.String[]
BLOB byte[] byte[][]
BIT java.util.BitSet java.util.BitSet[]
DATE com.ibm.broker.plugin.MbDate com.ibm.broker.plugin.MbDate[]
TIME 2 com.ibm.broker.plugin.MbTime com.ibm.broker.plugin.MbTime[]
GMTTIME 2 com.ibm.broker.plugin.MbTime com.ibm.broker.plugin.MbTime[]
TIMESTAMP 2 com.ibm.broker.plugin.MbTimestamp com.ibm.broker.plugin.MbTimestamp[]
GMTTIMESTAMP 2 com.ibm.broker.plugin.MbTimestamp com.ibm.broker.plugin.MbTimestamp[]
INTERVAL Não Suportado Não Suportado
BOOLEAN java.lang.Boolean java.lang.Boolean[]
REFERENCE (para uma árvore de mensagens) 3 4 5 6 com.ibm.broker.plugin.MbElement com.ibm.broker.plugin.MbElement[] (Suportado para INOUT. Não suportado para OUT)
ROW Não Suportado Não Suportado
LIST Não Suportado Não Suportado
  1. Variáveis que são declaradas como sendo CONSTANT (ou referências a variáveis declaradas como CONSTANT) não têm permissão para ter a direção INOUT ou OUT.
  2. A definição de fuso horário na variável Java não é importante; você pode obter o fuso horário requerido no ESQL de saída.
  3. O parâmetro de referência não pode ser NULL quando transmitido para um método Java.
  4. A referência não pode ter a direção OUT quando transmitida para um método Java.
  5. Se um MbElement for transmitido de volta do Java para ESQL como um parâmetro INOUT, ele deverá apontar para um local na mesma árvore de mensagens que aquela apontada pelo MbElement transmitido para o método Java chamado.

    Por exemplo, se uma referência ESQL para OutputRoot.XML.Test for transmitida para um método Java como um MbElement INOUT, mas um MbElement diferente for transmitido de volta para o ESQL quando a chamada retornar, o elemento diferente também deverá apontar para algum lugar na árvore OutputRoot.

  6. Um MbElement não pode ser retornado de um método Java com a cláusula RETURNS, porque nenhuma rotina ESQL pode retornar uma referência. No entanto, um MbElement pode ser retornado como um parâmetro de direção INOUT, sujeito às condições descritas no ponto 5 acima.

Uma REFERENCE a uma variável escalar pode ser utilizada na CALL de um método Java, desde que o tipo de dados da variável ao qual a referência se refere corresponda ao tipo de dados correspondente na assinatura do programa Java.

Restrições em Rotinas Java

As restrições a seguir aplicam-se a rotinas Java chamadas do ESQL:
  • Você deve assegurar que o método Java seja seguro em encadeamento (reentrante).
  • As únicas conexões com o banco de dados permitidas são conexões JDBC tipo 4. Além disso, as operações de banco de dados não fazem parte de uma transação do intermediário; isso significa que elas não podem ser controladas por um coordenador de recursos externo (como seria o caso de um ambiente XA).
  • A API Java UDN (User Defined Node) deve ser utilizada somente pelo mesmo encadeamento que chamou o método Java.

    Você tem direito a efetuar spawn de encadeamentos em seu método. No entanto, os encadeamentos que sofrerem spawn não deverão utilizar APIs Java de plug-in e você deverá retornar o controle ao intermediário.

    Observe que todas as restrições que se aplicam à utilização da API UDN também se aplicam aos métodos Java chamados a partir de ESQL.

  • Os métodos Java chamados a partir de ESQL não devem utilizar a classe MbNode. Isso significa que eles não podem criar objetos do tipo MbNode ou chamar qualquer um dos métodos em um objeto MbNode existente.
  • Se quiser executar trabalho MQ ou JMS em um método Java chamado a partir de ESQL, você deverá seguir as orientações de UDN (User Defined Node) para executar trabalho MQ e JMS em um UDN.

Implementando Classes Java

É possível implementar suas classes Java em um arquivo JAR (Java Archive). Existem duas maneiras de implementar um arquivo JAR no intermediário:
  1. Incluindo-o no arquivo BAR (Broker Archive)

    Incluir seu arquivo JAR no arquivo BAR é o método mais eficiente e flexível de implementação no intermediário.

    Você pode incluir um arquivo JAR no arquivo BAR manualmente ou automaticamente utilizando as ferramentas. Através das ferramentas é a maneira mais simples de incluir um arquivo JAR em um arquivo BAR.

    Se as ferramentas localizarem a classe Java correta dentro de um projeto Java referido aberto no espaço de trabalho, elas automaticamente compilarão a classe Java em um arquivo JAR e a incluirão no arquivo BAR. Este é o mesmo procedimento que você segue para implementar um nó Java Compute dentro de um JAR, conforme descrito em Carregamento de Classe do Nó Definido pelo Usuário.

    Quando implementar um arquivo JAR a partir das ferramentas, uma reimplementação do arquivo BAR contendo o arquivo JAR faz as classes Java referidas serem recarregadas pelo fluxo que foi reimplementando; assim como parar e reiniciar um fluxo de mensagens que se refere a uma classe Java. Certifique-se de parar e reiniciar (ou reimplementar) todos os fluxos que se referem ao arquivo JAR que você deseja atualizar. Isto evita o problema de alguns fluxos serem executados com a versão antiga do arquivo JAR e outros fluxos executados com a nova versão.

    Observe que as ferramentas implementarão apenas um arquivo JAR; elas não implementarão um arquivo de classe Java independente.

  2. Colocando-o em qualquer um dos seguintes elementos:
    1. A pasta <Workpath>/shared-classes/ na máquina que está executando o intermediário
    2. Na variável de ambiente CLASSPATH na máquina que está executando o intermediário

    Este procedimento deve ser feito manualmente; não é possível utilizar as ferramentas.

    Neste método, a reimplementação do fluxo de mensagens não recarrega as classes Java referidas; nem pára e reinicia o fluxo de mensagens. A única maneira de recarregar as classes neste caso é parar e reiniciar o próprio intermediário.

Para ativar o intermediário para localizar uma classe Java, certifique-se de que ela esteja em um dos locais acima. Se o intermediário não puder localizar a classe especificada, ele emitirá uma exceção.

Apesar de você ter as opções mostradas acima ao implementar o arquivo JAR, permitir que as ferramentas implementem o arquivo BAR fornece a maior flexibilidade ao implementar novamente o arquivo JAR.

Rotinas de Banco de Dados

As Rotinas de Banco de Dados são rotinas implementadas como procedimentos armazenados do banco de dados. As rotinas de banco de dados possuem uma cláusula LANGUAGE de DATABASE e devem ter um tipo de rotina de PROCEDURE.

Ao escrever procedimentos armazenados, em linguagens como o C, você deve utilizar indicadores NULL para assegurar que seu procedimento possa processar os dados corretamente.

Embora as definições de banco de dados de um procedimento armazenado variem entre os bancos de dados, o mesmo não ocorre com o ESQL utilizado para chamá-las. Os nomes dados aos parâmetros no ESQL não precisam corresponder aos nomes que recebem no lado do banco de dados. Entretanto, o nome externo da rotina, incluindo qualquer especificação de pacote ou de contêiner, deve corresponder a seu nome definido no banco de dados.

A cláusula DYNAMIC RESULT SET é permitida apenas para rotinas de banco de dados. Isso será necessário apenas se um procedimento armazenado retornar um ou mais conjuntos de resultados. O parâmetro inteiro para essa cláusula deve ser 0 (zero) ou mais e especifica o número de conjuntos de resultados a serem retornados.

A cláusula RETURNS opcional será requerida se um procedimento armazenado retornar um único valor escalar.

A cláusula EXTERNAL NAME especifica o nome pelo qual o banco de dados conhece a rotina. Este pode ser um nome qualificado ou não-qualificado, em que o qualificador é o nome do esquema do banco de dados no qual o procedimento está definido. Se você não fornecer um nome de esquema, o nome do usuário da conexão com o banco de dados será utilizado como o esquema no qual o procedimento será localizado. Se o procedimento requerido não existir nesse esquema, você deverá fornecer um nome de esquema explícito, na definição da rotina ou na chamada (CALL) da rotina no tempo de execução. Para obter informações adicionais sobre como escolher dinamicamente o esquema que contém a rotina, consulte a Instrução CALL. Quando um nome qualificado é utilizado, é necessário colocá-lo entre aspas.

Uma rotina completa normalmente tem a forma:
 EXTERNAL NAME "mySchema.myProc";
Entretanto, se o procedimento pertencer a um pacote Oracle, o pacote será tratado como parte do nome do procedimento. Portanto, você deve fornecer um nome de esquema, bem como o nome do pacote, na forma:
EXTERNAL NAME "mySchema.myPackage.myProc";  

Isso permite que o nome do esquema, mas não do pacote, seja escolhido dinamicamente na instrução CALL.

Se o nome do procedimento contiver curingas SQL (que são o caractere porcentagem (%) e o caractere sublinhado (_)), o nome do procedimento será modificado pelo intermediário para incluir o caractere de escape do banco de dados imediatamente antes de cada caractere curinga. Isto assegura que o banco de dados receba os curingas como caracteres literais. Por exemplo, supondo que o caractere de escape do banco de dados seja uma barra invertida, a cláusula abaixo será modificada pelo intermediário para que "mySchema.Proc\_" seja transmitido para o banco de dados. ;
EXTERNAL NAME "mySchema.Proc_";  
Todos os procedimentos externos têm as seguintes restrições:
  • Um procedimento armazenado não pode ser sobrecarregado no lado do banco de dados. Um procedimento armazenado será considerado sobrecarregado se houver mais de um procedimento com o mesmo nome no mesmo esquema do banco de dados. Se o intermediário detectar que um procedimento foi sobrecarregado, ele produz uma exceção.
  • Os parâmetros não podem ser dos tipos de dados ESQL REFERENCE, ROW, LIST ou INTERVAL.
  • Os tipos definidos pelo usuário não podem ser utilizados como parâmetros ou como valores de retorno.

Exemplo 1 de Rotina de Banco de Dados

A seguir está uma definição ESQL simples de um procedimento armazenado que retorna um único valor escalar e um parâmetro OUT:

CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
	LANGUAGE DATABASE
	RETURNS INTEGER
	EXTERNAL NAME "myschema.myproc";

Utilize este ESQL para chamar a invocar a rotina myProc1:

/*utilizando a sintaxe de chamada de instrução CALL*/
CALL myProc1(intVar1, intVar2) INTO intReturnVar3;

/*ou utilizando a sintaxe de chamada de função*/
SET intReturnVar3 = myProc1(intVar1, intVar2);

Exemplo 2 de Rotina de Banco de Dados

O código ESQL a seguir demonstra como definir e chamar procedimentos armazenados DB2:

Definição ESQL:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
    OUT parm2  CHARACTER,
    INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

Para registrar este procedimento armazenado com o DB2, copie o script a seguir em um arquivo (por exemplo, test1.sql)

-- Procedimento Armazenado de Exemplos do DB2
DROP PROCEDURE dbSwapParms @
CREATE PROCEDURE dbSwapParms
( IN in_param CHAR(32),
    OUT out_param CHAR(32),
    INOUT inout_param CHAR(32))
LANGUAGE SQL
  BEGIN
    SET out_param = inout_param;
        SET inout_param = in_param;
END @
e execute:
db2 -td@ -vf test1.sql 
a partir do prompt de comandos do DB2.
Espere os seguintes resultados da execução desse código:
  • O valor do parâmetro IN não se altera (e não pode, por definição).
  • O valor do parâmetro OUT torna-se "World".
  • O valor do parâmetro INOUT é alterado para "Hello".

Exemplo 3 de Rotina de Banco de Dados

O código ESQL a seguir demonstra como definir e chamar procedimentos armazenados Oracle:

Definição ESQL:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
    OUT parm2  CHARACTER,
    INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

Para registrar este procedimento armazenado com o Oracle, copie o script a seguir em um arquivo (por exemplo, test1.sql)

CREATE OR REPLACE PROCEDURE dbSwapParms
( in_param IN VARCHAR2 ,
    out_param OUT VARCHAR2,
    inout_param IN OUT VARCHAR2 )
AS
  BEGIN
    out_param := inout_param;
    inout_param := in_param;
      END;
/
e execute:
sqlplus <ID_do_usuário>/<senha> @test1.sql
Espere os seguintes resultados da execução desse código:
  • O valor do parâmetro IN não se altera (e não pode, por definição).
  • O valor do parâmetro OUT torna-se "World".
  • O valor do parâmetro INOUT é alterado para "Hello".

Exemplo 4 de Rotina de Banco de Dados

O código ESQL a seguir demonstra como definir e chamar procedimentos armazenados SQL Server:

Definição ESQL:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
  INOUT parm2  CHARACTER,
    INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

Para registrar este procedimento armazenado com o SQLServer, copie o script a seguir em um arquivo (por exemplo, test1.sql)

-- Procedimento Armazenado de Exemplos do SQLServer
DROP PROCEDURE dbSwapParms
   go
CREATE PROCEDURE dbSwapParms
 @in_param     CHAR(32),
 @out_param    CHAR(32) OUT,
 @inout_param  CHAR(32) OUT
AS
  SET NOCOUNT ON
  SET @out_param   = @inout_param
  SET @inout_param = @in_param
   go
e execute:
isql -U<ID_do_usuário> -P<senha> -S<servidor> -d<origem_de_dados> -itest1.sql
Nota:
  1. O SQL Server considera parâmetros OUTPUT de procedimentos armazenados como parâmetros INPUT/OUTPUT.

    Se declará-los como parâmetros OUT em seu ESQL, você encontrará um erro de incompatibilidade de tipo no tempo de execução. Para evitar essa incompatibilidade, você deve declarar os parâmetros OUTPUT do SQl Server como INOUT em seu ESQL.

  2. Você deve utilizar a opção SET NOCOUNT ON, conforme mostrada no exemplo anterior, com Procedimentos Armazenados SQL pelos seguintes motivos:
    1. Para limitar a quantidade de dados retornados do SQLServer para o intermediário.
    2. Para permitir que os conjuntos de resultados sejam retornados corretamente.
Espere os seguintes resultados da execução desse código:
  • O valor do parâmetro IN não se altera (e não pode, por definição).
  • O valor do parâmetro OUT torna-se "World".
  • O valor do parâmetro INOUT é alterado para "Hello".

Exemplo 5 de Rotina de Banco de Dados

O seguinte código ESQL demonstra como definir e chamar os procedimentos armazenados pelo SYBASE:

Definição ESQL:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;

SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
  INOUT parm2  CHARACTER,
    INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;

Para registrar este procedimento armazenado com o SYBASE, copie o script a seguir em um arquivo (por exemplo, test1.sql)

-- Procedimento Armazenado de Exemplos
DROP PROCEDURE dbSwapParms
   go
CREATE PROCEDURE dbSwapParms
 @in_param     CHAR(32),
 @out_param    CHAR(32) OUT,
 @inout_param  CHAR(32) OUT
AS
  SET @out_param   = @inout_param
  SET @inout_param = @in_param
   go
e execute:
isql -U<ID_do_usuário> -P<senha> -S<servidor> -d<origem_de_dados> -itest1.sql
Nota: O SYBASE considera os parâmetros OUTPUT dos procedimentos armazenados como parâmetros INPUT/OUTPUT.

Se declará-los como parâmetros OUT em seu ESQL, você encontrará um erro de incompatibilidade de tipo no tempo de execução. Para evitar essa incompatibilidade, você deve declarar os parâmetros SYBASE OUTPUT como INOUT em seu ESQL.

Espere os seguintes resultados da execução desse código:
  • O valor do parâmetro IN não se altera (e não pode, por definição).
  • O valor do parâmetro OUT torna-se "World".
  • O valor do parâmetro INOUT é alterado para "Hello".

Exemplo 6 de Rotina de Banco de Dados

Este exemplo mostra como chamar um procedimento armazenado que retorna dois conjuntos de resultados, bem como um parâmetro de saída:

CREATE PROCEDURE myProc1 (IN P1 INT, OUT P2 INT)
  LANGUAGE DATABASE
  	DYNAMIC RESULT SETS 2
  	EXTERNAL NAME "myschema.myproc";

Utilize o ESQL a seguir para chamar myProc1:

/* utilizando uma referência de campo */
CALL myProc1(intVar1, intVar2, Environment.RetVal[], OutputRoot.XML.A[])/* utilizando uma variável de referência*/
CALL myProc1(intVar1, intVar2, myReferenceVariable.RetVal[], myRef2.B[])
Conceitos relacionados
Visão Geral do ESQL
Tarefas relacionadas
Desenvolvendo ESQL
Chamando Procedimentos Armazenados
Referências relacionadas
Diagramas de Sintaxe: Tipos Disponíveis
instruções ESQL
Instrução CALL
Tabela de Mapeamento de Tipo de Dados de ESQL para Java
Avisos | Marcas Registradas | Downloads | Biblioteca | Suporte | Feedback
Direitos Autorais IBM Corporation 1999, 2006 Última Atualização: 1 Sep 2006
ak04970_