Cursor for a return variable

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

OUT -> Oracle_01.sql

CREATE OR REPLACE FUNCTION func1 (company_ VARCHAR, book_id_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/, object_id_ VARCHAR)
RETURNS INTEGER
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "09/06/2024" }}'
AS
$$
   WITH declaration_variables_cte1 AS
   (
      SELECT
         (
         SELECT col2
         FROM table1
         WHERE col3 = company_
         AND   col4 = object_id_
         AND   col5 = book_id_
         FETCH FIRST 1 ROW ONLY) AS temp_
   )
   SELECT
      temp_
   FROM
      declaration_variables_cte1
$$;

Result

FUNC1()          | 
-----------------+ 
2004-05-03.      |

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

OUT -> Oracle_02.sql

CREATE OR REPLACE FUNCTION func2 (fa_period_ NUMBER(38, 18),
  to_date_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/ DEFAULT NULL,
  from_date_ TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/ DEFAULT NULL )
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "oracle",  "convertedOn": "09/06/2024" }}'
AS
$$
   WITH declaration_variables_cte1 AS
   (
      SELECT
         0 AS
         value_,
         Get_Cond_Date( to_date_, 'MAX' ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Get_Cond_Date' NODE ***/!!! AS
         cond_date_to_,
         Get_Cond_Date( from_date_, 'MIN' ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'Get_Cond_Date' NODE ***/!!! AS
         cond_date_from_
   ),
   declaration_variables_cte2 AS
   (
      SELECT
         (
         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_
         FETCH FIRST 1 ROW ONLY) AS value_,
         cond_date_to_,
         cond_date_from_
      FROM
         declaration_variables_cte1
   )
   SELECT
      (NVL(value_,0))
   FROM
      declaration_variables_cte2
$$;

Result

FUNC1()          | 
-----------------+ 
2004-05-03.      |

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.