학원 34일차 - Oracle

仁·2021년 7월 10일
오라클학원
0

2021.05.14

수업 정리

  1. 자료형
    • number, varchar2, charm date
  2. select
    a. from절
    b. select 컬럼 리스트 -> 별칭(Alias), 산술 연산자
    c. where절 -> 연산자(산술, 비교, 논리), betweenm, in, like, is null

ex06_column.sql

case

  • 모든 곳에서 사용이 가능

    • 컬럼 리스트에서 사용
    • 조건절에서 사용
    • 정렬에서 사용

  • 자바의 if/switch와 비슷한 행동

  • 문장 단위의 제어(X) -> 값을 제어(O)

  • 조건을 만족하지 못하는 컬럼은 null을 반환(★★★★★)

-- 이름과 성별 가져오기
select
    last || first as name, 
    gender
from tblComedian;

-- m은 남자,f는 여자로 
-- 원본테이블을 가져와서 가공한 가상의 컬럼 
-- case end -> 자바의 {} 블럭 역할
select
    last || first as name, 
    case
        when gender = 'm' then '남자' -- 'm'이라면 '남자'를 반환
        when gender = 'f' then '여자' -- 'f'라면 '여자'를 반환
    end as gender
from tblComedian;
select 
    name,
    case
        when continent = 'AS' then '아시아'
        when continent = 'EU' then '유럽'
        when continent = 'AF' then '아프리카'
        -- when continent = 'AU' then '오세아니아'
        -- when continent = 'SA' then '남아메리카'
        -- else '기타' -- 앞의 세조건을 만족 못하는 나머지는 '기타'로 반환
        -- when continent = 'AU' then continent -- 조건의 반환값이 반드시 상수일 필요가 없다.(자신의 컬럼값을 돌려주는 것도 가능)
        -- else continent --  나머지는 원래의 값으로 리턴한다.
        -- else capital -- 의미는 다른 항목들과 동일해야한다.
        else population -- 자료형이 다른 항목들과 동일
        else '기타'
    end as continent
from tblCountry; 
select
    last || first as name,
    weight,
    case
        when weight > 100 then '과체중'
        when weight > 50 then '정상체중'
        when weight > 0 then '저체중'
    end as state,
    case
        when weight between 50 and 100 then '정상체중'
        else '비정상체중'
    end as state2    
from tblComedian; 
select
    title,
    case
        when completedate is null then '완료'
        when completedate is not null then '미완료'
    end as state
from tblTodo;
select
    name,
    jikwi,
    case
        when jikwi in ('과장', '부장') then '세단'
        when jikwi in ('대리', '사원') then '경차'
    end as car
from tblInsa;
select
    name,
    buseo,
    jikwi,
    -- 3년 미만: 주니어
    -- 3년 ~ 7년 미만 : 시니어
    -- 7년 이상 : 익스퍼트
    case
        when ibsadate > '2018-05-14' then '주니어'
        when ibsadate <= '2018-05-14' and ibsadate > '2014-05-14' then '시니어'
        when ibsadate <= '2014-05-14' then '익스퍼트'
    end as state
from tblInsa; 
-- 여자친구 유무
select
    name,
    case
    when couple is null then '없음'
    when couple is not null then '있음'
    end  as state
from tblMen;
-- [문제]
-- tblInsa. sudang
-- 결과셋 : name, buseo, jikwi, sudang, 추가수당(계산)
-- 정책1 : 직위별 수당 + a > 부장(X2), 과장(X1.7), 대리(X1.5), 사원(X1.3)
-- 정책2 : 직위별 수당 + a > 부장, 과장(X2), 대리, 사원(X1.5)

ex07_order.sql

정렬, sort

  • order by절
  • 레코드를 정렬
  • 원본 테이블의 레코드 정렬(XXX) - 오라클 데이터 베이스에 있는 테이블내의 레코드 순서는 우리가 손댈 수 없다.(절대!)
  • 원본 테이블 -> select -> 결과 테이블 (결과셋)의 정렬(★★★★)
  • 오름차순 정렬, 내림차순 정렬
  • 특정 컬럼값을 기준으로 레코드를 정렬한다. (★★★★)

[WITH ]
SELECT column_list
FROM table_name
[WHERE search_condition][GROUP BY group_by_expression]
[HAVING serach_condition]ORDER BY order_expression [ASC|DESC]];

SELECT column_list
FROM table_name
[WHERE search_condition]ORDER BY order_expression [ASC|DESC]];

SELECT 컬럼리스트
FROM 테이블명
WHERE 조건
ORDER BY 정렬기준;

정렬의 기준이 될 수 있는 자료형(컬럼의 자료형)

  1. 숫자형
    • 10 -> 20 -> 30
    • 30 -> 20 -> 10
  2. 문자형
    • '가' -> '나' -> '다'
    • '다' -> '나' -> '가'
  3. 날짜시간형
    • '2019' -> '2020' -> '2021'
    • '2021' -> '2020' -> '2019'

select를 구성하는 모든 절들은 실행 순서가 있다. (불변) -> 무조건 암기(이해)★★★★★

  1. FROM절
  2. WHERE절
  3. SELECT절
  4. ORDER BY절

고정된 컬럼을 정렬 기준으로 사용

-- 체중 오름차순으로 정렬
select
    last || first as name,
    weight
from tblComedian
-- order by weight asc;  -- 체중을 기준으로 정렬, 정렬 순서 지정을 안하면 오름차순 (asc 생략가능(ascending 오름차순))  
order by weight desc; -- descending 내림차순
-- order by 대상 컬럼이 결과셋에 포함되지 않아도 상관없다. -> 보편적으로는 포함시킨다.
select
    last || first as name -- weight X -> 없으면 무슨 기준으로 정렬되는지 알 기 어려움.
from tblComedian
order by weight desc; 
select                      -- 3. 원하는 컬럼만 선별
    last || first as name,
    weight,
    gender
from tblComedian            -- 1. 테이블 지정 (전체데이터)    
where gender = 'm'          -- 2. 원하는 레코드만 선별 : f(여자) 탈락 
order by weight desc;       -- 4. 결과셋의 정렬
-- 다중 정렬(정렬의 차원 높이기.)
select 
    name, buseo, jikwi, city
from tblInsa
    -- order by buseo asc;  -- 1차 정렬 (기준 1개)
    -- order by buseo asc, jikwi asc; -- 2차 정렬(기준 2개)
    order by buseo asc, jikwi asc, city asc; -- 3차 정렬 (기준 3개)
    -- order by 2 asc, by 3 asc, by 4 asc; -- 첨자로 컬럼을 지정 -> 사용금지!!!!

계산된 값을 정렬 기준으로 사용

-- 수당 + 기본급(계산된 값)의 내림차순으로 정렬
select
    name, basicpay, sudang, basicpay + sudang   
from tblInsa
    order by basicpay + sudang desc;  -- ★★★★
-- 직위별 수당 + a > 부장, 과장(X2), 대리, 사원(X1.5)
select
    name, sudang, jikwi,
    case
        when jikwi in ('부장', '과장') then sudang * 2
        when jikwi in ('사원', '대리') then sudang * 1.5
    end as sudang2 -- as 생략가능(그래도 쓰는 걸 권장)
from tblinsa
    -- order by sudang2 desc; 아래와 같은 구문
    order by case
        when jikwi in ('부장', '과장') then sudang * 2
        when jikwi in ('사원', '대리') then sudang * 1.5
    end desc;
    
 /*
 
 컬럼이기때문에 컬럼이 오는 자리에 올 수 있음!!!
 case
      when jikwi in ('부장', '과장') then sudang * 2
      when jikwi in ('사원', '대리') then sudang * 1.5
 end
    
 */   
-- 팁!!(★★★★)
-- 직위를 정렬(부장,과장,대리,사원) -> 가나다순(x) -> 직위순으로!!
select
    name, buseo, jikwi
from tblInsa
    order by jikwi desc;


select
    name, buseo, jikwi
--    case
--        when jikwi = '부장' then 1
--        when jikwi = '과장' then 2  
--        when jikwi = '대리' then 3
--        when jikwi = '사원' then 4
--    end as no
from tblInsa
	-- order bu no asc; -> 사용가능 -> 결과에 123가 결과테이블에 나타난다... -> Alias 사용불가..
    order by case
        when jikwi = '부장' then 1
        when jikwi = '과장' then 2
        when jikwi = '대리' then 3
        when jikwi = '사원' then 4
    end asc;

ex08_aggregation_function.sql

집계 함수, aggregation Function

  1. count()
  2. sum()
  3. avg()
  4. max()
  5. min()

1. count()

  • 결과셋의 레코드 개수를 반환
  • number count (컬럼명)
  • null은 카운터에서 제외한다.(★★★★★)
select name from tblCountry;
select count(name) from tblCountry; -- 14 (레코드 수)
select count(name)			    -- 3. 33개
from tblinsa	   			    -- 1. 60개
where city in ('서울', '경기');  -- 2. 33개
select area from tblCountry;
select count(area) from tblCountry;
select population from tblCountry;
select count (population) from tblCountry; -- 13 -> null이 들어있는 항목은 안센다.
-- ORA-00937: not a single-group group function
-- 컬럼리스트에 집계함수와 단일 컬럼은 동시에 사용할 수 없다. ★★★★★
-- 에러 : 집합정보, 개인정보는 성질이 다르기 때문에 같은 테이블을 사용할 수 없다. (무결성이 깨진다.)
select count(name), name from tblcountry;

-- 따로 확인해야한다.
select count(name) from tblcountry;
select name from tblcountry;

-- 집합 정보끼리는 동시에 사용 가능
select count(name), count(area) from tblcountry;

-- 매개변수의 컬럼은 하나만 가능
-- 에러 : ORA-00909: invalid number of arguments
select count(name, area) from tblcountry;
-- 살짝 고민? - ()안에 어떵 컬럼을 집어 넣지?
-- tblCountry의 나라 개수?
select count(name) from tblcountry; -- 14
select count(capital) from tblcountry; -- 14
select count(area) from tblcountry; -- 14
select count(population) from tblcountry; -- 13 (null은 제외..)


select count(*) from tblcountry; --  14 -> 무조건 모든 레코드 개수가 반환된다.(행을 센다. - 행에 하나의 데이터만 있어도 레코드(행)전체를 보기 때문에 데이터가 있다고 생각한다.) ★★★★★
select * from tblcountry;
select * from tbltodo;

-- 아직 안한 일의 갯수 8개
select count(*) from tbltodo where completedate is null;
 
-- 한 일의 갯수? 12개
select count(*) from tbltodo where completedate is not null;
 
-- 안한 일의 갯수, 한 일의 갯수 -> 하나의 결과셋으로
select
    count(*) as "전체 할 일 갯수",
    count(completedate) as "한 일의 갯수", 
    count(*) - count(completedate) as "아직 안한 일의 갯수"
from tbltodo;
-- 연락처가 없는 직원 수 
select
    count(*) as "총 직원수", -- 60
    count(tel) as "연락처가 있는 직원수", -- 57
    count(*) - count(tel) as "연락처가 없는 직원수" -- 3
from tblInsa;
-- tblInsa -> buseo컬럼 -> 어떤 부서들이 있나요?
-- tblInsa -> buseo컬럼 -> 부서가 몇개 인가요? -> 숫자세기 (중복제거)
select count (distinct buseo) from tblInsa;
-- tblComedian 남자 몇명? 여자 몇명?
select * from tblcomedian;

select count(*) from tblcomedian where gender= 'm';
select count(*) from tblcomedian where gender= 'f';
--  전체인원수, 남자인원수, 여자인원수 -> 결과셋
-- 힌트 : count() + case end + null 존재
select
    count(*) as "전체 인원수",
    count(case
        when gender = 'm' then 1 -- null빼고 다세기
    end) as "남자 인원수",
    count (case
        when gender = 'f' then 1 -- null빼고 다세기
    end) as "여자 인원수" 
from tblcomedian;
select avg(basicpay) from tblInsa; -- 1556526.666...

-- 평균 급여보다 많이 받는 직원들 명단을 가져오시오.
select * from tblInsa where basicpay > 1556526; -- 27

-- 에러 : ORA-00934: group function is not allowed here ★★★★★
-- 개인데이터와 집합데이터는 동시에 사용할 수 없다. 
-- where절에는 집계함수를 사용할 수 없다. > where절은 개인에 대한 조건절(집합 정보를 가져올 수 없다.)★★★★★
select * from tblInsa where basicpay > avg(basicpay); -- 27
  • 문제
-- tblCountry. 
-- 아시아(AS)와 유럽(EU)에 속한 나라의 개수?? -> 7개
select count(*) from tblCountry where continent in ('AS', 'EU');

-- 인구수가 7000 ~ 20000 사이인 나라의 개수?? -> 2개
select count(*) from tblCountry where population between 7000 and 20000;

-- hr.employees
-- job_id > 'IT_PROG' 중에서 급여가 5000불이 넘는 직원이 몇명? -> 2명
select count(*) from hr.employees where job_id = 'IT_PROG' and salary >= 5000;

-- tblInsa
-- tel. 010을 안쓰는 사람은 몇명?(연락처가 없는 사람은 제외) -> 42명
select count(*) from tblinsa where not tel like '010%' and tel is not null;

-- city. 서울, 경기, 인천 -> 그 외의 지역 인원수? -> 18명
select count(*) from tblinsa where city not in ('서울', '경기', '인천');
    
-- 80년대생 + 여자 직원 총 몇명? -> 9명
select count(*) from tblinsa where ssn like '8%-2%';

2. sum()

  • number sum(컬럼명)
  • 해당 컬럼값들의 합을 구한다.
  • 숫자형만 대상(문자형X, 날짜형X)
select weight from tblcomedian; -- 몸무게 총합

select sum(weight) from tblcomedian;

select sum(first) from tblcomedian; -- 문자열은 합할 수 없음 에러 : ORA-01722: invalid number

select
    sum(basicpay), sum(sudang),
    sum(basicpay) + sum(sudang),
    sum(basicpay + sudang)
from tblInsa;

3. avg()

  • number avg (컬럼명)
  • 해당 컬럼값들의 평균값을 반환한다.
  • 숫자형만 가능하다.
  • 해당 컬럼이 null을 가진 항목은 제외한다.★★★★★★★
-- 60명 전원의 급여
select
    basicpay
from tblinsa;

-- 직원들의 모든 급여의 합
select
    sum(basicpay)
from tblinsa;
select
    sum(basicpay), count(*),
    sum(basicpay) / count(*), -- 평균
    avg(basicpay) -- 평균
from tblinsa;
-- 평균인구수? 14475.14.., 15588
select
    sum(population) / count(*),
    sum(population) / count(population), -- 케냐 인구수 null -> 13
    avg(population), -- null은 취급 x ★★★★★★★★★★
    count(*), -- 14
    count(population) -- 13
from tblcountry;
-- 회사 성과급 지급
-- : 10명 팀원 -> 7명 참여 프로젝트 수익 발생, 3명 관련 없음
-- 1. 균등 지급 -> 수익 / 모든 팀원수 = sum() / count(*)
-- 2. 차등 지급 -> 수익 / 참여 팀원수 = sum() / count(참여팀원) = avg()★★★★★★★

4. max()

  • object max(컬럼명) : 최댓값 반환
  • 숫자형, 문자형, 날짜형

5. min()

  • object min(컬럼명) : 최솟값 반환
  • 숫자형, 문자형, 날짜형
-- 가장 높은 급여, 적은 급여
select max(basicpay), min(basicpay) from tblInsa;

-- 가나다순중 가장 먼저, 가장 마지막
select max(name), min(name) from tblInsa;

-- 입사날짜 가장 최근, 가장 오래된
select max(ibsadate), min(ibsadate) from tblInsa;
  • 문제
-- 1. 유럽과 아프리카에 속한 나라의 인구 수 합? tblCountry
select sum(population)
from tblcountry
where continent in ('EU', 'AF');

-- 2. 매니저(108)이 관리하고 있는 직원들의 급여 총합? hr.employees
select sum(salary) from hr.employees
where manager_id = 108;

-- 3. 직업(ST_CLERK, SH_CLERK)을 가지는 직원들의 급여 합? hr.employees
select sum(salary) from hr.employees
where job_id in ('ST_CLERK', 'SH_CLERK');

-- 4. 서울에 있는 직원들의 급여 합(급여 + 수당)? tblInsa
select sum(basicpay + sudang) from tblinsa
where city = '서울';

-- 5. 장급(부장+과장)들의 급여 합? tblInsa
select sum(basicpay) from tblinsa
where jikwi in ('부장', '과장');


-- 1. 아시아에 속한 국가의 평균 인구수? tblCountry
select avg(population) from tblcountry
where continent = 'AS';

-- 2. 이름(first_name)에 'AN'이 포함된 직원들의 평균 급여?(대소문자 구분없이) hr.employees
select avg(salary) from hr.employees
where first_name like '%AN%' or first_name like '%An%' or first_name like '%aN%' or first_name like '%an%';

-- 3. 장급(부장+과장)의 평균 급여? tblInsa
select avg(basicpay) from tblinsa
where jikwi in ('부장', '과장');

-- 4. 사원급(대리+사원)의 평균 급여? tblInsa
select avg(basicpay) from tblinsa
where jikwi in ('대리', '사원');

-- 5. 장급(부장,과장)의 평균 급여와 사원급(대리,사원)의 평균 급여의 차액? tblInsa
select
	avg(case
		when jikwi in ('부장', '과장') then basicpay
	end)
	-
	avg(case
		when jikwi in ('대리', '사원') then basicpay
	end)
from tblinsa


-- 1. 면적이 가장 넓은 나라의 면적은? tblCountry
select max(area) from tblcountry;

-- 2. 급여(급여+수당)가 가장 적은 직원은 총 얼마를 받고 있는가? tblInsa
select min(basicpay + sudang) from tblinsa;

ex09_numeric_function.sql

round()

  • 반올림 함수
  • number round(컬럼명) : 정수 반환
  • number round(컬럼명, 소수이하 자릿수) : 실수 반환
  • 숫자형, 날짜형
select height, weight, height / weight, round(height / weight) from tblComedian;

select 10 / 3 from tblComedian; -- 3.33333... -> 10개
select 10 / 3 from tblInsa; -- 3.33333.... -> 60개
select 10 / 3 from tblInsa where name = '홍길동';

select * from dual; -- 시스템 테이블★★★★ -> 특정테이블에 종속된 값이 아닌 값을 확인할 때 사용
select 10 / 3 from dual;

select round(987.654), round(987.654, 1), round(987.654, 2) from dual;

select round(avg(basicpay)) from tblInsa;

select round(name) from tblInsa; -- 에러
select round(ibsadate), ibsadate from tblInsa;

select 
    to_char(round(sysdate), 'yyyy-mm-dd hh24:mi:ss'), 
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
from tblInsa;

select
    sysdate, -- 현재 시각
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
from dual;

floor(), trunc()

  • 절삭 함수
  • 무조건 내림 함수
  • 자바의 정수/정수 -> 몫
  • number floor(컬럼명)
  • number trunc(컬럼명 [, 소수이하자릿수])
select 
    5.6789,
    round(5.6789),  -- 6
    floor(5.6789),  -- 5
    trunc(5.6789),  -- 5
    trunc(5.6789, 1)  -- 5.6
from dual;

ceil()

  • 무조건 올림 함수
  • number ceil(컬럼명)
select
    3.1,  -- 3.1
    round(3.1),  -- 3
    ceil(3.1),  -- 4
    ceil(3.000000000000000000001),  -- 4
    floor(3.999999999999999999999)  -- 3
from dual;

mod()

  • 나머지 함수
  • number mod(피제수, 제수)
select mod(10, 3) from dual;  -- 1
-- 100분 -> 1시간 40분
-- 100 / 60 -> 몫(시간)
-- 100 % 60 -> 나머지(분)
select
    floor(100 / 60) as 시,
    mod(100, 60) as 분
from dual;
select
    abs(-10), abs(10),
    power(2, 8),
    sqrt(4)
from dual;

ex10_string_function.sql

문자열 함수

upper(). lower(), initcap()

  • varchar2 upper(컬럼명)
  • varchar2 lower(컬럼명)
  • varchar2 initcap(컬럼명) -> 첫글자만 대문자로 변환
select
    first_name,
    upper(first_name),
    lower(first_name)
from employees;


select initcap('abc') from dual; -- Abc 첫 글자만 대문자로
-- 이름에 'an'이 포함 검색(대소문자 상관없이)
select first_name from employees
    where first_name like '%AN%' or first_name like '%an%'
        or first_name like '%An%' or first_name like '%aN%';

        
select first_name from employees
    where upper(first_name) like '%AN%';

substr()

  • 문자열 추출함수
  • varchar2 substr(컬럼명, 시작위치, 가져올 문자개수)
  • varchar2 substr(컬럼명, 시작위치)
  • 서수를 1부터 시작(★★★★★)
select
    '가나다라마바사아자차카타파하',
    substr('가나다라마바사아자차카타파하', 5, 3), -- 마바사
    substr('가나다라마바사아자차카타파하', 5) -- 마바사아자차타파하
from dual;
-- 주민등록번호 (1 : 남자 2 : 여자)
select count(*) from tblInsa where ssn like '%-1%';
select count(*) from tblInsa where substr(ssn, 8, 1) = '1';
select count(*) from tblInsa where substr(ssn, 8, 1) = '2';
select count(*) from tblInsa where substr(ssn, 8, 1) = '1' or substr(ssn, 8, 1) = '3';
select count(*) from tblInsa where substr(ssn, 8, 1) in ('2', '4');
select 
    name, ssn,
    case
        when substr(ssn, 8, 1) = '1' or substr(ssn, 8, 1) = '3' then '남자'
        when substr(ssn, 8, 1) in ('2', '4') then '여자'
    end as gender
from tblInsa;
select
    name, '19' || substr(ssn, 1, 2) as birthyear
from tblInsa;
-- 장급(부장, 과장)들은 어떤 성을 가지고 있는지?
select
    distinct substr(name, 1, 1)
from tblInsa
    where jikwi in ('부장', '과장')
        order by substr(name, 1, 1);
-- 직원 성? 26가지
select    
    distinct substr(name, 1, 1)
from tblInsa;
-- 각각의 성이 몇 명인지?
select
    count(case
        when substr(name, 1, 1) = '김' then 1
    end) as "김",
    count(case
        when substr(name, 1, 1) = '이' then 1
    end) as "이",
    count(case
        when substr(name, 1, 1) = '박' then 1
    end) as "박",
    count(case
        when substr(name, 1, 1) = '정' then 1
    end) as "정",
    count(case
        when substr(name, 1, 1) = '최' then 1
    end) as "최",
    count(case
        when substr(name, 1, 1) not in ('김','이','박','정','최') then 1
    end) as "기타"
from tblInsa;
-- 태어난 월별 순으로 정렬(ssn -> substr(ssn, 3, 2))
select
    *
from tblInsa
    order by substr(ssn, 3, 2) asc;

length()

  • 문자열 길이
  • number length(컬럼명)
-- 컬럼 리스트에서 사용
select name, length(name) from tblCountry;
-- 조건절에서 사용
select name from tblCountry where length(name) > 3;
select name from tblCountry where length(name) between 4 and 6;
select name, capital from tblCountry where length(name) > length(capital);
-- 정렬에서 사용
select name, length(name) from tblCountry order by length(name) desc, name asc;
-- 제목이 길면 자르고 ...하기
select  
    case
        when length(title) >= 8 then substr(title, 1, 8) || '..'
        else title
    end as title
from tblTodo;
  • 문제
-- hr.employees
-- 1. 전체 이름(first_name + last_name : fullname)이 가장 긴-> 짧은 사람 순으로 가져오기
--    컬럼리스트 : first_name + last_name, length(fullename)
select
    first_name || ' ' || last_name as fullname,
    length(first_name || ' ' || last_name) as length
from employees
    order by length(first_name || ' ' || last_name) desc;


-- 2. 전체 이름(first_name + last_name : fullname)이 가장 긴 사람이 몇글자? 가장 짧은 사람이 몇글자?
--    컬럼리스트 : 숫자만 출력
select
    max(length(first_name || ' ' || last_name)) as max,
    min(length(first_name || ' ' || last_name)) as min
from employees;

-- 3. last_name이 4자인 사람들의 first_name이 궁금하다. 정렬 : first_name 길이 오름차순으로
select
    first_name, last_name
from employees
    where length(last_name) = 4
        order by length(first_name) asc;

instr()

  • indexOf()
  • 검색어의 위치를 반환
  • one-based index(서수가 1부터 시작)
  • number instr(컬럼명, 검색어)
  • number instr(컬럼명, 검색어, 시작위치)
select
    '안녕하세요. 홍길동님' as c1,
    instr('안녕하세요. 홍길동님', '홍길동') as c2, -- 8
    instr('안녕하세요. 홍길동님', '아무개') as c3, -- 0
    instr('안녕하세요. 홍길동님. 잘가세요. 홍길동님', '홍길동', 11) as c4,  -- 20
    instr('안녕하세요. 홍길동님. 잘가세요. 홍길동님', '홍길동', instr('안녕하세요. 홍길동님', '홍길동')) as c5 -- 8 -- number instr(컬럼명, 검색어, 시작위치)
from dual;
-- 제목입니다. -> (*)제목입니다.
select
    case
        when instr(title, '자바') > 0 then '(*)' || title
        else title
    end as title
from tblTodo;

lpad(), rpad(), left padding, right padding

  • varchar2 lpad(컬럼명, 개수, 문자)
  • varchar2 rpad(컬럼명, 개수, 문자)
  • 여백 채우기
select 
    '1',
    lpad('1', 3, '0'), -- ★
    lpad('1', 3, '@'),
    lpad(' ', 20, '='),
    rpad('1', 3, '0'),
    rpad('1', 3, '+')
from dual;

trim(), ltrim(), rtrim()

  • varchar2 trim(컬럼명) : 좌우 공백 없애기
  • varchar2 ltrim(컬럼명) : 왼쪽 공백 없애기
  • varchar2 rtrim(컬럼명) : 오른쪽 공백 없애기
select
    '    하나    둘    셋    ',
    trim('    하나    둘    셋    '),
    ltrim('    하나    둘    셋    '),
    rtrim('    하나    둘    셋    ')
from dual;

replace()

  • 문자열 치환
  • varchar2 replace(컬럼명, 찾을 문자열, 바꿀 문자열)
select replace('홍길동', '홍', '김'), replace('홍길동', '이', '김') from dual;
-- tblInsa, 직원명, 주민번호, 성별(남자,여자)
select name, ssn, substr(ssn, 8, 1) from tblInsa;
select name, ssn,
    case
        when substr(ssn, 8,1) = '1' then '남자'
        when substr(ssn, 8,1) = '2' then '여자'
    end
from tblInsa;
select name, ssn, replace(replace(substr(ssn, 8, 1),'1','남자'), '2', '여자') from tblInsa;
select
    name,
    case
        when continent = 'AS' then '아시아'
        when continent = 'EU' then '유럽'
        when continent = 'AF' then '아프리카'
        when continent = 'AU' then '오세아니아'
        when continent = 'SA' then '아메리카'
    end as continent,
    replace(replace(replace(replace(replace(continent, 'AS', '아시아'), 'SA', '아메리카'), 'EU', '유럽'),'AF', '아프리카'), 'AU', '오세아니아')
from tblcountry;

decode()

  • 오라클 전용 함수
  • 문자열 치환
  • replace() 유사 + case end 유사
  • varchar2 decode(컬럼명, 찾을문자열 [, 찾을문자열, 바꿀문자열] x N)
  • 완전 똑같은 글자만 바꿔줌.
select
    name,
    ssn,
    decode(substr(ssn, 8, 1),'1', '남자', '2', '여자'), -- 못찾으면 null 반환
    replace(replace(substr(ssn, 8, 1),'1','남자'), '2', '여자')
from tblInsa;   
select
    name,
    continent,
    decode(continent,'AS', '아시아', 'EU','유럽', 'AF', '아프리카', 'AU', '호주', 'SA', '아메리카')
from tblcountry;
-- 팁(★)
-- decode를 유용하게 써먹는 경우

select * from tblcomedian;

select count(*) from tblcomedian where gender= 'm';
select count(*) from tblcomedian where gender= 'f';
select
    count(case
        when gender = 'm' then '남자'
    end),
    count(case
        when gender = 'f' then '남자'
    end)
from tblComedian; 
select
--    case
--        when gender = 'm' then 1
--    end,
    count(decode(gender, 'm', 1)),
    count(decode(gender, 'f', 1))
from tblComedian;
  • 문제
-- tblinsa. 부장 몇명?, 과장 몇명?, 대리 몇명?, 사원 몇명?
select
    count(decode(jikwi, '부장', 1)) as 부장,
    count(decode(jikwi, '과장', 1)) as 과장,
    count(decode(jikwi, '대리', 1)) as 대리,
    count(decode(jikwi, '사원', 1)) as 사원
from tblInsa;

-- tblAddressBook. job. 학생 몇명?, 건물주 몇명?
select
    count(decode(job, '학생', 1)) as 학생,
    count(decode(job, '건물주', 1)) as 건물주
from tblAddressBook;

-- tblAddressBook. address. 강동구 몇명? 마포구 몇명?
select
    decode(address, '강동구', 123),
    replace(address, '강동구', 123)
from tblAddressBook;


select
    count(case
        when instr(address, '강동구') > 0 then 1
    end) as 강동구,
    count(case
        when instr(address, '마포구') > 0 then 1
    end) as 마포구
from tblAddressBook;


select
    count(*) - count(decode(instr(address, '강동구'), 0, 1)) as "강동구",
    count(*) - count(decode(instr(address, '마포구'), 0, 1)) as "마포구"
from tblAddressBook;
profile
仁
모르면 괴롭고 알면 즐겁다.
이전 포스트

학원 33일차 - Oracle

다음 포스트

학원 35일차 - Oracle

0개의 댓글

관련 채용 정보