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