[Oracle] Loop, JDBC

JH·2023년 4월 11일

Oracle

목록 보기
5/6

1. TIL

A. Loop (반복문)

loop ~ end loop 구조를 가짐

1. loop

declare
	num NUMBER(2) := 1;
begin
	loop
		dbms_output.put_line(num);
		num := num + 1;
		exit WHEN num > 5;
	end loop;
end;
/

2. while

declare
	num NUMBER(2) := 1;
begin
	while num <= 5 loop
 		dbms_output.put_line(num);
		num := num + 1;
 	end loop;
end;
/

3. for

사용 빈도 높음

declare
	v_empno emp.empno%type := &no;
	v_ename emp.ename%type;
	v_number NUMBER(20);
	v_stars VARCHAR(20);
begin	
	SELECT ename, LENGTH(ename)
		INTO v_ename, v_number
	FROM EMP
	WHERE empno = v_empno;
    
	-- 1 부터 v_number 까지 반복하면서 *를 더함
	for i in 1..v_number loop
		v_stars := v_stars || '*';
	end loop;

	dbms_output.put_line(v_ename || '님의 이름 길이 수는 ' || v_stars);
end;
/

B. 저장 서브프로그램

패키지 : 저장 서브프로그램을 그룹화하는데 사용
프로시저, 함수, 커서, 트리거가 있음

Begin ~ End 는 단순히 PL/SQL에서 사용 (1회용)
IS 는 프로시저나 함수에서 사용 (저장 가능)
IS 가 Declare 의 역할까지 수행하므로 Declare를 사용하면 안됨

sSELECT * FROM USER_SOURCE; -- 프로시저, 함수, 커서, 트리거 확인
show error --에러 발생 출력

1. 저장 프로시저 (Procedure)

SQL문에서 사용 불가, PL/SQL에서 호출 하여 실행

:var var를 참조
IN 매개변수를 입력 받을 때 사용
OUT 매개변수를 출력할 때 사용

Ex1

create or replace procedure info_deptloc
(v_dname IN dept01.dname%type,
 v_loc OUT dept01.loc%type)
is
begin
    SELECT loc
        INTO v_loc
    FROM DEPT01
    WHERE dname = v_dname;
end;
/

-- 프로시저 실행 결과값을 대입받을 변수 선언
variable v_loc varchar2(20);

-- 프로 시저 호출(값을 대입받을 out mode에 선언된 변수 적용)
execute info_deptloc('SALES', :v_loc);

-- 변수 값을 출력
print v_loc;

Ex2

create or replace procedure insert_dept
(v_deptno dept.deptno%type, 
 v_dname dept.dname%type, 
 v_loc dept.loc%type)
is
begin
	insert into dept values(v_deptno, v_dname, v_loc);
	exception
		when dup_val_on_index then
			insert into dept values(v_deptno+10, v_dname, v_loc);
end;
/
execute insert_dept (40, 'a', 'a');

2. 저장 함수 (Function)

SQL문에서 사용 가능
리턴과 리턴 타입을 명시해야함

CREATE OR REPLACE FUNCTION avg_sal(no NUMBER)
RETURN NUMBER
IS
	v_avgsal emp.sal%type;
BEGIN
	SELECT AVG(sal)
		INTO v_avgsal
	FROM EMP
	WHERE deptno = no;

	RETURN v_avgsal;
END;
/

3. Cursor

여러 개의 행을 처리하고자 할 때 사용

DROP FUNCTION emp_info;

create or replace procedure emp_info(v_deptno emp.deptno%type)
is
	cursor emp_cursor is SELECT empno, ename FROM EMP WHERE deptno = v_deptno;
begin
	for emp_data IN emp_cursor loop
		dbms_output.put_line(emp_data.empno || ' ' || emp_data.ename);
	end loop;
end;
/

4. Trigger

PL/SQL Block으로 특정 이벤트 발생시 연관된 다른 작업이 자동 수행

실행 시점 : before, after
실행 시키는 이벤트 : 예로 insert, update, delete
:old 트리거 처리한 레코드의 원래 값을 저장
:new 새로운 값 포함

사용하려면 시스템에서 권한을 부여 해야함

grant create trigger to SCOTT;
grant alter any trigger to SCOTT;
grant drop any trigger to SCOTT;

Ex) 특정 시간에서만 insert

raise_application_error 사용자 지정 예외

drop trigger time_order;

create or replace trigger time_order
	before
		insert
	on 
		order_table
	begin
		if to_char(sysdate, 'HH24:MI') not between '13:45' and '13:50' then
			raise_application_error(-20100, '허용시간이 아님');
		end if;
	end;
/
-- test
insert into order_table values(1, 'c001', sysdate);

Ex) delete할 때 백업

for each row 행트리거로 적용됨 이전, 이후 레코드 값을 제어할 수 있음

create or replace trigger delete_backup
	before
		delete
	on 
		order_table2
	for each row
	begin
		insert into backup_order2 values(:old.no, :old.ord_code, sysdate);
	end;
/
-- test
delete from order_table2 where no=1;

C.Java DataBase Connectivity

JDBC : 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API
DB 종류에 종속적이지 않은 DB연동 표준 API

DTO (Data Transfer Object) : 계층 간 데이터 교환을 하기 위해 사용하는 객체
DAO (Data Access Object) : 데이터베이스의 data에 접근하기 위한 객체


순서

  1. DTO 생성
  2. DAO 생성
  3. DeptDAO.class 생성
  4. Oracle 드라이버 다운로드 : MAVEN Repo
  5. 프로젝트 적용(build path)
  6. DeptDAO.class 작성

1. DTO

public class Dept {
	private int deptno;
	private String dname;
	private String loc;
// 게터, 세터, 생성자, 사용자정의생성자, toString 생략
}

2. DAO

// package, import 생략
public class DeptDAO {
	// 모든 부서 정보 검색 : SELECT * FROM DEPT;
	public static ArrayList<Dept> getAllDept() throws SQLException {
		ArrayList<Dept> allDepts = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rset = null;
		
		try	{
			// step01 : driver 로딩  - static{}
			// step02 : DB접속 - ip/id/pw  - Connection
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "ID", "PASSWORD");
			
			// step03 : sql 문장 실행 객체 - Statement / PreparedStatement
			stmt = conn.createStatement();
			
			// step04 : sql 문장 실행 - ResultSet executeQuery(String query)
			rset = stmt.executeQuery("SELECT * FROM DEPT");
			
			
			// step05 : 데이터 활용	- select인 경우 ResultSet 객체 분해
			allDepts = new ArrayList<Dept>();
			while(rset.next()) {
				allDepts.add(new Dept(rset.getInt("deptno"), rset.getString("dname"), rset.getString("loc")));
			}
			
		// step06 : 종료-자원반환 - 필수 finally{}, 닫는 순서도 유의해야함
		} finally {
			rset.close();
			stmt.close();
			conn.close();
		}
			return allDepts;
	}
	
	// 부서 번호로 특정 부서 검색 : "SELECT * FROM DEPT WHERE deptno = " + deptno
	public static Dept getDeptByDeptno(int deptno) throws SQLException {
		Dept dept = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rset = null;
		
		try	{
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "ID", "PASSWORD");
			
			stmt = conn.createStatement();
			
			rset = stmt.executeQuery("SELECT * FROM DEPT WHERE deptno = " + deptno);
			
			if(rset.next()) {
				dept = new Dept(rset.getInt("deptno"), rset.getString("dname"), rset.getString("loc"));
			}
			
		} finally {
			rset.close();
			stmt.close();
			conn.close();
		}
			return dept;
	}
	
	// 새로운 부서 생성 ver1 : "INSERT INTO DEPT VALUES(" + deptno + ", " + 'dname' + ", " + 'loc' + ")"
	public static boolean insertDept(Dept dept) throws SQLException{
		Connection conn = null;
		Statement stmt = null;
		
		try	{
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "ID", "PASSWORD");
			
			stmt = conn.createStatement();
			
			int result = stmt.executeUpdate("INSERT INTO DEPT VALUES(" + dept.getDeptno() + ", '" + dept.getDname() + "', '" + dept.getLoc() + "')");
			
			if(result != 0) {
				return true;
			}
			
		} finally {
			stmt.close();
			conn.close();
		}
			return false;
	}
	
	// 새로운 부서 생성 ver2 : "INSERT INTO DEPT VALUES(?, ? , ?)"
	public static boolean insertDept2(Dept dept) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
			
		try	{
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "ID", "PASSWORD");
            
			pstmt = conn.prepareStatement("INSERT INTO DEPT VALUES(?, ? , ?)");
			pstmt.setInt(1, dept.getDeptno());
			pstmt.setString(2, dept.getDname());
			pstmt.setString(3, dept.getLoc());
				
			int result = pstmt.executeUpdate();
				
			if(result != 0) {
				return true;
			}
				
		} finally {
			pstmt.close();
			conn.close();
		}
			return false;
	}
    
    // 부서번호로 검색한 특정 부서의 위치 수정 : UPDATE DEPT SET loc = ? WHERE deptmo = ?
	public static boolean updateDeptLocByDeptno(int deptno, String newDeptLoc) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
			
		try {
			conn = DBUtil.getConnection(); // DBUtil로 연결
			pstmt = conn.prepareStatement("UPDATE DEPT SET loc = ? WHERE deptno = ?");
				
			pstmt.setString(1, newDeptLoc);
			pstmt.setInt(2, deptno);
				
			int result = pstmt.executeUpdate();
				
			if(result != 0) {
				return true;
			}
				
		}finally {
			DBUtil.close(pstmt, conn); // DBUtil에서 오버로딩으로 한번에 처리
		}	
		return false;
	}
		
	// 부서번호로 검색한 특정 부서를 삭제 : DELETE FROM DEPT WHERE deptno = ?
	public static boolean deleteDeptByDeptno(int deptno) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
			
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement("DELETE FROM DEPT WHERE deptno = ?");
				
			pstmt.setInt(1, deptno);
				
			int result = pstmt.executeUpdate();
				
			if(result != 0) {
				return true;
			}
				
		}finally {
			DBUtil.close(pstmt, conn);
		}
		return false;
	}
}

3. Main (DeptTest)

// package, import 생략
public class DeptTest {
	public static void main(String[] args) {
		try {
			// 모든 부서 검색
			System.out.println(DeptDAO.getAllDept());
			
			// 특정 부서 검색
			System.out.println(DeptDAO.getDeptByDeptno(20));
			
			// 새로운 부서 생성 v1
			System.out.println(DeptDAO.insertDept(new Dept(60, "DEV", "SEOUL")));
			
			// 새로운 부서 생성 v2
			System.out.println(DeptDAO.insertDept2(new Dept(70, "DEVOPS", "BUSAN")));
            
            // 부서명 수정
			System.out.println(DeptDAO.updateDeptLocByDeptno(70, "JEJU"));
			
			// 부서 삭제
			System.out.println(DeptDAO.deleteDeptByDeptno(70));
            
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

2. 에러

ORA 관련 예외는 구글링을 통해 해결함
Statement 구문에서 문법 오류 ('' "")가 종종 발생함

3. 보완 해야 할 것

JDBC의 Statement문에서 쿼리로 된 표현과 java의 혼용된 표현이 너무 낯설고 어렵게 느껴짐
" "로 감싸진 부분은 쿼리형태를 표현하기 위해서, ' '는 java에서의 값이 String이 아닐 경우 String으로 변환해야하기 때문에 사용함 (컬럼 이름이 String 이므로)

  • 는 java에서의 연산자를 사용하므로 너무 복합적인 표현이 되는 것 같아 어렵게 느껴짐
    JDBC는 아주 중요하므로 추가적으로 학습이 필요함

4. 느낀점

SQL DB가 마무리 되고 드디어 Java와 DB가 연결됬다.
연결 된 후 예외가 발생하면 정말 너무 길고 복잡하게 나와서 예외 처리의 중요성이 점차 부각되는 것 같음
Statement를 사용하면 '', "" 따옴표 때문에 정말 어지럽고 가독성도 안좋게 느껴짐, 개인적으로 지양하고 싶음
선택의 영역이라고 해서 PreparedStatement를 더 많이 쓸 것으로 예상됨

profile
JH
잘해볼게요
이전 포스트

[Oracle] 제약조건(INTEGRITY), Object(view, rownum, Sequence), PL/SQL

다음 포스트

[Oracle] DataBase Modeling

0개의 댓글