我编了一个ORACLE 函数,如下:
create or replace function GET_NEW_HEATNO(HEATNO INTEGER) return VARCHAR2 is
ReturnValue VARCHAR2(20);
VARCHAR_TEMP VARCHAR2(20);
INT_TEMP INTEGER ;
begin
SELECT COUNT(t.V_HEAT_ID) INTO INT_TEMP FROM JH2_STEEL_HEAT t
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(HEATNO);
ReturnValue:=CONCAT('H',TO_CHAR(SYSDATE,'YY'));
ReturnValue:=CONCAT(ReturnValue,TRIM(TO_CHAR(HEATNO)));
IF INT_TEMP=0 THEN
ReturnValue:=CONCAT(TRIM(ReturnValue),TRIM(TO_CHAR(1,'00000')));
ELSE
SELECT MAX(SUBSTR(V_HEAT_ID,5,5))+1 INTO VARCHAR_TEMP FROM JH2_STEEL_HEAT
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(HEATNO);
ReturnValue:=CONCAT(ReturnValue,TRIM(TO_CHAR(VARCHAR_TEMP,'00000')));
END IF;
RETURN (ReturnValue);
end GET_NEW_HEATNO;
数据库为oracle 10.2G pl/sql6.0
在pl/sql中建sql windows执行
SELECT COUNT(t.V_HEAT_ID) FROM JH2_STEEL_HEAT t
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(1);
结果为1而调试oracle函数,在test窗口输入HEATNO为1 integer类型,执行到
SELECT COUNT(t.V_HEAT_ID) INTO INT_TEMP FROM JH2_STEEL_HEAT t
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(HEATNO);时
INT_TEMP返回值为零。在另外一个函数中同样写法,没有错误
大惑不解,请高手指点如何解决。
create or replace function GET_NEW_HEATNO(HEATNO INTEGER) return VARCHAR2 is
ReturnValue VARCHAR2(20);
VARCHAR_TEMP VARCHAR2(20);
INT_TEMP INTEGER ;
begin
SELECT COUNT(t.V_HEAT_ID) INTO INT_TEMP FROM JH2_STEEL_HEAT t
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(HEATNO);
ReturnValue:=CONCAT('H',TO_CHAR(SYSDATE,'YY'));
ReturnValue:=CONCAT(ReturnValue,TRIM(TO_CHAR(HEATNO)));
IF INT_TEMP=0 THEN
ReturnValue:=CONCAT(TRIM(ReturnValue),TRIM(TO_CHAR(1,'00000')));
ELSE
SELECT MAX(SUBSTR(V_HEAT_ID,5,5))+1 INTO VARCHAR_TEMP FROM JH2_STEEL_HEAT
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(HEATNO);
ReturnValue:=CONCAT(ReturnValue,TRIM(TO_CHAR(VARCHAR_TEMP,'00000')));
END IF;
RETURN (ReturnValue);
end GET_NEW_HEATNO;
数据库为oracle 10.2G pl/sql6.0
在pl/sql中建sql windows执行
SELECT COUNT(t.V_HEAT_ID) FROM JH2_STEEL_HEAT t
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(1);
结果为1而调试oracle函数,在test窗口输入HEATNO为1 integer类型,执行到
SELECT COUNT(t.V_HEAT_ID) INTO INT_TEMP FROM JH2_STEEL_HEAT t
WHERE SUBSTR(V_HEAT_ID,1,1)='H'
AND SUBSTR(V_HEAT_ID,2,2)=TO_CHAR(SYSDATE,'YY')
AND SUBSTR(V_HEAT_ID,4,1)=TO_CHAR(HEATNO);时
INT_TEMP返回值为零。在另外一个函数中同样写法,没有错误
大惑不解,请高手指点如何解决。
解决方案 »
- ibatis oracle 调用存储过程 无法取得返回值
- 如何设置session级别的超时,在线等待
- Enterprise Manager Console 连接数据库自动消失了。。。
- 一个oracle错误。
- oracle developer suite 10g如何将form发布到web上?
- 分組查詢后排名次
- 如何判定当前日期的月份是双月 还是单月?
- oracle8.1.6的安装问题
- export/import 工具在oracle9i的那个地方,我怎么没找着
- 请问:使外键的约束失效的sql语句如何写啊?
- 子查询内用nvl,到外层再关联怎么就像成笛卡尔积了
- Oracle Application Adapter for SAP R/3
权限问题
grant select on JH2_STEEL_HEAT to ...
这个是什么意思,我不明白!
grant select on JH2_STEEL_HEAT to 用户名;