现在要从十几个表中查询数据 每个表表名的结尾是年的后两位,前面的都一样
通过循环定义完sql语句后怎么用动态游标把查询到的数取出来
写了一个 老是报错 说第12行市无效数字
Declare
Type cur Is Ref Cursor;
cura cur;
ls_sqlselect Varchar2(500);
ls_year Numeric(4);
ls_base numeric(12,2);
Begin
For ls_year In 1995..2005 Loop
ls_sqlselect:='select money from zm'||substr(to_char(ls_year),3,2)||' where code='||'41060319560910201';
Open cura For ls_sqlselect;
Loop
Fetch cura Into ls_base;
Exit When cura%Notfound;
dbms_output.put_line(ls_base);
End Loop;
End Loop;
End;
通过循环定义完sql语句后怎么用动态游标把查询到的数取出来
写了一个 老是报错 说第12行市无效数字
Declare
Type cur Is Ref Cursor;
cura cur;
ls_sqlselect Varchar2(500);
ls_year Numeric(4);
ls_base numeric(12,2);
Begin
For ls_year In 1995..2005 Loop
ls_sqlselect:='select money from zm'||substr(to_char(ls_year),3,2)||' where code='||'41060319560910201';
Open cura For ls_sqlselect;
Loop
Fetch cura Into ls_base;
Exit When cura%Notfound;
dbms_output.put_line(ls_base);
End Loop;
End Loop;
End;
在COMMAND WINDOWS中
SQL> CREATE TABLE ZM95(MONEY numeric, CODE NUMBER);Table createdSQL> CREATE TABLE ZM96(MONEY numeric, CODE NUMBER);Table createdSQL> INSERT INTO ZM95 VALUES(100, 41060319560910201);1 row insertedSQL> INSERT INTO ZM95 VALUES(110, 41060319560910201);1 row insertedSQL> INSERT INTO ZM96 VALUES(200, 41060319560910201);1 row insertedSQL> INSERT INTO ZM96 VALUES(210, 41060319560910201);1 row inserted在TEST SCRIPT中:
declare
Type cur Is Ref Cursor;
cura cur;
ls_sqlselect Varchar2(500);
ls_year Numeric(4);
ls_base numeric(12,2);
BEGIN
For ls_year In 1995..1996 Loop
ls_sqlselect:='select money from zm'||substr(to_char(ls_year),3,2)||' where code='||'41060319560910201';
Open cura For ls_sqlselect;
Loop
Fetch cura Into ls_base;
Exit When cura%Notfound;
dbms_output.put_line(ls_base);
End Loop;
CLOSE cura;
End Loop;
end;
输出结果:
100
110
200
210
语句没有问题啊!
问题出在这里了 code是varchar2型的 字符连接出问题了
ls_sqlselect:='select money from zm'||substr(to_char(ls_year),3,2)||' where code='||'''41060319560910201''';
用ls_sqlselect:='select money from zm'||substr(to_char(ls_year),3,2)||' where code='||“'41060319560910201'”;提示必须说明标示符‘'41060319560910201'’
用ls_sqlselect:='select money from zm'||substr(to_char(ls_year),3,2)||' where code='||‘“41060319560910201”’;提示无效列名
sql := 'select money from zm'||substr(to_char(ls_year),3,2)||' where code =' || value || ''';