8월6일 - 데이터입출력 2 : 프로시저 JAVA 연동하기 & 함수 트리거

PL/SQL 프로시저

JAVA에 프로시저 연동하기

package oop0806;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

public class Test01_Insert {

	public static void main(String[] args) {
		// sungjuk 테이블 행추가 연습
		
		// 오라클 DB서버 접근 기본정보
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";
		/*
		  ojdbc6.jar를 setup폴더에 복사
		  C:\oraclexe\app
		             \oracle
		             \product
		             \11.2.0
		             \server
		             \jdbc
		             \lib
		*/
		Connection con=null;          //DB연결
		PreparedStatement pstmt=null; //SQL변환
		ResultSet rs=null;            //select문 조회

		try{
			//1) 드라이버 로딩
			Class.forName(driver);
			
			//2) DB연결
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			//3) SQL문 작성
			StringBuilder sql=new StringBuilder();
			
			//4) SQL문 변환
			
			//5) SQL문 실행
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(pstmt!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(con!=null){rs.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

CRUD 프로시저를 JDBC에 연결하기

- Oracle에 생성되어있는 프로시저를 java에 연동해본다.

- 먼저 프로시저를 모두 컴파일 후 진행한다.

- 각 프로시저의 변수의 내용을 확인 후, 불러온다. (*중요)

1. Insert 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

public class Test03_ProcedureInsert {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 행추가 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();
			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukInsert(?,?,?,?,?)} ");
			
			cstmt=con.prepareCall(sql.toString());
			cstmt.setString(1, "호날두");
			cstmt.setInt(2, 90);
			cstmt.setInt(3, 95);
			cstmt.setInt(4, 60);
			cstmt.setString(5, "Seoul");
			
			int cnt=cstmt.executeUpdate();
			System.out.println("프로시저 sungjukInsert 행추가"+cnt+"개 됨");
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

2. Update 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

public class Test04_ProcedureUpdate {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 행수정 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukUpdate(?,?,?,?,?,?)} ");
			
			cstmt=con.prepareCall(sql.toString());
			cstmt.setString(1, "트럼프");
			cstmt.setInt(2, 80);
			cstmt.setInt(3, 85);
			cstmt.setInt(4, 90);
			cstmt.setString(5, "Seoul");
			cstmt.setInt(6, 24);
			
			int cnt=cstmt.executeUpdate();
			System.out.println("프로시저 sungjukUpdate 행수정"+cnt+"개 됨");
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

3. Delect 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

public class Test05_ProcedureDelete {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 행삭제 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();
			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukDelete(?)} ");
			
			cstmt=con.prepareCall(sql.toString());
			cstmt.setInt(1, 23);
			
			int cnt=cstmt.executeUpdate();
			System.out.println("프로시저 sungjukDelete 행삭제"+cnt+"개 됨");
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

4. Select 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

public class Test06_ProcedureSelect {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 행목록 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();
			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukInsert(?,?,?,?,?)} ");
			
			cstmt=con.prepareCall(sql.toString());
			cstmt.setString(1, "호날두");
			cstmt.setInt(2, 90);
			cstmt.setInt(3, 95);
			cstmt.setInt(4, 60);
			cstmt.setString(5, "Seoul");
			
			int cnt=cstmt.executeUpdate();
			System.out.println("프로시저 sungjukInsert 행추가"+cnt+"개 됨");
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

5. List 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

import oracle.jdbc.OracleTypes;

public class Test07_ProcedureList {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 목록 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();
			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukList(?)} ");
			cstmt=con.prepareCall(sql.toString());
			
			//1-> 첫번째 ? 인덱스
			//OracleTypes.CURSOR 조회하고자 하는 DB서버 커서
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.execute(); //실행
			
			//1이 의미하는 것 -> (1, OracleTypes.CURSOR)
			rs=(ResultSet) cstmt.getObject(1);
			if(rs.next()){
				System.out.println("자료있음");
				do{
					System.out.print(rs.getInt("sno")+" ");
					System.out.print(rs.getString("uname")+" ");
					System.out.print(rs.getInt("kor")+" ");
					System.out.print(rs.getInt("eng")+" ");
					System.out.print(rs.getInt("mat")+" ");
					System.out.print(rs.getInt("tot")+" ");
					System.out.print(rs.getInt("aver")+" ");
					System.out.print(rs.getString("addr")+" ");
					System.out.print(rs.getString("wdate")+" ");
					System.out.println();					
				}while(rs.next());						
			}else{
				System.out.println("자료없음");
			}//if end
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

6. Paging 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

import oracle.jdbc.OracleTypes;

public class Test07_ProcedurePaging {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 페이징 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();
			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukPaging(?,?,?)} ");
			cstmt=con.prepareCall(sql.toString());
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setInt(2, 5); //sno=10에서 부터 
			cstmt.setInt(3, 8); //sno=22까지 조회
			cstmt.execute(); //실행
			
			rs=(ResultSet) cstmt.getObject(1);
			if(rs.next()){
				System.out.println("자료있음");
				do{
					System.out.print(rs.getInt("sno")+" ");
					System.out.print(rs.getString("uname")+" ");
					System.out.print(rs.getString("addr")+" ");
					System.out.print(rs.getInt("rnum")+" ");
					System.out.println();					
				}while(rs.next());						
			}else{
				System.out.println("자료없음");
			}//if end
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

7. Search 프로시저 java에 연결하기

package oop0806;

import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Connection;

import oracle.jdbc.OracleTypes;

public class Test08_ProcedureSearch {

	public static void main(String[] args) {
		// 프로시저를 이용한 sungjuk 테이블 search 연습
		
		String url     ="jdbc:oracle:thin:@localhost:1521:xe";
		String user    ="java0514";
		String password="1234";
		String driver  ="oracle.jdbc.driver.OracleDriver";

		Connection con=null;          //DB연결
		//프로시저 호출관련 인터페이스
		CallableStatement cstmt=null;
		ResultSet rs=null;            //select문 조회

		try{
			Class.forName(driver);
			con=DriverManager.getConnection(url, user, password);
			System.out.println("오라클DB서버 연결 성공");
		
			StringBuilder sql=new StringBuilder();
			
			//프로시저 호출 형식
			//con.prepareCall("{call 프로시저이름(?,?,?)}")
			sql.append(" {call sungjukSearch(?,?,?)} ");
			cstmt=con.prepareCall(sql.toString());
			cstmt.registerOutParameter(1, OracleTypes.CURSOR);
			cstmt.setInt(2, 1);    //uname칼럼에 
			cstmt.setString(3, "화"); //"화"문자가 있는지조회
			cstmt.execute(); //실행
			
			rs=(ResultSet) cstmt.getObject(1);
			if(rs.next()){
				System.out.println("자료있음");
				do{
					System.out.print(rs.getInt("sno")+" ");
					System.out.print(rs.getString("uname")+" ");
					System.out.print(rs.getString("addr")+" ");
					System.out.println();					
				}while(rs.next());						
			}else{
				System.out.println("자료없음");
			}//if end
			
		}catch(Exception e){
			System.out.println("실패!");
		}finally{ //자원반납
			try{
				if(rs!=null){rs.close();}
			}catch(Exception e){}
			try{
				if(cstmt!=null){cstmt.close();}
			}catch(Exception e){}
			try{
				if(con!=null){con.close();}
			}catch(Exception e){}
		}//try end
		
	}//main() end
}//class end

함수 : 트리거 (Function : trigger)

1. 함수 Function?

- 값을 계산하고 결과값을 반환하기 위해서 사용
- 저장프로시저와의 차이점
-> 입력 매개변수만 사용할 수 있고 리턴 타입을 반드시 지정해야함 (필수)
- 형식)

create or replace function 함수이름
  매개변수
return 리턴자료형

is
  변수선언
begin
  문장
end;
/

문제) sungjuk테이블에서 국어점수의 평균을 구하시오

select round(avg(kor),1) from sungjuk;
select *  from sungjuk;

--행추가
insert into sungjuk(sno,uname,kor,eng,mat,addr)
values(sungjuk_seq.nextval,'이순신',40,60,75,'Busan');

--행수정
update sungjuk set kor=95 where sno=28;

--행삭제
delete from sungjuk where sno=29;

--sno=26의 국어점수(40)를 50점 추가하는 함수 생성
create or replace function fn_update_kor(v_sno number)
return number
is
  v_kor number;
begin
  update sungjuk set kor=kor+50 where sno=v_sno;
  select kor into v_kor --kor 점수를 v_kor에 넣어라
  from sungjuk where sno=v_sno;
  return v_kor; --인상된 국어점수(100)
end;

-> 실행결과는 sqlplus에서 구현,

cmd

>sqlplus java0514/1234

>var korea number;

>execute :korea := fn_update_kor(24);

>print korea;

- sno=8의 kor 칼럼에 50점이 더해진것을 볼 수 있다.

2. 트리거 Trigger?
- 방아쇠, 데이터베이스에서의 연쇄적인 동작을 정의함.

- insert, update, delete문이 실행될 때 묵시적으로 수행되는 프로시저

- 형식

create or replace trigger 트리거이름
before 또는 after
  트리거이벤트 on 테이블명
declare
  변수선언;
begin
  실행할 명령어;
end;

- 국어점수 추가, 수정, 삭제가 되면 국어평균도 달라짐

create or replace trigger kor_trigger
after
  insert or update or delete on sungjuk
declare
  avg_kor number;
begin
  select avg(kor) into avg_kor from sungjuk;
  dbms_output.put_line('국어평균:' || avg_kor);
end;

select * from sungjuk;

--행추가
insert into sungjuk(sno,uname,kor,eng,mat,addr)
values(sungjuk_seq.nextval,'이순신',40,60,75,'Busan');

--행수정
update sungjuk set kor=95 where sno=28;

--행삭제
delete from sungjuk where sno=27;

--콘솔창 출력 dbms_output.put_line을 사용하려면
set serveroutput on;

-> 실행결과

- 먼저 create 한 후 insert, update, delete 차례로 run을 시켜준다.

- 각 결과를 살펴볼 때, 삽입했을 때 수정했을 때 삭제했을 때의 국어점수 평균을 볼 수 있다.

- 하나의 create문으로 insert, update, delete 세개의 작업을 주었을 때 암묵적으로 자동 실행 되는 것이

바로 trigger!

저작자표시

'공부 > 데이터 입출력' 카테고리의 다른 글

8월7일 - 데이터 입출력 3 : 백업 및 복원 (0)	2019.08.07
8월5일 - 데이터입출력 1 : 프로시저 (0)	2019.08.05

3XL

8월6일 - 데이터입출력 2 : 프로시저 JAVA 연동하기 & 함수 트리거

PL/SQL 프로시저

CRUD 프로시저를 JDBC에 연결하기

함수 : 트리거 (Function : trigger)

'공부 > 데이터 입출력' 카테고리의 다른 글

티스토리툴바

8월6일 - 데이터입출력 2 : 프로시저 JAVA 연동하기 & 함수 트리거

PL/SQL 프로시저

CRUD 프로시저를 JDBC에 연결하기

함수 : 트리거 (Function : trigger)

'공부 > 데이터 입출력' 카테고리의 다른 글

'공부/데이터 입출력' Related Articles

티스토리툴바