시스템 테이블 사용 (MASTER.DBO.SPT_VALUES)

SELECT CONVERT(VARCHAR, DATEADD(D, NUMBER, '20120115'), 112) DATE
FROM MASTER..SPT_VALUES
WHERE TYPE = 'P'
AND NUMBER <= DATEDIFF(D, '20130115', '20130122')

 

RECURSIVE 를 사용

WITH A AS (
 SELECT LVL = 1
   , CAST('20130115' AS DATETIME) DT
 UNION ALL
 SELECT LVL + 1
   , DATEADD(D, 1, DT)
   FROM A
  WHERE DT < CAST('20130122' AS DATETIME)
)
SELECT LVL
  , DT
  , YEAR = DATEPART(YEAR, DT)
  , MONTH = DATEPART(MONTH, DT)
  , DAY = DATEPART(DAY, DT)
  , WEEK_OF_YEAR = DATEPART(WK, DT)
  , WEEK_OF_MONTH = DATEPART(WK, DT) - DATEPART(WK, LEFT(CONVERT(VARCHAR, DT, 112), 6) + '01') + 1
  , DAY = DATEPART(DW, DT)
  , 요일 = DATENAME(W, DT)
  , 분기 = DATEPART(Q, DT)
  , 반기 = CASE WHEN DATEPART(MONTH, DT) BETWEEN 1 AND 6 THEN '상반기' ELSE '하반기' END
  FROM A
OPTION (MAXRECURSION 0) 

 

휴일테이블의 휴일과 토/일요일 일자만 조회

 휴일테이블
INSERT INTO PIS_HOLIDAY (HOLI_DATE, HOLI_DESC) VALUES ('99990101', '신정');
INSERT INTO PIS_HOLIDAY (HOLI_DATE, HOLI_DESC) VALUES ('9999016', '테스트');
INSERT INTO PIS_HOLIDAY (HOLI_DATE, HOLI_DESC) VALUES ('20130117', '창립기념일');

WITH A AS (
 SELECT CAST('20130115' AS DATETIME) DT
 UNION ALL
 SELECT DATEADD(D, 1, DT)
   FROM A
  WHERE DT < CAST('20130122' AS DATETIME)
), B AS (
 SELECT CAST('20130115' AS DATETIME) DT
   , LVL = 1
 UNION ALL
 SELECT DATEADD(YEAR, 1, DT)
   , LVL + 1
   FROM B
  WHERE LVL <= DATEDIFF(YEAR, '20130115', '20130122')
), C AS (
 SELECT HOLI_DATE
   , HOLI_DESC
      FROM (
   SELECT SUBSTRING(CONVERT(VARCHAR, B.DT, 112), 1, 4) + SUBSTRING(A.HOLI_DATE, 5, 4) HOLI_DATE
     , A.HOLI_DESC
     FROM PIS_HOLIDAY A
     , B
    WHERE A.HOLI_DATE LIKE '9999%'
   ) A
  WHERE HOLI_DATE BETWEEN '20130115' AND '20130122'
)
SELECT HOLI_DATE
  , MAX(CASE WHEN GUBUN = 1 THEN HOLI_DESC ELSE DATENAME(W, HOLI_DATE) END) HOLI_DESC
  FROM (
 SELECT 1 GUBUN
   , HOLI_DATE
   , HOLI_DESC
   FROM PIS_HOLIDAY
     WHERE HOLI_DATE BETWEEN '20130115' AND '20130122'
 UNION ALL
 SELECT 1
   , HOLI_DATE
   , HOLI_DESC
   FROM C
 UNION ALL
 SELECT 2
   , CONVERT(VARCHAR, DT, 112)
   , NULL
   FROM A
  WHERE DATEPART(DW, DT) IN (1, 7)
  ) A
 GROUP BY HOLI_DATE
OPTION (MAXRECURSION 0) 

 

 

 

프로시저 생성

ALTER PROCEDURE [dbo].[SP_LEGACY_INS]
--ALTER PROCEDURE [dbo].[SP_LEGACY_INS]
    @LOG_SYSTEM VARCHAR(10),
    @LOG_MODE VARCHAR(2),
    @LOG_USER VARCHAR(20),
    @LOG_NAME VARCHAR(40),
    @LOG_PRG VARCHAR(40),
    @LOG_PRG_NAME VARCHAR(100),
    @LOG_FIELD VARCHAR(2),
    @LOG_IP VARCHAR(30),
    @LOG_DATA VARCHAR(100),
    @O_CODE INT OUTPUT,
    @O_MSG VARCHAR(255) OUTPUT
AS
BEGIN

    SET @O_CODE = 0
    SET @O_MSG = '정상 처리 되었습니다.'
   
    IF @LOG_SYSTEM IS NULL OR @LOG_SYSTEM = ''
        BEGIN
            SET @O_CODE = -101
            SET @O_MSG = '시스템명은 필수 입력항목입니다.'
        END
    ELSE IF @LOG_MODE IS NULL OR @LOG_MODE = ''
        BEGIN
            SET @O_CODE = -102
            SET @O_MSG = '접근모드는 필수 입력항목입니다.'
        END
    ELSE IF @LOG_USER IS NULL OR @LOG_USER = ''
        BEGIN
            SET @O_CODE = -103
            SET @O_MSG = '접근사용자ID는 필수 입력항목입니다.'
        END
    ELSE IF @LOG_NAME IS NULL OR @LOG_NAME = ''
        BEGIN
            SET @O_CODE = -104
            SET @O_MSG = '접근사용자명은 필수 입력항목입니다.'
        END
    ELSE IF @LOG_PRG IS NULL OR @LOG_PRG = ''
        BEGIN
            SET @O_CODE = -105
            SET @O_MSG = '접근프로그램ID는 필수 입력항목입니다.'
        END   
    ELSE IF @LOG_PRG_NAME IS NULL OR @LOG_PRG_NAME = ''
        BEGIN
            SET @O_CODE = -106
            SET @O_MSG = '접근프로그램명은 필수 입력항목입니다.'
        END
    ELSE IF @LOG_FIELD IS NULL OR @LOG_FIELD = ''
        BEGIN
            SET @O_CODE = -107
            SET @O_MSG = '접근개인정보는 필수 입력항목입니다.'
        END
    ELSE IF @LOG_IP IS NULL OR @LOG_IP = ''
        BEGIN
            SET @O_CODE = -108
            SET @O_MSG = '접근IP는 필수 입력항목입니다.'
        END

    ELSE
        BEGIN
            /*
            BEGIN TRY
                SELECT CONVERT(DATETIME, @LOG_DATE)
            END TRY

            BEGIN CATCH
                SET @O_CODE = -202
                SET @O_MSG = '로그 일자(YYYYMMDD)를 다시 확인해 주세요.'
            END CATCH

            BEGIN TRY
                SELECT CONVERT(DATETIME, @LOG_TIME)
            END TRY

            BEGIN CATCH
                SET @O_CODE = -203
                SET @O_MSG = '로그 시간(HH24:MI:SS)를 다시 확인해 주세요.'
            END CATCH
            */
           
            INSERT INTO PIS_LEGACY (LOG_SYSTEM, LOG_DATE, LOG_TIME, LOG_MODE, LOG_USER, LOG_NAME, LOG_PRG, LOG_PRG_NAME, LOG_FIELD, LOG_IP, LOG_DATA)
            VALUES
            (@LOG_SYSTEM, CONVERT(VARCHAR, GETDATE(), 112), CONVERT(VARCHAR, GETDATE(), 108), @LOG_MODE, @LOG_USER, @LOG_NAME, @LOG_PRG, @LOG_PRG_NAME, @LOG_FIELD, @LOG_IP, @LOG_DATA)
        END
END 


프로시저 실행

DECLARE
@O_CODE1 INT,
@O_MSG1 VARCHAR(255)
BEGIN
    EXEC SP_LEGACY_INS 'PIS', 'C', 'kdn100', '홍길동', 'PID10', 'VPN - 신청', '1', '127.0.0.1', '123456-1234567', @O_CODE1 OUTPUT, @O_MSG1 OUTPUT
    SELECT @O_CODE1, @O_MSG1
END 


자바에서 프로시저 실행

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
import java.util.Properties;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ProcedureTest {

    private final static Logger log = LoggerFactory.getLogger(ProcedureTest.class);
    private Connection conn;
    
    private void conn() throws Exception {
        String filePath = getClass().getResource("db.properties").getPath();
        File file = new File(filePath);
        if(file.exists()) {
            Properties prop = new Properties();
            prop.load(LegacyBatchBackup.class.getResourceAsStream("db.properties"));
            Class.forName(prop.getProperty("ewppis.mssql.driver"));
            String url = prop.getProperty("ewppis.mssql.url") + ";user=" + prop.getProperty("ewppis.mssql.user") + ";password=" + prop.getProperty("ewppis.mssql.password") + ";";
            conn = DriverManager.getConnection(url);
        } else {
            log.error("DB 접속정보가 없습니다.");
        }
    }
    
    @Test
    public void test1() throws Exception {
        
        CallableStatement cstmt = null;
        try {
            conn();
            String sql = "{CALL SP_LEGACY_INS(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
            cstmt = conn.prepareCall(sql);
            cstmt.setString(1, "PIS");
            cstmt.setString(2, "C");
            cstmt.setString(3, "admin");
            cstmt.setString(4, "이순신");
            cstmt.setString(5, "PID10");
            cstmt.setString(6, "VPN - 신청");
            cstmt.setString(7, "1");
            cstmt.setString(8, "127.0.0.1");
            cstmt.setString(9, "123456-1234567");
            cstmt.registerOutParameter(10, Types.INTEGER);
            cstmt.registerOutParameter(11, Types.VARCHAR);
            cstmt.executeUpdate();
            
            log.debug("return : {}", cstmt.getString(10));
            log.debug("return : {}", cstmt.getString(11));
            
        } catch (Exception e) {
            
        } finally {
            if(cstmt != null) cstmt.close();
            if(conn != null) conn.close();
        }
    }
}


⇄
Detect language » Korean


+ Recent posts

티스토리툴바