쿼리를 짜다보면 여러 행을

하나의 행으로 묶어서 보여줘야 할 때가 있다. 

여러 행을 하나로 합치는 건 기본적으로 집계이다.

그렇기 때문에 GROUP BY를 사용해 기준을 주어서 묶고 

함수를 사용해서 결과를 만들어야 한다.

이때, 여러 행을 ', '로 구분해서 합쳐주는 함수가

WM_CONCAT 이다. 

예시

다음의 예시를 통해 사용방법을

쉽게 익힐 수 있을 것이다.

개인별로 지원해본 회사명을 

입력한 테이블이 있다고 가정할 때,

이 테이블의 내용을 개인당 한 행으로

합쳐서 보이도록 하는 예시이다.

SELECT 이름,WM_CONCAT(회사명) AS "회사명"
FROM (
    SELECT 이름,회사명
    FROM TB_COM 
    )
GROUP BY 이름

도움이 되었다면

로그인이 필요없는 공감 버튼 꾹 눌러주세요! 

WITH 문

WITH [테이블명] AS (
    SELECT ... 
    FROM ...
    -- [임시테이블 SQL]
)
SELECT ..
FROM ... 
-- [사용 테이블]

쿼리를 만들다 보면 하나의 SQL문에서

가공한 테이블을 계속해서 사용해야 할 때가 있다.

가공한 테이블이 쿼리 안에서 계속 쓰다 보면 

가독성이 떨어져 혼란스럽고 개발하기 복잡해지는 경우가 종종 있다.

예시 

다음의 쿼리는 별로 복잡하지 않지만
엄청 복잡한 쿼리라고 가정해보자

-- 자주 조회할 테이블

SELECT T1.키,T1.컬럼1, T2.컬럼2
FROM T1 
    LEFT OUTER JOIN T2 
    ON T1.키 = T2.키

이제 이 쿼리는 복잡한 쿼리를 가져와서 

조건, 조인 등으로 사용하면서 더더욱 복잡해질 것이다.

-- 복잡해진 쿼리

SELECT A.키, TEST1.컬럼2-A, TEST2.컬럼2-B
FROM A
    LEFT OUTER JOIN 
    (
    SELECT T1.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
    ) TEST1
    ON A.키 = TEST1.키
    AND TEST1.구분코드 = 'A'
    
    LEFT OUTER JOIN 
    (
    SELECT T2.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
    ) TEST2
    ON A.키 = TEST2.키
    AND TEST2.구분코드 = 'B'

복잡해서 잘 안읽히는 코드는 유지보수에도 어려움이 있기 때문에 
최대한 보기 좋게 짜는 것도 요령인듯하다
이런 점에서 WITH 문을 활용할 수 있다

-- 간결해진 쿼리

-- 자주 조회할 테이블
WITH TEMP AS(
    SELECT T1.키,T1.컬럼1, T2.컬럼2
    FROM T1 
        LEFT OUTER JOIN T2 
        ON T1.키 = T2.키
)
-- 메인 쿼리
SELECT A.키, TEST1.컬럼2-A, TEST2.컬럼2-B
FROM A

    LEFT OUTER JOIN TEMP TEST1
    ON A.키 = TEST1.키
    AND TEST1.구분코드 = 'A'
    
    LEFT OUTER JOIN TEMP TEST2
    ON A.키 = TEST2.키
    AND TEST2.구분코드 = 'B'

도움이 된 정보였다면

로그인이 필요 없는 공감 버튼 꾹 눌러주세요! 

테이블에 컬럼 추가하는건 가끔하는데

매번 까먹어서 찾는다 ㅠㅠ

나름 DB 공부를 열심히 한다고 했고

DB 관련 자격증도 몇개 취득했음에도...

자주 사용하지 않으면 까먹는 건 어쩔 수 없나보다..!

화면에서 게시글이 보여질지 여부를 판단하기 위해서

컬럼을 하나 추가하는 작업이 었다.

오라클에 사용했고

DEFAULT로 'true'가 들어가게 해놓았다.

-- ALTER TABLE 테이블명 ADD (컬럼명 자료형 DEFAULT '값');
ALTER TABLE BOARD_CONTENT ADD (VIEW_YN varchar (5) DEFAULT 'true');

1. ORA-28000 에러

SCOTT 계정으로 접속해서 EMP테이블로 실습을 하려고하는데

다음과 같은 오라클 에러가 나왔어요... ㅠ 

계정이 잠겨있다는 말인데 계정의 잠김 여부는 어디서 확인할 수 있고 어떻게 풀 수 있는지 확인해보겠습니다!

2. 해결

USER의 잠김 상태확인

SYS계정으로 접속해서 DBA권한으로 유저들을 확인해보면 잠겨있는지 여부를 확인할 수 있어요.

수많은 USER들이 나오기 때문에 조건문에 SCOTT을 추가하여 검색해보겠습니다. 

SCOTT의 ACCOUNT_STATUS를 보니 패스워드 기한만료와 계정 잠금상태였네요.

여기서 잠깐 ACCOUNT_STATUS 상태를 정리하자면 아래와 같아요.

USER의 잠금해제

계정의 잠그거나 해제하는 방법은 아래와 같습니다.

위와 같은 방법으로 SCOTT 계정의 잠금 상태를 해제해보겠습니다.

USER의 패스워드 재생성

잠금은 풀렸으나 아직 패스워드가 파기된 상태입니다.

파기된 패스워드는 다시 재생성 해주어야 합니다.

패스워드 재생성은 이전에 올린 게시글 (클릭)을 참조하시면 될 것같아요

패스워드를 재생성 해주니 드디어 계정이 'OPEN' 상태가 되었네요.

OPEN된 계정으로 접속한 결과입니다. 

예상대로 잘 접속됩니다! :-) 

패스워드를 생성하는 것과 반대로 패스워드를 파기할 수도 있습니다.

구문은 아래와 같습니다.

1.USER 패스워드 분실  

오라클 사용자 비밀번호를 잊어버려서 접속이 안 되는 경우 어떻게 해야 할까요? 

(windows10 환경)

2.USER 패스워드 변경

이런 경우 SYS 계정에 접속해서 USER의 패스워드를 변경해주시면 됩니다.

사용자 패스워드 변경방법은 아래와 같아요!

SYS 계정의 패스워드도 잊어버렸다면 어떡할까?

명령 프롬프트에서 패스워드 입력 없이 접속이 가능합니다. (실습환경에서)

첫번째는 sqlplus 접속과 동시에 sys계정으로 들어가는 방법입니다. 

두번째 방법으로는, SQL Plus에 로그인 없이 접속하여 SYS로 연결하는 방법도 있습니다.

접속했다면 아래와 같이 SYS계정의 패스워드도 변경하시고 

원래 접속하려고 했던 USER의 패스워드까지 변경해주신다면 끝!

*참고로 데이터베이스에 USER의 목록을 확인하고 싶다면

위의 SELECT 문으로 확인하실 수 있습니다.

1.ERROR : ORA-28009  

: connection as SYS should be as SYSDBA or SYSOPER

SQL Plus에서 sys계정으로 로그인하기 위해 사용자명에 sys를 입력하면 다음과 같은 에러 메시지가 나옵니다.

ERROR:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

2. 해결

이를 해결하기는 아주 간단합니다.

사용자명을 sys as sysdba로 입력한 후 기존의 설정했던 oracle 패스워드를 입력하면 정상적으로 로그인 됩니다.

0. Oracle database 11g R2 완전제거 

오라클데이터 베이스를 지우려고 시도해본 사람들은 알겠지만 쉽지가 않아요.

어렵게, 어렵게 설치를 끝내고 나서 무언가 빼먹을 때도 종종 있고요! 간단한 설정을 하나라도 빼먹는 날엔 차라리 지우고 완벽하게 다시 설치하고 싶다는 생각이 듭니다..

그렇지만 설치만큼이나 어렵게 느껴지는 것이 삭제! ㅠㅠ

프로그램 추가/제거 안에 있으면 손쉽게 지울 수도 없기 때문에 완전히 지워졌다고 믿기도 어렵고요. 

오라클은 항상 호락호락하지 않네요. 

지우고서도 완전히 지워졌긴 한 걸까？ 의문이 들 때가 얼마나 많은지 몰라요,, 

그래서 혹시 저와 같은 분이 있을까 해서, 이번엔 오라클을 지우는 방법을 공유해보기로 했습니다! 

(windows10 환경)

1. De-install 파일 다운로드

오라클에 데이터베이스 설치 웹페이지 (http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html) 에 들어가보면 deinstall 파일을 다운받을 수 있습니다.

저는 C드라이브 바로 밑에 압축을 해제해두었답니다. (C:\deinstall)

사실 오라클 설치할 때 ORACLE_HOME에 같이 깔리기도 하니(~\app\user\product\11.2.0\deinstall) 확인해보시면 찾을 수도 있을 거에요. 

(저같은 경우 클라이언트도 설치하고 지우는 등 이것저것 반복하다보니...사라졌습니다. ㅜㅜ)

2. 명령 프롬프트 실행

설치가 완료됐으면 명령 프롬프트상에서 삭제를 시작할텐데, 주의할 점은 그냥 실행하지마시고 관리자 권한으로 명령프롬프트를 실행해주시면 됩니다.

*참고로 관리자 권한으로 실행하면 명령프로프트의 기본 디렉토리가 system32로 되어있는 것을 확인할 수 있을 거에요

3. De-install 수행

De-install 파일이 있는 경로를 입력 한 후 ‘-home’, 오라클홈 경로 입력 후 Enter!

저의 경우는 다음과 같은 경로를 입력하였답니다.

 -> C:\deinstall\deinstall -home C:\app\jaehyun\product

이 Oracle 홈에 구성된 데이터베이스 이름 목록을 지정하십시오. []:에서 Enter!

(만약에 [] 안에 데이터베이스 이름의 목록이 존재한다면 그 중 삭제하고자하는 데이터베이스의 이름을 대소문자 구분해서 정확히 입력하여주면 됩니다.)

계속하겠습니까(y – 예, n – 아니오)? [n]:에서 y를 입력해서 계속 삭제를 진행해주시면 삭제가 완료됩니다.

4. 레지스트리 삭제

레지스트리에서 oracle 에 대한 레지스트리를 삭제합니다.

[실행] - ‘regedit’ 입력 – HKEY_LOCAL_MACHINE – SOFTWARE – ORACLE을 삭제 합니다.

5. Program Files에서 삭제

C:\Program Files\oracle 폴더 또한 삭제합니다.

프로그램 폴더까지 삭제하면 모든 제거를 완료하신 것이랍니다.

잘 해결되셨다면 로그인이 필요없는 공감 버튼 부탁드려요!ㅎㅎ