[ORACLE] 231124

이슬기·2023년 11월 24일
0

project

목록 보기
5/42

데이터베이스 모델링

  • 테이블 설계

  • 컬럼 결정

  • 타입 정하기

  • 관계 정의

  • 폭포수 모델 : back 할 수 없다

  • 애자일 모델 : 자유롭게 back 할 수 있다.

  • ER-WIN (ERD(Entity Realation Diagram)) : ERD를 보며 조인 해야한다!

  • 관계 형태(1:1, 1:n, n:n)

  • PK와 FK를 통해서 상속관계 증명 : 관계가 있다. 주는 쪽과 받는 쪽이 결정된다.

논리적 설계

개체, Entity, 속성(Attribute)

물리적 설계

테이블, 컬럼 형성 - 타입이 결정된다

DML

  • DML
    SELECT
    FROM
    WHERE
    GROUP BY
    [[HAVING]]
    ORDER BY
SELECT 컬럼명1, 컬럼명2,.... 함수명(컬럼명3)
    FROM 집합1, 집합2, (SELECT- 인라인뷰)
 WHERE 컬럼명1 =(상수만 아니다. select- 서브쿼리) : 조건검색, 조인하는 곳
   AND 컬럼명2 =(SELECT) : AND는 교집합 - 원소가 줄어듦. 경우의 수 감소. 일량 감소 -> 속도 빠름
    OR  컬럼명3 =(IN)  : OR는 합집합 - 경우의 수가 계속 증가. 일량 증가(가능하면 사용하지 말 것)
 GROUP BY 컬럼명1, 컬럼명2(, 그룹함수 아님. GROUP BY 절에 업는 컬럼을 썼을 때)
 [[HAVING]]
 ORDER BY
  • OR는 있지만 사용하지 않음
    • 합집합으로 경우의 수가 계속 증가한다
    • 따라서 일량이 증가하기 때문에 웬만하면 사용하지 않는 것이 좋다
      (char타입(고정형)도 있지만 varchar2타입(가변형)만 쓰듯)

    GROUP BY

    GROUP BY절에 여러 개의 조건이 올 수 있다 : 업무에 대한 복잡도 높을수록 조건 증가
 --에러: 단일 그룹의 그룹 함수가 아닙니다
SELECT ename, sum(sal)
    FROM emp;
 
--원하는 값 출력되지 않음  
SELECT max(ename), sum(sal)
    FROM emp
 GROUP BY ename;
 
--GROUP BY절에 여러 개의 조건이 올 수 있다 : 업무에 대한 복잡도 높을수록 조건 증가
SELECT deptno, JOB
    FROM emp
GROUP BY deptno, JOB
ORDER BY deptno;

sum(decode(..)) 패턴

--sum(decode(...)) 패턴 : 소계, 총계, 계...
SELECT decode(JOB, 'CLERK', sal, NULL)
   FROM emp;
   
SELECT sum(decode(JOB, 'CLERK', sal, NULL))
   FROM emp;

JOIN

  • 2개 이상의 테이블을 가지고 JOIN 한다
  • 집합과 집합은 관계가 있다
  • 관계형태 : 1:1관계, 1:n관계, n:n관계
  • !주의!
    • n:n은 업무에 대한 정의가 덜 된 경우이므로 조인을 하면 카타시안 곱이 된다.

1. NATURAL JOIN ( = EQUAL JOIN)

  • 양 쪽에 모두 있는 값만 나온다
  • 한 쪽 테이블에만 있는 값은 나오지 않는다 - 이 때는 OUTER JOIN

2. NON-EQUALS JOIN

:= 같다로 비교하는 것 이외의 모든 것

3. OUTER JOIN

2개 이상의 테이블 조인 시 A 테이블의 행에 대해 B 테이블에 일치하는 행이 없더라도 B 테이블의 행을 NULL로 하여 행을 리턴한다.

4. SELF JOIN

  • 셀프 조인을 사용하는 경우
    • 하나의 테이블 안에 서로 계층적 관계와 같이 연관성이 있는 경우 이 컬럼들 간의 셀프 조인을 통해 값을 출력하고자 한다.

<self join 과정>
1. 모든 경우의 수를 만든다(FROM tdept A, tdept b와 같이 카타시안 곱을 만듦).
2. 카타시안 곱으로 모든 값이 출력된 상태에서 WHERE절로 조건검색을 한다(WHERE A.parent_dept = b.dept_code;).

문제)

tdept테이블에 자신의 상위 부서 정보를 관리하고 있다.
이 테이블을 이용하여 부서코드, 부서명, 상위부서코드, 상위부서명을 읽어오는
쿼리를 만들어 보자.

<생각하는 과정>
1. 테이블 하나로는 원하는 값을 출력할 수 없다.

--테이블 하나로는 원하는 값을 출력할 수 없다
SELECT dept_name, parent_dept
    FROM tdept;

  1. 테이블 하나로 alisa명만 바꾼다해도 결국 같은 값만 두 번 출력될 뿐이다.
SELECT dept_name AS "부서명"
     , dept_name AS "상위부서명"
  FROM tdept;

  1. 동일 테이블 두 개를 가져와 부서명, 상위부서명으로 출력하면 같은 값들 간 카타시안 곱이 발생하여 10 X 10으로 100개의 값이 출력된다.
SELECT A.dept_name AS "부서명"
     , b.dept_name AS "상위부서명"
   FROM tdept A, tdept b;

  1. 아래 쿼리문에서 10개의 행으로 이미지와 같은 값이 출력되는 과정은 다음과 같다.
  • WHERE절의 의미는 a테이블 parent_dept 컬럼이 b테이블 dept_code 컬럼과 동일한 것만 조인한다는 것이다.

  • 따라서 a.parent_dept의 aa0001 X3, b.dept_code의 aa0001이 동일하기 때문에 얘네끼리 조인을 하게 된다.

  • 이때 a.parent_dept의 aa0001은 3개라서 dept_name을 경영지원, 재무, 총무를 다 갖게 된다.
    b.dept_code의 aa0001은 1개라서 dept_name을 경영지원만 갖게 된다.

  • 출력

    • WHERE절에 a를 기준으로 셀프 조인을 했기 때문에 출력도 a를 기준으로 동일한 코드를 가진 dept_name들만 출력돼서 총 10개의 row가 보여진다.

    <답>

    --둘의 관계형태가 1:n이니 정의를 잘해서 사용해야 한다
    
    SELECT A.dept_code AS "부서코드"
               , A.dept_name AS "부서명"
               , b.parent_dept AS "상위부서코드"
               , b.dept_name AS "상위부서명"
       FROM tdept A, tdept b
    WHERE A.parent_dept = b.dept_code;

PL/SQL

http://www.gurubee.net/lecture/1039

<함수와 프로시저가 같은 것인가?>

  • 함수
    • 사용자 정의 함수는 반드시 반환값이 존재한다.
    • 호출해야 실행된다
  • 프로시저
    • 프로시저는 return 예약어를 사용하지 않고 파라미터 자리에 OUT 속성을 추가하여 자바단으로 보낼 수 있다.
    • 호출해야 실행된다
    • SELECT ... INTO 변수 문을 지원한다 - 단 한 개 로우에 대해서만 가능하다
    • 만일 멀티 로우에 있는 값을 담으려면 반드시 cursor를 사용해야 한다.

구구단 구하기

CREATE OR REPLACE PROCEDURE proc_gugudan(dan IN number)
IS
    n_i number(2);
BEGIN
    n_i :=0;
    dbms_output.put_line(dan||'단을 출력합니다.');
    FOR n_i IN 1..9 LOOP

        dbms_output.put_line(dan||'*'||n_i||'='||(dan*n_i));
    END LOOP;
END;

🔽출력

exec proc_gugudan(3);

문제

SELECT *
    FROM (
                    SELECT ROWNUM rno, workcd_vc, time_nu FROM t_worktime
                        WHERE ROWNUM < 4
                )A,
                (
                    SELECT ROWNUM rno, workcd_vc, time_nu FROM t_worktime
                        WHERE ROWNUM < 4
                )b;

0개의 댓글

관련 채용 정보