<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.5//EN" "http://www.oasis-open.org/docbook/xml/4.5/docbookx.dtd">
<chapter id="procedures">
  <title>Procedures</title>
  <section id="procedure_language">
    <title>Procedure Language</title>
    <para>
      Teiid supports a procedural language for defining
      <link linkend="virtual_procedures">virtual procedures</link>
      . These are similar to stored procedures in relational database
      management systems. You can use this language to define the
      transformation logic for decomposing INSERT, UPDATE, and DELETE
      commands against views; these are known as
      <link linkend="update_procedures">update procedures</link>
      .
    </para>
    <section>
      <title>Command Statement</title>
      <para>
        A command statement executes a
        <link linkend="sql_commands">SQL command</link>
        , such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE, against
        one or more data sources.
      </para>
      <example>
        <title>Example Command Statements</title>
        <programlisting>SELECT * FROM MySchema.MyTable WHERE ColA &gt; 100;
INSERT INTO MySchema.MyTable (ColA,ColB) VALUES (50, 'hi');</programlisting>
        </example>
      <para><link linkend="exec_command">EXECUTE</link> command statements may access IN/OUT, OUT, and RETURN parameters.  To access the return value the statement will have the form <code>var = EXEC proc...</code>.  
      To access OUT or IN/OUT values named parameter syntax must be used.  For example, <code>EXEC proc(in_param=>'1', out_param=>var)</code> will assign the value of the out parameter to the variable var.
      It is expected that the datatype of parameter will be implicitly convertable to the datatype of the variable.</para>
    </section>
    <section id="dynamic_sql">
      <title>Dynamic SQL Command</title>
      <para>Dynamic SQL allows for the execution of an arbitrary SQL command in a virtual procedure. Dynamic SQL is useful in situations where the exact command form is not known prior to execution.
      </para>
      <para>
        Usage:
        <synopsis>EXECUTE IMMEDIATE &lt;expression&gt; <optional>AS &lt;variable&gt; &lt;type&gt; [, &lt;variable&gt; &lt;type&gt;]* [INTO &lt;variable&gt;]</optional> 
[USING &lt;variable&gt;=&lt;expression&gt; [,&lt;variable&gt;=&lt;expression&gt;]*] [UPDATE &lt;literal&gt;]        
      </synopsis>
      </para>
      <itemizedlist>
        <para>Syntax Rules:</para>
        <listitem>
          <para>The "AS" clause is used to define the projected symbols names and types returned by the executed SQL string. The "AS" clause symbols will be matched positionally with the symbols returned by the executed SQL string. Non-convertible types or too few columns returned by the executed SQL string will result in an error.
          </para>
        </listitem>
        <listitem>
          <para>The "INTO" clause will project the dynamic SQL into the specified temp table. With the "INTO" clause specified, the dynamic command will actually execute a statement that behaves like an INSERT with a QUERY EXPRESSION. If the dynamic SQL command creates a temporary table with the "INTO" clause, then the "AS" clause is required to define the table’s metadata.
          </para>
        </listitem>
        <listitem>
          <para>The "USING" clause allows the dynamic SQL string to contain variable references that are bound at runtime to specified values. This allows for some independence of the SQL string from the surrounding procedure variable names and input names. In the dynamic command "USING" clause, each variable is specified by short name only. However in the dynamic SQL the "USING" variable must be fully qualified to "DVAR.". The "USING" clause is only for values that will be used in the dynamic SQL as legal expressions. It is not possible to use the "USING" clause to replace table names, keywords, etc. This makes using symbols equivalent in power to normal bind (?) expressions in prepared statements. The "USING" clause helps reduce the amount of string manipulation needed. If a reference is made to a USING symbol in the SQL string that is not bound to a value in the "USING" clause, an exception will occur.
          </para>
        </listitem>
        <listitem>
          <para>The "UPDATE" clause is used to specify the <link linkend="updating_model_count">updating model count</link>. Accepted values are (0,1,*). 0 is the default value if the clause is not specified.
          </para>
        </listitem>
      </itemizedlist>
      <example>
        <title>Example Dynamic SQL</title>
        <programlisting>... 
/* Typically complex criteria would be formed based upon inputs to the procedure. 
 In this simple example the criteria is references the using clause to isolate 
 the SQL string from referencing a value from the procedure directly */ 
DECLARE string criteria = 'Customer.Accounts.Last = DVARS.LastName'; 
/* Now we create the desired SQL string */ 
DECLARE string sql_string = 'SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria; 
/* The execution of the SQL string will create the #temp table with the columns (ID, Name, Birthdate). 
  Note that we also have the USING clause to bind a value to LastName, which is referenced in the criteria. */ 
EXECUTE IMMEDIATE sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name'; 
/* The temp table can now be used with the values from the Dynamic SQL */ 
loop on (SELCT ID from #temp) as myCursor 
...</programlisting>
      </example>
      <para>
      Here is an example showing a more complex approach to building criteria for the dynamic SQL string. In short, the virtual procedure AccountAccess.GetAccounts has inputs ID, LastName, and bday. If a value is specified for ID it will be the only value used in the dynamic SQL criteria. Otherwise if a value is specified for LastName the procedure will detect if the value is a search string. If bday is specified in addition to LastName, it will be used to form compound criteria with LastName.
      </para>
      <example>
        <title>Example Dynamic SQL with USING clause and dynamically built criteria string</title>
        <programlisting>...
DECLARE string crit = null; 
IF (AccountAccess.GetAccounts.ID IS NOT NULL) 
 crit = ‘(Customer.Accounts.ID = DVARS.ID)’; 
ELSE IF (AccountAccess.GetAccounts.LastName IS NOT NULL) 
BEGIN 
 IF (AccountAccess.GetAccounts.LastName == ‘%’) 
   ERROR "Last name cannot be %"; 
 ELSE IF (LOCATE(‘%’, AccountAccess.GetAccounts.LastName) &lt; 0) 
   crit = ‘(Customer.Accounts.Last = DVARS.LastName)’; 
 ELSE 
   crit = ‘(Customer.Accounts.Last LIKE DVARS.LastName)’; 
 IF (AccountAccess.GetAccounts.bday IS NOT NULL) 
   crit = ‘(‘ || crit || ‘ and (Customer.Accounts.Birthdate = DVARS.BirthDay))’; 
END 
ELSE 
 ERROR "ID or LastName must be specified."; 
EXECUTE IMMEDIATE ‘SELECT ID, First || ‘‘ ‘‘ || Last AS Name, Birthdate FROM Customer.Accounts WHERE ’ || crit USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday;
...</programlisting>
      </example>
      <itemizedlist>
        <para>Known Limitations and Work-Arounds
        </para>
        <listitem>
          <para>The use of dynamic SQL command results in an assignment statement requires the use of a temp table.
          </para>
          <example>
            <title>Example Assignment
            </title>
            <programlisting>EXECUTE IMMEDIATE &lt;expression&gt; AS x string INTO #temp; 
DECLARE string VARIABLES.RESULT = (SELECT x FROM #temp);</programlisting>
          </example>
        </listitem>
        <listitem>
          <para>The construction of appropriate criteria will be cumbersome if parts of the criteria are not present. For example if "criteria" were already NULL, then the following example results in "criteria" remaining NULL.
          </para>
          <example>
            <title>Example Dangerous NULL handling
            </title>
            <programlisting>...
criteria = ‘(‘ || criteria || ‘ and (Customer.Accounts.Birthdate = DVARS.BirthDay))’;</programlisting>
          </example>
          <para>The preferred approach is for the user to ensure the criteria is not NULL prior its usage. If this is not possible, a good approach is to specify a default as shown in the following example.
          </para>
          <example>
            <title>Example NULL handling
            </title>
            <programlisting>...
criteria = ‘(‘ || nvl(criteria, ‘(1 = 1)’) || ‘ and (Customer.Accounts.Birthdate = DVARS.BirthDay))’;</programlisting>
          </example>
        </listitem>
        <listitem>
          <para>If the dynamic SQL is an UPDATE, DELETE, or INSERT command, and the user needs to specify the "AS" clause (which would be the case if the number of rows effected needs to be retrieved).  The user will still need to provide a name and type for the return column if the into clause is specified.
          </para>
          <example>
            <title>Example with AS and INTO clauses</title>
            <programlisting>/* This name does not need to match the expected update command symbol "count". */
EXECUTE IMMEDIATE &lt;expression&gt; AS x integer INTO #temp;</programlisting>
          </example>
        </listitem>
        <listitem>
          <para>Unless used in other parts of the procedure, tables in the dynamic command will not be seen as sources in the Designer.
          </para>
        </listitem>
        <listitem>
          <para>When using the "AS" clause only the type information will be available to the Designer. ResultSet columns generated from the "AS" clause then will have a default set of properties for length, precision, etc.
          </para>
        </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>Declaration Statement</title>
      <para>A declaration statement declares a variable and its type. After you declare a variable, you can use it in that block within the procedure and any sub-blocks. A variable is initialized to null by default, but can also be assigned the value of an expression as part of the declaration statement. 
      </para>
      <para>
        Usage:
        <synopsis label="Usage" >DECLARE &lt;type&gt; [VARIABLES.]&lt;name&gt; [= &lt;expression&gt;];</synopsis>
      </para>        
      <itemizedlist>
        <para>Example Syntax</para>
        <listitem>
          <para>declare integer x;
          </para>
        </listitem>
        <listitem>
          <para>declare string VARIABLES.myvar = 'value';
          </para>
        </listitem>        
      </itemizedlist>
      <itemizedlist>
        <para>Syntax Rules:
        </para>
        <listitem>
          <para>You cannot redeclare a variable with a duplicate name in a sub-block
          </para>
        </listitem>
        <listitem>
          <para>The VARIABLES group is always implied even if it is not specified.
          </para>
        </listitem>
        <listitem>
          <para>The assignment value follows the same rules as for an Assignment Statement.
          </para>
        </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>Assignment Statement</title>
      <para>An assignment statement assigns a value to a variable by either evaluating an expression.</para>
      <para>
        Usage:
        <synopsis label="Usage" >&lt;variable reference&gt; = &lt;expression&gt;;</synopsis>
      </para>
      <itemizedlist>
        <para>Example Syntax</para>
        <listitem>
          <para>myString = 'Thank you';
          </para>
        </listitem>
        <listitem>
          <para>VARIABLES.x = (SELECT Column1 FROM MySchema.MyTable);
          </para>
        </listitem>
      </itemizedlist>
      <section>
	      <title>Special Variables</title>
	      <para><code>VARIABLES.ROWCOUNT</code> integer variable will contain the numbers of rows affected by the last insert/update/delete command statement executed.  Inserts that are processed by dynamic sql with an into clause will also update the ROWCOUNT.</para>
	      <example>
			<title>Sample Usage</title>
			<programlisting language="SQL"><![CDATA[...
UPDATE FOO SET X = 1 WHERE Y = 2;
DECLARE INTEGER UPDATED = VARIABLES.ROWCOUNT;
...]]></programlisting>
		  </example>
  	  </section>
    </section>
    <section>
      <title>Compound Statement</title>
      <para>A compound statement or block logically groups a series of statements.  Temporary tables and variables created in a compound statement are local only to that block are destroyed when exiting the block.</para>
      <para>
        Usage:
        <synopsis label="Usage" >[label :] BEGIN [[NOT] ATOMIC]  
  statement*
END</synopsis>
		<note><para>When a block is expected by a IF, LOOP, WHILE, etc. a single statement is also accepted by the parser.  Even though the block BEGIN/END are not expected, the statement will execute as if wrapped in a BEGIN/END pair.</para></note>
      </para>
      <itemizedlist>
	 	<para>Syntax Rules</para>
	    <listitem><para>IF NOT ATOMIC or no ATOMIC clause is specificed, the block will be executed non-atomically.</para>
	    </listitem>
	    <listitem><para>IF ATOMIC the block must execute atomically.  If a transaction is already associated with the thread, no aditional action will be taken - savepoints and/or sub-transactions are not currrently used.  Otherwise a transaction will be associated with the execution of the block.</para>
	    </listitem>
	    <listitem><para>The label must not be the same as any other label used in statements containing this one.</para>
	    </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>If Statement</title>
      <para>An IF statement evaluates a condition and executes either
        one of two statements depending on the result. You can nest IF
        statements to create complex branching logic. A dependent ELSE
        statement will execute its statement only if the IF statement
        evaluates to false.</para>
      <para>
        Usage:
        <synopsis label="Usage" >IF (criteria) 
  block
[ELSE
  block]
END</synopsis>
      </para>
      <example>
        <title>Example If Statement</title>
        <programlisting>IF ( var1 = 'North America') 
BEGIN 
  ...statement... 
END ELSE 
BEGIN 
  ...statement... 
END</programlisting>
      </example>
      <tip>
        <para>
          NULL values should be considered in the criteria of an IF statement.  IS NULL criteria can be used to detect the presense of a NULL value.  
        </para>
      </tip>
    </section>
    <section>
      <title>Loop Statement</title>
      <para>A LOOP statement is an iterative control construct that is used to cursor through a result set.</para>
      <para>
        Usage:
        <synopsis label="Usage" >[label :] LOOP ON &lt;select statement&gt; AS &lt;cursorname&gt; 
  block</synopsis>
      </para>
      <itemizedlist>
	 	<para>Syntax Rules</para>
	    <listitem><para>The label must not be the same as any other label used in statements containing this one.</para>
	    </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>While Statement</title>
      <para>A WHILE statement is an iterative control construct that is used to execute a block repeatedly whenever a specified condition is met.</para>
      <para>
        Usage:
        <synopsis label="Usage" >[label :] WHILE &lt;criteria&gt; 
  block</synopsis>
      </para>
      <itemizedlist>
	 	<para>Syntax Rules</para>
	    <listitem><para>The label must not be the same as any other label used in statements containing this one.</para>
	    </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>Continue Statement</title>
      <para>A CONTINUE statement is used inside a LOOP or WHILE construct to continue with the next loop by skipping over the rest of the statements in the loop. It must be used inside a LOOP or WHILE statement.</para>
      <para>
        Usage:
        <synopsis label="Usage" >CONTINUE [label];</synopsis>
      </para>
      <itemizedlist>
	 	<para>Syntax Rules</para>
	    <listitem><para>If the label is specified, it must exist on a containing LOOP or WHILE statement.</para>
	    </listitem>
	    <listitem><para>If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.</para>
	    </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>Break Statement</title>
      <para>A BREAK statement is used inside a LOOP or WHILE construct to break from the loop. It must be used inside a LOOP or WHILE statement.</para>
      <para>
        Usage:
        <synopsis label="Usage" >BREAK [label];</synopsis>
      </para>
      <itemizedlist>
	 	<para>Syntax Rules</para>
	    <listitem><para>If the label is specified, it must exist on a containing LOOP or WHILE statement.</para>
	    </listitem>
	    <listitem><para>If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.</para>
	    </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>Leave Statement</title>
      <para>A LEAVE statement is used inside a compound, LOOP, or WHILE construct to leave to the specified level.</para>
      <para>
        Usage:
        <synopsis label="Usage" >LEAVE label;</synopsis>
      </para>
      <itemizedlist>
	 	<para>Syntax Rules</para>
	    <listitem><para>The label must exist on a containing compound statement, LOOP, or WHILE statement.</para>
	    </listitem>
      </itemizedlist>
    </section>
    <section>
      <title>Error Statement</title>
      <para>An ERROR statement declares that the procedure has entered an error state and should abort. This statement will also roll back the current transaction, if one exists. Any valid expression can be specified after the ERROR keyword.</para>
      <para>
        Usage:
        <synopsis label="Usage" >ERROR message;</synopsis>
      </para>
      <example>
        <title>Example Error Statement</title>
        <programlisting>ERROR 'Invalid input value: ' || nvl(Acct.GetBalance.AcctID, 'null');</programlisting>
      </example>
    </section>
  </section>
  <section id="virtual_procedures">
    <title>Virtual Procedures</title>
    <para>Virtual procedures are defined using the Teiid
      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.</para>
    <section>
      <title>Virtual Procedure Definition</title>
      <para>
        Usage:
        <synopsis label="Usage" >CREATE VIRTUAL PROCEDURE 
block</synopsis>
      </para>
      <para>The CREATE VIRTUAL PROCEDURE line demarcates the beginning of
        the procedure. Within the body of the procedure, any valid <link linkend="procedure_language">statement</link> may be used.
      </para>
      <para>There is no explict 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.</para>      
    </section>
    <section>
      <title>Procedure Parameters</title>
      <para>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: </para>
      <itemizedlist>
        <listitem>
          <para>Name - The name of the input parameter</para>
        </listitem>
        <listitem>
          <para>Datatype - The design-time type of the input parameter</para>
        </listitem>
        <listitem>
          <para>Default value - The default value if the input parameter is not specified</para>
        </listitem>
        <listitem>
          <para>Nullable - NO_NULLS, NULLABLE, NULLABLE_UNKNOWN; parameter is optional if nullable, and is not required to be listed when using named parameter syntax</para>
        </listitem>
      </itemizedlist>
      <para>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.
      </para>
      <example>
        <title>Example of Referencing an Input Parameter and Assigning an Out Parameter for 'GetBalance' Procedure</title>
        <programlisting>
CREATE VIRTUAL PROCEDURE 
BEGIN 
  MySchema.GetBalance.RetVal = UPPER(MySchema.GetBalance.AcctID);
  SELECT Balance FROM MySchema.Accts WHERE MySchema.Accts.AccountID = MySchema.GetBalance.AcctID; 
END</programlisting>
      </example>
      <para>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.</para>
    </section>
    <section>
      <title>Example Virtual Procedures</title>
      <para>This example is a LOOP that walks through a cursored table and uses CONTINUE and BREAK.</para>
      <example>
        <title>Virtual Procedure Using LOOP, CONTINUE, BREAK
        </title>
        <programlisting>
CREATE VIRTUAL PROCEDURE
BEGIN
  DECLARE double total;
  DECLARE integer transactions;
  LOOP ON (SELECT amt, type FROM CashTxnTable) AS txncursor
  BEGIN
    IF(txncursor.type &lt;&gt; '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
        </programlisting>
      </example>
      <para>This example is uses conditional logic to determine which of two SELECT statements to execute.</para>
      <example>
        <title>Virtual Procedure with Conditional SELECT
        </title>
        <programlisting>
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 &gt; 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 
        </programlisting>
      </example>
    </section>
    <section>
      <title>Executing Virtual Procedures</title>
      <para>You execute procedures using the SQL <link linkend="exec_command">EXECUTE</link> 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.</para>
      <para> A virtual procedure call will return a result set just like any
        SELECT, so you can use this in many places you can use a SELECT.
        Typically you'll use the following syntax:</para>
      <programlisting>SELECT * FROM (EXEC ...) AS x</programlisting>  
    </section>
    <section>
    	<title>Limitations</title>
    	<para>Teiid virtual procedures can only be defined in Teiid Designer.  They also cannot use IN/OUT, OUT, or RETURN paramters and may only return 1 result set.</para>
    </section>
  </section>  
  <section id="update_procedures">
    <title>Update Procedures</title>
    <para>
      Views are abstractions above physical sources. They typically
      union or join information from multiple tables, often from multiple
      data sources or other views. Teiid 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
      <link linkend="virtual_procedures">virtual procedures</link>
      , 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.
    </para>
    <section>
      <title>Update Procedure Processing</title>
      <orderedlist>
        <listitem>
          <para>The user application submits the SQL command through one of SOAP, JDBC, or ODBC.
          </para>
        </listitem>
        <listitem>
          <para>The view this SQL command is executed against is detected.
          </para>
        </listitem>
        <listitem>
          <para>The correct procedure is chosen depending upon whether the command is an INSERT, UPDATE, or DELETE.
          </para>
        </listitem>
        <listitem>
          <para>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.
          </para>
        </listitem>
        <listitem>
          <para>Commands, as described in the procedure, are issued to the individual physical data sources or other views.
          </para>
        </listitem>
        <listitem>
          <para>A value representing the number of rows changed is returned to the calling application.
          </para>
        </listitem>
      </orderedlist>
    </section>  
    <section>
    	<title>For Each Row</title>
    	<para>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.</para>
    	<para>Teiid 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 proceudres of the form:
		<programlisting lang="sql">FOR EACH ROW
	BEGIN ATOMIC
	--before row logic
	
	--default insert/update/delete against the target view
	INSERT INTO VW (c1, c2, c3) VALUES (NEW.c1, NEW.c2, NEW.c3); 
	
	--after row logic
	END</programlisting>   	
    	</para>
    	<section>
	      <title>Definition</title>
	      <para>
	        Usage:
	        <synopsis label="Usage" >FOR EACH ROW 
	BEGIN ATOMIC
	  ...
	END</synopsis>
	      </para>
	      <para>The BEGIN and END keywords are used to denote block
	        boundaries. Within the body of the procedure, any valid <link linkend="procedure_language">statement</link> may be used.
	      </para>
	      <note>
	      <para>The use of the atomic keyword is currently optional for backward compatibility, but unlike a normal block, the default for instead of triggers is atomic.</para>
	      </note>
	    </section>
	    <section>
	      <title>Special Variables</title>
	      <para>You can use a number of special variables when defining your update procedure.</para>
	      <section id="new_variables">
	        <title>NEW Variables</title>
	        <para>Every attribute in the view whose UPDATE and INSERT transformations you are defining has an equivalent variable named NEW.&lt;column_name&gt;</para>
	        <para>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.</para>
	        <para>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.</para>
	      </section>
	      <section id="old_variables">
	        <title>OLD Variables</title>
	        <para>Every attribute in the view whose UPDATE and DELETE transformations you are defining has an equivalent variable named OLD.&lt;column_name&gt;</para>
	        <para>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.</para>
	      </section>
	      <section id="changing_variables">
	        <title>CHANGING Variables</title>
	        <para>Every attribute in the view whose UPDATE and INSERT transformations you are defining has an equivalent variable named CHANGING.&lt;column_name&gt;</para>
	        <para>When an INSERT or an UPDATE command is executed against the view, these variables are initialized to <literal>true</literal> or <literal>false</literal> 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.</para>
	        <para>For example, for a view with columns A, B, C:</para>
	        <informaltable>
	          <tgroup cols="2">
	            <thead>
	              <row>
	                <entry>If User Executes...</entry>
	                <entry>Then...</entry>
	              </row>
	            </thead>
	            <tbody>
	              <row>
	                <entry><code>INSERT INTO VT (A, B) VALUES (0, 1)</code></entry>
	                <entry>CHANGING.A = true, CHANGING.B = true, CHANGING.C = false</entry>
	              </row>
	              <row>
	                <entry><code>UPDATE VT SET C = 2</code></entry>
	                <entry>CHANGING.A = false, CHANGING.B = false, CHANGING.C = true</entry>
	              </row>
	            </tbody>
	          </tgroup>
	        </informaltable>
	      </section>
		</section>
		<section>
			<title>Examples</title>
			<para>For example, for a view with columns A, B, C:</para>
	        <example>
				<title>Sample DELETE Procedure</title>
				<programlisting language="SQL"><![CDATA[FOR EACH ROW
BEGIN
	DELETE FROM X WHERE Y = OLD.A;
	DELETE FROM Z WHERE Y = OLD.A; // cascade the delete
END]]></programlisting>
           </example>
	        <example>
				<title>Sample UPDATE Procedure</title>
				<programlisting language="SQL"><![CDATA[FOR EACH ROW
BEGIN
    IF (CHANGING.B)
    BEGIN
		UPDATE Z SET Y = NEW.B WHERE Y = OLD.B;
    END
END]]></programlisting>
           </example>
		</section>
    </section>
  </section>
</chapter>