[PL/SQL] 동적 SQL과 정적 SQL로 각각 컬럼 명만 변경하여 수행되는 프로시저 작성

PL/SQL 작성에 대해 문의가 온 내용이 있어서 정리하였습니다.

PL/SQL로 프로시저를 수정하는데, SQL의 대부분은 수정하지 않고, 조건에 따라 컬럼하나만 변경되어서 조회가 될 수있게 수정하는 가이드를 줄 수 있냐는 문의였습니다.

문의주신 분의 의도는 정적 SQL로 SQL의 변경을 최소화하여 기존과 같은 정적 SQL 형식으로의 가이드를 부탁했지만, 저는 처음에 동적 SQL로 답을 드렸었는데요, 덕분에 동적,정적 SQL 둘다 가이드를 줄 수 있었습니다.

 

동적 SQL과 정적 SQL에 따라 어떠한 방법으로 작성을 했는지 예시로 만든 프로시저를 통해서 작성해보겠습니다.

두개의 프로시저의 성격은 약간 차이가 있습니다. 1.동적 SQL을 사용해서 만든 프로시저는 EMP_COL_TEST의 ENAME,JOB 각각 컬럼에 EMP에서 조회한 값과 현재 시간(sysdate)를 insert하는 프로시저입니다.

반면에 2.정적 SQL은 EMP_COL_TEST의 ENAME 컬럼하나에 emp의 ENAME,JOB 값을 넣는 프로시저입니다.

둘다 emp 라는 테이블과 emp의 메타데이터만 복사해온 emp_col_test 두개의 테이블을 사용할 예정입니다.

 

create table emp_col_test
as select * from emp where 1=2;

-- 기본이 not null이기 때문에 null로 변경
alter table emp_col_test modify (empno null);

 

1. 동적 SQL

CREATE OR REPLACE PROCEDURE PROCESS_COLUMN
IS
	column_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('ENAME','JOB');
BEGIN
	for i in 1.. column_names.count LOOP
    	BEGIN
        	EXECUTE IMMEDIATE 'INSERT INTO EMP_COL_TEST('||column_names(i)||'),hiredate) select '||column_names(i)||',sysdate as hiredate from emp';
            commit;
        END;
    END LOOP;
END;

 

 

2.정적 SQL

CREATE OR REPLACE PROCEDURE PROCESS_COLUMN2
IS
	column_index PLS_INTEGER := 1;
BEGIN
	while column_index <= 2 LOOP
    	BEGIN
        	INSERT INTO EMP_COL_TEST(ENAME,hiredate) select case when column_index =1 then ename when column_index=2 then job end as ename,sysdate as hiredate from emp;
            commit;
        END;
		column_index := column_index + 1;
    END LOOP;
END;

 

PL/SQL 틀을 짤때 참고하면 좋을것 같습니다.