12장 데이터정의어
데이터 정의어(DDL : Data Definition Language)
: 데이터베이스 데이터를 보관하고 관리하기 위해 제공되는 여러 객체의 생성, 변경, 삭제 관련 기능을 수행
- 데이터 조작어(insert, delete, update)는 commit을 해줘야하지만, 데이터정의어는 명령을 수행하자마자 데이터베이스에 바로 반영되는 특성이 있어서 (자동으로 커밋돼서) commit 안해줘도 됨
=> rollback을 통한 실행취소 불가능 - CREATE(객체 생성), ALTER(이미 생성된 객체를 변경), DROP(객체를 삭제)등의 명령어
create : 테이블 생성
<형식>
create table 소유계정.테이블 이름(
열1 이름 열1 자료형,
열2 이름 열2 자료형,
...
);테이블 생성해보기
tab 목록 조회
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
SALGRADE TABLEtb_test 생성
SQL> create table tb_test(
2 id varchar2(10),
3 name varchar2(10),
4 pass varchar2(10),
5 addr varchar2(10),
6 point number,
7 regdate date);
Table created.
tab에서 생성된 테이블 확인
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
SALGRADE TABLE
TB_TEST TABLE테이블 구조확인
SQL> select * from tb_test;
no rows selected
SQL> desc tb_test;
Name Null? Type
------------------- -------- -----------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(10)
POINT NUMBER
REGDATE DATE
<참고> 자료형 39p
이미지를 db에 저장하지않음
varchar2(길이)
: 4000byte만큼의 가변길이 문자열 데이터 저장 가능(최소크기 1byte)
number(전체자릿수, 소수점이하자릿수)
: +-38자릿수의 숫자 저장 가능
date
: 날짜 형식을 저장
char(길이)
: 4000byte만큼의 고정길이 문자열 데이터 저장가능
<참고> 객체 40,41p
: 오라클 데이터베이스 내에서 데이터를 저장/관리하기 위한 논리구조 가진 구성요소
테이블
: 데이터를 저장하는 장소
인덱스
: 테이블의 검색 효율을 높이기 위해 사용
뷰
: 하나 또는 여러 개의 선별된 데이터를 논리적으로 연결해 하나의 테이블처럼 사용하게 해줌
시퀀스
: 일련번호를 생성해줌
시노님
: 오라클 객체의 별칭(다른이름)을 지정함
프로시저
: 프로그래밍 연산 및 기능수행이 가능(반환값 없음)
함수
: 프로그래밍 연산 및 기능수행이 가능(반환값 있음)
패키지
: 관련있는 프로시저와 함수를 보관함
트리거
: 데이터 관련 작업의 연결 및 방지 관련 기능을 제공함
다른테이블 조회해서 테이블 만들기
SQL> create table copytable
2 as select * from tb_test;
Table created.테이블 구조확인
SQL> desc copytable;
Name Null? Type
--------------------- -------- --------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(10)
POINT NUMBER
REGDATE DATE
drop : 테이블 삭제
SQL> drop table copytable2;
Table dropped.삭제됐는지 tab에서 조회
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
COPYTABLE TABLE
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
SALGRADE TABLE
TB_TEST TABLE
6 rows selected.rename : 테이블 이름 변경
SQL> rename copytable to tb_test2;
Table renamed.변경됐는지 tab에서 조회
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
SALGRADE TABLE
TB_TEST TABLE
TB_TEST2 TABLE
6 rows selected.Alter : 테이블 변경
- 14장 제약조건에서 제약조건 추가하는 것도 테이블수정에 해당
테이블 구조확인
SQL> desc tb_test2;
Name Null? Type
-------------------- -------- -----------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(10)
POINT NUMBER
REGDATE DATE글자수 설정값보다 넘쳐서 에러
SQL> insert into tb_test2 values('test',null,null,'서울특별시',null,null);
insert into tb_test2 values('test',null,null,'서울특별시',null,null)
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."TB_TEST2"."ADDR" (actual: 15, maximum: 10)alter로 자료형 변경하기 : modify
SQL> alter table tb_test2
2 modify (addr varchar2(100),point number(10));
Table altered.테이블 구조확인 : 글자수 변경됨
SQL> desc tb_test2;
Name Null? Type
-------------------- -------- -----------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(100)
POINT NUMBER(10)
REGDATE DATEalter로 열 추가하기 : add
SQL> alter table tb_test2
2 add email varchar2(15);
Table altered.테이블 구조확인 : 열 추가됨
SQL> desc tb_test2;
Name Null? Type
---------------- -------- -----------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(100)
POINT NUMBER(10)
REGDATE DATE
EMAIL VARCHAR2(15)alter로 열 이름 변경하기 : rename
SQL> alter table tb_test2
2 rename column regdate to hiredate;
Table altered.테이블 구조확인 : 열 이름 변경됨
SQL> desc tb_test2;
Name Null? Type
---------------- -------- ----------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(100)
POINT NUMBER(10)
HIREDATE DATE
EMAIL VARCHAR2(15)alter로 특정 열 삭제하기 :drop
SQL> alter table tb_test2
2 drop column email;
Table altered.10장 데이터조작어
테이블에 데이터 추가하기 (insert)
insert문
테이블에 데이터 추가하는데에 사용
<형태>
insert into 테이블이름(열1, 열2, ...)
values (열1에 들어갈 데이터, 열2에 들어갈 데이터,...)
테이블 구조확인
SQL> desc tb_test;
Name Null? Type
---------------- -------- ------------
ID VARCHAR2(10)
NAME VARCHAR2(10)
PASS VARCHAR2(10)
ADDR VARCHAR2(10)
POINT NUMBER
REGDATE DATEinsert로 테이블에 데이터 추가 방식1
SQL> insert into tb_test(id,name) values('jang','장동건');
1 row created.tb_test 테이블 데이터 확인
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
jang 장동건똑같은 데이터 한 번 더 저장 및 확인
SQL> insert into tb_test(id,name) values('jang','장동건');
1 row created.
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
jang 장동건
jang 장동건화면 껐다 켜면 insert로 등록한 데이터 없음(commit하지 않아서)
SQL> conn scott/tiger
Connected.
SQL> set linesize 300
SQL> set pagesize 100
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
SALGRADE TABLE
TB_TEST TABLE
SQL> select * from tb_test;
no rows selectedinsert로 테이블에 데이터 추가 방식2 & commit하기
SQL> insert into tb_test values('bts1','슈가','1234','서울시',10000,sysdate);
1 row created.
SQL> insert into tb_test values('bts2','RM','1234','인천',30000,sysdate);
1 row created.
SQL> insert into tb_test values('bts3','진','1234','서울시',10000,sysdate);
1 row created.
SQL> insert into tb_test values('bts4','정국','1234','경기도',10000,sysdate);
1 row created.
SQL> commit;
Commit complete.tb_test 테이블 데이터 확인
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11insert로 테이블에 데이터 추가 방식3(다른 테이블 가져오기)
SQL> insert into tb_test(id,name,regdate)
2 select empno,ename,hiredate
3 from emp
4 where deptno=10;
3 rows created.tb_test 테이블 데이터 확인
SQL> select* from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11
7782 CLARK 81/06/09
7839 KING 81/11/17
7934 MILLER 82/01/23
7 rows selected.delete : 테이블에 있는 데이터 삭제하기
[형식]
delete from(선택) 테이블이름
where 삭제할 대상 행을 선별하기 위한 조건식(선택)
delete로 테이블 전체 삭제
SQL> delete from tb_test;
7 rows deleted.데이터 삭제후 확인
SQL> select * from tb_test;
no rows selectedrollback : 수정한 내용 되돌리기
롤백하면 마지막 커밋 이후까지로 돌아감
SQL> rollback;
Rollback complete.되돌린 테이블 데이터 조회(commit전까지로 돌아감)
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11데이터 다시추가(insert로 다른테이블 복사해서 추가) 및 커밋
SQL> insert into tb_test(id,name,regdate)
2 select empno,ename,hiredate
3 from emp
4 where deptno=10;
3 rows created.
SQL> commit;
Commit complete.테이블 데이터 조회
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11
7782 CLARK 81/06/09
7839 KING 81/11/17
7934 MILLER 82/01/23
7 rows selected.
delete 데이터 조건삭제
SQL> delete from tb_test
2 where pass is null;
3 rows deleted.삭제후 테이블 데이터 조회 및 커밋
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11
SQL> commit;
Commit complete.insert로 데이터 추가 후 커밋
SQL> insert into tb_test(id,name,regdate)
2 select empno,ename,hiredate
3 from emp
4 where deptno=10;
3 rows created.
SQL> commit;
Commit complete.테이블 데이터 조회
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11
7782 CLARK 81/06/09
7839 KING 81/11/17
7934 MILLER 82/01/23
7 rows selected.delete로 데이터 삭제 후 커밋
SQL> delete from tb_test
2 where pass is null;
3 rows deleted.
SQL> commit;
Commit complete.delete 데이터 일부삭제(서브쿼리 사용)
SQL> delete from tb_test
2 where addr = (select addr
3 from tb_test
4 where id='bts3');
2 rows deleted.테이블 데이터조회
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts2 RM 1234 인천 30000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11rollback
SQL> rollback;
Rollback complete.테이블 데이터 조회 : 커밋전까지의 데이터로 돌아감
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------- ---------- --------
bts1 슈가 1234 서울시 10000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 10000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11
update : 테이블 데이터 수정하기, 277p
[형식]
update 변경할 테이블
set 변경할 열1=데이터, 변경할 열2=데이터, ...
where 데이터를 변경할 대상 행 선별 조건(선택)
update로 데이터 변경 에러
: 자료형 길이 초과함
SQL> update tb_test
2 set addr='서울특별시', point=20000
3 where addr='서울시';
set addr='서울특별시', point=20000
*
ERROR at line 2:
ORA-12899: value too large for column "SCOTT"."TB_TEST"."ADDR" (actual: 15, maximum: 10)데이터 정의어(alter, modify)로 자료형 변경
SQL> alter table tb_test
2 modify addr varchar2(100);
Table altered.update로 데이터 변경 및 커밋
SQL> update tb_test
2 set addr='서울특별시', point=20000
3 where addr='서울시';
2 rows updated.
SQL> commit;
Commit complete.테이블 데이터 조회
SQL> select * from tb_test;
ID NAME PASS ADDR
-------------------- -------------------- -------------------- ----------------
bts1 슈가 1234 서울특별시
bts2 RM 1234 인천
bts3 진 1234 서울특별시
bts4 정국 1234 경기도update와 서브쿼리로 데이터 수정
SQL> update tb_test
2 set addr='서울시', point=15000
3 where addr= (select addr
4 from tb_test
5 where id='bts1');
2 rows updated.테이블 데이터 조회
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- --------
bts1 슈가 1234 서울시 15000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 15000 24/04/11
bts4 정국 1234 경기도 10000 24/04/11update와 서브쿼리로 데이터 수정(연산자까지..?) 및 커밋
SQL> update tb_test
2 set point = (select point
3 from tb_test
4 where id='bts1') +500
5 where id='bts4';
1 row updated.
SQL> commit;
Commit complete.테이블 데이터 조회
bts1의 포인트로 변경하고 거기에 500 더하기한 결과
SQL> select * from tb_test;
ID NAME PASS ADDR POINT REGDATE
-------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- --------
bts1 슈가 1234 서울시 15000 24/04/11
bts2 RM 1234 인천 30000 24/04/11
bts3 진 1234 서울시 15000 24/04/11
bts4 정국 1234 경기도 15500 24/04/11
미션
1. 테이블생성하기
-
테이블명(myemp)
-
테이블구성하는 필드
empno : char(5)
ename: varchar2(10)
hiredate:date
sal : number(10)
memo : varchar2(10)
SQL> create table myemp(
2 empno char(5),
3 ename varchar2(10),
4 hiredate date,
5 sal number(10),
6 memo varchar2(10));
Table created.2. 데이터입력하기
00001,scott,오늘날짜,3000,신입
SQL> insert into myemp values('00001','scott',sysdate,3000,'신입');
1 row created.3. 삽입된 데이터 저장하기
SQL> commit;
Commit complete.4. 전체 데이터 조회하기
SQL> select * from myemp;
EMPNO ENAME HIREDATE SAL MEMO
---------- -------------------- -------- ---------- --------------------
00001 scott 24/04/11 3000 신입create로 department라는 이름의 테이블 생성
SQL> create table department(
2 deptcode varchar2(10),
3 name varchar2(30));
Table created.테이블 자료구조확인
SQL> desc department;
Name Null? Type
---------------------------- -------- --------------
DEPTCODE VARCHAR2(10)
NAME VARCHAR2(30)insert into로 생성한 department테이블에 데이터 추가
SQL> insert into department values('001','전산실');
1 row created.
SQL> insert into department values('002','인사과');
1 row created.
SQL> insert into department values('003','영업부');
1 row created.
SQL> insert into department values('004','기획실');
1 row created.insert로 했으니까 커밋
SQL> commit;
Commit complete.insert into로 myemp테이블에 데이터 추가
SQL> insert into myemp values('test','scott',sysdate,3000,'신입','005');
1 row created.
SQL> insert into myemp values('test','scott',sysdate,30000,'신입','005');
1 row created.테이블 조회
SQL> select * from myemp;
EMPNO ENAME HIREDATE SAL MEMO DEPTNO
---------- -------------------- -------- ---------- -------------------- -------------------
00001 scott 24/04/11 3000 신입
00001 scott 24/04/11 3000 신입
scott 24/04/11 3000 신입
test scott 24/04/11 3000 신입 005
test scott 24/04/11 30000 신입 005이렇게 기준없이 엉망으로 들어가는거 제한해야 하는 필요성
delete로 myemp 데이터 삭제 및 커밋
SQL> delete from myemp;
5 rows deleted.
SQL> select * from myemp;
no rows selected
SQL> commit;
Commit complete.14장 제약조건
딕셔너리는 데이터베이스에서 메타데이터(metadata)를 저장하고 관리하는 특별한 테이블의 모음을 의미합니다. 이 테이블들은 데이터베이스의 객체와 구조에 대한 정보를 제공하는 데 사용됩니다. Oracle 데이터베이스에서는 이러한 딕셔너리를 "데이터 딕셔너리" 또는 "뷰"로 참조할 수 있습니다.
데이터 딕셔너리를 사용하면 다음과 같은 정보를 얻을 수 있습니다:
- 테이블, 뷰, 인덱스 등의 데이터베이스 객체 정보
- 사용자, 롤(Role), 권한 정보
- 데이터베이스 세그먼트, 데이터 파일, 오라클 버전 정보 등
- 메타데이터
- 오라클 내부에서 데이터베이스(테이블,시퀀스,제약조건...)에 대한 정보를 저장하는 특별한 뷰
- 제약조건을 저장하는 딕셔너리(user_constraints)
제약조건 종류
- not null : 지정한 열에 null을 허용하지 않음. null을 제오한 데이터의 중복은 허용
- unique : 지정한 열이 유일한 값을 가져야함(중복불가). 단 null값은 중복에서 제외
- primary key : 지정한 열이 유일한 값이면서 null을 허용하지 않음. primary key는 테이블당 하나만 지정 가능
- foreign key : 다른 테이블의 열을 참조하며 존재하는 값만 입력할 수 있음.
- check : 설정한 조건식을 만족하는 데이터만 입력가능
SQL> desc user_constraints;
Name Null? Type
---------------------- -------- ------------------
OWNER VARCHAR2(120)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(120)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)제약조건
SQL> select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
2 from user_constraints;
OWNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME CO TABLE_NAME
------------------------------------------------------------ -- ------------------------------------------------------------
SCOTT
SYS_C006997 P EMP
test 테이블 만들기(primary key, not null)
SQL> create table test(
2 id varchar2(20) primary key,
3 name varchar2(20) not null);
Table created.제약조건이 추가된 것 확인가능
SQL> select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
2 from user_constraints;
OWNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME CO TABLE_NAME
------------------------------------------------------------ -- ------------------------------------------------------------
SCOTT
SYS_C006997 P EMP
SCOTT
SYS_C007008 C TEST
SCOTT
SYS_C007009 P TESTmyemp 테이블 구조확인
SQL> desc myemp
Name Null? Type
---------------- --------- ----------
EMPNO CHAR(5)
ENAME VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(10)
MEMO VARCHAR2(10)
DEPTNO VARCHAR2(10)myemp테이블 수정 : memo열에 check 제약조건 추가
- myemp 테이블에 memo 열에 대한 체크 제약 조건인 memo_check를 추가
- 이 체크 제약 조건은 memo 열이 '신입' 또는 '경력' 값만 포함
SQL> alter table myemp
2 add constraint memo_check check(memo in ('신입','경력'));
Table altered.제약조건 추가 확인
SQL> select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
2 from user_constraints;
OWNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME CO TABLE_NAME
------------------------------------------------------------ -- ------------------------------------------------------------
SCOTT
SYS_C006997 P EMP
SCOTT
SYS_C007008 C TEST
SCOTT
SYS_C007009 P TEST
SCOTT
MEMO_CHECK C MYEMP
myemp 테이블에 데이터 insert
SQL> insert into myemp(empno,ename,memo) values('bts1','슈가','신입');
1 row created.
SQL> insert into myemp(empno,ename,memo) values('bts2','RM','경력');
1 row created.데이터 insert 중 에러
- 첫 번째 쿼리에서 memo 열에 입력된 값 '신입사원'은 memo 열의 최대 길이인 10자를 초과
- 두 번째 쿼리에서는 체크 제약 조건 memo_check가 위배. '신입사'는 허용된 값인 '신입' 또는 '경력' 중 하나가 아님
SQL> insert into myemp(empno,ename,memo) values('bts2','RM','신입사원');
insert into myemp(empno,ename,memo) values('bts2','RM','신입사원')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."MYEMP"."MEMO" (actual: 12, maximum: 10)
SQL> insert into myemp(empno,ename,memo) values('bts2','RM','신입사');
insert into myemp(empno,ename,memo) values('bts2','RM','신입사')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.MEMO_CHECK) violated
myemp테이블 수정 : sal 열에 check제약조건 추가
sal은 3000과 10000사이의 값만 가짐
SQL> alter table myemp
2 add constraint sal_check check(sal between 3000 and 10000);
Table altered.myemp 테이블에 데이터 insert
SQL> insert into myemp(empno,ename,memo,sal) values('bts2','RM','신입',3000);
1 row created.
SQL> insert into myemp(empno,ename,memo,sal) values('bts2','RM','신입',10000);
1 row created.데이터 insert 중 에러
check제약조건 위배해서 에러
SQL> insert into myemp(empno,ename,memo,sal) values('bts2','RM','신입',30000);
insert into myemp(empno,ename,memo,sal) values('bts2','RM','신입',30000)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SAL_CHECK) violated
myemp 테이블에 제약조건 수정
이 SQL 문장은 myemp 테이블의 ename 열에 NOT NULL 제약 조건을 추가하려고 합니다. 이렇게 하면 ename 열에는 NULL 값을 허용하지 않게 됩니다.
SQL> alter table myemp
2 modify (ename not null);
Table altered.myemp 테이블 구조확인
SQL> desc myemp
Name Null? Type
-------------------- -------- ---------------
EMPNO CHAR(5)
ENAME NOT NULL VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(10)
MEMO VARCHAR2(10)
DEPTNO VARCHAR2(10)제약조건 추가 확인
SQL> select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
2 from user_constraints;
OWNER
------------------------------------------------------------------------------------
CONSTRAINT_NAME CO TABLE_NAME
------------------------------------------------------------ -- --------------------
SCOTT
SYS_C006997 P EMP
SCOTT
SYS_C007008 C TEST
SCOTT
SYS_C007009 P TEST
SCOTT
MEMO_CHECK C MYEMP
SCOTT
SAL_CHECK C MYEMP
SCOTT
SYS_C007012 C MYEMP
6 rows selected.
myemp 테이블 SAL_CHECK 제약 조건 삭제
SQL> alter table myemp
2 drop constraint SAL_CHECK;
Table altered.제약조건 삭제 확인
SQL> select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
2 from user_constraints;
OWNER
-------------------------------------------------------------------------------
CONSTRAINT_NAME CO TABLE_NAME
------------------------------------------------------------ -- ---------------
SCOTT
SYS_C006997 P EMP
SCOTT
SYS_C007008 C TEST
SCOTT
SYS_C007009 P TEST
SCOTT
MEMO_CHECK C MYEMP
SCOTT
SYS_C007012 C MYEMP
myemp 테이블 데이터 조회
SQL> select *from myemp;
EMPNO ENAME HIREDATE SAL MEMO DEPTNO
---------- -------------------- -------- ---------- -------------------- --------------------
bts2 RM 10000 신입
bts1 슈가 신입
bts2 RM 경력
bts2 RM 3000 신입myemp 테이블 deptno 수정 및 커밋
SQL> update myemp
2 set deptno='001';
4 rows updated.
SQL> commit;
Commit complete.
myemp 테이블의 모든 데이터 조회
SQL> select *from myemp;
EMPNO ENAME HIREDATE SAL MEMO DEPTNO
---------- -------------------- -------- ---------- -------------------- --------------------
bts2 RM 10000 신입 001
bts1 슈가 신입 001
bts2 RM 경력 001
bts2 RM 3000 신입 001department 테이블의 모든 데이터 조회
SQL> select *from department;
DEPTCODE NAME
-------------------- ------------------------------------------------------------
001 전산실
002 인사과
003 영업부
004 기획실
department 테이블에 제약조건 추가
- 이 명령을 실행하면 deptcode 열이 department 테이블의 기본 키(primary key)로 설정됨
- 기본 키는 각 행을 고유하게 식별하는 역할을 하며, 중복 값을 허용하지 않음.
SQL> alter table department
2 add constraint pk_deptcode primary key(deptcode);
Table altered.제약조건 확인
SQL> select OWNER,CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
2 from user_constraints;
OWNER
------------------------------------------------------------------------------
CONSTRAINT_NAME CO TABLE_NAME
------------------------------------------------------------ -- --------------
SCOTT
SYS_C006997 P EMP
SCOTT
SYS_C007008 C TEST
SCOTT
SYS_C007009 P TEST
SCOTT
MEMO_CHECK C MYEMP
SCOTT
PK_DEPTCODE P DEPARTMENT
SCOTT
SYS_C007012 C MYEMP
6 rows selected.department 테이블 구조확인
SQL> desc department;
Name Null? Type
------------------ -------- ------------------
DEPTCODE NOT NULL VARCHAR2(10)
NAME VARCHAR2(30)에러확인
- not null 제약조건있어서 null값 삽입 불가
- primary key라서 중복값 허용하지않음. 이미있는 값 삽입 불가
에러확인
- 제약조건 이름 지정하지 않음
- 기본 키 제약 조건을 검증할 수 없을 때 발생. 대개 테이블에 이미 중복된 기본 키 값이 있거나, 기본 키 제약 조건이 이미 있는데 다른 제약 조건이나 테이블 상태 때문에 해당 제약 조건을 활성화할 수 없을 때 발생
myemp 테이블의 모든 데이터 조회
SQL> select *from myemp;
EMPNO ENAME HIREDATE SAL MEMO DEPTNO
---------- -------------------- -------- ---------- -------------------- --------------------
bts2 RM 10000 신입 001
bts1 슈가 신입 001
bts2 RM 경력 001
bts2 RM 3000 신입 001myemp테이블의 empno이 bts2인 데이터 삭제 및 커밋
중복값 3개 삭제
SQL> delete from myemp
2 where empno='bts2';
3 rows deleted.
SQL> commit;
Commit complete.삭제됐으니 오류났던 empno 기본키 제약조건 다시 설정
SQL> alter table myemp
2 add constraint mypk primary key(empno);
Table altered.myemp 테이블 구조확인
SQL> desc myemp;
Name Null? Type
------------------ -------- -------------
EMPNO NOT NULL CHAR(5)
ENAME NOT NULL VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(10)
MEMO VARCHAR2(10)
DEPTNO VARCHAR2(10)department 테이블 구조확인
SQL> desc department
Name Null? Type
------------------------- -------- --------------
DEPTCODE NOT NULL VARCHAR2(10)
NAME VARCHAR2(30)myemp 테이블 제약조건 추가
myemp 테이블에 deptno 열에 대한 외래 키 제약 조건을 추가
- REFERENCES: 이 키워드는 외래 키 제약 조건을 설정할 때 사용
- department: 외래 키가 참조하는 테이블의 이름
- (deptcode): department 테이블에서 외래 키가 참조하는 열의 이름
SQL> alter table myemp
2 add constraint fk foreign key(deptno) references department(deptcode);
Table altered.
위 제약조건 추가로 인해 발생하는 insert오류
- 외래 키 제약 조건을 위배시 발생하는 오류
myemp 테이블에 삽입하려는 행의 deptno 값이 department 테이블의 deptcode 값 중 어느 것과도 일치하지 않을 때 발생합니다.
SQL> insert into myemp values('bts2','RM',sysdate,3000,'신입','005');
insert into myemp values('bts2','RM',sysdate,3000,'신입','005')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK) violated - parent key not found바르게 넣기
SQL> insert into myemp values('bts2','RM',sysdate,3000,'신입','004');
1 row created.무결한 데이터관리위해 pk,fk는들어감..
SQL> select SEARCH_CONDITION,CONSTRAINT_TYPE,CONSTRAINT_NAME
2 from user_constraints;
SEARCH_CONDITION CO CONSTRAINT_NAME
-------------------------- -- ---------------------
P SYS_C006997
"NAME" IS NOT NULL C SYS_C007008
P SYS_C007009
memo in ('신입','경력') C MEMO_CHECK
P PK_DEPTCODE
"ENAME" IS NOT NULL C SYS_C007012
P MYPK
R FK
8 rows selected.13장 객체종류
13-4 시퀀스, 348p
시퀀스 : 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체
ex. 은행/병원의 대기순번표
시퀀스 생성
시퀀스를 생성하려면 CREATE SEQUENCE 문을 사용합니다.
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MAXVALUE max_value
MINVALUE min_value
CYCLE | NOCYCLE
CACHE cache_value | NOCACHE;- sequence_name: 시퀀스의 이름
- initial_value: 시퀀스가 시작될 값
- increment_value: 시퀀스가 증가할 값
- max_value: 시퀀스의 최대 값 (CYCLE 옵션이 NOCYCLE로 설정된 경우에만 사용)
- min_value: 시퀀스의 최소 값 (CYCLE 옵션이 NOCYCLE로 설정된 경우에만 사용)
- CYCLE: 시퀀스가 최대 값에 도달하면 다시 최소 값부터 시작
- NOCYCLE: 시퀀스가 최대 값에 도달하면 오류를 발생
- CACHE: 시퀀스의 값을 미리 캐시하여 성능을 향상
- NOCACHE: 시퀀스의 값을 캐시하지 않음
시퀀스 사용
시퀀스에서 다음 값에 접근하려면 NEXTVAL 또는 CURRVAL 함수를 사용
- NEXTVAL: 시퀀스의 다음 값을 반환
- CURRVAL: 현재 세션에서 시퀀스의 현재 값을 반환 이 함수를 사용하려면 먼저 NEXTVAL 함수를 호출
-- 시퀀스의 다음 값을 가져옵니다.
SELECT sequence_name.NEXTVAL FROM dual;
-- 시퀀스의 현재 값을 가져옵니다.
SELECT sequence_name.CURRVAL FROM dual;시퀀스 삭제
시퀀스를 삭제하려면 DROP SEQUENCE 문을 사용
DROP SEQUENCE sequence_name;boardno_seq 시퀀스 생성
SQL> create sequence boardno_seq;
Sequence created.boardno_seq 시퀀스 다음값 반환
nextval: 오라클에서 자동으로 값증가시킬때
SQL> select boardno_seq.nextval from dual;
NEXTVAL
----------
1
SQL> select boardno_seq.nextval from dual;
NEXTVAL
----------
2
SQL> select boardno_seq.nextval from dual;
NEXTVAL
----------
3시퀀스 다음값 반환+문자넣기
SQL> select 'guest' || boardno_seq.nextval from dual;
'GUEST'||BOARDNO_SEQ.NEXTVAL
-----------------------------------------------------------------
guest4
SQL> select 'guest' || boardno_seq.nextval from dual;
'GUEST'||BOARDNO_SEQ.NEXTVAL
-----------------------------------------------------------------
guest5myorder테이블, orderproduct테이블 생성
SQL> create table myorder(
2 orderno varchar2(20),
3 orderdate date);
Table created.
SQL> create table orderproduct(
2 orderno varchar2(20),
3 prdno varchar2(20));,
Table created.
order_seq 시퀀스 생성
SQL> create sequence order_seq;
Sequence created.myorder 테이블에 order_seq 시퀀스
SQL> insert into myorder values('20240411'||order_seq.nextval, sysdate);
1 row created.currval : 현재값가져오기
SQL> select * from myorder;
ORDERNO ORDERDAT
---------------------------------------- --------
202404111 24/04/11SQL> select order_seq.currval from dual;
CURRVAL
----------
1SQL> insert into orderproduct values('20240411'||order_seq.currval,'001');
1 row created.
SQL> insert into orderproduct values('20240411'||order_seq.currval,'002');
1 row created.
SQL> insert into orderproduct values('20240411'||order_seq.currval,'003');
1 row created.SQL> select * from myorder;
ORDERNO ORDERDAT
---------------------------------------- --------
202404111 24/04/11SQL> select * from orderproduct;
ORDERNO PRDNO
---------------------------------------- ---------------------------------------
202404111 001
202404111 002
202404111 003
SQL> insert into myorder values('20240411'||order_seq.nextval, sysdate);
1 row created.SQL> insert into orderproduct values('20240411'||order_seq.currval,'001');
1 row created.SQL> select * from myorder;
ORDERNO ORDERDAT
---------------------------------------- --------
202404111 24/04/11
202404112 24/04/11
SQL> select * from orderproduct;
ORDERNO PRDNO
---------------------------------------- ----------------------------------------
202404111 001
202404111 002
202404111 003
202404112 001
SQL> desc myemp;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- -----------------
EMPNO NOT NULL CHAR(5)
ENAME NOT NULL VARCHAR2(10)
HIREDATE DATE
SAL NUMBER(10)
MEMO VARCHAR2(10)
DEPTNO VARCHAR2(10)SQL> alter table myemp
2 add nickname varchar2(20);
Table altered.
SQL> alter table myemp
2 add constraint nick_uniq unique(nickname);
Table altered.총정리과제
계정생성
- 관리자계정으로 들어가서 생성해야함
- shop/shop 으로 생성
- connect, resource 권한 주기
SQL> conn system/시스템비번
Connected.
SQL> create user shop identified by shop;
User created.
SQL> grant connect, resource to shop;
Grant succeeded.SQL> conn scott/tiger
Connected.
SQL> select *from tab
2 ;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
DEPARTMENT TABLE
DEPT TABLE
EMP TABLE
LOCATIONS TABLE
MYEMP TABLE
MYORDER TABLE
ORDERPRODUCT TABLE
SALGRADE TABLE
TB_TEST TABLE
TEST TABLE
10 rows selected.
SQL> drop table department;
drop table department
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
패어런트키(pk) 테이블을 함부로 삭제할수없음
본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.
