데이터베이스 질문과 답변
MS - SQL 에서 사용하는 변수테이블 처럼 오라클에서도 비슷하게 사용할 수 있는 임시테이블 같은걸 사용하고 싶은데
동시적으로 테이블을 INSERT UPDATE DELETE 시 영향 안받게 독립적으로 사용 할 수 있었으면 좋겠어요~
함수안에서 사용하려고 하는데 괜찮은게 있을까요?
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 = '고길동'
;
함수(function) 안에서는 일반적으로 dml 이 되지 않습니다.
global temporary table 이라는 실제 물리적인 임시 테이블도 있고 pl/sql 안에서 사용 가능한 type 이나 array 같은 개념도 있습니다만, 내용을 정확히 알지 못해서 어떤게 적합한지는 판단 하셔야 할 것 같습니다.
MS SQL SPLIT 함수가 안에 변수 테이블로 되어 있는 것을 오라클에 똑같이 적용하려고 하다보니.. 문제가 되어서 ..
split 함수가 뭔지 모르겠네요,,,
그냥 문자열 자르는 기능 함수이면 타입 같은걸 쓰는게 나아보입니다.
TYPE 사용법을 봐도 잘 몰라서 사용을 못했습니다..ㅠ
TYPE이용해서 문자열 자르기 쿼리좀 알 수 있을까요?
오라클 임시테이블은 MSSQL 과 다르게 함수 안에서 선언하는게 아니라
미리 만들어 두고 일반 테이블처럼 사용하기만 하면 됩니다.
입력된 자료는 커밋시 클리어 됩니다.
http://wiki.gurubee.net/pages/viewpage.action?pageId=28117339
TABLE 함수안에 INSERT UPDATE 등 넣다보면
PRAGMA AUTONOMOUS_TRANSACTION;
넣어야되고 저걸 넣으면
자꾸 롤백된다고 떠서 COMMIT을 넣으면 임시테이블이 초기화되서 RETURN값을 넘겨주지도 못하고 그러는데
어떻게 사용해야되나요?
링크에 보면 트렌젝션 기반과 세션 기반 두가지 임시테이블이 있습니다.
커밋해도 자료 유지하려면 세션기반으로 사용하시면 될 듯 하고요.
테이블 형태로 리턴을 해야 하는거라면?
임시테이블이 아닌 커서를 사용해야 하지 않을까? 생각되기도 하구요.
원본 MSSQL 소스를 보여주세요.
쿼리 올립니다.
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
함수나 임시테이블 없이 단일 쿼리로 작성해 봤습니다.
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
;
감사합니다!
하지만.. UI단에서 함수 호출로 진행해야되서 함수로 만들어야 됩니다..ㅠㅠ
글쎄요? UI 에서 어떻게 사용하는지 몰라서...
원하시는게 다음 링크가 아닐까? 생각되긴 하지만 확신은 없네요.
function에서 muti_row를 리턴받자...
-- 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 수행 테스트한 내역 공유드려 봅니다..
) 버튼을 클릭하여 작성 하시면 됩니다.