SQL Language Elements

Transformation for syntax and semantics of PL/SQL language elements.

Cursor FOR LOOP

You might also be interested in Cursor helper and Cursor declaration.

Oracle

CREATE OR REPLACE PROCEDURE PROC1
IS
    MyVariable1 NUMBER;
    MyOtherVariable2 NUMBER := 1;
    CURSOR C1 IS 
        SELECT * FROM Table1 WHERE ID = 123;
    CURSOR C2 (paramCursor1 NUMBER) IS
        SELECT COL1 AS C_1 FROM TABLE1 WHERE ID = paramCursor1;
BEGIN
    FOR myCursorRecord IN C1
        LOOP
            MyVariable1 := myCursorRecord.Col1;
        END LOOP;

    FOR myCursorRecord IN (SELECT * FROM Table1 WHERE ID = MyVariable1)
        LOOP
            MyVariable1 := myCursorRecord.Col1;
        END LOOP;

    <<Block1>>
    FOR myCursorRecord IN C2 (MyOtherVariable2)
        LOOP
            MyVariable1 := myCursorRecord.Col1;
        END LOOP Block1;
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
$$
   // REGION SnowConvert Helpers Code
   let MYVARIABLE1;
   let MYOTHERVARIABLE2 = 1;
   
   let C1 = new CURSOR(`SELECT * FROM PUBLIC.Table1 WHERE ID = 123`,() => []);
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   
   let C2 = new CURSOR(`SELECT COL1 AS C_1 FROM PUBLIC.TABLE1 WHERE ID = ?`,(PARAMCURSOR1) => [PARAMCURSOR1]);
   
   C1.OPEN();
   while ( C1.NEXT() ) {
      let MYCURSORRECORD = C1.CURRENT;
      MYVARIABLE1 = MYCURSORRECORD.COL1;
   }   
   C1.CLOSE();
   
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   for(var MYCURSORRECORD_CURSOR = new CURSOR(`(SELECT * FROM PUBLIC.Table1 WHERE ID = ?)`,[MYVARIABLE1]).OPEN();MYCURSORRECORD_CURSOR.NEXT();) {
      let MYCURSORRECORD = MYCURSORRECORD_CURSOR.CURRENT;
      MYVARIABLE1 = MYCURSORRECORD.COL1;
   }
   MYCURSORRECORD_CURSOR.CLOSE();
   
   C2.OPEN({
      binds : [MYOTHERVARIABLE2]
   });
   while ( C2.NEXT() ) {
      let MYCURSORRECORD = C2.CURRENT;
      MYVARIABLE1 = MYCURSORRECORD.COL1;
   }
   C2.CLOSE();
$$;

OPEN, FETCH and CLOSE Statement

You might also be interested in Cursor helper and Cursor declaration.

Oracle

CREATE OR REPLACE PROCEDURE PROC2
IS
    col1Value   table1.COL1%TYPE;
    col2Value   table1.COL2%TYPE;
    entireRow   table1%ROWTYPE;
    TYPE MyRowType IS RECORD ( COLUMN1 NUMBER, COLUMN2 NUMBER);
    entireRow_1 MyRowType;
    CURSOR C1 IS  SELECT * FROM table1;
    C2 SYS_REFCURSOR;
    TYPE COLLECTION_TYPE IS TABLE OF TABLE1.COL1%TYPE;
    MY_COLLECTION MY_COLLECTION_TYPE := MY_COLLECTION_TYPE();
    SOME_SELECT VARCHAR(200);
BEGIN
    OPEN C1;
    FETCH C1 INTO col1Value, col2Value;
    CLOSE C1;
    
    OPEN C1;
    FETCH C1 INTO entireRow;
    CLOSE C1;
    
    OPEN C1;
    FETCH C1 INTO entireRow_1;
    CLOSE C1;
    
    OPEN C2 FOR 'SELECT COL1 FROM TABLE1 WHERE COL1 <> :v' USING 123;
    FETCH C2 BULK COLLECT INTO MY_COLLECTION LIMIT 2;
    CLOSE C2;
    
    OPEN C2 FOR SELECT * FROM TABLE1 WHERE COL1 = NUM1;
    CLOSE C2;
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE PROC2 (NUM1 FLOAT)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let COL1VALUE;
   let COL2VALUE;
   let ENTIREROW;
   class MYROWTYPE {
      COLUMN1
      COLUMN2
   }
   let ENTIREROW_1 = new MYROWTYPE();
   let C1 = new CURSOR(`SELECT * FROM PUBLIC.table1`,() => []);
   let C2 = new CURSOR(undefined,undefined,true);
   /* ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR THIS TYPE IS PLANNED TO BE DELIVERED IN THE FUTURE ** */
   /*     TYPE COLLECTION_TYPE IS TABLE OF TABLE1.COL1%TYPE */
   ;
   let MY_COLLECTION = new MY_COLLECTION_TYPE();
   let SOME_SELECT;
   C1.OPEN();
   C1.FETCH(COL1VALUE,COL2VALUE) && ([COL1VALUE,COL2VALUE] = C1.INTO());
   C1.CLOSE();
   C1.OPEN();
   C1.FETCH(ENTIREROW) && ([ENTIREROW] = C1.INTO());
   C1.CLOSE();
   C1.OPEN();
   C1.FETCH(ENTIREROW_1) && ([ENTIREROW_1] = C1.INTO());
   C1.CLOSE();
   C2.OPEN({
      query : `SELECT COL1 FROM PUBLIC.TABLE1 WHERE COL1 <> ?`,
      binds : [123]
   });
   C2.FETCH_BULK_COLLECT_INTO([MY_COLLECTION],2);
   C2.CLOSE();
   // ** MSC-WARNING - MSCEWI1022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
   C2.OPEN({
      query : `SELECT * FROM PUBLIC.TABLE1 WHERE COL1 = ?`,
      binds : [NUM1]
   });
   C2.CLOSE();
$$;

Transformation for the following lines correspond to custom types which are work in progress:

entireRow   table1%ROWTYPE; // ROW TYPES
TYPE COLLECTION_TYPE IS TABLE OF TABLE1.COL1%TYPE; // COLLECTIONS

Currently the next statement is being emitted but the class is not being created yet. A warning will be applied in the future to all the uses of the unsupported custom types.

let MY_COLLECTION = new MY_COLLECTION_TYPE();

SQL Implicit Cursor

Oracle

CREATE OR REPLACE PROCEDURE SP_IMPLICIT_CURSOR_SAMPLE AUTHID DEFINER IS
  VAR_AUX  NUMBER(3);
  STMT_STAT1  NUMBER(3):= 0;
  STMT_STAT2  NUMBER(3):= 0;
  STMT_STAT3  NUMBER(3):= 0;
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE FTABLE35(COL1 NUMBER(3))';
  IF SQL%FOUND THEN
    STMT_STAT1 := 1;
  END IF;
  IF SQL%NOTFOUND THEN
   STMT_STAT2 := 1;
  END IF;
  IF SQL%ISOPEN THEN
   STMT_STAT3 := 1;
  END IF;
  EXECUTE IMMEDIATE 'INSERT INTO FTABLE33 VALUES(:D1,:D2,:D3,:D4)' USING SQL%ROWCOUNT, STMT_STAT1, STMT_STAT2, STMT_STAT3;
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE SP_IMPLICIT_CURSOR_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let VAR_AUX;
   let STMT_STAT1 = 0;
   let STMT_STAT2 = 0;
   let STMT_STAT3 = 0;
   EXEC(`CREATE OR REPLACE TABLE PUBLIC.FTABLE35 ( COL1 NUMBER (3))`);
   if (SQL.FOUND /*** MSC-WARNING - MSCEWI3021 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/) {
      STMT_STAT1 = 1;
   }
   if (SQL.NOTFOUND /*** MSC-WARNING - MSCEWI3021 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/) {
      STMT_STAT2 = 1;
   }
   if (SQL.ISOPEN /*** MSC-WARNING - MSCEWI3021 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/) {
      STMT_STAT3 = 1;
   }
   EXEC(`INSERT INTO PUBLIC.FTABLE33 VALUES(?, ?, ?, ?)`,[SQL.ROWCOUNT /*** MSC-WARNING - MSCEWI3021 - SQL IMPLICIT CURSOR VALUES MAY DIFFER ***/,STMT_STAT1,STMT_STAT2,STMT_STAT3]);
$$;

EXIT

You might also be interested in Loop and while statements.

Transformation for labels is a work in progress.

Oracle

CREATE OR REPLACE PROCEDURE PROCEDURE1
IS
  i NUMBER := 0;
  j NUMBER := 0;
  k NUMBER := 0;
BEGIN
  <<loop_a>>
  LOOP
    i := i + 1;

    <<loop_b>>
    LOOP
      j := j + 1;

      <<loop_c>>
      LOOP
        k := k + j + i;
        EXIT;
      END LOOP loop_c;

      EXIT loop_b WHEN (j > 3);
    END LOOP loop_b;

    EXIT loop_a WHEN (i > 3);
  END LOOP loop_a;
  
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE PROCEDURE1()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // REGION SnowConvert Helpers Code
    let I = 0;
    let J = 0;
    let K = 0;
    LOOP_A :
    while(true){
        I = I + 1;
        
        LOOP_B:
        while(true){
            J = J + 1;
    
            LOOP_C:
            while(true){
                K = K + J + I;
                break;
            }
    
            if(J > 3){
                break LOOP_B;
            }
        }
    
        if(i > 3){
            break LOOP_A;
        }
    }
$$;

Execute Immediate

You might also be interested in EXEC helper

Oracle

CREATE OR REPLACE PROCEDURE sp_sample5 AS
   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   dept_id     NUMBER(2) := 20;
   dept_id2     NUMBER(2) := 12;
   dept_id_upd VARCHAR(14);
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   dept_rec     deptt%ROWTYPE;
   TYPE NumList IS TABLE OF NUMBER;
   sals   NumList;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE dept (id NUMBER, name varchar(14), location varchar2(13))';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM dept WHERE id = :idd';
   EXECUTE IMMEDIATE sql_stmt INTO dept_rec USING dept_id;
   sql_stmt := 'UPDATE dept SET id = 200 WHERE id = :1 RETURNING name INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd;
   sql_stmt := 'delete from dept where id = :1 RETURNING name INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd;
   EXECUTE IMMEDIATE 'INSERT INTO dept VALUES (12, ''NAME1'', ''TEXAS'')';
   EXECUTE IMMEDIATE 'INSERT INTO DEPT VALUES(13, ''' || dept_name || ''', ''LA'')';
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE id = :num' USING dept_id2;
   EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MM-YYYY''';
   EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals;
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.sp_sample5 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let SQL_STMT;
   let PLSQL_BLOCK;
   let EMP_ID = 7566;
   let DEPT_ID = 20;
   let DEPT_ID2 = 12;
   let DEPT_ID_UPD;
   let DEPT_NAME = `PERSONNEL`;
   let LOCATION = `DALLAS`;
   let DEPT_REC;
   /* ** MSC-ERROR - MSCEWI3072 - PROCEDURAL MEMBER TYPE DEFINITION NOT SUPPORTED. ** */
   /*    TYPE NumList IS TABLE OF NUMBER */
   ;
   let SALS = new NUMLIST();
   EXEC(`CREATE OR REPLACE TABLE PUBLIC.dept ( id NUMBER (38,19),
name varchar(14),
location VARCHAR(13))`);
   SQL_STMT = `INSERT INTO dept VALUES (:1, :2, :3)`;
   EXEC(SQL_STMT,[DEPT_ID,DEPT_NAME,LOCATION]);
   SQL_STMT = `SELECT * FROM PUBLIC.dept WHERE id = ?`;
   [DEPT_REC] = EXEC(SQL_STMT,[DEPT_ID]);
   SQL_STMT = `UPDATE dept SET id = 200 WHERE id = :1 RETURNING name INTO :2`;
   /* ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR THIS EXECUTE IMMEDIATE CASE IS PLANNED TO BE DELIVERED IN THE FUTURE ** */
   /*    EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd */
   ;
   SQL_STMT = `delete from dept where id = :1 RETURNING name INTO :2`;
   /* ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR THIS EXECUTE IMMEDIATE CASE IS PLANNED TO BE DELIVERED IN THE FUTURE ** */
   /*    EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO dept_id_upd */
   ;
   EXEC(`INSERT INTO PUBLIC.dept VALUES (12, 'NAME1', 'TEXAS')`);
   EXEC(`INSERT INTO PUBLIC.DEPT VALUES(13, '${concatValue(DEPT_NAME)}', 'LA')`);
   EXEC(`DELETE FROM PUBLIC.dept WHERE id = ?`,[DEPT_ID2]);
   EXEC(`ALTER SESSION SET /*** MSC-WARNING - MSCEWI3058 - NLS_DATE_FORMAT SESSION PARAMETER DOES NOT ENFORCE THE INPUT FORMAT IN ORACLE ***/
 DATE_INPUT_FORMAT = 'DD-MM-YYYY' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY'`);
   /* ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR THIS EXECUTE IMMEDIATE CASE IS PLANNED TO BE DELIVERED IN THE FUTURE ** */
   /*    EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals */
   ;
$$;

Since the "RETURNING INTO" clause requires special analysis of the statement executed, its translation is planned to be delivered in the future.

Transformation for the following line correspond to collection types which is work in progress:

TYPE NumList IS TABLE OF NUMBER;

Currently the next statement is being emitted but the class is not being created yet. A warning will be applied in the future to all the uses of the unsupported custom types.

let SALS = new NUMLIST();

Also the following EXECUTE IMMEDIATE related with the BULK COLLECT into the sals variable, is also work in progress.

EXECUTE IMMEDIATE 'SELECT id FROM dept' BULK COLLECT INTO sals;

Errors and Exception Handling

You might also be interested in Raise helper

Raise Helper Usage

Oracle

CREATE OR REPLACE PROCEDURE HANDLERS_WITH_OTHERS_COMMENTS AUTHID DEFINER IS
  deadlock_detected EXCEPTION;
  deadlock_dex EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
  PRAGMA EXCEPTION_INIT(deadlock_dex, -63);
BEGIN

  IF true THEN
    RAISE NO_DATA_FOUND;
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20010, SQLERRM);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, SQLERRM, PARM);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, SQLERRM, TRUE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, SQLERRM, FALSE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20000, 'CUSTOM ERROR MESSAGE', TRUE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20010, 'SECOND CUSTOM ERROR MESSAGE', TRUE);
  END IF;
  IF TRUE THEN
    RAISE_APPLICATION_ERROR(-20010, 'OTHER CUSTOM ERROR MESSAGE', FALSE);
  END IF;

EXCEPTION
    WHEN EXC_NAME THEN
        --Handle Exc_name  found exception
        null;
    WHEN NO_DATA_FOUND THEN
        --Handle No data found exception
        null;
    WHEN OTHERS THEN
        --Handler for others exception
        null;
END;

Snowflake

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

CREATE OR REPLACE PROCEDURE PUBLIC.HANDLERS_WITH_OTHERS_COMMENTS ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code   

   /* ** MSC-WARNING - MSCEWI3052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */
   /*   deadlock_detected EXCEPTION */
   ;
   /* ** MSC-WARNING - MSCEWI3052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */
   /*   deadlock_dex EXCEPTION */
   ;
   /* ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED. ERROR CODE IS ASSIGNED IN RAISE CALL ** */
   /*   PRAGMA EXCEPTION_INIT(deadlock_detected, -60) */
   ;
   /* ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED. ERROR CODE IS ASSIGNED IN RAISE CALL ** */
   /*   PRAGMA EXCEPTION_INIT(deadlock_dex, -63) */
   ;
   try {
      if (true) {
         RAISE(100,`NO_DATA_FOUND`,`Single row SELECT returned no rows or your program referenced a deleted element in a nested table or an uninitialized element in an associative array (index-by table).`);
      }
      if (true) {
         RAISE(-20010,SQLERRM);
      }
      if (true) {
         // ** MSC-WARNING - MSCEWI3066 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT PARM WAS REMOVED. **
         RAISE(-20000,SQLERRM);
      }
      if (true) {
         // ** MSC-WARNING - MSCEWI3066 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
         RAISE(-20000,SQLERRM);
      }
      if (true) {
         RAISE(-20000,SQLERRM);
      }
      if (true) {
         // ** MSC-WARNING - MSCEWI3066 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
         RAISE(-20000,`CUSTOM ERROR MESSAGE`);
      }
      if (true) {
         // ** MSC-WARNING - MSCEWI3066 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
         RAISE(-20010,`SECOND CUSTOM ERROR MESSAGE`);
      }
      if (true) {
         RAISE(-20010,`OTHER CUSTOM ERROR MESSAGE`);
      }
   } catch(error) {
      switch(error.name) {
         case `EXC_NAME`: {
            //Handle Exc_name  found exception
            null;
            break;
         }
         case `NO_DATA_FOUND`: {
            //Handle No data found exception
            null;
            break;
         }
         default: {
            //Handler for others exception
            null;
            break;
         }
      }
   }
$$;

When there is not OTHERS handler, SnowConvert uses the "default" case in the switch that throws the original Error Object.

Commit

You might also be interested in EXEC helper

Oracle

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

Snowflake

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

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

CASE

Oracle

CREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE2 ()
IS
  localVar1 NUMBER;
  localVar2 VARCHAR(100);
BEGIN
CASE (localVar1)
WHEN 1 THEN
    localVar2 := 'one';
WHEN 2 THEN 
    localVar := 'two'; 
WHEN 3 THEN 
    lovalVar := 'three';
ELSE
    localVar := 'error';
END CASE;

CASE
WHEN localVar = 1 THEN
    localVar2 := 'one';
WHEN localVar = 2 THEN 
    localVar := 'two'; 
WHEN localVar = 3 THEN 
    lovalVar := 'three';
ELSE
    localVar := 'error';
END CASE;
END;

Snowflake

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

/*** MSC-WARNING - MSCEWI3056 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. ***/
CREATE OR REPLACE PROCEDURE PROCEDURE2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let LOCALVAR1;
   let LOCALVAR2;
   switch(LOCALVAR1) {
      case 1:LOCALVAR2 = `one`;
      break;
      case 2:LOCALVAR = `two`;
      break;
      case 3:LOVALVAR = `three`;
      break;
      default:LOCALVAR = `error`;
      break;
   }
   if (LOCALVAR == 1) {
      LOCALVAR2 = `one`;
   } else if (LOCALVAR == 2) {
      LOCALVAR = `two`;
   } else if (LOCALVAR == 3) {
      LOVALVAR = `three`;
   } else {
      LOCALVAR = `error`;
   }
$$;

CASE in a variable assignment

Oracle

CREATE OR REPLACE EDITIONABLE PROCEDURE PROCEDURE2 ()
IS
  localVar1 NUMBER;
BEGIN
	var1 := CASE flag
	WHEN 1 THEN 'one'
	WHEN 2 THEN 'two'
	WHEN 3 THEN 'three'
	WHEN 4 THEN 'four'
	ELSE 'unknown' END;

END;

Snowflake

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

/*** MSC-WARNING - MSCEWI3056 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. ***/
CREATE OR REPLACE PROCEDURE PROCEDURE2 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   let LOCALVAR1;
   VAR1 = FLAG == 1 && `one` || (FLAG == 2 && `two` || (FLAG == 3 && `three` || (FLAG == 4 && `four` || `unknown`)));
$$;

Call to external C or Java programs

Oracle

CREATE OR REPLACE EDITIONABLE PROCEDURE "OWB_REP_OWNER"."WB_RT_DP_CREATE_FKPARTITION" (prfID IN NUMBER,datatype IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'oracle.wh.service.impl.dataProfile.analysis.storedprocs.ForeignKey.createFKPartition(int,java.lang.String)';

Snowflake

/*** MSC-WARNING - MSCEWI3056 - SNOWFLAKE DOESN'T SUPPORT VERSIONING OF OBJECTS. DEVELOPERS SHOULD CONSIDER ALTERNATE APPROACHES FOR CODE VERSIONING. ***/
CREATE OR REPLACE PROCEDURE OWB_REP_OWNER.WB_RT_DP_CREATE_FKPARTITION (prfID FLOAT,datatype STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   /* ** MSC-ERROR - MSCEWI3012 - CALL TO AN EXTERNAL JAVA PROGRAM IS NOT SUPPORTED ** */
   /* LANGUAGE JAVA NAME 'oracle.wh.service.impl.dataProfile.analysis.storedprocs.ForeignKey.createFKPartition(int,java.lang.String)' */
   ;
$$;

GOTO

Planned for future.