Преобразование функций Oracle в функции Snowflake

Вопрос или проблема

CREATE OR REPLACE function schema_1.getCName(CCodes VARCHAR2)
  return varchar2
as
  TYPE results_type IS REF CURSOR;
  results results_type;
  CValue VARCHAR2(1023 CHAR);
  CCodesForInClause VARCHAR2(511 CHAR) DEFAULT '';
  CCodesValue VARCHAR2(510 CHAR);
  cursorQuery VARCHAR2(1023 CHAR) DEFAULT '';
  CNames VARCHAR2(1023 CHAR);
begin
  CNames := '';
  IF(instr(CCodes,',') > 0) THEN
    CCodesValue := CCodes;
    WHILE (instr(CCodesValue,',') > 0)
    LOOP
      CCodesForInClause := CCodesForInClause || '''' || 
        trim(substr(CCodesValue, 0, instr(CCodesValue,',')-1)) || 
        ''',';
      CCodesValue := substr(CCodesValue, instr(CCodesValue,',') + 1, 
        LENGTH(CCodesValue) - instr(CCodesValue,','));
    END LOOP;

    CCodesForInClause := CCodesForInClause || '''' || trim(CCodesValue) || '''';
    cursorQuery := 'SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV IN ' || '(' ||  CCodesForInClause || ') order by C_NAME';

    OPEN results FOR cursorQuery;
    LOOP
      IF (length(trim(CValue)) > 0) THEN
        CNames := CNames || CValue || ';';
      END IF;
      FETCH results INTO CValue;
      EXIT WHEN results%NOTFOUND;
    END LOOP;

    IF (length(trim(CNames)) > 0) THEN
      CNames := substr(CNames, 1, length(CNames)-1);
    END IF;
    CLOSE results;
  ELSE
    OPEN results FOR 'SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV = :cc' USING CCodes;
    FETCH results into CValue;

      IF (results%FOUND) THEN
        CNames := CValue;
      END IF;
      CLOSE results;
    END IF;

    DBMS_Output.Put_Line(CNames);
    return CNames;
end;

Ответ или решение

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

Шаг 1: Основные отличия

1. Объявление функций: В Snowflake мы используем CREATE OR REPLACE FUNCTION подобно Oracle, но RETURN указывается позже, без ключевого слова AS.
2. Указание типов данных: В Snowflake нет типа VARCHAR2. Вместо этого используется VARCHAR.
3. Обработка курсоров: Snowflake не поддерживает курсоры в том виде, как это реализовано в Oracle. Вместо этого рекомендуется использовать оператор RETURN TABLE для возврата результатов.
4. Условие выборки с использованием конструкций IF/ELSE: В Snowflake это осуществляется несколько иначе, но мы можем придерживаться аналогичной логики.

Шаг 2: Преобразование кода

Ниже представлена новая версия функции, адаптированной под требования Snowflake.

CREATE OR REPLACE FUNCTION schema_1.getCName(CCodes VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  CNames VARCHAR := '';
  CCodesForInClause STRING := '';
  CCodesValue VARCHAR := CCodes;
BEGIN
  IF POSITION(',' IN CCodes) > 0 THEN
    WHILE POSITION(',' IN CCodesValue) > 0 DO
      CCodesForInClause := CCodesForInClause || '''' || TRIM(SUBSTRING(CCodesValue, 1, POSITION(',' IN CCodesValue) - 1)) || ''',';
      CCodesValue := SUBSTRING(CCodesValue, POSITION(',' IN CCodesValue) + 1);
    END WHILE;

    CCodesForInClause := CCodesForInClause || '''' || TRIM(CCodesValue) || '''';

    -- Выполнение запроса и сбор результатов
    CNames := (
      SELECT LISTAGG(C_NAME, ';') WITHIN GROUP (ORDER BY C_NAME)
      FROM (SELECT DISTINCT C_NAME FROM Table1 WHERE C_ABBREV IN (CCodesForInClause))
    );
  ELSE
    SELECT C_NAME INTO CNames
    FROM Table1
    WHERE C_ABBREV = CCodes
    LIMIT 1;
  END IF;

  RETURN CNames;
END;
$$;

Шаг 3: Объяснение кода

1. Инициализация переменных: Заменили тип VARCHAR2 на VARCHAR, что является соответствующим типом в Snowflake.
2. Проверка на наличие запятой: Используется POSITION вместо INSTR для поиска запятой в строке.
3. Строковая агрегация: Для объединения имен используется функция LISTAGG, которая позволяет объединять строки с заданным разделителем. Функция ORDER BY в LISTAGG управляет порядком объединения имен.
4. Выбор имени с помощью SELECT INTO: Это реализация аналогична получению значения через курсор в Oracle, но в Snowflake мы можем просто использовать SELECT ... INTO.

Шаг 4: Заключение

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