Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35979

Oracle: Cursor expressions

Details

    Description

      Syntax:

      CURSOR <left paren> subquery <right paren>

      This example passes a cursor expression with a set of INTs to the function f1() which calculates the sum of all INTs in the set:

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1);
      		 
      INSERT INTO t1 VALUES (2);
      		 
      INSERT INTO t1 VALUES (3);
      		 
      CREATE OR REPLACE FUNCTION f1(c SYS_REFCURSOR) RETURN INT
      		 
      AS
      		 
        v INT;
      		 
        t INT := 0;
      		 
      BEGIN
      		 
        LOOP
      		 
          FETCH c INTO v;
      		 
          EXIT WHEN c%NOTFOUND;
      		 
          t:= t + v;
      		 
        END LOOP;
      		 
        RETURN t;
      		 
      END;
      		 
      /
      		 
      CREATE OR REPLACE PROCEDURE p1
      		 
      AS
      		 
        t INT;
      		 
      BEGIN
      		 
        FOR i IN 1..3 LOOP
      		 
          SELECT f1(cursor(SELECT a FROM t1 WHERE a<=i)) INTO t FROM DUAL;
      		 
          DBMS_OUTPUT.PUT_LINE(' sum(t1.a<=' || i || ')=' || t);
      		 
        END LOOP;
      		 
      END;
      		 
      /
      		 
      CALL p1;


       sum(t1.a<=1)=1
      		 
       sum(t1.a<=2)=3
      		 
       sum(t1.a<=3)=6

      TODO:
      Add details (rules, restrictions, when the cursor gets closed, etc)

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-35973 Oracle Compatibility Project 1 for 2025

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-10152 Add support for TYPE .. IS REF CURSOR

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR

          • Major - Major loss of function.
          • Closed
          links to

          Web Link Cursor expressions in Oracle docs

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            Description Syntax:
            {code:sql}
            CURSOR <left paren> subquery <right paren>
            {code}

            This example passes a cursor expression with a set of INTs to the function f1() which calculates the sum of all INTs in the set:

            {code:sql}
            DROP TABLE t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (1);
            INSERT INTO t1 VALUES (2);
            INSERT INTO t1 VALUES (3);
            CREATE OR REPLACE FUNCTION f1(c SYS_REFCURSOR) RETURN INT
            AS
              v INT;
              t INT := 0;
            BEGIN
              LOOP
                FETCH c INTO v;
                EXIT WHEN c%NOTFOUND;
                t:= t + v;
              END LOOP;
              RETURN t;
            END;
            /
            CREATE OR REPLACE PROCEDURE p1
            AS
              t INT;
            BEGIN
              FOR i IN 1..3 LOOP
                SELECT f1(cursor(SELECT a FROM t1 WHERE a<=i)) INTO t FROM DUAL;
                DBMS_OUTPUT.PUT_LINE(' sum(1..' || i || ')=' || t);
              END LOOP;
            END;
            /
            CALL p1;
            {code}
            {noformat}
            sum(1..1)=1
            sum(1..2)=3
            sum(1..3)=6
            {noformat}

            TODO:
            Add details (rules, restrictions, when the cursor gets closed, etc)
            		Syntax:
            {code:sql}
            CURSOR <left paren> subquery <right paren>
            {code}

            This example passes a cursor expression with a set of INTs to the function f1() which calculates the sum of all INTs in the set:

            {code:sql}
            DROP TABLE t1;
            CREATE TABLE t1 (a INT);
            INSERT INTO t1 VALUES (1);
            INSERT INTO t1 VALUES (2);
            INSERT INTO t1 VALUES (3);
            CREATE OR REPLACE FUNCTION f1(c SYS_REFCURSOR) RETURN INT
            AS
              v INT;
              t INT := 0;
            BEGIN
              LOOP
                FETCH c INTO v;
                EXIT WHEN c%NOTFOUND;
                t:= t + v;
              END LOOP;
              RETURN t;
            END;
            /
            CREATE OR REPLACE PROCEDURE p1
            AS
              t INT;
            BEGIN
              FOR i IN 1..3 LOOP
                SELECT f1(cursor(SELECT a FROM t1 WHERE a<=i)) INTO t FROM DUAL;
                DBMS_OUTPUT.PUT_LINE(' sum(t1.a<=' || i || ')=' || t);
              END LOOP;
            END;
            /
            CALL p1;
            {code}
            {noformat}
             sum(t1.a<=1)=1
             sum(t1.a<=2)=3
             sum(t1.a<=3)=6
            {noformat}

            TODO:
            Add details (rules, restrictions, when the cursor gets closed, etc)
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Labels Compatibility Oracle
            ralf.gebhardt Ralf Gebhardt made changes -
            Parent MDEV-35973 [ 132608 ]
            Issue Type New Feature [ 2 ] Technical task [ 7 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            julien.fritsch Julien Fritsch made changes -
            Parent MDEV-35973 [ 132608 ]
            Issue Type Technical task [ 7 ] New Feature [ 2 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Minor [ 4 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Minor [ 4 ] Major [ 3 ]

            People

              Unassigned Unassigned
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.