- SQL code
-- Created on 2012-5-5 by ADMIN declare CURYEAR VARCHAR(4); CURSEA VARCHAR(1);begin -- Test statements here CURYEAR := '2012'; CURSEA := to_char((sysdate+interval'-1' day),'q'); --- 1/2/3/4 UPDATE t_report_bfsshouru T SET T.SEASON0 = 9999.999, T.SEASON1 = (SELECT SUM(S.REALPAYRMB) FROM T_INIT_BFSHOURU S WHERE S.VY='2012' AND S.VSEASON = '2'), T.SEASON2 = (SELECT SUM(S.REALPAYRMB) FROM T_INIT_BFSHOURU S WHERE S.VY=CURYEAR AND S.VSEASON = CURSEA) where T.Qdtype='AL' AND T.STATUS=1; dbms_output.put_line(SEASON2 ->> SELECT SUM(S.REALPAYRMB) FROM T_INIT_BFSHOURU S WHERE S.VY='||CURYEAR ||' AND S.VSEASON = '||CURSEA);end;
Curmonth1 有数据
但Curmonth2 却没有数据
从dbms_output.put_line打印出来的SEASON2 更新的SQL单独执行是有和Curmonth1 一样的返回值。。
为什么
------解决方案--------------------
select * from dual where ' 2' = 2;
select * from dual where ' 2' = '2';
------解决方案--------------------
楼上正解，如下：
CURSEA := 2;
SELECT SUM(S.REALPAYRMB) FROM T_INIT_BFSHOURU S WHERE S.VSEASON = 2;
SELECT SUM(S.REALPAYRMB) FROM T_INIT_BFSHOURU S WHERE S.VSEASON = CURSEA ;
因为ORACLE会先进行CHAR向NUMBER类型进行转换，这个时侯VSEASON中的空格就被取消了。
而下一句因为“S.VSEASON = CURSEA”这个等式的前后都是CHAR类型的数据，在进行等式判断时也就不会进行数据类型转化，也就不能去除多余的空格了。所以第二句未能查询到数据