//
// Demonstration of how to use the various MaxDB functions
//
// User defined procedure (UDP) using recursive expression
// and interting result in a table
DROP   TABLE mona.test
//
CREATE TABLE mona.test (row INT,num10 FIXED(10))
//
DROP   DBPROC test_data
//
CREATE DBPROC test_data (IN rows INT) AS
VAR row INT;
    n10 FIXED(10);
    DECLARE c_test_data CURSOR FOR WITH RECURSIVE td (levl,r10) AS 
     (SELECT 1
           , FIXED(RAND(1)*10000000000,10) 
        FROM dbadmin.dual 
       UNION ALL 
      SELECT levl + 1
           , FIXED(RAND(levl + 1)*10000000000,10) 
        FROM td  
       WHERE levl < 1000 
     )
      SELECT levl,r10
        FROM td;
  FETCH NEXT c_test_data INTO :row, :n10;
  WHILE $RC = 0 DO BEGIN
    INSERT INTO mona.test (row,num10) VALUES (:row, :n10);
    FETCH NEXT c_test_data INTO :row, :n10;
  END;
  CLOSE c_test_data;
//
CALL test_data (1000)
//
SELECT * FROM mona.test WHERE rowno < 5
//

> DROP   TABLE mona.test
> CREATE TABLE mona.test (row INT,num10 FIXED(10))
> DROP   DBPROC test_data
> CREATE DBPROC test_data (IN rows INT) AS VAR row INT;     n10 FIXED(10);     DECLARE c_test_data CURSOR FOR WITH RECURSIVE td (levl,r10) AS      (SELECT 1            , FIXED(RAND(1)*10000000000,10)         FROM dbadmin.dual        UNION ALL       SELECT levl + 1            , FIXED(RAND(levl + 1)*10000000000,10)         FROM td        WHERE levl < 1000      )       SELECT levl,r10         FROM td;   FETCH NEXT c_test_data INTO :row, :n10;   WHILE $RC = 0 DO BEGIN     INSERT INTO mona.test (row,num10) VALUES (:row, :n10);     FETCH NEXT c_test_data INTO :row, :n10;   END;   CLOSE c_test_data;
> CALL test_data (1000)
> SELECT * FROM mona.test WHERE rowno < 5
> | ROW            | NUM10            |
> | -------------- | ---------------- |
> |           1000 |       9130434783 |
> |            999 |       9086956522 |
> |            998 |       9043478261 |
> |            997 |       9000000000 |