evaluation/ims-service/ims-am/src/main/resources/ims_postgresql_func.sql

CREATE OR REPLACE FUNCTION get_next_id(obj_id_  IN VARCHAR  ,
                                       ext_value_ IN  VARCHAR  ) RETURNS VARCHAR AS $$

declare
     l_res VARCHAR ;
     l_temp VARCHAR;
     l_temp_ext_value VARCHAR;
     l_field_value VARCHAR;
     l_key_type VARCHAR;
     l_start integer;
     l_step integer;
     l_value integer;
     nPos integer;
     l_index integer;
     l_lead_zero_len integer;
     l_mask VARCHAR;
     l_ext_value VARCHAR;


     get_sys_id cursor(obj_id_  varchar)  FOR
     SELECT  KEY_TYPE, start ,STEP, VALUE ,MASK, lead_zero_len,ext_value
     FROM sys_id WHERE obj_id =  obj_id_;
BEGIN

   open get_sys_id(obj_id_);
   fetch get_sys_id INTO l_key_type,l_start,l_step,l_value,l_mask ,l_lead_zero_len,l_ext_value;
   close get_sys_id ;
   IF l_start IS NULL THEN
     l_start:=1;
   END IF;
   IF l_step IS NULL THEN
       l_step :=1;
   END IF;
   if l_key_type is null THEN
     SELECT  sys_guid() INTO l_res;
     l_res := REPLACE(l_res,'-','') ;
   end if;
   /*l_key_type*/
   /*1: 自增,2: UUID,3: 日期+自增,4: 年月+自增;5: 年+自增  */
    IF l_key_type='1' THEN
				 IF l_value IS NULL THEN
						 l_value :=l_start;
				 ELSE
						 l_value := l_value+ l_step;
				 END IF;
         IF l_lead_zero_len>0 THEN
            l_res :=LPAD(l_value,l_lead_zero_len,'0') ;
         ELSE
            l_res :=cast(l_value as INTEGER) ;
         END IF;
         IF l_mask IS NOT NULL THEN
            l_res:= REPLACE(l_mask,'{SEQ}',l_res) ;
         END IF;
        UPDATE sys_id
        SET VALUE=l_value
        WHERE obj_id =  obj_id_ ;
   ELSIF l_key_type='3' THEN

     SELECT to_char(now(),'YYYYMMDD') INTO l_temp_ext_value  ;
     IF l_temp_ext_value= l_ext_value THEN
				 IF l_value IS NULL THEN
						l_value :=l_start;
				 ELSE
					 l_value := l_value+ l_step;
				 END IF;
     ELSE
          l_value :=l_start;
          l_ext_value :=l_temp_ext_value;
     END IF;
		 IF l_lead_zero_len>0 THEN
			   l_temp :=LPAD(l_value,l_lead_zero_len,'0') ;
		 ELSE
				 l_temp :=cast(l_value as INTEGER);
		 END IF;
     IF l_mask IS NOT NULL THEN
             l_res := REPLACE(l_mask,'{DATE}',l_ext_value) ;
             l_res := REPLACE(l_res,'{SEQ}',l_temp) ;
     ELSE
             l_res := l_ext_value||'-'||l_temp ;
     END IF;
        UPDATE sys_id
        SET VALUE=l_value,
            ext_value=l_ext_value
        WHERE obj_id =  obj_id_ ;
   ELSIF l_key_type='4' THEN

     SELECT to_char(now(),'YYYYMM') INTO l_temp_ext_value ;
     IF l_temp_ext_value= l_ext_value THEN
				 IF l_value IS NULL THEN
					  l_value :=l_start;
				 ELSE
					  l_value := l_value+ l_step;
				 END IF;
     ELSE
          l_value :=l_start;
          l_ext_value :=l_temp_ext_value;
     END IF;
		 IF l_lead_zero_len>0 THEN
			  l_temp :=LPAD(l_value,l_lead_zero_len,'0') ;
		 ELSE
				 l_temp :=cast(l_value as INTEGER);
		 END IF;
     IF l_mask IS NOT NULL THEN
           l_res := REPLACE(l_mask,'{DATE}',l_ext_value) ;
           l_res := REPLACE(l_res,'{SEQ}',l_temp) ;
     ELSE
          l_res :=  l_ext_value||'-'||l_temp ;
     END IF;
         UPDATE sys_id
        SET VALUE=l_value,
            ext_value=l_ext_value
        WHERE obj_id =  obj_id_ ;
   ELSIF l_key_type='5' THEN

     SELECT to_char(now(),'YYYY') INTO l_temp_ext_value ;
     IF l_temp_ext_value= l_ext_value THEN
				 IF l_value IS NULL THEN
						 l_value :=l_start;
				 ELSE
						 l_value := l_value+ l_step;
				 END IF;
     ELSE
          l_value :=l_start;
          l_ext_value :=l_temp_ext_value;
     END IF;
		 IF l_lead_zero_len>0 THEN
				 l_temp :=LPAD(l_value,l_lead_zero_len,'0') ;
		 ELSE
				 l_temp :=cast(l_value as INTEGER);
		 END IF;
     IF l_mask IS NOT NULL THEN
              l_res := REPLACE(l_mask,'{DATE}',l_ext_value) ;
              l_res := REPLACE(l_res,'{SEQ}',l_temp) ;
     ELSE
              l_res := l_ext_value||'-'||l_temp ;
     END IF;
         UPDATE sys_id
        SET VALUE=l_value,
            ext_value=l_ext_value
        WHERE obj_id =  obj_id_ ;
   ELSE  --l_key_type='2' THEN
     SELECT  sys_guid() INTO l_res ;
     l_res := REPLACE(l_res,'-','') ;
   END IF;
   IF  ext_value_  is not null THEN
        l_index:=1;
        l_temp:=ext_value_ ;
        WHILE position(';' in l_temp)>0 loop
            nPos:=position(';' in l_temp);
            l_field_value:=substring(l_temp from 1 for nPos-1);
            l_temp:=substring(l_temp from nPos+1 );
            l_res := REPLACE(l_res,'{'||cast(l_index as INTEGER)||'}',l_field_value) ;
            l_index:=l_index+1;
        END loop;
        l_res := REPLACE(l_res, '{'||cast(l_index as INTEGER)||'}' ,l_temp) ;
   END IF;
   --commit;
   RETURN l_res;
END ;
$$ language plpgsql;

CREATE or REPLACE FUNCTION  sys_guid()
RETURNS varchar AS
$$
DECLARE
v_seed_value varchar(32);
BEGIN
select
md5(
inet_client_addr()::varchar ||
timeofday() ||
inet_server_addr()::varchar ||
to_hex(inet_client_port())
)
into v_seed_value;


return (substr(v_seed_value,1,8) ||
substr(v_seed_value,9,4) ||
substr(v_seed_value,13,4) ||
substr(v_seed_value,17,4) ||
substr(v_seed_value,21,12));
END;
$$
LANGUAGE 'plpgsql' ;


CREATE OR REPLACE FUNCTION   TREPS_CONCAT(str1  VARCHAR  , str2  VARCHAR  , str3  VARCHAR  ) RETURNS VARCHAR  AS $$

BEGIN

   RETURN str1||str2||str3;

END  ;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION  treps_date_to_char(date1  timestamp    , strFormat  VARCHAR ) RETURNS VARCHAR  as $$

BEGIN

   RETURN to_char(date1,strFormat) ;

END   ;
$$ language plpgsql;


CREATE OR REPLACE FUNCTION  treps_instr(str   varchar, substr1  varchar,flag integer default 0) returns  integer as $$
declare
    l_res integer;
    posSep integer;
    posSepSub integer;
    strTemp varchar;
BEGIN
  if (substr1 is null or substr1='')  THEN

      return 0;

  end if;
  if (str is null or str='')  THEN
    if   (flag=1) then
      return 1;
    ELSE
      return 0;
    end if;
  end if;
  posSep:=position( ',' in str );
  posSepSub:=position( ',' in substr1 ) ;
  if (posSep>0) OR (posSepSub>0) then
     if posSepSub>0 then
		     l_res:=0;
		     strTemp:=substr1;
		     while (posSepSub>0)and(char_length(strTemp)>0) loop

		       if position( ','||substring(strTemp from 1 for posSepSub-1)||',' in  ','||str||',')>0 then
		         return 1;
		       end if;

		       strTemp:= substring(strTemp from posSepSub+1 );
		       posSepSub:= position( ',' in strTemp );
		     end loop;
		     if char_length(strTemp)>0 then
			       if  position( ','||strTemp||',' in ','||str||',' )>0 then
		           return 1;
		         end if;
		     end if;
      else
         l_res:= position(','||substr1||',' in ','||str||',' );

      end if;
  else
     l_res:= position(substr1 in str );
  end if;

	RETURN l_res;

END  ;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION   treps_to_date(str1  VARCHAR , strFormat  VARCHAR ) RETURNS date as $$

BEGIN

   RETURN to_date(str1,strFormat) ;

END  ;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION  TREPS_TO_INT(TEMP_ in varchar ) RETURNS integer AS $$

BEGIN
    RETURN  to_number(TEMP_,'9999999999');

END    ;
$$ language plpgsql;