1. 내장함수(오라클)
    1. 단일행 함수
      1. 숫자함수 : ABS, CEIL, COS, EXP, FLLOR, LN, LOG, MOD, POWER, ROUND(number), SIGN, TRUNC(number)
      2. 문자반환함수 : CHR, CONCAT, LOWER, LPAD, LTRIM, STR, REPLACE, RPAD, RTRIM, SUBSTR, TRIM, UPPER
      3. 숫자반환함수 : ASCII, INSTR, LENGTH
      4. 날짜/시간함수 : ADD_MONTHS, LAST_DAY, NEXT_DAY, ROUND(date), SYSDATE, TO_CHAR(DATETIME)
      5. 변환함수 : ASCIISTR, CONVERT, TO_BINARY_DOUBLE, TO_BINARY_FLOAT, TO_CHAR(character), TO_CHAR(datetime), TO_CHAR(number), TO_DATE, TO_NUMBER
      6. 인코딩과 디코딩 : DECODE, DUMP, VSIZE
      7. NULL 관련함수 : COALESCE, NULLIF, NVL
    2. 집계함수 : AVG, COUNT, CUME_DIST, FIRTST, LAST, MAX, MEDIAN, MIN, PERCENT_RANK, PERCENTILE_CONT, SUM
    3. 분석함수 : AVG, CORR, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAST_VALUE, LEAD, MAX, MIN, RRANK, SUM
  2. 내장함수 사용예
    1. 숫자함수
      1. SELECT post-50000 FROM TSTUDENT;
      2. SELECT (post-50000)/15 FROM TSTUDENT;
      3. SELECT (post-50000)/15.0 FROM TSTUDENT;
      4. SELECT abs((post-50000)/15.0) FROM TSTUDENT;
      5. — SELECT ceil((post-50000)/15.0) FROM TSTUDENT;
      6. — SELECT floor((post-50000)/15.0) FROM TSTUDENT;
      7. SELECT round((post-50000)/15.0, 1) FROM TSTUDENT;
      8. — SELECT power((post-50000)/15.0, 2) FROM TSTUDENT;
      9. — SELECT sqrt((post-50000)/15.0) FROM TSTUDENT;
      10. SELECT sign((post-50000)/15.0) FROM TSTUDENT;
    2. 문자함수
      1. SELECT hakbun || “-“ || name FROM TSTUDENT;
      2. SELECT hakbun, name, lower(gender) FROM TSTUDENT;
      3. SELECT replace(hakbun, “2015”, “2023”), name, lower(gender) FROM TSTUDENT;
      4. SELECT replace(hakbun, “2015”, “2023”) as hakbun, name, lower(gender) FROM TSTUDENT;
      5. SELECT title, length(title) as len FROM TSUBJ;
      6. SELECT substr(name, 1, 1) as lastname FROM TSTUDENT;
      7. SELECT substr(name, 1, 1) as lastname, count(*) as cnt FROM TSTUDENT
        group by substr(name, 1, 1);
    3. 날짜/시간 함수
  3. 부속질의
    1. 중첩질의 – WHERE 부속질의
      1. SELECT * FROM tsungjuk
        WHERE final >= (SELECT avg(final) FROM tsungjuk)
    2. 스칼라 부속질의 – SELECT 부속질의
      1. SELECT
        sj.hakbun, avg(sj.final)
        FROM tsungjuk sj
        GROUP BY sj.hakbun
      2. SELECT
        (SELECT name FROM tstudent st WHERE st.hakbun=sj.hakbun), sj.hakbun, avg(sj.final)
        FROM tsungjuk sj
        GROUP BY sj.hakbun
      3. SELECT
        (SELECT name FROM tstudent st WHERE st.hakbun=sj.hakbun) as name, sj.hakbun as hakbun, avg(sj.final) as final
        FROM tsungjuk sj
        GROUP BY sj.hakbun
    3. 인라인 뷰 – FROM 부속질의
      1. SELECT
        sj.hakbun, avg(sj.final)
        FROM tsjungk sj
        WHERE sj.hakbun < ‘201600000’
        GROUP BY sj.hakbun
      2. SELECT
        st.name, sj.hakbun, avg(sj.final)
        FROM (SELECT name, hakbun FROM tstudent WHERE hakbun < ‘201600000’) st, tsungjuk sj
        WHERE st.hakbun = sj.hakbun
        GROUP BY sj.hakbun
  4. 뷰(View)
    1. 뷰 생성 : CREATE VIEW 뷰이름 AS <SELECT 문>
    2. 뷰 수정 : CREATE OR REPLACE VIEW 뷰이름 AS <SELECT 문>
    3. 뷰 삭제 : DROP VIEW 뷰이름
  5. 인덱스(Index)
    1. 인덱스 생성 : CREATE INDEX 인덱스이름 ON 테이블(필드1, 필드2, …)
      1. CREATE INDEX ix_student ON tstudent(hakbun, name)
    2. 인덱스 재구성 : ALTER INDEX ix_student REBUILD
      1. 단편화된 인덱스를 버리는 작업
    3. 인덱스 삭제 : DROP INDEX ix_student
error: Content is protected !!