例: 再帰処理を指定するSQLプロシージャの作成 - Teradata Database - Teradata Vantage NewSQL Engine - 例: 再帰処理を指定するSQLプロシージャの作成、CREATE PROCEDURE (SQL形式)およびREPLACE PROCEDURE (SQL形式) SQL文

次のCREATE PROCEDURE定義では、再帰処理を指定します。

     CREATE PROCEDURE rqspl(
       OUT rowcnt INTEGER)
     BEGIN
         DECLARE target1 INTEGER DEFAULT 0;
         DECLARE target2 INTEGER DEFAULT 0;
         DECLARE target3 INTEGER DEFAULT 0;
         DECLARE finalcount INTEGER DEFAULT 0;
         DECLARE pscursor SCROLL CURSOR FOR
         WITH RECURSIVE RQ(x,y,depth) AS
         (
          SELECT a1, b1, 0
 
          FROM t1
          UNION ALL
          SELECT a1, y, depth+1
 
          FROM RQ,t1
 
          WHERE y = a1
 
          AND   depth < 10
          )
         SELECT *
 
         FROM RQ;
         OPEN pscursor;
         FETCH pscursor INTO target1, target2, target3;
         -- if there is no row from the cursor, 
         -- SQLCODE=7632, SQLSTATE=02000
         WHILE (SQLCODE <> 7632)
         DO
            FETCH pscursor INTO target1, target2, target3;
            SET finalcount = finalcount+1;
         END WHILE;
         CLOSE pscursor;
         SET rowcnt = finalcount;
     END;