Red Hat Summit Red Hat SummitSuporteConsoleDesenvolvedoresComeçar um testeContato

Este conteúdo não está disponível no idioma selecionado.

3.10. Procedures

3.10.1. Virtual Procedures
Copiar o link

Virtual procedures are defined using the JBoss Data Virtualization procedural language (see Section 3.9.1, “Procedural Language”). A virtual procedure has zero or more input parameters, and a result set return type. Virtual procedures support the ability to execute queries and other SQL commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.
Usage: 
CREATE VIRTUAL PROCEDURE
block
Copy to Clipboard Toggle word wrap
The CREATE VIRTUAL PROCEDURE line indicates the beginning of the procedure. Within the body of the procedure, any valid statement may be used. See Section 3.9.1, “Procedural Language”.
There is no explicit cursoring or return statement, rather the last command statement executed in the procedure that returns a result set will be returned as the result. The output of that statement must match the expected result set and parameters of the procedure.

3.10.2. Virtual Procedure Parameters
Copiar o link

Virtual procedures can take zero or more IN/INOUT parameters and may also have any number of OUT parameters and an optional RETURN parameter. Each input has the following information that is used during runtime processing:
  • Name - The name of the input parameter.
  • Datatype - The design-time type of the input parameter.
  • Default value - The default value if the input parameter is not specified.
  • Nullable - NO_NULLS, NULLABLE, NULLABLE_UNKNOWN; parameter is optional if nullable, and is not required to be listed when using named parameter syntax
You reference a parameter in a virtual procedure by using the fully-qualified name of the param (or less if unambiguous). For example, MySchema.MyProc.Param1.

Example 3.20. Example of Referencing an Input Parameter and Assigning an Out Parameter for 'GetBalance' Procedure

CREATE VIRTUAL PROCEDURE
BEGIN
  MySchema.GetBalance.RetVal = UPPER(MySchema.GetBalance.AcctID);
  SELECT Balance FROM MySchema.Accts WHERE MySchema.Accts.AccountID = MySchema.GetBalance.AcctID;
END
Copy to Clipboard Toggle word wrap
If an INOUT parameter is not assigned any value in a procedure it will remain the value it was assigned for input. Any OUT/RETURN parameter not assigned a value will remain the as the default NULL value. The INOUT/OUT/RETURN output values are validated against the NOT NULL metadata of the parameter.

3.10.3. Example Virtual Procedures
Copiar o link

This example is a LOOP that walks through a cursored table and uses CONTINUE and BREAK.

Example 3.21. Virtual Procedure Using LOOP, CONTINUE, BREAK

CREATE VIRTUAL PROCEDURE
BEGIN
  DECLARE double total;
  DECLARE integer transactions;
  LOOP ON (SELECT amt, type FROM CashTxnTable) AS txncursor
  BEGIN
    IF(txncursor.type <> 'Sale')
    BEGIN
      CONTINUE;
    END ELSE
    BEGIN
      total = (total + txncursor.amt);
      transactions = (transactions + 1);
      IF(transactions = 100)
      BEGIN
        BREAK;
      END
    END
  END
  SELECT total, (total / transactions) AS avg_transaction;
END
Copy to Clipboard Toggle word wrap
This example is uses conditional logic to determine which of two SELECT statements to execute.

Example 3.22. Virtual Procedure with Conditional SELECT

CREATE VIRTUAL PROCEDURE
BEGIN
  DECLARE string VARIABLES.SORTDIRECTION;
  VARIABLES.SORTDIRECTION = PartsVirtual.OrderedQtyProc.SORTMODE;
  IF ( ucase(VARIABLES.SORTDIRECTION) = 'ASC' )
  BEGIN
    SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID;
  END ELSE
  BEGIN
    SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID DESC;
  END
END
Copy to Clipboard Toggle word wrap

3.10.4. Executing Virtual Procedures
Copiar o link

You execute procedures using the SQL EXECUTE command. See Section 3.5.7, “EXECUTE Command”.
If the procedure has defined inputs, you specify those in a sequential list, or using "name=value" syntax. You must use the name of the input parameter, scoped by the full procedure name if the parameter name is ambiguous in the context of other columns or variables in the procedure.
A virtual procedure call will return a result set like any SELECT, so you can use this in many places you can use a SELECT. Typically you'll use the following syntax: 
SELECT * FROM (EXEC ...) AS x
Copy to Clipboard Toggle word wrap

3.10.5. Virtual Procedure Limitations
Copiar o link

JBoss Data Virtualization virtual procedures can only be defined in Teiid Designer. They also cannot use IN/OUT, OUT, or RETURN parameters and may only return 1 result set.

3.10.6. Update Procedures
Copiar o link

Views are abstractions above physical sources. They typically union or join information from multiple tables, often from multiple data sources or other views. JBoss Data Virtualization can perform update operations against views. Update commands - INSERT, UPDATE, or DELETE - against a view require logic to define how the tables and views integrated by the view are affected by each type of command. This transformation logic is invoked when an update command is issued against a view. Update procedures define the logic for how a user's update command against a view should be decomposed into the individual commands to be executed against the underlying physical sources. Similar to virtual procedures , update procedures have the ability to execute queries or other commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic. See Section 3.10.1, “Virtual Procedures” for more information about virtual procedures.

3.10.7. Update Procedure Processing
Copiar o link

  1. The user application submits the SQL command through one of SOAP, JDBC, or ODBC.
  2. The view this SQL command is executed against is detected.
  3. The correct procedure is chosen depending upon whether the command is an INSERT, UPDATE, or DELETE.
  4. The procedure is executed. The procedure itself can contain SQL commands of its own which can be of different types than the command submitted by the user application that invoked the procedure.
  5. Commands, as described in the procedure, are issued to the individual physical data sources or other views.
  6. A value representing the number of rows changed is returned to the calling application.

3.10.8. The FOR EACH ROW Procedure
Copiar o link

A FOR EACH ROW procedure will evaluate its block for each row of the view affected by the update statement. For UPDATE and DELETE statements this will be every row that passes the WHERE condition. For INSERT statements there will be 1 new row for each set of values from the VALUES or query expression. The rows updated is reported as this number regardless of the affect of the underlying procedure logic.
JBoss Data Virtualization FOR EACH ROW update procedures function like INSTEAD OF triggers in traditional databases. There may only be 1 FOR EACH ROW procedure for each INSERT, UPDATE, or DELETE operation against a view. FOR EACH ROW update procedures can also be used to emulate BEFORE/AFTER each row triggers while still retaining the ability to perform an inherent update. This BEFORE/AFTER trigger behavior with an inherent update can be achieved by creating an additional updatable view over the target view with update procedures of the form:
Usage: 
FOR EACH ROW
        BEGIN ATOMIC
          ...
        END
Copy to Clipboard Toggle word wrap
The BEGIN and END keywords are used to denote block boundaries. Within the body of the procedure, any valid statement may be used. See Section 3.9.1, “Procedural Language”.

Note

Use of the ATOMIC keyword is currently optional for backward compatibility, but unlike a normal block, the default for INSTEAD OF is atomic.

3.10.9. Special Variables for Update Procedures
Copiar o link

You can use a number of special variables when defining your update procedure.
NEW
Every attribute in the view whose UPDATE and INSERT transformations you are defining has an equivalent variable named NEW.<column_name>
When an INSERT or an UPDATE command is executed against the view, these variables are initialized to the values in the INSERT VALUES clause or the UPDATE SET clause respectively.
In an UPDATE procedure, the default value of these variables, if they are not set by the command, is the old value. In an INSERT procedure, the default value of these variables is the default value of the virtual table attributes. See CHANGING variables for distinguishing defaults from passed values.
OLD
Every attribute in the view whose UPDATE and DELETE transformations you are defining has an equivalent variable named OLD.<column_name>
When a DELETE or UPDATE command is executed against the view, these variables are initialized to the current values of the row being deleted or updated respectively.
CHANGING
Every attribute in the view whose UPDATE and INSERT transformations you are defining has an equivalent variable named CHANGING.<column_name>
When an INSERT or an UPDATE command is executed against the view, these variables are initialized to true or false depending on whether the INPUT variable was set by the command. A CHANGING variable is commonly used to differentiate between a default insert value and one specified in the user query.
For example, for a view with columns A, B, C:
Expand
If User Executes... Then...
INSERT INTO VT (A, B) VALUES (0, 1) CHANGING.A = true, CHANGING.B = true, CHANGING.C = false
UPDATE VT SET C = 2 CHANGING.A = false, CHANGING.B = false, CHANGING.C = true

3.10.10. Example Update Procedures
Copiar o link

For example, for a view with columns A, B, C:

Example 3.23. Sample DELETE Procedure

FOR EACH ROW
BEGIN
        DELETE FROM X WHERE Y = OLD.A;
        DELETE FROM Z WHERE Y = OLD.A; // cascade the delete
END
Copy to Clipboard Toggle word wrap

Example 3.24. Sample UPDATE Procedure

FOR EACH ROW
BEGIN
    IF (CHANGING.B)
    BEGIN
                UPDATE Z SET Y = NEW.B WHERE Y = OLD.B;
    END
END
Copy to Clipboard Toggle word wrap
Voltar ao topo
Red Hat logoGithubredditYoutubeTwitter

Aprender

Experimente, compre e venda

Comunidades

Sobre a documentação da Red Hat

Ajudamos os usuários da Red Hat a inovar e atingir seus objetivos com nossos produtos e serviços com conteúdo em que podem confiar. Explore nossas atualizações recentes.

Tornando o open source mais inclusivo

A Red Hat está comprometida em substituir a linguagem problemática em nosso código, documentação e propriedades da web. Para mais detalhes veja o Blog da Red Hat.

Sobre a Red Hat

Fornecemos soluções robustas que facilitam o trabalho das empresas em plataformas e ambientes, desde o data center principal até a borda da rede.

Theme

© 2025 Red Hat