DDL - DML Statements

All statements uses the EXEC helper.

SELECT

Oracle

CREATE OR REPLACE PROCEDURE PROC1 (param1 VARCHAR)
IS
    VAR1 NUMBER := 789;
BEGIN
    SELECT * FROM TABLE01;
    SELECT DISTINCT COL1 FROM TABLE01;
    SELECT * FROM TABLE01 WHERE COL1 = VAR1;
    SELECT * FROM TABLE01 WHERE COL1 = PARAM1;
    SELECT * FROM TABLE01 WHERE COL1 = PARAM1 AND COL2 = VAR1;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1 (param1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let VAR1 = 789;
   EXEC(`SELECT * FROM PUBLIC.TABLE01`);
   EXEC(`SELECT DISTINCT COL1 FROM PUBLIC.TABLE01`);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`SELECT * FROM PUBLIC.TABLE01 WHERE COL1 = ?`,[VAR1]);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`SELECT * FROM PUBLIC.TABLE01 WHERE COL1 = ?`,[PARAM1]);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`SELECT * FROM PUBLIC.TABLE01 WHERE COL1 = ? AND COL2 = ?`,[PARAM1,VAR1]);
$$;

SELECT INTO

Oracle

CREATE OR REPLACE PROCEDURE PROC1 (param1 VARCHAR, param2 VARCHAR)
IS
    VAR1 NUMBER;
    VAR2 NUMBER;
BEGIN
    SELECT COL1 INTO VAR1 FROM TABLE01;
    SELECT COL1 INTO VAR1 FROM TABLE01 WHERE COL2 = PARAM1;
    SELECT COL1 INTO VAR1, VAR2 FROM TABLE01;
    SELECT COL1 INTO VAR1, VAR2 FROM TABLE01
        WHERE COL2 = param1 AND COL3 = param1;
END

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1 (param1 STRING, param STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let VAR1;
   let VAR2;
   [VAR1] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE01`);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   [VAR1] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE01 WHERE COL2 = ?`,[PARAM1]);
   [VAR1,VAR2] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE01`);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   [VAR1,VAR2] = EXEC(`SELECT COL1 FROM PUBLIC.TABLE01
WHERE COL2 = ? AND COL3 = ?`,[PARAM1,PARAM1]);
$$;

INSERT and INSERT INTO SELECT

Oracle

CREATE OR REPLACE PROCEDURE PROC1 (param1 VARCHAR)
IS
    var1 NUMBER := 789;
BEGIN
    INSERT INTO TABLE01 VALUES('name', 123);
    INSERT INTO TABLE01 VALUES(param1, 456);
    INSERT INTO TABLE01 VALUES(param1, var1);
    INSERT INTO TABLE01 (col1, col2)
    SELECT col1, col2 FROM TABLE02 tb2
    WHERE tb2.col1 = 'myName';
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1 (param1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let VAR1 = 789;
   EXEC(`INSERT INTO PUBLIC.TABLE01 VALUES('name', 123)`);
   EXEC(`INSERT INTO PUBLIC.TABLE01 VALUES(?, 456)`,[PARAM1]);
   EXEC(`INSERT INTO PUBLIC.TABLE01 VALUES(?, ?)`,[PARAM1,VAR1]);
   EXEC(`INSERT INTO PUBLIC.TABLE01 (col1, col2)
    SELECT col1, col2 FROM PUBLIC.TABLE02 tb2
    WHERE tb2.col1 = 'myName'`);
$$;

DELETE

Oracle

CREATE OR REPLACE PROCEDURE PROC1 (PARAM1 VARCHAR)
IS
    VAR1 NUMBER := 0;
BEGIN
    DELETE FROM TABLE1 WHERE COL2 = 1;
    DELETE FROM TABLE1 WHERE COL2 = VAR1;
    DELETE FROM TABLE1 WHERE COL1 = PARAM1;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1(PARAM1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code   
   let VAR1 = 0;
   EXEC(`DELETE FROM PUBLIC.TABLE1 WHERE COL2 = 1`);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`DELETE FROM PUBLIC.TABLE1 WHERE COL2 = ?`,[VAR1]);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`DELETE FROM PUBLIC.TABLE1 WHERE COL1 = ?`,[PARAM1]);
$$;

UPDATE

Oracle

CREATE OR REPLACE PROCEDURE PROC1(PARAM1 VARCHAR)
IS
    VAR1 NUMBER := 3;
BEGIN
    UPDATE TABLE1 SET COL2 = 1 where COL2 = 0;
    UPDATE TABLE1 SET COL1 = VAR1 where COL1 = 0;
    UPDATE TABLE1 SET COL1 = 'name' where COL1 = PARAM11;
    UPDATE TABLE1 SET COL2 = VAR1 where COL1 = PARAM1;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1(PARAM1 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let VAR1 = 3;
   EXEC(`UPDATE TABLE1 SET COL2 = 1 where COL2 = 0`);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`UPDATE TABLE1 SET COL1 = ? where COL1 = 0`,[VAR1]);
   EXEC(`UPDATE TABLE1 SET COL1 = 'name' where COL1 = PARAM11`);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   EXEC(`UPDATE TABLE1 SET COL2 = ? where COL1 = ?`,[VAR1,PARAM1]);
$$;

MERGE

Oracle

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
	MERGE INTO TABLE01 t01
	USING TABLE02 t02
		ON (t01.col2 = t02.col2)
	WHEN MATCHED THEN 
		UPDATE SET t01.col1 = t02.col2;
END;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code  
   EXEC(`MERGE INTO TABLE01 t01
	USING PUBLIC.TABLE02 t02
		ON (t01.col2 = t02.col2)
	WHEN MATCHED THEN
		UPDATE SET t01.col1 = t02.col2`);
$$;