Skip to content

Commit 6fda66f

Browse files
gaoxueyugaoxueyu
authored
Merge pull request #983 from rophy/feat/implicit-for
feat: add implicit RECORD declaration for FOR loop cursors
2 parents 2836675 + e9c6412 commit 6fda66f

4 files changed

Lines changed: 242 additions & 2 deletions

File tree

‎src/pl/plisql/src/Makefile‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -63,7 +63,7 @@ REGRESS = plisql_array plisql_call plisql_control plisql_copy plisql_domain \
6363
plisql_record plisql_cache plisql_simple plisql_transaction \
6464
plisql_trap plisql_trigger plisql_varprops plisql_nested_subproc \
6565
plisql_nested_subproc2 plisql_out_parameter plisql_type_rowtype \
66-
plisql_exception plisql_autonomous
66+
plisql_exception plisql_for_loop_implicit plisql_autonomous
6767

6868
# where to find ora_gen_keywordlist.pl and subsidiary files
6969
TOOLSDIR = $(top_srcdir)/src/tools

‎src/pl/plisql/src/expected/plisql_for_loop_implicit.out‎

Lines changed: 119 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,119 @@
1+
--
2+
-- Test implicit RECORD declaration in FOR loops (Oracle compatibility)
3+
--
4+
-- Setup test table
5+
CREATE TABLE test_loop_data (
6+
id NUMBER,
7+
name VARCHAR2(50),
8+
amount NUMBER
9+
);
10+
INSERT INTO test_loop_data VALUES (1, 'Item A', 100);
11+
INSERT INTO test_loop_data VALUES (2, 'Item B', 200);
12+
INSERT INTO test_loop_data VALUES (3, 'Item C', 300);
13+
-- Test 1: Simple FOR loop with implicit cursor variable
14+
-- Oracle: cr is implicitly declared as RECORD
15+
-- Expected: Should work (currently fails)
16+
DECLARE
17+
total NUMBER := 0;
18+
BEGIN
19+
FOR cr IN (SELECT id, name, amount FROM test_loop_data ORDER BY id) LOOP
20+
RAISE INFO 'Processing: id=%, name=%, amount=%', cr.id, cr.name, cr.amount;
21+
total := total + cr.amount;
22+
END LOOP;
23+
RAISE INFO 'Total amount: %', total;
24+
END;
25+
/
26+
INFO: Processing: id=1, name=Item A, amount=100
27+
INFO: Processing: id=2, name=Item B, amount=200
28+
INFO: Processing: id=3, name=Item C, amount=300
29+
INFO: Total amount: 600
30+
-- Test 2: FOR loop with different cursor variable name
31+
DECLARE
32+
count_val NUMBER := 0;
33+
BEGIN
34+
FOR rec IN (SELECT name FROM test_loop_data ORDER BY id) LOOP
35+
count_val := count_val + 1;
36+
RAISE INFO 'Row %: %', count_val, rec.name;
37+
END LOOP;
38+
END;
39+
/
40+
INFO: Row 1: Item A
41+
INFO: Row 2: Item B
42+
INFO: Row 3: Item C
43+
-- Test 3: Nested FOR loops with implicit variables
44+
BEGIN
45+
FOR outer_rec IN (SELECT id, name FROM test_loop_data WHERE id <= 2 ORDER BY id) LOOP
46+
RAISE INFO 'Outer: id=%, name=%', outer_rec.id, outer_rec.name;
47+
FOR inner_rec IN (SELECT amount FROM test_loop_data WHERE id = outer_rec.id) LOOP
48+
RAISE INFO ' Inner: amount=%', inner_rec.amount;
49+
END LOOP;
50+
END LOOP;
51+
END;
52+
/
53+
INFO: Outer: id=1, name=Item A
54+
INFO: Inner: amount=100
55+
INFO: Outer: id=2, name=Item B
56+
INFO: Inner: amount=200
57+
-- Test 4: Implicit cursor in package procedure
58+
CREATE OR REPLACE PACKAGE test_implicit_pkg IS
59+
PROCEDURE process_data;
60+
END test_implicit_pkg;
61+
/
62+
CREATE OR REPLACE PACKAGE BODY test_implicit_pkg IS
63+
PROCEDURE process_data IS
64+
counter NUMBER := 0;
65+
BEGIN
66+
FOR item IN (SELECT id, name, amount FROM test_loop_data ORDER BY id) LOOP
67+
counter := counter + 1;
68+
RAISE INFO 'Item %: id=%, name=%, amount=%', counter, item.id, item.name, item.amount;
69+
END LOOP;
70+
RAISE INFO 'Processed % items', counter;
71+
END process_data;
72+
END test_implicit_pkg;
73+
/
74+
-- Execute the package procedure
75+
BEGIN
76+
test_implicit_pkg.process_data();
77+
END;
78+
/
79+
INFO: Item 1: id=1, name=Item A, amount=100
80+
INFO: Item 2: id=2, name=Item B, amount=200
81+
INFO: Item 3: id=3, name=Item C, amount=300
82+
INFO: Processed 3 items
83+
-- Test 5: Multiple FOR loops reusing same variable name (different scope)
84+
DECLARE
85+
sum1 NUMBER := 0;
86+
sum2 NUMBER := 0;
87+
BEGIN
88+
FOR cr IN (SELECT amount FROM test_loop_data WHERE id <= 2) LOOP
89+
sum1 := sum1 + cr.amount;
90+
END LOOP;
91+
RAISE INFO 'First loop sum: %', sum1;
92+
-- cr should be implicitly declared again in this scope
93+
FOR cr IN (SELECT amount FROM test_loop_data WHERE id > 2) LOOP
94+
sum2 := sum2 + cr.amount;
95+
END LOOP;
96+
RAISE INFO 'Second loop sum: %', sum2;
97+
END;
98+
/
99+
INFO: First loop sum: 300
100+
INFO: Second loop sum: 300
101+
-- Test 6: Verify cursor variable doesn't leak outside loop
102+
DECLARE
103+
x NUMBER;
104+
BEGIN
105+
FOR loop_var IN (SELECT id FROM test_loop_data WHERE id = 1) LOOP
106+
x := loop_var.id;
107+
END LOOP;
108+
-- This should fail: loop_var not visible here
109+
RAISE INFO 'Value: %', loop_var.id;
110+
END;
111+
/
112+
ERROR: "loop_var"."id": invalid identifier
113+
LINE 1: loop_var.id
114+
^
115+
QUERY: loop_var.id
116+
CONTEXT: PL/iSQL function inline_code_block line 8 at RAISE
117+
-- Cleanup
118+
DROP PACKAGE test_implicit_pkg;
119+
DROP TABLE test_loop_data;

‎src/pl/plisql/src/pl_gram.y‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2337,12 +2337,24 @@ for_variable : T_DATUM
23372337
$$.name = $1.ident;
23382338
$$.lineno = plisql_location_to_lineno(@1, yyscanner);
23392339
$$.scalar = NULL;
2340-
$$.row = NULL;
23412340
/* check for comma-separated list */
23422341
tok = yylex(&yylval, &yylloc, yyscanner);
23432342
plisql_push_back_token(tok, &yylval, &yylloc, yyscanner);
23442343
if (tok == ',')
2344+
{
23452345
word_is_not_variable(&($1), @1, yyscanner);
2346+
$$.row = NULL;
2347+
}
2348+
else
2349+
{
2350+
/* Oracle compatibility: implicitly create RECORD variable for FOR loop */
2351+
$$.row = (PLiSQL_datum *)
2352+
plisql_build_record($1.ident,
2353+
plisql_location_to_lineno(@1, yyscanner),
2354+
NULL,
2355+
RECORDOID,
2356+
true);
2357+
}
23462358
}
23472359
| T_CWORD
23482360
{

‎src/pl/plisql/src/sql/plisql_for_loop_implicit.sql‎

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,109 @@
1+
--
2+
-- Test implicit RECORD declaration in FOR loops (Oracle compatibility)
3+
--
4+
5+
-- Setup test table
6+
CREATE TABLE test_loop_data (
7+
id NUMBER,
8+
name VARCHAR2(50),
9+
amount NUMBER
10+
);
11+
12+
INSERT INTO test_loop_data VALUES (1, 'Item A', 100);
13+
INSERT INTO test_loop_data VALUES (2, 'Item B', 200);
14+
INSERT INTO test_loop_data VALUES (3, 'Item C', 300);
15+
16+
-- Test 1: Simple FOR loop with implicit cursor variable
17+
-- Oracle: cr is implicitly declared as RECORD
18+
-- Expected: Should work (currently fails)
19+
DECLARE
20+
total NUMBER := 0;
21+
BEGIN
22+
FOR cr IN (SELECT id, name, amount FROM test_loop_data ORDER BY id) LOOP
23+
RAISE INFO 'Processing: id=%, name=%, amount=%', cr.id, cr.name, cr.amount;
24+
total := total + cr.amount;
25+
END LOOP;
26+
RAISE INFO 'Total amount: %', total;
27+
END;
28+
/
29+
30+
-- Test 2: FOR loop with different cursor variable name
31+
DECLARE
32+
count_val NUMBER := 0;
33+
BEGIN
34+
FOR rec IN (SELECT name FROM test_loop_data ORDER BY id) LOOP
35+
count_val := count_val + 1;
36+
RAISE INFO 'Row %: %', count_val, rec.name;
37+
END LOOP;
38+
END;
39+
/
40+
41+
-- Test 3: Nested FOR loops with implicit variables
42+
BEGIN
43+
FOR outer_rec IN (SELECT id, name FROM test_loop_data WHERE id <= 2 ORDER BY id) LOOP
44+
RAISE INFO 'Outer: id=%, name=%', outer_rec.id, outer_rec.name;
45+
FOR inner_rec IN (SELECT amount FROM test_loop_data WHERE id = outer_rec.id) LOOP
46+
RAISE INFO ' Inner: amount=%', inner_rec.amount;
47+
END LOOP;
48+
END LOOP;
49+
END;
50+
/
51+
52+
-- Test 4: Implicit cursor in package procedure
53+
CREATE OR REPLACE PACKAGE test_implicit_pkg IS
54+
PROCEDURE process_data;
55+
END test_implicit_pkg;
56+
/
57+
58+
CREATE OR REPLACE PACKAGE BODY test_implicit_pkg IS
59+
PROCEDURE process_data IS
60+
counter NUMBER := 0;
61+
BEGIN
62+
FOR item IN (SELECT id, name, amount FROM test_loop_data ORDER BY id) LOOP
63+
counter := counter + 1;
64+
RAISE INFO 'Item %: id=%, name=%, amount=%', counter, item.id, item.name, item.amount;
65+
END LOOP;
66+
RAISE INFO 'Processed % items', counter;
67+
END process_data;
68+
END test_implicit_pkg;
69+
/
70+
71+
-- Execute the package procedure
72+
BEGIN
73+
test_implicit_pkg.process_data();
74+
END;
75+
/
76+
77+
-- Test 5: Multiple FOR loops reusing same variable name (different scope)
78+
DECLARE
79+
sum1 NUMBER := 0;
80+
sum2 NUMBER := 0;
81+
BEGIN
82+
FOR cr IN (SELECT amount FROM test_loop_data WHERE id <= 2) LOOP
83+
sum1 := sum1 + cr.amount;
84+
END LOOP;
85+
RAISE INFO 'First loop sum: %', sum1;
86+
87+
-- cr should be implicitly declared again in this scope
88+
FOR cr IN (SELECT amount FROM test_loop_data WHERE id > 2) LOOP
89+
sum2 := sum2 + cr.amount;
90+
END LOOP;
91+
RAISE INFO 'Second loop sum: %', sum2;
92+
END;
93+
/
94+
95+
-- Test 6: Verify cursor variable doesn't leak outside loop
96+
DECLARE
97+
x NUMBER;
98+
BEGIN
99+
FOR loop_var IN (SELECT id FROM test_loop_data WHERE id = 1) LOOP
100+
x := loop_var.id;
101+
END LOOP;
102+
-- This should fail: loop_var not visible here
103+
RAISE INFO 'Value: %', loop_var.id;
104+
END;
105+
/
106+
107+
-- Cleanup
108+
DROP PACKAGE test_implicit_pkg;
109+
DROP TABLE test_loop_data;

0 commit comments

Comments
 (0)