08월 06일 화 | 데이터입출력 구현 02

SQL 프로시저와 JDBC

CRUD 프로시저

8) 페이징 프로시저

create or replace procedure sungjukPaging
(
  v_cursor out sys_refcursor
  ,v_start number
  ,v_end   number

)
is
begin
  open v_cursor for
    select AA.sno, AA.uname, AA.addr, AA.rnum
    from (select sno, uname, addr, rownum as rnum
    from sungjuk) AA
    where AA.rnum >=v_start and AA.rnum <=v_end;
end;

Java 이클립스로 DB연결

★★★시작하기 전에 프로젝트폴더에 DB jar 라이브러리 연결하기!!!!!★★★

참고하기

https://seaweedisland.tistory.com/49?category=843418

JDBC와 프로시저

1) 성적 프로시저를 자바로 연결하기

package jdbc0806;

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

public class Test01_Select {

public static void main(String[] args) {
	//sungjuk 테이블 전체 목록을 sno 칼럼순으로 정렬해서 출력하시오.
	String url ="jdbc:oracle:thin:@localhost:1521:xe";
	String user ="java0514";
	String password ="1234";
	String driver ="oracle.jdbc.driver.OracleDriver";
		
//	ojdbc6.jar를 setup폴더에 복사
		
		
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null; //
		
try{				
		Class.forName(driver);				
		con = DriverManager.getConnection(url, user, password); 
		System.out.println(" ------ 오라클DB 서버 연결 성공!! ------ ");
				
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT SNO,UNAME,KOR,ENG,MAT,TOT,AVER,ADDR,WDATE "); //칼럼의 순서는 이곳에서.
		sql.append(" FROM SUNGJUK ");
		sql.append(" ORDER BY SNO DESC ");
									
		pstmt = con.prepareStatement(sql.toString());				
		rs=pstmt.executeQuery(); //SELECT문에서 사용한다. SELECT한 테이블이 담김.
		//cursor는 불러온 값 가장 처음것을 가리킴
		//cursor : 가리키는 값
		if(rs.next()){ //cursor가 존재하는지?
			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.getDate("WDATE")+" "); //System.out.print(rs.getString("WDATE").substring(0,10)+" ");						
				System.out.println();
			} while(rs.next());
					
		}else{
			System.out.println("자료없음");
		}
			
	}catch(Exception e){
		System.out.println("실패!" + e);
	}finally{
		//자원반납(순서주의!!!!) 자원반납할시 역순으로 가장 최근에 실행했던 것부터 반납.
		try{
			if(rs!=null){rs.close();}
		} catch (Exception e){}		
				
		try{
			if(pstmt!=null){pstmt.close();}
		} catch (Exception e){}
				
		try{
			if(con!=null){con.close();}
		} catch (Exception e){}
				
	}

	}
}

2) 데이터 삽입하기

package jdbc0806;

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

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";
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null; //

		try{	
				// 1) DB불러오기
				Class.forName(driver);
				
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
				
				StringBuilder sql = new StringBuilder();
				String uname = "박지성";
				int kor=50, eng=60, mat=90;
				int tot = kor+eng+mat;
				int aver = tot/3;
				String addr= "SUWON";
				
				// 3) SQL문 작성
				sql.append(" INSERT INTO SUNGJUK(SNO,UNAME,KOR,ENG,MAT,TOT,AVER,ADDR,WDATE) "); //칼럼의 순서는 이곳에서.
				sql.append(" VALUES(SUNGJUK_SEQ.NEXTVAL,?,?,?,?,?,?,?,SYSDATE) "); //직접 쓰는 것은 절대상수 값이다. 
				
				// 4) SQL문 변환					
				pstmt = con.prepareStatement(sql.toString());				
				// ?순서와 ?에 들어갈 자료형 주의
				pstmt.setString(1, uname);
				pstmt.setInt(2, kor);
				pstmt.setInt(3, eng);
				pstmt.setInt(4, mat);
				pstmt.setInt(5, tot);
				pstmt.setInt(6, aver);
				pstmt.setString(7, addr);		
				
				int result = pstmt.executeUpdate();
				if(result == 0){
					System.out.println("행이 추가되지 않았습니다.");
				}else {
					System.out.println("행이 "+result+"개 추가되었습니다.");
				}
			
			}catch(Exception e){
				System.out.println("실패!" + e);
			}finally{
				//자원반납(순서주의!!!!) 자원반납할시 역순으로 가장 최근에 실행했던 것부터 반납.
				try{
					if(rs!=null){rs.close();}
				} catch (Exception e){}		
				
				try{
					if(pstmt!=null){pstmt.close();}
				} catch (Exception e){}
				
				try{
					if(con!=null){con.close();}
				} catch (Exception e){}
				
			}

	}
}

3) insert 프로시저

package jdbc0806;

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

public class Test02_ProcedureInsert {

	public static void main(String[] args) {
		
		//오라클DB 서버 접근 기본 정보
		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; //

		try {	
				// 1) DB불러오기
				Class.forName(driver);
				
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
				
				StringBuilder sql = new StringBuilder();			
				
				// 3) SQL문 작성
				//con.prepareCall(" {call sungjukInsert()} ");
				//프로시저로 작성하기
				sql.append(" {call sungjukInsert(?,?,?,?,?)} ");			
				
				// 4) SQL문 변환					
				cstmt = con.prepareCall(sql.toString());
				cstmt.setString(1, "남도일");
				cstmt.setInt(2, 80);
				cstmt.setInt(3, 90);
				cstmt.setInt(4, 100);
				cstmt.setString(5, "SUWON");
				// ?순서와 ?에 들어갈 자료형 주의		
				
				int result = cstmt.executeUpdate();
				if(result == 0){
					System.out.println("행이 추가되지 않았습니다.");
				}else {
					System.out.println("행이 "+result+"개 추가되었습니다.");
				}
			
			}catch(Exception e){
				System.out.println("실패!" + e);
			}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){}
				
			}				

		

	}

}

4) update 프로시저

package jdbc0806;

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

public class Test03_ProcedureUpdate {

	public static void main(String[] args) {
		//오라클DB 서버 접근 기본 정보
		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; //

		try {	
				// 1) DB불러오기
				Class.forName(driver);
					
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
						
				StringBuilder sql = new StringBuilder();			
						
				// 3) SQL문 작성
				//con.prepareCall(" {call sungjukInsert()} ");
				//프로시저로 작성하기
				sql.append(" {call sungjukUpdate(?,?,?,?,?,?)} ");			
						
				// 4) SQL문 변환					
				cstmt = con.prepareCall(sql.toString());
				cstmt.setString(1, "남도현");
				cstmt.setInt(2, 85);
				cstmt.setInt(3, 95);
				cstmt.setInt(4, 88);
				cstmt.setString(5, "SEOUL");
				cstmt.setInt(6, 22);
							
						
				int result = cstmt.executeUpdate();
				if(result == 0){
					System.out.println("행이 수정되지 않았습니다.");
				}else {
					System.out.println("행이 "+result+"개 수정되었습니다.");
				}
					
				}catch(Exception e){
						System.out.println("실패!" + e);
				}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){}
						
			}				

	}

}

5) delete 프로시저

package jdbc0806;

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

public class Test04_ProcedureDelete {

	public static void main(String[] args) {
		//오라클DB 서버 접근 기본 정보
		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; //

		try {	
				// 1) DB불러오기
				Class.forName(driver);
					
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
						
				StringBuilder sql = new StringBuilder();			
						
				// 3) SQL문 작성
				//con.prepareCall(" {call sungjukInsert()} ");
				//프로시저로 작성하기
				sql.append(" {call sungjukDelete(?)} ");			
						
				// 4) SQL문 변환					
				cstmt = con.prepareCall(sql.toString());
				cstmt.setInt(1, 22);
							
						
				int result = cstmt.executeUpdate();
				if(result == 0){
					System.out.println("행이 삭제되지 않았습니다.");
				}else {
					System.out.println("행이 "+result+"개 삭제되었습니다.");
				}
					
				}catch(Exception e){
						System.out.println("실패!" + e);
				}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){}
						
			}				

	}

}

6) List 프로시저

package jdbc0806;

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

import oracle.jdbc.OracleTypes;

public class Test05_ProcedureList {

	public static void main(String[] args) {
		//오라클DB 서버 접근 기본 정보
		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; //

		try {	
				// 1) DB불러오기
				Class.forName(driver);
					
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
						
				StringBuilder sql = new StringBuilder();			
						
				// 3) SQL문 작성
				//con.prepareCall(" {call sungjukInsert()} ");
				//프로시저로 작성하기
				sql.append(" {call sungjukList(?)} ");			
						
				// 4) SQL문 변환					
				cstmt = con.prepareCall(sql.toString());
				//첫번째 ?
				cstmt.registerOutParameter(1, OracleTypes.CURSOR);
				cstmt.execute();
				
				//1의 의미하는 것 ->  (1, OrableType.CURSOR)
				rs = (ResultSet) cstmt.getObject(1);
						
				int result = cstmt.executeUpdate();
				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.getDate("WDATE")+" | "); //System.out.print(rs.getString("WDATE").substring(0,10)+" ");						
						System.out.println();
					} while(rs.next());
				}else {
					System.out.println("자료없습니다.");
				}
					
				}catch(Exception e){
						System.out.println("실패!" + e);
				}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){}
						
			}				

	}

}

7) Paging 프로시저

package jdbc0806;

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

import oracle.jdbc.OracleTypes;

public class Test06_ProcedurePaging {

	public static void main(String[] args) {
		//오라클DB 서버 접근 기본 정보
		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; //

		try {	
				// 1) DB불러오기
				Class.forName(driver);
					
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
						
				StringBuilder sql = new StringBuilder();			
						
				// 3) SQL문 작성
				//con.prepareCall(" {call sungjukInsert()} ");
				//프로시저로 작성하기
				sql.append(" {call sungjukPaging(?,?,?)} ");			
						
				// 4) SQL문 변환					
				cstmt = con.prepareCall(sql.toString());
				//첫번째 ?
				cstmt.registerOutParameter(1, OracleTypes.CURSOR);
				cstmt.setInt(2, 4);
				cstmt.setInt(3, 6);
				cstmt.execute(); //실행
				
				//1의 의미하는 것 ->  (1, OrableType.CURSOR)
				rs = (ResultSet) cstmt.getObject(1);
						
				int result = cstmt.executeUpdate();
				if(rs.next()){
					System.out.println("자료있습니다.");
					do{ //칼럼명으로 접근한다.
						System.out.print(rs.getInt("SNO")+" | ");
						System.out.print(rs.getString("UNAME")+" | ");						
						System.out.print(rs.getString("A0DDR")+" | ");
						System.out.print(rs.getInt("RNUM")+" | ");
						System.out.println();
					} while(rs.next());
				}else {
					System.out.println("자료없습니다.");
				}
					
				}catch(Exception e){
						System.out.println("실패!" + e);
				}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){}
						
			}				


	}
}

8) Search 프로시저

package jdbc0806;

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

import oracle.jdbc.OracleTypes;

public class Test07_ProcedureSearch {

	public static void main(String[] args) {
		//오라클DB 서버 접근 기본 정보
		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; //

		try {	
				// 1) DB불러오기
				Class.forName(driver);
					
				// 2) DB 연결
				con = DriverManager.getConnection(url, user, password); 
				System.out.println("오라클DB 서버 연결 성공!!");
						
				StringBuilder sql = new StringBuilder();			
						
				// 3) SQL문 작성
				//con.prepareCall(" {call sungjukInsert()} ");
				//프로시저로 작성하기
				sql.append(" {call sungjukSearch(?,?,?)} ");			
						
				// 4) SQL문 변환					
				cstmt = con.prepareCall(sql.toString());
				//첫번째 ?
				cstmt.registerOutParameter(1, OracleTypes.CURSOR);
				cstmt.setInt(2, 2); //프로시저 조건의 번호
				cstmt.setString(3, "김");
				cstmt.execute(); //실행
				
				//1의 의미하는 것 ->  (1, OrableType.CURSOR)
				rs = (ResultSet) cstmt.getObject(1);
						
				int result = cstmt.executeUpdate();
				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("자료없습니다.");
				}
					
				}catch(Exception e){
						System.out.println("실패!" + e);
				}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){}
						
			}				


	}

}

함수와 트리거

함수 (function)

- 값을 계산하고 결과값을 반환하기 위해 사용

- 저장프로시저와의 차이점

- 입력 매개변수만 사용할 수 있고

- 리턴 타입을 반드시 지정해야 함(필수형식)

create or replace function 함수이름 매개변수

return 리턴자료형

변수선언

begin

문장

end;

1) kor(국어점수)에 점수를 추가하는 함수

--
select * from sungjuk;
select avg(kor) as 국어성적평균 from sungjuk;
--1) 76.2점
--2) 76.54545454545454
--3) 78.36363636363636
--4) 76.2

-- 1행 추가
insert into sungjuk(sno, uname,kor, eng, mat, addr)
values(sungjuk_seq.nextval, '안민기',80,60,50,'BUSAN');

-- 1행 수정
update sungjuk set kor = 100 where sno = 24;

-- 1행 삭제
delete from sungjuk where sno = 23;

-- insert, update, delete 할 때마다 avg(kor)의 값이 변경된다.
-- sno = 24의 국어점수(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
  from sungjuk where sno = v_sno;
  return v_kor; --인상된 국어점수(90)
end;

트리거 (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;

--콘솔용 출력을 사용하려면
set serveroutput on;

행이 추가변경삭제될 때마다 자동으로 정보가 갱신된다.

저작자표시 비영리

'Backend' 카테고리의 다른 글

08월 21일 수 \| JSP 01 - 톰캣설치, 개발환경설정 (0)	2019.08.21
08월 07일 수 \| 데이터입출력 구현 03 - 백업 및 복원 (2)	2019.08.07
08월 05일 월 \| 데이터입출력 구현 01 - 프로시저 (0)	2019.08.05
07월 31일 수 \| SQL활용 10 - SQL DB변환과 NCS 시험범위 (1)	2019.07.31
07월 30일 화 \| SQL활용 09 - SQL 연습 Ⅲ 과 트랜잭션 (0)	2019.07.30

열대섬의 해초🌴

08월 06일 화 | 데이터입출력 구현 02 - SQL 프로시저와 JDBC

SQL 프로시저와 JDBC

CRUD 프로시저

Java 이클립스로 DB연결

JDBC와 프로시저

함수와 트리거

'Backend' 카테고리의 다른 글

댓글

티스토리툴바

08월 06일 화 | 데이터입출력 구현 02 - SQL 프로시저와 JDBC

SQL 프로시저와 JDBC

CRUD 프로시저

Java 이클립스로 DB연결

JDBC와 프로시저

함수와 트리거

'Backend' 카테고리의 다른 글

관련글

댓글

티스토리툴바