2012-12-27
[ORACLE] CONNECT BY PRIOR 한방정리
WITH A AS(
SELECT 'A' AS CODE, '' AS P_CODE , '인사팀' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'B' AS CODE, '' AS P_CODE , '관리팀' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'C' AS CODE, '' AS P_CODE , '구매팀' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'D' AS CODE, 'A' AS P_CODE , '인사파트' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'E' AS CODE, 'A' AS P_CODE , '총무파트' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'F' AS CODE, 'B' AS P_CODE , '관리파트' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'G' AS CODE, 'B' AS P_CODE , '경리파트' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'J' AS CODE, 'D' AS P_CODE , '천과장' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'K' AS CODE, 'D' AS P_CODE , '이대리' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'L' AS CODE, 'D' AS P_CODE , '윤주임' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'M' AS CODE, 'E' AS P_CODE , '김과장' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'N' AS CODE, 'E' AS P_CODE , '홍대리' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'O' AS CODE, 'E' AS P_CODE , '안선임' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'P' AS CODE, 'E' AS P_CODE , '문사원' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'Q' AS CODE, 'F' AS P_CODE , '오과장' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'R' AS CODE, 'F' AS P_CODE , '도사원' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'S' AS CODE, 'G' AS P_CODE , '서부장' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'T' AS CODE, 'G' AS P_CODE , '허대리' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'U' AS CODE, 'G' AS P_CODE , '전주임' AS TEAM_NAME FROM DUAL
UNION ALL
SELECT 'V' AS CODE, 'C' AS P_CODE , '고상무' AS TEAM_NAME FROM DUAL
)
SELECT LEVEL, CODE,P_CODE,LPAD(' ', 8 * (LEVEL-1))||TEAM_NAME
FROM A
START WITH P_CODE IS NULL
CONNECT BY PRIOR CODE = P_CODE
;
***** ORDER BY & ORDER SIBLINGS BY
- ORDER BY : 통으로 정렬
- ORDER SIBLINGS BY : 계층별로 정렬
2012-12-24
2012-12-18
2012-12-17
[ORACLE] Join Update
Sql Server에서 Join Update는 아래와 같이 쉽게 가능 함
Update A_TableName Set Column = B.Column
From A_TableName as A Inner join
B_TableName as B ON A.keyColumn = B.keyColumn
Where A.Column <> B.Column
Oracle의 경우 아래와 같은 문법으로 가능
UPDATE /*+bypass_ujvc*/
(
SELECT A.Column, B.RefColumn
FROM ATable A,
BTable B
WHERE A.keyColumn = B.keyColumn
)
SET Column = RefColumn
inner join을 한 다음, 수정할 필드와 대상 필드를 조회하고,
그 데이터를 가져와서 바로 업데이트 처리...
**** 특이점..
/*+bypass_ujvc*/
이 부분을 빼고 쿼리를 돌리는 경우
ORA-01779:키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.
(ORA-01779 cannot modify a column which maps to a non key-preserved table)
라는 메시지와 함께 에러 발생!!!
이 부분은 Constraint를 피해서 Join Update를 할수 있도록 해주는 구문(힌트)이다.
Join Update 라 함은 1:1 혹은 1:M 로 묶인 상태에서 왼편..즉 M쪽 집합이어야 하는데..
1쪽 집합의 조인하는 컬럼이 UK혹은 PK로 설정되어 있어야 한다..
만약 그런 상황이라면 /*+bypass_ujvc*/ 구문은 없어도 쿼리가 동작한다..
하지만..대부분의 경우 여러개의 테이블을 조인하거나 엑셀등의 데이터를
data import 해서 만들어진 테이블들과 조인을 하는 경우가 많기에 UK 혹은 PK로 설정하고
업데이트를 실행하기가 쉽지 않다..
그럴때 '오라클에서 잘못되어도 책임지지 않음' 이라는 의미로 쓰는 힌트이다..
2012-12-16
2012-12-14
[ORACLE] CAST Function
* CAST
CAST는 하나의 built-in 데이터 타입이나 collection-typed value를
다른 built-in datatype 이나 collection-typed value 로 변환해 준다.
* 사용문법
CAST(EXPR AS TYPE_NAME)
하나의 built-in 데이터타입을 다른 데이터타입으로 변경하는 경우 일반적인 데이터 형변환
함수의 사용과 동일하다.
일반적인 형변환 함수와 구분되는 특징은 multiset 에 대해 형변환을 할 수 있다는 것이다.
CAST 는 LONG, LONG RAW 를 지원하지 않는다.
* CAST 예제
A. 하나의 built-in data type 에서 하나의 built-in data type 으로 변환
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> create table dept1
2 as select cast(deptno as varchar2(2)) deptno,
3 cast(dname as varchar2(20)) dname
4 from dept;
Table created.
SQL> desc dept1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO VARCHAR2(2)
DNAME VARCHAR2(20)
DEPTNO 의 데이터형이 변경되고 DNAME VARCHAR2 타입의 데이터길이가 변경된 걸
확인할 수 있다.
SQL> create table dept2
2 as select cast(deptno as varchar2(2)) deptno,
3 cast(dname as varchar2(5)) dname
4 from dept;
Table created.
SQL> select * from dept2;
DE DNAME
-- -----
10 ACCOU
20 RESEA
30 SALES
40 OPERA
cast 에서 varchar2 데이터 형의 길이를 본래의 길이보다 더 짧게 변환할 때
입력되어있는 데이터 길이가 변경되는 길이보다 긴 경우 위와 같이 overflow 된
부분은 제거된다.
B.built-in data type 을 collection-typed value 로 변환
SQL> create or replace type dept_typ as table of varchar2(14);
Type created.
SQL> select e.ename,
2 cast(multiset(select d.dname from dept d where d.deptno=e.deptno) as dept_typ) as charset
3 from emp e
4 where rownum < 5;
ENAME CHARSET
---------- --------------------
SMITH DEPT_TYP('RESEARCH')
ALLEN DEPT_TYP('SALES')
WARD DEPT_TYP('SALES')
JONES DEPT_TYP('RESEARCH')
2012-12-13
2012-12-12
[ORACLE] Replace 함수 사용법
* 사용법
REPLACE(char, searh_str, replace_str)
REPLACE(대상구문, 찾을 문장. 바꿀 문장)
ex) REPLACE('I CAN NOT DO IT','CAN NOT', 'CAN')
I CAN NOT DO IT 이라는 문장중 CAN NOT 이라는 문장을 CAN 으로 바꾸는 구문
문자열을 다른문자열로 바꾸는 함수이다.
char 문자열에서 search_str 을 찾아 replace_str 로 바꾼다.
replace_str 값이 생략되었을때는 searh_str 문자열을 제거한 결과를 돌려준다.
SELECT REPLACE('YOU ARE MY ANGEL,'MY ANGEL', 'ANGEL') AS "결과"
FROM DUAL;
----------------
YOU ARE ANGLE
* 공백을 '=' 형태로 바꾸는 구문
SELECT REPLACE('YOU ARE MY ANGEL',' ', '=') AS "결과"
FROM DUAL;
FROM DUAL;
----------------
YOU=ARE=MY=ANGEL
* REPLACE를 이용해 TRIM 효과(공백제거) 구문
SELECT REPLACE('YOU ARE MY ANGEL',' ', '') AS "결과" FROM DUAL;
-------------
YOUAREMYANGEL
주의) 대상구문중에 공백이 있을 경우 공백 또한 포함이 되어야 한다.
* 공백이 2개
SELECT REPLACE('YOU ARE MY ANGEL','MY ANGEL', 'ANGEL') AS "결과"
FROM DUAL;
FROM DUAL;
----------------
YOU ARE MY ANGEL
* 공백이 1개
SELECT REPLACE('YOU ARE MY ANGEL','MY ANGEL', 'ANGEL') AS "결과"
FROM DUAL;
FROM DUAL;
-------------
YOU ARE ANGEL
피드 구독하기:
글 (Atom)