데이터베이스 질문과 답변
by 춤과파티 [PL/SQL] [2018.03.08 09:03:23]
1.테이블 생성
CREATE TABLE PLANT_CHANGE_TABLE /*바뀌는 엑셀 데이터 테이블*/
(
NEW_COMPANY_CODE VARCHAR2(10);
NEW_PLANT_CODE VARCHAR2(10);
OLD_PLANT_CODE VARCHAR2(10);
);
2.프로시저 생성
create or replace PROCEDURE PLANT_CHANGE_PROCEDURE
(
TABLE_NAME VARCHAR2(50)
)
IS
CURSOR PLANT_CHANGE_CURSOR IS
SELECT NEW_COMPANY_CODE, NEW_PLANT_CODE, OLD_PLANT
FROM PLANT_CHANGE_TABLE;
AAA PLANT_CHANGE_TABLE.NEW_COMPANY_CODE%TYPE;
BBB PLANT_CHANGE_TABLE.NEW_PLANT_CODE%TYPE;
CCC PLANT_CHANGE_TABLE.OLD_PLANT_CODE%TYPE;
BEGIN
OPEN PLANT_CHANGE_CURSOR;
LOOP
FETCH PLANT_CHANGE_CURSOR INTO AAA, BBB, CCC;
EXIT WHEN PLANT_CHANGE_CURSOR%NOTFOUND;
INSERT INTO TABLE_NAME /*수정 할 테이블명*/
(
HOUSE_CODE, COMPANY_CODE, OPERATING_CODE, PLANT_CODE, PLANT_NAME_LOC, PLANT_NAME_ENG, STATUS, ADD_DATE, ADD_TIME, ADD_USER_ID, ADD_USER_NAME_LOC, ADD_USER_NAME_ENG, ADD_USER_DEPT, CHANGE_DATE, CHANGE_TIME, CHANGE_USER_ID, CHANGE_USER_NAME_LOC, CHANGE_USER_NAME_ENG, CHANGE_USER_DEPT, MAIN_PLANT_FLAG, OPERATING_TYPE
)
select
HOUSE_CODE, AAA, OPERATING_CODE, BBB, 'NEW_'||PLANT_NAME_LOC, 'NEW_'||PLANT_NAME_ENG, STATUS, ADD_DATE, ADD_TIME, ADD_USER_ID, ADD_USER_NAME_LOC, ADD_USER_NAME_ENG, ADD_USER_DEPT, CHANGE_DATE, CHANGE_TIME, CHANGE_USER_ID, CHANGE_USER_NAME_LOC, CHANGE_USER_NAME_ENG, CHANGE_USER_DEPT, MAIN_PLANT_FLAG, OPERATING_TYPE
FROM TABLE_NAME /*수정 할 테이블명*/
WHERE HOUSE_CODE = '100'
AND COMPANY_CODE = 'H100' AND PLANT_CODE = CCC and operating_code in ('1100','1101');
COMMIT;
END LOOP;
CLOSE PLANT_CHANGE_CURSOR;
END PLANT_CHANGE_PROCEDURE;
3.프로시저 실행
EXECUTE PLANT_CHANGE_PROCEDURE(TABLE_NAME);
1번 테이블을 생성한후 2번 처럼 프로시저를 생성하는데 첫번째 행인
create or replace PROCEDURE PLANT_CHANGE_PROCEDURE 에서
ORA-00911: 문자가 부적합합니다 에러가 발생합니다.
ORACLE 11G에서 작업했구요. 첫번째 행은 문제가 없는듯 하니 프로시저 문의 문법 중 한곳이 잘못된 걸까요? 콤마나 세미콜론 등은 확인해봤는데 이상 없었습니다.
무엇이 문제일까요,,,, 고민끝에 질문드립니다.
참고 URL 감사합니다 덕분에 고쳐야 할 부분 열심히 연구하고 있습니다!
1. 프로시져 입력 인자에는 크기를 지정할 수 없구요.
- 변경전 : VARCHAR2(50)
- 변경후 : VARCHAR2
2. 오타도 보이구요.
- 변경전 : OLD_PLANT
- 변경후 : OLD_PLANT_CODE
3. SQL 구문에서의 바인드변수 처리는
- 값에 대해서만 가능합니다. ==> aaa, bbb, ccc 가능
- 명칭(테이블명, 컬럼명 등)에 대해서는 처리 안됩니다. ==> table_name 불가
- 동적 쿼리 이용하셔야 합니다.
오타지적 및 문법에 대한 첨언 감사합니다 항상 많은 도움받고 있습니다. 즐거운 하루 되세요^^
위에 한가지 더 한 질문에 대한 제 생각인데요,,
바인드 변수 선언을 한개 더해서 change_table_name varchar2(50)
[바꿀 테이블명을 조회 해 올 쿼리문]
EXECUTE IMMEDIATE v_str INTO CHANGE_TABLE_NAME;
요렇게 해서 밑에서 나머지 INSERT 구문을 작성하여 실행 하면 될까요?
한가지 더 질문이 생겼는데요, 테이블명OR컬럼명에 바인드 변수 처리가 불가하다고 하셨는데
제가 하려고 한 작업이 3번 프로시져(입력인자)실행 할 때 테이블명 만 주면 알아서 그 테이블에 INSERT를 해주는
(재사용성 높은)쿼리를 사용할 목적으로 만든 프로시져인데 제가 하고자 하는 프로시져를 짠다고 했을때 INSERT 구문의 테이블명에 프로시져의 입력인자를 값으로 줘서 재사용성 높게 사용할 방법이 있을까요?
일단 왜 저렇게 하시는지 이유를 알고싶네요.
저위에 작성된 같은 INSERT 구문을 실행해야할 테이블이 수백건이라 프로시저를 만들어 놓구
바꿔야할TABLE명만 입력해서 실행을 하면 돌아가게끔 해놓은 다음에 바꿔야할 바꿔야할TABLE명이 들어있는 테이블을
생성하여 반복문으로 프로시저(바꿔야할TABLE명) 실행을 돌려서 한번에 처리하려는 걸 하고자하여 질문을 드렸습니다
질문이요 ..
모든테이블의 컬럼 구조가 동일한가요?
다르네요,,
테이블명을 변수로 넘기지 마시고 v_str 에 문자열 붙이기 하세요.
반대로 aaa, bbb, ccc 를 변수로 넘기세요.
v_str := 'INSERT INTO ' || change_table_name; v_str := v_str || ' VALUES(..., :aaa, :bbb ... WHERE plan_code = :ccc)'; EXECUTE IMMEDIATE v_str USING aaa, bbb, ccc;
어익후~!! ㅎㅎ 테이블마다 쿼리가 달라져야 하겠네요?
select 쿼리의 조회 항목들도 동적으로 구성하셔야 합니다.
user_tab_columns 를 이용해야 하겠네요.
맞네요,, 긴 여정이 될 거 같습니다ㅜㅜ
특정 컬럼 변경이라면 조금 수월할수도 있을것 같습니다.
조회 조건마져도 테이블마다 달라진다면? 좋은 방법은 아닐 것 같네요.
operating_code in ('1100','1101') 같은 조건이 모든 테이블 공통이라면 해볼만 합니다.
Insert 쿼리를 컬럼 다 적어가면서 하기는 힘들 듯 하네요.
CTAS 로 table_name_imsi 테이블을 만든뒤
table_name_imsi 컬럼을 Update 하고
table_name_imsi 을 이용해 그대로 table_name 에 Insert 한뒤
table_name_imsi 를 drop 하는 형태로 하는 것도 한가지 방법입니다.
저도 테이블마다 컬럼명 갯수 등 다다르고 심지어 WHERE 절에 들어가는 조건의 컬럼명까지 통일 되어있지 않은 상태를 보고 아 ,, 이건 무리겠다 싶어서 방향을 전환했습니다. 새로운 방법을 가르쳐주신 점 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.