ITEM_CODE(ITEM_001)쿼리로 만들기(LPAD, TO_NUMBER, SUBSTR, MAX, NVL, || 활용하기)

"ITEM_ "까지는 문자 "001"은 숫자로 하여

상품이 하나씩 늘어날 때마다 +1씩 숫자가 변경되어

ITEM_001 ITEM_002 ITEM_003 ITEM_004 ITEM_005

이런 형식으로 변경되도록 쿼리를 만들고자 한다. 

 

1. 임의로 자료하나를 넣고 쿼리를 만들어 보자

INSERT INTO SHOP_ITEM (

ITEM_CODE

, ITEM_NAME

, ITEM_PRICE

, ITEM_COMMENT

, CATE_CODE

)VALUES (

'ITEM_001'

, '가장 빨리 만나는 코어 자바' 

, 20000

, '자바 기본서'

, 'CATE_002'

);

 

2. SUBSTR(문자열 자르기), TO_NUMBER(문자를 숫자로 변경)

임의로 넣은 자료의 ITEM_CODE는 ITEM_001이다.

여기서 001은 숫자일까 문자일까? 결론적으로 문자이다.

우리는 001 부분에 상품이 추가될 때마다 +1씩 하도록 만들것이기 때문에

ITEM_001에서 001부분만 잘라 숫자로 바꿔주는 쿼리를 만들 것이다.

 

ITEM_001의 여섯번째 자리부터 자르면

SELECT SUBSTR(ITEM_CODE, 6) FROM SHOP_ITEM; 

실행 값은 001이 나온다.

이때, 001이 문자이기 때문에 숫자로 바꾸어주는 작업을 한다.

SELECT TO_NUMBER( SUBSTR(ITEM_CODE, 6)) FROM SHOP_ITEM; 

 

3. NVL(NULL값일 경우,변경할 내용), MAX(가장 큰 값)

위의 식에는 치명적인 결함이 있다. 첫번째 데이터가 없을 때는 쿼리 자체가 실행되지 않는다는 것이다.

즉, 데이터가 있을 때야 쿼리가 정상 작동하지만, 없을 때는 실행되지 않아 보안해주어야 한다.

SELECT NVL(MAX(TO_NUMBER(SUBSTR(ITEM_CODE, 6))),0) +1 FROM SHOP_ITEM; 

임의로 넣었던 데이터가 있었기 때문에 1+1로 2가 나왔다.

 

4. LPAD(넣을글자, 총만들 문자 길이, 나머지칸에 넣을 문자/숫자)

우리는 위에서 2라는 숫자를 도출했다.

그런데 우리가 원하는 것은 001, 002라는 숫자이다.

위에서 만든 숫자에, 총 3자리를 만들것이고, 나머지에는 0을 넣는 쿼리를 만든다.

SELECT LPAD(NVL(MAX(TO_NUMBER(SUBSTR(ITEM_CODE, 6))),0) +1 , 3, 0) FROM SHOP_ITEM;

 

5. ||(문자열 붙이는 함수)

최종적으로 ITEM_ 이라는 문자와 위에서 만든 순자를 합하여 ITEM_CODE를 만들고자 한다.

두개의 식을 합하기 위해서는 문자열을 합하는 함수 ||를 사용한다.

SELECT 'ITEM_' || LPAD(NVL(MAX(TO_NUMBER(SUBSTR(ITEM_CODE, 6))),0) +1 , 3, 0) FROM SHOP_ITEM;

 

 

임의로 넣은 데이터를 넣고 다시 실행 했을 때