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