This section is a translation specification for the compound statements
This section is a work in progress, information may change in the future.
Some parts in the output code are omitted for clarity reasons.
General description
The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.
A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. (PL/SQL Anonymous Blocks)
The BEGIN...END block in Oracle can have the following characteristics:
DECLARE age NUMBER :=18;BEGIN IF age >=18 THEN DBMS_OUTPUT.PUT_LINE('You are an adult.'); ELSE DBMS_OUTPUT.PUT_LINE('You are a minor.'); END IF;END;
Statement processed.
You are an adult.
Snowflake
When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.
BEGIN DECLARE day_of_week NUMBER :=3; BEGIN CASE day_of_week WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Sunday'); WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Monday'); WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Tuesday'); WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Wednesday'); WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Thursday'); WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('Friday'); WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('Saturday'); ELSE DBMS_OUTPUT.PUT_LINE('Invalid day'); END CASE; END;END;
Statement processed.
Tuesday
Snowflake
When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.
Anonymous block in Oracle may have calls to procedures. Furthermore, the following documentation may be useful: SnowConvert Procedure documentation.
The following example uses the OUT parameters, the information about the current transformation can be found here: SnowConvert OUTPUT Parameters
Oracle
IN -> Oracle_04.sql
-- Procedure declarationCREATE OR REPLACE PROCEDURE calculate_sum( p_num1 IN NUMBER, p_num2 IN NUMBER, p_result OUT NUMBER)ISBEGIN-- Calculate the sum of the two numbers p_result := p_num1 + p_num2;END;/-- Anonymous block with a procedure callDECLARE-- Declare variables to hold the input and output values v_num1 NUMBER :=10; v_num2 NUMBER :=20; v_result NUMBER;BEGIN-- Call the procedure with the input values and get the result calculate_sum(v_num1, v_num2, v_result);-- Display the result DBMS_OUTPUT.PUT_LINE('The sum of '|| v_num1 ||' and '|| v_num2 ||' is '|| v_result);END;/
Statement processed.
The sum of 10 and 20 is 30
Snowflake
OUT -> Oracle_04.sql
-- Procedure declarationCREATE OR REPLACE PROCEDURE calculate_sum (p_num1 NUMBER(38, 18), p_num2 NUMBER(38, 18), p_result NUMBER(38, 18))RETURNS VARIANTLANGUAGE SQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTE AS CALLERAS$$ BEGIN-- Calculate the sum of the two numbers p_result := :p_num1 + :p_num2; RETURN p_result; END;$$;-- Anonymous block with a procedure callDECLARE-- Declare variables to hold the input and output values v_num1 NUMBER(38, 18) :=10; v_num2 NUMBER(38, 18) :=20; v_result NUMBER(38, 18); call_results VARIANT;BEGIN call_results := ( CALL-- Call the procedure with the input values and get the result calculate_sum(:v_num1, :v_num2, :v_result) ); v_result := :call_results;-- Display the result--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. ** call_results := ( CALL DBMS_OUTPUT.PUT_LINE_UDF('The sum of '|| NVL(:v_num1 :: STRING, '') ||' and '|| NVL(:v_num2 :: STRING, '') ||' is '|| NVL(:v_result :: STRING, '')) ); RETURN call_results;END;
The following warning may be added in the future:
/*** MSC-WARNING - MSCEWI3058 - NLS_DATE_FORMAT SESSION PARAMETER DOES NOT ENFORCE THE INPUT FORMAT IN ORACLE ***/
OUT -> Oracle_05.sql
DECLARE lv_sql_txt VARCHAR(200);BEGIN lv_sql_txt :='ALTER SESSION SET nls_date_format = ''DD-MM-YYYY'''; !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!! EXECUTE IMMEDIATE :lv_sql_txt;END;
anonymous block
Done
6. Cursors
The following example displays the usage of a cursor inside a BEGIN...END block. Review the following documentation to learn more: Cursor documentation.
Oracle
IN -> Oracle_06.sql
CREATE TABLE employee ( ID_Number NUMBER, emp_Name VARCHAR(200), emp_Phone NUMBER);INSERT INTO employee VALUES (1, 'NameA NameZ', 1234567890);INSERT INTO employee VALUES (2, 'NameB NameY', 1234567890);DECLARE var1 VARCHAR(20); CURSOR cursor1 IS SELECT emp_Name FROM employee ORDER BY ID_Number;BEGIN OPEN cursor1; FETCH cursor1 INTO var1; CLOSE cursor1; DBMS_OUTPUT.PUT_LINE(var1);END;
Statement processed.
NameA NameZ
Snowflake
When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.
CREATE OR REPLACE TABLE employee ( ID_Number NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/, emp_Name VARCHAR(200), emp_Phone NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}';INSERT INTO employeeVALUES (1, 'NameA NameZ', 1234567890);INSERT INTO employeeVALUES (2, 'NameB NameY', 1234567890);DECLARE var1 VARCHAR(20); cursor1 CURSOR FOR SELECT emp_Name FROM employee ORDER BY ID_Number; call_results VARIANT;BEGIN OPEN cursor1; FETCH cursor1 INTO :var1; CLOSE cursor1;--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. ** call_results := ( CALL DBMS_OUTPUT.PUT_LINE_UDF(:var1) ); RETURN call_results;END;
CREATE TABLE employee ( ID_Number NUMBER, emp_Name VARCHAR(200), emp_Phone NUMBER);INSERT INTO employee VALUES (1, 'NameA NameZ', 1234567890);INSERT INTO employee VALUES (2, 'NameB NameY', 1234567890);DECLARE var_Result NUMBER;BEGIN SELECT COUNT(*) INTO var_Result FROM employee; DBMS_OUTPUT.PUT_LINE(var_Result);END;
Statement processed.
2
Snowflake
When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.
CREATE OR REPLACE TABLE employee ( ID_Number NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/, emp_Name VARCHAR(200), emp_Phone NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}' ; INSERT INTO employeeVALUES (1, 'NameA NameZ', 1234567890); INSERT INTO employeeVALUES (2, 'NameB NameY', 1234567890); DECLARE var_Result NUMBER(38, 18); call_results VARIANT; BEGIN SELECT COUNT(*) INTO :var_Result FROM employee;--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. ** call_results := ( CALL DBMS_OUTPUT.PUT_LINE_UDF(:var_Result) ); RETURN call_results; END;
anonymous block
2
8. Join Statements
For more information review the following documentation: Joins documentation.
Oracle
IN -> Oracle_08.sql
CREATE TABLE t1 (col1 INTEGER);CREATE TABLE t2 (col1 INTEGER);INSERT INTO t1 (col1) VALUES (2);INSERT INTO t1 (col1) VALUES (3);INSERT INTO t1 (col1) VALUES (4);INSERT INTO t2 (col1) VALUES (1);INSERT INTO t2 (col1) VALUES (2);INSERT INTO t2 (col1) VALUES (2);INSERT INTO t2 (col1) VALUES (3);DECLARE total_price FLOAT; CURSOR cursor1 IS SELECT t1.col1 as FirstTable, t2.col1 as SecondTable FROM t1 INNER JOIN t2ON t2.col1 = t1.col1 ORDER BY 1,2;BEGIN total_price :=0.0; FOR rec IN cursor1 LOOP total_price := total_price + rec.FirstTable; END LOOP; DBMS_OUTPUT.PUT_LINE(total_price);END;
Statement processed.
7
Snowflake
When calling a procedure or user-defined function (UDF), generating code is needed to support the equivalence as call_results variable. In this case, is used to print the information.
CREATE OR REPLACE TABLE t1 (col1 INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}';CREATE OR REPLACE TABLE t2 (col1 INTEGER)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}';INSERT INTO t1(col1) VALUES (2);INSERT INTO t1(col1) VALUES (3);INSERT INTO t1(col1) VALUES (4);INSERT INTO t2(col1) VALUES (1);INSERT INTO t2(col1) VALUES (2);INSERT INTO t2(col1) VALUES (2);INSERT INTO t2(col1) VALUES (3);DECLARE total_price FLOAT; cursor1 CURSOR FOR SELECT t1.col1 as FIRSTTABLE, t2.col1 as SECONDTABLE FROM t1 INNER JOIN t2ON t2.col1 = t1.col1 ORDER BY 1,2; call_results VARIANT;BEGIN total_price :=0.0; OPEN cursor1;--** SSC-PRF-0004 - THIS STATEMENT HAS USAGES OF CURSOR FOR LOOP ** FOR rec IN cursor1 DO total_price := !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN AproxNumeric AND unknown ***/!!! :total_price + rec.FIRSTTABLE; END FOR; CLOSE cursor1;--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. ** call_results := ( CALL DBMS_OUTPUT.PUT_LINE_UDF(:total_price) ); RETURN call_results;END;
9. Exception handling
Oracle
IN -> Oracle_09.sql
DECLARE v_result NUMBER;BEGIN v_result :=1/0; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE( SQLERRM );END;
Statement processed.
ORA-01476: divisor is equal to zero
Snowflake
ZERO_DIVIDE exception in Snowflake is not supported.