НОУ ИНТУИТ | Введение в генерацию программного кода. Лекция 4: Генерация запросов SQL

Опубликован: 25.07.2012 | Уровень: специалист | Доступ: платный

Генерация запроса на обновление записи таблицы

Теперь рассмотрим генерацию запросов с применением оператора UPDATE. Для таблиц drawing и equipment запрос на обновление строки будет иметь следующий вид.

update drawing set
	name = p_name,
	title = p_title,
	revision = p_revision
where id = p_id

update equipment set
	serial_number = p_serial_number,
	model = p_model,
	description = p_description
where id = p_id

Как видим, будет обновляться одна строка таблицы. Какая именно строка должна обновляться определяется по значению идентификатора. Все значения передаются в запрос с помощью переменных формата p_<имя поля>.

Программа для генерации запроса на обновление строки таблицы:

Table table = new Table(filepath);
List<string> query = new List<string>();
string text = "update " + table.name + " set";
query.Add(text);
for (int i = 0; i < table.fields.Count; i++)
{
    if (table.fields[i].name != "id")
    {
        text = "\t" + table.fields[i].name + " = p_" + table.fields[i].name;
        if (i < table.fields.Count - 1) text += ",";
        query.Add(text);
    }
}
query.Add("where id = p_id");
Output.PutResult(query, resultpath);

Пример 3.10.

Так же как и для предыдущего примера, в цикле проходим по всем полям таблицы, кроме идентификатора id. В самом конце в условие where добавляется выбор строки по значению идентификатора.

Генерация запроса на удаление строки таблицы

Генерация запроса с применением оператора DELETE будет выглядеть так.

Table table = new Table(filepath);
string query = "delete from " + table.name + " where id = p_id";
Output.PutResult(query, resultpath);

Пример 3.11.

Для таблиц drawing и equipment будут сгенерированы такие запросы

delete from drawing where id = p_id

delete from equipment where id = p_id

Генерация запроса на выборку всех полей из нескольких таблиц

До этого момента мы рассматривали примеры запросов из одной таблицы, однако в большинстве случаев в запросах участвуют несколько таблиц. Для этого нужно каким-либо образом представить связи между таблицами. Пусть в нашем случае информация о внешней таблице и ключе внешней таблицы хранится в полях foreign_key_table и foreign_key_field, причем в первом из них хранится короткое имя таблицы:

<?xml version="1.0" encoding="utf-8" ?>
<tables>
  <table name="drawing" shortname="d">
    <field name="id" type="number" length=""/>
    <field name="name" type="varchar2" length="(100 char)"/>
    <field name="title" type="varchar2" length="(1000 char)"/>
    <field name="revision" type="varchar2" length="(10 char)"/>
  </table>
  <table name="equipment" shortname="e">
    <field name="id" type="number" length=""/>
    <field name="serial_number" type="varchar2" length="(100 char)"/>
    <field name="model" type="varchar2" length="(100 char)"/>
    <field name="description" type="varchar2" length="(1000 char)"/>
  </table>
  <table name="drawing_equipment" shortname="de">
    <field name="id" type="number" length=""/>
    <field name="drawing_id" type="number" length="" foreign_key_table="d" foreign_key_field="id"/>
    <field name="equipment_id" type="number" length="(100 char)" foreign_key_table="e" foreign_key_field="id"/>
  </table>
</tables>

Пример 3.12.

Создадим класс DBStructure, хранящий структуру таблиц в виде списка объектов класса Table. Определим в нем конструктор, выполняющий чтение структуры таблиц по указанному пути к XML-файлу.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;

class DBStructure
{
    public List<Table> tables;
    public DBStructure(string path)
    {
        XmlDocument reader = new XmlDocument();
        reader.Load(path);
        XmlElement elem = reader.DocumentElement;
        tables = new List<Table>();
        for (int i = 0; i < elem.ChildNodes.Count; i++)
        {
            Table table = new Table((XmlElement)elem.ChildNodes[i]);
            tables.Add(table);
        }
    }
}

Пример 3.13.

Так как мы добавили атрибуты foreign_key_table и foreign_key_field, то класс Table и структура Field тоже изменятся - добавится чтение соответствующих атрибутов. В остальном код класса Table и структуры Field останутся без изменений.

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;

class Table
{
    public string name;
    public string shortname;
    public List<Field> fields;
    public Table(XmlElement elem)
    {
        name = elem.GetAttribute("name");
        shortname = elem.GetAttribute("shortname");
        fields = new List<Field>();
        for (int i = 0; i < elem.ChildNodes.Count; i++)
        {
            if (elem.ChildNodes[i].Name == "field")
            {
                XmlElement elemField = (XmlElement)elem.ChildNodes[i];
                Field fld = new Field();
                fld.name = elemField.GetAttribute("name");
                fld.type = elemField.GetAttribute("type");
                fld.length = elemField.GetAttribute("length");
                fld.foreign_key_table = elemField.GetAttribute("foreign_key_table");
                fld.foreign_key_field = elemField.GetAttribute("foreign_key_field");
                fields.Add(fld);
            }
        }
    }
}

Пример 3.14. Класс Table

struct Field
{
    public string name;
    public string type;
    public string length;
    public string foreign_key_table;
    public string foreign_key_field;
}

Пример 3.15. Структура Field

Рассмотрим непосредственно саму программу генерации запроса:

using System;
using System.Collections.Generic;
using System.Text;

class Program
{
    static void AllSelectSQL()
    {
        //считывается структура таблиц
        DBStructure dbstructure = new DBStructure(@"A:\input\structure.xml");
        //задаются начальные значения переменных, 
        // в которых будут храниться части запросов,
        // начинающиеся с select, from, where
        string select_query = "select ";
        string from_query = "from ";
        string where_query = "where ";
        List<string> query = new List<string>();
        for (int i = 0; i < dbstructure.tables.Count; i++)
        {
            //к "from" добавляется имя каждой таблицы
            from_query+=dbstructure.tables[i].name + " " + dbstructure.tables[i].shortname;
            if(i < dbstructure.tables.Count-1) from_query+=", ";
            //цикл по полям таблицы
            for (int j = 0; j < dbstructure.tables[i].fields.Count; j++)
            {
                //к "select" добавляется имя каждого поля 
                // вместе с коротким именем таблицы
                select_query += dbstructure.tables[i].shortname + ".";
                select_query += dbstructure.tables[i].fields[j].name + " ";
                if (j < dbstructure.tables[i].fields.Count - 1) select_query += ", ";
                //для каждого внешнего ключа добавляется 
                // соединение таблиц в условие where
                if (dbstructure.tables[i].fields[j].foreign_key_table != "")
                {
                    where_query += dbstructure.tables[i].name + ".";
                    where_query += dbstructure.tables[i].fields[j].name + " = ";
                    where_query += dbstructure.tables[i].fields[j].foreign_key_table + ".";
                    where_query += dbstructure.tables[i].fields[j].foreign_key_field + " and ";
                }
            }
        }
        if (where_query.Length > 6) where_query = where_query.Substring(0, where_query.Length - 5);
        query.Add(select_query);
        query.Add(from_query);
        query.Add(where_query);
        Output.PutResult(query, @"A:\Result\select_all.sql");
    }
    static void Main()
    {
        AllSelectSQL();
    }
}

Пример 3.16.

Во внешнем цикле for делается проход по всем таблицам. В этом цикле одновременно формируются тексты частей запроса, начинающиеся на select, from, update. Причем части запроса, которые начинаются с select и update формируются во вложенном цикле, когда делается проход по полям таблиц. В результате работы программы будет выведен следующий запрос:

select d.id , d.name , d.title , d.revision e.id , e.serial_number , e.model , 
 e.description de.id , de.drawing_id , de.equipment_id 
from drawing d, equipment e, drawing_equipment de
where drawing_equipment.drawing_id = d.id and drawing_equipment.equipment_id = e.id

То же самое можно выполнить шаблонами T4, используя аналогичный код, вывод будет выполняться в результат работы шаблона. Подробно технология шаблонов T4 будет рассмотрена в "четвертой лекции" .

Применение шаблонов XSLT мы рассмотрим подробно в "пятой лекции" . Поэтому, если приведенный ниже пример оказался непонятен, можете вернуться к нему после изучения "пятой лекции" .

Для вывода рассмотренного запроса при помощи XSLT надо добавить следующую строку в указанный выше XML-файл:

<?xml-stylesheet type="text/xsl" href="allselect.xsl"?>

И создать XSLT-стиль со следующим содержимым.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html"/>
  <xsl:template match="/">
    select 
    <xsl:for-each select="tables/table">
      <xsl:variable name="tshort" select="@shortname"/>
      <xsl:for-each select="field">
        <xsl:value-of select="$tshort"/>
        <xsl:text>.</xsl:text>
        <xsl:value-of select="@name"/>
        <xsl:if test="not(position()=last())">, </xsl:if>
      </xsl:for-each>
      <xsl:if test="not(position()=last())">, </xsl:if>
    </xsl:for-each>
    <br/>
    from
    <xsl:for-each select="tables/table">
      <xsl:value-of select="@name"/>
      <xsl:text> </xsl:text>
      <xsl:value-of select="@shortname"/>
      <xsl:if test="not(position()=last())">, </xsl:if>
    </xsl:for-each>
    <br/>
    where
    <xsl:for-each select="tables/table">
      <xsl:variable name="tshort" select="@shortname"/>
      <xsl:for-each select="field">
        <xsl:if test="@foreign_key_table">
          <xsl:value-of select="$tshort"/>
          <xsl:text>.</xsl:text>
          <xsl:value-of select="@name"/>
          <xsl:text> = </xsl:text>
          <xsl:value-of select="@foreign_key_table"/>
          <xsl:text>.</xsl:text>
          <xsl:value-of select="@foreign_key_field"/>
          <xsl:if test="not(position()=last())"> and </xsl:if>
        </xsl:if>
      </xsl:for-each>
    </xsl:for-each>
    <br/>
  </xsl:template>
</xsl:stylesheet>

Пример 3.17.

Результат будет следующим (то есть тем же самым):

select d.id, d.name, d.title, d.revision, e.id, e.serial_number, e.model, 
  e.description, de.id, de.drawing_id, de.equipment_id
from drawing d, equipment e, drawing_equipment de
where de.drawing_id = d.id and de.equipment_id = e.id

Как видим, в XSLT генерация из простых шаблонов получается легче.

Генерация SQL при помощи SQL

Напоследок рассмотрим достаточно редкую технику - это генерация запросов SQL с помощью запросов SQL. Рассмотрим следующий запрос:

select
'Grant select on '||table_name||' to public;'
from user_tables where created > sysdate -1;

Пример 3.18.

В нем из встроенного представления Oracle берутся названия таблиц, созданных не более чем 24 часа назад (берется значение sysdate-1) и генерируются запросы предоставления доступа на чтение всем пользователям. Результат для одной таблицы будет следующего вида:

Grant select on <имя таблицы> to public;

После запуска будет сгенерирован целый список таких запросов. Для выполнения подобной операции на выборочных таблицах без применения генерации пришлось бы писать запросы на каждую таблицу вручную. В данном же случае результат генерации можно сохранить в файле или скопировать и запустить в среде, подобной PLSQL Developer.

Это был пример операции DDL, но можно также генерировать запросы DML. Рассмотрим такой пример:

select
'Insert into all_history(id, object_id, table_name, update_date)'||
'select seq_all_history.nextval, id,'''||table_name||'''||
'from '||table_name||';'
from user_tables where created > sysdate -1;

Пример 3.19.

В данном случае генерируются запросы для вставки в одну таблицу дат обновлений строк из таблиц, созданных не более чем 24 часа назад. Результат будет следующего вида:

Insert into all_history(id, object_id, table_name, update_date)
select seq_all_history.nextval, id,'tbl_name'
from table _name;

Вставляются имя таблицы, дата обновления, идентификатор строки в таблице. Также ведется идентификатор общей таблицы. Сгенерированное множество запросов вставки новых записей в таблицу также может быть сохранено в отдельном файле и запущено.

Замена подстроки во всей базе данных

При работе с базами данных может возникнуть задача по замене одного значения на другое. Причем сделать это надо во всей базе и значение может содержаться во многих таблицах и в разных полях. Примером может быть изменившееся имя сервера или адрес URL, или любая другая "константа" - не меняющееся или очень редко меняющееся значение. Писать вручную запрос для каждого поля из сотен таблиц, где потенциально находится искомое значение, будет весьма затруднительным.

Рассмотрим функцию PL/SQL, которая по заданным названиям таблицы и поля подсчитывает количество строк, в которых это поле содержит искомую подстроку. Применяя эту функцию, мы можем узнать, содержит ли поле значение, которое собираемся заменить, и не генерировать запросы для тех полей, в которых искомая подстрока не содержится.

create or replace function value_exists
        (table_name in varchar2
        ,column_name in varchar2
        ,text in varchar2
        )
return number
as
        cnt number;
begin
        begin
        execute immediate
                'select count(*)'||
                ' from '||table_name||
                ' where lower('||column_name||') like ''%'||lower(text)||'%'''
                into cnt;
        exception when others then
                return 0;
        end;
        return cnt;
end;
/

Пример 3.20.

Как видим, здесь применяется динамический SQL. В нем запрос пред-ставлен в виде текста. К тексту запроса добавляются названия таблицы и поля, а также подстрока поиска. Возвращается число строк, содержащих данную подстроку.

А теперь рассмотрим запрос, генерирующий запросы UPDATE. В нем используется встроенное представление COLS базы Oracle, имеющее поля column_name и table_name. Запросы UPDATE генерируются для тех полей, в которых имеется хотя бы одна строка, содержащая заменяемый текст. Если же в таблице по данному полю нет ни одной такой строки, то запрос на обновление для этого поля не генерируется.

select 'update '|| lower(table_name) || ' set '||lower(column_name)||  ' = replace('||lower(column_name)||','
 'value1'',''value2'') where lower('||lower(column_name)||') like ''%value1%'';'
from cols
where cols.data_type like '%CHAR%'
  and value_exists(table_name, column_name, 'value1') > 0
  order by table_name, column_name

Пример 3.21.

Генерируются запросы, заменяющие одну подстроку на другую. Просматриваются все поля из представления COLS, которые имеют строковый тип. Если в таблице содержится много полей, содержащих заменяемый текст, то для одной этой таблицы будет сгенерировано несколько запросов. Все запросы будут похожими на следующий запрос:

update equipment set description = replace(description,'value1','value2') where lower(description) like '%value1%';

В этом запросе подстрока "value1" заменяется на "value2" в поле description таблицы equipment.

Автоматическое создание запросов средствами самого SQL в некоторых случаях может оказать существенную помощь, особенно если под рукой нет других средств разработки. При этом затраты усилий на создание такого генератора запросов будут минимальными.

Дальше >>

Введение в генерацию программного кода

Введение в генерацию программного кода

Генерация запросов SQL

Генерация запроса на обновление записи таблицы

Генерация запроса на удаление строки таблицы

Генерация запроса на выборку всех полей из нескольких таблиц

Генерация SQL при помощи SQL

Замена подстроки во всей базе данных

Вопросы и ответы

Студенты

Авторизоваться

Введение в генерацию программного кода

Введение в генерацию программного кода

Генерация запросов SQL

Генерация запроса на обновление записи таблицы

Генерация запроса на удаление строки таблицы

Генерация запроса на выборку всех полей из нескольких таблиц

Генерация SQL при помощи SQL

Замена подстроки во всей базе данных

Вопросы и ответы

Студенты