데이터 검증하기 - 값 보정방법 및 쿼리

데이터 검증하기 - 값 보정방법 및 쿼리

 9  1  0  3  1  5  -  9  2  3  4  4   5   6

[1  2  3  4  5 6  7  8  9 10 11  12 13 14]

1. 자르기 SUBSTR(문자열,자를첫번째자리수,자를자리길이)

:보정할 자릿수 잘라내어 추출. 

2. 잘라낸 자리수 목적에 맞게 보정

3. 보정후 잘라낸 문자 다시 이어 붙이기. CONCAT( , , ,)

: 보정할 자릿수 이전까지 문자 + 보정 완료+ 보정할 자릿수 이후문자

성별보정 

8번째 자리 추출 SUBSTR(): [1-4] 임의 값으로. FLOOR(RAND()*4) +1

단,  1,2번째 자리가 18년 이상: 3,4 /  이하 1,2 ..

IF(조건,참,거짓): 조건: 문자열이 18이상이면 3,4 / 이외에는 1,2 

월값보정

3,4번째 자리 보정: [1-12] 임의 값으로 FLOOR(RAND()*12) +1

단, 1자리 숫자일때는 앞에 0을 붙어주어야함. LPAD(문자열, 채울자리수,붙일문자)

1. 

일값보정

*** 일값은 월값처럼 고정되어있지 않고 각 월에따라 일수가 변하는 것을 고려해야함.

5,6번째 자리 보정: 3,4번째(월)값에 따른 LAST_DAY()추출 해야한다.

LAST_DAY()함수를 사용하기 위해서는 날짜값양식이 필요함 '1991-03-15' 형식의.

즉, 민번의 910315 를 1991-03-15 로 보정하여, last_day를 추출하면 1991-03-31의 값을 얻어낼수 있다. 이때 얻은 값을 다시 잘라서 마지막 일수의 자리수만 잘라내어 substr(날짜,9,2) 마지막 일값을 추출하여 1 ~ 마지막 일수 값 사이의 임의 값을 구한다 .so..

1. 생년월일을 날짜 형식에 맞게 보정하기. 

1) 년도: 1991: 1,2번째 자리수를 잘라내어 성별이 3,4 인경우 20을/ 

    1,2 인 경우 19를 붙여준다. CONCAT

IF 혹은 CASE문. 

***    CASE WHEN 값 IN (1,2) / 값 < 3 THEN ~~~  //값이 3 이하의 값일때.

  WHEN ~~~  THEN ~~~

  ELSE ~~~~ 

  END // 필수 ! 

(범위가 아닌 하나의 값인경우에는) CASE 값 WHEN 1 THEN~~ // 값이 1일때,

2. 4자리의 년도수를 다시 '1991-03-15'의 형식으로 만들어 붙여준다. 이때 

lastday를 추출하기 위해서.. 무조건 유효한 01일자로 대체한다. → '1991-03-01'

concat으로 붙여 만든 날짜형식의 문자에

 last_day() 함수를 사용하여 마지막 날짜를 구한다 → '1991-03-31'

마지막 일수만 잘라낸다. substr 또는 RIGHT(문자열, 자를번째자리수) → 31.

3. 마지막 일수를 사용하여 1~ 마지막 일수까지의 임의값을 구한다.

floor(rand() * 마지막일수) + 1 

4. 이때 한자리수앞에는 0을 붙여야한다.  LPAD()

5. 이제 이렇게 구한 01~31 까지의 값을 민번에서 원래의 5,6번째자리에 넣어준다 concat()

6. 업데이트

UPDATE table_name

SET column_name = 최종 보정 값.

일값보정 함수

이 작업을 function으로 만들때.

*** RETURNS 타입맞춰주어야 함.!!! 

값을 대입해줄 변수 선언해주어야함. 

변수선언 DECLARE: 항상 BIGIN 밑에 모두 선언해주어야 한다. 

각각 순서별로 SET해줄 변수를 선언한후,

SET 변수명 = 보정값.  

마지막값을 반환 꼭 해주어야함 !!! 

RETURN 변수명 ; 

SELECT 펑션명(매개변수) →매개변수 필요.. 

ex) SELECT lect fn_get_right_cus_rn(cus_rn) FROM customer;

**기능에서는 SELECT만 가능하고, [UPDATE/INSERT/DELETE] 를 수행할수 없어서 디비 적용하려면 기능 만들어주고 따로 [UPDATE/INSERT/DELETE]해주어야함.

모든과정 프로시저.

procedure

반환(return) 하지 않음. 일련의 과정을 수행하고 그 결과를 [UPDATE/INSERT/DELETE]한다. ,

call.→매개변수 안넣어줘도 된다.  넣을수도있지만.

cursor :  수행할 작업의 데이터 범위를 지정. 

fetch: 범위에서 1건을 꺼내겠다. 

1. 사용할 변수 선언.

DECLARE _done INT DEFAULT FALSE; 

// 커서반복 종료 조건 저장할 변수 done을 0으로 초기화 한다.

DECLARE 변수명 변수 타입

... // 사용한 변수선언한다. 이때, 커서 선언시 셀렉해오는 컬럼의 갯수와 변수가 일치해야한다.. 

2. 커서선언:

DECLARE 커서명 CURSOR FOR

 [범위] // 미리 보정할 범위를 셀렉해온다. 

ex) SELECT cus_no, cus_rn // 2개의 컬럼 셀렉해오므로 2개 변수 필요.

    FROM customer;

3. 핸들러 선언.

DECLARE CONTINUE HANDLER FOR NOT FOUND

// FOR 다음의 조건에 해당하면 (여기서는 조건이 not found: 더이상 없으면)

컨디뉴 핸들러를 수행하라는 말.

SET _done = TRUE; // _done이 참인것이 없으면 수행. (거짓으로 초기화 해놓은상태.)

4. 커서열기

OPEN 커서명 ; //커서 시작 

REPEAT  //반복하라 

FETCH 커서명 INTO 변수명. // 꺼내와서 변수로 담아라.

IF NOT _done THEN // _done이 거짓일때 (값이 false 일때 수행하라..) 

[보정 내용.

1)성별 

UPDATE table // 업뎃하라.

SET 컬럼명 = 보정값

WHERE cus_no = _cusNo; // PK일치하는 것들만..

2)월 

UPDATE table

SET 컬럼명 = 보정값

WHERE cus_no = _cusNo;

3) 일 값 보정. **기능 사용가능 

 UPDATE table

SET 컬럼명 = 보정값 

WHERE cus_no = _cusNo;

 ** 펑션사용시

UPDATE customer
SET cus_rn = fn_get_right_cus_rn(_cusRn) 
WHERE cus_no = _cusNo;

// 변수에 담아논 값을 펑션의 매개변수로 다시 줌

END IF;

UNTIL _done END REPEAT;

CLOSE 커서명; // 커서닫기.

END