728x90
반응형
오늘도 역시 DB를 배웠다. 끝나고 바로 복습을 하지 않으면 까먹을것 같아서 그냥 지금 바로 해버려야 겠다.
오전 내내 오라클 문법에 대해서 공부했는데 그렇게 어렵진 않았다.
자바 문법이랑 비슷한 부분도 있고, 아예 다른 부분도 있긴 했는데, 여튼 연습한 내용을 아래 적어보도록 하겠다.
-- 2021-02-09 SQL 단일행 함수 => row단위로 처리
/*
오라클 : 비절차적 언어
1
2
3 Error 입고
4 재고
5
함수 = (오라클에서 제공 , 사용자 정의 함수 => PL ) => 재사용, 소스의 중복 방지 (모든 데이터를 페이징화)
= FUNCTION : 리턴형이 존재
= PROCEDURE : 리턴형이 없는 함수 (void)
단일행 함수 (69 page)
=========
1) 문자 함수 (문자,문자열)
= 변환 함수
1. 대문자 변환 UPPER('oracle') => ORACLE
CREATE FUNCTION UPPER(data VARCHAR2):VARCHAR2
2. 소문자 변환 LOWER('ORACLE') => oracle
3. 이니셜 변환 INITCAP('oracle') => Oracle
= 기타 함수
1. LENGTH : 문자의 갯수(한글도 동일) LENGTH('ABC') => 3 , LENGTH('홍길동') => 3
= 제어 함수
1. 문자열 결합 : CONCAT , ||
CONCAT('Hello','Oracle') => HelloOracle
'Hello'||'Oracle' => HelloOracle
=============================
String s1="Hello";
String s2="Java";
s1.concat(s2) => HelloJava
s1+s2
============================
*** 2. 문자 자르기 : SUBSTR()
SUBSTR('1234567890',시작점,갯수) => 자바는 문자 시작번호 0,오라클 1
SUBSTR('1234567890',3,5) => 34567
양수 (시작점) : 왼쪽
SUBSTR('1234567890',-3,2) => 89
음수 (시작점) : 오른쪽
3. 문자 위치 찾기 : INSTR()
INSTR('A-B-C-D','-',1,3) => indexOf
=> 6
INSTR('A-B-C-D','-',-1,3) => lastIndexOf
=> 2
4. 공백문자 제거 : LTRIM() , RTRIM() , TRIM() => 공백 포함 특정문자 제거 (좌우만 가능)
5. 문자 추가 : LPAD() , *** RPAD()
2) 숫자 함수 (정수,실수)
= ROUND() : 반올림 원형 : ROUND(실수,소수점) ROUND(67.897,2) => 76.90
= TRUNC() : 버림 : TRUNC(실수,소수점) TRUNC(67.897,2) => 76.89
= *** CEIL() : 올림 : CEIL(67.1) => 68 => 총페이지
= MOD() : 나머지 (%) : MOD(5,2) => 5%2 = 1
3) 날짜 함수
= *** SYSDATE
= *** MONTHS_BETWEEN
= ADD_MONTHS
= LAST_DAY
= NEXT_DAY
4) 변환 함수 (문자열 , 숫자,날짜)
= *** TO_CHAR => 댓글(시간)
= TO_NUMBER
= TO_DATE
= ASCII
5) 기타
= *** NVL
= NVL2
= DECODE (다중 if)
= CASE (switch~case)
6) 정규식 함수
*/
-- 문자 함수 (UPPER(toUpperCase()),LOWER(toLowerCase()),ININCAT)
SELECT ename "이름",UPPER(ename) "대문자변환",LOWER(ename) "소문자변환",INITCAP(ename) "이니셜변환"
FROM emp;
SELECT ename,job
FROM emp
WHERE ename=UPPER('king');
-- 문자 함수 (SUBSTR) : 문자 추출 (중요한 함수 : 자주 사용되는 함수)
-- SUBSTR(문자열,시작위치,갯수) : 양수(왼쪽) , 음수(오른쪽) , 오라클 문자 시작번호는 1번이다
SELECT SUBSTR('1234567890',5,3) FROM DUAL;
-- 12345678910
SELECT SUBSTR('1234567890',-5,3) FROM DUAL;
-- -3-2-1
SELECT SUBSTR(ename,1,2) FROM emp;
SELECT RPAD(SUBSTR(ename,1,2),LENGTH(ename),'*') FROM emp; -- ID찾기
-- 1980년에 입사한 사원의 모든 정보
SELECT *
FROM emp
WHERE SUBSTR(hiredate,1,2)=81;
-- 문자 위치 찾기 INSTR(문자열,찾는문자,시작위치,번째) => 번째생략 => 1 (앞=> 양수 , 뒤:음수)
SELECT INSTR('A-B-C-D','-',1,3) FROM DUAL;
-- 앞 indexOf
SELECT INSTR('A-B-C-D','-',-1,3) FROM DUAL;
-- 뒤 lastIndexOf
SELECT ename,INSTR(ename,'A',1,2) FROM emp;
-- TRIM() => 공백 제거 , 특정문자 제거
-- LTRIM('문자열','제거할문자') => 왼쪽에 있는 문자만 제거가 가능
-- LTRIM('문자열') => 공백 제거
SELECT LTRIM('AAAAAAAAABACDEFG ','A') FROM DUAL;
SELECT RTRIM(' ABCDEFG','G') FROM DUAL;
SELECT LENGTH(' ABC '),LENGTH('ABC') FROM DUAL;
SELECT TRIM(' ' FROM ' ABC ') FROM DUAL;
SELECT ename,TRIM('S' FROM ename) FROM emp;
-- RPAD , LPAD
-- LPAD(문자열,문자갯수,채울문자) => 왼쪽에 채운다
-- LPAD('ABC',7,'#') ####ABC
-- RPAD('ABC',7,'#') ABC#### => ID찾기,비밀번호 찾기
-- LPAD('ABCDEFG',5,'*') ABCDE
SELECT ename,LENGTH(ename),RPAD(ename,5,'*') FROM emp;
-- 문자열 : LENGTH , SUBSTR , RPAD
-- 숫자 함수 : MOD
SELECT MOD(5,2) FROM DUAL; --5%2
-- emp에서 사번이 짝수인 사원의 모든 정보 출력
SELECT *
FROM emp
WHERE MOD(empno,2)=0;
SELECT CEIL(COUNT(*)/10) FROM emp;
-- COUNT(*) => emp의 총갯수 14/10 => 1.4 => 2
-- ROUND (세금) , TRUNC(퇴직금)
SELECT ROUND(67.894,2),TRUNC(67.894,2) FROM DUAL;
-- 날짜 : SYSDATE => 시스템의 날짜(시간포함) => 숫자형
-- 날짜 등록 (게시판,회원가입,예매일...)
SELECT SYSDATE-1 "어제",SYSDATE "오늘",SYSDATE+1 "내일" FROM DUAL;
-- MONTHS_BETWEEN => 기간의 개월수를 읽어 온다
-- 형식 MONTHS_BETWEEN(현재날짜,전날짜)
-- emp에 있는 사원 => 몇개월 일했는지 확인 => 시간 + 분 => 소수점이 출력 할 수 있다
SELECT ename , hiredate , ROUND(MONTHS_BETWEEN(SYSDATE,hiredate),0) "근무개월",
TRUNC(ROUND(MONTHS_BETWEEN(SYSDATE,hiredate),0)/12,0) "근무년"
FROM emp;
-- ADD_MONTHS => 개월수 등록 원형: ADD_MONTHS(날짜,개월수) => SYSDATE,6
SELECT SYSDATE , ADD_MONTHS('20/12/22',5) FROM DUAL;-- 적금 , 보험
-- NEXT_DAY , LAST_DAY , ROUND , TRUNC
-- NEXT_DAY : 요일
SELECT SYSDATE,NEXT_DAY(SYSDATE,'수') FROM DUAL;
-- LAST_DAY : 등록한 달의 마지막 날
-- '21/08/01' => 21/08/31
SELECT SYSDATE,LAST_DAY('21/12/01') FROM DUAL;
많은 함수들이 있지만 그중에서 웹에서 많이 필요한 것들을 따로 정리해보려고 한다.
1. SUBSTR - 문자를 잘라주는 함수 (잘라낼 문자열, 시작 위치, 갯수)
2. LENGTH - 문자열의 길이를 알려주는 함수 LENGTH(문자열)
3. RPAD - 뒤에(오른쪽에) 문자를 추가해주는 함수 RPAD(문자열, 길이, 채울 문자)
이 세 함수를 이용해서 웹사이트에서 아이디 찾기 로직을 만들 수 있다.
SELECT RPAD(SUBSTR(ename, 1, 3), LENGTH(ename), '*') "ID" FROM emp;
위의 로직이 바로 아이디를 찾는데 사용되는 코드이다. 실행한 결과는 아래와 같다.
그 외에도 날짜 함수들을 이용해서 근무년수, 근무개월수를 구하는 로직을 짤 수 있다.
먼저 필요한 함수들
1. SYSDATE - 현재 시스템 날짜를 가져올 수 있다. SYSDATE
2. MONTHS_BETWEEN() - 개월수를 계산해줄 수 있다. MONTHS_BETWEEN(start, end)
3. ROUND() - 반올림 함수 ROUND(숫자, 소숫점자리수)
4. TRUNC() - 버림 함수 TRUNC(숫자, 소숫점자리수)
5. CEIL() - 올림 함수 / 근무개월, 년수랑은 상관없지만 페이지 나누기에 자주 나오기 때문에 그냥 같이 써주겠다.
// 근무 개월 수 구하는 로직
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, hiredate),0) "근무 개월 수" FROM emp;
// 근무 년 수 구하는 로직
SELECT TRUNC(ROUND(MONTHS_BETWEEN(SYSDATE, hiredate),0)/12, 0) "근무 년 수" FROM emp;지금 내가 직접 써본건데 쓰면서 느낀게 헷갈리고 싶지 않으면 괄호 안쪽에서부터 채워나가야 할 것 같다.
그래야 함수들이 서로 안 헷갈린다.
-- 변환 함수
/*
오라클 데이터형
=============
문자형 => String
= CHAR(1~2000byte) CHAR(2) => 고정
CHAR(100) => 'A'
= ***VARCHAR2(1~4000byte) => 가변 VARCHAR2(100) 'A' => 1byte
= ***CLOB (4GA) => 가변
숫자형 => int / double
= ***NUMBER(1~38) => NUMBER(4) NUMBER => 14자리 int a="100";
= NUMBER(7,2) => 7자리에 두자리 소수점으로 사용
날짜형 => java.util.Date
= ***DATE
= TIMESTAMP : 기록경주
기타형 => 이미지,동영상 , 애니메이션 : InputStream
= BLOB (4GA) : Binary저장
= BFILE (4GA): 파일 형식
문자 변환 : TO_CHAR
숫자 변환 : TO_NUMBER
날짜 변환 : TO_DATE
2+'2' => 4
*/
--SELECT 2+TO_NUMBER('2') FROM DUAL;
--SELECT 2+ASCII('12') FROM DUAL;--'0' = 48
-- 문자 변환 (TO_CHAR) => String.valueOf()
-- 날짜 변환
SELECT ename,hiredate,TO_CHAR(hiredate,'yyyy-mm-dd') FROM emp;
SELECT ename,hiredate,TO_CHAR(hiredate,'YYYY"년도" MM"월" DD"일"') FROM emp;
SELECT SYSDATE,TO_CHAR(SYSDATE,'RRRR-MM-DD HH24:MI:SS') FROM DUAL;
/*
날짜 문자열로 변경
년도 : YYYY(yyyy) , YYY(yyy) , YY(yy)
RRRR,RR (2000년도 Y2K)
월 : MM
일 : DD
시간 : HH , HH24
분 : MI
초 : SS
숫자 : 999999
'10' 'A'
*/
SELECT ename,sal,TO_CHAR(sal,'L999,999') FROM emp;
SELECT TO_NUMBER('10') , TO_DATE('21/02/09') FROM DUAL;
-- '2' Integer.parseInt() , SimpleDateFormat()
-- 기타 함수 (NVL:NULL값을 다른 값을 변경할 때 사용)
-- NVL2(comm,값,값)
-- NVL2(comm,sal+comm,sal+0)
-- ''=>NULL ' '=>공백
-- NVL => 데이터형을 맞춘다
SELECT ename,sal "급여",comm "성과급", sal+NVL(comm,0) "실제급" FROM emp;
SELECT zipcode,sido,gugun,dong,NVL(bunji,' ') FROM zipcode;
-- NVL2
-- NVL2(컬럼명(NULL이 있는),처리 , 처리)
SELECT ename,sal,comm,NVL2(comm,sal+comm,sal) FROM emp;--삼항연산자
-- DECODE => 다중 조건문)
/*
DECODE(컬럼명,10,''
20,''
30,''
40,'')
DECODE(tel,02,'서울'
032,'인천'
*/
-- Trigger
SELECT ename,job,sal,deptno,DECODE(deptno,10,'개발부',
20,'영업부',
30,'기획부') "부서"
FROM emp;
SELECT ename,job,deptno,CASE WHEN deptno=10 THEN '개발부'
WHEN deptno=20 THEN '영업부'
WHEN deptno=30 THEN '기획부'
END "부서"
FROM emp;
SELECT ename,job,sal,CASE WHEN sal>=800 AND sal<=1500 THEN '1등급'
WHEN sal>1500 AND sal<=2000 THEN '2등급'
WHEN sal>2000 AND sal<=2500 THEN '3등급'
WHEN sal>2500 AND sal<=3000 THEN '4등급'
WHEN sal>3000 THEN '5등급'
END "grade"
FROM emp;
SELECT * FROM emp;
SELECT ename,job,DECODE(job,'MANAGER','관리자',
'SALESMAN','판매자',
'ANALYST','분석가',
'CLERK','일반사원',
'PRESIDENT','사장') "직위"
FROM emp;
-- NVL , NVL2
/*
문자 함수 : LENGTH,SUBSTR , RPAD
숫자 함수 : CEIL
날짜 함수 : SYSDATE , MONTHS_BETWEEN
변환 함수 : TO_CHAR
기타 함수 : NVL
DECODE (약간)
*/
데이터형은 앞으로 계속해서 나올거라 잘 알아둬야 하고, 나머지 변환 함수들은 어차피 자바에서 데이터 변환을 다 하고 들어온다고 하니 엄청 암기할 필요까진 없을거 같다.
그리고 emp 데이터가 오라클 실습을 하기에는 너무 부족해서 저번에 만들어둔 movie 파일을 가져와서 집어넣어주려고 했다. 다만 데이터의 양이 많아서 오라클에서 직접 삽입하는게 아니라
자바에서 데이터를 처리해서 오라클에 삽입해주는 방법을 써줬다.
1. MovieVO 파일을 만들어서 데이터를 묶어준다.
2. MovieManager에서 텍스트 파일을 읽어와서 VO에 저장해둔다.
3. MovieDAO 에서는 오라클에 연결해서 데이터를 전송해준다.
이 세 작업을 다 해주면 자바를 통해 긁어온 데이터를 오라클에 넣어줄 수 있다.
먼저 VO 파일 코드
package com.sist.movie;
/*
* MNO NUMBER(4)
TITLE VARCHAR2(100)
GENRE VARCHAR2(100)
POSTER VARCHAR2(300)
ACTOR VARCHAR2(100)
REGDATE VARCHAR2(100)
GRADE VARCHAR2(50)
DIRECTOR VARCHAR2(50)
*/
public class MovieVO {
private int mno;
private String title;
private String genre;
private String poster;
private String actor;
private String regdate;
private String grade;
private String director;
public int getMno() {
return mno;
}
public void setMno(int mno) {
this.mno = mno;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getGenre() {
return genre;
}
public void setGenre(String genre) {
this.genre = genre;
}
public String getPoster() {
return poster;
}
public void setPoster(String poster) {
this.poster = poster;
}
public String getActor() {
return actor;
}
public void setActor(String actor) {
this.actor = actor;
}
public String getRegdate() {
return regdate;
}
public void setRegdate(String regdate) {
this.regdate = regdate;
}
public String getGrade() {
return grade;
}
public void setGrade(String grade) {
this.grade = grade;
}
public String getDirector() {
return director;
}
public void setDirector(String director) {
this.director = director;
}
}필요한 변수들 잡아주고, getter/ setter 만들어 주면 된다.
다음은 MovieManager에서 텍스트 파일을 읽어오는 코드
package com.sist.movie;
// 파일을 읽어서 VO에 값을 채운다
import java.io.*;
import java.util.*;
/*
* MNO NUMBER(4)
TITLE VARCHAR2(100)
GENRE VARCHAR2(100)
POSTER VARCHAR2(300)
ACTOR VARCHAR2(100)
REGDATE VARCHAR2(100)
GRADE VARCHAR2(50)
DIRECTOR VARCHAR2(50)
*/
public class MovieManager {
public static void main(String[] args) {
try
{
MovieDAO dao=new MovieDAO();
StringBuffer sb=new StringBuffer();
FileReader fr=new FileReader("c:\\javaDev\\movie.txt");
int i=0;
while((i=fr.read())!=-1)
{
sb.append(String.valueOf((char)i));
}
fr.close();
//System.out.println(sb.toString());
String data=sb.toString();
String[] movie=data.split("\n");
for(String s:movie)
{
StringTokenizer st=new StringTokenizer(s, "|");
// VO에 저장 => 오라클로 전송
MovieVO vo=new MovieVO();
vo.setMno(Integer.parseInt(st.nextToken()));
vo.setTitle(st.nextToken());
vo.setGenre(st.nextToken());
vo.setPoster(st.nextToken());
vo.setActor(st.nextToken());
vo.setRegdate(st.nextToken());
vo.setGenre(st.nextToken());
vo.setDirector(st.nextToken());
//dao.movieInsert(vo);
}
}catch(Exception ex) {}
}
}이제는 하도 써서 정말 외워줘야 하는데.. 일단 지금 다시 써보겠다.
-> 완료
MovieDAO에서 오라클로 연결하는 코드
package com.sist.movie;
// 오라클 연결
import java.sql.*;
public class MovieDAO {
private Connection conn; // 오라클 연결
private PreparedStatement ps; // SQL문장을 전송
private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
// 드라이버 등록 => 한번만 설정
public MovieDAO()
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}catch(Exception ex) {}
}
// 오라클 연결
public void getConnection()
{
try
{
conn=DriverManager.getConnection(URL, "hr","happy");
// sqlplus hr/happy
}catch(Exception ex) {}
}
// 오라클 닫기
public void disConnection()
{
try
{
if(ps!=null) ps.close();
if(conn!=null) conn.close();
// exit
}catch(Exception ex) {}
}
// 기능 (데이터 추가)
public void movieInsert(MovieVO vo)
{
try
{
getConnection();
String sql="INSERT INTO movie VALUES(?,?,?,?,?,?,?,?)";
ps=conn.prepareStatement(sql);
ps.setInt(1, vo.getMno());
ps.setString(2, vo.getTitle());
ps.setString(3, vo.getGenre());
ps.setString(4, vo.getPoster());
ps.setString(5, vo.getActor());
ps.setString(6, vo.getRegdate());
ps.setString(7, vo.getGrade());
ps.setString(8, vo.getDirector());
// 실행
ps.executeUpdate();
}catch(Exception ex) {
ex.printStackTrace();
}
finally
{
// 열었으니까 무조건 닫아주기
disConnection();
}
}
}이렇게 연결해주고, MovieManager에서 다시 오라클에 넣어주면 완성이다.
사실 아직 다 한건 아니고 내일 마저 한다고 한다.
728x90
반응형
'programming > Gukbi' 카테고리의 다른 글
| 국비 교육 35일차 - GROUP BY, ORDER BY, JOIN (0) | 2021.02.15 |
|---|---|
| 국비 교육 34일차 - ORACLE (0) | 2021.02.10 |
| 국비 교육 32일차 - DB (0) | 2021.02.08 |
| 국비 교육 31일차 - oracle (0) | 2021.02.07 |
| 국비 교육 31일차 - 네트워크를 이용한 채팅 프로그램 (0) | 2021.02.07 |
