[새싹] 현대IT&E 231103 기록 - Oracle

최정윤·2023년 11월 3일

oracle sql

새싹

목록 보기

14/67

리마인딩

DDL

create: 새로운 테이블을 생성
alter: 기존 테이블 구조 변경
drop: 기존 테이블 삭제
truncate: 기존 테이블 초기화
rename: 기존 테이블 이름 변경

DML

select: 저장된 데이터를 조회
insert: 새로운 데이터를 저장
update: 저장된 데이터를 수정
delete: 저장된 데이터를 삭제

DCL

grant: 유저에게 권한을 부여
revoke: 유저로부터 권한을 회수

TCL

commit: 올바르게 완료한 작업으로 인한 데이터를 데이터베이스에 영구적으로 반영
rollback: 작업 시작 이전의 상태로 되돌림
savepoint: 저장점을 지정, 이후 rollback과 함께 사용하여 특정지점까지 rollback이 가능

[참고링크]

https://icedhotchoco.tistory.com/entry/SQL-DDL-DML-DCL-TCL

계층형 쿼리

-- ace 접속
select last_name, level from employees;
start with last_name = 'King';
connect by manager_id = prior employee_id;

describe employees;

SELECT employee_id, manager_id,
LEVEL,
CONNECT_BY_ROOT last_name AS 대표, CONNECT_BY_ISLEAF AS 막내, sys_connect_by_path(last_name, '/') AS PATH, lpad(' ', (LEVEL - 1) * 3) || last_name AS last_name, PRIOR last_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY manager_id = PRIOR employee_id
ORDER SIBLINGS BY employee_id ASC;

select department_id, job_id, null as manager_id, avg(salary) as avgsal 
from employees
group by department_id, job_id
union all
select null, job_id, manager_id, avg(salary) as avgsal 
from employees
group by job_id, manager_id;

select department_id, job_id, manager_id, avg(salary)
from employees
group by grouping sets ((department_id, job_id),(job_id, manager_id));

GROUPING SETS

-- tester1 접속
SELECT deptno, job, NULL, avg(sal) FROM emp
GROUP BY deptno, job
UNION ALL
SELECT NULL, job, mgr, avg(sal) FROM emp
GROUP BY job, mgr;

SELECT deptno, job, mgr, avg(sal)
FROM emp
GROUP BY GROUPING SETS ((deptno, job),(job,mgr));

WITH 절

동일 테이블 접근을 최소화하며 메모리에 생성된 임시 테이블에서 필요한 데이터를 메모리로 접 근하기 때문에 디스크 IO로 테이블에 접근하는 것보다 성능을 개선할 수 있습니다.
WITH 절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름 을 부여하여 재사용할 수 있게 함으로써 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이 름을 부여해서 Query Block을 만듭니다. 자주 실행되는 경우 한 번만 Parsing 되고 Plan 계획이 수립되므로 쿼리의 성능 향상에 도움이 됩니다.

-- tester1 접속
-- with 절 사용 전
select 'sum', sum(sum_sal)
from (select deptno, sum(sal) as sum_sal, count(*) as cnt
from emp
      group by deptno)
union all
select 'avg', sum_sal/cnt
from (select deptno, sum(sal) as sum_sal, count(*) as cnt
from emp
group by deptno);

-- with 절 사용 후
with a as (select deptno, sum(sal) as sum_sal, count(*) as cnt from emp
           group by deptno)
select 'sum', sum(sum_sal)
from a
union all
select 'avg', sum_sal/cnt from a;

여러번 사용되는 문장을 with로 만들면 결과가 임시 테이블로 저장되어 사용되므로 성능상의 이익이 있다.
하나의 뭐리 내에서는 여러 번 반복 사용하는 문장의 임시 테이블로 저장되어 사용되지만 다시 쿼리 던질 때는 다시 임시 테이블을 만들기 때문에 다시 쿼리 할 때는 임시 테이블을 사용하는 효과가 없다.

-- tester1
WITH
DEPT_COSTS AS (SELECT d.deptno,
                 SUM(E.SAL) AS DEPT_TOTAL
                 FROM EMP E, DEPT D
                 WHERE E.DEPTNO = D.DEPTNO
                 GROUP BY D.DEPTNO),
 AVG_COST AS (SELECT SUM(DEPT_TOTAL) / COUNT(*)
 AS DEPT_AVG
 FROM DEPT_COSTS)
 SELECT * FROM DEPT_COSTS
 WHERE DEPT_TOTAL > (SELECT DEPT_AVG FROM AVG_COST)
 ORDER BY DEPTNO;

Multiple Column 서브쿼리

-- tester1 접속
CREATE TABLE fruits ( 
name varchar2(20),
grade varchar2(1));

INSERT INTO fruits VALUES ('딸기', 'A');
INSERT INTO fruits VALUES ('딸기', 'B');
INSERT INTO fruits VALUES ('바나나', 'A');
INSERT INTO fruits VALUES ('바나나', 'B');
INSERT INTO fruits VALUES ('바나나', 'C');
commit;


CREATE TABLE purchases ( 
name varchar2(20),
grade varchar2(1));

INSERT INTO purchases VALUES ('딸기', 'A');
INSERT INTO purchases VALUES ('딸기', 'B');
INSERT INTO purchases VALUES ('바나나', 'A');
INSERT INTO purchases VALUES ('바나나', 'B');
INSERT INTO purchases VALUES ('바나나', 'C');
commit;


SELECT *
FROM fruits
WHERE name in (SELECT name FROM purchases) 
AND grade in (SELECT grade FROM purchases);

SELECT *
FROM fruits
WHERE name in ('딸기','바나나') AND grade in ('A','B');

SELECT *
FROM fruits
WHERE (name = '딸기' or name = '바나나') 
AND (grade= 'A' or grade= 'B');

SELECT *
FROM fruits
WHERE (name,grade) in (SELECT name,grade FROM purchases);

SELECT *
FROM fruits
WHERE (name,grade) not in (SELECT name,grade FROM purchases);

SELECT deptno, min(sal) 
FROM emp
GROUP BY deptno;

SELECT ename, sal, deptno FROM emp
WHERE (deptno, sal) in (SELECT deptno, min(sal)
FROM emp
GROUP BY deptno);

CREATE TABLE test1 (
EMPNO NUMBER(4)
ENAME VARCHAR(30)
HP VARCHAR(11)
SAL NUMBER
DEPTNO NUMBER(2));

CREATE TABLE test2 (
DEPTNO NUMBER(2)
DNAME VARCHAR2(30)
LOC VARCHAR2(10)
));

-- 사원번호는 not null + unique
-- 사원명은 not null
-- 휴대폰은 unique
-- 급여는 100만원 이상
-- 사원 테이블의 부서번호는 부서테이블에 존재해야 함...

-- 컬럼레벨 constraints
create table t_dept (
deptno number(2)    constraint t_dept_deptno_pk primary key,
dname varchar2(30)  constraint t_dept_dname_nn not null,
loc varchar2(10)
);

create table t_emp (
empno number(4)     constraint t_emp_empno_pk primary key,
ename varchar2(30)  constraint t_emp_ename not null,
hp varchar2(11)     constraint t_emp_hp_uk unique,
sal number          constraint t_emp_sal_ck check(sal >= 100),
deptno number(2)    constraint t_emp_deptno_fk references t_dept(deptno)
);

-- 컬럼레벨 Constraints에 제약조건 이름 추가 
drop table t_emp purge;
drop table t_dept purge;


-- 테이블 레벨 constraints
create table t_dept (
deptno number(2),
dname varchar2(30),
loc varchar2(10),
constraint t_dept_deptno_pk primary key(deptno),
constraint t_dept_dname_nn check(dname is not null)
);

create table t_emp (
empno number(4),
ename varchar2(30),
hp varchar2(11),
sal number,
deptno number(2),
constraint t_emp_empno_pk primary key(empno),
constraint t_emp_ename_nn check(ename is not null),
constraint t_emp_hp_uk unique(hp),
constraint t_emp_sal_ck check(sal >= 100),
constraint t_emp_deptno_fk foreign key(deptno) references t_dept(deptno)

JDBC 사용해보기

https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/
https://m-ur-phy.tistory.com/entry/M1-%EC%98%A4%EB%9D%BC%ED%81%B4-%EC%9D%B4%ED%81%B4%EB%A6%BD%EC%8A%A4-%EC%97%B0%EB%8F%99%ED%95%98%EA%B8%B0

jdbcTest1.java

package jdbc;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.ResultSet;

public class JdbcTest1 {
	public static void main(String[] args) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			System.out.println("jdbc driver 로드");
		} catch (Exception e) {
			System.out.println("드라이버 로드 실패");
		}
		System.out.println("종료");
	}
}

jdbcTest2.java

package jdbc;

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

public class JdbcTest1 {
	public static void main(String[] args) {
//		Connection conn = null;
//		Statement stmt = null;
//		ResultSet rs = null;
		String sql = "SELECT employee_id, last_name, salary FROM employees";
		try (Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xepdb1", "ace", "ace");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql)){
			
//			Class.forName("oracle.jdbc.OracleDriver");
//			conn = DriverManager.getConnection(
//					"jdbc:oracle:thin:@localhost:1521/xepdb1", "ace", "ace");
//			stmt = conn.createStatement();
//			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				System.out.println(rs.getInt("employee_id") + "\t");
				System.out.println(rs.getString("last_name") + "\t");
				System.out.println(rs.getLong("salary") + "\t");
			}
		} catch (Exception e) {
			System.out.println("실패:" + e.getMessage());
		} 
//		finally {
//			if (rs != null) try {rs.close();} catch(Exception e) {}
//			if (stmt != null) try {stmt.close();} catch(Exception e) {}
//			if (conn != null) try {conn.close();} catch(Exception e) {}
//		}
//		System.out.println("종료");
	}
}

jdbcTest3.java

package jdbc;

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

public class JdbcTest3 {
	public static void main(String[] args) {
		String sql = "SELECT employee_id, last_name, salary FROM employees" +
				"FROM employees " +
				"WHERE department_id >= ?";
		
		try (Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/xepdb1", "ace", "ace");
			PreparedStatement stmt = conn.prepareStatement(sql)){
			stmt.setInt(1, 80);
			
			try (ResultSet rs = stmt.executeQuery(sql)){
			
				while(rs.next()) {
					System.out.println(rs.getInt("employee_id") + "\t");
					System.out.println(rs.getString("last_name") + "\t");
					System.out.println(rs.getLong("salary") + "\t");
				}
			}
		} catch (Exception e) {
			System.out.println("실패:" + e.getMessage());
		}
		System.out.println("종료");
	}
}

https://mvnrepository.com/
commons-codec 검색 > 1.16.0 선택 > jar 파일 다운로드

LoginTest1.java

package jdbc;

import org.apache.commons.codec.digest.DigestUtils;

public class LoginTest1 {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		System.out.println(DigestUtils.sha512Hex("1234"));
	}

}

d404559f602eab6fd602ac7680dacbfaadd13630335e951f097af3900e9de176b6db28512f2e000b9d04fba5133e8b1c6e8df59db3a8ab9d60be4b97cc9e81db

ace.sql

create table members(
userid varchar2(20) primary key,
userpw varchar2(256) not null);


insert into members
values('xci', 'd404559f602eab6fd602ac7680dacbfaadd13630335e951f097af3900e9de176b6db28512f2e000b9d04fba5133e8b1c6e8df59db3a8ab9d60be4b97cc9e81db');
commit;

로그인 구현해보기

LoginTest0.java -> 비번 1234에 대한 코드값 출력

package jdbc;

import org.apache.commons.codec.digest.DigestUtils;

public class LoginTest0 {

	public static void main(String[] args) {
		System.out.println(DigestUtils.sha512Hex("1234"));
	}

}

ace.sql -> db에 id, pw, name 값 저장

create table members(
userid varchar2(20) primary key,
userpw varchar2(256) not null);


insert into members
values('xci', 'd404559f602eab6fd602ac7680dacbfaadd13630335e951f097af3900e9de176b6db28512f2e000b9d04fba5133e8b1c6e8df59db3a8ab9d60be4b97cc9e81db');
commit;
alter table members
add (name varchar2(20));
update members set name='최정윤' where userid='xci';
commit;

DELETE FROM members
WHERE userid = 'xci';
COMMIT;
SELECT * FROM members;

LoginTest1.java -> 비밀번호를 몰라도 로그인되는 오류가 발생.

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

import org.apache.commons.codec.digest.DigestUtils;

public class LoginTest1 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.print("id : ");
		String userid = sc.nextLine();
		System.out.print("pw : ");
		String userpw = DigestUtils.sha512Hex(sc.nextLine());
		
		String sql = "SELECT userid, name " +
					"FROM members " +
					"WHERE userid='" + userid + "' and userpw='" + userpw + "' ";
		
//		String sql = "SELECT employee_id, last_name, salary FROM employees";
		try (Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/xepdb1", "ace", "ace");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql)){
			
			if(rs.next()) {
				System.out.println(
						rs.getString("name") + "(" +
						rs.getString("userid") + ") 님이 로그인 하였습니다.");
			} else {
				System.out.println(
						"아이디 혹은 비번이 틀립니다.");
//				System.out.println(rs.getString("last_name") + "\t");
//				System.out.println(rs.getLong("salary") + "\t");
			}
		} catch (Exception e) {
			System.out.println("실패:" + e.getMessage());
		}
		
		System.out.println(DigestUtils.sha512Hex("1234"));
	}

}

LoginTest1.java -> 비밀번호를 몰라도 로그인되는 오류 해결.

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

import org.apache.commons.codec.digest.DigestUtils;

public class LoginTest1 {

	public static void main(String[] args) {
		Scanner sc = new Scanner(System.in);
		System.out.print("id : ");
		String userid = sc.nextLine();
		System.out.print("pw : ");
		String userpw = DigestUtils.sha512Hex(sc.nextLine());
		
//		쿼리 작성이 용이하다.
//		성능이 향상된다.
//		SQL Injection에 강해짐.
		String sql = "SELECT userid, name " +
					"FROM members " +
					"WHERE userid=? and userpw=? ";
		
//		String sql = "SELECT employee_id, last_name, salary FROM employees";
		try (Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@localhost:1521/xepdb1", "ace", "ace");
			PreparedStatement stmt = conn.prepareStatement(sql)){
			
			stmt.setString(1,  userid);
			stmt.setString(2, userpw);
			
			try (ResultSet rs = stmt.executeQuery()){
			
				if(rs.next()) {
					System.out.println(
							rs.getString("name") + "(" +
							rs.getString("userid") + ") 님이 로그인 하였습니다.");
				} else {
					System.out.println(
							"아이디 혹은 비번이 틀립니다.");
	//				System.out.println(rs.getString("last_name") + "\t");
	//				System.out.println(rs.getLong("salary") + "\t");
				}
			}
		} catch (Exception e) {
			System.out.println("실패:" + e.getMessage());
		}
		System.out.println(DigestUtils.sha512Hex("1234"));
	}

}