Data integrity
-----------------
1. What?
(1) 실체 무결성 ( Entity Integrity)
1) 중복을 막음
2) PRIMARY KEY, UNIQUE
(2) 영역 무결성 ( Domain )
1) 범위
2) CHECK
(3) 참조 무결성 ( Reference)
1) 참조
2) FOREIGN KEY (외부에서 온 키)
2. 컬럼(필드) 의 특성
1) NN 속성
2) ND 속성
3) NC 속성
===============================================================================
Primary key
1) 하나의 테이블에서 단 한개만 사용 가능
2) 여러개의 필드에 설정 가능
------------------------------------------------------
DROP TABLE tbltest;
CREATE TABLE tbltest(
id number null,
name varchar2(10) null
):
중복된 데이터 입력 alter insert create delete drop
INSERT INTO tbltest VALUE (1, '홍길동');
INSERT INTO tbltest VALUE (1, '홍길동');
DELETE FROM tbltest;
ALTER TABLE tbltest ;
** DD(Data Dictionary)
desc user_constraints;
select constraint_name, table_name, status from user_constraints;
desc user_objects;
select object_name from user_objects;
기본키 제약 제거
alter table tbltest drop constraint pk_id;
alter table tbltest add age number null; //컬럼은 테이블의 구성이기 때문에 생략이 가능하다.
alter table tbltest modify name varchar2(20);
속성 제거
alter table tbltest drop column age;
다시 기본키 장착(두개의 필드를 묶어서 부여)
alter table tbltest add constraint pk_id_name primary key(id, name);
insert into tbltest values (1, '홍길동');
insert into tbltest values (1, '임꺽정');
insert into tbltest values (2, '홍길동');
insert into tbltest values (1. '임꺽정');
drop table tbltest;
select tname from tab;
show recyclebin;
flashback table tbltest to before drop;
purge recyclebin; // 휴지통 비우기
create table tbltest(
id number primary key,
name varchar2(10) null
);
select constraint_name, table_name, status from user_constraints;
create table tbltest(
id number constraint pk_id primary key,
name varchar2(10) null
);
create table tbltest(
id number,
name varchar2(10),
constraint pk_id_name primary key(id. name)
);
create table tbltest(
id number,
name varchar2(10) null,
constraint pk_id_name primary key(id)
);
NN속성 확인
INSERT INTO tbltest values(null, '홍길동');
INSERT INTO tbltest(name) values('홍길동');
2. UNIQUE
(1) 하나의 테이블에 여러 개를 설정 가능하다
DROP table tbltest;
create table tbltest(
id number(2) constraint uk_id UNIQUE,
name varchar2(10) constraint uk_name UNIQUE,
age number(2, 3),
gender char(1)
);
// 유니크는 기본키설정 여러개 가능하다.
insert into tbltest(id, name) values(1, '홍길동');
insert into tbltest(id, name) values(1, '임꺽정');
insert into tbltest(id, name) values(2, '홍길동');
insert into tbltest(id) values(2);
insert into tbltest(name) values('신돌석');
3. 중복을 지켜주는 도구 : SEQUENCE, sequence (자동 증가)
drop table tbltest;
create table tbltest(
id number(2),
name varchar2(10)
);
create sequence seq_id;
insert into tbltest values(seq_id.nextval, '홍길동');
insert into tbltest values(seq_id.nextval, '홍길동');
insert into tbltest values(seq_id.nextval, '홍길동');
insert into tbltest values(seq_id.nextval, '홍길동');
4. NULL 값을 지켜주는 도구 : DEFAULT
DROP TABLE tbltest;
CREATE TABLE tbltest(
id number(2)
name varchar2(10) default '무명씨'
age number default(0)
);
insert into tbltest(id) values ( seq_id . nextVal);
5. CHECK
DROP TABLE tbltest;
CREATE TABLE tbltest(
id number(2),
name varchar2(10),
age number,
city varchar2(10),
CONSTRAINT ck_city
CHECK ( city = '서울' or city ='경기' or city = '인천')
);
insert into tbltest values ( seq_id.nextval, '홍길동', 20, '서울');
insert into tbltest values ( seq_id.nextval, '임꺽정', 20, '제주');
ALTER 를 이용해서 age 에 나이를 10~60 세 까지만 입력받을 수 있도록 수정하라
ALTER TABLE tbltest ADD constraint ck_age check ( age between 10 and 60);
6. FOREIGN KEY
CREATE TABLE tblDept(
deptno char(4),
dname varchar2(10),
);
CREATE TABLE tblEmp(
empno number,
ename varchar2(10),
hiredate date,
deptno char(4)
);
insert into tbldept values('1110', '영업부');
insert into tbldept values('1111', '마케팅부');
insert into tblemp values (1, '홍길동', sysdate, '1111');
tlbDept 테이블의 deptno에 기본키 추가
ALTER TABLE tbldept ADD CONSTRAINT pk_deptno PRIMARY KEY ( deptno );
ALTER TABLE tblEmp
ADD CONSTRAINT fk_tbldept_tblemp_deptno FOREIGN KEY (deptno)
REFERENCES tbldept(deptno);
insert into tblemp values ( 2, '임꺽정', sysdate, '1113);
NN속성
UPDATE tbldept SET deptno = '1100' WHERE deptno='1110';
UPDATE tbldept SET deptno = '2222' WHERE deptno='1111';
참조가 있던 없던 강제로 진행할 때 - CASCADE
=======================================================================
***** VIEW 테이블
1. 가상테이블
2. 실제테이블 을 가지고 여러 관점에서 다양하게 사용할 수 있게 하는 개념
3. 장점
1) 사용자에게 필요한 필드만 제공
2) 보안성
4. 스키마 수정불가
5. 문법
CREATE [OR REPLACE] VIEW 이름 [{컬럼명...}]
AS SELECT 구문
6. 주의할점 - 조회만 한다면 문제없음
1) View 를 통해 입력되지 않은 컬럼에 대한 수정
*실습
------------
conn system/1111
GRANT create view TO scott;
CREATE OR REPLACE VIEW vw_emp_20
AS
SELECT empno, ename, deptno, job, sal from emp where deptno = 20;
select * from vw_emp_20;
insert into vw_emp_20(empno, ename) values(100,'test');
select object_name from user_objects;
-------------------------------------
CREATE OR REPLACE VIEW vw_emp_20
AS
SELECT ename, deptno from emp where deptno = 20;
insert into vw_emp_20 values('test1',30);
-----------------------------------------
CREATE OR REPLACE VIEW vw_emp_20
AS
SELECT ename, deptno, sal + nvl(comm,0) as total from emp where deptno = 20;
select * from vw_emp_20;
-------------------------------------------
CREATE OR REPLACE VIEW vw_emp_20
AS
SELECT ename, sal emp.deptno, dname, loc from emp
inner join dept on emp.deptno = dept.deptno and dept.deptno=20;
select * from vw_emp_20
UPDATE vw_emp_20
set sal = 100, loc = 'BOSTTON'
where ename = 'SMITH';
drop view vw_emp_20;
delete from emp where ename='test';
==============================================
*** Stored Procedure
1. 절차적인 구조
[DECLARE
선언부]
BEGIN
코드작성 ( 처리)
[EXCEPTION
예외처리]
END;
2. 문법
CREATE [OR REPLACE] PROCEDURE 이름 [(파라미터 , ...)]
IS
BEGIN
내용
END;
------------------------------------------------
**실습
1. 사번이 7788 인 사원의 급여를 3500 으로 수정하는 프로시저
CREATE OR REPLACE PROCEDURE sp_emp_sal
IS
BEGIN
UPDATE emp
SET sal = 3500
WHERE empno = 7788;
END;
/ - 프로시저 생성시에는 반드시 끝에 슬래쉬를 붙여준다
exec sp_emp_sal // execute pl/xql 처리가 완료되도록 해줌
show error; // 에러를 구체적으로 보여줌
exec sp_emp_sal(3000,7788)
//parameter p //() 매개변수
2. 1번예제를 개선하여 매개변수를 사용할 수 있도록 해보자.
CREATE OR REPLACE PROCEDURE sp_emp_sal
(p_sal in number, p_empno in number)
//(p_sal in emp.sal%type, p_empno in emp.empno%type) 해당 변수와 같은타입으로 써라
IS
BEGIN
UPDATE emp
SET sal = p_sal
WHERE empno = p_empno;
( if 조건식 then // 만약 if조건식이 참이라면
else
end if;)
if sql%notfound then
else
end if;
END;
/
CREATE OR REPLACE PROCEDURE sp_emp_sal
(p_sal in emp.sal%type, p_empno in emp.empno%type)
IS
BEGIN
UPDATE emp
SET sal = p_sal
WHERE empno = p_empno;
if sql%notfound then
dbms_output,put_line(p_empno|| ' 는 없는 사번임');
else
dbms_output,put_line(sql%rowcount|| ' 명이 처리됨');
end if;
END;
/
set serveroutput on
exec sp_emp_sal(3500,7788);
//pl/sql 처리가완료되었습니다.
set serveroutput on
exec sp_emp_sal(3500,7788);
//1명이 처리됨
4. 값을 리턴하는 경우
세금계산 프로시저 작성
특정한 수에 7% 의 세금을 계산하여 그 결과를 돌려주는 프로시저
CREATE OR REPLACE PROCEDURE usp_tax
(p_num in number,p_result out number)
// in 외부로부터 값을 받기위한 , out 외부로 값을 보내기위한
IS
BEGIN
p_result :=p_num * 0.07;
END;
/
var g_result number;
exec usp_tax(3500, :g_result); // p_num = 3500, p_result = g_result
print : g_result
5. 사원 등록 프로시저
사원의 이름, 업무, 직속상사, 급여 를 입력받는다.
부서번호는 직속상사의 부서번호와 같다.
커미션은 SALESMAN 일 경우 0, 그외에는 null 로 처리
CREATE OR REPLACE PROCEDURE usp_emp_insert
(p_ename in emp.ename%type.p_job in emp.job%type,
p_mgr in emp.ngr%type,p_sal in emp.sal%type)
IS
v_deptno emp. deptno%type;
v_comm emp. comm%type;
BEGIN
select deptno into v_deptno from emp where empno = p_mgr;
if then
p_job = 'SALESMAN' than
v_comm:= 0;
else
v_comm:= null;
end if;
insert into emp(ename, ename, job, mgr,
sal, deptno, comm, hiredate)
values (seq_empno.nextval, p_ename, p_job, p_mgr, p_sal,
v_deptno, v_comm, sysdate)
END;
/
exec usp_emp_insert('test1', 'SALESMAN', 7788, 1500);
