我编了一个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返回值为零。在另外一个函数中同样写法,没有错误
大惑不解,请高手指点如何解决。
解决方案 »
- 请推荐国内好的数据交换中心解决方案或产品
- "delete作删除时 总共分几步?"
- 如何计算like 语句中找到的关键词数
- 郁闷,从SQL SERVER存储过程转到ORACLE来,没写一个都有问题,帮忙看看
- deallocate到底怎么用!
- 如何用一条SQL语句实现按某一时间间隔查询?oracle 数据库(在线等待!!!)
- 如何更新user_tab_comments表的COMMENTS列?
- 求ORACLE中用PLSQL写的大小写金额转换的后台函数!!!
- tomcat的问题,请各位关注!!
- 请教:谁知道哪有介绍oracle8i的语言设置的资料?
- 子查询内用nvl,到外层再关联怎么就像成笛卡尔积了
- Oracle Application Adapter for SAP R/3
权限问题
grant select on JH2_STEEL_HEAT to ...
这个是什么意思,我不明白!
grant select on JH2_STEEL_HEAT to 用户名;