국비지원 35일차 - JDBC INSERT, UPDATE, SELECT ,PL/SQL 테이블 타입, 레코드 타입

JDBC

Statement의 메소드
 * 1) execute() => 모든 sql문 실행 가능
 * 2) int executeUpdate() => DML문장 실행가능 (insert, update, delete)
 * DML문장에 의해 영향받은 레코드수를 반환한다.
 * 3) RedultSet executeQuery() ==> DQL문장 실행 가능 (select문)

package jdbc.day02;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;

public class MemoInsert {
	Connection con;
	Statement stmt;
	String url = "jdbc:oracle:thin:@localhost:1521:XE";
	String user = "scott", pwd="tiger";
	public MemoInsert() {
		//드라이버로딩
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Driver Loading Success");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public void memoInsert(String name, String msg) {
		//db연결하여 insert문 수행
		try {
			con=DriverManager.getConnection(url,user,pwd);
			String sql="INSERT INTO MEMO(idx,name,msg,wdate)";
			sql+=" VALUES(memo_seq.nextval,'"+name+"','"+msg+"',sysdate)";
			stmt = con.createStatement();
			boolean b = stmt.execute(sql);
			System.out.println("INSERT 성공");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close();
		}
	}
	public void close() {
		//db연결 해제
		try {
			if(stmt!=null) stmt.close();
			if(con!=null)	con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	public static void main(String[] args) {
		String name = JOptionPane.showInputDialog("작성자를 입력하세요");
		String msg = JOptionPane.showInputDialog("메모 내용을 입력하세요");
		if(name==null||msg==null)	return;
		MemoInsert mi = new MemoInsert();
		mi.memoInsert(name,msg);
	}
}

JDBC UPDATE

package jdbc.day02;

import java.util.Scanner;
import java.sql.*;


public class MemoUpdate {
	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.println("수정할 메모글 번호 입력");
		int idx = sc.nextInt();
		
		System.out.println("작성자");
		String name = sc.next();
		sc.skip("\r\n");//엔터값 건너뛰기
		System.out.println("메모 내용=>");
		String msg = sc.nextLine();//라인 단위로 입력 받음
		
		System.out.println(idx+"/"+name+"/"+msg);
		
		
		try {
			//1. 드라이버 로딩
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Driver Loading Success");
			//2. db연결
			String url="jdbc:oracle:thin:@localhost:1521:XE";
			String user="scott",pwd="tiger";
			Connection con = DriverManager.getConnection(url,user,pwd);
			//3. sql문 => update문 작성
			String sql = "update memo set name='"+name+"', msg='"+msg+"' WHERE idx="+idx;
			//4. statement 얻기
			Statement stmt = con.createStatement();			
			//5. sql문 실행하기
			int cnt = stmt.executeUpdate(sql);
			System.out.println(cnt+"update");
			//6. db연결자원 해제
			if(stmt!=null)	stmt.close();
			if(con!=null)	con.close();
			
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

JDBC SELECT

rs는 select문에 의해 영향받은 결과 테이블을 참조하는 객체이다.
rs는 커서 개념을 이용해 데이터를 꺼내올 수 있는데 이때 커서는 첫번째 행의 직전 (before first)에 위치하고 있다가
next()가 호출되면 다음 행으로 커서를 이동시킨다. 이때 커서가 가리키고 있는 행에 레코드가 있으면 true를 반환한다.
ResultSet의 주요 메소드
- boolean next();
- getXXX("컬럼명") 또는 getXXX(컬럼인덱스)
:컬럼 데이터를 꺼내와 반환해준다.

package jdbc.day02;
import java.sql.*;
import java.sql.DriverManager;

public class MemoSelect {
	public static void main(String[] args) 
	throws Exception
	{
		//1. 드라이버 로딩
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@localhost:1521:XE";
		String user = "scott", pwd="tiger";
		Connection con = DriverManager.getConnection(url,user,pwd);
		
		String sql="SELECT * FROM memo ORDER BY idx DESC";
		Statement stmt= con.createStatement();
		
		//public ResultSet executeQuery(select문)
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()) {
			int idx = rs.getInt("idx");
			String name = rs.getString("name");
			String msg = rs.getString("msg");
			Date wdate =rs.getDate("wdate");
			System.out.println(idx+"\t"+name+"\t"+msg+"\t"+wdate);
		}
        	rs.close();
		stmt.close();
		con.close();
	}
}

실습 스캐너로 부서번호를 입력받는다. 
 * 그러면 해당 부서의 사원정보를 가져와 보여준다.
 * 부서번호, 사번, 사원명, 담당업무, 입사일

package jdbc.day02;
import java.sql.*;
import java.util.*;

public class EmpSelect {
	public static void main(String[] args) 
	throws Exception
	{	
		Scanner sc = new Scanner(System.in);
		System.out.println("부서번호를 입력");
		int no = sc.nextInt();
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		String url="jdbc:oracle:thin:@localhost:1521:XE";
		String user="scott", pwd="tiger";
		
		Connection con = DriverManager.getConnection(url,user,pwd);
		
		String sql="SELECT empno, ename, rpad(job,15,' ') job, hiredate FROM EMP WHERE DEPTNO="+no;
		
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()) {
			int empno = rs.getInt("empno");
			String ename = rs.getString("ename");
			String job= rs.getString(3);
			java.sql.Date wdate=rs.getDate("hiredate");
			System.out.println(empno+"\t"+no+"\t"+ename+"\t"+job+"\t"+wdate);
		}
		rs.close();
		stmt.close();
		con.close();
	}
}

SQL문 작동하게 해주는 class

package jdbc.day02;

import java.sql.*;
import java.util.*;

public class MyQuery {
	public static void main(String[] args) 
	throws Exception
	{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		System.out.println("Driver Loading Success!!");
		
		//2. DB와 연결
		String url = "jdbc:oracle:thin:@localhost:1521:XE";
		String user = "scott", pwd="tiger";
		Connection con = DriverManager.getConnection(url,user,pwd);
		System.out.println("DB 연결 성공");
		Statement stmt = con.createStatement();
		
		Scanner sc = new Scanner(System.in);
		System.out.println("SQL문을 입력하세요=>");
		String sql="";
		while((sql=sc.nextLine())!=null) {
			if(sql.contentEquals("x"))	break;
			boolean isSel = stmt.execute(sql);
			if(!isSel) {
				//dml문장에 의해 영향받은 레코드 객수 받아오려면
				int cnt=stmt.getUpdateCount();
				System.out.println(cnt+"개의 레코드가 변경되었습니다.");
			}else {
				ResultSet rs = stmt.getResultSet();
				while(rs.next()) {
					int empno = rs.getInt("empno");
					String ename = rs.getString("ename");
					String job= rs.getString("job");
					java.sql.Date wdate=rs.getDate("hiredate");
					System.out.println(empno+"\t"+ename+"\t"+job+"\t"+wdate);
				}
				rs.close();
			}
			System.out.println("SQL문을 입력하세요=>");
		}
		
		stmt.close();
		con.close();
		
	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ
PL/SQL

테이블 타입

CREATE OR REPLACE PROCEDURE TABLE_TYPE
(PDNO IN EMP.DEPTNO%TYPE)
IS
  --테이블 타입 선언
TYPE ENAME_TABLE IS TABLE OF EMP.ENAME%TYPE
INDEX BY BINARY_INTEGER;
--테이블 타입의 변수 선언해서 사용
enameArr ename_table;
i binary_integer:=0;
BEGIN
  for K in (SELECT ENAME FROM EMP WHERE DEPTNO=PDNO) LOOP
  i:=i+1;
  enameArr(i):=K.ename;
  end loop;
  --저장된 값 출력하기
  for j in 1..i loop
  dbms_output.put_line(rpad(enameArr(j), 15 ,' '));
  end loop;
END;
/

set serveroutput on

execute table_type(10);

 레코드 타입

CREATE OR REPLACE PROCEDURE RCD_TYPE
(PNAME IN DEPT.DNAME%TYPE)
IS
TYPE DEPT_RECORD IS RECORD(
  VNO DEPT.DEPTNO%TYPE,
  VNAME DEPT.DNAME%TYPE,
  VLOC DEPT.LOC%TYPE
);

MYD DEPT_RECORD;
BEGIN
  SELECT * INTO MYD FROM DEPT
  WHERE DNAME=PNAME;
  
  DBMS_OUTPUT.PUT_LINE('부서번호: '||MYD.VNO);
  DBMS_OUTPUT.PUT_LINE('부서명: '||MYD.VNAME);
  DBMS_OUTPUT.PUT_LINE('지역: '||MYD.VLOC);
END;
/

EXECUTE RCD_TYPE('ACCOUNTING');

 파라미터 종류
1) in parameter : 내부 프로그램에 제공
2) out parameter : 호출자에게 제공
3) in out parameter : 입력과 동시에 출력용으로 사용 가능

--out parameter 갖는 프로시저를 실행하려면 바인트 변수를 선언한 후 실행해야 한다.

CREATE OR REPLACE PROCEDURE empFind
(PNO IN emp.empno%type,oname out emp.ename%type)
is
begin
select ename into oname from emp
where empno=pno;
end;
/

variable name varchar2(30);

execute empFind(7788, :name);

print name;

예제
--문제 1 부서별 사원수와 평균급여, 최대급여, 최소급여를 가져와 출력하는 프로시저를 
--작성 단 , for 루프를 이용

CREATE OR REPLACE PROCEDURE deptinfo
IS 
VDNAME dept.dname%type;
BEGIN
  FOR K IN (SELECT deptno,count(deptno) cnt, round(AVG(SAL),2)  av,MAX(SAL) mx,MIN(SAL) mn FROM EMP Group BY DEPTNO ) LOOP
  select dname into vdname from dept where deptno=K.deptno;
    DBMS_OUTPUT.PUT_LINE(K.deptno||' '||VDNAME||' '||K.cnt||' '||K.av||' '||K.mx||' '||K.mn);
  END LOOP;
END;
/

execute deptinfo;

--문제 2 emp2테이블에서 사번을 인파라미터로 받아 해당 사원이 있는지 먼저 검색하고
--해당 사원이 있으면 해당 사원 정보를 삭제하는 프로시저를 작성

CREATE OR REPLACE PROCEDURE empdel(pno in emp2.empno%type)
IS
vcnt number(3);
BEGIN
  select count(*) into vcnt from emp2 where empno=pno;
  if(vcnt>0) then
    delete from emp2 where empno=pno;
    dbms_output.put_line(pno||'번 사원 정보를 emp2에서 삭제하였습니다.');
  else
    dbms_output.put_line(pno||'번 사원은 존재하지 않습니다.');
  end if;
END;
/
execute empdel(7788);