Procedures

Snowflake CREATE PROCEDURE is defined in SQL Syntax whereas its inner statements are defined in JavaScript.

Example 1: Basic Procedure Conversion

Oracle

CREATE OR REPLACE PROCEDURE PROC1
IS
BEGIN
null;
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    null;
$$

Example 2: Procedure Conversion with basic statements: Declaration, Assignment, Cursor Declaration, FOR Cursor, Open, LOOP, CLOSE, IF,

Oracle

CREATE OR REPLACE PROCEDURE PROC1
(
  param1 NUMBER
)
IS
  localVar1 NUMBER;
  countRows NUMBER;
  tempSql VARCHAR(100);
  tempResult NUMBER;
  CURSOR MyCursor
    IS
       SELECT COL1 FROM Table1;

BEGIN
    localVar1 := param1;
    countRows := 0;
    tempSql := 'SELECT COUNT(*) FROM Table1 WHERE COL1 =' || localVar1;

    FOR myCursorItem IN MyCursor
        LOOP
            localVar1 := myCursorItem.Col1;
            countRows := countRows + 1; 
        END LOOP;
    INSERT INTO Table2 VALUES(countRows, 'ForCursor: Total Row count is: ' || countRows);
    countRows := 0;

    OPEN MyCursor;
    LOOP
        FETCH MyCursor INTO tempResult;
        EXIT WHEN MyCursor%NOTFOUND;
        countRows := countRows + 1;
    END LOOP;
    CLOSE MyCursor;
    INSERT INTO Table2 VALUES(countRows, 'LOOP: Total Row count is: ' || countRows);

    EXECUTE IMMEDIATE tempSql INTO tempResult;
    IF tempResult > 0 THEN 
        INSERT INTO Table2 (COL1, COL2) VALUES(tempResult, 'Hi, found value:' || localVar1 || ' in Table1 -- There are ' || tempResult || ' rows');
        COMMIT;
    END IF;
END PROC1;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.PROC1
(
  param1 FLOAT
)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let LOCALVAR1;
   let COUNTROWS;
   let TEMPSQL;
   let TEMPRESULT;
   let MYCURSOR = new CURSOR(`       SELECT COL1 FROM PUBLIC.Table1`,() => []);
   LOCALVAR1 = PARAM1;
   COUNTROWS = 0;
   // ** MSC-WARNING - MSCEWI1038 - THIS STATEMENT MAY BE A DYNAMIC SQL THAT COULD NOT BE RECOGNIZED AND CONVERTED **
   TEMPSQL = `SELECT COUNT(*) FROM PUBLIC.Table1 WHERE COL1 =${LOCALVAR1}`;
   MYCURSOR.OPEN();
   while ( MYCURSOR.NEXT() ) {
      let MYCURSORITEM = MYCURSOR.CURRENT;
      LOCALVAR1 = MYCURSORITEM.COL1;
      COUNTROWS = COUNTROWS + 1;
   }
   MYCURSOR.CLOSE();
   EXEC(`INSERT INTO PUBLIC.Table2 VALUES(countRows, 'ForCursor: Total Row count is: ' || NVL( countRows, ''))`);
   COUNTROWS = 0;
   MYCURSOR.OPEN();
   while ( true ) {
      MYCURSOR.FETCH(TEMPRESULT) && ([TEMPRESULT] = MYCURSOR.INTO());
      if (MYCURSOR.NOTFOUND()) {
         break;
      }
      COUNTROWS = COUNTROWS + 1;
   }
   MYCURSOR.CLOSE();
   EXEC(`INSERT INTO PUBLIC.Table2 VALUES(countRows, 'LOOP: Total Row count is: ' || NVL( countRows, ''))`);
   [TEMPRESULT] = EXEC(TEMPSQL);
   if (TEMPRESULT > 0) {
      EXEC(`INSERT INTO PUBLIC.Table2 (COL1, COL2) VALUES(tempResult, 'Hi, found value:' || NVL( localVar1, '') || ' in Table1 -- There are ' || NVL( tempResult, '') || ' rows')`);
      EXEC(`COMMIT;`);
   }
$$;

Call of procedures inside other procedure

Oracle

CREATE OR REPLACE PROCEDURE PROCEDURE01(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
INSERT INTO TABLE1 VALUES(param1, param2);
END;

CREATE OR REPLACE PROCEDURE PROCEDURE02(param1 NUMBER, param2 VARCHAR)
IS
BEGIN
PROCEDURE01(param1, param2);
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE PROCEDURE01(param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   EXEC(`INSERT INTO PUBLIC.TABLE1 VALUES(?, ?)`,[PARAM1,PARAM2]);
$$;

CREATE OR REPLACE PROCEDURE PROCEDURE02(param1 FLOAT, param2 STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   EXEC(`CALL PUBLIC.PROCEDURE01(?, ?)`,[PARAM1,PARAM2]);
$$;