#1
--1. 30번 부서 사원들의 직위, 이름, 월급을 담는 VIEW를 만들어라.
SQL> CREATE VIEW MY01
2 AS
3 SELECT JOB, ENAME, SAL FROM EMP
4 WHERE DEPTNO = 30;
--2. 30번 부서 사원들의 직위, 이름, 월급을 담는 VIEW를 만드는데,
-- 각각의 컬럼명을 직위, 사원이름, 월급으로 ALIAS를 주고 월급이
-- 300보다 많은 사원들만 추출하도록 하라.
--3. 부서별 최대월급, 최소월급, 평균월급을 담는 VIEW를 만들어라.
--4. 부서별 평균월급을 담는 VIEW를 만들되, 평균월급이 2000 이상인
-- 부서만 출력하도록 하라.
SQL> CREATE VIEW MY01
2 AS
3 SELECT AVG(SAL) A
4 FROM EMP
5 GROUP BY DEPTNO;
-> 테이블은 별칭을 줄필요가 딱히 없지만, 그룹함수를 뷰에서 사용하기 위해서는 별칭을 주어야 한다.
-> 뷰는 생성할 당시에 별칭을 줄 수 있다
-> 뷰는 SELECT를 묶은 것(쿼리 뭉치)
-> FROM WHERE GROUP HAVING SELECT ORDER BY 외우기
COMMAND LINE에 쓰는 순서
SELECT -> FROM -> WHERE -> HAVING -> GROUP -> ORDER
EXECUTE 순서
FROM -> WHERE -> GROUP -> HAVING -> SELECT -> ORDER
--5. 직위별 총월급을 담는 VIEW를 만들되, 직위가 MANAGER인
-- 사원들은 제외하고 총월급이 3000이상인 직위만 출력하도록 하라.
HAVING = 그룹지어진 애들에 관해서 WHERE절 같은 역할을 함
SQL> CREATE VIEW MY01
2 AS
3 SELECT SUM(SAL) "총월급", JOB "직위"
4 FROM EMP
5 WHERE JOB != 'MANAGER'
6 HAVING SUM(SAL) >= 3000
7 GROUP BY JOB;
--6. 30번 부서 사원의 부서번호, 사원번호, 사원이름, 월급을 담는
-- VIEW를 작성하되 다른 부서에서 근무하는 사원들의 레코드는
-- 입력 및 수정하지 못하도록 WITH CHECK OPTION을 사용하라.
SQL> CREATE VIEW MY01
2 AS
3 SELECT DEPTNO "부서번호", EMPNO "사원번호", ENAME "사원이름", SAL "월급"
4 FROM EMP
5 WHERE DEPTNO = 30 WITH CHECK OPTION;
--7. 20번 부서에서 근무하는 사원의 이름, 월급, 커미션, 부서번호를
-- 담는 VIEW를 작성하되, 그 누구도 VIEW의 정의를 보지 못하도록 하라.
CREATE VEIW MY01(사원이름, 월급, 커미션, 부서번호)
AS
SELECT ENAME, SAL, NVL(COMM, 0), DEPTNO
FROM EMP
WHERE DEPTNO = 20
WITH READ ONLY;
--8. 7번 문제에서 만든 VIEW를 20번 부서에서 근무하는 사원의 이름과
-- 월급만 담는 VIEW가 되도록 수정하고 다시 암호화하여라.
CREATE OR REPLACE VIEW VIEW01(사원이름, 월급)
AS
SELECT ENAME, SAL
FROM EMP
WHRE DEPTNO = 20
WITH READ ONLY;
-> VIEW는 ALTER할 수 없다.
-- 9. 위의 VIEW를 삭제하라.
#2
JDBC = JAVA + DB
DB의 PROCESS
연결 -> SQL 구문실행 -> 결과확인(SELECT / INSERT, DELETE, UPDATE) -> TRANSACTION -> 종료
1) 연결
ⓐ DB 설치 / SQLPLUS 실행 => Class.forName("java.lang.Thread")
ⓑ ID / PW 입력 => Connection con = DriverManager.getConnection(String url, Properties info);
2) SQL 구문 실행 & 결과확인(SELECT / INSERT, DELETE, UPDATE)
ⓐ SELECT -> 리턴 테이블
ⓑ INSERT INTO TBNAME -> INT로 리턴
UPDATE TBNAME SET
DELETE FROM TBNAME
3) Transaction
ROLLBACK;
COMMIT;
4) 종료
#3
ⓐ
JAVA API 문서 참고!
- JAVA.LANG PACKAGE -> CLASS -> forName Method 찾아보기
- JAVA.SQL PACKAGE -> DRIVER CLASS -> Driver manager Class 찾아보기
ⓑ
- OJDBC14.JAR -> ORACLE.JDBC.DRIVER -> ORACLEDRIVER.CLASS -> Class.forName("oracle.jdbc.driver.OracleDriver") 실행
접속되기 전까지의 SQL상태
- Connection con = DriverManager.getConnection(" jdbc:oracle:thin:@127.0.0.1:1521:XE " , " BIGDATA ", " ADMIN1234 ") throws SQLException
접속을 위한 SQL 상태
=> JAVA로 구현
public class DbConTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "bigdata", "admin1234");
if (!con.isClosed()) {
System.out.println("연결했어 : " + !con.isClosed());
} else {
System.out.println("연결 실패야");
}
con.close();
}
}
ⓒ import vs forName
import : 실제 클래스를 생성
forName : 메모리는 실행시키지 않고 클래스를 참조만 하고 싶은 경우.
ⓓ C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN에서 listener, tnsnames.ora 만 건들기
#4
ⓐ #3의 파일을 복사한 DbConTest01파일에서 url, id, pw로 지정
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
String id = "bigdata";
String pw = "admin1234";
Connection con = DriverManager.getConnection(url, id, pw);
ⓔ DbConTest파일을 복사한 DbConTest03파일을 다음과 같이 수정한다.(try ~ catch)
package com.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DbConTest03 {
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection con = null;
try {
DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "bigdata", "admin1234");
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (!con.isClosed()) {
System.out.println("연결했어 : " + !con.isClosed());
} else {
System.out.println("연결 실패야");
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
ⓕ SQL 구문 실행 : SELECT ENAME, SAL FROM EMP;
Statement stmt = con.createStatement(); // SQL >
ResultSet rs = stmt.executeQuery("SELECT ENAME, SAL FROM EMP");
while(rs.next()){
System.out.println(rs.getString(1) + ":" + rs.getDouble("SAL"));
}
ⓖ DBConTest04.java파일을 구성한다.
package com.test;
import java.sql.*;
public class DBConTest04 {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "bigdata", "admin1234");
String sql = "SELECT ENAME, SAL FROM EMP";
Statement stmt = con.createStatement(); // SQL >
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString(1) + ":" + rs.getDouble(2));
}
rs.close(); // close는 역순으로
stmt.close();
con.close();
}
}
다음과 같이 구현한다.
package common;
//DB 연결 / DB 종료 /
import java.sql.*;
import java.util.Properties;
// 웹페이지에서 유저가 1페이지에도 있을 수 있고 2 3페이지에도 있을 수 있으므로
// static 변수를 사용한다.
public class JDBCTemplate {
public static Connection getConnection() {
// DB 연결
// 위임하면 안됨 try catch로 할 것
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
String id = "user";
String pw = "password";
Properties pro = new Properties();
pro.put(id, "bigdata");
pro.put(pw, "admin1234");
Connection conn = null;
// 위임하면 안됨
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", pro);
conn.setAutoCommit(false); // AutoCommit 해제
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// DB 종료
public static void Close(Connection con) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
ⓘ com.test02 패키지를 만든 뒤 DbTest.java 파일을 만들어 구현한다.
package com.test02;
import java.sql.*;
import common.*;
public class DbTest {
public static void main(String[] args) {
Connection con = JDBCTemplate.getConnection();
System.out.println("연결했어");
JDBCTemplate.Close(con);
}
}
이를 이용하여
DbTest.java파일과 DBConTest04.java 파일을 바꿔준다.
- DbTest.java의 경우
package com.test02;
import java.sql.*;
import static common.JDBCTemplate.*;
public class DbTest {
public static void main(String[] args) {
Connection con = getConnection();
System.out.println("연결했어");
Close(con);
}
}
ⓚ 이를 이용하여 DBTest02.java파일을 만들어 DEPT내용을 출력할 수 있다.
package com.test02;
import static common.JDBCTemplate.*;
import java.sql.*;
public class DbTest02 {
// DEPT 테이블의 내용을 출력해보자.
public static void main(String[] args) throws Exception {
Connection con = getConnection();
String sql = "SELECT * FROM DEPT";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt(1) + " : " + rs.getString(2) + " : " + rs.getString(3));
}
rs.close();
stmt.close();
Close(con);
}
}
ⓛ JDBCTemplate.java를 수정해준뒤 DBTest02.java파일을 수정해준다.
- JDBCTemplate.java 파일
package common;
import java.sql.*;
import java.util.Properties;
/* NAME : JDBCTemplate
* DESC : DB를 읽어오고 반환하는 등 기능을 수행하는 Utility 클래스
* DB 연결 / DB 종료
*/
public class JDBCTemplate {
public static Connection getConnection() {
// DB 연결
// 위임하면 안됨 try catch로 할 것
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e1) {
e1.printStackTrace();
}
String id = "user";
String pw = "password";
Properties pro = new Properties();
pro.put(id, "bigdata");
pro.put(pw, "admin1234");
Connection conn = null;
// 위임하면 안됨
try {
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", pro);
conn.setAutoCommit(false); // AutoCommit 해제
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// DB 종료
public static void Close(Connection con) {
// DB와 Connect 되었는지 여부를 Return
if (isConnection(con)) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static boolean isConnection(Connection conn) {
boolean valid = true;
try {
if (conn == null || conn.isClosed()) {
valid = false;
}
} catch (SQLException e) {
valid = true;
e.printStackTrace();
}
return valid;
// Statement Close
}
public static void Close(Statement stmt) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// ResultSet Close
public static void Close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- DBTest02.java파일
package com.test02;
import static common.JDBCTemplate.*;
import java.sql.*;
public class DbTest02 {
// DEPT 테이블의 내용을 출력해보자.
public static void main(String[] args) throws Exception {
Connection con = getConnection();
String sql = "SELECT * FROM DEPT";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt(1) + " : " + rs.getString(2) + " : " + rs.getString(3));
}
Close(rs);
Close(stmt);
Close(con);
}
}
ⓞ DBTest04 파일을 만들어 다음과 같이 입력한다.
package com.test02;
import java.sql.*;
import static common.JDBCTemplate.*;
public class DbTest03{
public static void main(String[] args) throws Exception{
Connection con = getConnection();
int id = 2;
String sname = "홍길동";
String sql = "INSERT INTO TEST VALUES(?, ?)";
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setString(2, sname);
int r = pstmt.executeUpdate();
if(r > 0) {
System.out.println("입력성공헀어");
}
Close(pstmt);
Close(con);
}
}
'국비교육 > JAVA' 카테고리의 다른 글
| 외워야 할 목록 (0) | 2019.01.21 |
|---|---|
| 1월 17일 국비교육 (0) | 2019.01.17 |
| 1월 16일 국비교육 (0) | 2019.01.16 |
| 1월 15일 국비교육 33일차 (0) | 2019.01.15 |
| 1월 14일 국비교육 32일차 (0) | 2019.01.14 |
댓글