데이터베이스 질문과 답변
테이블이 2개로 나누어져 있습니다.
테이블1 : 사원정보 테이블
테이블2 : 사원가족 테이블
테이블3 : 테이블1,테이블2 조인한 결과를 재저장
프로시저를 이용하고 싶음
테이블1
================================================
사원번호 | 성명 | 주민번호 | ...
================================================
20090201 홍길동 700102-1111111
20090301 이길순 800102-2111111
테이블2
================================================
사원번호 | 가족순번 | 성 명 | 주민번호 | ...
================================================
20090201 01 홍만득 000101-3111111
20090201 02 홍만호 030101-3111112
20090201 03 홍아호 030401-3111113
20090301 01 이수아 040101-4111111
테이블3
================================================
사원번호 | 성 명 | 주민번호 | ...
================================================
20090201 홍만득 000101-3111111
20090201 홍만호 030101-3111112
20090201 홍아호 030401-3111113
이런식으로 구성되어 있습니다.
기본적인 테이블이라고 할 수 있죠.
문제는 프로시저로 테이블1과 테이블2를 조인해서 새로운 테이블3에
INSERT 하려고 합니다.
그냥 평범하게 INSERT문과 SELECT을 이용해서 테이블1,테이블2를 조인해서
입력을 할 수 있겠지만 프로시저를 통해서 작업을 하려고 합니다.
프로시저에 대해서 한참 초보입니다.
간단히 SELECT 해서 1 ROW 결과가 나오는 값을 다시 다른 테이블로
INSERT 하는 것은 가능하지만, SELECT 했을때 여러 ROW가 나오면 어떻게 해야 할지 잘
모르겠습니다.
예를 들어 테이블2 같은 경우에 사원번호로 SELECT 했을때 2개 이상의 ROW가
결과값으로 나오면 어떻게 해야 할지 모르겠습니다.
대략 테이블2가 사원번호당 1ROW 의 결과가 나온다면 아래와 같이 하면 될 것 같은데요.
------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE TEST_PRC
(
v_company IN VARCHAR2,
v_result OUT VARCHAR2
) IS
CURSOR TEST_LOOPX IS
-- 테이블1에서 전체 사원번호 가져오기
SELECT
COMPANY,
EMP_NO
FROM 테이블1
WHERE COMPANY = v_company;
BEGIN
FOR R_LIST IN TEST_LOOPX LOOP
v_company VARCHAR2(2) := v_company;
v_emp_no VARCHAR2(10) := R_LIST.EMP_NO;
v_name VARCHAR2(30) := '';
v_jumin_no VARCHAR2(13) := '';
-- 테이블2에서 원하는 정보 가져와서 변수에 저장
SELECT
NAME,
JUMIN_NO
INTO v_name,
v_jumin_no
FROM 테이블2
WHERE COMPANY = v_company
AND EMP_NO = R_LIST.EMP_NO;
-- 테이블3에 INSERT 하기
INSERT INTO 테이블3(
EMP_NO,
NAME,
JUMIN_NO
) VALUES (
v_emp_no,
v_name,
v_jumin_no
);
END LOOP;
-- 중략 --
-----------------------------------------------------------------------------------------------------------------------
위의 프로시저에서는 테이블1,테이블2가 조인이 안 되어 SELECT 를 했지만,
SELECT 에서 잡다한 것을 추가로 더 가져오신다고 가정하시면 됩니다.
새로운 테이블3는 테이블2의 일부 컬럼 내용과 테이블1의 내용 일부 등이
조인되어서 들어갑니다.
프로시저를 이용해서 테이블2(여러 ROW 나옴)의 일부 컬럼 내용을 새로운 테이블3에
INSERT 하는 방법을 좀 알려주세요.
Oracle 강좌 > PL/SQL 강좌
쭈욱 읽어보세요.
6.2.1. 명시적 커서(EXPLICIT CURSOR)
6.2.2. FOR문에서 커서 사용(Cursor FOR Loops)
강좌를 질문하기도 전에도 계속 봤는데 잘 몰라서 게시판에 올렸거든요.
오라클도 초보고, 프로시저는 더 몰라서요.
아무튼 답변 감사합니다^^
저 같은 경우는 LOOP를 2번 돌아야 하는데 모르겠습니다.
저는 전체 사원번호를 기본 루프로 잡고, 사원번호로 루프를 돕니다.
문제는 사원번호는 가지고 오지만, 테이블2에 사원번호에 딸린 가족들을 다 가지고 와야 합니다. 단순히 테이블1,테이블2를 조인하는게 아니라 3개이상 더 조인하고 값을 가지고 와야 해서 LOOP를 2중으로 돌면 될것 같은데 좀 머리를 써 봐도 잘 안 되네요.
도움을 부탁드립니다.
첫번째 방법. 커서를 선언할때 인자를 갖는 커서를 선언합니다.
CURSOR cur_family(inEmp_no VARCHAR2)
IS
SELECT ... WHERE emp_no = inEmp_no;
이를 For문에서 호출하시면 됩니다.
FOR c2 IN cur_family(c1.emp_no)
두번째 방법. 커서를 만들지 않고 For문에서 바로 쿼리를 사용합니다.
FOR c2 IN (SELECT ... WHERE emp_no = c1.emp_no)
일단 조인해서 한번의 루프로 끝내는 것이 다중 루프보다 더 좋구요.
아예 루프를 안돌리고 바로 insert.. select 하는 것이 더 좋습니다.
쉽게 말해서 테이블 조인보다 테이블1이 주축이 되어서, 사원번호가 PK로 테이블2의 가족 정보를 가지고 와야 합니다.
즉, 가족이 대부분 1명이상이기 때문에 값이 1ROW만 나오는게 아니고, 2개 이상의 ROW가 발생해서 제가 난감해 하고 있습니다.
제가 이해를 잘못하고 있는지 모르지만, 테이블 2~3개를 조인해서 SELECT 한 컬럼들의 값을 변수로 임시 저장시키고, 그 변수들을 다시 테이블3에 INSERT 하려고 하는 것이 저의 목적입니다.
FOR(테이블1.사번)
{
FOR(테이블2.가족주민번호+테이블4.기타정보들...)
{
테이블2.가족주민번호-개별
테이블4.기타 정보들...
}
}
대략 이런 식입니다.
저도 다중 FOR 문이나 LOOP를 안 돌고 싶은데
우선 SQL 실력도 없고 좀 복잡해서 한큐에는 못 할 것 같아서
우선은 그렇게 되었습니다.
주목적은 테이블3에 조인된 결과 컬럼값들(사번에 따라 여러ROW가 한꺼번에 나옴)을 INSERT 하는 것입니다.
TOAD에서 간단히 실행을 하니까 이런 메세지도 나오구요.
ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다에러 발생
ROW의 값들을 배열값으로 처리를 해야 하는 것이 아닌가 싶기도 합니다.
도와주세요^^ 어제 밤부터 계속 헤매고 있습니다.
어차피 인서트 대상 행도 여러행입니다.
FOR cur_1 IN (SELECT * FROM emp)
LOOP -- 사원정보
FOR cur_2 IN (SELECT * FROM family WHERE emp_no = cur_1.empno)
LOOP -- 가족정보
END LOOP; -- 가족정보
END LOOP; -- 사원정보
SELECT 사원.사원번호, 가족.성명, 가족.주민번호
FROM 사원, 가족
WHERE 사원.사원번호 = 가족.사원번호
) 버튼을 클릭하여 작성 하시면 됩니다.