데이터베이스 질문과 답변
안녕하세요 오라클에서 함수를 만들고 있는데 막히는 부분이 있어서
조언 좀 구하고자 글 올립니다.
declare @temp table
(
org_id varchar(20),
used int
)
insert into @temp
select org_id,0
from organization_info
where org_id = @c_org_id
and org_use = 0
MS-SQL 에서는 위에 코드처럼 임시테이블 하나 선언하고
임시테이블에 데이터 INSERT, UPDATE 마음대로 할수 있었는데..
오라클은 함수 안에서 DML도 사용이 불가능한거 같고...
임시테이블 개념도 없는건지 테이블을 사용할려면 함수 안에서 선언해서 쓰는게 아닌
먼저 선언해놓고 써야되는거 같더라구요...
CREATE OR REPLACE TYPE TYPE_TEMP_AUTH AS OBJECT
(
ORG_ID VARCHAR2(20 CHAR),
USED NUMBER
)
CREATE OR REPLACE TYPE TABLE_TEMP_AUTH AS TABLE OF TYPE_TEMP_AUTH
그래서 위와 같이 선언 해놓고
SELECT * FROM TABLE_TEMP_AUTH
함수에서 이렇게 조회 해보면 오류가 발생합니다...
오류 메세지는
PL/SQL : ORA-04004 : 프로시저, 함수, 패키지 또는 유형이 이곳에서 허용되지 않습니다.
PL/SQL : ORA-00942 : 테이블 또는 뷰가 존재하지 않습니다.
이렇게 오류가 뜨네요...
요점
선언해놓은 테이블을 함수 안에서 어떻게 해야 사용할수 있는지 궁금합니다.
테이블에 데이터 (INSERT, UPDATE) 해놨다가
함수 마지막에 RETURN해줄때 JOIN 걸어서 사용하고 싶습니다.
기존 질문 지우셨네요. 답변 다 완성했는데...
임시테이블 사용도 가능하지만.
임시 테이블 없이 쿼리만으로도 결과 추출 가능합니다.
MSSQL 에서도 마찬가지입니다. 한방 쿼리로 가능합니다.
재귀 쿼리 사용으로 원하는 결과 추출 가능합니다.(이건 이전 질문에 대한 답변)
이번 변경된 질문에 대한 답변
임시 테이블은 미리 만들어 두고 사용하셔야 하구요.
SELECT 문은 단독으로 사용하는게 아니라
단건인 경우 INTO 절과 함께 사용해야 하고
다건인 경우 커서 형태로 사용해야 합니다.
http://gurubee.net/oracle/plsql
INSERT 하려면 커서 없이 그냥 INSERT ~ SELECT 형태로도 가능합니다.
http://gurubee.net/lecture/1016
이전 질문에 대한 완성 쿼리 올립니다.
WITH tmp(org_id, org_upper_id, org_use, sub_org) AS
(
SELECT b.org_id, b.org_upper_id, b.org_use, a.sub_org
FROM org_auth_info a
INNER JOIN organization_info b
ON a.org_id = b.org_id
WHERE a.login_id = :v_user_id
AND b.org_use = 0
UNION ALL
SELECT c.org_id, c.org_upper_id, c.org_use, p.sub_org
FROM tmp p
INNER JOIN organization_info c
ON p.org_id = c.org_upper_id
WHERE p.sub_org = 1
AND c.org_use = 0
)
SELECT DISTINCT
b.org_type
, a.org_id
, b.org_upper_id
, b.org_name
, b.org_full_name
, b.org_use
, b.org_sort
, b.sort
, b.lvl
FROM tmp a
INNER JOIN v_organization b
ON a.org_id = b.org_id
;
안녕하세요~ 답변 감사드립니다.
게시글 다시 확인하니 너무 두서없이 적을거 같아서
제가 필요한 부분 다시 정리해서 글올리다가 삭제하게 됐네요... 죄송합니다.
댓글내용과 정리해주신 쿼리가 많은 도움이 됐습니다.
감사드립니다.
추가로
while exists(select 1 from temp_table where used = 0)
SQL에서 썼던 방법인데
반복문 돌때마다 SELECT해서 ROW가 존재하면 돌고 존재하지 않으면 빠져나오도록
처리하고 싶은데.. 오라클에는 EXISTS가 WHILE문에는 사용이 안되는거 같네요...
오라클에서 구현할수 있는 방법이 있을까요?
그리고 함수 안에서 임시테이블 사용시 COMMIT은 언제 해주면 될까요?..
INSERT, UPDATE 할때마다 COMMIT 해줘야될까요?..
임시테이블 설정은 COMMIT PRESERVE ROWS 로 설정해놓은 상태입니다..
WHILE 문 안에서 별도 Select into 구문으로 카운트를 변수에 담아 IF 문 처리하시면 될 듯.
임시테이블의 데이터는 해당 세션에서만 유효하고, COMMIT 하면 데이터가 날아갑니다.
감사합니다!!! 도움주셔서 개념 탑재가 많이 됐습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.