| DML | //
// Demonstration of how to use the various MaxDB functions
//
// Use of UDF RAND and recursive SQL to generate 10 rows of test data
DECLARE test_data CURSOR FOR WITH RECURSIVE td
(levl, num1, num2, num3, r0, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10) AS
(SELECT 1
, 1
, 0.5
, 0.5
, RAND(1) AS r0
, FIXED(RAND(1)*10,1) AS r1
, FIXED(RAND(1)*100,2) AS r2
, FIXED(RAND(1)*1000,3) AS r3
, FIXED(RAND(1)*10000,4) AS r4
, FIXED(RAND(1)*100000,5) AS r5
, FIXED(RAND(1)*1000000,6) AS r6
, FIXED(RAND(1)*10000000,7) AS r7
, FIXED(RAND(1)*100000000,8) AS r8
, FIXED(RAND(1)*1000000000,9) AS r9
, FIXED(RAND(1)*10000000000,10) AS r10
FROM dual
UNION ALL
SELECT levl + 1
, num1 * 10
, FIXED((num2 * 1.2),10,8)
, FIXED(num3 * 1.2,10,8) * -1
, RAND(levl + 1) AS r0
, FIXED(RAND(levl + 1)*10,1) AS r1
, FIXED(RAND(levl + 1)*100,2) AS r2
, FIXED(RAND(levl + 1)*1000,3) AS r3
, FIXED(RAND(levl + 1)*10000,4) AS r4
, FIXED(RAND(levl + 1)*100000,5) AS r5
, FIXED(RAND(levl + 1)*1000000,6) AS r6
, FIXED(RAND(levl + 1)*10000000,7) AS r7
, FIXED(RAND(levl + 1)*100000000,8) AS r8
, FIXED(RAND(levl + 1)*1000000000,9) AS r9
, FIXED(RAND(levl + 1)*10000000000,10) AS r10
FROM td
WHERE levl < 10
)
SELECT *
FROM td
FOR REUSE /* the result set "td" is kept until db session ends */
//
// Other way to define test_data:
SELECT -2.4,'ABCDEF','1996-04-22-23.58.58.123456' FROM dual UNION ALL
SELECT +0.0,'ABCD ' ,'1996-08-15-15.15.15.151515' FROM dual UNION ALL
SELECT +1.8,'AB ' ,'0001-01-01-00.00.00.000000' FROM dual
//
|
| Result | > DECLARE test_data CURSOR FOR WITH RECURSIVE td (levl, num1, num2, num3, r0, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10) AS (SELECT 1 , 1 , 0.5 , 0.5 , RAND(1) AS r0 , FIXED(RAND(1)*10,1) AS r1 , FIXED(RAND(1)*100,2) AS r2 , FIXED(RAND(1)*1000,3) AS r3 , FIXED(RAND(1)*10000,4) AS r4 , FIXED(RAND(1)*100000,5) AS r5 , FIXED(RAND(1)*1000000,6) AS r6 , FIXED(RAND(1)*10000000,7) AS r7 , FIXED(RAND(1)*100000000,8) AS r8 , FIXED(RAND(1)*1000000000,9) AS r9 , FIXED(RAND(1)*10000000000,10) AS r10 FROM dual UNION ALL SELECT levl + 1 , num1 * 10 , FIXED((num2 * 1.2),10,8) , FIXED(num3 * 1.2,10,8) * -1 , RAND(levl + 1) AS r0 , FIXED(RAND(levl + 1)*10,1) AS r1 , FIXED(RAND(levl + 1)*100,2) AS r2 , FIXED(RAND(levl + 1)*1000,3) AS r3 , FIXED(RAND(levl + 1)*10000,4) AS r4 , FIXED(RAND(levl + 1)*100000,5) AS r5 , FIXED(RAND(levl + 1)*1000000,6) AS r6 , FIXED(RAND(levl + 1)*10000000,7) AS r7 , FIXED(RAND(levl + 1)*100000000,8) AS r8 , FIXED(RAND(levl + 1)*1000000000,9) AS r9 , FIXED(RAND(levl + 1)*10000000000,10) AS r10 FROM td WHERE levl < 10 ) SELECT * FROM td FOR REUSE /* the result set "td" is kept until db session ends */
> | LEVL | NUM1 | NUM2 | NUM3 | R0 | R1 | R2 | R3 | R4 | R5 | R6 | R7 | R8 | R9 | R10 |
> | ---------------------------------------------- | ---------------------------------------------- | ----------------- | ---------------------------------------------- | ------------------------------------------------------- | ------ | ------ | ------- | -------- | ---------- | ----------- | ------------ | -------------- | --------------- | ---------------- |
> | 10 | 1000000000 | 2.57989018 | -2.57989018 | 0.6260869565217391304348000000000000000 | 6 | 63 | 626 | 6261 | 62609 | 626087 | 6260870 | 62608696 | 626086957 | 6260869565 |
> | 9 | 100000000 | 2.14990848 | 2.14990848 | 0.6217391304347826086957000000000000000 | 6 | 62 | 622 | 6217 | 62174 | 621739 | 6217391 | 62173913 | 621739130 | 6217391304 |
> | 8 | 10000000 | 1.79159040 | -1.7915904 | 0.6173913043478260869565000000000000000 | 6 | 62 | 617 | 6174 | 61739 | 617391 | 6173913 | 61739130 | 617391304 | 6173913043 |
> | 7 | 1000000 | 1.49299200 | 1.492992 | 0.6130434782608695652174000000000000000 | 6 | 61 | 613 | 6130 | 61304 | 613043 | 6130435 | 61304348 | 613043478 | 6130434783 |
> | 6 | 100000 | 1.24416000 | -1.24416 | 0.6086956521739130434783000000000000000 | 6 | 61 | 609 | 6087 | 60870 | 608696 | 6086957 | 60869565 | 608695652 | 6086956522 |
> | 5 | 10000 | 1.03680000 | 1.0368 | 0.6043478260869565217391000000000000000 | 6 | 60 | 604 | 6043 | 60435 | 604348 | 6043478 | 60434783 | 604347826 | 6043478261 |
> | 4 | 1000 | 0.86400000 | -0.864 | 0.6000000000000000000000000000000000000 | 6 | 60 | 600 | 6000 | 60000 | 600000 | 6000000 | 60000000 | 600000000 | 6000000000 |
> | 3 | 100 | 0.72000000 | 0.72 | 0.5217391304347826086957000000000000000 | 5 | 52 | 522 | 5217 | 52174 | 521739 | 5217391 | 52173913 | 595652174 | 5956521739 |
> | 2 | 10 | 0.60000000 | -0.6 | 0.5173913043478260869565000000000000000 | 5 | 52 | 517 | 5174 | 51739 | 517391 | 5173913 | 51739130 | 517391304 | 5173913043 |
> | 1 | 1 | 0.50000000 | 0.5 | 0.5130434782608695652174000000000000000 | 5 | 51 | 513 | 5130 | 51304 | 513043 | 5130435 | 51304348 | 513043478 | 5130434783 |
> SELECT -2.4,'ABCDEF','1996-04-22-23.58.58.123456' FROM dual UNION ALL SELECT +0.0,'ABCD ' ,'1996-08-15-15.15.15.151515' FROM dual UNION ALL SELECT +1.8,'AB ' ,'0001-01-01-00.00.00.000000' FROM dual
> | EXPRESSION1 | EXPRESSION2 | EXPRESSION3 |
> | ------------------- | ----------- | -------------------------- |
> | 1.8 | AB | 0001-01-01-00.00.00.000000 |
> | 0.0 | ABCD | 1996-08-15-15.15.15.151515 |
> | -2.4 | ABCDEF | 1996-04-22-23.58.58.123456 |
|