Ce contenu n'est pas disponible dans la langue sélectionnée.
2.10. Procedures
2.10.1. Virtual Procedures
CREATE VIRTUAL PROCEDURE block
2.10.2. Virtual Procedure Parameters
- 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
Example 2.19. 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
2.10.3. Example Virtual Procedures
Example 2.20. 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
Example 2.21. 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
2.10.4. Executing Virtual Procedures
SELECT * FROM (EXEC ...) AS x
2.10.5. Virtual Procedure Limitations
2.10.6. Update Procedures
INSTEAD OF
triggers on views in a similar way to how they would be used in traditional databases. You can only have one FOR EACH ROW
procedure for each INSERT
, UPDATE
, or DELETE
operation against a view.
CREATE TRIGGER ON view_name INSTEAD OF INSERT|UPDATE|DELETE AS FOR EACH ROW ...
2.10.7. Update Procedure Processing
- The user application submits the SQL command through one of SOAP, JDBC, or ODBC.
- The view this SQL command is executed against is detected.
- The correct procedure is chosen depending upon whether the command is an INSERT, UPDATE, or DELETE.
- 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.
- Commands, as described in the procedure, are issued to the individual physical data sources or other views.
- A value representing the number of rows changed is returned to the calling application.
2.10.8. The FOR EACH ROW Procedure
FOR EACH ROW BEGIN ATOMIC ... END
Note
2.10.9. Special Variables for Update Procedures
- 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
orfalse
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: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
2.10.10. Example Update Procedures
Example 2.22. 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
Example 2.23. Sample UPDATE Procedure
FOR EACH ROW BEGIN IF (CHANGING.B) BEGIN UPDATE Z SET Y = NEW.B WHERE Y = OLD.B; END END
2.10.11. Comments
/* */
:
/* comment comment comment... */
SELECT ... -- comment