dev-waterparsley

SELECT SYSDATE
      ,TO_CHAR(SYSDATE, ‘Q’) - 1 AS “전분기”
      ,ADD_MONTHS(TRUNC(SYSDATE), ‘Q’,- 3) AS “전분기첫일”
      ,TRUNC(SYSDATE, ‘Q’) - 1 AS “전분기막일”
      ,TO_CHAR(SYSDATE, ‘Q’) AS “현분기“
      ,TRUNC(SYSDATE, ‘Q’) AS “현분기첫일”
      ,ADD_MONTHS(SYSDATE, ‘Q’, 3) - 1 AS “현분기막일”
FROM dual
;

✅MYSQL 등: LIMIT

MYSQL, PostgreSQL과 같은 일부 RDBMS에는 LIMIT절을 사용하여 출력 데이터의 행 개수를 지정할 수 있음.
 

SELECT *
FROM MEMBER_PROFILE
ORDER BY DATE_OF_BIRTH DESC
LIMIT 1
;

출력 결과

✅ORACLE: FETCH

오라클에는 LIMIT절이 없으나
12c 릴리스부터 LIMIT절과 유사하지만 더 유연한 절을 제공함.
 

SELECT *
FROM MEMBER_PROFILE a
ORDER BY DATE_OF_BIRTH DESC
FETCH FIRST 1 ROW ONLY
;

출력 결과

FETCH절 구문

[ OFFSET offset ROWS]
 FETCH  NEXT [  row_count | percent PERCENT  ] ROWS  [ ONLY | WITH TIES ]

• 의미를 명확하게 하기 위해 ROW 대신 ROWS를 사용할 수 있음. 예를 들어 아래 절들은 동일하게 동작함
  • FETCH NEXT 1 ROWS
  • FETCH FIRST 1 ROW
• ONLY와 WITH TIES
  • ONLY: 정확한 행 개수(row_count) 또는 행 비율(percent)를 반환
  • WITH TIES: 마지막 행과 동일한 값을 가진 행을 추가 반환

 
 
https://www.oracletutorial.com/oracle-basics/oracle-fetch/0/-

3. 복합형

• 여러 종류 및 개수의 데이터를 저장하기 위해 사용자가 직접 정의하는 자료형
• 레코드: 다양한 자료형을 가진 데이터들을 한 번에 저장(테이블의 행과 유사)
• 컬렉션: 동일한 자료형을 가진 데이터를 여러 개 저장(테이블의 열과 유사, like 배열)

레코드

DECLARE
  TYPE rec_emp IS RECORD(
        name VARCHAR2(200)
       ,salary emp%TYPE
       ,dept_code emp%TYPE
  )
BEGIN
  rec_emp.name := ‘이동욱‘;
  rec_emp.salary := 3000000;
  rec_emp.dept_code := ‘10000678’;
  
  —-레코드를 사용한 INSERT
  INSERT INTO emp
  VALUES rec_emp;
  
  
  rec_emp.salary := 5500000;
  rec_emp.dept_code := ‘10000999’;
  
  —-레코드를 사용한 UPDATE
  UPDATE emp
  SET ROW = rec_emp
  WHERE name = ‘이동욱’
  ;
END
;

컬렉션

• 특정 자료형의 데이터를 여러 개 저장하는 복합 자려향
• 종류: *연관 배열, 중첩 테이블, VARRAY

*연관 배열

TYPE 연관배열명 IS TABLE OF 연관배열자료형[NOT NULL] —참조 자료형 사용 가능
INDEX BY 인덱스자료형;

• 인덱스(key)와 값(value)으로 구성되는 컬렉션
• 중복되지 않은 유일한 키를 통해 값을 저장하고 불러오는 방식
• 인덱스는 1부터 시작
• 사용 빈도가 제일 높은 컬렉션

DECLARE
  idx NUMBER(10) := 0;
  
  TYPE itab_lang IS TABLE OF VARCHAR2(200)
  INDEX BY PLS_INTEGER;
  arr_lang itab_lang;
  
  —-참조 자료형 연관 배열 생성
  TYPE itab_emp IS TABLE OF emp%ROWTYPE
  INDEX BY PLS_INTEGER;
  arr_emp itab_emp;
  
BEGIN
  arr_lang(1) := ‘java’;
  arr_lang(2) := ‘javaScript’;
  arr_lang(3) := ‘sql’;
  arr_lang(4) := ‘python’;
  
  —-LOOP문으로 arr_lang 출력
  LOOP
    idx := idx + 1;
    IF idx > arr_lang.COUNT THEN EXIT;
    END IF;
    dbms_output.put_line(arr_lang(idx));
  END LOOP;

  —-FOR LOOP문으로 arr_lang 출력
  FOR i IN arr_lang.FIRST..arr_lang.LAST LOOP
    dbms_output.put_line(arr_lang(i));
  END LOOP;
  
  idx := 0;
  —-FOR LOOP 응용
  FOR i IN (SELECT * FROM emp) LOOP
    idx := idx + 1;
    arr_emp(idx) := i;
    —-emp 테이블 전체 직원명 출력
    dbms_output.put_line(arr_emp(idx).name);
  END LOOP;

  —-컬렉션을 사용한 INSERT
  INSERT INTO emp2
  VALUES arr_emp(idx)
  ;
END
;

변수의 자료형

• 스칼라형(scalar)
• 참조형(reference)
• 복합형(composite)
• LOB(LargeOBject)

1. 스칼라형

• 오라클에서 기본으로 정의 해놓은 자료향
• 내부 구성 요소가 없는 단일 값을 의미
• NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN

2. 참조형

• 오라클 데이터베이스에 존재하는 특정 테이블 열의 자료형나 하나의 행 구조을 참조하는 자료형
• %TYPE(열 참조), %ROWTYPE(행 참조)

  —-상수
  PI CONSTANT NUMBER(10) := 3.14;
  
  —-스칼라형
  test1 VARCHAR2(9);
  test2 NUMBER(10) DEFAULT 1; 
  test3 NUMBER(10) NOT NULL := 100;
  —-*NOT NULL 키워드를 사용한 변수는 반드시 선언과 동시에 특정 값을 지정해야 함

  —-참조형
  test4 emp.name%TYPE;
  test5 emp%ROWTYPE;

1. 조건문

• IF
• CASE

DECLARE
  person emp%ROWTYPE;
BEGIN
  SELECT * 
  INTO person
  FROM emp
  WHERE yyyy = ‘2022’
  AND dept_code = ‘04001000’
  AND name = ‘에이다 러블레이스‘
  ;
  
  
—-IF조건문
  IF person.name = ‘에이다 러블레이스’;
    THEN dbms_output.put_line(‘세계 최초의 프로그래머입니다.’);
  ELSIF person.name = ‘제임스 고슬링’;
    THEN dbms_output.put_line(‘최초로 JAVA를 개발하였습니다.’);
  ELSE
    dbms_output.put_line(‘정보가 없습니다.’);
  END IF;
  
—-단순CASE조건문
  CASE person.name
    WHEN ‘에이다 러블레이스‘ THEN dbms_output.put_line(‘세계 최초의 프로그래머입니다.’);
    WHEN ‘제임스 고슬링‘ THEN dbms_output.put_line(‘최초로 JAVA를 개발하였습니다.’);
  ELSE
    dbms_output.put_line(‘정보가 없습니다.’);
  END CASE;

—-검색CASE조건문
  CASE
    WHEN person.salary < 3000000 THEN dbms_output.put_line(‘기본급이 300만원 미만입니다.’);
  ELSE
    dbms_output.put_line(‘기본급이 300만원 이상입니다.’);
  END CASE;

2. 반복문

• LOOP: 기본 반복문
• WHILE LOOP: 특정 조건식의 결과를 통해 반복 수행
• FOR LOOP: 반복 횟수를 정하여 반복 수행
• CURSOR FOR LOOP: 커서를 활용한 반복 수행

*반복 수행을 중단 시키거나 특정 반복 주기를 건너뛰는 명령어

• EXIT: 수행 중인 반복 종료
• EXIT WHEN: 반복 종료를 위한 조건식을 지정하고 만족하면 반복 종료
• CONTINUE: 수행 중인 반복의 현재 주기를 건너뜀
• CONTINUE WHEN: 특정 조건힉을 지정하고 조건식을 만족하면 현재 반복 주기를 건너뜀

DECLARE
  num NUMBER(10) DEFAULT 1;
  total NUMBER(10) DEFAULT 0;
  v_emp emp%ROWTYPE;
  
  CURSOR c_emp IS
  SELECT *
  INTO v_emp
  FROM emp
  WHERE yyyy = ‘2022’
  AND dept_code = 04001000’
  ;
BEGIN
  —-기본LOOP(1부터 10까지 합산값 구하기)
  LOOP
    total := total + num;
    num := num + 1;
    IF num > 10 THEN EXIT;
    END IF;
  END LOOP;
  
  total := 0;
  num := 1;
  —-WHILE LOOP
  —-반복 수행 전에 조건식을 검사하기 때문에 조건식 결과 값에 따라 단 한 번도 반복 수행 되지 않을 수도 있음!
  WHILE num < 11 LOOP
    total := total + num;
    num := num + 1;
  END LOOP;
  
  total := 0;
  —-FOR LOOP
  FOR i IN 1..10 LOOP
    total := total + i;
    CONTINUE WHEN MOD(i, 2) = 0;
    dbms_output.put_line(‘i: ‘||i);
  END LOOP;
  
  —-CURSOR FOR LOOP(커서 출력)
  FOR i IN c_emp LOOP
    dbms_output.put_line(‘이름: ‘||i.name||’, 기본급: ’||i.salary);
  END LOOP;
END
;

1. cmd 창에서 명령어 SET 실행

2. 출력 결과 중, Path란에서 오라클 설치(ORACLE_HOME) 경로 확인
*ORACLE_HOME은 가변적인 경로

tnsnames.ora

-데이터베이스 주소를 정의해놓은 파일
-해당 파일이 있으면 IP주소와 SID 혹은 Service Name을 직접 입력하지 않고도 DB 연결 가능
-(고정)경로: ORACLE_HOME\network\admin\tnsnames.ora

sqlplus 계정명/패스워드@tnsnames.ora에 설정한 DB alias명

✅ 오라클 데이터 딕셔너리(Data Dictionary)

• 오라클 데이터베이스 운영과 연관된 중요한 정보를 제공하는 읽기 전용 시스템 테이블
• 뷰의 집합으로 일반 사용자가 직접 수정하는 것은 불가능하며 오라클 서버가 스스로 관리하는 영역(소유자: SYS)

모든 스키마 오브젝트(테이블, 인덱스, 뷰, 시퀀스, 동의어, DB링크, PL/SQL 함수나 프로시저)의 정의와 이 오브젝트에 대해 얼마나 많은 저장공간을 사용하는지, 칼럼의 기본값은 무엇이고 제약조건은 어떤 것들이 있는지, 오라클 사용자의 이름, DB에 생성된 권한과 롤 등이 어떻게 부여 되었는지, 객체접근 및 수정에 따른 시스템 감사정보, 데이터베이스 생성시각, 이름, 운영모드, 인스턴스의 이름 등과 같이 DB운영에 필요한 중요한 정보들이 기록된다.

오라클은 사용자 정보 및 관련 스키마 오브젝트와 저장구조등에 접근하기 위해 데이터 딕셔너리를 접근하고 사용자가 실행하는 DDL(Data Definition Language)에 맞추어 데이터 딕셔너리를 갱신하고 모든 오라클 사용자는 이러한 정보를 읽기 전용 뷰를 통해 참조할 수 있다.

▪️접두어에 따른 데이터 딕셔너리 분류

✅ 예시

—-데이터베이스 사용자가 접근 가능한 딕셔너리뷰를 나열
SELECT *
FROM DICTIONARY
;

—-모든 프로시저, 함수, 패키지 소스가 text 컬럼에 담겨있음
SELECT *
FROM DBA_SOURCE
;

—-모든 테이블, 뷰, 프로시저 등의 정보
SELECT *
FROM DBA_OBJECTS
;

—-모든 테이블과 뷰에 지정된 comment 정보
SELECT *
FROM DBA_TAB_COMMENTS
;

—-모든 테이블과 뷰의 컬럼과 지정된 comment 정보
SELECT *
FROM DBA_TAB_COMMENTS
;

참조
http://www.ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=636

REGEXP_LIKE(컬럼, ‘패턴1|패턴2|패턴3’, [매칭파라미터])

NOT REGEXP_LIKE(컬럼, ‘패턴1|패턴2|패턴3’, [매칭파라미터])

• 오라클 10g 버전부터 추가된 기능으로 유닉스(리눅스) 정규식과 파이프(|)문자를 사용하여 다양한 패턴의 다중 검색 가능

✅ 정규식

✅ 예시

--직업명에 end 또는 Full 문자열을 포함하는 직원들 조회
SELECT *
FROM employees
WHERE yyyy = ‘2022’
AND REGEXP_LIKE(job, ‘end|Full’)
;

--직업명이 Android 또는 ios로 시작하는 직원들은 제외하고 조회
SELECT *
FROM employees
WHERE yyyy = ‘2022’
AND NOT REGEXP_LIKE(job, ‘^Android|^ios’)
;

--직업명이 Designer 또는 Developer로 끝나는 직원들 조회
SELECT *
FROM employees
WHERE yyyy = ‘2022’
AND REGEXP_LIKE(job, ‘Designer$|Developer$’)
;

--90년대 1월 또는 80년대 1월에 출생한 직원들 조회
SELECT *
FROM employees
WHERE yyyy = ‘2022’
AND REGEXP_LIKE(birth_date, ‘^9.01|^8.01’)
;

✅응용

SELECT *
FROM user_source
WHERE REGEXP_LIKE(text, (SELECT LISTAGG(table_name, ‘|’) WITHIN GROUP(ORDER BY table_name) AS table_name
                         FROM user_tables
                         WHERE table_name LIKE ‘%GNPS%’))

• 이전 행 데이터 리턴
LAG(컬럼명 [,offset] [,default]) OVER([PARTITION BY 그룹컬럼명] ORDER BY 정렬컬럼명)

• 다음 행 데이터 리턴
LEAD(컬럼명 [,offset] [,default]) OVER([PARTITION BY 그룹컬럼명] ORDER BY 정렬컬럼명)

-offset: 값을 가져올 행의 위치(기본 값: 1)
-default: 값이 없을 경우 출력할 기본 값(생략 시, null)

✅ 예시

SELECT yyyy
      ,pin
      ,start_date
      ,end_date   
      ,LAG(end_date) OVER(ORDER BY yyyy, pin, start_date) AS r_end_date --(실제)이전 근무 종료일
      ,LEAD(start_date) OVER(ORDER BY yyyy, pin, start_date) AS r_next_start_date --(실제)다음 근무 시작일
FROM employees 
AND yyyy = ‘2022’

✅ 응용

--연속적으로 근무한 직원들의 pin 번호 조회
SELECT z.pin
FROM (
       SELECT y.*
             ,DENSE_RANK() OVER(ORDER BY y.pin) AS grp
       FROM (
              SELECT x.yyyy
                    ,x.pin
                    ,x.start_date
                    ,x.end_date
                    ,(CASE WHEN x.c_past_end_date = LAG(x.end_date) OVER(ORDER BY x.yyyy, x.pin, x.start_date) 
                           THEN 1 ELSE 0 END) AS chk1 --(예측)이전 근무 종료일과 (실제)이전 근무 종료일의 값 비교
                    ,(CASE WHEN x.c_next_start_date = LEAD(x.start_date) OVER(ORDER BY x.yyyy, x.pin, x.start_date)
                           THEN 1 ELSE 0 END) AS chk2 --(예측)다음 근무일과 (실제)다음 근무 시작일 값 비교
              FROM (
                     SELECT yyyy
                           ,pin
                           ,start_date --근무 시작일
                           ,end_date --근무 종료일
                           ,to_char(to_date(start_date, ‘YYYYMMDD’) - 1) As c_past_end_date --(예측)이전 근무 종료일
                           ,to_char(to_date(end_date, ‘YYYYMMDD’) + 1) AS c_next_start_date --(예측)다음 근무 시작일
                     FROM employees
                     AND yyyy = ‘2022’
                   ) x
            ) y
       WHERE ((y.chk1 = 1 AND y.chk = 0) 
               OR (y.chk1 = 1 AND y.chk = 1)
               OR (y.chk1 = 0 AND y.chk = 1))
     ) z
GROUP BY z.yyyy, z.pin, z.grp

https://www.oracle.com/kr/database/technologies/oracle-database-software-downloads.html#19c

1. 위의 오라클 공식 홈페이지에 접속하여 자신의 운영체제에 맞는 압축파일을 다운로드 한다.

(오라클 계정으로 로그인 해야만 다운로드 가능)

2. 다운 받은 압축파일을 풀고 setup 설치 파일을 실행한다.

단일 인스턴스 데이터베이스 생성 및 구성 선택 후 [다음]

- 데스크톱 클래스(D): 데이터베이스를 혼자 사용하는 경우 선택

- 서버 클래스(S): 네트워크 상의 여러 PC들과 네트워킹을 통해 DB를 사용할 때 선택

설치 용도가 개인 연습용이라면 데스크톱 클래스를 설치하고, 네트워크를 통해 여러 PC와 데이터를 주고 받는 작업을 할 경우 서버 클래스를 설치 하면 된다. 나는 서버 클래스로 설치를 진행

표준 설치 선택 후 [다음]

고급 설치를 통해 세밀한 설정을 할 수 있지만, 표준 설치로 진행해도 괜춘

가상 계정 사용 선택 후 [다음]

오라클을 삭제해야할 경우가 종종 생기는데 경로는 수정해버리면 해당 경로를 기억하고 있어야 하기 때문에 설치 경로는 특별한 경우가 아니라면 수정하지 말고 기본 설정대로 하는게 좋음. 근데 난 경로 수정함.

- admin 비밀번호는 따로 기록해두기!

필요 조건 검사 수행 > 요약 확인 후 [설치] 실행!

 설치 끝

3. 간단하게 cmd를 통해 설치 확인

cmd 창에서 sqlplus로 오라클 접속되면 잘 설치된거임