| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
- VUE
- 정보처리기사정리
- php
- ReactNative
- 자바스크립트
- 국비IT
- spring
- 정보처리기사요약
- typescript
- 정보처리기사실기요약
- 코딩테스트
- 자바의정석
- react
- 이안의평일코딩
- 자스코테
- 리액트네이티브
- 리액트
- 자바스크립트 코딩테스트
- CSS
- javascript
- Java의정석
- 정보처리기사
- 스프링
- Oracle
- 타입스크립트
- 정보처리기사실기
- 오라클
- 국비코딩
- 평일코딩
- 정보처리기사실기정리
- Today
- Total
이안의 평일코딩
Oracle 7일차 - INNER JOIN 본문
2020.08.12(수)
1. JOIN
=> 여러개의 테이블에서 사용자 요청한 데이터를 가지고 오는 프로그램
=> 종류
INNER JOIN(교집합) => 다른 테이블에서 데이터를 연결
============== 단점은 NULL일 경우에 처리가 불가능
= EQUI_JOIN(가장 많이 사용되는 기술) A.col=B.col
형식)
1. 오라클 조인 : 오라클에서만 사용하는 쿼리문장
형식)
SELECT A.컬럼명, B.컬럼명...
FROM 테이블명(A), 테이블명(B)
WHERE 테이블명.col = 테이블명.col
***** 컬럼명이 다른 경우에는 테이블(별칭), 생략이 가능
deptno deptno 컬럼명이 둘다 동일하므로 테이블명 emp.deptno 붙여줌
2. ANSI 조인 : 전체 데이터베이스에서 사용하는 쿼리
SELECT 컬럼명, 컬럼명...
FROM 테이블명 (INNER)JOIN 테이블명
ON 테이블명.col = 테이블명.col
=============== 1,2 => 컬럼명이 다를 수도 있다
SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp e, dept d WHERE e.deptno=d.deptno;
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp e, dept d WHERE e.deptno=d.deptno
*
ERROR at line 1:
ORA-00918: column ambiguously defined //deptno => e.deptno
SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e, dept d WHERE e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 82/12/09 3000 20 RESEARCH DALLAS
7839 KING PRESIDENT 81/11/17 5000 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 30 SALES CHICAGO
7876 ADAMS CLERK 7788 83/01/12 1100 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 81/12/03 950 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 ACCOUNTING NEW YORK
7935 Hong 20/08/11 10 ACCOUNTING NEW YORK
15 rows selected.
SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,e.deptno,dname,loc FROM emp e JOIN dept d ON e.deptno=d.deptno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 82/12/09 3000 20 RESEARCH DALLAS
7839 KING PRESIDENT 81/11/17 5000 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 30 SALES CHICAGO
7876 ADAMS CLERK 7788 83/01/12 1100 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 81/12/03 950 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 ACCOUNTING NEW YORK
7935 Hong 20/08/11 10 ACCOUNTING NEW YORK
15 rows selected.3. NATURAL JOIN (별칭 사용안함 e.deptno => deptno)
SELECT 컬럼명, 컬럼명...
FROM emp NATURAL JOIN dept;
4. JOIN~USING
SELECT 컬럼명, 컬럼명...
FROM emp JOIN dept USING(deptno);
=============== 3,4 => 반드시 같은 컬럼명이 존재
SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp NATURAL JOIN dept;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 82/12/09 3000 20 RESEARCH DALLAS
7839 KING PRESIDENT 81/11/17 5000 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 30 SALES CHICAGO
7876 ADAMS CLERK 7788 83/01/12 1100 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 81/12/03 950 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 ACCOUNTING NEW YORK
7935 Hong 20/08/11 10 ACCOUNTING NEW YORK
SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp JOIN dept USING(deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 82/12/09 3000 20 RESEARCH DALLAS
7839 KING PRESIDENT 81/11/17 5000 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 30 SALES CHICAGO
7876 ADAMS CLERK 7788 83/01/12 1100 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 81/12/03 950 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 ACCOUNTING NEW YORK
7935 Hong 20/08/11 10 ACCOUNTING NEW YORK= NON_EQUI_JOIN(Oracle JOIN 또는 ANSI JOIN) => 연산자 (=이 아닌 다른 연산자)
=> 비교연산자, BETWEEN ~ AND
EQUI_JOIN(JOIN 네개 다 가능) ==> 연산자 (=)
SQL> SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,grade FROM emp, salgrade WHERE sal BETWEEN losal AND hisal;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20 1
7900 JAMES CLERK 7698 81/12/03 950 30 1
7876 ADAMS CLERK 7788 83/01/12 1100 20 1
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 2
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 2
7934 MILLER CLERK 7782 82/01/23 1300 10 2
7844 TURNER SALESMAN 7698 81/09/08 1500 30 3
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 3
7782 CLARK MANAGER 7839 81/06/09 2450 10 4
7698 BLAKE MANAGER 7839 81/05/01 2850 30 4
7566 JONES MANAGER 7839 81/04/02 2975 20 4
7902 FORD ANALYST 7566 81/12/03 3000 20 4
7788 SCOTT ANALYST 7566 82/12/09 3000 20 4
7839 KING PRESIDENT 81/11/17 5000 10 5OUTER JOIN => 다른 테이블에서 데이터를 연결
= INNER JOIN+@(NULL) => NULL값 포함
SQL> SELECT e1.ename "본인" , e2.ename "사수" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;
본인 사수
-------------------- --------------------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
13 rows selected.
SQL> SELECT e1.ename "본인" , e2.ename "사수" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno(+);
본인 사수
-------------------- --------------------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING (null)
14 rows selected.
SELF JOIN => 같은 테이블에서 데이터 연결
emp : 사원정보
= empno : 사번
= ename : 이름
= job : 직위
= mgr : 사수번호
= hiredate : 입사일
= sal : 급여
= comm : 성과급
= deptno : 부서번호
dept : 부서정보
= deptno : 부서번호
= dname : 부서명
= loc : 근무지
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno,dname,loc FROM emp e, dept d
=> 테이블명이 길때 사용 => 별칭
SELECT empno,ename,job,mgr,hiredate,sal,comm,emp.deptno,dname,loc FROM emp, dept WHERE emp.deptno=dept.deptno;
=============================================
2. 오라클 연습문제
SELECT : 데이터 검색
= 형식)
* => 전체 데이터 검색
column~~ => 필요한 데이터 검색
=====================
SELECT * | column1, colum2
FROM 테이블명 (View명, SELECT~~)
===================== 필수
[
WHERE => 조건 검색 (if)
GROUP BY => 관련된 데이터를 모아서 별도로 처리
HAVING => GROUP BY에 조건
ORDER BY => 정렬 (올림차순(ASC), 내림차순(DESC))
]
WHERE 조건 (컬럼명 연산자 값)
연산자
=====
= 같다 => 문자열, 날짜, 숫자
ename = '값' (값 => 대소문자 구분)
hiredate = 'YY/MM/DD' => 날짜 형식
숫자 ename=7788, ename='7788' 둘다 가능 자동으로 형변환
= 같지않다 !=, ^=, <>
= 비교 <, >, <=, >= : 숫자, 문자열, 날짜
논리 연산자 : AND, OR
오라클에서 지원 연산자
==================
BETWEEN ~ AND : 범위, 기간 (예매가능한 날, 체크인..)
IN (OR가 많은 경우에 대체)
NULL 연산자 => 값이 NULL일 경우에는 연산처리가 안된다
============
=> IS NULL , IS NOT NULL
LIKE => 포함하는 문자열을 찾는 경우
% : 문자의 갯수를 모르는 경우
=> A%(시작)
=> %A(끝)
=> %A%(포함)
: 한글자
NOT (부정)
1) 직업과 직업 토탈 월급을 출력 직업별 토탈 월급이 4000이상 출력, 직업이 SALESMAN은 제외
SELECT job, SUM(sal) FROM emp
-- WHRE SUM(sal)>=4000; WHERE에서 집합함수를 사용할 수 없다
WHERE job!= 'SALESMAN'
GROUP BY job
HAVING SUM(sal)>=4000
ORDER BY 2 DESC; (job이 1, SUM(sal)이 2)
SQL> SELECT job, SUM(sal) FROM emp WHERE job <> 'SALESMAN'
GROUP BY job HAVING SUM(sal)>=4000 ORDER BY 2 DESC;
JOB SUM(SAL)
------------------ ----------
MANAGER 8275
ANALYST 6000
PRESIDENT 5000
CLERK 4150
2) 입사연도가 4자리 => 입사연도, 토탈 월급 (SUM)
콤마가 천단위로 찍히게
SQL> SELECT TO_CHAR(hiredate,'RRRR'), TO_CHAR(sum(sal),'99,999')
FROM emp GROUP BY TO_CHAR(hiredate,'RRRR');
TO_CHAR( TO_CHAR(SUM(SA
-------- --------------
1980 800
1983 1,100
1982 4,300
1981 22,825
3) 사원테이블의 사원수 몇명인지 조회
SQL> SELECT COUNT(*) from emp;
COUNT(*)
----------
15
4) 직업이 SALESMAN 제외, 직업별 인원수 3명 이상, 직업별 인원수 높은것부터 출력
SQL> SELECT job, COUNT(*) FROM emp WHERE job!='SALESMAN'
GROUP BY job HAVING COUNT(*) >=3 ORDER BY 2 DESC;
JOB COUNT(*)
------------------ ----------
CLERK 4
MANAGER 3
5) 부서번호, 부서별 평균 월급, 20번 제외
평균 급여가 2000이상, 높은 순 출력
SQL> SELECT deptno, ROUND(AVG(sal),2) FROM emp WHERE deptno!=20
GROUP BY deptno HAVING AVG(sal) >= 2000 ORDER BY 2 DESC;
DEPTNO ROUND(AVG(SAL),2)
---------- -----------------
10 2916.67
<JOIN> : 여러개의 테이블에서 필요한 데이터를 합쳐서 가지고 온다
조인 형식
==> INNER JOIN (교집합 => NULL값이 있는 경우에는 처리하지 않는다)
EUQI_JOIN , NON_EQUI_JOIN
연산자 (=) 연산자(비교연산자, BETWEEN~AND)
1. 오라클 조인 : 오라클에서만 적용되는 형식
SELECT A.col, B.col
FROM A, B
WHERE A.col = B.col;
2. ANSI 조인 : 모든 데이터베이스 표준화
SELECT A.col, B.col
FROM A JOIN B
ON A.col = B.col;
1) 직업 SALESMAN인 사원들의 이름, 월급, 직업, 부서위치 출력
SQL> SELECT ename, sal, job, loc FROM emp, dept
WHERE emp.deptno = dept.deptno AND job='SALESMAN';
ENAME SAL JOB LOC
-------------------- ---------- ------------------ --------------------------
TURNER 1500 SALESMAN CHICAGO
MARTIN 1250 SALESMAN CHICAGO
WARD 1250 SALESMAN CHICAGO
ALLEN 1600 SALESMAN CHICAGO
2) DALLAS에서 근무하는 사원의 이름, 부서위치, 부서명 ,월급
SQL> SELECT ename, loc, dname, sal FROM emp, dept WHERE emp.deptno = dept.deptno AND loc = 'DALLAS';
ENAME LOC DNAME SAL
-------------------- -------------------------- ---------------------------- ----------
SMITH DALLAS RESEARCH 800
JONES DALLAS RESEARCH 2975
SCOTT DALLAS RESEARCH 3000
ADAMS DALLAS RESEARCH 1100
FORD DALLAS RESEARCH 3000
3) 1981년도 입사한 사원들의 이름과 입사일과 부서위치
SQL> SELECT ename, hiredate, loc FROM emp, dept WHERE emp.deptno = dept.deptno
AND TO_CHAR(hiredate,'YYYY')=1981;
= SUBSTR(hiredate, 1, 2) = 81;
= hiredate LIKE '81%';
ENAME HIREDATE LOC
-------------------- -------- --------------------------
ALLEN 81/02/20 CHICAGO
WARD 81/02/22 CHICAGO
JONES 81/04/02 DALLAS
MARTIN 81/09/28 CHICAGO
BLAKE 81/05/01 CHICAGO
CLARK 81/06/09 NEW YORK
KING 81/11/17 NEW YORK
TURNER 81/09/08 CHICAGO
JAMES 81/12/03 CHICAGO
FORD 81/12/03 DALLAS
4) 부서위치, 부서위치별 토탈월급 (콤마)
SQL> SELECT loc, TO_CHAR(SUM(sal), '99,999') FROM emp,dept
WHERE emp.deptno=dept.deptno GROUP BY loc;
LOC TO_CHAR(SUM(SA
-------------------------- --------------
NEW YORK 8,750
CHICAGO 9,400
DALLAS 10,875
5) 급여 등급이 2등급인 사원들의 이름과 월급과 급여등급
SQL> SELECT empno, ename, job, hiredate, sal, grade FROM emp, salgrade s
WHERE sal BETWEEN s.losal AND s.hisal AND s.grade=2;
EMPNO ENAME JOB HIREDATE SAL GRADE
---------- -------------------- ------------------ -------- ---------- ----------
7521 WARD SALESMAN 81/02/22 1250 2
7654 MARTIN SALESMAN 81/09/28 1250 2
7934 MILLER CLERK 82/01/23 1300 2
SQL> SELECT empno, ename, job, hiredate, sal, grade FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal AND grade=2;
EMPNO ENAME JOB HIREDATE SAL GRADE
---------- -------------------- ------------------ -------- ---------- ----------
7521 WARD SALESMAN 81/02/22 1250 2
7654 MARTIN SALESMAN 81/09/28 1250 2
7934 MILLER CLERK 82/01/23 1300 2
6) 이름, 부서위치 출력. BOSTON도 출력(emp 테이블에 BOSTON 근무중인 사원없는상태)
SQL> SELECT ename, loc FROM emp, dept WHERE emp.deptno(+) = dept.deptno;
ENAME LOC
-------------------- --------------------------
SMITH DALLAS
ALLEN CHICAGO
WARD CHICAGO
JONES DALLAS
MARTIN CHICAGO
BLAKE CHICAGO
CLARK NEW YORK
SCOTT DALLAS
KING NEW YORK
TURNER CHICAGO
ADAMS DALLAS
JAMES CHICAGO
FORD DALLAS
MILLER NEW YORK
Hong NEW YORK
BOSTON값이 없는 쪽 (NULL값인 쪽에) (+)에 붙인다
WHERE emp.deptno(+) = dept.deptno; => RIGHT OUTER JOIN
WHERE emp.deptno = dept.deptno(+); => LEFT OUTER JOIN
셀프JOIN은 별칭을 줘야함
SQL> SELECT e1.empno "본인사번", e1.ename "본인 이름", e2.empno "사수사번", e2.ename "사수이름"
FROM emp e1, emp e2 WHERE e1.mgr=e2.empno(+);
본인사번 본인 이름 사수사번 사수이름
---------- -------------------- ---------- --------------------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7782 CLARK 7839 KING
7698 BLAKE 7839 KING
7566 JONES 7839 KING
7369 SMITH 7902 FORD
7839 KING
7) 사원이름, 자기의 사수이름 출력
SQL> SELECT e1.ename "본인이름", e2.ename "사수이름"
FROM emp e1, emp e2 WHERE e1.mgr=e2.empno;
본인이름 사수이름
-------------------- --------------------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
EmpVO private DeptVO dvo = new DeptVO();
package com.sist.join;
import java.util.Date;
public class EmpVO {
private int empno;
private String ename;
private String job;
private int mgr;
private Date hiredate;
private int sal;
private int comm;
private int deptno;
private DeptVO dvo = new DeptVO();
public DeptVO getDvo() {
return dvo;
}
public void setDvo(DeptVO dvo) {
this.dvo = dvo;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}
DeptVO
package com.sist.join;
public class DeptVO {
private int deptno;
private String dname;
private String loc;
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}
EmpDAO list.add(vo); vo.getDvo().setDname(rs.getString(6));
package com.sist.join;
import java.util.*;
import java.sql.*;
public class EmpDAO {
// 오라클 연결
private Connection conn;
// SQL문장을 오라클로 전송
private PreparedStatement ps;
// 오라클 주소
private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
// 드라이버 설치
public EmpDAO()
{
// 생성자 => 멤버변수의 초기화 , 네트워크 서버 연결
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch(Exception ex)
{
System.out.println(ex.getMessage());
}
}
// 연결
public void getConnection()
{
try
{
conn=DriverManager.getConnection(URL,"hr","happy");
// conn hr/happy
}catch(Exception ex) {}
}
// 연결 종료
public void disConnection()
{
try
{
if(ps!=null) ps.close();
if(conn!=null) conn.close();
}catch(Exception ex) {}
}
public ArrayList<EmpVO> empDeptJoinData(){
ArrayList<EmpVO> list = new ArrayList<EmpVO>();
try {
getConnection();
String sql="SELECT empno, ename, job, hiredate, sal, dname, loc "
+"FROM emp, dept "
+"WHERE emp.deptno=dept.deptno";
ps=conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery(); // 엔터(문장실행), 데이터는 rs안에 들어옴
while(rs.next()) {
EmpVO vo = new EmpVO();
vo.setEmpno(rs.getInt(1));
vo.setEname(rs.getString(2));
vo.setJob(rs.getString(3));
vo.setHiredate(rs.getDate(4));
vo.setSal(rs.getInt(5));
vo.getDvo().setDname(rs.getString(6));
vo.getDvo().setLoc(rs.getString(7));
list.add(vo);
}
}catch(Exception ex) {
System.out.println(ex.getMessage());
}finally {
disConnection();
}
return list;
}
}
EmpServlet out.println("<th>+vo.getDvo().getDname()+"</th>");
package com.sist.join;
import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
@WebServlet("/EmpServlet")
public class EmpServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
// run()
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=EUC-KR");
PrintWriter out = response.getWriter(); //tomcat 웹서버 doGet호출
EmpDAO dao = new EmpDAO();
ArrayList<EmpVO> list = dao.empDeptJoinData();
out.println("<html>");
out.println("<body>");
out.println("<center>");
out.println("<h1>사원 정보</h1>");
out.println("<table border=1 width=700>");
out.println("<tr>");
out.println("<th>사번</th>");
out.println("<th>이름</th>");
out.println("<th>직위</th>");
out.println("<th>입사일</th>");
out.println("<th>급여</th>");
out.println("<th>부서명</th>");
out.println("<th>근무지</th>");
out.println("</tr>");
for(EmpVO vo:list) {
out.println("<tr>");
out.println("<th>"+vo.getEmpno()+"</th>");
out.println("<th>"+vo.getEname()+"</th>");
out.println("<th>"+vo.getJob()+"</th>");
out.println("<th>"+vo.getHiredate().toString()+"</th>");
out.println("<th>"+vo.getSal()+"</th>");
out.println("<th>"+vo.getDvo().getDname()+"</th>");
out.println("<th>"+vo.getDvo().getLoc()+"</th>");
out.println("</tr>");
}
out.println("</table>");
out.println("</center>");
out.println("</body>");
out.println("</html>");
}
}
'Back-end > Oracle' 카테고리의 다른 글
| Oracle 9일차 - DML(Data Manipulation Language) (0) | 2020.08.14 |
|---|---|
| Oracle 8일차 - OUTER JOIN, DDL(Data Definition Language) (0) | 2020.08.13 |
| Oracle 6일차 - SQL 복수행함수2 (0) | 2020.08.11 |
| Oracle 5일차 - SQL 복수행함수 (0) | 2020.08.10 |
| Oracle 4일차 - SQL 단일행함수2 / 지니뮤직 (0) | 2020.08.07 |
