DB2 SQL Function的一个问题

0 0

DB2 SQL Function的一个问题10

SET SCHEMA SCMUSER;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SCMUSER";

CREATE FUNCTION "SCMUSER"."GETBILLNO"
 (
 "STRING" VARCHAR(30),
 "BTYPE" varchar(1)
 ) RETURNS VARCHAR(1000)
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
BEGIN ATOMIC


	  Declare cd DATE;   --时间
	  DECLARE y VARCHAR(4); --年
	  DECLARE m VARCHAR(2); --月
	  DECLARE d VARCHAR(2); ---日
	  DECLARE LASTDATE DATE;  --上次插入时间
	  DECLARE GSTEP varchar(4); ---
	  DECLARE DIGIT varchar(4);
	  
	  
	  	 set cd = current date;   --得到当前时间
		 set y = char(year(cd));   --得到年
		 set m = char(month(cd));   --得到月
		 
		 if int(m) < 10 then
		 	set m ='0'|| m;
		 end if;
		 
		 set d = char(day(cd));  --得到日
		 
		 if int(d) < 10 then
		 	 set d = '0'||d;
	     end if;
	  
      --查询是否存储当前实例的数据
if not exists(SELECT * from GENERATEBILLNO where SGCODE = STRING and BILLTYPE = BTYPE) then   --如不存在符合条件的记录 		 		 
	  	 insert into GENERATEBILLNO values(STRING,BTYPE,cd,'1');  -- 插入一条记录
		 --取出当前STEP
         
		 set GSTEP = '1';

		 set DIGIT = char(length(GSTEP));   --检查位数
		 
		 if DIGIT = '1' then
		   set  GSTEP = '000'||GSTEP;  -- 不足四位补3个0
		 end if;
		 if DIGIT = '2' then
		   set GSTEP = '00'||GSTEP;
		  end if;
		  if DIGIT = '3' then
		   set GSTEP = '0'||GSTEP;
		  end if;
		  
		  RETURN y||m||d||GSTEP;    ---返回结果
else
 	  set LASTDATE =( select GTIME from GENERATEBILLNO where SGCODE=STRING and BILLTYPE=BTYPE);   -- 得到其时间
	 
	    if year(LASTDATE) = year(cd) and month(LASTDATE) = MONTH(cd) and day(LASTDATE) = day(cd) then   --判断是否与当天时间相等    LASTDATE = current date
	   	   set GSTEP = ( select step from GENERATEBILLNO  where SGCODE = STRING and BILLTYPE=BTYPE);
		   set GSTEP = char(int(GSTEP)+1);
		   update GENERATEBILLNO set STEP = GSTEP where SGCODE = STRING and BILLTYPE=BTYPE;
		else               -- 与不相等
		   set GSTEP = '1';
		   update GENERATEBILLNO set STEP = GSTEP,GTIME = cd where SGCODE = STRING and BILLTYPE=BTYPE;
		end if;
		
		set  DIGIT = char(length(GSTEP));
		 
		 if DIGIT = '1' then
		   set  GSTEP = '000'||GSTEP;
		 end if;
		 if DIGIT = '2' then
		   set GSTEP = '00'||GSTEP;
		  end if;
		  if DIGIT = '3' then
		   set GSTEP = '0'||GSTEP;
		  end if;
		  
		  RETURN y||m||d||GSTEP;
	
	end if;	 
END;

这个是源SQL Function的代码,报的错是 "存在与MODIFIES SQL DATA 子句有关的多个关键字或相冲突的关键字!" 不知道怎么解决 ...