[MSSQL] procedure (프로시저) 사용하기

2013. 1. 18. 09:40

프로시저 생성

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();
       }
   }
}

Thë 굽은ㄴr무™

[MSSQL] procedure (프로시저) 사용하기

+ Recent posts

티스토리툴바