Collections & Records

This section is focused in the transformation of composite data types: Collections and Records.

Records

You might also be interested in Records declaration.

Oracle

CREATE OR REPLACE PROCEDURE RECORDS_PROC AS 
 TYPE DEPTRECTYP IS RECORD (
    DEPT_ID    NUMBER(4) NOT NULL := 10,
    DEPT_NAME  VARCHAR2(30) NOT NULL := 'ADMINISTRATION',
    MGR_ID     NUMBER(6) := 200,
    LOC_ID     NUMBER(4) := 1700
  );
  
  TYPE NAME_REC IS RECORD (
    FIRST  EMPLOYEES.FIRST_NAME%TYPE,
    LAST   EMPLOYEES.LAST_NAME%TYPE
  );
 
  TYPE CONTACT IS RECORD (
    NAME  NAME_REC,-- NESTED RECORD
    PHONE EMPLOYEES.PHONE_NUMBER%TYPE
  );
 
  DEPT1 DEPTRECTYP;
  DEPT_NAME DEPTRECTYP;
  C1 CONTACT;
BEGIN
  DEPT1.DEPT_NAME := 'PURCHASING';
  C1.NAME.FIRST := 'FALVARADO';
  C1.PHONE := '50687818481';
  SELECT * INTO DEPT1 FROM FTABLE46;
  INSERT INTO TABLA1 VALUES (DEPT1.DEPT_NAME);
  INSERT INTO TABLA1 VALUES (DEPT_NAME.DEPT_NAME);
  EXECUTE IMMEDIATE 'SELECT * FROM FTABLE46' INTO DEPT_NAME;
END;

Snowflake

Transformation for "SELECT INTO Record" is in progress.

CREATE OR REPLACE PROCEDURE PUBLIC.RECORDS_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code 
   
    class DEPTRECTYP {
      DEPT_ID = 10
      DEPT_NAME = `ADMINISTRATION`
      MGR_ID = 200
      LOC_ID = 1700
      constructor() {
         [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
      }
   }
   class NAME_REC {
      FIRST
      LAST
      constructor() {
         [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
      }
   }
   class CONTACT {
      NAME = new NAME_REC()
      PHONE
      constructor() {
         [...arguments].map((element,Index) => this[(Object.keys(this))[Index]] = element)
      }
   }
   let DEPT1 = new DEPTRECTYP();
   let DEPT_NAME = new DEPTRECTYP();
   let C1 = new CONTACT();
   DEPT1.DEPT_NAME = `PURCHASING`;
   C1.NAME.FIRST = `FALVARADO`;
   C1.PHONE = `50687818481`;
   [DEPT1] = EXEC(`SELECT * FROM PUBLIC.FTABLE46`);
   EXEC(`INSERT INTO PUBLIC.TABLA1 VALUES (?)`,[DEPT1.DEPT_NAME]);
   EXEC(`INSERT INTO PUBLIC.TABLA1 VALUES (?)`,[DEPT_NAME.DEPT_NAME]);
   EXEC(`SELECT * FROM PUBLIC.FTABLE46`,{
      rec : DEPT_NAME
   });
$$;