Cursor with IF statement
This pattern defines a function that conditionally uses a cursor to fetch and return a value based on an IF
statement.
Components:
Function Declaration:
CREATE FUNCTION functionName(parameters) RETURN returnType
Declares the function with input parameters and the return type.
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.
Variable Declaration:
Declares variables, including the return variable.
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 aRETURN
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
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;
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;
CREATE OR REPLACE FUNCTION func3 (
company_ IN NUMBER) RETURN NUMBER
IS
CURSOR getmaxperiod IS
SELECT max(col2)
FROM table1;
max_period_ NUMBER := 0;
BEGIN
IF company_ = 1 THEN
OPEN getmaxperiod;
FETCH getmaxperiod INTO max_period_ ;
CLOSE getmaxperiod;
END IF;
RETURN max_period_;
END func10;
Known Issues
No issues were found.
Related EWIS
No EWIs related.
Last updated