Orafce

Это расширение QHB реализует функции и операторы, которые эмулируют подмножество функций и пакетов из Oracle RDBMS.

Обратите внимание, что эта версия orafce работает с QHB версии 1.5.1 и выше.


Установка

Функции совместимости и пакеты Oracle для QHB поставляется в виде пакета qhb-1.5.2-orafce.

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


Функции и пакеты Oracle

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

Встроенные функции даты Oracle были протестированы на совместимость с Oracle 10. Диапазоны дат с 1960 по 2070 год работают корректно. Даты до 1100-03-01 не могут быть проверены из-за ошибки в Oracle.

Все функции полностью совместимы с Oracle и учитывают все известные строки формата.


Список строк формата для функций trunc, round

Y,YY,YYY,YYYY,SYYY,SYEAR      год
I,IY,IYY,IYYY			            недельный год по ISO 8601
Q				                      квартал
WW				                    неделя, начиная с первого дня года
IW				                    неделя, начиная с понедельника
W				                      неделя, начиная с первого дня месяца
DAY,DY,D			                первый день недели, воскресенье
MONTH,MON,MM,RM			          месяц
CC,SCC				                век
DDD,DD,J			                день
HH,HH12,HH24			            час
MI				                    минута

Функции округляются. То есть дата 1 июля будет округлена до следующего года. 16 июля округляется до августа.


Функции даты

Функция
Описание
Пример(ы)
add_months ( date, integer ) → date
Возвращает дату плюс n месяцев.
add_months(date '2005-05-31',1) → 2005-06-30
last_day ( date ) → date
Возвращает последний день месяца на основе значения даты.
last_day(date '2005-05-24') → 2005-05-31
next_day ( date, text ) → date
Возвращает первый день недели, который больше значения даты.
next_day(date '2005-05-24', 'monday') → 2005-05-30
next_day ( date, integer ) → date
То же, что и выше. Второй аргумент должен быть 1..7 и интерпретироваться как воскресенье..суббота.
next_day(date '2005-05-24', 1) → 2005-05-30
months_between ( date, date ) → numeric
Возвращает количество месяцев между датой1 и датой2. Если вычисляется дробный месяц, функция months_between вычисляет дробь на основе 31-дневного месяца.
months_between(date '1995-02-02', date '1995-01-01') → 1.0322580645161
trunc ( date, text ) → date
Обрезает дату в соответствии с указанным форматом.
trunc(date '2005-07-12', 'iw') → 2005-07-11
round ( date, text ) → date
Округляет дату в соответствии с указанным форматом.
round(date '2005-07-12', 'yyyy') → 2006-01-01
to_date ( text ) → timestamp
Приводит вводимый текста к отметке времени. GUC orafce.nls_date_format используется для указания формата входного текста для этой функции. Если значение оставлено пустым или установлено как default (формат по умолчанию), тогда введите текстовый формат в соответствии с настройкой стиля даты GUC QHB.
orafce.nls_date_format value to DEFAULT

to_date('2014-05-19 17:23:53+5:30') -> 2014-05-19 17:23:53

orafce.nls_date_format='YYYY-MMDD HH24:MI:SS'

to_date('2014-0519 17:23:53+5:30')  -> 2014-05-19 17:23:53

Тип данных oracle.date

Этот модуль содержит реализацию совместимого с Oracle типа данных date, «oracle.date», и функций, использующих тип данных date, таких как oracle.add_months, oracle.last_day(), oracle.next_day(), oracle.months_between() и т. д.

Пример:

SET search_path TO oracle,"$user", public, pg_catalog;
CREATE TABLE oracle_date(col1 date);
INSERT INTO oracle_date values('2014-06-24 12:12:11'::date);
SELECT * FROM oracle_date;
        col1
---------------------
 2014-06-24 12:12:11
(1 row)

Функции oracle.date

Функция
Описание
Пример(ы)
oracle.add_months ( timestamp with time zone, integer ) → timestamp with time zone
Возвращает дату и время плюс n месяцев.
oracle.add_months(oracle.date'2005-05-31 10:12:12',1) → 2005-06-30 10:12:12
oracle.last_day ( timestamp with time zone ) → timestamp with time zone
Возвращает последний день месяца на основе значения даты.
oracle.last_day(oracle.date '2005-05-24 11:12:12') → 2005-05-31 11:12:12
oracle.next_day ( timestamp with time zone, text ) → timestamp with time zone
Возвращает первый день недели, который больше значения даты.
oracle.next_day(oracle.date '2005-05-24 10:12:12', 'monday') → 2005-05-30 10:12:12
oracle.next_day ( timestamp with time zone, integer ) → timestamp with time zone
То же, что и выше. Второй аргумент должен быть 1..7 и интерпретироваться как воскресенье..суббота.
oracle.next_day(oracle.date '2005-05-24 11:21:12', 1) → 2005-05-29 11:21:12
oracle.months_between ( timestamp with time zone, timestamp with time zone ) → numeric
Возвращает количество месяцев между меткой времени1 и меткой времени2. Если вычисляется дробный месяц, функция months_between вычисляет дробь на основе 31-дневного месяца.
oracle.months_between(oracle.date '1995-02-02 10:00:00', oracle.date '1995-01-01 10:21:11') → 1.03225806451613
oracle.to_date ( text, text ) → timestamp without time zone
Возвращает метку времени без часового пояса.
oracle.to_date('02/16/09 04:12:12', 'MM/DD/YY HH24:MI:SS') → 2009-02-16 04:12:12
oracle.to_date ( text ) → oracle.date
Возвращает oracle.date.
oracle.to_date('16.02.09 04:12:12') → 2009-02-16 04:12:12
oracle.sysdate() → timestamp with time zone
Возвращает временную метку оператора в часовом поясе сервера (orafce.timezone).
oracle.sysdate() → 2015-12-09 17:47:56
oracle.dbtimezone() → timezone
Возвращает часовой пояс сервера — эмулируется через orafce.timezone.
oracle.dbtimezone() → GMT
oracle.sessiontimezone() → timezone
Возвращает часовой пояс сеанса — текущий часовой пояс QHB.
oracle.sessiontimezone() → Europe/Prague
oracle.to_char ( timestamp ) → nls_date_format
Возвращает временную метку в формате nls_date_format.
orafce.nls_date_format='YY-MonDD HH24:MI:SS'

oracle.to_char(to_date('14-Jan08 11:44:49+05:30')) -> 14-Jan08 11:44:49

orafce.nls_date_format='YY-MonDD HH24:MI:SS'

oracle.to_char(oracle.to_date('21052014 12:13:44+05:30','DDMMYYYY HH24:MI:SS')) -> 14-May21 12:13:44

Операторы oracle.date

Оператор
Описание
Пример(ы)
oracle. + ( oracle.date, smallint ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::smallint → 2014-07-11 10:08:55
oracle. + ( oracle.date, integer ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::integer → 2014-07-11 10:08:55
oracle. + ( oracle.date, bigint ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::bigint → 2014-07-11 10:08:55
oracle. + ( oracle.date, numeric ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::numeric → 2014-07-11 10:08:55
oracle. - ( oracle.date, smallint ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::smallint → 2014-06-23 10:08:55
oracle. - ( oracle.date, integer ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::integer → 2014-06-23 10:08:55
oracle. - ( oracle.date, bigint ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::bigint → 2014-06-23 10:08:55
oracle. - ( oracle.date, numeric ) → oracle.date
Возвращает oracle.date.
oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::numeric -> 2014-06-23 10:08:55
oracle. - ( oracle.date, oracle.date ) → double precision
Возвращает двойную точность.
oracle.to_date('2014-07-17 11:10:15', 'yyyy-mm-dd hh24:mi:ss') - oracle.to_date('2014-02-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss') → 166.048785

Вам нужно установить search_path TO oracle,"$user", public, pg_catalog, потому что такие функции, как oracle.add_months, oracle.last_day, oracle.next_day и oracle.months_between, устанавливаются бок о бок с функциями pg_catalog.add_months, pg_catalog.last_day, pg_catalog.next_day и pg_catalog.months_between.


«Двойная» таблица

QHB не нуждается в «двойной» таблице Oracle, но поскольку она интенсивно используется пользователями Oracle, она была добавлена ​​в orafce. Эта таблица находится в схеме oracle. Обычно имеет смысл разрешить к ней широкий доступ, поэтому следует добавить эту схему в конфигурацию search_path (как search_path = 'oracle, pg_catalog, "$user", public' в qhb.conf).


Пакет dbms_output

QHB отправляет информацию клиенту через RAISE NOTICE. Oracle использует dbms_output.put_line(). Это работает иначе, чем RAISE NOTICE. В Oracle имеется очередь сеанса, функция put_line() добавляет строку в очередь, а функция get_line() читает из очереди. Если установлен флаг serveroutput, то клиент по всем операторам sql читает очередь. Вы можете использовать:

SELECT dbms_output.enable();
SELECT dbms_output.put_line('first_line');
SELECT dbms_output.put_line('next_line');
SELECT * FROM dbms_output.get_lines(0);

или

SELECT dbms_output.enable();
SELECT dbms_output.serveroutput('t');
SELECT dbms_output.put_line('first_line');

Этот пакет содержит следующие функции: enable(), disable(), serveroutput(), put(), put_line(), new_line(), get_line(), get_lines(). Очередь пакетов реализована в локальной памяти сеанса.


Пакет utl_file

Этот пакет позволяет программам PL/pgSQL читать и записывать любые файлы, доступные с сервера. Каждый сеанс может открыть максимум десять файлов, а максимальный размер строки составляет 32 КБ. Этот пакет содержит следующие функции:

Функция
Описание
utl_file.fclose ( file utl_file.file_type)
Закрывает файл.
utl_file.fclose_all()
Закрывает все файлы.
utl_file.fcopy ( src_location text, src_filename text, dest_location text, dest_filename text [, start_line text ] [, end_line text ] )
Копирует текстовый файл.
utl_file.fflush ( file utl_file.file_type )
Сбрасывает все данные из буферов.
utl_file.fgetattr ( location text, filename text )
Выводит атрибуты файла.
utl_file.fopen ( location text, filename text, file_mode text [, maxlinesize int ] [, encoding name ] ) → utl_file.file_type
Открывает файл.
utl_file.fremove ( location text, filename text )
Удаляет файл.
utl_file.frename ( location text, filename text, dest_dir text, dest_file text [, overwrite ])
Переименовывает файл.
utl_file.get_line ( file utl_file.file_type) → text
Считывает одну строку из файла.
utl_file.get_nextline ( file utl_file.file_type) → text
Считывает одну строку из файла или возвращает NULL.
utl_file.is_open ( file utl_file.file_type) → bool
Возвращает true, если файл открыт.
utl_file.new_line ( file utl_file.file_type [, rows int ])
Помещает в файл несколько новых строковых символов.
utl_file.put ( file utl_file.file_type, buffer text )
Помещает буфер в файл.
utl_file.put_line ( file utl_file.file_type, buffer text )
Помещает строку из буфера в файл.
utl_file.putf ( file utl_file.file_type, buffer format [, arg1 text ] [, arg2 text ] [..] [, arg5 text ])
Помещает форматированный текст в файл.
utl_file.tmpdir()
Выводит путь к временному каталогу.

Поскольку QHB не поддерживает вызов по ссылке, некоторые функции немного отличаются: fclose и get_line.

do $$
declare f utl_file.file_type;
begin
  f := utl_file.fopen('/tmp', 'sample.txt', 'r');
  <<readl>>
  loop
    begin
      raise notice '%', utl_file.get_line(f);
    exception
      when no_data_found then
        exit readl;
    end;
  end loop;
  f := utl_file.fclose(f);
end;
$$;

или второй (со специальной функцией QHB get_nextline)

do $$
declare
  f utl_file.file_type;
  line text;
begin
  f := utl_file.fopen('/tmp', 'sample.txt', 'r');
  loop
    line := utl_file.get_nextline(f);
    exit when line is NULL;
    raise notice '%', line;
  end loop
exception
  when others then
  perform utl_file.fclose_all();
end;
$$;

Перед использованием пакета вы должны установить таблицу utl_file.utl_file_dir. Она содержит все разрешенные каталоги без завершающего символа ('/' или '\'). На платформе WinNT пути должны каждый раз заканчиваться символом '\'.

Записи каталога могут быть названы (второй столбец в таблице utl_file.utl_file_dir). Параметр location может быть либо именем каталога, либо путем к словарю. Расположение сначала интерпретируется и проверяется как имя каталога. Если не найден (во 2-м столбце), то расположение интерпретируется и проверяется как путь.

Функции из пакета utl_file (схема в QHB) требуют доступа к таблице utl_file.utl_file_dir. Этот факт можно использовать для контроля того, могут ли пользователи использовать эти функции или нет. Значение по умолчанию — READ для public. INSERT и UPDATE может делать только привилегированный пользователь (суперпользователь). Таким образом, непривилегированный пользователь может использовать функции из этого пакета, но не может изменять список безопасных каталогов (содержимое таблицы utl_file.utl_file_dir). Содержимое этой таблицы видно для public (или должно быть видно для пользователей, использующих функции из этого пакета).


Пакет dbms_sql

Данный пакет — реализация Oracle API пакета DBMS_SQL.

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

Внимание: архитектура QHB отличается от архитектуры Oracle. PL/pgSQL выполняется в том же контексте, что и механизм SQL. Тогда нет никакой причины использовать шаблоны Oracle, такие как массовый сбор и итерация по сбору в QHB, для получения хорошей производительности. Этот код предназначен для сокращения работы, связанной с переносом некоторых приложений с Oracle на QHB, и может работать хорошо. Но не будет никакого преимущества в производительности по сравнению со встроенными операторами PL/pgSQL. Эмуляция API Oracle требует дополнительных ресурсов памяти и ЦП, которые могут быть значительными при больших объемах данных.


Функциональность

Это расширение реализует подмножество интерфейса Oracle dbms_sql. Целью этого расширения является не совместимость с Oracle, оно предназначено для сокращения объема работы, связанной с миграцией приложений Oracle на QHB. Поддерживаются некоторые базовые функции массового DML:

do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
  a := ARRAY[1, 2, 3, 4, 5];
  b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
  ca := ARRAY[3.14, 2.22, 3.8, 4];

  call dbms_sql.bind_array(c, 'a', a, 2, 3);
  call dbms_sql.bind_array(c, 'b', b, 3, 4);
  call dbms_sql.bind_array(c, 'c', ca);
  raise notice 'inserted rows %d', dbms_sql.execute(c);
end;
$$;

do $$
declare
  c int;
  a int[];
  b varchar[];
  ca numeric[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)');
  call dbms_sql.define_array(c, 1, a, 10, 1);
  call dbms_sql.define_array(c, 2, b, 10, 1);
  call dbms_sql.define_array(c, 3, ca, 10, 1);

  perform dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c) > 0
  loop
    call dbms_sql.column_value(c, 1, a);
    call dbms_sql.column_value(c, 2, b);
    call dbms_sql.column_value(c, 3, ca);
    raise notice 'a = %', a;
    raise notice 'b = %', b;
    raise notice 'c = %', ca;
  end loop;
  call dbms_sql.close_cursor(c);
end;
$$;

Имеется функция dbms_sql.describe_columns_f, то есть процедура dbms_sql.describe_columns. Обратите внимание, что идентификаторы типов относятся к системе типов QHB. Значения не конвертируются в числа Oracle.

do $$
declare
  c int;
  r record;
  d dbms_sql.desc_rec;
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  r := dbms_sql.describe_columns(c);
  raise notice '%', r.col_cnt;

  foreach d in array r.desc_t
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
end;
$$;

do $$
declare
  c int;
  n int;
  d dbms_sql.desc_rec;
  da dbms_sql.desc_rec[];
begin
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  call dbms_sql.describe_columns(c, n, da);
  raise notice '%', n;

  foreach d in array da
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
end;
$$;

Пакет dbms_pipe

Этот пакет является эмуляцией пакета Oracle dbms_pipe. Обеспечивает межсеансовую связь. Вы можете отправлять и читать любое сообщение с ожиданием или без него, просмотреть список активных каналов, установить канал как частный или общедоступный и использовать явные или неявные каналы.

Максимальное количество каналов: 50.

Разделяемая память используется для отправки сообщений.

Ниже приведен пример:

-- Сеанс A
SELECT dbms_pipe.create_pipe('my_pipe',10,true); -- явное создание канала
SELECT dbms_pipe.pack_message('neco je jinak');
SELECT dbms_pipe.pack_message('anything is else');
SELECT dbms_pipe.send_message('my_pipe',20,0); -- изменение предела и передача без ожидания
SELECT * FROM dbms_pipe.db_pipes; -- список текущих каналов

-- Сеанс B
SELECT dbms_pipe.receive_message('my_pipe',1); -- ожидание для сообщения максимум 1 секунда
SELECT dbms_pipe.next_item_type(); -- -> 11, текст
SELECT dbms_pipe.unpack_message_text();
SELECT dbms_pipe.next_item_type(); -- -> 11, текст
SELECT dbms_pipe.unpack_message_text();
SELECT dbms_pipe.next_item_type(); -- -> 0, больше элементов нет
SELECT dbms_pipe.remove_pipe('my_pipe');

Однако есть некоторые отличия от Oracle:

  • предел для каналов не в байтах, а в элементах в канале
  • вы можете отправить сообщение без ожидания
  • вы можете отправлять пустые сообщения
  • next_item_type знает о timestamp (тип 13)
  • QHB не знает о типе raw, вместо этого используйте bytea

Пакет dbms_alert

Еще одно средство межпроцессного взаимодействия.

-- Сеанс A
SELECT dbms_alert.register('boo');
SELECT * FROM dbms_alert.waitany(10);

-- Сеанс B
SELECT dbms_alert.register('boo');
SELECT * FROM dbms_alert.waitany(10);

-- Сеанс C
SELECT dbms_alert.signal('boo','Nice day');

Пакет PLVdate

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

Вы должны определить свои собственные нерабочие дни (максимум 50 дней) и собственные праздники (максимум 30 дней). Выходной день — это любой нерабочий день, который одинаков каждый год. Например, Рождество в западных странах.


Функции

Функция
Описание
plvdate.add_bizdays ( day date, days int ) → date
Выводит дату, созданную путем добавления n рабочих дней к заданной.
plvdate.nearest_bizday ( day date ) → date
Выводит рабочую дату, ближайшую к заданной дате.
plvdate.next_bizday ( day date ) → date
Выводит следующую рабочую дату с заданной даты.
plvdate.bizdays_between ( day1 date, day2 date ) → int
Выводит количество рабочих дней между двумя заданными датами.
plvdate.prev_bizday ( day date ) → date
Выводит предыдущую рабочую дату с заданной даты.
plvdate_isbizday ( date ) → bool
Возвращает true, если заданная дата является рабочим днем.
plvdate.set_nonbizday ( dow varchar )
Устанавливает заданный день недели как нерабочий день.
plvdate.unset_nonbizday ( dow varchar )
Отменяет установку заданного дня недели как нерабочего.
plvdate.set_nonbizday ( day date )
Устанавливает заданный день как нерабочий.
plvdate.unset_nonbizday ( day date)
Отменяет установку заданного дня как нерабочего.
plvdate.set_nonbizday ( day date, repeat bool )
Устанавливает заданный день как нерабочий; если repeat равно true, то день будет нерабочим каждый год.
plvdate.unset_nonbizday ( day date, repeat bool )
Отменяет установку заданного дня как нерабочего; если repeat равно true, то установка отменяется и на последующие годы.
plvdate.use_easter()
Пасхальное воскресенье и Пасхальный понедельник будут выходными.
plvdate.unuse_easter()
Пасхальное воскресенье и Пасхальный понедельник не будут выходными.
plvdate.use_easter ( use bool )
Если true, то Пасхальное воскресенье и Пасхальный понедельник будут выходными.
plvdate.using_easter() → bool
Возвращает true, если Пасхальное воскресенье и Пасхальный понедельник являются выходными.
plvdate.use_great_friday()
Страстная пятница будет выходным.
plvdate.unuse_easter()
Страстная пятница не будет выходным.
plvdate.use_easter ( use bool )
Если true, то Страстная пятница будет выходным.
plvdate.using_easter() → bool
Возвращает true, если Страстная пятница является выходным.
plvdate.include_start()
Включает в расчет bizdays_between дату начала.
plvdate.noinclude_start()
Исключает из расчета bizdays_between дату начала.
plvdate.include_start (use bool)
Если true, то в расчет bizdays_between включается дата начала.
plvdate.include_start() → bool
Возвращает true, если в расчет bizdays_between включена дата начала.
plvdate.default_holidays ( varchar )
Загружает конфигурации по умолчанию. На данный момент доступны следующие конфигурации: Чехия, Германия, Австрия, Польша, Словакия, Россия, Великобритания и США. Конфигурация содержит только общие праздники для всех регионов. Свой региональный праздник можно добавить с помощью plvdate.set_nonbizday(nonbizday, true).

Пример:

qhb=# select plvdate.default_holidays('czech');
 default_holidays
 -----------------

(1 row)
qhb=# select to_char(current_date, 'day'),
           plvdate.next_bizday(current_date),
	   to_char(plvdate.next_bizday(current_date),'day');
  to_char  | next_bizday |  to_char
 ----------+-------------+-----------
 saturday  | 2006-03-13  | monday
(1 row)

Изменение для неевропейской среды:

SELECT plvdate.unset_nonbizday('saturday');
SELECT plvdate.unset_nonbizday('sunday');
SELECT plvdate.set_nonbizday('friday');
SELECT plvdate.set_nonbizday('2006-05-19', true);
SELECT plvdate.unuse_easter();

Пакет PLVstr и PLVchr

Этот пакет содержит несколько полезных строковых и символьных функций. Каждая функция поддерживает положительные и отрицательные смещения, т. е. смещение как от начала, так и от конца строки. Например:

plvstr.left('abcdef',2) -> ab
plvstr.left('abcdef',-2) -> abcd
plvstr.substr('abcdef',1,1) -> a
plvstr.substr('abcdef',-1,1) -> f
plvstr.substr('abcde',-2,1) -> d

Список функций:

Функция
Описание
plvstr.normalize ( str text )
Нормализует заданную строку: заменяет пустые символы на пробел и несколько пробелов на один.
plvstr.is_prefix ( str text, prefix text, cs bool ) → bool
Возвращает true, если заданный префикс является префиксом указанной строки.
plvstr.is_prefix ( str text, prefix ) → bool
Возвращает true, если заданный префикс является префиксом указанной строки.
plvstr.is_prefix ( str int, prefix int ) → bool
Возвращает true, если заданный префикс является префиксом указанной строки.
plvstr.is_prefix ( str bigint, prefix bigint ) → bool
Возвращает true, если заданный префикс является префиксом указанной строки.
plvstr.substr ( str text, start int, len int ) → text
Извлекает из заданной строки подстроку, начинающуюся с символа на позиции start и длиной в len символов.
plvstr.substr ( str text, start ) → text
Извлекает из заданной строки подстроку, начинающуюся с символа на позиции start.
plvstr.instr ( str text, patt text, start int, n int ) → text
Ищет n-й заданный шаблон в указанной строке, начиная с символа на позиции start.
plvstr.instr ( str text, patt text, start int ) → text
Ищет заданный шаблон в указанной строке, начиная с символа на позиции start.
plvstr.instr ( str text, patt text ) → text
Ищет заданный шаблон в указанной строке.
plvstr.lpart ( str text, div text, start int, nth int, all_if_notfound bool ) → text
Вызовите эту функцию, чтобы вернуть левую часть строки. Если параметр all_if_notfound равен true, то в случае отсутствия искомого возвращается вся строка.
plvstr.lpart ( str text, div text, start int, nth int ) → text
Вызовите эту функцию, чтобы вернуть левую часть строки.
plvstr.lpart ( str text, div text, start int ) → text
Вызовите эту функцию, чтобы вернуть левую часть строки.
plvstr.lpart ( str text, div text ) → text
Вызовите эту функцию, чтобы вернуть левую часть строки.
plvstr.rpart ( str text, div text, start int, nth int, all_if_notfound bool ) → text
Вызовите эту функцию, чтобы вернуть правую часть строки. Если параметр all_if_notfound равен true, то в случае отсутствия искомого возвращается вся строка.
plvstr.rpart ( str text, div text, start int, nth int ) → text
Вызовите эту функцию, чтобы вернуть правую часть строки.
plvstr.rpart ( str text, div text, start int ) → text
Вызовите эту функцию, чтобы вернуть правую часть строки.
plvstr.rpart ( str text, div text, ) → text
Вызовите эту функцию, чтобы вернуть правую часть строки.
plvstr.lstrip ( str text, substr text, num int ) → text
Вызовите эту функцию, чтобы удалить символы с начала строки.
plvstr.lstrip ( str text, substr text ) → text
Вызовите эту функцию, чтобы удалить символы с начала строки.
plvstr.rstrip ( str text, substr text, num int ) → text
Вызовите эту функцию, чтобы удалить символы с конца строки.
plvstr.rstrip ( str text, substr text ) → text
Вызовите эту функцию, чтобы удалить символы с конца строки.
plvstr.rvrs ( str text, start int, _end int ) → text
Реверсирует строку или часть строки.
plvstr.rvrs ( str text, start int ) → text
Реверсирует строку или часть строки.
plvstr.rvrs ( str text ) → text
Реверсирует строку или часть строки.
plvstr.left ( str text, n int ) → text
Возвращает первые num_in символов. Можно использовать отрицательный num_in.
plvstr.right ( str text, n int ) → text
Возвращает последние num_in символов. Можно использовать отрицательный num_in.
plvstr.swap ( str text, replace text, start int, length int ) → text
Заменяет заданной строкой подстроку в указанной строке, начиная с символа на позиции start и длиной в length символов.
plvstr.swap ( str text, replace text ) → text
Заменяет заданной строкой подстроку в указанной строке.
plvstr.betwn ( str text, start int, _end int, inclusive bool ) → text
Ищет строку между заданными начальным и конечным символами. Если параметр inclusive равен true, граничные символы включаются в поиск.
plvstr.betwn( str text, start int, _end text, startnth int, endnth int, inclusive bool, gotoend bool ) → text
Ищет строку между заданными начальным и конечным символами. Если параметр inclusive равен true, граничные символы включаются в поиск. Если параметр gotoend равен true, поиск ведется до конца строки.
plvstr.betwn ( str text, start int, _end int ) → text
Ищет строку между заданными начальным и конечным символами.
plvstr.betwn( str text, start int, _end text, startnth int, endnth int ) → text
Ищет строку между заданными начальным и конечным символами.
plvchr.nth ( str text, n int ) → text
Вызовите эту функцию, чтобы вернуть N-й символ в строке.
plvchr.first ( str text ) → text
Вызовите эту функцию, чтобы вернуть первый символ в строке.
plvchr.last ( str text ) → text
Вызовите эту функцию, чтобы вернуть последний символ в строке.
plvchr.is_blank ( c int )
Это пустой символ.
plvchr.is_blank ( c text )
Это пустой символ.
plvchr.is_digit ( c int )
Это цифра.
plvchr.is_digit ( c text )
Это цифра.
plvchr.is_quote ( c int )
Это кавычка.
plvchr.is_quote ( c text )
Это кавычка.
plvchr.is_other ( c int )
Это другое.
plvchr.is_other ( c text )
Это другое.
plvchr.is_letter ( c int )
Это буква.
plvchr.is_letter ( c text )
Это буква.
plvchr.char_name ( c text )
Возвращает имя символа в код ASCII как VARCHAR.
plvchr.quoted1 ( str text )
Текст в кавычках вида '''.
plvchr.quoted2 ( str int )
Текст в кавычках вида '"'.
plvchr.stripped ( str text, char_in text )
Очищает строку от всех вхождений заданных символов.

Пакет PLVsubst

Пакет PLVsubst выполняет замену строк на основе замещающего ключевого слова.

Функция
Описание
plvsubst.string ( template_in text, vals_in text[] )
Сканирует строку на предмет всех вхождений замещающего ключевого слова и заменяет его следующим значением из списка замещающих значений.
plvsubst.string ( template_in text, vals_in text[], subst_in text )
Сканирует строку на предмет всех вхождений замещающего ключевого слова и заменяет его следующим значением из списка замещающих значений.
plvsubst.string ( template_in text, vals_in text[], delim_in text )
Сканирует строку на предмет всех вхождений замещающего ключевого слова и заменяет его следующим значением из списка замещающих значений.
plvsubst.string ( template_in text, vals_in text[], delim_in text subst_in text )
Сканирует строку на предмет всех вхождений замещающего ключевого слова и заменяет его следующим значением из списка замещающих значений.
plvsubst.setsubst ( str text )
Устанавливает замещающее ключевое слово в значение по умолчанию ('%s').
plvsubst.subst()
Выводит замещающее ключевое слово.

Примеры:

SELECT plvsubst.string('My name is %s %s.', ARRAY['Pavel','Stěhule']);
          string
 --------------------------
 My name is Pavel Stěhule.
(1 row)

SELECT plvsubst.string('My name is %s %s.', 'Pavel,Stěhule');
          string
 --------------------------
 My name is Pavel Stěhule.
(1 row)

SELECT plvsubst.string('My name is $$ $$.', 'Pavel|Stěhule','|','$$');
          string
 --------------------------
 My name is Pavel Stěhule.
(1 row)

Пакет DBMS_utility

dms_utility.format_call_stack()

Возвращает отформатированную строку с содержимым стека вызовов.

qhb=# select foo2();
               foo2
 ---------------------------------
 -----  Call Stack  -----
   line             object
 number  statement  name
      1  return     function foo
      1  return     function foo1
      1  return     function foo2
(1 row)

Пакет PLVlex

Этот пакет несовместим с оригинальным PLVlex.

plvlex.tokens(str text, skip_spaces bool, qualified_names bool)

Возвращает таблицу лексических элементов в строке.

qhb=# select * from
	plvlex.tokens('select * from a.b.c join d ON x=y', true, true);

 pos | token  | code |  class  | separator | mod
 ----+--------+------+---------+-----------+------
   0 | select |  527 | KEYWORD |           |
   7 | *      |   42 | OTHERS  |           | self
   9 | from   |  377 | KEYWORD |           |
  25 | a.b.c  |      | IDENT   |           |
  20 | join   |  418 | KEYWORD |           |
  25 | d      |      | IDENT   |           |
  27 | on     |  473 | KEYWORD |           |
  30 | x      |      | IDENT   |           |
  31 | =      |   61 | OTHERS  |           | self
  32 | y      |      | IDENT   |           |
(10 rows)

ВНИМАНИЕ!
Коды ключевых слов могут меняться от версии к версии QHB!


Пакет DBMS_ASSERT

Этот пакет защищает пользовательский ввод от SQL-инъекций.

Функция
Описание
dbms_assert.enquote_literal(varchar) → varchar
Добавляет открывающие и закрывающие кавычки, проверяет, что все апострофы сопоставлены с соседними апострофами.
dbms_assert.enquote_name(varchar [, boolean]) → varchar
Заключает имя в двойные кавычки. Необязательный второй параметр переводит имя в нижний регистр. Внимание — в Oracle второй параметр переводит имя в верхний регистр.
dbms_assert.noop(varchar) → varchar
Возвращает значение без какой-либо проверки.
dbms_assert.qualified_sql_name(varchar) → varchar
Эта функция удостоверяется, что входная строка является уточненным именем SQL.
dbms_assert.schema_name(varchar) → varchar
Эта функция удостоверяется, что входная строка является существующим именем схемы.
dbms_assert.simple_sql_name(varchar) → varchar
Эта функция удостоверяется, что входная строка является простым именем SQL.
dbms_assert.object_name(varchar) → varchar
Эта функция удостоверяется, что входная строка является уточненным идентификатором SQL для существующего объекта SQL.

Пакет PLUnit

Этот пакет содержит некоторые функции утверждения.

Функция
Описание
plunit.assert_true(bool [, varchar])
Утверждает, что условие истинно.
plunit.assert_false(bool [, varchar])
Утверждает, что условие ложно.
plunit.assert_null(anyelement [, varchar])
Утверждает, что фактическое значение является NULL.
plunit.assert_not_null(anyelement [, varchar])
Утверждает, что фактическое значение не является NULL.
plunit.assert_equals(anyelement, anyelement [, double precision] [, varchar])
Утверждает, что ожидаемое и фактическое значения равны.
plunit.assert_not_equals(anyelement, anyelement [, double precision] [, varchar])
Утверждает, что ожидаемое и фактическое значения не равны.
plunit.fail([varchar])
Эту функцию можно использовать, чтобы с помощью предоставленного сообщения заставить тестовую процедуру немедленно завершиться ошибкой.

Пакет DBMS_random

Функция
Описание
dbms_random.initialize(int)
Инициализирует пакет с величиной рандомизации.
dbms_random.normal()
Возвращает случайные числа со стандартным распределением Гаусса.
dbms_random.random()
Возвращает случайное число из диапазона -231 .. 231.
dbms_random.seed(int)
Возвращает величину рандомизации.
dbms_random.seed(text)
Сбрасывает величину рандомизации.
dbms_random.string(opt text(1), len int)
Создает случайную строку.
dbms_random.terminate()
Завершает пакет (ничего не делает в Pg).
dbms_random.value()
Возвращает случайное число из диапазона [0.0 - 1.0).
dbms_random.value ( low double precision, high double precision )
Возвращает случайное число из диапазона [low - high).

Другие функции

Этот пакет содержит реализацию функций: concat, nvl, nvl2, lnnvl, decode, greatest, least, bitand, nanvl, sinh, cosh, tanh, oracle.substr и oracle.mod.

Функция
Описание
oracle.substr ( str text, start int, len int )
Подстрока, совместимая с Oracle.
oracle.substr ( str text, start int )
Подстрока, совместимая с Oracle.
oracle.substr ( str numeric, start numeric )
Подстрока, совместимая с Oracle.
oracle.substr ( str numeric, start numeric, len numeric )
Подстрока, совместимая с Oracle.
oracle.substr ( str varchar, start numeric )
Подстрока, совместимая с Oracle.
oracle.substr ( str varchar, start numeric, len numeric )
Подстрока, совместимая с Oracle.
oracle.lpad ( string, length [, fill] )
Функция lpad, совместимая с Oracle.
oracle.rpad ( string, length [, fill] )
Функция rpad, совместимая с Oracle.
oracle.ltrim ( string text [, characters text] )
Функция ltrim, совместимая с Oracle.
oracle.rtrim ( string text [, characters text] )
Функция rtrim, совместимая с Oracle.
oracle.btrim ( string text [, characters text] )
Функция btrim, совместимая с Oracle.
oracle.length ( string char )
Функция length, совместимая с Oracle.
oracle.listagg ( str text [, separator text] )
Агрегирует значения в список.
oracle.wm_concat ( str text )
Агрегирует значения в список через запятую.
oracle.median(float4)
Вычисляет медиану.
oracle.median(float8)
Вычисляет медиану.
oracle.to_number(text)
Преобразует строку в число.
oracle.to_number(numeric)
Преобразует строку в число.
oracle.to_number(numeric,numeric)
Преобразует строку в число.
public.to_multi_byte(text)
Преобразует все однобайтовые символы в соответствующие им многобайтовые символы.
public.to_single_byte(text)
Преобразует все многобайтовые символы в соответствующие им однобайтовые символы.
oracle.greatest(anyelement, anyelement[])
Функция greatest, совместимая с Oracle; при вводе NULL возвращает NULL.
oracle.least(anyelement, anyelement[])
Функция least, совместимая с Oracle; при вводе NULL возвращает NULL.
oracle.mod(int, int)
Функция mod, совместимая с Oracle; если второй параметр равен нулю, возвращает первый параметр.
oracle.remainder(int, int)
Возвращает остаток от деления числа на другое число.
oracle.remainder(numeric, numeric)
Возвращает остаток от деления числа на другое число.
oracle.sys_guid() → bytea
Возвращает 16 байт глобального уникального идентификатора.

Возможно, вам потребуется установить для параметра search_path значение oracle, pg_catalog, "$user", public, поскольку функции oracle.substr, oracle.lpad, oracle.rpad, oracle.ltrim, oracle.rtrim, oracle.btrim и oracle.length устанавливаются бок о бок с функциями pg_catalog.substr, pg_catalog.lpad, pg_catalog.rpad, pg_catalog.ltrim, pg_catalog.rtrim, pg_catalog.btrim и pg_catalog.length соответственно.

Функции oracle.decode, oracle.greatest и oracle.least всегда должны начинаться с префикса имени схемы, даже если oracle стоит перед pg_catalog в search_path, потому что эти функции реализованы внутри парсера и анализатора QHB. Без имени схемы всегда будут использоваться внутренние функции.

Обратите внимание, что в случае lpad** и rpad параметры string и fill могут иметь типы char, varchar, text, varchar2 или nvarchar2 (обратите внимание, что последние два типа предоставляются orafce). Символ заполнения по умолчанию представляет собой пробел половинной ширины. То же самое верно для ltrim, rtrim и btrim.

Обратите внимание, что у функции oracle.length есть ограничение, заключающееся в том, что она работает только с единицами символов, поскольку в QHB тип char поддерживает только семантику символов.


Функция oracle.sys_guid()

Эта функция возвращает глобальный уникальный идентификатор. Она вызывает указанные функции из расширения uuid-ossp, а затем эта функция должна быть установлена ​​до использования функции sys_guid. По умолчанию эта функция использует функцию uuid_generate_v1, но также можно использовать функции uuid_generate_v1mc и uuid_generate_v4 (путем установки функции orafce.sys_guid_source). Кроме того, oracle.sys_guid может использовать встроенную функцию gen_random_uuid. В этом случае расширение uuid-ossp не требуется.


Поддержка varchar2 и nvarchar2

Тип varchar2 от orafce реализует части спецификации базы данных Oracle о varchar2:

  • Единица модификатора типа — 'байты' (семантику символов см. в описании nvarchar2).

  • В отличие от varchar QHB, неявное приведение к varchar2 не усекает пробелы сверх заявленной максимальной длины.

  • При включении orafce.varchar2_null_safe_concat TO true для этих типов можно использовать защищенный от NULL оператор ||. Это поведение очень похоже на Oracle.

ВНИМАНИЕ!
Если результатом является пустая строка, то результат равен NULL. По умолчанию это поведение отключено.

-- защищенная от NULL конкатенация (по умолчанию отключено)
SELECT NULL || 'hello'::varchar2 || NULL;

SET orafce.varchar2_null_safe_concat TO true;
SELECT NULL || 'hello'::varchar2 || NULL;

Обратите внимание, что QHB не позволяет динамически указывать, как мы интерпретируем строки varchar. Она всегда интерпретирует их как «символьные» строки в соответствии с кодировкой базы данных. Таким образом, мы не можем одновременно поддерживать семантику байтов и символов для данного типа varchar в одной и той же базе данных. Мы решили реализовать семантику байтов, так как она используется в Oracle по умолчанию. Семантику символов см. в описании nvarchar2, который по умолчанию всегда реализует семантику символов.

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

nvarchar2 реализует следующее:

  • Единица модификатора типа = 'символы' (с использованием набора символов/ кодировки базы данных)

Используйте этот тип, если предпочтительна семантика символов.

Обратите внимание, что, в отличие от Oracle, в orafce типы varchar2* и nvarchar2 не налагают ограничение в 4000 байт на «объявленный» размер. На самом деле это то же самое, что и у varchar в QHB, размер которого составляет около 10 МБ (хотя теоретически varchar может хранить значения размером до 1 ГБ).

Некоторые строковые функции на основе байтов для использования со строками varchar2:

substrb(varchar2, int [, int])

Извлекает подстроку заданной длины (в байтах), начиная с заданной позиции байта (считая с единицы); если третий аргумент не указан, то считается длина до конца строки.

strposb(varchar2, varchar2)

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

lengthb(varchar2)

Возвращает длину (в байтах) заданной строки.


Триггеры

Oracle не делает различий между NULL и пустой строкой (когда значение используется как текст). Для QHB NULL и пустая строка являются разными значениями. Для простоты рекомендуется обеспечить (в базе данных QHB) использование только NULL (и не использовать пустые строки) или использование только пустых строк (и не использовать NULL) для столбцов текстового типа. Оба варианта имеют некоторые преимущества и недостатки.

Это можно обеспечить с помощью триггерных функций:

oracle.replace_empty_strings([raise_warnings boolean])
oracle.replace_null_strings([raise_warnings boolean])

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

CREATE TABLE test(id serial, name varchar, surname varchar);
CREATE TRIGGER test_trg
  BEFORE INSERT OR UPDATE
  ON test
  FOR EACH ROW
  EXECUTE PROCEDURE oracle.replace_empty_strings();

INSERT INTO test(name, surname) VALUES('', 'Stehule');

-- имя будет заменено на NULL

Эмулированные представления

  • oracle.user_tab_columns
  • oracle.user_tables
  • oracle.user_cons_columns
  • oracle.user_constraints
  • oracle.product_component_version
  • oracle.user_objects
  • oracle.dba_segments

См. также

Документация по orafce на Github