现在要从十几个表中查询数据 每个表表名的结尾是年的后两位,前面的都一样
通过循环定义完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;
解决方案 »
- dbms_random.value 好慢
- 数据库语句和datawindow
- 能否写出这样的SQL
- 高分求助:只安装了oracle9的客户端如何使用sqlldr命令??
- ADO访问Oracle9i的连接字符串怎么写?
- 请问索引组织表里的“OVERFLOW“ 表空间可以定义多个吗?---急求----
- 免费版Oracle10g安装后,数据库实例未创建和服务未启动的问题
- 关于Berkeley DB 的备份 db_dump
- sqlserver中维护oracle数据库信息问题
- 序列递增出现跳空现象如何处理???
- 请各位高手帮忙:程序包没有权限
- 为啥我用insert into ... select ..语句在sqlplus中执行会让sqlsplus无响应的呢?急
在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 || ''';