<commit_before><?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>
  <sect1 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 virtual tables; these are known as
      <link linkend="update_procedures">update procedures</link>
      .
    </para>
    <sect2>
      <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 other models (and their underlying data sources).
      </para>
      <example>
        <title>Example Command Statements</title>
        <programlisting>SELECT * FROM MyModel.MyTable WHERE ColA &gt; 100;
INSERT INTO MyModel.MyTable (ColA,ColB) VALUES (50, 'hi');</programlisting>
        </example>
    </sect2>
    <sect2 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 STRING &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 special element symbols 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 "USING.". 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 = USING.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 STRING sql_string; AS ID integer, Name string, Birthdate date INTO #temp USING LastName='<PASSWORD>'; 
/* 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><commit_msg>fixing minor typo<commit_after><?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>
  <sect1 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 virtual tables; these are known as
      <link linkend="update_procedures">update procedures</link>
      .
    </para>
    <sect2>
      <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 other models (and their underlying data sources).
      </para>
      <example>
        <title>Example Command Statements</title>
        <programlisting>SELECT * FROM MyModel.MyTable WHERE ColA &gt; 100;
INSERT INTO MyModel.MyTable (ColA,ColB) VALUES (50, 'hi');</programlisting>
        </example>
    </sect2>
    <sect2 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 STRING &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 special element symbols 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 "USING.". 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 = USING.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 STRING sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='<PASSWORD>'; 
/* 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>
{'fraction_non_alphanumeric': 0.08132245598969515, 'fraction_numerical': 0.0032632030914555602, 'mean_word_length': 3.426453819840365, 'pattern_counts': {'":': 0, '<': 149, '<?xml version=': 2, '>': 149, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 4}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7488511', 'n_tokens_mistral': 3081, 'n_tokens_neox': 2972, 'n_words': 1453}