Параметры и макросы SQL-запросов

Last updated 7 months ago

Параметры и макросы SQL-запросов и анонимных PL/SQL-блоков

Что такое SQL-запросы и анонимные PL/SQL-блоки?

  • Под SQL-запросами (здесь) понимаются текстовые выражения, начинающиеся со слова 'SELECT' и использующиеся для получения данных в табличной форме.

  • Под анонимными PL/SQL-блоками (здесь) понимаются текстовые выражения, начинающиеся со слов 'DECLARE', 'BEGIN' или 'CALL' и использующиеся для выполнения действий над данными на стороне сервера. Теоретически сюда же относятся все остальные виды выражений, не относящихся к SQL-запросам.

Что такое параметры и макросы SQL-запросов и анонимных PL/SQL-блоков?

  • Под параметрами (здесь) понимаются отдельные слова, начинающиеся с двоеточия. Параметры могут быть входными и выходными. Перед выполнением выражения сервер заменяет эти слова значениями, которые передаются в скрытом виде вместе с текстом выражения. После выполнения выражения сервер заполняет значения выходных параметров для передачи их на сторону клиента. Длина параметра (не включая двоеточия) не должна превышать 30 символов.

  • Под макросами (здесь) понимаются отдельные слова, начинающиеся с амперсанда. Перед отправкой выражения на сервер клиентзаменяет эти слова определенными значениями. Таким образом, на сервер выражение попадает с уже подставленными вместо макросов значениями. Длина макроса (не включая амперсанда) не должна превышать 30 символов.

В каких случаях программа автоматически обрабатывает параметры и макросы SQL-запросов и анонимных PL/SQL-блоков?

  • При выполнении действий (Actions) документов или форм: для каждого свойства SQLx

  • При открытии настраиваемых выпадающих справочников: для свойств SQL и PrepareSQL

  • При открытии выпадающих справочников UNIVERS внутри документа: для свойства SQL_UNIV документа (обычно для инициализации нужных сисконтекстов)

  • При построении печатных форм документов и форм: для свойств SQLHeader, SQLMaster и SQLDetail

  • При построении некоторых отчетов: для свойств SQLHeader, SQLMaster и SQLDetail

Какие типы параметров различает программа?

  • Перед выполнением выражения программа заполняет значения только входных параметров (выходные параметры игнорируются).

  • Информация о типе параметра и способе получения его значения содержится в имени параметра.

  • Для преодоления ограничения на длину имени параметра допускается замена короткого имени, используемого в выражении, на полное. Для этого на отдельной строке выражения (обычно внутри многострочного блока комментариев /* ... */) нужно написать <короткое имя параметра>=<полное имя параметра> - без угловых скобок, разумеется (см. ниже).

  • Параметры с именем RESULT игнорируются.

  • Параметры с именем, начинающимся с символа "\", обрабатываются отдельно (см. ниже).

  • Параметры с именем NRDOC получают значение номера текущего документа.

  • Параметры с именем DATADOC, DOCDATA или DATAMANUAL получают значение даты текущего документа.

  • Параметры с именем NRSET или NRCM получают значение NRSET текущего документа.

  • Параметры с именем DATEBEGIN или DATEFINAL получают соответствующие даты периода построения отчета, а при отсутствии окна отчетов - периода просмотра документов.

  • Параметры с именем, начинающимся на "INI_", получают значение параметра текущего узла настроек (см. ниже).

  • Параметры с именем, начинающимся на "INDOC_", получают значение из текущего документа (метод получения значения определяется типом и содержанием документа) (см. ниже).

  • Параметры с именем, начинающимся на "INFORM_", получают значение из текущей формы (метод получения значения определяется типом и содержанием формы) (см. ниже).

  • Параметры с именем, начинающимся на "EDIT_", получают значение из указанного поля ввода на указанном окне (см. ниже).

  • Параметры с именем, начинающимся на "FIELD_", получают значение из указанного поля указанной таблицы указанного модуля(см. ниже).

  • Параметры с именем, начинающимся на "OUTBDE", помечаются как выходные параметры символьного (строкового) типа с размером буфера 32000 символов. После выполнения действия значение параметра используется для создания таблицы BDE и экспорта в нее соответствующих данных с сервера (см. ниже).

  • Параметры с именем, начинающимся на "OUTOLE", помечаются как выходные параметры символьного (строкового) типа с размером буфера 32000 символов. Параметр должен содержать в себе запрос, позволяющий сделать выгрузку OLE объектов из него, а также имя процедуры, для последовательного подтверждения выгрузки. После выполнения действия предлагается выбрать директорию для сохранения данных с сервера, после выбора которой, начинается процесс копирования, который, в случае чего, можно отменить (см. ниже).

  • Неидентифицированные входные параметры пользователю предлагается заполнить в отдельном окне, появляющемся на экране перед выполнением выражения. Способ ввода значений этих параметров и подписи к ним можно указать на отдельных строках выражения (см. ниже).

Какие типы макросов различает программа?

  • Макросы с именем, начинающимся на BDE# или BDEQ#, заменяются именем глобальной временной таблицы, которая создается для каждого макроса перед выполнением выражения. В эту таблицу загружается содержимое таблицы BDE (см. ниже).

  • Макросы с именем, начинающимся на TXT# или TXTQ#, заменяются именем глобальной временной таблицы, которая создается для каждого макроса перед выполнением выражения. В эту таблицу загружается содержимое текстового файла (см. ниже).

    Примечание

    Варианты макросов BDEQ# и TXTQ# отличаются только тем, что имя таблицы при вставке в текст выражения будет обрамлено кавычками (используется в анонимных PL/SQL блоках - в отличие от SQL-запросов, где имя таблицы должно указываться без кавычек).

Как обрабатываются параметры, начинающиеся с символа "\"?

  • Эти параметры позволяют получить значение любого свойства любой секции настроек программы - достаточно знать название секции и свойства, и в некоторых случаях явно указать используемый раздел настроек.

  • Общий синтаксис параметра выглядит как "\<section>\<value>" или "\<kind>\<section>\<value>", где <kind> - раздел настроек (одно из значений DOCS, REPS, FORMS или SYS), <section> - имя секции, а <value> - имя требуемого свойства.

  • В случае отсутствия явного указания раздела берется текущий (по смыслу) раздел.

Как обрабатываются параметры, начинающиеся с "INI_"?

  • Эти параметры позволяют получить значение любого свойства текущей секции настроек программы - достаточно знать название свойства.

  • Имя параметра строится как INI_<value>, где <value> - имя требуемого свойства.

    Пример:

SELECT * FROM VMS_UNIVERS WHERE TIP='F' AND GR1=:INI_CONTAFX

Как обрабатываются параметры, начинающиеся с "INDOC_"?

  • Эти параметры позволяют получить значение, возвращаемое текущим документом.

  • Имя параметра строится как INDOC_<value>, где <value> - информация для обработки текущим документом.

    Пример:

    Так, стандартный документ 201 позволяет получить доступ к значениям полей master и detail таблиц

SELECT * FROM VMS_UNIVERS WHERE TIP='O' AND GR1='I' AND CODI=:INDOC_M_DTDEP

Как обрабатываются параметры, начинающиеся с "EDIT_"?

  • Эти параметры позволяют получить значение из любого поля ввода, расположенного в любом окне программы.

  • Имя параметра строится как EDIT_<owner>_<edit>, где <owner> - имя окна, а <edit> - имя поля ввода.

  • Получить имя параметра для некоторых полей можно в окне их свойств на вкладке "Other field properties" в поле "Parameter name".

    Пример (доступ к значению поля "Аналит. счет" на панели "Универсального отчета"):

SELECT * FROM VMS_UNIVERS WHERE TIP='M' AND CODI=:SC
/*
SC=edit_fmRG1b_sl12Sc
*/

Как обрабатываются параметры, начинающиеся с "FIELD_"?

  • Эти параметры позволяют получить значение из любого поля любого набора данных (датасета), расположенного в любом окне программы.

  • Имя параметра строится как FIELD_<owner>_<field>, где <owner> - имя окна, а <field> - имя компонента поля, или какFIELD_<owner>_<dataset>_<fieldname>, где <owner> - имя окна, <dataset> - имя компонента датасета, а <fieldname> - имя поля.

  • Получить имя параметра можно при редактировании дизайна в окне свойств столбца на вкладке "Other field properties" в поле "Parameter name".

Как отдельно указать полное имя параметра?

  • Полное имя параметра требуется указывать на отдельной строке в следующих случаях:

  • Когда имя параметра превышает 30 символов

  • Когда имя параметра содержит недопустимые символы (например, "\")

    Пример:

SELECT * FROM VMS_UNIVERS WHERE TIP='F' AND GR1=:CONT
/*
CONT=\SYS\AFX\pkg_afx_Acc5342
*/

Как указать подписи, методы ввода и начальные значения к неидентифицированным параметрам?

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

  • Текст подписи к параметру, не указанный явно, выглядит как имя параметра.

  • Текст подписи к параметру задается свойством вида .text.<paramname>, где <paramname> - имя параметра в тексте выражения.

  • Текст подписи к параметру на одном из трех языков E, M, R задается свойством вида .text.<lang>.<paramname>, где <lang> - код языка (E, M или R), а <paramname> - имя параметра в тексте выражения.

  • Метод ввода значения параметра задается свойством вида .type.<paramname>, где <paramname> - имя параметра в тексте выражения.

  • Допустимые значения свойства .type.<paramname>:

    • NUMBER - разрешается вводить только числовые значения

    • DATE - разрешается вводить только даты

    • LIST,U,<tip>,<gr1> - предлагается выбор из справочника UNIVERS с фильтром <tip> и <gr1> (параметры фильтра необязательны).

    • LIST,S,<tip>,<cod> - предлагается выбор из справочника SYSS с фильтром <tip> и <cod> (параметры фильтра необязательны).

  • Начальное значение параметра задается свойством вида .def.<paramname>, где <paramname> - имя параметра в тексте выражения.

  • Начальное значение параметра может быть либо константой (числом или строкой), либо именем другого параметра (перед именем параметра должно стоять двоеточие).

    Пример задания подписей, методов ввода и начальных значений для нескольких параметров:

begin
:DefDate:=:DATADOC;
raise_application_error(-20000,'Param values:
SimpleString: '''||:SimpleString||'''
AnyNumber: '''||:AnyNumber||'''
AnyDate: '''||:AnyDate||'''
Univers: '''||:Univers||'''
SysS: '''||:SysS||'''
');
end;
/*
Captions:
.text.SimpleString=Простая строка
.text.AnyNumber=Любое число
.text.AnyDate=Любая дата
.text.Univers=Унив. справ.
.text.SysS=Сист. справ.
English captions:
.text.E.SimpleString=Simple string
.text.E.AnyNumber=Any number
.text.E.AnyDate=Any date
.text.E.Univers=Universal list
.text.E.SysS=System list
Types:
.type.AnyNumber=Number
.type.AnyDate=Date
.type.Univers=List,U,O,E
.type.SysS=List,S,F,2
Default values:
.def.SimpleString=Example
.def.AnyNumber=100
.def.AnyDate=:DefDate
*/

Как импортировать данные из таблицы BDE?

  • В тексте выражения нужно ссылаться на таблицу так, как будто она уже импортирована в Oracle, используя вместо имени таблицымакрос специального вида.

  • Имя макроса строится как BDE#<alias>#<table>, где <alias> - имя алиаса BDE, а <table> - имя таблицы BDE (без расширения).

  • Подразумевается стандартное расширение файлов БД Paradox ".DB".

  • При необходимости задать другое расширение файла имя макроса может принимать вид BDE#<alias>#<table>#<ext>, где все - как и раньше, а <ext> - расширение файла таблицы BDE.

  • При обращении к таблице BDE посредством EXECUTE IMMEDIATE ее имя в тексте выражения должно быть обрамлено кавычками. Для этого префикс имени макроса BDE# нужно заменить на BDEQ#.

    Пример:

BEGIN
EXECUTE IMMEDIATE 'DELETE FROM '||&BDEQ#1mPriv#Bank#dbf||' WHERE VALUTA=:VAL'
USING 'LEI';
INSERT INTO TMS_TEST(VALUTA,CURS)SELECT VALUTA,CURS FROM &BDE#1mPriv#Bank#dbf;
END;

Здесь демонстрируются оба вида префикса BDE# и BDEQ#. Несмотря на то, что имена макросов формально разные, в обоих будет использована одна и та же таблица Oracle.

Как импортировать данные из текстового файла?

  • В тексте выражения нужно ссылаться на таблицу так, как будто она уже импортирована в Oracle, используя вместо имени таблицымакрос специального вида.

  • Имя макроса строится как TXT#<id>, где <id> - целочисленный индекс, позволяющий различать разные таблицы.

  • Имя текстового файла можно задать в настройках документа в свойстве OraLoadFile<id>. Если файл не задан или не существует, пользователю будет предложено выбрать файл самостоятельно.

  • Имя таблицы Oracle и ее структура могут быть заданы несколькими способами.

    • Имя таблицы Oracle может быть задано в справочнике TMS_LOAD_INFO:

      • поле ID содержит индекс <id>

      • поле TABLENAME - имя таблицы

      • поле DESCRIPTION - примечание (назначение таблицы)

    • Если имя таблицы не задано, оно будет сформировано как временное.

    • Если имя таблицы Oracle не определено или она не существует, то структура ее столбцов должна быть задана в справочнике TMS_LOAD_STRUCT:

      • поле ID содержит индекс <id>

      • поле COL - порядковый номер столбца

      • поле COLUMNNAME - имя столбца

      • поле TYPE - тип столбца (одна из букв "S", "I", "F" или "D")

      • поле PREC - размер максимального значения столбца (для "D" не используется)

      • поле SCALE - точность значения столбца (используется только для "F")

      • поле FORMAT - формат значения столбца (используется только для "D")

    • Если соответствущая запись в справочнике TMS_LOAD_INFO отсутствует, программа попытается выполнить скрипт, текст которого считывается из настроек документа из MEMO-свойства OraLoadScript<id>, а затем пройти вышеописанные шаги (предполагается, что скрипт заполняет справочники TMS_LOAD_INFO и TMS_LOAD_STRUCT требуемой для импорта информацией).

  • При обращении к таблице Oracle посредством EXECUTE IMMEDIATE ее имя в тексте выражения должно быть обрамлено кавычками. Для этого префикс имени макроса TXT# нужно заменить на TXTQ#.

    Пример:

    Пусть свойство SQL1 какой-то функции документа содержит следующий текст:

BEGIN
EXECUTE IMMEDIATE 'DELETE FROM '||&TXTQ#123||' WHERE NVL(CANT,0)=0'
USING 'LEI';
INSERT INTO TMS_TEST(SC,CANT)SELECT COD,CANT FROM &TXT#123;
END;

В свойствах документа могут быть определены следующие свойства:

OraLoadFile123=C:\Import\barcodes.txt
OraLoadScript123=
BEGIN
INSERT INTO TMS_LOAD_INFO(ID,TABLENAME,DESCRIPTION)
VALUES(123,'','Strih-Cod inventarizatia');
INSERT INTO TMS_LOAD_STRUCT(ID,COL,COLUMNNAME,TYPE,PREC)
VALUES(123,1,'COD','I',10);
INSERT INTO TMS_LOAD_STRUCT(ID,COL,COLUMNNAME,TYPE,PREC,SCALE)
VALUES(123,2,'CANT','F',10,3);
COMMIT;
END;

Здесь демонстрируются оба вида префикса TXT# и TXTQ#. Несмотря на то, что имена макросов формально разные, в обоих будет использована одна и та же таблица Oracle.

Как экспортировать данные в таблицу BDE или в текстовый файл?

  • В тексте выражения нужно использовать параметр с именем, начинающимся на OUTBDE (можно просто OUTBDE).

  • В тексте выражения этому параметру нужно присвоить значение, представляющее собой текст, разделенный на строки при помощи символа CHR(10). Каждая строка имеет вид <property>=<value>, где <property> имя свойства, а <value> - его значение.

  • Источник данных задается свойством source - ему присваивается имя таблицы или представления или текст SQL-запроса. Например: source=SELECT * FROM TMS_SYSS WHERE TIP='S' AND COD=3 AND COD1>0

  • Целевая таблица BDE задается свойством target - его значение должно иметь вид :<alias>:<tablename>, где <alias> - имя алиаса BDE, а <tablename> - имя таблицы BDE. Имя таблицы может включать расширение, отделенное точкой. Например: target=:Bank:bank

  • Тип данных конкретного поля (при необходимости) можно задать свойством type.<fieldname> - ему присваивается строка, состоящая из символа типа и (необязательно) размера поля. Символ типа может быть одним из C, N или D. Размер поля имеет смысл только для типа C, но для типа N можно указать точность. Например: type.DENUMIREA=C50 type.NRDOC=N10 type.SUMA=N14.2 type.DATASTART=D

  • Имеется возможность задать текст сообщения, которое появится при успешном завершении процесса экспорта таблицы - для этого текст сообщения нужно присвоить свойству ShowMessage. При отсутствии текста сообщение показано не будет. Например: ShowMessage=Данные успешно экспортированы в файл D:\Bank\bank.dbf

  • Тип результирующей таблицы определяется в первую очередь расширением имени файла таблицы:

    • .DB - формат PARADOX (формат по умолчанию)

    • .DBF - формат DBASE (формат старых приложений dBase IV и FoxPro)

    • .TXT - формат ASCII (формат текстовых файлов)

  • Если расширение имени файла таблицы не указано явно, тип таблицы (и соответствующее ему расширение имени файла) определяется настройками алиаса BDE - значением свойства "DEFAULT DRIVER":

    • PARADOX - формат Paradox (расширение ".db")

    • DBASE - формат dBase IV (расширение ".dbf")

    • FOXPRO - формат FoxPro (расширение ".dbf")

    • ASCIIDRV - формат ASCII (расширение ".txt")

  • В случае формата ASCII на самом деле создаются два файла с одинаковыми именами и с расширениями ".txt" и ".sch". В первый экспортируются данные, а во втором сохраняется структура столбцов исходной таблицы или запроса.

    Пример:

begin
:outbde:='target=:1mDBF:testsyss.dbf'||chr(10)||
'source=SELECT * FROM TMS_SYSS WHERE TIP=''S'' AND COD=3 AND COD1>0';
end;

Как экспортировать OLE данные из таблицы на локальный компьютер?

  • В тексте выражения нужно использовать параметр с именем, начинающимся на OUTOLE (можно просто OUTOLE).

  • В тексте выражения этому параметру нужно присвоить значение, представляющее собой текст, разделенный на строки при помощи символа CHR(10). Каждая строка имеет вид <property>=<value>, где <property> имя свойства, а <value> - его значение.

  • Источник данных задается свойством source - ему присваивается имя таблицы или представления или текст SQL-запроса. Например: source=SELECT * FROM TMS_SYSS WHERE TIP='S' AND COD=3 AND COD1>0

  • Процедура, отвечающая за последовательное логирование выгрузки объектов задается свойством procedure Например: procedure=pkg_fs_util.submit_export_ole

    Пример:

begin
:outole := 'procedure=pkg_fs_util.submit_export_ole'||chr(10)||
'source=select nrdoc, nrdoc1, filename_prefix, dir_path, oleobj from tmdb_docs_ole'
end;