SQL Language Elements
Transformation for syntax and semantics of PL/SQL language elements.
Some parts in the output code are omitted for clarity reasons.
Cursor FOR LOOP
You might also be interested in Cursor helper and Cursor declaration.
Oracle
IN -> Oracle_01.sql
--Additional Params: -t JavaScript
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.
OUT -> Oracle_01.sql
CREATE OR REPLACE PROCEDURE PROC1 ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
// SnowConvert Helpers Code section is omitted.
let MYVARIABLE1;
let MYOTHERVARIABLE2 = 1;
let C1 = new CURSOR(`SELECT * FROM
Table1
WHERE ID = 123`,() => []);
// ** SSC-EWI-0022 - 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
TABLE1
WHERE ID = ?`,(PARAMCURSOR1) => [PARAMCURSOR1]);
C1.OPEN();
while ( C1.NEXT() ) {
let MYCURSORRECORD = C1.CURRENT;
MYVARIABLE1 = MYCURSORRECORD.COL1;
}
C1.CLOSE();
// ** SSC-EWI-0022 - ONE OR MORE IDENTIFIERS IN THIS STATEMENT WERE CONSIDERED PARAMETERS BY DEFAULT. REFERENCED TABLE NOT FOUND. **
for(var MYCURSORRECORD_CURSOR = new CURSOR(`(SELECT * FROM
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
IN -> Oracle_02.sql
--Additional Params: -t JavaScript
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;