REM ********************************************************
REM * 명 칭 : mkTblToVo
REM * 목 적 : 테이블 컬럼을 jsp용 VO 로 변경해주는 스크립트
REM *
REM *
REM * 파라미터 : p1, p2, p3 ..
REM *
REM * 작 성 자 : 2018/06/01 김과장 (kimazfactory@gmail.com)
REM * 수정이력 : v3 컴마수 맞추기
REM * 메 모 : 12/04 Select DAO 용 구문 추가
REM * 12/06 컴마수 맞추기
REM ********************************************************
SET SERVEROUTPUT ON size 1000000
SET PAGESIZE 10000
SET FEEDBACK OFF
SET ECHO OFF
SET VERIFY OFF
SET LINESIZE 300
SET HEADING OFF
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI';
ACCEPT TABLE_NAME PROMPT 'ENTER TABLE_NAME >'
declare
lvar_col_name varchar2(512) := '';
lvar_data_type varchar2(512) := '';
lvar_index1 number:=0;
lvar_index2 number:=0;
CURSOR cur_column IS
select column_name, data_type from user_tab_cols where table_name=upper('&TABLE_NAME') order by COLUMN_ID;
begin
OPEN cur_column;
dbms_output.put_line('');
dbms_output.put_line('VO information of table : &TABLE_NAME');
dbms_output.put_line('');
dbms_output.put_line('----------------------------------------------');
dbms_output.put_line('');
LOOP
FETCH cur_column INTO lvar_col_name, lvar_data_type;
EXIT WHEN cur_column%NOTFOUND;
IF lvar_data_type = 'VARCHAR2' then dbms_output.put_line('private String '||lvar_col_name||';');
ELSIF lvar_data_type = 'NUMBER' then dbms_output.put_line('private int '||lvar_col_name||';');
ELSIF lvar_data_type = 'DATE' then dbms_output.put_line('private Timestamp '||lvar_col_name||';');
ELSE dbms_output.put_line('--Check it!!!-- '||lvar_col_name||';');
END IF;
END LOOP;
CLOSE cur_column;
OPEN cur_column;
dbms_output.put_line('');
dbms_output.put_line('----------------------------------------------');
dbms_output.put_line('');
dbms_output.put_line('Insert DAO Query for : &TABLE_NAME');
dbms_output.put_line('');
dbms_output.put('String sql="insert into &TABLE_NAME (');
lvar_index1 :=0;
LOOP
FETCH cur_column INTO lvar_col_name, lvar_data_type;
IF cur_column%NOTFOUND THEN
EXIT;
ELSE
IF lvar_index1 > 0 THEN
dbms_output.put(',');
END IF;
END IF;
dbms_output.put(lvar_col_name);
lvar_index1 := lvar_index1+1;
END LOOP;
dbms_output.put(') values (');
CLOSE cur_column;
OPEN cur_column;
lvar_index1 :=0;
LOOP
FETCH cur_column INTO lvar_col_name, lvar_data_type;
IF cur_column%NOTFOUND THEN
EXIT;
ELSE
IF lvar_index1 > 0 THEN
dbms_output.put(',');
END IF;
END IF;
dbms_output.put('?');
lvar_index1 := lvar_index1+1;
END LOOP;
dbms_output.put(')";');
CLOSE cur_column;
dbms_output.put_line('');
OPEN cur_column;
lvar_index2:=1;
LOOP
FETCH cur_column INTO lvar_col_name, lvar_data_type;
EXIT WHEN cur_column%NOTFOUND;
IF lvar_data_type = 'VARCHAR2' then dbms_output.put_line('pstmt.setString('||lvar_index2||', dVo.get'||lvar_col_name||'());');
ELSIF lvar_data_type = 'NUMBER' then dbms_output.put_line('pstmt.setInt('||lvar_index2||', dVo.get'||lvar_col_name||'());');
ELSIF lvar_data_type = 'DATE' then dbms_output.put_line('pstmt.setTimestamp('||lvar_index2||', dVo.get'||lvar_col_name||'());');
ELSE dbms_output.put_line('--Check it!!!-- '||lvar_col_name||';');
END IF;
lvar_index2:=lvar_index2+1;
END LOOP;
CLOSE cur_column;
dbms_output.put_line('');
dbms_output.put_line('----------------------------------------------');
dbms_output.put_line('');
dbms_output.put_line('SELECT DAO Query for : &TABLE_NAME');
dbms_output.put_line('');
OPEN cur_column;
lvar_index2:=1;
LOOP
FETCH cur_column INTO lvar_col_name, lvar_data_type;
EXIT WHEN cur_column%NOTFOUND;
IF lvar_data_type = 'VARCHAR2' then dbms_output.put_line('Vo.set'||lvar_col_name||'(rs.getString('||lvar_index2||'));');
ELSIF lvar_data_type = 'NUMBER' then dbms_output.put_line('Vo.set'||lvar_col_name||'(rs.getInt('||lvar_index2||'));');
ELSIF lvar_data_type = 'DATE' then dbms_output.put_line('Vo.set'||lvar_col_name||'(rs.getTimestamp('||lvar_index2||'));');
ELSE dbms_output.put_line('--Check it!!!-- '||lvar_col_name||';');
END IF;
lvar_index2:=lvar_index2+1;
END LOOP;
CLOSE cur_column;
end;
/
mkTblToVo3.sql
