정렬
- 문자열이 숫자형식의 값을 가졌을 때, 정렬을 수행하면 자릿수에 대한 정렬을 한다.
- 각 자릿수 기준 오름차순 진행 ( EX 202의 두번째 자리 0보다 21의 두번째 자리 1이 높으므로 뒤로 감)
function
- 자주 사용될 기능을 미리 구현하여 제공하는 것.
- 오라클 자체에서 제공하는 함수 (built-in function) 와 사용자가 정의(user define function)
- 사용자 정의 함수 : PL/SQL에서 정의할 수 있다.
- create or replace function 함수명(매개변수,,,,)
return 데이터형
is
변수 선언
begin
코드 작성
return 값;
end;
/
- insert, where절 사용, having절에서 사용
- 사용법 )
- 함수명( 컬럼명,, )
패키지명.함수명( 컬럼명,, )
- 수치 : abs, round, ceil, floor, trunc**
- 반올림 : round(값), round(값, 자릿수)
- 양의 정수를 사용하면, 실수부의 자릿수가 설정되고 반올림해서 볼 자릿수를 설정
- 음의 정수를 사용하면, 정수부의 자릿수가 설정되고 해당 자리에서 반올림하여 보여진다.
- 올림 : ceil(값)
- 실수부에 하나라도 값이 있다면 올림처리
- 내림 : floor(값)
- 절사 : trunc(값), trunc(값, 자릿수)
- 양의 정수를 사용하면, 실수부의 자릿수가 설정되고 설정된 자리를 절사한다.
- 음의 정수를 사용하면, 정수부의 자릿수가 설정되고 해당 자리를 절사한다.
- 문자열함수 : upper, lower, initcap, length, instr, substr, trim, ltrim, rtrim, lpad, rpad, replac,,,,
- 문자열의 길이 : length(값)
- 대문자 : upper(값)
- 소문자 : lower(값)
- 첫글자만 대문자로 변경( 띄어쓰기 이후 글자에도 적용된다. ) : initcap(값)
- 특정문자의 인덱스 얻기 => 문자열이 없다면 0이 나온다.
- instr(값, 찾을 문자열, 시작인덱스)
- 자식문자열 얻기 : substr(값, 시작인덱스, 자를글자수)
- 문자열의 앞, 뒤 공백을 제거 : trim
- trim(값) trim(' ABC ') => 'ABC'
- 문자열의 앞 공백을 제거 : ltrim
- ltrim(값) ltrim(' ABC ') => 'ABC '
- 문자열의 뒷 공백을 제거 : rtrim
- rtrim(값) rtrim(' ABC ') => ' ABC'
- 문자열의 앞, 뒤에 특정 문자 채우기 :
lpad(값, 총글자수, 채울문자열),rpad(값, 총글자수, 채울문자열)
- lpad('ABCDE',10,'#')
- rpad('ABCDE',10,'#')
- 조건 함수 : decode , case
- decode : PL/SQL에서는 사용할 수 없다.
- 조건을 부여하고 조건에 맞으면 짧을 코드를 실행해야할 때 사용
- decode(컬럼명, 비교값, 실행코드,비교값,실행코드,,,,비교값이 없을 때 실행코드)
- case문
- select 조회 컬럼에서 사용.
- 조회된 결과로 비교하여 다른 코드를 실행할 때 사용.
- 조회 결과로 비교할 때 실행될 코드가 긴 경우 사용.
- 문법 )
- select case 컬럼명
when 비교값 then 실행코드
when 비교값 then 실행코드
when 비교값 then 실행코드
else 비교값이 없을 때 실행코드
end alias
- 집계함수 : count, sum, avg, max, min,rollup,cube,,,,
- 모든 컬럼 값을 모아서 하나로 만드는 일.
- group by절과 함께 사용하면 그룹별 집계를 얻을 수 있다.
- select 컬럼명, 집계함수(일반컬럼명)
from 테이블명
group by 그룹으로 묶을 컬럼명
- 일반컬럼명
- 그룹별 집게를 얻기 위한 컬럼
- group by 절에 포함되지 않는 함수도 사용 가능.
- 여러 레코드가 조회되는 컬럼과 함께 사용하면 error가 발생.
- where절에서는 집계함수를 직접 사용할 수 없다.
- count : 레코드의 총 수를 얻을 때 사용. null인 컬럼은 count에 포함되지 않는다.
- sum : 컬럼의 합계를 구할 때 사용
- avg : 컬럼의 평균을 구할 때 사용
- max : 컬럼의 값 중 최고 값을 구할 때 사용
- min : 컬럼의 값 중 최저 값을 구할 때 사용
- 그룹별 합과 총계 얻기
- rollup, cube 사용
- 소계 후 전체 합계 얻기
- group by rollup(그룹으로 묶을 컬럼명)
- 전체 합계 출력후 소계 얻기
- group by cube
- rollup과 cube는 여러 컬럼이 그룹으로 묶이면 다른 결과를 보여준다.
- 사용예 )
- group by rollup(컬럼명,컬럼명)
- 소계 후 합계가 출력되고, 마지막에 총 합계가 출력된다.
- group by cube(컬럼명,컬럼명,,)
- 그룹별 전체 합계가 먼저 출력된 후 합계와 소계가 출력된다.
- 변환함수 : to_char , to_date , to_number
- 문자 변환 : to_char
- 숫자 -> 문자열로 변환 (2024 -> 2,024) : 패턴의 길이가 데이터의 크기보다 작다면 적용 결과가 ###으로 반환된다.
- to_char(값,'pattern');
- 날짜 변환 : to_date( 날짜형식의 문자열 ,' 패턴')
- 날짜 형식의 문자열을 날짜로 변환.
- 패턴에 패턴 문자나 특수문자가 아닌 문자열을 사용하면 error발생 => "문자열" 패턴이 길면 error발생
- 숫자 변환 : to_number('문자열');
- 랭크함수 : rank over, row_number over
- 조회되는 결과에 순차적인 번호를 붙여 조회하는 함수.
- rank() over()는 동일 순위가 출력된다.
- row_number() over()는 동일 순위가 출력되지 않는다.
- order by와 함께 사용하지 않는다. => 순위가 섞인다.
- 사용법 )
- rank() over((partition by : 생략가능) 컬럼명 order by 순위를설정할컬럼명 정렬조건(asc,desc))
- row_number() over((partition by : 생략가능) 컬럼명 order by 순위를설정할컬럼명 정렬조건(asc,desc))
- 날짜함수 : months_between, add_months
- 날짜에 + 연산자를 사용하면 일자가 증가한다
- 날짜에 - 연산자를 사용하면 일자가 감소한다.
- add_months : 월을 더할 때 사용하는 함수
- months_between : 두 날짜간의 개월 차이 ( 실수로 결과가 나온다. )
- months_between(큰날짜, 작은날짜)
- null변환함수 : nvl, nvl2
- null은 숫자도 문자열도아닌 사용할 수 없는 값.
- null은 연산되면 연산의 결과가 null로 나온다.
- nvl과 nvl2함수로 null인 컬럼 값을 다른 값으로 변경할 수 있다.
- nvl : 컬럼값이 null인 경우 제공할 값을 설정할 때.
- nvl2 : 컬럼값이 null인 경우와 null이 아닌 경우에 제공할 값을 설정할 때 사용.
- 사용법 )
- nvl(컬럼명, null일때 제공할 값) -> 컬럼의 데이터형과 일치한 값만 사용가능.
- nvl2(컬럼명,null이 아닐때 제공할 값,null일 때 제공할 값)
- 난수 생성 함수
- dbms_random 패키지를 사용
- 사용법 )
- dbms_random.string(생성문자,자릿수)
- 생성문자
- u : 대문자
- l : 소문자
- a : 대,소문자가 합쳐진 난수
- x : 대문자와 숫자가 합쳐진 난수
- p : 특수문자혼합
컬럼에 NLL이 입력되는 상황
- number, date는 insert할 때 컬럼명이 생략되면 null이 입력된다.
- varchar2, char는 insert할 때 컬럼명이 생략되거나''가 입력되면 null이 입력된다.
dual 테이블
- 모든 계정이 사용할 수 있는 가상 테이블.
- 같은 이름으로 테이블이 생성되면 사라지고, 테이블을 삭제하면 다시 사용할 수 있다.
- 함수를 테스트할 때 사용하면 편리
- select에 정의하는 값으로 컬럼을 만들고, 한 행 조회를 수행한다.
subquery
- query문 안에 select query문 정의하는 문법.
- 단수행, 복수행 서브쿼리 모두 사용할 수 있다.
- 단수행(Single-row subquery) : 서브쿼리의 조회 결과가 하나
- 복수행(Multi-row subquery) : 서브쿼리의 조회 결과가 여러개
- 조회결과를 사용하여 쿼리문을 실행해야 할 때.
- create, insert, update, delete, select에서 사용할 수 있다.
