이전 회사에 한 후배가 With 문을 쓰는걸 보고 VB.NET의 기능이 오라클에도 있구나 생각했었는데 배워두면 좋을 것 같단 생각에 공부삼에 한번 시도해 보았다.

WITH TOTAL AS (

  SELECT *

    FROM TABLES

   WHERE OPER = '1000' AND WORK_DATE BETWEEN '20151201' AND '20151231' 

)

SELECT * FROM TOTAL;

간단해 보이면서도 복잡하게 느껴지는 이유는 뭘까?

복잡한 서브쿼리에서는 도움이 좀 될려나?

대용량 테이블을 새로 만들경우나 백업할 경우에 유용하게 쓰일 구문

CREATE TABLE EMP_1

UNRECOVERABLE

AS

SELECT * FROM EMP3;

롤백 세그먼트 없이 처리 하는 구문이란다.

참고 : http://blog.naver.com/speedsky98

오라클 계층형 쿼리

이전에 몇번 사용해 본거 같은데 오늘 조직도 관련하여 쓸일이 있어서 기술해 본다.

계층 관계가 있는 칼럼(외래키로 자기자신 테이블을 참조)이 있는 경우 계층 구조를 이용하여 데이터를 추출

select 칼럼 from 테이블 start with 조건 connect by [prior] [nocycle]

형식은 위 쿼리형태이다.

CREATE TABLE JOJIK (

    GROUP_CD VARCHAR2(20)

  , DEPT_CD VARCHAR2(20)

  , DEPT_NM VARCHAR2(50)

  , SUB_DEPT_CD VARCHAR2(20)

);

이렇게 테이블을 구성한뒤

SELECT LEVEL

     , GROUP_CD

     , LPAD(' ',(LEVEL-1)*2,' ')||DEPT_NM

     , DEPT_CD

  FROM JOJIK

 START WITH SUB_DEPT_CD IS NULL

       CONNECT BY PRIOR DEPT_CD = SUB_DEPT_CD

 ORDER BY GROUP_CD, ORDER_SEQ;

이렇게 쿼리를 구성하면 멋지게 다음과 같이 결과가 나온다.

CREATE TRIGGER [dbo].[TRG_AAA] on [dbo].[AAA]

AFTER INSERT

AS
BEGIN
   SET NOCOUNT ON
  
   DECLARE @V_A VARCHAR(10)

   SELECT @V_A = A FROM inserted
  
   IF (@V_A = 'A')
   BEGIN
      INSERT INTO BBB
      SELECT A, B FROM inserted
   END
  
  
   --IF @@RowCount > 0
   --BEGIN
   --   INSERT INTO BBB
   --   SELECT A, B FROM inserted
   --END
  
END

---------------------------------------------

INSERT INTO AAA (A, B) VALUES ('A', 0)

SELECT * FROM AAA
SELECT * FROM BBB

MSSQL 트리거도 오라클과 똑같을 줄 알았고 별 생각없이 인터넷상에 도는 트리거 예제를 긁어다가 적용했는데

왠걸 오늘 큰건 하나 저질렀다.

내용인 즉슨 바로 'AFTER INSERT' 이 옵션이었는데 오라클에서는 주로 내가 쓰던건 INSERTED(?) 였다. INSERT가 되고난 이후 처리되는 뭐 이 정도로 이해를 했다.

오늘 저지른 실수의 옵션은 바로 'INSTEAD OF INSERT' 이미 늦었지만 눈에 들어오기 시작한것은 INSTEAD OF... INSTEAD OF... INSTEAD OF...

대신에... 대신에... 대신에... INSERT 대신에... OTL

INSERT되지 않고 설정한 내용으로 처리가 되다보니 정작 본래 테이블에는 데이터를 하나도 안들어가는 불상사가 발생... 그것도 운영 DB로...

뭐 좋은 경험으로 삼고 다음에 조심해 나가면 되겠지...

SP_RENAME '기존 테이블명', '바뀔 테이블명'

SP_RENAME '테이블명.기존컬럼명', '바뀔 컬럼명'

Management Studio에서 테이블 구조를 변경할때 기본적으로 저장이 반영되지 않음

이럴땐 다음과 같은 옵션 설정을 해 주어야함

이상 끝

FETCH처리를 하니 역시 속도는 떨어지는거 같다.

DROP PROC UP_TEST_001

GO

CREATE PROC UP_TEST_001

(

   @TEST VARCHAR(99)

)

AS

DECLARE @I_COUNT     INT = 0

BEGIN

    DECLARE @KEY_MAT_ID VARCHAR(90)

          , @MAT_ID     VARCHAR(90)

          , @QTY        INT

    DECLARE BOM_CURSOR CURSOR FOR

SELECT KEY_MAT_ID, MAT_ID, QTY 

 FROM MES_BOM_DEF 

WHERE KEY_MAT_ID = 'XXX'

    OPEN BOM_CURSOR

FETCH NEXT FROM BOM_CURSOR

INTO @KEY_MAT_ID, @MAT_ID, @QTY

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @KEY_MAT_ID, @MAT_ID, @QTY

FETCH NEXT FROM BOM_CURSOR

INTO @KEY_MAT_ID, @MAT_ID, @QTY

END

    CLOSE BOM_CURSOR

    DEALLOCATE BOM_CURSOR

END

EXEC   sp_addextendedproperty 'MS_Description', '테이블설명', 'user', dbo, 'table',테이블명
//컬럼들
EXEC   sp_addextendedproperty 'MS_Description', '컬럼설명', 'user', dbo, 'table', 테이블명, 'column', 컬럼명

2. 주석수정 (update)

//테이블

EXEC   sp_updateextendedproperty 'MS_Description', '테이블설명', 'user', dbo, 'table',테이블명

//컬럼들

EXEC   sp_updateextendedproperty 'MS_Description', '컬럼설명', 'user', dbo, 'table', 테이블명, 'column', 컬럼명

-- 아래는 응용 : 프로시저를 만들어 아래와 같이 이용하면 도움이 될 듯!

-- EXEC UP_SET_TABLE_COMMENT 'I', 'MES_EQUIP_DEF', 'LINE_CD',   '라인코드'

-- EXEC UP_SET_TABLE_COMMENT 'I', 'MES_EQUIP_DEF', 'OPER_CD', '공정코드'

...

/******************************************************************************************************* 

    1. Procedure    : C_SP_SET_BPA_PLC_READY

    2. Process Func : 테이블 설명 추가(테이블, 컬럼)

    3. Create Date  : 2013-11-28 오전 11:24:32

    4. Create User  : YHKIM

    5. Execute Test : EXEC UP_SET_TABLE_COMMENT 'I', 'MES_EQUIP_DEF', 'FACTORY', '공장코드'

    6. History Info :

*******************************************************************************************************/ 

ALTER PROC  [dbo].[UP_SET_TABLE_COMMENT]

(

   @I_TYPE        VARCHAR(01),   -- I:추가, U:수정

   @S_TABLE       VARCHAR(50),

   @S_COLUMN      VARCHAR(50),

   @S_COMMENT     VARCHAR(1000)

)

AS

BEGIN

   IF @I_TYPE = 'I'

   BEGIN

      IF @S_COLUMN = ''

         -- TABLE 설명

         EXEC   sp_addextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE

      ELSE

         -- 컬럼 설명

         EXEC   sp_addextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE, 'column', @S_COLUMN

   END

   IF @I_TYPE = 'U'

   BEGIN

      IF @S_COLUMN = ''

         -- TABLE 설명

         EXEC   sp_updateextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE

      ELSE

         -- 컬럼 설명

         EXEC   sp_updateextendedproperty 'MS_Description', @S_COMMENT, 'user', dbo, 'table', @S_TABLE, 'column', @S_COLUMN

   END

END

프로시저 파라미터 값을 던질때 'AAA^BBB^CCC^DDD^' 이렇게 던지면 프로시저 내부에서 LOOP처리 하며 일일이

계산하여 처리했는데 그 해결책을 찾은 듯 하다!

--------------------------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[SPLIT_STR](@S_VAL NVARCHAR(4000), @S_SPLIT_CHAR NVARCHAR(1))

RETURNS @T_SPLIT TABLE(NUM INT, VAL NVARCHAR(4000))

AS

-- SELECT * FROM DBO.SPLIT_STR('AB|BC|CD', '|')

BEGIN

   DECLARE @oPos INT, @nPos INT, @i INT

   DECLARE @tempVar NVARCHAR(4000)

   SET @oPos = 1

   SET @nPos = 1

   SET @i    = 0

   WHILE(@nPos > 0)

   BEGIN

      SET @nPos = CHARINDEX(@S_SPLIT_CHAR, @S_VAL, @oPos)

      IF @nPos = 0

         SET @tempVar = RIGHT(@S_VAL, LEN(@S_VAL) - @oPos + 1)

      ELSE

         SET @tempVar = SUBSTRING(@S_VAL, @oPos, @nPos - @oPos)

      IF LEN(@tempVar) > 0

         INSERT INTO @T_SPLIT VALUES (@i, @tempVar)

      SET @oPos = @nPos + 1

      SET @i = @i + 1

   END

   RETURN

END

[참조] http://cong4u.tistory.com/157

PC를 포맷하고 프로그램을 이것저것 깔다보면 이전에 편리하던 기능이 그리울때가 많다.

설치할때 초반에서 설정하다 보니 나중에 시간이 지나면 까먹는 기능인데 대표적으로 사용하는 기능이 바로 자동 인사이트시 대문자로 표시하는 것이다.

토드가 아무리 좋아도 Sqldeveloper의 자동인사이트 기능만하지 못하다.

오라클 관리툴은 Toad 오라클 개발툴은 Sqldeveloper!!!