DSQL, ESQL, ISQL, PSQL

полезные запросы, которые можно будет оформить в статью или просто создать страничку

если нужно отобрать записи по ключам, то намного быстрее будет засунуть эти ID в строку, распарсить и вытягивать каждую запись по по ключу по отдельности в цикле:

CREATE PROCEDURE CONVERT_IDS_TO_ROWS(
    IDS VARCHAR(32700))
RETURNS (
    ID INTEGER)
AS
DECLARE I INTEGER = 1;
  DECLARE J INTEGER = 1;
BEGIN
  IDS = TRIM(IDS);
  WHILE (I <= CHAR_LENGTH(IDS)) DO
  BEGIN
    IF (',' = SUBSTRING(IDS FROM I FOR 1)) THEN
    BEGIN
      ID = SUBSTRING(IDS FROM J FOR I - J);
      SUSPEND;
      I = I + 1;
      J = I;
    END
    I= I + 1;
  END
  IF (I > J) THEN
  BEGIN
   ID = SUBSTRING(IDS FROM J FOR I - J);
   SUSPEND;
  END
END
 
 
CREATE OR ALTER PROCEDURE LIST_TO_ROWS(
    LST BLOB SUB_TYPE TEXT)
RETURNS (
    ID INTEGER)
AS
  DECLARE i INTEGER = 1;
  DECLARE j INTEGER = 1;
BEGIN
  while (i <= CHAR_LENGTH(lst)) do BEGIN
    IF (SUBSTRING(lst FROM i FOR 1) = ',') THEN BEGIN
      IF (i > j) THEN
        id = SUBSTRING(lst FROM j FOR i - j);
      ELSE
        id = NULL;
      suspend;
      j = i+1;
    END
    i = i+1;
  END
 
  IF (i > j) THEN
    id = SUBSTRING(lst FROM j FOR i - j);
  ELSE
    id = NULL;
  suspend;
END
 
 
CREATE OR ALTER PROCEDURE LIST_TO_ROWS(
    LST BLOB SUB_TYPE TEXT)
RETURNS (
    ID INTEGER)
AS
  DECLARE pos_ INT;
  DECLARE offset INT = 1;
  DECLARE beg INT;
  DECLARE buf VARCHAR(30100);
BEGIN
  while (0=0) do BEGIN
    buf = SUBSTRING(lst FROM offset FOR 30100);
    pos_ = 1; beg = 1;
    while (pos_ <= CHAR_LENGTH(buf) AND pos_ <= 30000) do BEGIN
      IF (SUBSTRING(buf FROM pos_ FOR 1) = ',') THEN BEGIN
        IF (pos_ > beg) THEN
          id = SUBSTRING(buf FROM beg FOR pos_ - beg);
        ELSE
          id = NULL;
        suspend;
        beg = pos_ + 1;
      END
      pos_ = pos_ + 1;
    END
    IF (offset + pos_ - 2 = CHAR_LENGTH(lst)) THEN leave;
    offset = offset + beg - 1;
    IF (offset > CHAR_LENGTH(lst)) THEN leave;
  END
 
  IF (pos_ > beg) THEN
    id = SUBSTRING(buf FROM beg FOR pos_ - beg);
  ELSE
    id = NULL;
  suspend;
END

Если нужно проверить во входящей строке можно ли её считать числом можно использовать следующую процедуру

CREATE PROCEDURE IS_NUMBER (
    a_value VARCHAR(32))
RETURNS (
    RESULT VARCHAR(10))
AS
DECLARE variable i INTEGER;
DECLARE variable j INTEGER;
BEGIN
  RESULT = 'Фиг';
  a_value = TRIM(a_value);
  IF (a_value IS NULL OR CHAR_LENGTH(a_value) = 0) THEN BEGIN
    suspend;
    exit;
  END
  i = 1;
  j = CHAR_LENGTH(a_value);
  while (i <= j) do BEGIN
    IF (SUBSTRING(a_value FROM i FOR 1) BETWEEN '0' AND '9'
     OR (i =1 AND SUBSTRING(a_value FROM 1 FOR 1) IN ('-', '+')
       AND CHAR_LENGTH(a_value) > 1 )) THEN
      RESULT = 'Это число';
    ELSE BEGIN
      RESULT = 'Фиг';
      Break;
    END
    i = i + 1;
  END
  suspend;
END

CREATE OR ALTER PROCEDURE CURRENCYSTR(
    VAL NUMERIC(15,2),
    SHOWCURRENCY INTEGER)
RETURNS (
    CURR_STR VARCHAR(1000))
AS
DECLARE razryad VARCHAR(50);
DECLARE razryad_idx VARCHAR(28);
DECLARE hundreds VARCHAR(64);
DECLARE hundreds_idx VARCHAR(30);
DECLARE tens VARCHAR(69);
DECLARE tens_idx VARCHAR(40);
DECLARE ones VARCHAR(138);
DECLARE ones_idx VARCHAR(100);
 
DECLARE sign_of_val VARCHAR(6);
DECLARE raz INT;
DECLARE cents VARCHAR(3);
DECLARE val_str VARCHAR(20);
DECLARE num VARCHAR(20);
DECLARE i INT;
DECLARE buf VARCHAR(200);
DECLARE buf1 VARCHAR(200);
 
BEGIN
  /* Константы */
  razryad_idx = /* 2.2 */ '0100010506071308210829114011';
  razryad = 'тысячмиллионмиллиардтриллионквадриллионквинтиллион';
  hundreds_idx = /* 2.1 */ '010013046106169257328407479569';
  hundreds = 'стодвеститристачетырестапятьсотшестьсотсемьсотвосемьсотдевятьсот';
  tens_idx = /* 2.2 */ '0100010001080908170522093110410950116109';
  tens = 'двадцатьтридцатьсорокпятьдесятшестьдесятсемьдесятвосемьдесятдевяносто';
  ones_idx = /* 3.2 */ '0010000100001000010300406010040140501904023060290603506041110521006210072120841009411105101151212712';
  ones = 'тричетырепятьшестьсемьвосемьдевятьдесятьодиннадцатьдвенадцатьтринадцатьчетырнадцатьпятнадцатьшестнадцатьсемнадцатьвосемнадцатьдевятнадцать';
 
  IF (ShowCurrency IS NULL) THEN ShowCurrency = 0;
  curr_str = '';
 
  /* Смотрим знак */
  IF (val < 0) THEN BEGIN
    sign_of_val = 'минус ';
    val = -val;
  END ELSE
    sign_of_val = '';
 
  /* Выбираем и запоминаем копейки, убираем их из числа */
  val_str = CAST(val AS VARCHAR(20));
  i = POSITION('.' IN val_str);
  cents = lpad(SUBSTRING(val_str FROM i+1 FOR 2), 2, '0');
  val_str = lpad(SUBSTRING(val_str FROM 1 FOR i-1), ((i+1)/3*3), '0');
 
  /* Разбираем число */
  raz = 0; curr_str = '';
  while (val_str != '') do BEGIN
    /* Берём триаду символов */
    num = RIGHT(val_str, 3);
    /* Если не нулевое число */
    IF (num != '000') THEN BEGIN
      /* Берём сотни */
      i = CAST(SUBSTRING(num FROM 1 FOR 1) AS INT);
      buf = SUBSTRING(hundreds FROM CAST(SUBSTRING(hundreds_idx FROM i*3+1 FOR 2) AS INT) FOR CAST(SUBSTRING(hundreds_idx FROM i*3+3 FOR 1) AS INT));
 
      /* Далее десятки */
      /* Для "десятнадцатых" упрощённая обработка */
      IF (SUBSTRING(num FROM 2 FOR 1) = '1') THEN BEGIN
          /* Вставляем нужную "десятнадцать" */
          i = CAST(SUBSTRING(num FROM 2 FOR 2) AS INT);
          buf1 = SUBSTRING(ones FROM CAST(SUBSTRING(ones_idx FROM i*5+1 FOR 3) AS INT) FOR CAST(SUBSTRING(ones_idx FROM i*5+4 FOR 2) AS INT));
          IF (buf != '') THEN buf = buf || ' ';
          buf = buf || buf1;
      END ELSE
      /* Для "нормальных" чисел своя обработка */
      BEGIN
        /* Десятки */
        i = CAST(SUBSTRING(num FROM 2 FOR 1) AS INT);
        buf1 = SUBSTRING(tens FROM CAST(SUBSTRING(tens_idx FROM i*4+1 FOR 2) AS INT) FOR CAST(SUBSTRING(tens_idx FROM i*4+3 FOR 2) AS INT));
        IF (buf != '' AND buf1 != '') THEN buf = buf || ' ';
        buf = buf || buf1;
 
        /* Единицы */
        i = CAST(SUBSTRING(num FROM 3 FOR 1) AS INT);
        /* Смотрим количество для нужного окончания */
        IF (i = 1) THEN BEGIN
          IF (raz = 1) THEN buf1 = 'одна'; ELSE buf1 = 'один';
        END ELSE
        IF (i = 2) THEN BEGIN
          IF (raz = 1) THEN buf1 = 'две'; ELSE buf1 = 'два';
        END ELSE
          buf1 = SUBSTRING(ones FROM CAST(SUBSTRING(ones_idx FROM i*5+1 FOR 3) AS INT) FOR CAST(SUBSTRING(ones_idx FROM i*5+4 FOR 2) AS INT));
        IF (buf != '' AND buf1 != '') THEN buf = buf || ' ';
        buf = buf || buf1;
      END
 
      /* Разряд числа */
      buf1 = SUBSTRING(razryad FROM CAST(SUBSTRING(razryad_idx FROM raz*4+1 FOR 2) AS INT) FOR CAST(SUBSTRING(razryad_idx FROM raz*4+3 FOR 2) AS INT));
      IF (buf1 != '') THEN BEGIN
        /* Подбор окончания для разряда */
        IF (i = 1) THEN BEGIN
          IF (raz = 1) THEN buf1 = buf1 || 'а';
        END ELSE
        IF (i IN (2,3,4)) THEN BEGIN
          IF (raz = 1) THEN buf1 = buf1 || 'и';
          ELSE IF (raz > 1) THEN buf1 = buf1 || 'а';
        END ELSE
          IF (raz > 1) THEN buf1 = buf1 || 'ов';
        buf = buf || ' ' || buf1;
      END
    END ELSE
      buf = '';
 
    /* Присоединяем обработанную триаду к результату */
    IF (curr_str != '' AND buf != '') THEN buf = buf || ' ';
    curr_str = buf || curr_str;
    /* Переходим к следующей триаде */
    val_str = LEFT(val_str, CHAR_LENGTH(val_str)-3);
    /* Увеличиваем счётчик разряда */
    raz = raz + 1;
  END
 
  /* Припысываем знак */
  curr_str = sign_of_val || curr_str;
  /* Делаем первую букву прописной */
  curr_str = UPPER(SUBSTRING(curr_str FROM 1 FOR 1)) || SUBSTRING(curr_str FROM 2);
 
  /* Флаг "показать название валюты" */
  IF (ShowCurrency = 1) THEN
    curr_str = curr_str || ' руб. ' || cents || ' коп.';
 
  suspend;
END

WITH RECURSIVE
  RELATIONS AS (
    SELECT DISTINCT
      RC_1.RDB$RELATION_NAME AS RN1, RC_2.RDB$RELATION_NAME AS RN2
    FROM RDB$RELATION_CONSTRAINTS RC_1
    JOIN RDB$REF_CONSTRAINTS REC_2 ON REC_2.RDB$CONST_NAME_UQ = RC_1.RDB$CONSTRAINT_NAME
      AND RC_1.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY'
    JOIN RDB$RELATION_CONSTRAINTS RC_2 ON RC_2.RDB$CONSTRAINT_NAME = REC_2.RDB$CONSTRAINT_NAME
      AND RC_2.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY'
    WHERE RC_1.RDB$RELATION_NAME <> RC_2.RDB$RELATION_NAME
  ),
 
  RELATIONS_TREE AS (
    SELECT DISTINCT 1 AS LVL, RN1 AS RN_ID, CAST(NULL AS VARCHAR(32)) AS RN_PARENT
    FROM RELATIONS
 
    UNION ALL
 
    SELECT RT.LVL + 1, R.RN1, R.RN2
    FROM RELATIONS R
    JOIN RELATIONS_TREE RT ON R.RN2 = RT.RN_ID
  ),
 
  RELATION_REFS AS (
    SELECT RN_ID, SUM(LVL) AS REFS_COUNT
    FROM RELATIONS_TREE
    GROUP BY 1
  )
 
SELECT
  RR.RDB$RELATION_NAME AS RELATION_NAME, COALESCE(RT.REFS_COUNT, 0)
FROM RDB$RELATIONS RR
LEFT JOIN RELATION_REFS RT ON RT.RN_ID = RR.RDB$RELATION_NAME
WHERE RR.RDB$VIEW_BLR IS NULL
  AND COALESCE(RR.RDB$SYSTEM_FLAG, 0) = 0
ORDER BY 2 DESC

Полезные запросы

2008-05-12irebird%\doc\