학원 35일차 - Oracle

仁·2021년 7월 10일

오라클 학원

2021.05.17

ex11_datetime_function.sql

ex12_casting_function.sql

ex13_DDL.sql

ex14_sequence.sql

ex15_insert.sql

2021.05.17

ex11_datetime_function.sql

날짜 시간 함수

sysdate

현재 시스템의 시각을 반환(오늘날짜)
date sysdate
자바의 Calendar.getInstance(), new Date()

select sysdate from dual; -- 21/05/17 --> 인자값이 없는 함수 
-- select sysdate() from dual; 에러

날짜 연산

+연산, -연산

시각 - 시각 = 시간(단위:일)
시각 + 시간 = 시각
시각 - 시간 = 시각

-- 시각 - 시각 = 시간(단위:일)
select 
    name,
    ibsadate as 입사일,
    sysdate as 현재,
    -- 홍길동	08/10/11	21/05/17	4601
    round(sysdate - ibsadate) as "근무기간(일)",
    round((sysdate - ibsadate) * 24) as "근무시간(시)",
    round((sysdate - ibsadate) * 24 * 60) as "근무시간(분)",
    round((sysdate - ibsadate) * 24 * 60 * 60) as "근무시간(초)",
    round((sysdate - ibsadate) / 365) as "근무시간(년)" -- 근사치
from tblinsa;

-- 시각 + 시간(단위: 일) = 시각
-- 시각 - 시간(단위: 일) = 시각
select 
    sysdate, -- 21/05/17
    sysdate + 100,  -- 21/08/25 (100일뒤)
    sysdate - 100 -- 21/02/06 (100일전)
    sysdate + (2 / 24) -- 2시간뒤
from dual;

시분초까지 보이게 설정하는 법

last_day()

해당 시각이 포함된 달의 마지막 날짜
date last_day(date)

select 
    sysdate, -- 21/05/17
    last_day(sysdate) -- 21/05/31 시각반환
from dual;

months_between()

시각 - 시각 = 시간(단위:월)
number months_between(date, date)

select
    name,
    ibsadate as "입사일",
    sysdate - ibsadate as "근무시간(일)",
    (sysdate - ibsadate) / 30.4 as "근무시간(개월)",
    months_between(sysdate, ibsadate) as "근무시간(개월)", -- ★★★★
    (sysdate - ibsadate) / 365 as "근무시간(년)",
    months_between(sysdate, ibsadate) / 12 as "근무시간(년)" -- ★★★★
from tblinsa;

add_months()

number add_months(date, number)

select
    sysdate,
    sysdate + 31, -- (윤년계산 어려움) 사용X
    add_months(sysdate,1), -- 한달 뒤, 사용O

    sysdate + 100, -- 100일뒤
    add_months(sysdate,12) -- 1년뒤, 첫돌
from dual;

시각 - 시각 = 일(시,분,초)
시각 - 시각 = 월(년) -> months_between

시각 + 시간(일) = 시각
시각 - 시간(일) = 시각
시각 + 시간(월) = 시각 -> add_months()
시각 - 시간(월) = 시각 -> add_months()

-- 컬럼리스트에서 사용
-- 조건절에서 사용
-- 정렬에서 사용
select
    name,
    ibsadate,
    ceil(months_between(sysdate, ibsadate) / 12) as "근무년차"
from tblInsa
    where months_between(sysdate, ibsadate) < (12 * 10); -- 10년차이내

ex12_casting_function.sql

형변환 함수

to_char() : 숫자 -> 문자
to_char() : 날짜 -> 문자 ★★★★
to_number() : 문자 -> 숫자
to_date() : 문자 -> 날짜 ★★★★

1. to_char()

char to_char(숫자컬럼명, 형식문자열)
형식문자열 구성요소
- 9 : 숫자 1개를 문자 1개로 바꾸는 역할(빈자리는 공백으로 치환한다.) + 부호 자리 추가
- 0 : 숫자 1개를 문자 1개로 바꾸는 역할(빈자리는 0으로 치환한다.) + 부호 자리 추가
- $ : 통화 기호 표시
- L : 통화 기호 표시(Locale) - ￦
- . : 소수점 표시
- , : 자릿수 표시(천단위)

select
     '@' || 100 || '@' as a, -- 자료형 number
     '@' || to_char(100) || '@' as b, -- 자료형 char
     '@' || to_char(100, '99999') || '@' as c, -- char, 5자리 문자열로 바꾸기
     -- '@' || to_char(-100, '99999') || '@' as c, -- char, 5자리 문자열로 바꾸기
     '@' || to_char(100, '00000') || '@' as d -- char, 5자리 문자열로 바꾸기
from dual;

-- trim : 공백 없애기
select
     '@' || 100 || '@', -- 자료형 number
     '@' || to_char(100) || '@', -- 자료형 char
     '@' || ltrim(to_char(100, '99999')) || '@', -- char, 5자리 문자열로 바꾸기
     '@' || ltrim(to_char(100, '00000')) || '@' -- char, 5자리 문자열로 바꾸기
from dual;

selectt
    100,
    to_char(100,'$999'), -- 통화표시
    to_char(100,'L999'),
    '$' || 100
from dual;

select
    123.456,
    to_char(123.456),
    to_char(123.456,'999.999'),
    to_char(123.456,'999.99'),
    to_char(123.456,'999.9'), -- round()와 유사, 리턴값이 char
    
    1000000,
    to_char(1000000, '9,999,999'),
    to_char(1000000, '999,9999') -- ★★★★
from dual;

2. to_char()

사용빈도 높음 ★★★★★★★★★★★★
날짜 -> 문자
char to_char(날짜컬럼명, '형식문자열')
형식 문자열 구성 요소
- yyyy
- yy
- month
- mon
- mm
- day
- dy
- ddd
- dd
- d
- hh(hh12)
- hh24
- mi
- ss
- am(pm)
- -, :

select
    sysdate, -- 21/05/17
    to_char(sysdate,'yyyy'),  -- 2021 , 년(4자리)  ★★★★
    to_char(sysdate,'yy'),    -- 21, 년(2자리)
    to_char(sysdate,'month'), -- 5월, 월(풀네임)
    to_char(sysdate,'mon'),   -- 5월, 월(약어)
    to_char(sysdate,'mm'),    -- 05, 월(2자리숫자)  ★★★★
    to_char(sysdate,'day'),   -- 월요일, 요일(풀네임)  ★★★★
    to_char(sysdate,'dy'),    -- 월, 요일(약어)  ★★★★
    to_char(sysdate,'ddd'),   -- 137,날짜 (올해들어 몇일째?)
    to_char(sysdate,'dd'),    -- 17, 날짜(이번달 들어 몇일째?)
    to_char(sysdate,'d'),     -- 2, 날짜(이번주 들어 몇일째?), 요일(일요일 1 ~ 토요일7) ****
    to_char(sysdate,'hh'),    -- 10, 시(12시간 표기법)
    to_char(sysdate,'hh24'),  -- 10, 시(24시간 표기법) ★★★★
    to_char(sysdate,'mi'),    -- 44, 분 ★★★★
    to_char(sysdate,'ss'),    -- 00, 초 ★★★★
    to_char(sysdate,'am')     -- 오전
from dual;

-- 컬럼리스트에서 사용
select
    name,
    to_char(ibsadate,'yyyy-mm-dd') as a, -- ★★★★
    -- to_char(ibsadate,'yyyy년mm월dd일') 에러
    to_char(ibsadate,'yyyy') || '년' || to_char(ibsadate,'mm') || '월' || to_char(ibsadate,'dd') || '일' as b,
    to_char(ibsadate, 'hh24:mi:ss') as 시분초, -- ★★★★
    to_char(ibsadate, 'yyyy-mm-dd hh24:mi:ss') as 년월일시분초 -- ★★★★
from tblinsa;

-- 조건절에서 사용
-- 2010년에 입사한 사람들?
select
    name,
    ibsadate
from tblInsa
    -- where ibsadate >= '2010-01-01' and ibsadate <= '2010-12-31' -- 오답
    -- where ibsadate between '2010-01-01' and '2010-12-31' -- 오답 (2010-01-01 00:00:00 ~ 2010-12-31 00:00:00 -> 31일 하루 오차)
    -- where ibsadate between '2010-01-01 00:00:00' and '2010-12-31 23:59:59' -- 에러
    
    -- where to_char(ibsadate, 'yyyy') = '2010'; -- 2010년도에 입사한 사람 ★★★★
    -- where to_char(ibsadate, 'mm') = '05' -- 년도와 상관없이 5월에 입사한 사람 
    
    -- 월요일에 입사한 사람
    -- where to_char(ibsadate, 'day') = '월요일' -- 가독성은 좋지만 오타의 위험이 있다.
    -- where to_char(ibsadate, 'dy') = '월'
    where to_char(ibsadate, 'd') = 2 -- ★★★★ 활용가치가 높다.(어느 국가에서도 동일한 값을 반환하기 때문)

-- SQL에서 날짜 상수 표기법
-- '2010-01-01' -- O
-- '2010-01-01 12:00:00' -- X 시분초가 붙으면 상수라고 생각 x

select
    '2010-01-01' -- 시분초? -> 시분초를 명시안하면 자동으로 00:00:00(자정)로 시작한다.
from dual;

-- 정렬에서 사용
-- 입사 월별로 정렬
select
    name,
    to_char(ibsadate, 'yyyy-mm-dd hh24:mi:ss') as 입사일
from tblInsa
    order by to_char(ibsadate, 'mm') asc, to_char(ibsadate, 'yyyy') asc, to_char(ibsadate, 'dd');

-- 계절 순으로 출력
select
    name,
    to_char(ibsadate, 'yyyy-mm-dd hh24:mi:ss') as 입사일,
    case
        when to_char(ibsadate, 'mm') between '03' and '05' then '봄'
        when to_char(ibsadate, 'mm') between '06' and '09' then '여름'
        when to_char(ibsadate, 'mm') between '10' and '11' then '가을'
        when to_char(ibsadate, 'mm') in ('12', '01', '02') then '겨울'
    end as 계절
from tblInsa
    order by case -- 봄, 여름, 가을, 겨울 순으로 정렬
        when to_char(ibsadate, 'mm') between '03' and '05' then 1
        when to_char(ibsadate, 'mm') between '06' and '09' then 2
        when to_char(ibsadate, 'mm') between '10' and '11' then 3
        when to_char(ibsadate, 'mm') in ('12', '01', '02') then 4
    end asc;

3. to_number()

number to_number(문자열)
문자 -> 숫자

 select
    '123' as "aaaaaaaaaaaaaaaaaaa", -- 문자열
    to_number('123'), -- 숫자형
    
    '123' * 2, -- 암시적 형변환 발생
    to_number('123') * 2 -- 문자열 상수 안에 숫자만 들어있으면, 자료형은 문자열이지만 취급은 숫자...
from dual;

정렬이 다름 (문자열 : 좌측정렬, 숫자 : 우측정렬)

4. to_date() ★ ★ ★ ★ ★ ★ ★ ★ ★

date to_date(문자열, 형식문자열)
문자 -> 날짜

SQL의 날짜 시간 리터럴(상수)

문자열 표기
문맥에 따라 날짜 상수로 인식하기도 하고 문자열 상수로 인식하기도 한다.- > 암시적 형변환 발생

select 
    sysdate, -- 자료형(date)
    '2021-05-17', -- 자료형 (char)
    sysdate - '2021-01-01' -- 자료형(char)
from dual;

select
*
from tblinsa
    where ibsadate > '2010-01-01' -- 자료형(date) -- 주로 비교연산, 조건절에 사용시 날짜상수로 인식한다.

-- 문자열을 날짜상수로
 select
    sysdate,
    to_date('2021-05-17', 'yyyy-mm-dd'), -- 앞의 데이터와 똑같으 형식문자 사용
    to_date('2021/05/17', 'yyyy/mm/dd'),
    to_date('20210517', 'yyyymmdd')
from dual;

-- 2010년 입사 명단?
select *
from tblinsa
where ibsadate between '2010-01-01' and '2010-12-31'; -- '2010-01-01 00:00:00' ~ '2010-12-31 00:00:00' -> 정확하지 않은 방법

select *
from tblinsa where to_char(ibsadate, 'yyyy') = '2010'; -- 100%

-- between을 쓰면서 확실한 연산을 하는 법 -> 시분초 표현
select * 
from tblinsa
where ibsadate between to_date('2010-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') 
    and to_date('2010-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'); -- 100%

-- 년월일없이 시분초만 다루는 일은 잘 없음.
select '14:23:45', to_char(to_date('14:23:45', 'hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual;

ex13_DDL.sql

DDL

데이터 정의어
데이터베이스 객체를 생성, 수정, 삭제한다.
데이터베이스 객체 : 테이블, 뷰, 인덱스, 트리거, 프로시저, 제약 상황, 계정 등,,,
create, alter, drop
DDL + 테이블(제약사항)
테이블 생성하기 -> 테이블을 구성하는 컬럼을 생성하기(컬럼 정의)

create table 테이블명
(
    컬럼정의,
    컬럼 정의,
    컬럼 정의(길이) null표기 제약상황
);

제약사항, Constraint ★★★★★★★★★★★★★★★★

해당 컬럼에 들어갈 데이터(값)에 대한 조건(규제 사항)

-> 조건을 만족하면 데이터를 통과, 조건을 만족하지 못하면 데이터 거부 -> 에러 발생 -> 유효성 검사 도구
6가지

not null
- 해당 컬럼에 반드시 값을 가져야한다.
- 해당 컬럼이 값이 없으면 에러
- 필수값(★★★★★★★)
- null을 가지지 못한다.
primary key, PK
- 해당 컬럼이 모든 레코드 중에 유일한 값을 가져야 한다.(중복불가)
- 다른 레코드와 같은 값을 가질 수 없다.
- 유일하다. + 필수값 ★★★★★★★
- 주로 식별자로 사용합니다.★★★★★★★
- 테이블의 모든 행동들을 유일하게 구분하는 역할의 키(컬럼)
- 반드시 테이블엔 PK가 존재해야 한다.(PK가 없는 테이블을 만들 수 있다. -> 비권장)
- 기본키의 형태
  a. 단일 기본키 > 기본키 : 1개의 컬럼이 PK역할
  b. 복합 기본키 > 복합키 : 2개 이상의 컬럼이 모여서 PK역할, Composite Key
foreign key
- 참조키, 외래키
- 나중에 수업
Unique
- primary key - not null
- 중복값은 가질 수 없는데 null은 가질 수 있다.

check
- 사용자 정의 제약 조건
- where절에서 조건을 거는 것과 동일
default
- 기본값 설정
- 해당 컬럼값에 null이면 대신 준비한 기본값을 대입한다.

null

-- 메모 테이블
-- null : 값을 넣지 않아도 상관없음, 생략 가능 -> 아무것도 안적어도 값을 생략해도 된다고 약속되어있음.
create table tblMemo(

    seq number(3),             -- 메모 번호
    name varchar2(30),         -- 작성자
    memo varchar2(1000) null,  -- 메모내용
    regdate date null          -- 작성날짜

);

select * from tblMemo;

-- 데이터 추가하기
-- insert into tblMemo (컬럼리스트) values (값리스트);
insert into tblMemo (seq, name, memo, regdate) values (1, '홍길동', '메모입니다.', sysdate);
insert into tblMemo (seq, name, memo) values (1, '홍길동', '메모입니다.'); -- 날짜 생략
insert into tblMemo (seq, name, memo, regdate) values (1, null, '메모입니다.', sysdate);

not null

-- 테이블 삭제하기
drop table tblMemo;

create table tblMemo(
    
    seq number(3) not null, 
    name varchar2(30) not null,  -- not null : 생략불가, null을 가질 수 없음
    memo varchar2(1000) null,   
    regdate date null 
    
);

insert into tblMemo (seq, name, memo, regdate) values (1, '홍길동', '메모입니다.', sysdate);
insert into tblMemo (seq, name, memo, regdate) values (1, '홍길동', null, sysdate);

-- 에러 : ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."NAME") 
-- null을 ("HR"."TBLMEMO"."NAME") 안에 삽입할 수 없습니다.
insert into tblMemo (seq, name, memo, regdate) values (1, null, '메모입니다.', sysdate); -- null 사용 불가
insert into tblMemo (seq,memo, regdate) values (1, '메모입니다.', sysdate); -- 생략 불가

select * from tblMemo;

-- 기본키, PK
drop table tblMemo;

create table  tblMemo(
    
    -- 모든 테이블에는 PK가 반드시 있어야한다. -> 의미하는 것?!?!?
    -- 기본키(key) -> 중복값을 가질 수 없다.(Unique) + not null(생략불가) -> 완벽한 식별자
    seq number(3) primary key,
    name varchar2(30),
    memo varchar2(1000),
    regdate date
    
);

-- 현재 상태는 레코드를 구분할 방법이 없다.!!!
insert into tblMemo (seq, name, memo, regdate) values (1,'홍길동','메모입니다.', sysdate);
insert into tblMemo (seq, name, memo, regdate) values (2,'아무개','테스트~', sysdate);

-- 더이상 같은 메모번호를 갖는 메모 추가 불가
-- ORA-00001: unique constraint (HR.SYS_C007085) violated (무결성 제약조건에 위배되서 에러)
-- insert into tblMemo (seq, name, memo, regdate) values (2,'아무개','테스트~', sysdate); -- 중복값 배제

-- ORA-01400: cannot insert NULL into ("HR"."TBLMEMO"."SEQ") 
-- insert into tblMemo (seq, name, memo, regdate) values (null,'아무개','테스트~', sysdate); -- 빈값사용 불가

select seq, name, memo, to_char(regdate, 'yyyy-mm-dd hh24:mi:ss') from tblMemo where seq = 2;
select seq, name, memo, to_char(regdate, 'yyyy-mm-dd hh24:mi:ss') from tblMemo where seq = 1;

select * from tblMemo where seq = 2;

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

select * from tblinsa;	  -- num(PK)
select * from tblMen; 	  -- name(PK)
select * from tblcountry  -- name(PK)
select * from tblcomedian -- first_name, last_name 복합키

unique

drop table tblMemo;

create table tblMemo(
    
    seq number(3) primary key,
    name varchar2(30) unique, -- 중복값 금지, 식별자로는 사용할 수 없다.(null을 허용하기 때문에)
    -- name varchar2(30) unique not null, -- primary key랑 똑같은 역할
    memo varchar2(1000),
    regdate date
    
);

insert into tblMemo (seq, name, memo, regdate) values (1,'홍길동','메모입니다.', sysdate);
insert into tblMemo (seq, name, memo, regdate) values (2,'아무개','메모입니다.', sysdate);

-- ORA-00001: unique constraint (HR.SYS_C007087) violated
-- 기존에 홍길동이 있기때문에 중복된 홍길동 사용 불가(한사람이 메모를 한번만 사용가능)
-- insert into tblMemo (seq, name, memo, regdate) values (3,'홍길동','또 다른 메모입니다.', sysdate);
insert into tblMemo (seq, name, memo, regdate) values (3,'하하하','또 다른 메모입니다.', sysdate);

-- unique -> null 허용 -> 값이 존재할때만 유일성 보장
insert into tblMemo (seq, name, memo, regdate) values (4,null,'또 다른 메모입니다.', sysdate);
insert into tblMemo (seq, name, memo, regdate) values (5,null,'또 다른 메모입니다.', sysdate);

select * from tblMemo;

check
- 잘못된 값이 들어가지 않기 위해 제약 설정

drop table tblMemo;

create table  tblMemo(
    
    seq number(3) primary key,
    name varchar2(30),
    memo varchar2(1000),
    regdate date,
    -- lv number not null check(lv >= 1 and lv <= 5), -- 숫자(메모 중요도, 1 ~ 5)
    -- color varchar2(30) not null check(color = 'red' or color = 'blue' or color = 'yellow')
    lv number not null check(lv between 1 and 5),
    color varchar2(30) not null check(color in('red', 'blue', 'yellow')),
    updatedate date check(to_char(updatedate, 'd') = 1) -- 글쓰는 날짜가 일요일만 가능하게 지정
);

insert into tblMemo (seq, name, memo, regdate, lv) values (1, '홍길동', '메모입니다.', sysdate, 1);

-- ORA-02290: check constraint (HR.SYS_C007089) violated (체크 제약 조건을 위배)
-- insert into tblMemo (seq, name, memo, regdate, lv) values (1, '홍길동', '메모입니다.', sysdate, 10);

select * from tblMemo;

default

drop table tblMemo;

create table tblMemo (
    
    seq number(3) primary key,
    name varchar2(30) default '익명', -- 미리 준비한 기본값
    memo varchar2(1000) default '메모없음',
    regdate date
    
);

insert into tblMemo (seq, name, memo, regdate) values (1, '홍길동', '메모입니다.', sysdate);
insert into tblMemo (seq, regdate) values (2, sysdate);

select * from tblMemo;

-- 시퀀스
insert into tblMemo (seq, name, memo, regdate) values (seqNum.nextVal, '홍길동', '메모입니다.', sysdate);

제약 사항을 만드는 방법

컬럼 수준에서 만드는 방법
- 현재 방법
- 컬럼 1개를 정의할 때 제약 사항도 같이 정의하는 방법
- 컬럼명 자료형(길이) [constraint 제약명] 제약조건
테이블 수준에서 만드는 방법

drop table tblMemo;

create table  tblMemo(
    
    -- 제약사항을 구분할 수 있는 이름 정하기 : 테이블명_컬럼_제약사항종류(모두 소문자로)★★
    seq number(3) constraint tblmemo_seq_pk primary key,
    name varchar2(30) constraint tblmemo_name_ck check(length(name) between 9 and 30),
    memo varchar2(1000) constraint tblmemo_memo_ck check(length(memo) > 30),
    regdate date
    
);

-- ORA-02290: 체크 제약조건(HR.SYS_C007590)이 위배되었습니다
-- ORA-02290: 체크 제약조건(HR.TBLMEMO_MEMO_CK)이 위배되었습니다
-- ORA-02290: 체크 제약조건(HR.TBLMEMO_NAME_CK)이 위배되었습니다
-- ORA-00001: 무결성 제약 조건(HR.TBLMEMO_SEQ_PK)에 위배됩니다
insert into tblMemo (seq, name, memo, regdate) values (1, '홍길동홍길동홍길동', '메모입니다.메모입니다 하하하메모입니다.메모입니다 하하하ㅎㅎ', sysdate);

select * from tblMemo;

제약을 별도로 추가 (테이블 수준에서 제약을 추가)

drop table tblMemo;

create table  tblMemo(
    
    seq number(3), -- seq number(3) constraint tblmemo_seq_ck primary key
    name varchar2(30),
    memo varchar2(1000) ,
    regdate date,
    
    -- 제약 사항 추가 -> 가독성 차이
    constraint tblmemo_seq_pk primary key(seq),
    constraint tblmemo_name_ck check(length(name) between 9 and 30),
    constraint tblmemo_memo_ck check(length(memo) > 30)
    
);

ex14_sequence.sql

시퀀스, sequence

데이터베이스 객체 중 하나
식별자를 만드는데 주로 사용 > PK 컬럼에서 잘 사용
일련번호를 만들어서 제공하는 객체(중복되지 않은 번호)
오라클 전용

시퀀스 객체 만들기

create sequence 시퀀스명;

시퀀스 객체 삭제하기

drop sequence 시퀀스명;

시퀀스 객체 사용하기

시퀀스명.nextVal 함수 (★★★★)
시퀀스명.currVal 함수

create sequence seqNum;

select seqNum.nextVal from dual; -- 계수기 같은 역할 (호출하는 횟수만큼 증가된 숫자를 리턴)

-- 문자열 조합 시퀀스
select 'A' || seqNum.nextVal from dual; -- 상품번호, 주문번호
select 'A' || trim(to_char(seqNum.nextVal, '000')) from dual; -- A001, A002, A003...

select seqNum.currVal from dual; -- 자바 Queue.peek()와 비슷, 일련번호 증가x, 무슨 번호가 나올지 체크

시퀀스 상세 설정

create sequence 시퀀스명;
create sequence 시퀀스명
increment by n -- 증감치(양수, 음수)
start with n -- 시작값(seed)
maxvalue n -- 최대값(넘어가면 에러)
minvalue n -- 최소값(넘어가면 에러)
cycle -- 순환구조(PK 써먹기 곤란함..)
cache n;

drop sequence seqTest;

create sequence seqTest 
		-- increment by -1
        increment by 1
        -- start with 123
        start with 1
        maxvalue 10
        cycle
        cache 5;

select seqTest.nextVal from dual;

create sequence seqA
    maxvalue 10
    cycle
    cache 5;
    
create sequence seqB
    maxvalue 5
    cycle
    cache 3;
    
select 'ABC' || trim(to_char(seqA.nextVal, '000')) || trim(to_char(seqB.nextVal, '00')) from dual;    
-- ABC00101
-- ABC00202
-- ABC00505
-- ABC00601
-- ABC00702

drop sequence seqNum;
create sequence seqNum;

-- 오라클 서버가 비정상적으로 중지-> 5 -> ?? -> 21 =>  신경 안써도 괜찮음..(cache에 의한 현상)
select seqNum.nextVal from dual; 

-- ORA-08002: sequence SEQNUM.CURRVAL is not yet defined in this session
-- currVal 함수는 반드시 1번 이상의 nextVal가 호출해야만 동작이 된다.

select seqNum.currval from dual;

-- 메모 번호: seqMemo.nextVal
-- 중복이 되면 안되는 번호(★★★★★★★★)
-- 1, 2, 3, 4, 5... 21, 22, 23, 24..
-- 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23..

drop sequence seqNum;

create sequence seqNum start with 125;

select seqNum.nextVal from dual;

오라클 서버를 중지시키는 방법

win+R -> services.msc -> OracleServiceXE 중지
메뉴 -> Stop Datebase
cmd -> net stop OracleServiceXE

오라클 서버를 시작하는 방법

win+R -> services.msc -> OracleServiceXE 시작
메뉴 -> Start Datebase
cmd -> net start OracleServiceXE

drop sequence seqNum;

create sequence seqNum;
select seqNum.nextVal from dual; -- a. 이전 번호만큼 실행하고 시작


create sequence seqNum start with 125;
select seqNum.nextVal from dual; -- b. 시작하는 값 지정

ex15_insert.sql

insert문

DML
데이터를 테이블에 추가하는 명령어(행추가, 레코드추가)
insert into 테이블명(컬럼리스크) values (값리스트);

drop table tblMemo;

create table tblMemo(
    seq number primary key,
    name varchar2(30) default '익명' not null,
    memo varchar2(1000) not null,
    regdate date default sysdate null
);

create sequence seqMemo; -- tblMemo.seq 대입

-- 1. 표준 : 원본 테이블에 정의된 컬럼 순서대로 컬럼리스트와 값리스트를 구성하는 방식
insert into tblMemo (seq, name, memo, regdate) values (seqMemo.nextVal, '홍길동', '메모입니다.', sysdate);

-- 2. 컬럼리스트의 순서와 값리스트의 순서는 반드시 일치해야 한다.
insert into tblMemo (name, memo, regdate, seq) values (seqMemo.nextVal, '홍길동', '메모입니다.', sysdate);

-- 3. 원본테이블의 컬럼 순서와 insert 컬럼 순서는 무관하다.
insert into tblMemo (name, memo, regdate, seq) values ('홍길동', '메모입니다.', sysdate,seqMemo.nextVal);

-- 4.00947. 00000 -  "not enough values"
insert into tblMemo (seq, name, memo, regdate) values (seqMemo.nextVal, '홍길동', sysdate);

-- 5. 00913. 00000 -  "too many values"
insert into tblMemo (seq, name, regdate) values (seqMemo.nextVal, '홍길동', '메모입니다.', sysdate);

-- 6. null 허용 컬럼 조작
insert into tblMemo (seq, name, memo) values (seqMemo.nextVal, '홍길동', '메모입니다.'); -- 암시적인 방법(해당 컬럼 명시 X)
insert into tblMemo (seq, name, memo, regdate) values (seqMemo.nextVal, '홍길동', '메모입니다.', null); -- 명시적인 방법(null 상수)

-- 7. default 컬럼 조작 
insert into tblMemo (seq, memo) values (seqMemo.nextVal, '메모입니다.'); -- 암시적 null 대입 -> default 동작
insert into tblMemo (seq, name, memo, regdate) values (seqMemo.nextVal, null, '메모입니다.', null); -- 명시적 null 대입
insert into tblMemo (seq, memo, regdate) values (seqMemo.nextVal, '메모입니다.', null); -- 이름(암시적), 날짜(명시적)

-- default 상수(null 대입을 통해서 default값을 호출하는 방법보다 훨씬 직관적이고 편한 방법)
insert into tblMemo (seq, name, memo, regdate) values (seqMemo.nextVal, default, '메모입니다.', default);

-- 8. 컬럼 리스트 생략 표현, 가독성 낮음.
insert into tblMemo values (seqMemo.nextVal, '홍길동', '메모입니다.', sysdate);
insert into tblMemo values (seqMemo.nextVal, '홍길동', '메모입니다.'); -- 00947. 00000 -  "not enough values"
insert into tblMemo values ('홍길동', '메모입니다.', sysdate, seqMemo.nextVal); -- 00932. 00000 -  "inconsistent datatypes: expected %s got %s"


-- 9. 서브 쿼리 
create table tblMemoCopy(
    seq number primary key,
    name varchar2(30) default '익명' not null,
    memo varchar2(1000) not null,
    regdate date default sysdate null
);

-- 다량의 행을 한번에 집어넣을 수 있음
insert into tblMemoCopy select * from tblMemo;

select * from tblMemo;
select * from tblMemoCopy;




CREATE TABLE tblComedianMale
(
   first varchar2(20) not null,
   last varchar2(20) not null,
   gender varchar2(1) check(gender in ('m', 'f')) not null,
   height number not null,
   weight number not null,
   nick varchar2(50) not null
);

-- tblComedian 테이블에서 남자만 추출해서 따로 테이블을 만들어주세요.
insert into tblcomedianmale
select * from tblcomedian where gender = 'm';

select * from tblcomedianmale;



-- 테이블 만들기 + 데이터 추가하기
-- 단점 : 테이블이 만들어질 때 제약사항이 복사가 안된다.(★★★★★★★★★★★★★★★★) > 실제 사용하는 용도의 테이블(x) > 개발자 임시 테이블 사용 (o) > 더미용
create table tblcomedianFemale
as select * from tblcomedian where gender = 'f';
    
select * from tblcomedianFemale;

仁

모르면 괴롭고 알면 즐겁다.

이전 포스트

학원 34일차 - Oracle

다음 포스트

학원 35일차 - Oracle

2021.05.17

ex11_datetime_function.sql

날짜 시간 함수

sysdate

날짜 연산

last_day()

months_between()

add_months()

ex12_casting_function.sql

형변환 함수

1. to_char()

2. to_char()

3. to_number()

4. to_date() ★ ★ ★ ★ ★ ★ ★ ★ ★

SQL의 날짜 시간 리터럴(상수)

ex13_DDL.sql

DDL

제약사항, Constraint ★★★★★★★★★★★★★★★★

제약 사항을 만드는 방법

ex14_sequence.sql

시퀀스, sequence

시퀀스 객체 만들기

시퀀스 객체 삭제하기

시퀀스 객체 사용하기

시퀀스 상세 설정

오라클 서버를 중지시키는 방법

오라클 서버를 시작하는 방법

ex15_insert.sql

insert문

학원 34일차 - Oracle

학원 36일차 - Oracle

0개의 댓글

관련 채용 정보