데이터베이스 질문과 답변

오라클 임시테이블? 0 12 5,106

by 농sql [Oracle 기초] 오라클 임시테이블 [2019.05.15 14:36:57]

MS - SQL 에서 사용하는 변수테이블 처럼 오라클에서도 비슷하게 사용할 수 있는 임시테이블 같은걸 사용하고 싶은데

동시적으로 테이블을 INSERT UPDATE DELETE 시 영향 안받게 독립적으로 사용 할 수 있었으면 좋겠어요~

함수안에서 사용하려고 하는데 괜찮은게 있을까요?

 

?
1
2
3
4
5
6
7
DECLARE @NA_TABLE TABLE ( AAA VARCHAR(200), BBB INT, CCC INT, DDD VARCHAR(100));
 
INSERT INTO @NA_TABLE (AAA) VALUES ('홍길동', 100, 200, '승리');
 
UPDATE @NA_TABLE
SET AAA = '고길동'
;

 

by 임상준 [2019.05.15 14:46:19]

함수(function) 안에서는 일반적으로 dml 이 되지 않습니다.

global temporary table 이라는 실제 물리적인 임시 테이블도 있고 pl/sql 안에서 사용 가능한 type 이나 array 같은 개념도 있습니다만, 내용을 정확히 알지 못해서 어떤게 적합한지는 판단 하셔야 할 것 같습니다.

 

by 농sql [2019.05.15 14:49:30]

MS SQL SPLIT 함수가 안에 변수 테이블로 되어 있는 것을 오라클에 똑같이 적용하려고 하다보니.. 문제가 되어서 ..

by 임상준 [2019.05.15 15:01:22]

split 함수가 뭔지 모르겠네요,,,

그냥 문자열 자르는 기능 함수이면 타입 같은걸 쓰는게 나아보입니다.

by 농sql [2019.05.15 15:05:23]

TYPE 사용법을 봐도 잘 몰라서 사용을 못했습니다..ㅠ

TYPE이용해서 문자열 자르기 쿼리좀 알 수 있을까요?

by 마농 [2019.05.15 15:07:15]

오라클 임시테이블은 MSSQL 과 다르게 함수 안에서 선언하는게 아니라
미리 만들어 두고 일반 테이블처럼 사용하기만 하면 됩니다.
입력된 자료는 커밋시 클리어 됩니다.
http://wiki.gurubee.net/pages/viewpage.action?pageId=28117339

by 농sql [2019.05.15 15:22:13]

TABLE 함수안에 INSERT UPDATE 등 넣다보면 

PRAGMA AUTONOMOUS_TRANSACTION;

넣어야되고 저걸 넣으면

자꾸 롤백된다고 떠서 COMMIT을 넣으면 임시테이블이 초기화되서 RETURN값을 넘겨주지도 못하고 그러는데

어떻게 사용해야되나요?

by 마농 [2019.05.15 15:31:18]

링크에 보면 트렌젝션 기반과 세션 기반 두가지 임시테이블이 있습니다.
커밋해도 자료 유지하려면 세션기반으로 사용하시면 될 듯 하고요.
테이블 형태로 리턴을 해야 하는거라면?
임시테이블이 아닌 커서를 사용해야 하지 않을까? 생각되기도 하구요.
원본 MSSQL 소스를 보여주세요.

by 농sql [2019.05.15 15:40:52]
?
1
쿼리 올립니다.

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE FUNCTION [dbo].[FN_GET_SPLIT_TABLE_STR]
(
        @vList         VARCHAR(1000)
    ,   @vDelim        VARCHAR(2)
)
RETURNS @Arrary TABLE (IdxNo int identity, Value varchar(1000), PRIMARY KEY (IdxNo))
BEGIN
    DECLARE    @PStart    INT,
        @PEnd    int,
        @LenDelim    tinyint,
        @Exit        tinyint,
        @vStr        varchar(1000)
 
    SET @PStart = 1
    SET @PEnd = 1
    SET @LenDelim = LEN(@vDelim)
 
    SET @Exit = 0
    IF @vList IS NOT NULL AND @vList <> ''
    BEGIN
 
      WHILE @Exit = 0
      BEGIN
         SET @PEnd = CHARINDEX(@vDelim, @vList, @PStart)
 
         IF @PEnd <= 0
         BEGIN
               SET @PEnd = LEN(@vList) + 1
               SET @Exit = 1
         END
 
         SET @vStr = LTRIM(RTRIM(SUBSTRING(@vList, @PStart, @PEnd - @PStart)))
 
         IF @vStr <> 'NULL'
            INSERT INTO @Arrary (Value) VALUES (@vStr)
         ELSE
            INSERT INTO @Arrary (Value) VALUES (NULL)
 
         SET @PStart = @PEnd + @LenDelim
      END
    END
    ELSE
    BEGIN
       INSERT INTO @Arrary (Value) VALUES (NULL)
    END
 
    RETURN
END
by 마농 [2019.05.15 15:58:14]

함수나 임시테이블 없이 단일 쿼리로 작성해 봤습니다.

?
1
2
3
4
5
6
7
8
SELECT SUBSTR(vList
       , INSTR(vDelim||vList, vDelim, 1, LEVEL)
       , INSTR(vList||vDelim, vDelim, 1, LEVEL)
       - INSTR(vDelim||vList, vDelim, 1, LEVEL)
       ) x
  FROM (SELECT 'aaa,bb,c' vList, ',' vDelim FROM dual)
 CONNECT BY LEVEL <= LENGTH(vList) - LENGTH(REPLACE(vList, vDelim)) + 1
;

 

by 농sql [2019.05.15 16:08:36]

감사합니다!

하지만.. UI단에서 함수 호출로 진행해야되서 함수로 만들어야 됩니다..ㅠㅠ

by 마농 [2019.05.15 16:27:13]

글쎄요? UI 에서 어떻게 사용하는지 몰라서...
원하시는게 다음 링크가 아닐까? 생각되긴 하지만 확신은 없네요.
function에서 muti_row를 리턴받자...

by 이준환 [2019.05.15 16:40:38]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- GTT 생성
CREATE GLOBAL TEMPORARY TABLE SYSDBA.TMP1
( IN_NO NUMBER(5)
, OUT_NO NUMBER(5)
, DT TIMESTAMP DEFAULT SYSTIMESTAMP
, ERR_TXT VARCHAR2(1000))
ON COMMIT PRESERVE ROWS -- transaction only
--ON COMMIT DELETE ROWS -- session only
;
 
-- GTT 확인
SELECT TEMPORARY, DURATION
FROM DBA_TABLES
WHERE OWNER = 'SYSDBA'
AND TABLE_NAME = 'TMP1';
 
-- 함수 생성
CREATE OR REPLACE FUNCTION SYSDBA.F1(NO IN NUMBER)
RETURN NUMBER
IS
V_OUT_NO NUMBER(10) DEFAULT 0;
V_ERR_TXT VARCHAR2(1000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    BEGIN
        V_OUT_NO := NO*1E3;
        INSERT INTO SYSDBA.TMP1
        ( IN_NO
        , OUT_NO)
        VALUES
        ( NO
        , V_OUT_NO)
        ;
        EXCEPTION WHEN OTHERS THEN
        V_ERR_TXT := 'IN_NO['||NO||'] ERROR['||SQLCODE||SQLERRM||']';
        GOTO ERROR_RETURN;
    END;
 
    COMMIT;
 
    RETURN NO*1E3;
 
    <<ERROR_RETURN>>
    BEGIN
        INSERT INTO SYSDBA.TMP1 (ERR_TXT)
        VALUES (V_ERR_TXT)
        ;
        EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
    END;
 
    COMMIT;
     
    RETURN -99999999;
END;
/
 
SELECT SYSDBA.F1(1) FROM DUAL;
 
SELECT * FROM SYSDBA.TMP1;
 
SELECT SYSDBA.F1(123456789012) FROM DUAL;
 
SELECT * FROM SYSDBA.TMP1;

오라클 함수내에서 GTT에 대한 DML 수행 테스트한 내역 공유드려 봅니다..

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입