꿈꾸는 별나라

-- STEP 1 테스트 테이블 생성
CREATE TABLE TEST_A (
TEST_CN VARCHAR2(100)
);
-- STEP 2 테스트 데이터 생성
INSERT INTO TEST_A ( TEST_CN ) VALUES ('AAA');
-- STEP 3 테이블 및 데이터 생성 확인
SELECT * FROM  TEST_A;
-- STEP 4 프로시저 생성 
CREATE OR REPLACE PROCEDURE TEST_AA (
IN_TABEL_NM IN VARCHAR2
)
IS
  V_STR VARCHAR2(500);
  V_RESULT  VARCHAR2(500);
BEGIN 

FOR  REC  IN  (
  SELECT OBJECT_NAME
   FROM   SYS.ALL_OBJECTS
   WHERE  OBJECT_TYPE = 'TABLE'
     AND  OBJECT_NAME = IN_TABEL_NM
   )LOOP
    
    V_STR := 'SELECT TEST_CN FROM '|| REC.OBJECT_NAME ;
EXECUTE IMMEDIATE V_STR INTO V_RESULT;

DBMS_OUTPUT.PUT_LINE('TABLE 명 : ' || REC.OBJECT_NAME || ', ' || V_RESULT );
    
    V_STR := 'DELETE FROM ' || REC.OBJECT_NAME;
    EXECUTE IMMEDIATE V_STR;


END LOOP;
END;

-- STEP 5 프로시저 실행.
   EXEC TEST_AA('TEST_A');

-- STEP 6 테스트 데이터 삭제 확인.
   SELECT * FROM  TEST_A;

-- STEP 7 테스트 테이블 삭제    
   DROP TABLE TEST_A;

프로젝트 진행중에 테스트 데이터를 마이그레이션 하는 동안

시퀀스는 초기화 시켜주지 않아서 매번 수동으로 시퀀스 값을 설정했었다.

한 두번도 아니고 일일이 수정하기에는 너무 손이 많이 가는 작업이라서

시퀀스를 새롭게 들어온 데이터에 맞춰서 새로 설정 해주는 방법을 찾아서 기록에 남기고자 한다.

1.  먼저 시퀀스를 설정할 프로시저를 하나 생성하고 매개변수로 3가지를 받는다.

 1-1 시퀀스명 

 1-2 시퀀스 대상 테이블명

 1-3 시퀀스 대상 컬럼명 

원리는 이렇다. 

현재의 데이터를 기준으로 시퀀스 값을 읽어 들이고 

그에 맞춰서 시퀀스 값을 변경해준다. 

CREATE OR REPLACE PROCEDURE P_REBUILD_SP_SEQUANCE
(
  I_SEQ_NAME IN VARCHAR2 /* 시퀀스 이름 */
  ,I_TB_NAME IN VARCHAR2 /* 시퀀스 대상 테이블 */
  ,I_TB_KEY IN VARCHAR2  /* 시퀀스 대상 컬럼 */
)
IS
  I_SEQNO1 NUMBER;
  I_SEQNO2 NUMBER;

BEGIN 
  /* 시퀀스 증가값을 1로 설정 */

   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || I_SEQ_NAME || ' INCREMENT BY 1';  

   /* 현재 시퀀스 값 */   

   EXECUTE IMMEDIATE 'SELECT ' || I_SEQ_NAME || ' .NEXTVAL FROM DUAL' INTO I_SEQNO1;  

   /* 업무 테이블 시퀀스값 확인 */  

   EXECUTE IMMEDIATE 'SELECT NVL(MAX(TO_NUMBER('||I_TB_KEY||')),1) +10 FROM '||I_TB_NAME INTO I_SEQNO2;

   
   dbms_output.put_line(I_SEQNO2 - I_SEQNO1);

   /* 업무 테이블 시퀀스 값과 현재 시퀀스 설정값을 비교하여 시퀀스 증가값을 설정 */
   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || I_SEQ_NAME || ' INCREMENT BY ' || (I_SEQNO2 - I_SEQNO1);
   /* 시퀀스 실행 */

   EXECUTE IMMEDIATE 'SELECT  ' || I_SEQ_NAME || ' .NEXTVAL FROM DUAL' INTO I_SEQNO1;
   dbms_output.put_line(I_SEQNO1);

   /* 시퀀스 증가값을 다시 1로 변경 */
   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || I_SEQ_NAME || ' INCREMENT BY 1';
END;

이렇게 시퀀스를 새롭게 설정할 프로시저를  만들고

시퀀스를 가지고 있는 테이블 목록을 가지고 있는

프로시저를 하나 더 생성하였다.

CREATE OR REPLACE PROCEDURE P_RESET_SP_SEQUANCE
AS
BEGIN
/* 매개변수의 순서를 시퀀스명 , 테이블명 , 컬럼명 순임 */
P_REBUILD_SP_SEQUANCE('DOC_MASTER_SEQ','DOC_MASTER_INFO','DOC_ID'); /* 문서 정보 */ 
P_REBUILD_SP_SEQUANCE('DOC_ATTACH_FILE_SEQ','DOC_ATTACH_FILE_INFO','FILE_ID'); /* 파일 정보 */ 
P_REBUILD_SP_SEQUANCE('DOC_PRINT_HIST_SEQ','DOC_PRINT_HIST','PRINT_ID'); /* 인쇄 히스토리 정보 */ 
P_REBUILD_SP_SEQUANCE('DOC_PRINT_RSN_SEQ','DOC_PRINT_RSN','REQ_ID'); /* 인쇄 재요청 테이블 */ 
P_REBUILD_SP_SEQUANCE('DOC_STAMP_SEQ','DOC_STAMP_HIST','STAMP_ID'); /* 인쇄 시 직인 사용 히스토리 */

END; 

이렇게 해두면 새롭게 데이터마이그레이션이 진행되어도 

EXCUTE P_RESET_SP_SEQUANCE; 

SQL에서 한번의 명령어로 시퀀스값을 마이그레이션 데이터 기준으로 설정이 가능하다. 

/*

SELECT * FROM [dbo].[FN_SPLIT] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')

*/

CREATE FUNCTION [dbo].[FN_SPLIT]

(

    @PARAM VARCHAR(MAX) -- 대상문자열

    , @DIV VARCHAR(1) -- 특정구분자

)

RETURNS

@RESULT TABLE

(

    [VALUE] VARCHAR(MAX)

    , [ORDER] INT

)

AS

BEGIN

    DECLARE @INDEX INT

    DECLARE @POS INT

    DECLARE @ORDER INT

    DECLARE @TEMP VARCHAR(MAX)

    SET @INDEX = 1

    SET @POS = 1

    SET @ORDER = 1

    WHILE @POS > 0

    BEGIN

        SET @POS = CHARINDEX(@DIV, @PARAM, @INDEX)

        IF @POS = 0

        BEGIN

            SET @TEMP = RIGHT(@PARAM, LEN(@PARAM) - @INDEX + 1)

        END

        ELSE

        BEGIN

            SET @TEMP = SUBSTRING(@PARAM, @INDEX, @POS - @INDEX)

        END

        IF LEN(@TEMP) > 0

        BEGIN

            SET @TEMP = RTRIM(LTRIM(@TEMP))

            INSERT INTO @RESULT VALUES (@TEMP, @ORDER)

        END

       SET @INDEX = @POS + 1

       SET @ORDER = @ORDER + 1

    END

RETURN

END

--실행결과--

SELECT * FROM [dbo].[FN_SPLIT] ('감기,콧물,어지러움,콧물기침,열,가래,몸살',',')

출처: http://mdj1234.tistory.com/38 [짬타이거 화이팅!]

실제 작업시에는 대부분 unique index를 걸거나, primary key 를 걸어 놓아 그런 일이 벌어지지 않지만, 벌크로 데이터를 작업하거나 할 경우, 중복된 데이터를 보거나, 제거하고 싶을 때가 있습니다.

단순히 중복 카운트를 조회 하고 싶을 경우엔, 다음과 같이 grouping 하고 count를 세면 됩니다만,

-- 중복 카운트
SELECT some_id, COUNT(*)
  FROM tbl_some_table
 GROUP BY some_id
 HAVING COUNT(*) > 1;

-- 여러 컬럼의 중복 카운트
SELECT some_id, some_nm, some_description, COUNT(*)
  FROM tbl_some_table
 GROUP BY some_id, some_nm, some_description
 HAVING COUNT(*) > 1;

중복된 데이터 중, 첫번째(또는 마지막) 하나만 빼고 나머지를 조회한다거나, 삭제하고 싶을 때가 있습니다. 그러한 경우 다음과 같은 inline view 로 처리할 수 있습니다. (WITH 를 사용해도 되나, 하위 버전의 SQL 일 경우에는 WITH 구문이 먹지 않죠. 응? 하위 버전은 ROW_NUMBER 가 안 먹겠네요. 아이고 배야. - 하위 버전도 inline view 를 여럿 쓰면 되긴 합니다만.)

-- 중복 데이터를 알고 싶다.
SELECT *
  FROM (
          SELECT some_id
               , dup_idx = ROW_NUMBER() OVER (
                           PARTITION BY some_id ORDER BY some_id )
            FROM tbl_some_table (NOLOCK)
       ) tb_dup
 WHERE tb_dup.dup_idx > 1;

-- 중복 데이터 기준으로 중복된 데이터를 다 조회하고 싶다.
SELECT t.*
  FROM (
          SELECT some_id
               , dup_idx = ROW_NUMBER() OVER (
                           PARTITION BY some_id ORDER BY some_id )
            FROM tbl_some_table (NOLOCK)
       ) tb_dup INNER JOIN tbl_some_table t (NOLOCK)
                        ON t.some_id = tb_dup.some_id
 WHERE tb_dup.dup_idx > 1;

-- 중복 데이터를 지우고 싶다!!!
DELETE tb_dup
  FROM (
          SELECT some_id
               , dup_idx = ROW_NUMBER() OVER (
                           PARTITION BY some_id ORDER BY some_id )
            FROM tbl_some_table
       ) tb_dup
 WHERE tb_dup.dup_idx > 1;

-- 여러 컬럼일 경우? 중복 데이터를 지우고 싶다!!!
DELETE tb_dup
  FROM (
          SELECT some_id, some_nm, some_description
               , dup_idx = ROW_NUMBER() OVER (
                           PARTITION BY some_id, some_nm, some_description
                           ORDER BY some_id, some_nm, some_description )
            FROM tbl_some_table
       ) tb_dup
 WHERE tb_dup.dup_idx > 1;

이것 또한 그냥 메모입니다. 하하.

출처: http://withsoju.tistory.com/686 [읽든지 말든지]

//현재 날짜가 몇주차인지.
1.SELECT (DAY(convert(varchar(10),getdate(),120)) + (DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0,convert(varchar(10),getdate(),120)), 0)) -1)-1)/7 + 1

2.SELECT DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, convert(varchar(10),getdate(),120)), 0),convert(varchar(10),getdate(),120)) +1;

EX) 달력상 2010-09-13일은 두번째주가 아니라 세번째주로 갖고온다.

      특정 날짜형식 'YYYY-MM-DD' 을 넣고 싶으면 convert(varchar(10),getdate(),120) 이 부분만    값 다르게 넣으면 됨.

결과

3

참고로 오라클에서 2010-09-13 일을 TO_CHAR(SYSDATE,'W') 이런식으로 갖고오면 13일을 두번째 주로 인식함.

//현재 날짜가 무슨 요일인지

select datepart(dw,convert(varchar(10),getdate(),120))

결과

0:일요일 1:월요일....

select to_char(sysdate, 'day') from dual
--결과 : 화요일
;
select to_char(sysdate, 'dy') from dual
--결과 : 화
;
select to_char(sysdate, 'd') from dual
--결과 : 3 (1:일, 2:월, 3:화, 4:수, 5:목, 6:금, 7:토)
;

SELECT  (CASE 

           WHEN DATEPART(dw , regdate) = 1 THEN '일'

WHEN DATEPART(dw , regdate) = 2 THEN '월'

WHEN DATEPART(dw , regdate) = 3 THEN '화'

WHEN DATEPART(dw , regdate) = 4 THEN '수'

WHEN DATEPART(dw , regdate) = 5 THEN '목'

WHEN DATEPART(dw , regdate) = 6 THEN '금'

WHEN DATEPART(dw , regdate) = 7 THEN '토'

END ) AS Week

FROM (

SELECT '2017-06-18'  as regdate

)AA

DISTINCT 와 GROUP BY의 차이

IF NOT EXISTS (SELECT * 

                 FROM hsscm_ocp.dbo.mpi_workorder 

WHERE PLANT = '@@plant'

      AND MATERIAL = '@@material'

      AND INPUT_DATE = '@@inputDate'

      AND OUTPUT_DATE = '@@outputDate')

  INSERT INTO hsscm_ocp.dbo.mpi_workorder (

PLANT

,MATERIAL

,INPUT_DATE

,OUTPUT_DATE

,QTY

,OPERATION

,RESOURCE

,ORDER_NO

,ORDER_DIV

,REMARK

,CREATE_ID

,CREATE_DATE

)VALUES(

'@@plant'

,'@@material'

,'@@inputDate'

,'@@outputDate'

, @@qty

,'@@operation'

,'@@resource'

,'@@orderNo'

,'@@orderDiv'

,'@@remark'

,'@@createId'

,getdate()

);

ELSE 

   UPDATE hsscm_ocp.dbo.mpi_workorder

      SET REMARK = '';

MERGE hsscm_ocp.dbo.mpi_workorder T1

      USING (VALUES(1)) AS Source (Number)

 ON  T1.PLANT = '1140'

      AND T1.MATERIAL = '10000003'

      AND T1.INPUT_DATE = '2016-12-23'

      AND T1.OUTPUT_DATE = '2016-12-23'

   WHEN MATCHED THEN 

     UPDATE 

SET  T1.REMARK = ''

   WHEN NOT MATCHED THEN

     INSERT   (

PLANT

,MATERIAL

,INPUT_DATE

,OUTPUT_DATE

,QTY

,OPERATION

,RESOURCE

,ORDER_NO

,ORDER_DIV

,REMARK

,CREATE_ID

,CREATE_DATE

)VALUES(

'1140'

,'10000003'

,'2016-12-23'

,'2016-12-23'

,'' 

,''

,'S311'

,'000010044747'

,'S'

,''

,'sysadmin'

,getdate()

);