01/04 국비학원 28회차 오라클수업

컬럼명은 오라클에서 변수이다.

 

DECODE문

Decode는 일반적인 프로그래밍 언어의 IF문을 SQL문장 또는 PL/SQL
안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.

따라서 일반적인 프로그래밍 언어의 IF문이 수행할 수 있는 기능을 포함하고 있다.

SELECT시의 DECODE사용은 FROM 절만 빼고는 어디서나 사용할 수 있다.

FROM 집합1, 집합2,.....

FROM에서 사용할 수 없다는 것은 SELECT시 테이블을 DECODE를 이용하여
동적으로 변경할 수 없다는 뜻이기도 하다.

오라클 문법아님...밑에는 
IF A = B THEN
    RETURN 'T';
END IF;
A컬럼과 B컬럼이 같다면 'T'를 RETURN 받고 싶다는 문장이다.
DECODE를 이용하면 다음과 같이 된다.
DECODE(A,B,'T')
SELECT문에서 이용된다면
A컬럼과 B컬럼이 같은 Record는 'T'값을 돌려달라는 의미가 된다.
SELECT DECODE(A,B,'T') AS col1
  FROM TABLE이름
 WHERE ...
이 경우 A컬럼과  B컬럼의 값이 다르다면 어떤 값이 나오겠는가?
결과는 널(NULL)이 나온다.
이와같이 단순비교로 사용되는 DECODE는 내부적으로 ELSE의 기능을
가지고 있으며 ELSE에 해당될 경우 널(NULL)을 리턴한다.
다시 말해서 위 문장의 완전한 형태는 다음과 같다.
SELECT DECODE(A,B,'T',NULL) AS col1
  FROM TABLE이름
 WHERE ....

 

SELECT 

              컬럼명1, 컬럼명2, ......, 함수가능 ROUND(TO_CHAR()) 중첩도 가능---제공함수

FROM 집합1, 집합2, ....SELECT도 올수있다-인라인 뷰

WHERE 컬럼명 비교연산자 값

   AND

      OR (로우의 수 늘어난다. 속도 떨어진다.... 많이쓰면 안좋다...)

GROUP BY 컬럼명-----> 부서별, 분기별, 반기별로 그룹해서 뽑아낼 때

                    주의 :  여기에 적힌 컬럼명만 셀렉트절에 올 수 있다(deptno, ename 둘 다 온 예제)

(Having절-GROUP BY 절 사용에 대한 조건 검색시 사용-WHERE절 사용하면 망한다)

ORDER BY 컬럼명1 asc/desc, 컬럼명2 asc/desc

 

사용자 정의 함수 

PL/SQL표준

CREATE OR REPLACE FUNTION func_XXXX()

 

CREATE OR REPLACE PROCEDURE proc_XXXX

 

CREATE OR PREPLACE TRIGGER tri_XXX()

 

        그룹함수
max min count avg sum

인라인뷰 기본 문법

SELECT column_list
FROM (SELECT * FROM table_name) [alias]
WHERE 조건식;

 

인라인뷰는 왜 사용할까?

1) 서브쿼리와 데이블 조인이 이루어질 경우 검색하는 데이터명(필드명)은 [테이블.필드명]과 같이 길어지된다. 이때 서브쿼리를 통애 메인쿼리로 올라갈수록 쿼리의 길이가 점점 더 길어질 뿐 아니라 쿼리의 가독성이 떨어진다. 이때, FROM절에서 사용하는 인라인뷰에 별칭(Alias)를 줘서 간단하게 만들 수 있다. 

 

2) 전체 테이블을 비교하는 것 보다 테이블의 일부 데이터만을 불러와 그 중에서 조건을 따지는것이 비교하는 횟수가 적다. 예를들어, 전체 데이터에서 비교하는 것보다 ROWNUM을 이용해서 10번째 튜플 정보만 가져와 그 중에서 조건을 따져 데이터를 가져온다면 비교하는 횟수가 줄어들게 할 수도 있다. (ROWNUM은 데이터를 가져오는 갯수를 제한하는 방식으로 N-TOP 질의라고 한다.)

출처 : https://loghada.tistory.com/18 

[Oracle] Inline View 인라인뷰란

그룹바이쓸땐 where사용 안된다. having사용해야 한다.

lec_time 이랑 lec_point랑 같으면 '일반과목' 아니면 null

lec_time 이랑 lec_point랑 같으면 '일반과목' 아니면 null

lec_time 이랑 lec_point랑 같으면 count 가 로우의 갯수를 세준다. 디코드 안의 결과는 상관이 없다.

 

UNION ALL

오라클에서 여러 개의 SELECT 문 결과를 합치기 위해서는 UNION, UNION ALL을 사용하면 된다. UNION을 사용할 경우 합쳐진 결과에서 중복을 제거한 결과를 반환하고, UNION ALL을 사용하면 중복을 제거하지 않고 합쳐진 결과를 그대로를 반환한다.

1을 넣어라 타임이랑 포인트가 같으면

1의 갯수를 세라 타임이랑 포인트가 같으면 1을 넣고

1을 넣어라 합쳐라 2를 넣어라

 

to_char로 sysdate에있는 날짜, 요일을 문자로 바꿈

|| 를 사용해서 문자열을 붙인다.

 

to_char를 사용해서 sysdate를 문자로 바꿔주었다.

|| 로 붙이기

decode로 요일이 월요일,화요일....이면 '01' 등등으로 바꿔줌

dual로 맞춤 표 만들기

 

**인라인뷰**

GROUP BY

그룹바이 안에있는 컬럼만 뽑을 수 있다.

위는 MAX를 이용해서 해결했다. 근데 의미 있나?...

 

3번의 최종 답안

max min은 널이 포함안된다. 그래서 빈칸 빼주기 위해서 max, min을 사용하였다.

값이 있으면 널보다 최대값이다. 혹은 널보다 최소값이다.

 

오더바이로 정렬 해줬다... 그래서 그냥 테이블이랑 똑같은 결과가 되었다...

 

 

* : 와일드 카드

집합에 존재하는 모든 컬럼을 말한다.

데이터 검증이나 확인용으로만 사용하세요

자바코드에서 사용하면 직관적이지 않아서 권장하지 않습니다.

IT 프로젝트는 거의 모두 협업으로 진행되므로 다른 개발자를 위한 배려입니다.

 

별칭(Alias)

 

 

키위에서 조건절 만들기

인라인뷰 안에 case로 조건절 만들어주기

멤버 집합에서

멤 아뒤랑 변수로 받은 아뒤가 같으면 

        {  비번이 같으면 1

           아니면 0

          }

아뒤 다르면 -1

as result는 result에 담아라

 

행은 하나다.

 

도전문제

case when 조건할때 then 결과

 

단일 그룹 함수가 아니라 안된다.

count로 몇명인지 셀 수 없다.

 

그룹 바이로 해결했다.

 

그룹바이 없이 문제풀기

 

2023년 1월 4일 데이터베이스 구현 숙제
C:\오라클_수업\오라클실습소스\SQL\04장 t_giftpoint.sql실행하여 테이블 생성합니다.문제1
영화 티켓을 받을 수 있는 사람의 명단과 현재 가지고 있는 포인트, 영화 티켓의 포인트
그리고 그 티켓을 사용한 후 남은 예상 포인트를 출력하시오.문제2
김유신씨가 보유하고 있는 마일리지 포인트로 얻을 수 있는 상품 중 가장 포인트가 높은 것은
무엇인가? 

 

두개의 집합에서 데이터를 가져올때 m과 g처럼 어디에서 온 데이터인지 표시하기위해서.도트연산자쓴다

 

 

데이터 정규화

https://lee-mandu.tistory.com/478

데이터베이스(DB) 정규화(1차,2차,3차)

데이터 정규화의 목적

데이터를 보다 효율적으로 저장하고, 데이터 중복을 방지하며, 저장 공간이 낭비되는 테이블 설계를 제거하기 위함

 

관계지향형 데이터베이스에서는 테이블의 수가 많아질 수 밖에 없다.

1차 정규화

정의

☞ 반복되는 속성이나 그룹의 속성을 제거하고, 새로운 테이블을 추가한 후에 기존 테이블과 일대다 관계를 형성하는 것을 의미합니다.

2차 정규화

정의

☞ 기본키가 복합 키로 구성되었을 때, 복합 키 전체에 의존하지 않고 일부에 의존적인 열이 있으면 이를 제거해야 하는 것을 의미합니다.

3차 정규화

정의

☞ 기본 키에 의존하지 않고 일반 열에 의존하는 열이 있다면 이를 제거해야 하는 것을 의미합니다.

 

관계 형태에는 

1 : 1 -사원 -가족테이블

1 : n -

n : n - 회원 대 도서, 회원 대 상품, 회원 대 영화, 학생 대 교과목

 

n : n은 업부에 대한 정의가 덜 된 경우이다.

카타시안의 곱이 발생하므로 조인하면 안된다.

데이터 유효성 체크가 안된다.

ERD를 볼 수 있어야 한다.

n:n의 관계를 1:1로 만들어 주는 교차 entity 즉, 행위엔티티를 찾는것이 중요하다.

 

회사에서 가족의 신상을 관리한다.... 1:1

 

ERWin으로 행위 entity만들었다. 제 2 정규화이다.

 

제 3 정규화를 위반했다. 부서명이 부서번호(FK)에 의존하고 있기 때문이다.

 

제 1 정규화

중복되는 값을 관리하지 않는다.

 

제 2 정규화

복합키 모두에 종속적인 컬럼명만 쓴다.

교차엔티티 또는 행위 엔티티

 

제 3 정규화

테이블에 관계형태로 FK가 존재하는 경우

PK가 아니고 FK에 종속적인 컬럼이 존재하면 안됨

 

Natural Join

Outer Join

Self Join

 

job_id 따라 연봉인상해주기

쌤답안

 

테이블에 있는게 아니라서 '전체' 안된다. 인라인뷰 쓰자.

 

인라인뷰로 프롬 안에 중복제거 함수 넣어줬다.

 

시구를 중복제거하고 시구라는 별명붙인다

우편번호 테이블에서

도이름이 =:zdo 내가 넣은 변수랑 같은 경우

시구를 순서대로 정렬해서 보여준다.

똑같이 시구 넣으면 동을 보여주는것이다.

 

자바랑 우편번호 sql문 연동하기

 

package dev_java.basic.basic3;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JPanel;

import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;

import dev_java.util.DBConnectionMgr;

public class ZipcodeSearchView extends JFrame implements ItemListener {
  // 선언부
  // 게으른 인스턴스화-오라클과 연동하기 위해
  // 사용자가 선택한 zdo
  String zdo = null;
  // 사용자가 선택한 sigu
  String sigu = null;
  // 시용자가 선택한 dong
  String dong = null;
  // 콤보에는 배열이 들어가더라
  // DB에서 가져온 zdos[]
  String[] zdos = null;
  // DB에서 가져온 sigus[]
  String[] sigus = null;
  // DB에서 가져온 dongs[]
  String[] dongs = null;
  // 중분류(sigu), 소분류(dong)
  String[] totals = new String[]{"전체"};

  JPanel jp_north = new JPanel();
  JComboBox jcb_zdo = null;
  JComboBox jcb_sigu = null;
  JComboBox jcb_dong = null;
  DBConnectionMgr dbMgr = new DBConnectionMgr();
  Connection con = null;
  PreparedStatement pstmt = null;
  ResultSet rs = null;

  // 생성자
  public ZipcodeSearchView() {
    zdos = getZDOList();
    jcb_zdo = new JComboBox(zdos);
    jcb_sigu = new JComboBox(totals);
    jcb_dong = new JComboBox(totals);
    jcb_zdo.addItemListener(this);
    jcb_sigu.addItemListener(this);
    jcb_dong.addItemListener(this);
  }

  // 대분류 정보 초기화에 필요한 DB조회하기 구현
  public String[] getZDOList() {
    StringBuilder sql = new StringBuilder();
    sql.append("select '전체' zdo from dual ");
    sql.append(" union all                  ");
    sql.append(" select  zdo                ");
    sql.append(" from (         ");
    sql.append("   select distinct(zdo) zdo");
    sql.append("   from zipcode_t  ");
    sql.append("   order by zdo asc)");
    try {
      // con의 주소번지가 확인 되면 오라클 서버와 연결통로가 확보되었다.
      con = dbMgr.getConnection();
      // 전령의 역할. 정적-스테이트먼트 동적-프리페어드스테이트먼트
      pstmt = con.prepareStatement(sql.toString());
      // 오라클에서 생성된 테이블의 커서 디폴트위치는 항상 isTop이다.커서가 존재하는데...이동시킴
      rs = pstmt.executeQuery();
      Vector<String> v = new Vector<>();
      // rs.previous(); ???
      while (rs.next()) {
        String zdo = rs.getString("zdo");
        v.add(zdo);
      }
      zdos = new String[v.size()];
      v.copyInto(zdos);
    } catch (SQLException se) {
      System.out.println(se.toString());// getMessage();도 가능
      System.out.println(sql.toString());// 위에서 쓴 쿼리문 프린트
    } finally {
      // 사용한 자원 반납하기-생성의 역순으로 할것
      // 생략해도 언젠가 반납은 이루어짐-명시적으로 반납처리 권장함
      // 왜냐하면 오라클 서버에서 커넥션을 강제로 종료시켜버리니까...
      try {
        dbMgr.freeConnection(con, pstmt, rs);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return zdos;
  }// end of getZDOList

  public String[] getSiguList(String zdo) {
    StringBuilder sql = new StringBuilder();
    sql.append("select '전체' sigu from dual ");
    sql.append(" union all                  ");
    sql.append(" select  sigu                ");
    sql.append(" from (         ");
    sql.append("   select distinct(sigu) sigu");
    sql.append("   from zipcode_t  ");
    sql.append("   where zdo=?  "); //오라클에서 :zdo가 ?변수됨
    sql.append("   order by sigu asc)");
    try {
      // con의 주소번지가 확인 되면 오라클 서버와 연결통로가 확보되었다.
      con = dbMgr.getConnection();
      // 전령의 역할. 정적-스테이트먼트 동적-프리페어드스테이트먼트
      pstmt = con.prepareStatement(sql.toString());
      pstmt.setString(1,zdo);
      // 오라클에서 생성된 테이블의 커서 디폴트위치는 항상 isTop이다.커서가 존재하는데...이동시킴
      rs = pstmt.executeQuery();
      Vector<String> v = new Vector<>();//coptInto메소드 사용 위해서 그대로 복사하려고
      // rs.previous(); ???
      while (rs.next()) {
        String sigu = rs.getString("sigu");
        v.add(sigu);
      }
      //시구 콤보박스에 들어갈 배열 생성하기
      sigus = new String[v.size()];
      //벡터에 들어있는 값 스트링 배열에 복사하기
      v.copyInto(sigus);
    } catch (SQLException se) {
      System.out.println(se.toString());// getMessage();도 가능
      System.out.println(sql.toString());//위에서 쓴 쿼리문 프린트
    } finally {
      // 사용한 자원 반납하기-생성의 역순으로 할것
      // 생략해도 언젠가 반납은 이루어짐-명시적으로 반납처리 권장함
      // 왜냐하면 오라클 서버에서 커넥션을 강제로 종료시켜버리니까...
      try {
        dbMgr.freeConnection(con, pstmt, rs);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return sigus;
  }

  // 화면처리부
  public void initDisplay() {
    this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
    jp_north.add(jcb_zdo);
    jp_north.add(jcb_sigu);
    jp_north.add(jcb_dong);

    this.add("North", jp_north);
    this.setSize(630, 400);
    this.setVisible(true);
  }

  // 메인
  public static void main(String[] args) {
    JFrame.setDefaultLookAndFeelDecorated(true);
    ZipcodeSearchView zcsv = new ZipcodeSearchView();
    zcsv.initDisplay();

  }

  @Override
  public void itemStateChanged(ItemEvent ie) {
    //이벤트가 감지되는 소스 가져오기
    Object obj=ie.getSource();
    //너 ZDO콤보박스니??
    if(obj==jcb_zdo){
      if(ie.getStateChange()==ItemEvent.SELECTED){
        System.out.println("선택한 ZDO===>"+ zdos[jcb_zdo.getSelectedIndex()]);
        zdo=zdos[jcb_zdo.getSelectedIndex()];
        sigus=getSiguList(zdo);
        //대분류가 결정이 되었을때  sigus를 초기화 해줘야 한다.
        //기존에 디폴트로 전체 상수값을 넣어 두었으니 이것을 삭제하고
        jcb_sigu.removeAllItems();//아까 토탈 넣어놓은거 지워줌
        //새로운 DB서버에서 읽어온 값으로 아이템을 추가 해야 한다.-초기화
        for(int i=0;i<sigus.length;i++){
          jcb_sigu.addItem(sigus[i]);

        }
      }
    }
    
  }

}