테이블: DB에서 데이터 저장되는 가장 기본적인 단위의 객체
- column과 row로 구성
- user가 table 생성 권한(create table)과 data를 저장할 수 있는 공간할당(quota)가 있어야 한다.
- 테이블 이름 규칙
- USER명이 스키마 이름이 된다.
스키마는 특성 USER소유의 객체들의 집합(구조)으로서 스키마 내에서 동일한 이름의 객체가 될 수 없다.
- 테이블에 대한 메타 정보는 데이터 사전 (시스템 카탈로그)에 저장된다.
- 테이블 생성
Create table 테이블 이름 (
컬럼 이름 컬럼타입(크기) [default 기본값] [제약 조건],
컬럼 이름 컬럼타입(크기) …..
);
- 컬럼타입 종류
가변길이의 문자열: varchar2 (1~4000) à 동적일 때
Varchar2(10) 타입에 'A' 문자를 저장할 경우 실제 저장은 1byte만 할당되어 저장된다.
고정길이의 문자열: char(1~2000) à 성능 고려일 때
Char(10) 타입에 'A'문자를 저장할 경우 실제 저장은 10byte할당되어서 저장된다.
숫자타입: 정수, 실수, number(1자리(p), 소수점)
Ex) numer(5) : 정수 5자리 저장, number(8,2) : 정수 8자리, 소수점 이하 2자리 저장.
날짜: Date(세기, 년, 월, 일, 시, 분, 초)
Timestamp (세기, 년, 월, 일, 시, 분, 초, 정밀한 초 단위(10억분의 1초))
Timestamp with time zone,
Timestamp with local timezone(DB위치가 어디든 해당 나라에 맞춰서 시간대역이 자동 변환된다)
RAW: binary data(2진)를 저장 (1~2000) 증명사진, 지문이미지, sound데이터
CLOB (1~4G): character large object
BLOB (1~4G): binary large object
BFILE: DB외부에 운영체제의 파일 시스템에 파일 형태로 저장 (읽기용)
ROWID: DB에 저장되는 모든 레코드들의 논리적인 주소 값(객체번호+파일번호+블락번호+행번호)
- 테이블 생성 예제
Create table travel(
travel_code char(6) primary key,
travel_name varchar2(20) not null,
flight varchar2(30),
max_people number(3),
reserved number(3)
);
생성 확인하기
desc travel;
describe travel
select * from travel;
select * from tab;
select constraint_name, constraint_type
from user_constraints
where table_name = 'TRAVEL';
* constraint_type 키 종류 (PK,FK….c(not null))
* constraint_name 제약조건에 해당하는 이름
drop table travel;
-- 오라클 9i 버전에서는 물리적으로 완전히 삭제
-- 오라클 10g 버전에서는 recyclebin에 이름이 변경이 되어서 남아 있다.
select * from tab;
select * from recyclebin;
-- bin 폴더에 남아있다. 삭제된 결과것들...
flashback table travel to before drop;
-- recycle에 남아 있던 travel 테이블 자료를 복구 한다.
desc travel;
drop tabel travel purge;
-- 처음부터 아예 travel 자료를 삭제하는것이다.
-- 오라클 10g 버전부터는 물리적으로 완전 삭제한다는 문장.
purge recyclebin;
-- 윈도우 운영체제의 휴지통과 같은 개념인 recyclebin안에 내용 전부 삭제
1]이미 생성되어 있는 테이블에 대해서 구조와 데이터를 모두 복제해서 다른 이름의 테이블로 생성(backup용)
desc dept
select * from dept;
create table copy_dept
as select * from dept;
desc copy_dept
select * from copy_dept;
-- 서브 쿼리가 항상 먼저 실행된다.
2] 이미 생성되어 있는 테이블에 대해서 부분적 구조와 데이터를 모두 복제해서 다른 이름의 테이블로 생성
create table copy_emp
as select empno id, ename name, job jobid, sal salary, deptno
from emp
where deptno = 30;
desc copy_emp
select * from copy_emp;
3] 이미 생성되어 있는 테이블에 대해서 구조만(컬럼, 이름도 변경가능) 복제한다.
drop table copy_dept purge; // 위에서 생성한 copy_dept 삭제 (오라클 10g버전 이상)
create table copy_dpet
as select * from dept
where 1=2;
desc copy_dept
select * from copy_dept; // 오류 메시지는 where조건을 만족하는 내용이 없으므로 나온다.
create table t_emp
as select * from emp
where 1=2;
desc t_emp
select * from t_emp; // 테이블 생성
insert into dept
values (50, 'IT개발', '서울');
è 컬럼 리스트를 생략한 경우 테이블에 정의된 컬럼순서로 모든값을 VALUE절에 선언해야 한다.
insert into dept
values (60,'IT디자인',null);
select * from dept;
insert into dept(deptno)
values (70);
è 생략한 컬럼은 null값을 허용하는 컬럼이어야 하며, 컬럼값으로 null로 추가된다.
insert into dept (deptno, dname)
values (10, '모바일 개발');
è 오류! deptno는 PK(기본키) 컬럼이며 중복된 값을 허용 하지 않는다.
insert into dept (deptno, dname)
values (200, '모바일 개발');
è 오류! deptno 컬럼 타입의 크기 초과 (number(2)인데 200은 3자리이므로 초과)
insert into dept (dname, loc)
values ('모바일개발', '인천');
è 오류! Deptno컬럼은 필수컬럼이며, null허용 안함
insert into t_dept(deptno, dname)
values (10, '웹개발');
select * from t_dept;
insert into t_dept(deptno, dname)
values (10, '모바일개발');
select * from t_dept;
è 오류 PK 에는 중복된 값이 들어갈 수 없다.
insert into t_dept(deptno, dname)
values (20, '모바일개발', default);
select * from t_dept;
è 오류
insert into t_dept(deptno, dname)
values (30, '자바개발', '인천');
select * from t_dept;
è 오류
desc t_dept;
select * from t_dept;
è 확인
desc t_emp
select * from t_emp;
insert into t_emp
select * from emp
where deptno = 20;
select * from t_emp;
-- insert into ~ values 절은 한번 수행에 1행만 추가된다.
-- 서브쿼리를 이용한 insert는 서브쿼리에서 조회된 행수만큼 insert된다.
insert into t_emp(empno, ename, job, sal, deptno)
select empno, ename, job, sal, deptno from emp
where deptno = 30;
-- 서브쿼리와 insert구문의 컬럼개수와 타입을 맞춰줘야 한다.
- UPDATE 문
update 테이블 이름
Set 컬럼명 = 컬럼값 [, 컬럼명 = 컬럼값,….];
update 테이블 이름
Set 컬럼명 = 컬럼값 [, 컬럼명 = 컬럼값,….];
Where 조건;
Select * from t_emp;
Update t_emp
Set sal = 0;
Select * from t_emp;
è 대상 테이블의 모든 레코드의 변경 컬럼값이 단일값으로 변경된다.
Select * from t_empt;
Update t_empt
set dname ='아이폰개발'
where deptno = 20;
Select * from t_emp;
-- where절 조건을 만족하는 행의 컬럼값만 변경됨
select * from t_emp;
update t_emp
set hiredate = (select sysdate from dual);
select * from t_emp;
-- update문의 set절과 where절에 서브 쿼리 사용 가능
rollback;
-- 전부 취소되어 전 상황으로 돌아간다.
-DELETE 문
delete from 테이블명;
delete from 테이블명
where조건;
select * from t_emp;
select * from t_dept;
select * from dept;
delete from dept
where deptno > 40 ;==>where조건을 만족하는 행만 삭제됨
select * from dept;
insert into t_dept
select * from dept;
select * from t_dept;
delete from t_dept; ==>전체 테이블의 행이 삭제됨
select * from t_dept;
insert into t_emp
select * from emp;
select * from t_emp;
delete from t_dept
where deptno = (select deptno
from dept
where dname = '기아');
è delete의 where절에도 subquery사용 가능
-MERGE 문
Merge 문 : 조인조건에 따라서 대상 테이블에 행이 존재하면 update수행 대상테이블에
행이 존재하지 않으면 insert수행
Merge into 대상테이블 alias
Using 소스테이블 alias
On (대상테이블과 소스테이블의 조인조건)
When matched then
Update set 컬럼명= 컬럼값,….. // 대상테이블에 low가 이미 존재할 때
When not matched then
Insert into (컬럼리스트) values (값 리스트); // 대상테이블에 low가 없을 때
select * from emp;
commit;
insert into t_emp (empno, deptno, ename, sal);
select empno, deptno, ename, sal
from emp
where deptno = 30;
insert into t_emp (empno, deptno, ename, sal);
select empno, deptno, ename, sal
from emp
where deptno = 30;
select * from t_emp;
-- 30번 부서의 일부데이터만 추가된다.
merge into t_emp t
using emp s
on (t.empno = s.empno)
when matched then
update set t.ename = s.ename,
t.sal = s.sal,
t.deptno = s.deptno,
t.hiredate = s.hiredate,
t.job = s.job,
t.comm = s.comm
when not matched then
insert(empno, ename, sal, deptno, hiredate, job, comm)
values(s.empno, s.ename, s.sal, s.deptno, s.hiredate, s.job, s.comm);
select * from t_emp;
-제약조건
-constraints 제약조건
제약조건: 테이블 level의 컬럼에 선언되는 DML규칙
NOT NULL: null을 허용하지 않는다.
primary key: null 허용 안함, 중복된 값 허용 안함.
unique: 중복 값 허용 안함, null 허용
foreign key: 참조하는 부몬 테이블의 컬럼값으로만 insert, update가능
부모테이블에서는 참조하는 자식 레코드가 존재할 경우 delete불가
check: where절에 조건처럼 다양한 조건을 컬럼에 선언(0보다 커야, '남', '여', 값만 insert, update되도록 규칙정의)
create table book (
isbn number(5) constraint book_isbn_nn not null,
title varchar2(20) not null,
price number(6) );
desc book
select constraint_name, constraint_type
from user_constraints
where table_name = 'BOOK';
insert into bool
values (12345, 'java', 20000);
insert into book(isbn, title)
values (1000, 'oracle');
insert into book (title, price)
values ('android', 30000);
alter table book drop constraint book_isbn_nn; // 제약조건 변경
select constraint_name, constraint_type
from user_constraints
where table_name = 'BOOK';
è 테이블을 삭제하면 컬럼에 선언했던 제약조건도 모두 삭제된다.
insert into book (title, price)
values ('android', 3000);
desc book;
select * from book;
è 확인
drop table book purge;
è 테이블 완전삭제
create table book (
isbn number(5),
title varchar2(20),
price number(6) constraint book_price_ck_check (price>0));
desc book
select constraint_name, constraint_type
from user_constraints
where table_name = 'BOOK';
insert into book
values (12345, 'java', 0); è 오류
insert into book (isbn, title)
values (1000, 'oracle'); è null은 허용됨.
insert into book ( title, price)
values ('android', 20000);
create table book_member(
userid varchar2(20),
gender char(1) check (gender in('M','F'))
);
insert into book_member(userid, gender)
values ('kin', 'f'); -- ERROR
insert into book_member(userid, gender)
values ('park', 'M');
insert into book_member
values ('lee', 'W'); -- ERROR
select * from book_member; // 결과 park, m만 나온다.
primary key
: not null+unique
몇십만~몇천만의 행 데이터중 유일하게 하나의 행을 검색할때 사용
Create table book (
isbn number(5) constraint book_isbn_pk primary key,
title varchar2(20),
price number(6) );
desc book
select constraint_name, constraint_type
from user_constraints
where table_name = 'BOOK';
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
à 인덱스 개체 생성, 인덱스 객체는 컬럼값에서 생성 : 빠른 검색을 위해서 데이터 베이스 서버가 사용하는 객체이다.
à primary key컬럼에 대해서 자동으로 인덱스가 생성된다.
Create table book (
isbn number(5) constraint book_isbn_pk primary key,
title varchar2(20),
price number(6) );
desc book
select constraint_name, constraint_type
from user_constraints
where table_name = 'BOOK';
select constraint_name, constraint_type
from user_indexes
where table_name = 'BOOK';
-- 인덱스 개체 생성, 인덱스 객체는 컬럼값에서 생성
-- : 빠른 검색을 위해서 데이터 베이스 서버가 사용하는 객체이다.
insert into book
values (12345, 'java', 20000);
insert into book (isbn, title)
values (12345, 'oracle'); -- 오류
insert into book ( title, price)
values ('android', 30000); -- 오류
select * from book;
alter table book
disable constriant book_isbn_pk;
insert into book (isbn, title)
values (12345, 'oracle');
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
-- PK제약조건을 비활성화하면 자동으로 인덱스도 삭제됨
alter table book
enable constriant book_isbn_pk;
-- 중복된 키가 존재하므로 에러
delete from book
where title = 'oracle';
alter table book
enable constriant book_isbn_pk;
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
-- PK제약조건을 활성화하면 자동으로 인덱스도 삭제됨
drop table book purge;
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
-- 테이블 삭제하면 인덱스도 삭제된다.
unique : null허용, 중복된 값 허용 안함
create table book (
isbn number(5) constraint book_isbn_uk unique,
title varchar2(20) ,
price number(6)
);
desc book
select constraint_name, constraint_type
from user_constraints
where table_name = 'BOOK'; --->? 제약조건타입 :U
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
==> unique key컬럼에 대해서 자동으로 인덱스 생성
insert into book
values (12345, 'java', 20000);
insert into book (isbn, title)
values (12345, 'oracle');-->error
insert into book ( title, price)
values ('android', 30000); --->null허용
alter table book
disable constriant book_isbn_pk;
insert into book (isbn, title)
values (12345, 'oracle');
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
==>unique제약조건을 비활성화하면 자동으로 인덱스도 삭제됨
alter table book
enable constraint book_isbn_uk; -->중복된 키가 존재하므로 에러
delete from book
where title = 'oracle';
alter table book
enable constraint book_isbn_uk;
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
==>unique제약조건을 활성화하면 자동으로 인덱스도 삭제됨
drop table book purge;
select index_name, index_type
from user_indexes
where table_name = 'BOOK';
==> 테이블 삭제하면 인덱스도 삭제됨
Foreign key
foreign key :
create table parent
(p_code varchar2(5),
p_type varchar2(20)
);
create table child (
code varchar2(5),
title varchar2(20),
r_code varchar2(5) constraint child_rcode_fk references parent(p_code)
);
==>error
foreign key 제약조건이 참조하는 테이블의 컬럼에는 반드시 PK나 Unique제약조건이 설정되어 있어야 합니다.
alter table parent
add constraint parent_code_pk primary key (p_code);
create table child (
code varchar2(5),
title varchar2(20),
r_code varchar2(5) constraint child_rcode_fk references parent(p_code)
);
select constraint_name, constraint_type, r_constraint_name
from user_constraints
where table_name= 'CHILD';
insert into parent values ('M001' ,'Music');
insert into parent values ('B001' ,'Book');
insert into parent values ('C001' ,'Cinema');
insert into child
values ( '0002', '거북이', 'M001');
insert into child
values ( '0003', '눈꽃', null);
insert into child
values ( '0001', 'java', 'B001');
insert into child
values ( 'C001-1', '월드 워즈3', 'c002'); -->error
update child
set r_code ='C002'
where code='0003'; -->error
delete from parent
where p_type='Music'; -->error
==>부모 테이블의 레코드를 참조하는 자식 테이블의 레코드가 존재하므로 삭제 불가
delete from parent
where p_type='Cinema'; -->부모 테이블의 레코드를 참조하는 자식 테이블의 레코드가 존재하지 않으므로 삭제 됨
drop table parent ; -->?
drop table parent cascade constraint;
-->부모 테이블의 컬럼을 참조하는 모든 자식테이블의 foreign key제약조건을 삭제
select * from parent;
select * from child;
select constraint_name, constraint_type, r_constraint_name
from user_constraints
where table_name= 'CHILD';
테이블의 컬럼 타입 변경>>
select * from t_emp;
alter table t_emp
modify (hiredate number(6));-->error
==>호환가능하지 않은 타입은 에러, 컬럼 타입 변경 불가
==>해당 컬럼값이 전부 null일 경우 변경됨
update t_emp
set hiredate = null;
alter table t_emp
modify (hiredate number(6));
alter table t_emp
modify (sal varchar2(10)); --->?error
alter table t_emp
modify (job char(20));
-->char, varchar2 문자열에 해당하는 컬럼타입이므로 호환..데이터가 존재하더라도 변경됨
테이블의 컬럼만 삭제===========================
alter table t_emp
drop colum hiredate;
alter table t_emp
drop (sal);
==>컬럼은 한번에 한개씩 삭제하는것을 권장
desc t_emp
select * from t_emp;
테이블에 컬럼을 추가================================
desc t_dept
select * from t_dept;
alter table t_dept
add (manager number(5) );
desc t_dept
select * from t_dept;
==>테이블이 생성된 이후에 추가되는 컬럼은 마지막 컬럼으로 추가됨
==>컬럼 선언 순서를 변경할 수 없다.
==> 테이블이 이미 레코드가 존재하는 경우 새로 추가된 컬럼의 컬럼값은 null로 추가됨
alter table t_dept
add ( floor number(2) default 1 );
desc t_dept
select * from t_dept;
테이블의 컬럼이름 변경================================
alter table t_dept
rename colum manager to mgr_id;
desc t_dept
select * from t_dept;
truncate table t_dept;
desc t_dept
select * from t_dept;
==> 데이터만 모두 물리적으로 완전 삭제, 테이블의 구조는 남아 있음
==>delete보다 수행속도가 더 빠름
PL/SQL: BLOCK 구조 형태
익명 PL/SQL BLOCK : DB내에 저장되지 않으며, 변수 불가능, 파라미터 전달할 수 없으며, 결과 리턴 못함
DB 내에 컴파일 되어서 저장되는 PL/SQL BLOCK : 서브프로그램, STORED PROCEDURE, STORED FUNCTION, TRIGGER
논리적으로 연관된 프로시져, 펑션을 그룹핑 하는 것: Package
[Declare]
… 선언문; 변수, 상수, 예외, 커서…
Begin
… 실행문장; sql문, 제어문, 반복문, 할당문장, 커서처리문장
[exception]
… 예외처리문장;
End;
/
보기-> DBMS 출력
(예제 실행해보기)
declare
v_deptno number(2) := 50;
v_avg number(8,2) := 0;
begin
select avg(salary) into v_avg
from employees
where department_id = v_deptno;
dbms_output.put_line(v_deptno||'부서의 평균월급은 '||v_avg||'입니다.');
end;
/
결과>> 50부서의 평균월급은 3500입니다.
declare
v_empno employees.employee_id%type := 176;
v_emp employees%rowtype; -- 테이블의 레코드와 동일한 타입
begin
select * into v_emp
from employees
where employee_id = v_empno;
dbms_output.put_line(v_emp.employee_id || ', ' ||
v_emp.last_name || ', ' ||
v_emp.salary || ', ' || v_emp.job_id);
end;
/
결과 >> 176, Taylor, 8600, SA_REP
declare
v_num number(3)
V_NUM constant number(3) : = 100
à 상수, 반드시 초기값 할당해줘야 한다.
반복문
begin
if 조건 then 문장;
end if;
if 조건 then 문장;
else 문장;
end if;
if 조건1 then 문장1;
elsif 조건2 then 문장2;
..
else 문장n
end if;
accept v_num prompt '정수값 하나 입력하세요'
àscanf, scanner와 유사한 기능
accept v_num prompt '정수값 하나 입력하세요=>'
begin
--홀수 짝수 판별 출력
if mod(&v_num, 2)=0 then
dbms_output.put_line(&v_num ||' 짝수');
else
dbms_output.put_line(&v_num ||' 홀수');
end if;
end;
/
-- 학점 판별
accept v_num prompt '정수값 하나 입력하세요=>'
begin
if (&v_num > 89) then
dbms_output.put_line(&v_num ||'는 A 학점이다.');
elsif (&v_num > 79) then
dbms_output.put_line(&v_num ||'는 B 학점이다.');
elsif (&v_num > 69) then
dbms_output.put_line(&v_num ||'는 C 학점이다.');
elsif (&v_num > 59) then
dbms_output.put_line(&v_num ||'는 D 학점이다.');
else
dbms_output.put_line(&v_num ||'는 F 학점이다.');
end if;
end;
/
accept v_num prompt '정수값 하나 입력하세요=>'
declare
v_hakjum varchar2(10) ;
begin
v_hakjum := case when &v_num>= 90 then ' A'
when &v_num>= 80 then ' B'
when &v_num>= 70 then ' C'
when &v_num>= 60 then ' D'
else ' F' end;
dbms_output.put_line(&v_num ||v_hakjum);
end;
/
기본 테이블 생성 중요정리!!
create table new_ec_product(
Product_code varchar(10) constraint EC_PRODUCT_PK PRIMARY KEY, -- 제약조건 constraints c.name c.type
Product_name varchar(20) not null,
standard varchar(20),
Unit varchar(10),
Unit_Price number(7) not null,
Left_QTY number(5) not null,
Company varchar(20),
ImageName varchar(20),
Info varchar(50),
Detail_Info varchar(255));
-- 문제 1,2,3번 생성하기. 출력은 아래.
drop table new_ec_product;
alter table new_ec_product drop column Detail_Info;
-- 문제4번 detail_info 열 정보를 삭제한다.
alter table new_ec_product modify (Info varchar(40));
-- 문제5,6번 해당 열의 제약규정을 바꾼다.
-- alter table 테이블명 drop/add[column, constraints,..], modify [테이블 내용]
-- 외부키로 관계가 정의된 테이블의 기본키를 강제 삭제 : cascade
-- alter table new_ec_product drop primary key cascade;
select * from tab;
-- 전체 테이블 보여준다.
desc new_ec_product
-- 테이블 제약규정 확인하기
select * from new_ec_product;
-- 테이블 내용 확인하기
select constraint_name, constraint_type
from user_constraints
where table_name = 'NEW_EC_PRODUCT';
-- 제약조건 확인시에는 반드시 대문자로 확인해야한다.