<DataBase_231030월> 단일행 함수

<Constraint(제약조건)>

 

컬럼단위(=속성), 인덱스(=객체)에 줄 수 있다. -> 데이터 무결성 구현(제약조건의 목적)

 

1. 개체 무결성

- not null : null은 허용하지 않음. ex)이름, 필수로 입력해야 하는 곳

- unique : 유일한 값 이어야 함. 중복값이 안된다는 것. null은 허용함. ex)주민번호

- primary key : 식별자. 기본키. null도 안되고, 중복값도 안됨(not null+unique), 테이블에 하나밖에 못줌(한 테이블에 하나만 존재) ex)사원번호, 환자번호

 

2. 영역 무결성

- check : 영역 내의 유효값을 체크하기 때문에 조건식이 들어감.

- default(기본값) : 뭔가 입력하지 않아도 저절로 들어가게끔 세팅. 대신 입력할 때 인위적으로 넣으면 그게 기본값이 됨. ex)주문시간

 

3. 참조 무결성

- foreign key(참조키=외래키) : 다른 컬럼(부모=기본키)의 값을 참조. 생성 및 데이터 입력은 부모 먼저. 삭제는 자식이 다 삭제가 된 이후에 부모것이 삭제 가능.

- delete cascade (자식이 이 옵션을 행사하면 부모가 삭제가 됨. 부모가 참조하던 애들도 같이 삭제가 됨.)

- update cascade (자식이 이 옵션을 행사하면 부모가 변경이 됨. 부모가 참조하던 애들도 같이 변경이 됨.)

 

--------------------------------------------------------------

 

<문법>

 

(1) 컬럼단위 : create table info (id number(10) [constraint 제약명] primary key, ... );

- 제약명 ex) table_col_제약

- 컬럼 정의와 함께

- not null 만큼은 무조건 컬럼단위로만, 테이블단위는x

- 모든 제약조건은 컬럼단위로 가능하다.

 

ex)

create table student(

hakbun number(10) constraint st_hakbun_pk primary key,

sname varchar2(10) constraint st_sname_nn not null,

age number(3) constraint st_age_ck check(age>=20 and age<=30),

addr varchar2(30) default '서울시 구로구',

); 

 

create table lib(

sno number(10) constraint lib_sno_fk references student(hakbun) [on delete cascade], 

book varchar2(100) constraint lib_book_nn not null,

loan_date date default sysdate

); 

 

(2) 테이블단위 : create table info (id number(10), name varchar2(10), ... constraint info-id-pk primary key(id));

- 컬럼 추가 하듯이

- 복합 컬럼 제한조건은 테이블단위로 밖에 설정 안됨.

ex) constraint xx primary key (c1,c2,c3);

 

ex)

create table student(

hakbun number(10),

sname varchar2(10) constraint st_sname_uq unique,

age number(3),

addr varchar2(30) default '서울시 구로구',

constraint st_hakbun_pk primary key(hakbun),
constraint st_age_ck check(age>=20 and age<=30)

); 

 

create table lib(
sno number(10),
book varchar2(100) constraint lib_book_nn not null,
loan_date date default sysdate,
constraint lib_sno_fk foreign key(sno) references student(hakbun)
);

 

--제약조건 수정
alter table table_name
modify book varchar2(100) [constraint lib_book_nn not null | null];   // not null/null만 modify로!

alter table table_name
--add constraint st_hakbun_pk primary key(hakbun);   // 제약추가
--drop constraint st_hakbun_pk;         // 제약삭제

 

--------------------------------------------------------------

 

단일행 함수(행만큼 실행) -> 다중행 함수 / 고급쿼리

데이터베이스의 함수는 반드시 리턴값이 한개 존재한다.

 

--------------------------------------------------------------

 

<단일행 함수>

 

round, trunc, ceil, floor

length, lengthb

 

 

// 연습문제

 

1. 사원명, 급여, 월급여, 세금(=급여의3.3%) 추출

select saname, sapay, sapay/12, sapay*0.033 from sawon;

 

2. 단, 월급은 십단위에서 올리고, 세금은 일단위에서 절삭

select saname, sapay, round(sapay/12, -2), trunc(sapay*0.033, -1) from sawon;

 

--------------------------------------------------------------

 

<공백제거 함수>

 

trim, ltrim, rtrim

 

--------------------------------------------------------------

 

<변환 함수>

 

lower, upper, initcap

to_char

to_date

to_number

 

--------------------------------------------------------------

 

<대체 함수>

 

replace

 

--------------------------------------------------------------

 

<채우기 함수>

 

lpad, rpad

 

lpad & rpad(데이터, 전체크기, '채울문자')

ex) select lpad(saname, '20', '@') name, rpad(saname, 20, '^') name from sawon

 

col name format a20 // 출력되는 컬럼의 크기 설정 : col 컬럼명(별칭) format aXX(byte)

 

ex) 

1. 사원명, 급여, 급여현황(급여 100단위에 *하나)

col 급여현황 format a50

select saname, sapay, lpad('*', (sapay/100), '*') "급여현황"

from sawon;

 

--------------------------------------------------------------

 

<문자열추출 함수>

 

substr(문자열, N번째, N개)

lpad & rpad(데이터, 전체크기, '채울문자')

 

// 연습문제

1. 고객명, 주민번호1(******-3182565), 주민번호2(041201-*******)를 추출

-- 답 :

col 주민번호1 format a14

col 주민번호2 format a14

select goname, lpad(substr(gojumin, 7, 8),14, '*') "주민번호1" ,rpad(substr(gojumin, 1, 7),14, '*') "주민번호2" from gogek;

// 1. substr을 이용하여 주민번호에서 7번째문자열 -부터 주민번호 뒷자리 끝까지 추출

// 2. lpad를 이용하여 뽑아낸 주민번호 전체크기 14자리문자의 왼쪽을 *로 채워라

// 3. substr을 이용하여 주민번호에서 1번째문자열 -부터 주민번호 뒷자리 앞까지 추출

// 4. rpad를 이용하여 뽑아낸 주민번호 전체크기 14자리문자의 오른쪽을 *로 채워라 

 

2. 검색문자의 위치값 instr(문자열, '찾을문자', 1(앞에서부터찾기)|-1(뒤에서부터찾기), N번째)

 

--------------------------------------------------------------

 

<조건 함수>

 

decode함수 : decode(데이터, 조건1, 결과1, 조건2, 결과2, .... 그 외 결과) "컬럼명"

case문 : case [데이터] when 조건1 then 결과1 when 조건2 then 결과2 ..... else 그 외 결과 end "컬럼명"

 

ex)

1. decode함수를 사용하여 고객명, 전화번호, 성별을 추출

select goname, gotel, decode(substr(gojumin, 8 ,1), 1, '남자', 3, '남자', '여자') 성별 

from gogek;

 

2. case문을 사용하여 고객명, 전화번호, 성별을 추출

select goname, gotel, case substr(gojumin, 8, 1)

when '1' then '남자' when '2' then '여자' when '3' then '남자' when '4' then '여자' end 성별

from gogek;

 

--------------------------------------------------------------

 

숙제는 scott/tiger 계정으로 하기.

 

---------------------------------------------

 

<선생님이 정리해주신>

<단일행함수>

 

*** 단일행 함수 : 하나의 행 단위 실행

1. 수학함수
SQL> select sin(10), cos(10), tan(10), log(10, 2), power(6, 5) from dual;

   SIN(10)    COS(10)    TAN(10)  LOG(10,2)
---------- ---------- ---------- ----------
-.54402111 -.83907153 .648360827 .301029996   7776

--반올림 관련 함수***
SQL> select round(246.5412, 2), round(246.5412, -2), round(246.5412, 0) from dual;  // 자리수 지정한 반올림

ROUND(246.5412,2) ROUND(246.5412,-2) ROUND(246.5412,0)
----------------- ------------------ -----------------
           246.54                200               247

SQL> select trunc(246.5412, 2), trunc(246.5412, -2), trunc(246.5412, 0) from dual;     // 자리수 지정한 버림

TRUNC(246.5412,2) TRUNC(246.5412,-2) TRUNC(246.5412,0)
----------------- ------------------ -----------------
           246.54                200               246

SQL> select ceil(10.1), floor(10.9) from dual;

CEIL(10.1) FLOOR(10.9)
---------- -----------
        11          10

-- 사원명, 급여, 월급여(급여/12), 세금(급여의 3.3%)를 추출
-- 단, 월급은 십단위에서 반올림하고, 세금은 일단위에서 절삭
select saname, sapay, round(sapay/12, -2) "월급", trunc(sapay*0.033, -1) "세금" from sawon;

2. 문자열 함수
SQL> select length('한국직업전문학교'), lengthb('한국직업전문학교') from dual;

LENGTH('한국직업전문학교') LENGTHB('한국직업전문학교')
-------------------------- ---------------------------
                         8                          16  -- 바이트(byte)개수로 변환

-- 공백제거 관련 함수

ltrim : 왼쪽공백 제거

rtrim : 오른쪽공백 제거

 

SQL> select trim('  korea  VC   '), ltrim('  korea  VC   '), rtrim('  korea  VC   ') from dual;

TRIM('KOR LTRIM('KOREA RTRIM('KORE
--------- ------------ -----------
korea  VC korea  VC      korea  VC

SQL> select trim('k' from 'koreaVC') from dual;

TRIM('
------
oreaVC

SQL> select lower('I am a koreavc'), upper('I am a koreavc'), initcap('I am a koreavc') from dual;

LOWER('IAMAKOR UPPER('IAMAKOR INITCAP('IAMAK
-------------- -------------- --------------
i am a koreavc I AM A KOREAVC I Am A Koreavc

SQL> select replace('koreavc koreavc','r' ,'x') from dual;  --

REPLACE('KOREAV
---------------
koxeavc koxeavc

-- 채우기 함수*** lpad & rpad(데이터, 전체크기, '채울문자')
SQL> col name format a20  // 출력되는 컬럼의 크기 설정 : col 컬럼명(별칭) format aXX(byte)
SQL> select lpad(saname, '20', '@') name, rpad(saname, 20, '^') name from sawon

NAME                 NAME
-------------------- --------------------
@@@@@@@@@@@@@@홍길동 홍길동^^^^^^^^^^^^^^
@@@@@@@@@@@@@@한국남 한국남^^^^^^^^^^^^^^
@@@@@@@@@@@@@@이순신 이순신^^^^^^^^^^^^^^
@@@@@@@@@@@@@@이순라 이순라^^^^^^^^^^^^^^
@@@@@@@@@@@@@@놀기만 놀기만^^^^^^^^^^^^^^
@@@@@@@@@@@@@@류별나 류별나^^^^^^^^^^^^^^
@@@@@@@@@@@@@@채시라 채시라^^^^^^^^^^^^^^

-- 사원명, 급여, 급여현황(급여 100단위에 * 하나)

박길동  1500  ***************
이길동  1000  **********

col 급여현황 format a50
select saname, sapay, lpad('*', sapay/100, '*') 급여현황 from sawon;

-- 문자열 추출 함수*** substr(문자열, N번째, N개)
SQL> select substr('koreavc koreavc', 3, 4) from dual;

SUBS
----
reav

-- 고객명, 주민번호1(******-3182565), 주민번호2(041201-*******)를 추출
col 주민번호1 format a14
col 주민번호2 format a14
select goname, lpad(substr(gojumin, 7, 8), 14, '*') 주민번호1, rpad(substr(gojumin, 1, 7), 14, '*') 주민번호2 from gogek;

-- 검색 문자의 위치값 instr(문자열, '찾을문자', 1|-1(뒤), N번째)
SQL> select instr('koreavc koreavc', 'k', 1, 2) from dual;

INSTR('KOREAVCKOREAVC','K',1,2)
-------------------------------
                              9

SQL> select instr('koreavc koreavc', 'k', -1, 2) from dual;

INSTR('KOREAVCKOREAVC','K',-1,2)
--------------------------------
                               1

3. 조건함수
- decode함수 : decode(데이터, 조건1, 결과1, 조건2, 결과2,........, 그외 결과) "컬럼명"
- case문 : case [데이터] when 조건1 then 결과1 when 조건2 then 결과2 ....... else 그외 결과 end "컬럼명"

select saname, deptno, decode(deptno, 10,'총무',20,'관리',30,'영업','전산') "부서명" from sawon;

select saname, deptno,                                                                                         
case deptno when 10 then '총무' when 20 then '영업' when 30 then '전산' else '관리' end "부서명" from sawon;

SANAME         DEPTNO 부서
---------- ---------- ----
홍길동             10 총무
한국남             20 관리
이순신             20 관리
이순라             20 관리
놀기만             20 관리
류별나             20 관리
채시라             20 관리
이성계             30 영업

--고객명, 전화번호, 성별을 추출
(decode)
select goname, gotel, gojumin, decode(substr(gojumin, 8, 1), 1, '남자', 3, '남자', '여자') sex from gogek;

(case)
select goname, gotel, 
case substr(gojumin, 8, 1) when '1' then '남자' when '3' then '남자' else '여자' end 성별
from gogek;

4. 변환 함수
--to_char('날짜' | '숫자', '형식') : 날짜 또는 숫자를 지정한 형식의 문자*로 변환
--to_date('날짜 형태의 문자열' , '형식') : 날짜 형태의 문자열을 날짜로 변환  // 시간표현***
--to_number('숫자 형태의 문자열') : 숫자 형태의 문자열을 숫자로 변환

--현재 세션의 날짜관련 형식 정보
SQL> col parameter format a50
SQL> col value format a50
SQL> select * from nls_session_parameters;

PARAMETER                                          VALUE
-------------------------------------------------- ---------------------------------------------
NLS_LANGUAGE                                       KOREAN --***
NLS_TERRITORY                                      KOREA
NLS_CURRENCY                                       ￦ --***
NLS_ISO_CURRENCY                                   KOREA
NLS_NUMERIC_CHARACTERS                             .,
NLS_CALENDAR                                       GREGORIAN
NLS_DATE_FORMAT                                    RR/MM/DD --***
NLS_DATE_LANGUAGE                                  KOREAN --***
NLS_SORT                                           BINARY
NLS_TIME_FORMAT                                    HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT                               RR/MM/DD HH24:MI:SSXFF

PARAMETER                                          VALUE
-------------------------------------------------- ---------------------------------------------
NLS_TIME_TZ_FORMAT                                 HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT                            RR/MM/DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY                                  ￦
NLS_COMP                                           BINARY
NLS_LENGTH_SEMANTICS                               BYTE
NLS_NCHAR_CONV_EXCP                                FALSE

17 개의 행이 선택되었습니다.

--세션의 형식 변경
SQL> alter session set nls_date_format='yyyy.mm.dd hh24:mi:ss';  // 변수=값

세션이 변경되었습니다.

SQL> select sysdate from dual;

SYSDATE
-------------------
2023.10.31 09:36:03

--날짜 관련 형식 종류들
SQL> select sysdate, to_char(sysdate, 'year') from dual;

SYSDATE  TO_CHAR(SYSDATE,'YEAR')
-------- ------------------------------------------
23/10/31 twenty twenty-three

'yy'
'month'
'mon'
'mm'
'q' -- 4(분기)
'd' -- 3(요일, 1-일요일) : 요일순서대로 정렬하기 위함
'day'
'dy'
'dd'
'ddd' -- 304(365 기준)
'hh'
'hh24'
'miss'
'am' | 'pm'

SQL> select sysdate, to_char(sysdate, 'yyyy.mm.dd day am hh:mi:ss') from dual;

SYSDATE  TO_CHAR(SYSDATE,'YYYY.MM.DDDAYAMHH
-------- ----------------------------------
23/10/31 2023.10.31 화요일 오전 10:13:57

SQL> select saname, to_char(sahire,'fmyyyy.mmsp.ddth day am hh:mi:ss') hiredate from sawon; // fm:(0제거), sp(영문추출), th

SANAME     HIREDATE
---------- ----------------------------------------
홍길동     1980.one.1st 화요일 오전 12:0:0
한국남     1988.eleven.1st 화요일 오전 12:0:0
이순신     1985.three.1st 금요일 오전 12:0:0

-- 오늘 날짜를 [2023년 10월 31일 화요일] 형태로 추출
SQL> select sysdate, to_char(sysdate, 'fmyyyy"년" mm"월" dd"일" day') from dual;

-- 숫자관련 형식(9-값이 없다면 출력하지 않음, 0-값이 없어도 출력)
SQL> select saname, to_char(sapay, 'fm$9,990') sapay from sawon;

SANAME     SAPAY
---------- -------
홍길동     $5,000
한국남     $3,000
이순신     $3,500
이순라     $1,200
놀기만     $2,300
류별나     $1,600
채시라     $3,400
이성계     $2,803
무궁화     $3,000

5. 날짜 함수

SQL> select last_day(sysdate), next_day(sysdate, '월'), add_months(sysdate, 3) from dual;

LAST_DAY NEXT_DAY ADD_MONT
-------- -------- --------
23/10/31 23/11/06 24/01/31

-- 두 날짜 사이의 개월수를 반환하는 함수*** months_between(A, B)
SQL> select months_between(sysdate, '04/12/01') from dual;

MONTHS_BETWEEN(SYSDATE,'04/12/01')
----------------------------------
                        226.982077

-- 사원명, 입사일, 근무기간(XX년 XX개월)을 추출
col 근무기간 format a20

select  saname, sahire,
floor(months_between(sysdate, sahire)/12) || '년 ' ||
floor(mod(months_between(sysdate, sahire), 12)) || '개월' "근무기간"
from sawon;

-- 날짜와 연산되는 함수
SQL> select saname, sahire, sahire + to_yminterval('01-06') from sawon;   // '년-개월'

SQL> select saname, sahire, sahire + to_dsinterval('100 00:00:00') from sawon; // '일수 시:분:초'

-- 날짜에 사용되는 round, trunc 함수 : 기준형식에서 하위 개념의 형식에서 반올림 또는 버림
SQL> select sysdate, round(sysdate, 'year') from dual;

SYSDATE  ROUND(SY
-------- --------
23/10/31 24/01/01

SQL> select sysdate, round(sysdate, 'month') from dual;

SYSDATE  ROUND(SY
-------- --------
23/10/31 23/11/01

SQL> select sysdate, trunc(sysdate, 'year') from dual;

SYSDATE  TRUNC(SY
-------- --------
23/10/31 23/01/01

SQL> select sysdate, trunc(sysdate, 'month') from dual;

SYSDATE  TRUNC(SY
-------- --------
23/10/31 23/10/01

6. 통계 함수(rank 함수)
-- 형식 : rank() over([partition by 컬럼명] order by 컬럼명 (asc)|desc) "석차"

-- 입사일 순서대로 석차를 추출
select saname, sahire, rank() over(order by sahire) "근무연차 랭킹" from sawon;
--where sahire is not null

--부서별로 급여를 많이 받는 순서대로 석차를 추출(단, 급여가 널인 사람을 제외)
select deptno, saname, sapay, rank() over(partition by deptno order by sapay desc) "급여석차" from sawon
where sapay is not null;