db2工作笔记

最新推荐文章于 2018-07-04 16:47:42 发布

iteye_15821

最新推荐文章于 2018-07-04 16:47:42 发布

阅读量117

点赞数

分类专栏：数据库类

本文链接：https://blog.csdn.net/iteye_15821/article/details/82278135

版权

数据库类专栏收录该内容

22 篇文章 0 订阅

订阅专栏


// 生成32位随机数
CREATE OR REPLACE FUNCTION "RSPDB"."F_GETROUNDOF32" () 
specific F_GETROUNDOF32
  LANGUAGE SQL
  RETURNS varchar(40)
  BEGIN atomic
  declare v_branch_id_ods varchar(40);
  declare v_count int;
  declare v_branch_id_rsp  varchar(40);
      set   v_branch_id_ods =    (SELECT  substr(max
(branch_id),1,24) || to_char(floor(RAND() * 89999999 + 10000000))  from SYS_branch_info);  
      set   v_count =    (select  count(*) from sys_branch_info where branch_id = v_branch_id_ods);
      set   v_branch_id_rsp = (select  branch_id  from sys_branch_info where branch_id = v_branch_id_ods);
       if v_count =  0  then
         return v_branch_id_ods;
       end if;
       return v_branch_id_rsp;
END
select substr(A.ACCDATE,1,4) || '-' || substr(A.ACCDATE,5,2)|| '-' || substr(A.ACCDATE,7,2)  as ACCDATE  from F_INN_GEN_GL_BRIAC A


db2  取月所在的天数
select (current date + 1 month - day(current date + 1 month) days)
from  sysibm.sysdummy1


计算月数
select   timestampdiff(64,timestamp(to_date('2013-11-01','YYYY-MM-DD')) - timestamp(to_date('2012-11-01','YYYY-MM-DD')))  from sysibm.SYSDUMMY1

timestampdiff(64,timestamp(to_date(enddate,'YYYY-MM-DD')) - timestamp(to_date(startdate,'YYYY-MM-DD')))

求月数
db2函数 定义
CREATE FUNCTION "DB2INST"."F_GETMONTHS" (startdate varchar(40),enddate varchar(40))
  RETURNS int
  specific F_GETMONTHS
  LANGUAGE SQL
BEGIN atomic
  declare v_time int;
  set v_time = (SELECT (year(to_date(enddate,'YYYY-MM-DD')) - year(to_date(startdate,'YYYY-MM-DD')))* 12 + (month(to_date(enddate,'YYYY-MM-DD')) - month(to_date(startdate,'YYYY-MM-DD'))) FROM  sysibm.SYSDUMMY1);
  return v_time;
END

验证 values(f_getmonths('2011-01-01','2012-03-03'))


[code="sql"]

CREATE OR REPLACE FUNCTION "DB2INST"."F_ISNUMBER"
(v_str varchar(100))
specific F_ISNUMBER
returns int
begin atomic
declare v_len int;
set v_len=length(v_str);
if v_len=0 or v_str is null then
return 0;
end if;
while v_len >0 do
if substr(v_str,v_len,1) not in ('0','1','2','3','4','5','6','7','8','9') then
return 0;
end if;
set v_len=v_len-1;
end while;
return 1;
end

values(F_ISNUMBER(123123)) return 1;

//去掉回车换行符号和导入导出带分隔符
select replace(OBJ_VAL,chr(10),'') from APP_FAST_RPT_DS_INFO where DATASET_ID = '48e4a83134664df10134665e7b25000b'
select replace(replace(OBJ_VAL,chr(13),''),chr(10),'') from APP_FAST_RPT_DS_INFO where DATASET_ID = '48e4a83134664df10134665e7b25000b'
db2 "export to f_com_com_sp_brctl.txt of del modified by codepage=1208 COLDEL| select * from f_com_com_sp_brctl"
db2 "import from f_com_com_sp_brctl.txt of del modified by codepage=1208 COLDEL| insert into f_com_com_sp_brctl_t"

[/code]


//环比 
values (to_date(substr('20120531',1,6)||'01','yyyymmdd')-1 day) 

2012-4-30 上午12:00:00

//同比


//比年初
values (to_date(substr('20120531',1,4)||'0101','yyyymmdd'))
2012-1-1 上午12:00:00