DB2 存储函数

本文主要是介绍DB2 存储函数，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

创建fuction：根据年份和周数，获取到当周开始日期

--  Generate SQL 
--  Version:                       V7R1M0 100423 
--  Generated on:                  03/29/22 12:08:46 
--  Relational Database:           S685D13X 
--  Standards Option:              DB2 for i 
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","KT" ; 
  
CREATE FUNCTION KTGL.GET_DOT_SDATE ( INYY NUMERIC(4, 0) , INWW NUMERIC(2, 0) ) 
    RETURNS CHAR(10)   
    LANGUAGE SQL 
    SPECIFIC KTGL.GET_DOT_SDATE 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    SRTSEQ = *HEX   
    BEGIN 
---------------------------------------------------------------------------- 
-- 
---------------------------------------------------------------------------- 
------------------------------------------------------------- 
-- WORK AREAS 
------------------------------------------------------------- 
DECLARE W_RESULT CHAR ( 10 ) ; 
  
-------------------------------------------- 
-- SELCODE DEFINE 
-------------------------------------------- 
SET W_RESULT = ( 
  SELECT CHAR ( DATE + ( WEEK + DAY ) DAY ) AS DATE FROM ( 
　　SELECT DATE ( TIMESTAMP_FORMAT ( CHAR ( INYY || '0101' ) , 'YYYYMMDD' ) ) DATE 
   　　 , ( CAST ( INWW AS INTEGER ) - 1 ) * 7 WEEK 
   　　 , CASE DAYNAME ( TIMESTAMP_FORMAT ( CHAR ( INYY || '0101' ) , 'YYYYMMDD' ) ) 
      　　  WHEN '星期一' THEN 0 
       　　 WHEN '星期二' THEN - 1 
       　　 WHEN '星期三' THEN - 2 
       　　 WHEN '星期四' THEN - 3 
       　　 WHEN '星期五' THEN 3 
        　　WHEN '星期六' THEN 2 
        　　WHEN '星期天' THEN 1 END AS DAY 
　　FROM SYSIBM . SYSDUMMY1 
  ) A 
) ; 
  
RETURN W_RESULT ; 
  
END  ; 
  
GRANT ALTER , EXECUTE   
ON SPECIFIC FUNCTION KTGL.GET_DOT_SDATE 
TO QUSER ;

使用function

SELECT KTGL.GET_DOT_SDATE(2021,01)
  FROM SYSIBM.SYSDUMMY1 ;　　--DB虚拟表

这篇关于DB2 存储函数的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！