Cursor with IF statement

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

This pattern defines a function that conditionally uses a cursor to fetch and return a value based on an IF statement.

Components:

  1. Function Declaration:

    • CREATE FUNCTION functionName(parameters) RETURN returnType

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

  2. 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.

  3. Variable Declaration:

    • Declares variables, including the return variable.

  4. BEGIN-END Block with IF Statement:

    • Variables assignment.

    • Check if a condition is true.

    • If true, opens the cursor, fetches the result into the return variable, closes the cursor, and returns the fetched value. (The cursor can also be opened in the ELSE block and must meet the same conditions)

    • The ELSE Block is optional, if it exists, it should only contain a single statement that can be an assignment or a RETURN statement.

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.

IF/ELSE statement can be handled using the CASE EXPRESSION inside the select allowing conditionals inside the queries. RETURN statement is transformed to the final select..

Queries

IN -> Oracle_01.sql
CREATE OR REPLACE FUNCTION func1 (
   company_          IN NUMBER) RETURN NUMBER
IS
   CURSOR getmaxperiod IS
      SELECT max(col2)
      FROM   table1;
   max_period_               NUMBER := 12;
BEGIN
   IF 1 = 1 THEN
      OPEN   getmaxperiod;
      FETCH  getmaxperiod INTO max_period_ ;
      CLOSE  getmaxperiod;
      RETURN max_period_;
   ELSE
      RETURN NULL;
   END IF;
END func1;
IN -> Oracle_02.sql
CREATE OR REPLACE FUNCTION func2(
   company_          IN NUMBER) RETURN NUMBER
IS
   CURSOR getmaxperiod IS
      SELECT max(col2)
      FROM   table1;
   max_period_               NUMBER := 1;
BEGIN
   max_period_:= 2;
   IF company_ = 1 THEN
      RETURN max_period_ * 2;
   ELSE
      OPEN   getmaxperiod;
      FETCH  getmaxperiod INTO max_period_ ;
      CLOSE  getmaxperiod;
      RETURN max_period_;
   END IF;
END func2;