Snowflake Documentation Home

COMPOUND STATEMENTS

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:

  1. Be nested.

  2. Contain the DECLARE statement for variables.

  3. Group multiple SQL or PL/SQL statements.

Oracle syntax

[DECLARE <Variable declaration>]
BEGIN
  <Executable statements>
[EXCEPTION <Exception handler>]
END

Snowflake syntax

BEGIN
    <statement>;
    [ <statement>; ... ]
[ EXCEPTION <exception_handler> ]
END;

In Snowflake, a BEGIN/END block can be the top-level construct inside an anonymous block (Snowflake documentation).

Sample Source Patterns

1. IF-ELSE block

Review the following documentation about IF statements to learn more: SnowConvert IF statements translation and Snowflake IF statement documentation

Oracle

IN -> Oracle_01.sql
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;

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.

Review the user-defined function (UDF) used here.

OUT -> Oracle_01.sql
DECLARE
    age NUMBER(38, 18) := 18;
    call_results VARIANT;
BEGIN
    IF (:age >= 18) THEN
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE_UDF('You are an adult.')
        );
    ELSE
        --** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **
        call_results := (
            CALL DBMS_OUTPUT.PUT_LINE_UDF('You are a minor.')
        );
    END IF;
    RETURN call_results;
END;

2. CASE statement

For more information, review the following documentation: SnowConvert CASE statement documentation and Snowflake CASE documentation

Oracle

IN -> Oracle_02.sql
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;

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.

Review the user-defined function (UDF) used here.

OUT -> Oracle_02.sql
DECLARE
   call_results VARIANT;
BEGIN
   DECLARE
      day_of_week NUMBER(38, 18) := 3;