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

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

< Лекция 3 || Лекция 4: 12 || Лекция 5 >

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

Теперь рассмотрим генерацию запросов с применением оператора 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 в некоторых случаях может оказать существенную помощь, особенно если под рукой нет других средств разработки. При этом затраты усилий на создание такого генератора запросов будут минимальными.

< Лекция 3 || Лекция 4: 12 || Лекция 5 >
Дмитрий Клочков
Дмитрий Клочков
Россия, Рубцовск
Волков Олег
Волков Олег
Украина, Днепропетровск