例: 再帰処理を指定するSQLプロシージャの作成 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ定義言語 構文規則および例

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
2021年1月
Language
日本語
Last Update
2021-03-30
dita:mapPath
ja-JP/ncd1596241368722.ditamap
dita:ditavalPath
ja-JP/ncd1596241368722.ditaval
dita:id
B035-1144
Product Category
Software
Teradata Vantage

次の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;