Cursor for a return variable

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Some parts in the output code are omitted for clarity reasons.

This pattern defines a function in Oracle PL/SQL that uses a cursor to fetch a single value and return it.

Components:

1. Function Declaration:

   • CREATE FUNCTION functionName(parameters) RETURN returnType

   • Declares the function with input parameters and the return type.

2. Variable Declarations:

   • Declares variables, including the return variable.

3. Cursor Declaration:

   • CURSOR cursorName IS SELECT singleColumn FROM ... WHERE ... [AND col1 = localVar1];

   • Defines a cursor to select a single column from a table with optional filtering conditions.

4. BEGIN-END Block:

   • Variables assignment.

   • Opens the cursor.

   • Fetch the result into the return variable.

   • Closes the cursor.

   • Returns the fetched value.

In this case, the variables are transformed into a common table expression (CTE). As well as the query within the cursor to which, in addition, the FETCH FIRST 1 ROW ONLY clause is added to simulate the FETCH CURSOR behavior.

RETURN statement is transformed to the final select.

Queries

Oracle

IN -> Oracle_01.sql

CREATE OR REPLACE FUNCTION func1 (
   company_ IN VARCHAR2,
   book_id_ IN DATE,
   object_id_ IN VARCHAR2 ) RETURN INTEGER
IS
   temp_ table1.col2%TYPE;
   CURSOR get_attr IS
      SELECT col2
      FROM table1
      WHERE col3 = company_
      AND   col4 = object_id_
      AND   col5 = book_id_;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN temp_;
END func1;

Snowflake

Result

Query

IN -> Oracle_02.sql

CREATE FUNCTION func2 (
   fa_period_   IN NUMBER,
   to_date_     IN DATE DEFAULT NULL,
   from_date_   IN DATE DEFAULT NULL ) RETURN NUMBER
IS
   value_                    NUMBER;
   cond_date_to_             DATE;
   cond_date_from_           DATE;
   CURSOR get_acq_value IS
      SELECT NVL(SUM(col1),0)
      FROM   table1
      WHERE  col3                   IN (DECODE(fa_period_, 1, 'val1_1', 'val2_1'))
      AND    col5           <= cond_date_to_
      AND    col5           >= cond_date_from_;
BEGIN
   value_ := 0;
   cond_date_to_       := Get_Cond_Date( to_date_, 'MAX' );
   cond_date_from_     := Get_Cond_Date( from_date_, 'MIN' );
   OPEN get_acq_value;
   FETCH get_acq_value INTO value_;
   CLOSE get_acq_value;
   RETURN (NVL(value_,0));
END func2;

Snowflake

Result

Known Issues

No issues were found.

Related EWIS

1. SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior.

2. SSC-EWI-0073: Pending Functional Equivalence Review.