Язык-хранимых-процедур-Getl.md

Содержание

Описание
Переменные в скрипте
Оператор SET
Оператор ECHO
Блочные операторы
Оператор ERROR
Оператор EXIT
Оператор RUN_FILE
Выполнение текста скрипта из кода
Выполнение файла скрипта из кода

Описание

Для построения консолидированного слоя и разработки аналитических витрин Getl предлагает собственный язык хранимых процедур (ХП), реализуемый в операторе “sql” на языке Dsl или вызываемый через класс getl.jdbc.SQLScripter.

В языке ХП поддерживается:

Поочередное выполнение SQL операторов, разделенных точкой с запятой;
Работа с переменными внутри скрипта;
Установка значений переменных перед выполнением SQL скрипта и получение их значений после выполнения в коде;
Получение в переменную количества обработанных записей после выполнения DML оператора SQL;
Команда SET для получения значений в переменные из полей записи;
Команда IF для выполнения блока команд по условию;
Команда FOR для выполнения блока команд в цикле;
Команда COMMAND для выполнения блока команд в оригинальном виде без обработки языком ХП;
Команда ERROR для аварийного завершения работы SQL скрипта с указанной ошибкой;
Команда EXIT для выхода из скрипта с указанным кодом;
Команда ECHO для вывода текста в консоль лога;
Команда LOAD_POINT для загрузки последнее сохраненное значение указанного инкремента в заданную переменную;
Команда SAVE_POINT для записи последнего максимального значения с заданной переменной в указанный инкремент.

Работа с переменными в скриптах

При выполнении ХП Getl вместо {переменных} или ${переменных} подставляет их значения, которые заданы в свойстве vars sql оператора по именам переменных. Помимо имени переменной, внутри фигурных скобок можно задавать текстовые выражения с использованием множества переменных, которые будут подставляться в код скрипта, если используемых в них переменные объявлены в vars и не равны null. В таком случае переменные должны обрамляться знаком процента. В случае, если внутри выражения требуется использовать сам символ процента, то его надо экранировать обратной косой чертой (escape символ).

Ниже пример вернет записи с полем, имя которого задано в переменной field1 с таблицы, для которого имя задано в переменной table1 с подстановкой условия, если были заданы переменные start и finish:

SELECT {field1}
FROM ${table1}
{WHERE name LIKE 'a\%' AND dt BETWEEN '%start%' AND '%finish%'}

При использовании текстовых переменных внутри скриптов следует их обрамлять в одинарные кавычки. Для переменных с типом дата-время Getl подставляет текстовое значение в формате “yyyy-MM-dd HH:mm:ss”.

Если перед DML оператором поставить комментарий в формате “/:переменная/”, то количество обработанных записей будет занесено в эту переменную и она после выполнения оператора будет далее доступна для следующих операторов скрипта и в свойстве vars:

/*:count_inserted*/
INSERT INTO table1 SELECT * FROM table2;
ECHO Было вставлено {count_inserted} записей.

Установка значений переменным внутри SQL скрипта

Для установки в переменные значений используется оператор “SET SELECT Значение AS Переменная”. В SELECT можно использовать любой SQL запрос, но он должен возвращать одну запись. Поля этой записи и будут добавлены как переменные в свойство vars и видны далее в выполняемом SQL скрипте:

SET SELECT Upper(name) AS name FROM table WHERE id = {id};
ECHO Было получено имя {name}

Вывод сообщений выполняемого SQL скрипта в консоль

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

ECHO Тестовое сообщение {num} {от %fio%}

Блоковые операторы SQL скрипта

В языке ХП поддерживается три оператора, которые работают с блоком команд. Указанные внутри блоков скрипты видят все переменные, которые были объявлены в главном скрипте, но все переменные, которые они объявляют у себя внутри кода, включая переменные главного скрипта имеют локальную видимость и не изменяют значения переменных главного скрипта.

Оператор COMMAND

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

-- Все команды уходят на сервер одним скриптом
COMMAND {
  INSERT INTO table VALUES ({id}, '{name}');
  COMMIT;
}

Оператор IF

Позволяет выполнить блок команд только при выполнении заданного условия. Условие должно указываться в круглых скобках и возвращать true или false.

IF ({id} IN (SELECT id FROM table)) DO {
  ECHO Выполнение блока команд
}

Оператор FOR

Позволяет выполнить блок команд для каждой записи указанного запроса. Поля записей запроса передаются в блок команд как переменные:

FOR (SELECT id, name FROM table ORDER BY id) DO {
  ECHO Обработка записи с кодом {id} и именем {name}
}

P.S. При обработке записей в цикле, оператор FOR сначала получит в память весь массив записей и потом начнем по нему обработку. Не рекомендуется возвращать в SELECT большое количество записей.

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

Если в ходе выполнения SQL скрипта требуется остановить его выполнение и передать ошибку выше, используется оператор ERROR. Как и оператор ECHO, он не требует завершения точкой с запятой:

IF ((SELECT max(last_time) FROM table) > Now()) DO {
  ERROR Время последнего результата больше текущего времени!
}

Если скрипт завершается ошибкой, сгенерированной оператором “ERROR”, то текст ошибки можно получить в свойстве “errorText” объекта SQLScripter. При этом в свойстве “exitCode” будет значение -1. Если выполнение скрипта было прервано ошибкой на стороне выполняющего скрипт сервера БД, то свойство “errorText” будет содержать null, а в “exitCode” будет значение -2.

Принудительное завершение работы SQL скрипта

Если в ходе выполнения SQL скрипта требуется остановить его выполнение без ошибки, используется оператор EXIT:

IF ((SELECT max(last_time) FROM table) > Now()) DO {
  EXIT;
}

Код возврата можно получить в свойстве “exitCode” объекта SQLScripter. Если код возврата в операторе не указан, то по умолчанию оператор EXIT вернет 0.

Если вызванный с помощью оператора “RUN_FILE” скрипт прекратил выполнение оператором “EXIT” с кодом, то его можно получить в вызывающем скрипте с помощью переменной “sql_exit_code”:

ECHO Вызов подчиненного скрипта ...
RUN_FILE sql/script1.sql;
IF ({sql_exit_code} = 1) DO {
  ECHO Дальнейшее выполнение скрипта не требуется.
  EXIT;
}

Выполнение SQL скрипта из другого файла

Если требуется выполнить из одного SQL скрипта команды, которые сохранены в другом файле, используется оператор RUN_FILE:

-- Вызвать выполнение команд файла по абсолютному пути Windows:
RUN_FILE c:/sql/file1.sql
-- Вызвать выполнение команд файла по абсолютному пути Unix:
RUN_FILE /sql/file1.sql
-- Вызвать выполнение команд файла с использованием переменной окружения:
RUN_FILE {SQL_PATH}/file1.sql
-- Вызвать выполнение команд файла, который находится в репозитории:
RUN_FILE repository:/sql/file1.sql

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

Выполнение текста SQL скрипта из кода

Для выполнения ХП скриптов используется метод exec в операторе sql:

// Выполнить SQL скрипт
sql {
  // Использовать указанное соединение Vertica
  useConnection verticaConnection('ver:con1')

  // Установить значение переменным для скрипта
  vars.param1 = 1
  vars.param2 = 'ok'
  vars.param3 = new Date()

  // Выполнить скрипт из текста с включенным языком ХП
  exec true, '''
/*:count_row*/ 
UPDATE schema1.table1 
SET field4 = '{param3}'::timestamp 
WHERE field2 = {param1} AND field1 = '{param2}'
    '''
  // Проверить полученное значение переменной из скрипта
  assert vars.count_row == 1, 'Не удалось обновить запись!'
}

Выполнение SQL скрипта из указанного файла в коде

Скрипты можно выносить в отдельные файлы, в том числе ресурсные и вызывать их с помощью метода runFile в операторе sql.

Пример файла “/sql/script.sql”, который располагается в ресурсах проекта:

-- Вывести сообщение на консоль
ECHO Расчет региона {regionid} ...

-- Получение периода расчета в переменные по именам полей из SELECT
SET SELECT Min(hour) as hour_start, Max(hour) as hour_finish, Count(*) AS count_rows
    FROM schema1.fact1
    WHERE regionid = {regionid} AND status = 'incomplete';

-- Проверить, что нет записей для обработки или данные старше заданной даты
IF ({count_rows} = 0 OR '{hour_finish}'::timestamp < '2020-01-01'::timestamp) DO {
    ECHO 'Записей для расчета не найдено!'
    // Прекращение работы SQL скрипта
    EXIT;
}

ECHO Выявлено {count_rows} записей с {hour_start} по {hour_finish}

-- Провести расчет в цикле по часам региона, где будет созданы переменные по именам полей SELECT
FOR SELECT hour
    FROM schema1.fact1
    WHERE regionid = {regionid} AND hour BETWEEN '{hour_start}'::timestamp AND '{hour_finish}'::timestamp DO {

  UPDATE schema1.fact1
  SET status = 'complete'
  WHERE regionid = {regionid} AND hour = '{hour}'::timestamp

  ECHO Час {hour} успешно рассчитан.
}

Пример вызова ресурсного SQL файла:

// Выполнить SQL скрипт
sql {
  // Использовать указанное соединение
  useConnection verticaConnection('ver:con1')

  // Установить значение переменным скрипта
  vars.regionid = 1

  // Выполнить скрипт из ресурсного файла с включенным парсингом языка ХП
  runFile true, 'resource:/sql/script.sql'

  logInfo "Было обработано ${vars.count_rows} записей"
}