Instrução CREATE PROCEDURE

A instrução CREATE PROCEDURE define um procedimento, que é uma sub-rotina que não possui nenhum valor de retorno.

Sintaxe

Notas:
  1. Quando as cláusulas NAMESPACE e NAME são utilizadas, os valores são implicitamente constantes e do tipo CHARACTER (ou CHAR).

Para obter informações sobre o uso de instruções CONSTANT, consulte Instrução DECLARE.

Forneça o nome do procedimento utilizando ProcedureName e os parâmetros do procedimento utilizando ParameterList. O procedimento é implementado utilizando a instrução única ESQL Statement. Essa instrução também pode ser composta, por exemplo BEGIN ... END. Alternativamente, é possível usar a cláusula EXTERNAL em vez de Statement; isso chama a rotina mencionada como um procedimento armazenado no banco de dados que é identificado pela propriedade da origem de dados do nó que o contém (consulte Procedimentos Armazenados Externos).

Procedimentos ESQL e procedimentos armazenados, diferentemente das funções ESQL, podem ter parâmetros OUT e INOUT além dos parâmetros IN. Isso permite que eles retornem vários valores para o responsável pela chamada.

Os procedimentos definidos em um módulo são locais em escopo para o nó atual apenas. Para utilizar o mesmo procedimento em mais de um nó, defina-o em um esquema.

Procedimentos internos sobrecarregados não são suportados. (Um procedimento sobrecarregado é um procedimento que tem o mesmo nome de outro procedimento no mesmo esquema do intermediário, com quantidade diferente de parâmetros ou parâmetros com tipos diferentes). Se o intermediário detectar que um procedimento foi sobrecarregado, ele produz uma exceção.

Parâmetros OUT transmitidos em um procedimento (quer interno ou externo) sempre contêm um valor NULL do tipo correto quando são recebidos pelo procedimento. Isso acontece independente do seu valor antes de CALL.

Os parâmetros IN e INOUT podem ser NULL quando recebidos pelo procedimento. Por exemplo, o seguinte transmite NULL para o procedimento testProc:

DECLARE nullChar CHARACTER;
CALL testProc( nullChar ); 	 

Isso acontece porque não foi designado um valor a nullChar antes de CALL.

Procedimentos Armazenados Externos

Para chamar um procedimento armazenado, você deve defini-lo no banco de dados e no intermediário em ESQL. Ao escrever procedimentos armazenados, em linguagens como o C, você deve utilizar indicadores NULL para garantir que seu procedimento possa processar os dados corretamente.

Você pode especificar um nome qualificado ou não-qualificado na cláusula EXTERNAL (o qualificador é o nome do esquema do banco de dados no qual o procedimento é definido). Se você não fornecer o nome de um esquema, o nome de usuário da conexão do banco de dados será utilizado como esquema padrão. Se o procedimento requerido não existir nesse esquema, você deve dar um nome de esquema explícito:

CREATE PROCEDURE ..... EXTERNAL NAME "mySchema.myProc";

Se o procedimento pertencer a um pacote Oracle, você deve fornecer um nome qualificado explícito da seguinte forma:

...EXTERNAL NAME "mySchema.myPackage.myProc"

Você não pode usar caracteres curinga SQL para nomes de esquemas, pacotes ou procedimentos, exceto para especificar um nome de esquema dinâmico. Os caracteres curinga (porcentagem, que representa um ou mais caracteres e traço sublinhado, que representa um único caractere) são modificados pelo intermediário para incluir o caractere de escape do banco de dados imediatamente antes de cada um deles. Assim, o banco de dados recebe esses caracteres como literais.

A cláusula abaixo é modificada pelo intermediário e mySchema.Proc\_ é transmitido ao banco de dados (supondo que o caractere de escape do banco de dados seja a barra invertida);

...EXTERNAL NAME "mySchema.Proc_"

Se você especificar o nome de um esquema de banco de dados como um único caractere de porcentagem ao criar o procedimento, o intermediário tratará isso como um caso especial e resolverá o nome no tempo de execução. A cláusula abaixo é válida e identifica um esquema de banco de dados dinâmico:

...EXTERNAL NAME "%.myProc"

Se você utilizar essa técnica, deve incluir a cláusula EXTERNAL SCHEMA na instrução CALL utilizada para chamar esse procedimento, para identificar o esquema apropriado.

Exemplo 1: procedimento armazenado externo demonstra como definir e chamar um procedimento armazenado no Oracle e no . Embora as definições dos bancos de dados variem, o ESQL não varia. Os nomes dados aos parâmetros no ESQL não precisam corresponder aos nomes que recebem no lado do banco de dados. No entanto, o nome externo do procedimento deve corresponder com o nome definido no banco de dados, incluindo quaisquer especificações de pacote ou contêiner. Coloque ExternalRoutineName entre aspas se ele contiver um caractere que não é permitido em um identificador.

Todos os procedimentos externos têm as seguintes restrições:

Exemplo 1: procedimento armazenado externo

ESQL Definition:
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) e execute:

    db2 -td@ -vf test1.sql do prompt de comandos do DB2.

-- 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 @

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

sqlplus <userid>/<password>  @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;
/

Resultados esperados no exemplo 1

Você pode esperar os seguintes resultados:

  1. O valor do parâmetro IN não se altera (e não pode por definição).
  2. O valor do parâmetro OUT se torna 'World'.
  3. O valor do parâmetro INOUT muda para 'Hello'.

Exemplo 2: procedimento ESQL

O exemplo a seguir mostra o mesmo procedimento como em Exemplo 1: procedimento armazenado externo, mas implementado como um procedimento ESQL interno. A sintaxe CALL para este procedimento é a mesma, assim como o resultado.

CREATE PROCEDURE swapParms (
    IN parm1 CHARACTER,
    OUT parm2  CHARACTER,
    INOUT parm3 CHARACTER )
  BEGIN
      SET parm2 = parm3;
      SET parm3 = parm1;
       END;

Exemplo 3: utilização recorrente de CRIAR PROCEDIMENTO

O exemplo de procedimento a seguir analisa uma árvore, visitando todos os lugares no ponto inicial e abaixo dele e relata o que encontrou:

 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 CHARACTER)||
        ': 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><Name>John Smith</Name><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

Conceitos relacionados
ESQL

Tarefas relacionadas
Desenvolvendo ESQL
Chamando Procedimentos Armazenados

Referências relacionadas
Preferência de Sintaxe
instruções ESQL
Instrução CALL