오라클에서 뷰(VIEW)를 생성할 때 FORCE, NOFORCE 옵션을 부여할 수 있다. 옵션을 부여하지 않으면 기본값은 NOFORCE로 설정된다. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름 [(column_aliases)] AS SELECT 문 ; FORCE : 쿼리문의 테이블, 컬럼, 함수 등이 일부 존재하지 않아도 생성 됨 NOFORCE : 쿼리문의 테이블, 컬럼 함수 등이 모두 정상적으로 생성되어 있어야 생성됨 FORCE 옵션을 사용 후 컴파일 오류가 발생하면 INVALID 상태로 뷰가 생성 되고, 오류가 없을경우 정상적으로 생성 된다. NOFORCE 옵션을 사용 후 컴파일 오류가 발생하면 뷰는 생성되지 않는다. NOFORCE 사용 NOFORCE 옵션을 사용했..
오라클의 프로시저(Procedure)는 일련의 작업들을 하나로 묶어서 모듈화한 후 필요할 때 마다 호출하여 사용할 수 있는 기능이다. 함수(Function)와 비슷해 보이지만 프로시저는 리턴 값이 없다. (C언어의 void 함수와 비슷) 그러나 프로시저에서 레퍼런스 변수를 사용하면 결과 값을 리턴할 수 있다. 프로시저 생성 예시 부서코드 UPDATE 프로시저 (단순 작업 수행) CREATE OR REPLACE PROCEDURE pc_update_deptno ( p_job IN VARCHAR2, p_deptno IN VARCHAR2 ) IS BEGIN UPDATE emp SET deptno = p_deptno WHERE job = p_job; EXCEPTION WHEN OTHERS THEN ROLLBACK..
SQL Server 2012 버전부터 FORMAT() 함수를 사용하여 소수점의 형식을 변경할 수 있는 기능이 추가 되었다. 오라클 SQL의 TO_CHAR 함수와 FM을 사용하는 방법과 비슷하다. SQL Server 2012 이전 버전에서는 DECIMAL 형의 값을 FLOAT 형으로 변환하는 방법으로 소수점 이하의 0값을 제거하였지만, FORMAT 함수를 사용하면 간편하게 변환이 가능하다. SELECT FORMAT(123.456700, '##0.######') AS result 주의할 점은 숫자의 최대 자리수와 포맷(# or 0)의 길이가 일치해야 한다. 만약 소수점 이하의 값의 길이보다 "#"의 길이가 짧으면 해당 위치에서 반올림하여 표시한다. #은 수치값의 "0"을 제거하고, "0"은 해당 자리에 "0..
SQL Server 2012 버전부터 월의 마지막 일자를 구할 수 있는 EOMONTH() 함수가 추가되었다. 오라클 SQL의 LAST_DAY와 동일한 기능을 수행하는 함수이다. 월의 마지막 날짜 구하기 (SQL Server 2012 이상) SELECT EOMONTH('2020-09-09') AS result SQL Server 2012 버전부터 EOMONTH (End of Month) 함수를 사용하여 간편하게 해당 일자 기준으로 해당 월의 마지막 날짜를 구할 수 있다. 월의 마지막 날짜 구하기 (SQL Server 모든 버전) SELECT DATEADD (DAY, -1, DATEADD (MONTH, DATEDIFF (MONTH, 0, '2020-09-09') + 1, 0)) AS result SELECT..
SQL Server에서 CHARINDEX() 함수는 문자열에서 특정 문자를 찾고 위치를 반환한는 함수이다. 오라클 SQL의 INSTR 함수와는 다르게 뒤에서 부터 찾는 기능은 지원하지 않고, 문자열의 앞부터 또는 특정 위치부터 문자를 검색한다. CHARINDEX("찾을문자", "문자열", "시작위치") 찾을 문자가 존재하면 위치를 리턴하고, 존재하지 않으면 "0"을 리턴한다. 기본 사용법 SELECT CHARINDEX('sql', 'Microsoft SQL Server') 대소문자 구분없이 'sql' 문자를 찾아서 위치를 리턴하다. 시작위치를 생략하면 첫번째 문자부터 찾는다. 시작위치를 지정하여 찾기 SELECT CHARINDEX('sql', 'Microsoft SQL Server', 11) 문자열의 1..
SQL Server 2016 버전 부터 JSON 문자열의 특수문자를 Escape 처리를 할 수 있는 STRING_ESCAPE() 함수가 추가 되었다. 모든 문자열에 사용 가능하지만 현재는, json에서 사용하는 특수문자만 지원한다. STRING_ESCAPE("문자열", "유형") 현재 유형은 "json"만 지원함 json 문자열 자체를 Escape 처리하는 것이 아니라, json에 사용할 문자열 값을 Escape 처리하는 기능임 SELECT STRING_ESCAPE('"젠트"의 프로그래밍세상','json') AS result SELECT STRING_ESCAPE('https://gent.tistory.com/','json') AS result DECLARE @site VARCHAR(100) = '"젠트"..
SQL Server 2017 이상의 버전에서는 여러 행의 컬럼 값을 하나의 컬럼으로 합칠 때에는 STRING_AGG() 함수를 사용하면 된다. 오라클 SQL의 LISTAGG() 함수와 비슷한 기능을 수행한다. SQL Server 2017 이전 버전을 사용한다면 FOR XML PATH 서브 쿼리를 사용해야 한다. [MSSQL] 여러 행의 컬럼을 합치는 방법 (STUFF, FOR XML PATH) STRING_AGG 함수는 GROUP BY 절과 함께 사용해야 한다. ORDER BY 절을 사용하여 정렬이 가능하며 ORDER BY 절은 생략할 수 있다. STRING_AGG("합칠컬럼명", "구분자") WITHIN GROUP(ORDER BY "컬럼명") 기본 사용법 SELECT job , STRING_AGG(en..
SQL Server에서 여러 행(Row)의 컬럼 값을 하나로 합치기 위해서는 STUFF 함수와 FOR XML PATH 구문을 사용하면 된다. 오라클 SQL에서 XMLAGG, WM_CONCAT, LISTAGG 함수와 비슷한 기능을 수행한다. SQL Server 2017 이상 버전을 사용한다면 STRING_AGG 함수를 사용하면 된다. [MSSQL] STRING_AGG 함수 사용법 (컬럼 문자열 합치기) SELECT a.job , STUFF((SELECT ',' + ename FROM emp WHERE job = a.job FOR XML PATH('') ), 1, 1, '') AS enames FROM emp AS a GROUP BY a.job STUFF() 함수는 문자열을 자리수 만큼 특정문자로 치환하고 ..
SQL Server에서 행을 열로 변환하기 위해서는 PIVOT() 함수를 이용하면 된다. 특히 그룹함수를 이용하여 통계를 추출할 때 많이 사용된다. SELECT * FROM ( 피벗할 쿼리문 ) AS result PIVOT ( 그룹합수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값] ... ) AS pivot_result 그룹함수는 SUM(), COUNT(), AVG() 등을 사용할 수 있다. 피벗컬럼값은 한번 지정하면 데이터가 존재하지 않아도 고정적으로 출력된다. 피벗컬럼값의 대괄호([ ])는 존재해야하며, FROM절과 PIVOT절의 별칭(result, pivot_result)은 꼭 붙여줘야 오류가 발생하지 않는다. PIVOT 기본 사용법 - 직군별, 근무지별 급여 합계 SELECT * FROM (..
SQL Server 2016 부터 STRING_SPLIT() 함수가 추가되어 컬럼 문자열의 구분자를 행으로 분리 할 수 있다. 이전 버전까지 사용자 함수를 만들어서 사용했지는 STRING_SPLIT() 함수를 사용하면 쉽게 해결 된다. STRING_SPLIT( "문자열", "구분자" ) 함수 사용법 SELECT empno , ename , skills , value FROM emp CROSS APPLY STRING_SPLIT(skills, ',') 조회된 skills 컬럼의 쉼표(',') 구분자를 잘라서 해당 개수만큼 행으로 변환한다. 결과 컬럼은 value로 생성된다. SELECT * FROM STRING_SPLIT('Java,Python,C++,C#', ',') FROM절에서 해당 함수를 사용할 수 ..