기억력이 나쁜 나에게 주는 페이지

-- 값에서 검색값의 시작위치를 반환

oracle : select instr('달력1,달력2,달력3', ',', 1) from dual; ----> 4를 반환

mssql : select charindex(',', '달력1,달력2,달력3') ----> 4를 반환

​

-- 조건 비교 후 처리시

oracle : decode(조건, true값, false값)

mssql : case when 조건 then true값 else false값 end

​

-- 현재일에서 1달 뒤 구하기

oracle : SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;

mssql : SELECT DATEADD(mm, 1, GETDATE())

​

-- 날짜로 변환

oracle : SELECT TO_DATE('202005' || '01','YYYYMMDD' ) FROM DUAL;

mssql : SELECT CAST('202005' + '01' AS DATE)

​

-- 널값시 처리

oracle : NVL(END_YN, 'N')

mssql : ISNULL(END_YN, 'N')

​

-- 사용된 바이트 수를 반환합니다

oracle : vsize(expression)

mssql : DATALENGTH ( expression )

​

-- 조건문

oracle : IF 조건1 THEN 처리1 ELSIF 조건2 THEN 처리2 ELSE 처리3 END IF;

mssql : IF 조건1 처리1 ELSE IF 조건2 처리2 ELSE 처리3

​

-- 변수에 값 할당시

oracle : param := 0;

mssql : SET @param = 0;

​

-- 문자열 자르기

oracle : substr('20200520', 1, 4) --- 2020

mssql : substring('20200520', 1, 4)

​

oracle : substr('20200520', 0, 4) --- 2020

mssql : substring('20200520', 1, 4) -- 변환 작업시 주의해서 1로 수정해야 함

​

-- 절삭하기

oracle : SELECT TRUNC(150.75, 1) FROM DUAL;

mssql : SELECT ROUND(150.75, 1, 1);

​

-- 나머지 구하기

oracle : SELECT MOD(12,5) FROM DUAL;

mssql : SELECT 12%5

​

-- 두 기간의 월 수 구하기

oracle : SELECT TRUNC(MONTHS_BETWEEN(TO_DATE('2020-06-05','RRRR-MM-DD'), TO_DATE('2020-05-01','RRRR-MM-DD'))) "month" FROM DUAL;

mssql : SELECT DATEDIFF(MONTH, CAST('2020-05-01' AS DATE), CAST('2020-06-05' AS DATE))

​

-- 마지막 일자 구하기

oracle : select TO_CHAR(LAST_DAY(sysdate), 'DD') from dual

mssql : select DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(CONVERT(CHAR(6),GETDATE(),112)+'01' AS DATE))))

​

-- 올림

oracle : select ceil(1.1) from dual; -- 2

mssql : select ceiling(1,1) -- 2

​

-- ORACLE

SELECT LEVEL_CODE,

LAG(LEVEL_CODE) OVER (ORDER BY LEVEL_CODE) UPLEVEL,

LAG(GRADE_CODE) OVER (ORDER BY LEVEL_CODE) UPGRADE

FROM HR_CODE_LEVEL

​

-- MSSQL

SELECT A.LEVEL_CODE, B.LEVEL_CODE UPLEVEL, B.GRADE_CODE UPGRADE

FROM

(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) A

LEFT OUTER JOIN

(SELECT ROW_NUMBER() OVER(ORDER BY LEVEL_CODE) ROWID, LEVEL_CODE, LEVEL_NAME, GRADE_CODE FROM HR_CODE_LEVEL) B

ON

A.ROWID = B.ROWID+1

[출처] blog.naver.com/dbbible/221969929542

MSSQL 에서 sp_help "테이블명" 으로 하면 테이블의 정보를 확인할 수 있으나
컬럼의 Comment까지 같이 확인은 안된다.
아래는 테이블의 컬럼Comment를 확인할 수 있는 쿼리이다.
 
SELECT A.TABLE_NAME, 
		C.VALUE AS TABLE_COMMENT,
		A.COLUMN_NAME, A.DATA_TYPE, 
		ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),  
				CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' +
				CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,
		A.COLUMN_DEFAULT, A.IS_NULLABLE,
		B.VALUE AS COLUM_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS A LEFT OUTER JOIN
	SYS.EXTENDED_PROPERTIES B
	ON B.major_id = object_id(A.TABLE_NAME) 
	AND A.ORDINAL_POSITION = B.minor_id
	LEFT OUTER JOIN
		(SELECT object_id(objname) AS TABLE_ID,
					VALUE
		FROM ::FN_LISTEXTENDEDPROPERTY
			(NULL, 'User','dbo','table',
				NULL, NULL, NULL)
			) C
	ON object_id(A.TABLE_NAME) = C.TABLE_ID
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION 
 

SELECT A.COLUMN_ID,
       A.COLUMN_NAME,
       A.DATA_TYPE,
       CASE WHEN A.DATA_TYPE = 'NUMBER' THEN A.DATA_PRECISION ||','|| A.DATA_SCALE
            WHEN A.DATA_TYPE = 'DATE' THEN '7'
            ELSE TO_CHAR(DATA_LENGTH)
       END DATA_SIZE,
       B.COMMENTS COMMENTS,
       DECODE(D.CONSTRAINT_TYPE, 'P', 'PK') AS KEY_FIELD
FROM ALL_TAB_COLUMNS A INNER JOIN ALL_COL_COMMENTS B
                       ON  A.TABLE_NAME = B.TABLE_NAME
                       AND A.OWNER = B.OWNER
                       AND A.COLUMN_NAME= B.COLUMN_NAME
                     LEFT OUTER JOIN ALL_CONS_COLUMNS C
                       ON A.TABLE_NAME = C.TABLE_NAME
                       AND A.OWNER = C.OWNER
                       AND A.COLUMN_NAME = C.COLUMN_NAME
                       AND C.POSITION IS NOT NULL
                     LEFT OUTER JOIN ALL_CONSTRAINTS D
                       ON  C.TABLE_NAME = D.TABLE_NAME
                       AND C.OWNER = D.OWNER
                       AND C.CONSTRAINT_NAME  = D.CONSTRAINT_NAME
                       AND D.CONSTRAINT_TYPE = 'P'
WHERE A.TABLE_NAME = '테이블명'
ORDER BY A.COLUMN_ID

출처 : http://blog.naver.com/PostView.nhn?blogId=2zerox&logNo=90150539116
[출처] MSSQL 테이블 정보 쿼리(Comment 포함)|작성자 수정이아빠

- 오라클 tns설정하는 방법

오라클이 있는 폴더를 연다.

/ORACLE/product/9.2.0/network/admin/tnsnames.ora 
연결하고자 하는 오라클 host를 다음과 같이 입력한다.

DBTEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =xxx.xxx.xxx.xxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbtest)
    )
  )

2대의 오라클 서버로 failover기능 부여하여 설정 시
  
TESTDB =
  (DESCRIPTION =
        (LOAD_BALANCE=OFF)
        (FAILOVER=ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xx1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xx2)(PORT = 1521))
    )
 (CONNECT_DATA =
                 (SERVER = DEDICATED)
                 (SERVICE_NAME =testdb)
                 (failover_mode=(type=none)(method=basic))
          )
)