SnowConvert AI - Oracle - HELPERS¶

이 섹션에서는 Snowflake Scripting에서 기본적으로 지원되지 않는 일부 Oracle 기능의 기능적 동등성을 달성하기 위해 사용되는 헬퍼 함수를 확인할 수 있습니다.

대량 커서 헬퍼¶

참고

기본값 FORALL 변환에 관심이 있을 수도 있습니다.

커서는 커서 상태에 관한 다양한 정보가 포함된 OBJECT 로 시뮬레이션됩니다. 커서의 쿼리 결과 세트를 저장하기 위해 임시 테이블이 생성됩니다.

대부분의 프로시저는 커서의 상태가 업데이트된 새 오브젝트를 반환합니다.

INIT_CURSOR¶

이 함수는 기본 커서 정보로 새 오브젝트를 초기화합니다

CREATE OR REPLACE FUNCTION INIT_CURSOR(NAME VARCHAR, QUERY VARCHAR)
RETURNS OBJECT
AS
$$
  SELECT OBJECT_CONSTRUCT('NAME', NAME, 'ROWCOUNT', -1, 'QUERY', QUERY, 'ISOPEN', FALSE, 'FOUND', NULL, 'NOTFOUND', NULL)
$$;

Copy

OPEN_BULK_CURSOR¶

These procedures create a temporary table with the query of the cursor. An optional overload exists to support bindings.

CREATE OR REPLACE PROCEDURE OPEN_BULK_CURSOR(CURSOR OBJECT, BINDINGS ARRAY)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  var query = `CREATE OR REPLACE TEMPORARY TABLE ${CURSOR.NAME}_TEMP_TABLE AS ${CURSOR.QUERY}`;
  snowflake.execute({ sqlText: query, binds: BINDINGS });
  CURSOR.ROWCOUNT = 0;
  CURSOR.ISOPEN = true;
  return CURSOR;
$$;

Copy

CREATE OR REPLACE PROCEDURE OPEN_BULK_CURSOR(CURSOR OBJECT)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL OPEN_BULK_CURSOR(:CURSOR, NULL));
    RETURN :RESULT;
  END;
$$;

Copy

CLOSE_BULK_CURSOR¶

이 프로시저는 커서의 결과 세트를 저장한 임시 테이블을 삭제하고 커서의 속성을 초기 상태로 재설정합니다.

CREATE OR REPLACE PROCEDURE CLOSE_BULK_CURSOR(CURSOR OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  var query = `DROP TABLE ${CURSOR.NAME}_TEMP_TABLE`;
  snowflake.execute({ sqlText: query });
  CURSOR.ROWCOUNT = -1;
  CURSOR.ISOPEN = false;
  CURSOR.FOUND = null;
  CURSOR.NOTFOUND = null;
  return CURSOR;
$$;

Copy

FETCH 헬퍼¶

Due to Oracle being capable of doing the FETCH statement on different kind of scenarios, multiple procedures with overloads were created to handle each case. These helpers save the fetched values into the RESULT property in the CURSOR object.

Some of the overloads include variations when the LIMIT clause was used or not. Other overloads have a COLUMN_NAMES argument that is necessary when the FETCH statement is being done into a variable that has or contains records with column names that are different to the column names of the query.

FETCH_BULK_COLLECTION_RECORDS¶

이 프로시저는 FETCH BULK 를 레코드 모음으로 생성할 때 사용됩니다.

CREATE OR REPLACE PROCEDURE FETCH_BULK_COLLECTION_RECORDS(CURSOR OBJECT, LIMIT FLOAT, COLUMN_NAMES ARRAY)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  var objectConstructArgs = [];
  if (COLUMN_NAMES) {
    for (let i = 0 ; i < COLUMN_NAMES.length ; i++) {
      objectConstructArgs.push("'" + COLUMN_NAMES[i] + "'");
      objectConstructArgs.push('$' + (i + 1));
    }
  } else {
    objectConstructArgs.push('*');
  }
  var limitValue = LIMIT ?? 'NULL';
  var query = `SELECT ARRAY_AGG(OBJECT_CONSTRUCT(${objectConstructArgs.join(', ')})) FROM (SELECT * FROM ${CURSOR.NAME}_TEMP_TABLE LIMIT ${limitValue} OFFSET ${CURSOR.ROWCOUNT})`;
  var stmt = snowflake.createStatement({ sqlText: query});
  var resultSet = stmt.execute();
  resultSet.next();
  CURSOR.RESULT = resultSet.getColumnValue(1);
  CURSOR.ROWCOUNT += CURSOR.RESULT.length;
  CURSOR.FOUND = CURSOR.RESULT.length > 0;
  CURSOR.NOTFOUND = !CURSOR.FOUND;
  return CURSOR;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_COLLECTION_RECORDS(CURSOR OBJECT)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_COLLECTION_RECORDS(:CURSOR, NULL, NULL));
    RETURN :RESULT;
  END;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_COLLECTION_RECORDS(CURSOR OBJECT, LIMIT INTEGER)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_COLLECTION_RECORDS(:CURSOR, :LIMIT, NULL));
    RETURN :RESULT;
  END;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_COLLECTION_RECORDS(CURSOR OBJECT, COLUMN_NAMES ARRAY)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_COLLECTION_RECORDS(:CURSOR, NULL, :COLUMN_NAMES));
    RETURN :RESULT;
  END;
$$;

Copy

FETCH_BULK_COLLECTIONS¶

이 프로시저 는FETCH 문을 하나 또는 여러 개의 컬렉션으로 처리할 때 사용됩니다. 이 FETCH 작업에서 열이 지정되므로 특정 COLUMN_NAMES 에 대한 재정의는 필요하지 않습니다.

CREATE OR REPLACE PROCEDURE FETCH_BULK_COLLECTIONS(CURSOR OBJECT, LIMIT FLOAT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  var limitClause = '';
  var limitValue = LIMIT ?? 'NULL';
  var query = `SELECT * FROM ${CURSOR.NAME}_TEMP_TABLE LIMIT ${limitValue} OFFSET ${CURSOR.ROWCOUNT}`;
  var stmt = snowflake.createStatement({ sqlText: query});
  var resultSet = stmt.execute();
  var column_count = stmt.getColumnCount();
  CURSOR.RESULT = [];
  for (let i = 0 ; i < column_count ; i++) {
    CURSOR.RESULT[i] = [];
  }

  while (resultSet.next()) {
    for (let i = 1 ; i <= column_count ; i++) {
      let columnName = stmt.getColumnName(i);
      CURSOR.RESULT[i - 1].push(resultSet.getColumnValue(columnName));
    }
  }
  CURSOR.ROWCOUNT += stmt.getRowCount();
  CURSOR.FOUND = stmt.getRowCount() > 0;
  CURSOR.NOTFOUND = !CURSOR.FOUND;
  return CURSOR;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_COLLECTIONS(CURSOR OBJECT)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_COLLECTIONS(:CURSOR, NULL));
    RETURN :RESULT;
  END;
$$;

Copy

FETCH_BULK_RECORD_COLLECTIONS¶

이 프로시저는 FETCHBULK 를 수집 레코드로 생성할 때 사용됩니다.

CREATE OR REPLACE PROCEDURE FETCH_BULK_RECORD_COLLECTIONS(CURSOR OBJECT, LIMIT FLOAT, COLUMN_NAMES ARRAY)
RETURNS OBJECT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  var limitValue = LIMIT ?? 'NULL';
  var query = `SELECT * FROM ${CURSOR.NAME}_TEMP_TABLE LIMIT ${limitValue} OFFSET ${CURSOR.ROWCOUNT}`;
  var stmt = snowflake.createStatement({ sqlText: query});
  var resultSet = stmt.execute();
  var column_count = stmt.getColumnCount();
  CURSOR.RESULT = {};
  if (COLUMN_NAMES)
  {
    for (let i = 0 ; i < COLUMN_NAMES.length ; i++) {
      CURSOR.RESULT[COLUMN_NAMES[i]] = [];
    }
  } else {
    for (let i = 1 ; i <= column_count ; i++) {
      let columnName = stmt.getColumnName(i);
      CURSOR.RESULT[columnName] = [];
    }
  }
  
  while (resultSet.next()) {
    for (let i = 1 ; i <= column_count ; i++) {
      let columnName = stmt.getColumnName(i);
      let fieldName = COLUMN_NAMES ? COLUMN_NAMES[i - 1] : columnName;
      CURSOR.RESULT[fieldName].push(resultSet.getColumnValue(columnName));
    }
  }
  CURSOR.ROWCOUNT += stmt.getRowCount();
  CURSOR.FOUND = stmt.getRowCount() > 0;
  CURSOR.NOTFOUND = !CURSOR.FOUND;
  return CURSOR;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_RECORD_COLLECTIONS(CURSOR OBJECT)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_RECORD_COLLECTIONS(:CURSOR, NULL, NULL));
    RETURN :RESULT;
  END;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_RECORD_COLLECTIONS(CURSOR OBJECT, LIMIT INTEGER)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_RECORD_COLLECTIONS(:CURSOR, :LIMIT, NULL));
    RETURN :RESULT;
  END;
$$;

Copy

CREATE OR REPLACE PROCEDURE FETCH_BULK_RECORD_COLLECTIONS(CURSOR OBJECT, COLUMN_NAMES ARRAY)
RETURNS OBJECT
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
  DECLARE
    RESULT OBJECT;
  BEGIN
    RESULT := (CALL FETCH_BULK_RECORD_COLLECTIONS(:CURSOR, NULL, :COLUMN_NAMES));
    RETURN :RESULT;
  END;
$$;

Copy