하드코딩이 싫어요

WITH TT AS (
    SELECT 10000 AS DEPTCD, '회사' AS DEPTNAME, NULL AS PARENTDEPTCD, 1 AS DEPTH
    UNION ALL
    SELECT 11000 AS DEPTCD, '경영지원실' AS DEPTNAME, 10000 AS PARENTDEPTCD, 2 AS DEPTH
    UNION ALL
    SELECT 12000 AS DEPTCD, '영업지원실' AS DEPTNAME, 10000 AS PARENTDEPTCD, 2 AS DEPTH
    UNION ALL
    SELECT 12100 AS DEPTCD, '영업팀' AS DEPTNAME, 12000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 12200 AS DEPTCD, '영업팀' AS DEPTNAME, 12000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 11100 AS DEPTCD, '경제팀' AS DEPTNAME, 11000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 11200 AS DEPTCD, '경영팀' AS DEPTNAME, 11000 AS PARENTDEPTCD, 3 AS DEPTH
    UNION ALL
    SELECT 11210 AS DEPTCD, '총무파트' AS DEPTNAME, 11200 AS PARENTDEPTCD, 4 AS DEPTH
    UNION ALL
    SELECT 11220 AS DEPTCD, '인사파트' AS DEPTNAME, 11200 AS PARENTDEPTCD, 4 AS DEPTH
    UNION ALL
    SELECT 11110 AS DEPTCD, '경제전략1팀' AS DEPTNAME, 11100 AS PARENTDEPTCD, 4 AS DEPTH
    UNION ALL
    SELECT 11120 AS DEPTCD, '경제전략2팀' AS DEPTNAME, 11100 AS PARENTDEPTCD, 4 AS DEPTH
), CTE AS (
    SELECT DEPTCD
        ,  DEPTNAME
        ,  PARENTDEPTCD
        ,  CONVERT(NVARCHAR(300), DEPTNAME) AS FULLPATH
        ,  CONVERT(NVARCHAR(300), DEPTNAME) AS REVERSEFULLPATH
    FROM TT
    WHERE PARENTDEPTCD IS NULL
    UNION ALL
    SELECT B.DEPTCD
        ,  B.DEPTNAME
        ,  B.PARENTDEPTCD
        ,  CONVERT(NVARCHAR(300), CONCAT(C.FULLPATH, ' > ', B.DEPTNAME)) AS FULLPATH
        ,  CONVERT(NVARCHAR(300), CONCAT(B.DEPTNAME, ' > ', C.REVERSEFULLPATH)) AS REVERSEFULLPATH
    FROM TT B, CTE C
    WHERE B.PARENTDEPTCD = C.DEPTCD
)
SELECT * 
FROM CTE

오라클 DB에서 MSSQL로 옮기는 작업을 하는 중인데

차이점을 기록해둘 필요가 있어, 하나씩 작성해보려 한다.

※ 변수선언 및 값 대입

ORACLE
	abc VARCHAR2(10);
	abc VARCHAR2(10) := 1;
	abc := 5;
    

MSSQL
	DECLARE @abc VARCHAR(10);
	DECLARE @abc VARCHAR(10) = 1;
	SET @abc = 5;

※ SELECT 변수 대입

ORACLE
	V_A VARCHAR2(100);
	V_B NUMBER(5);
	NUM_A NUMBER(10);
    
	SELECT COLUMN_A, COLUMN_B
	  INTO V_A, V_B
	  FROM TABLE_NAME
	 WHERE COLUMN_A = 1
	   AND COLUMN_B = NUM_A;
       

MSSQL
	DECLARE @V_A 	VARCHAR(100);
	DECLARE @V_B 	INT;
	DECLARE @NUM_A 	BIGINT;
    
	SELECT @V_A = COLUMN_A
	    ,  @V_B = COLUMN_B
	  FROM TABLE_NAME
	 WHERE COLUMN_A = 1
	   AND COLUMN_B = @NUM_A;

※ 예외 선언

ORACLE
    BEGIN
        -- SQL
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- SQL
        WHEN OTHERS THEN
            -- SQL
        
        
MSSQL
    BEGIN TRY
        -- SQL
        
        -- ORACLE: NO_DATA_FOUND
        IF @@ROWCOUNT = 0 BEGIN
            -- SQL
        END
    END TRY
    BEGIN CATCH
        -- SQL
        -- WHEN OTHERS THEN
    END CATCH

※ 카탈로그 통계 정보 갱신

ORACLE
	DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE_NAME');
    
MSSQL
	UPDATE STATISTICS TABLE_NAME;

Window Application 설치 유무를 확인하는 방법이다.

레지스트리를 읽어서 하는 방법인데..

programName은 "프로그램 추가/제거"에 나와있는 명칭으로 진행하면 된다.

public static bool CheckInstalledApplications(string programName) {
    bool isInstalled = false;

    foreach
    (string item in Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall").GetSubKeyNames())
    {
        object itemProgramName
        = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Uninstall\\" + item).GetValue("DisplayName");

        Console.WriteLine(itemProgramName);

        if (string.Equals(itemProgramName, programName)) {
            Console.WriteLine("Install status: INSTALLED");
            isInstalled = true;
            break;
        }
    }
    return isInstalled;
 }

출처: https://ucnn.tistory.com/119

프로젝트를 진행한 후 마무리 단계에서

산출물 관련 문서중 테이블 정의서나, 프로시저 정의서를 작성해야 할 때가 있다.

매번, 클릭해서 정보 확인하다가, 이번에 귀찮음의 귀찮음을 이기고 기록을 남겨본다.

필수정보(개인적으로)들만 조회 쿼리 만들어 보았다.

테이블 정보 및 컬럼정보다. (참고로 INFORMATION_SCHEMA.TABLES는 테이블 정보)

SELECT UPPER(TABLE_NAME), COLUMN_NAME
    ,  COLUMN_COMMENT
    ,  DATA_TYPE
    ,  CASE WHEN DATA_TYPE = 'datetime' THEN ''
            ELSE REPLACE(substring(COLUMN_TYPE, INSTR(COLUMN_TYPE, '(') + 1), ')', '') 
             END AS LENGTH
    ,  IS_NULLABLE
    ,  COLLATION_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA='db_schema' AND TABLE_NAME='table_name';

db_schema는 DB명을 입력하면 되고 table_name은 테이블명을 입력하면 된다.

다음은, 프로시저와 파라미터 정보다

SELECT A.ROUTINE_NAME
    ,  A.ROUTINE_TYPE
    ,  B.PARAMETER_MODE
    ,  B.DATA_TYPE
    ,  B.PARAMETER_NAME
    ,  B.DTD_IDENTIFIER
    ,  A.ROUTINE_DEFINITION
  FROM INFORMATION_SCHEMA.ROUTINES   A
  JOIN INFORMATION_SCHEMA.PARAMETERS B ON B.SPECIFIC_CATALOG = A.ROUTINE_CATALOG
                                      AND B.SPECIFIC_NAME    = A.ROUTINE_NAME
 WHERE A.ROUTINE_SCHEMA 	= 'db_schema'
   AND A.ROUTINE_CATALOG 	= 'def'
   AND A.ROUTINE_TYPE 		= 'PROCEDURE'
;

db_schema는 DB명을 입력하면 된다