Using a Cursor with a Cte

Using a cursor with a CTE

Just put it in before the common table expression:

DECLARE @FROMDATE DATETIME 
DECLARE @TODATE DATETIME 
select @FROMDATE=getdate()
select @TODATE =getdate()+7

declare boris cursor for

WITH DATEINFO(DATES)
     AS (SELECT @FROMDATE
         UNION ALL
         SELECT DATES + 1
         FROM   DATEINFO
         WHERE  DATES < @TODATE)
SELECT *
FROM   DATEINFO
OPTION (MAXRECURSION 0)

(However, insert usual cautions about cursors almost always being the wrong tool for the job. If you can find a way to do the whole operation in a set based manner, it's usually preferable, and likely to perform better (or at least be more amenable to performance tuning))

Common table expression defining a function in a cursor

Yes and no. The 12.1 docs for the CURSOR statement explicitly say:

Restriction on select_statement

This select_statement cannot have a WITH clause.

This docs are wrong in this case, since you can have a WITH clause, it apparently just can't have the new 12c PL/SQL declarations in it. This block works fine, for example.

DECLARE
  CURSOR LV_CUR IS
  WITH  
   TEMP_AV AS
    (
      SELECT
        level
      FROM
       DUAL
        CONNECT BY LEVEL < 10
    )
  SELECT
    *
  FROM
    TEMP_AV;
BEGIN
  FOR I IN LV_CUR
  LOOP
    NULL;
    dbms_output.put_line(i.level);
  END LOOP;
END;
/

Why is CTE better than cursor/derived table/ subqueries/ temp table etc.?

A (non-recursive) CTE does not use cursors. It is a set based approach. That's the big difference compared to using cursors. But then that's true of not using cursors in general.

Cursors should be avoided where absolutely possible (as I'm sure we are all aware).

A CTE is not necessarily better than using a derived table, but does lead to more understandable TSQL code. A CTE is really just shorthand for a query or subquery; something akin to a temporary view.

The situation where CTE's might not be the best approach, is when the query plan optimiser gets inaccurate row estimates for the CTE.

Related question: What are the advantages/disadvantages of using a CTE?

Using a Cursor with a Cte