SnowConvert AI - Oracle - HELPERS¶

Dans cette section, vous trouverez des fonctions ou procédures d’assistant qui sont utilisées pour obtenir l’équivalence fonctionnelle de certaines fonctionnalités Oracle qui ne sont pas prises en charge nativement dans Snowflake Scripting.

Assistants du curseur Bulk¶

Note

Vous pouvez également être intéressé par Transformation FORALL par défaut.

Le curseur est simulé à l’aide d’un OBJECT contenant différentes informations sur l’état du curseur. Une table temporaire est créée pour stocker le jeu de résultats de la requête du curseur.

La plupart de ces procédures renvoient un nouvel objet contenant l’état actualisé du curseur.

INIT_CURSOR¶

Cette fonction initialise un nouvel objet avec les informations de base sur le curseur

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¶

Ces procédures créent une table temporaire avec la requête du curseur. Une surcharge optionnelle existe pour prendre en charge les liaisons.

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¶

Cette procédure supprime la table temporaire qui stockait le jeu de résultats du curseur et remet les paramètres du curseur à leur état initial.

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

Assistants FETCH¶

Oracle étant capable d’exécuter l’instruction FETCH pour différents types de scénarios, plusieurs procédures avec des surcharges ont été créées pour traiter chaque cas. Ces assistants enregistrent les valeurs obtenues dans la propriété RESULT de l’objet CURSOR.

Certaines des surcharges comprennent des variations lorsque la clause LIMIT a été utilisée ou non. D’autres surcharges ont un argument COLUMN_NAMES qui est nécessaire lorsque l’instruction FETCH est effectuée dans une variable qui a ou contient des enregistrements dont les noms de colonnes sont différents des noms de colonnes de la requête.

FETCH_BULK_COLLECTION_RECORDS¶

Ces procédures sont utilisées lorsqu’un FETCH BULK est créé dans une collection d’enregistrements.

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¶

Ces procédures sont utilisées lorsque l’instruction FETCH est effectuée dans une ou plusieurs collections. Comme les colonnes sont spécifiées dans cette opération FETCH, il n’est pas nécessaire de prévoir une dérogation pour une COLUMN_NAMES spécifique.

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¶

Ces procédures sont utilisées lorsqu’un FETCH BULK est effectué dans un enregistrement de collections.

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