설예의 블로그: 151008 - integrity / KEY / view / procedure

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, '홍길동');

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, '홍길동');

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

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

SELECT ename, deptno from emp where deptno = 20;

insert into vw_emp_20 values('test1',30);

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

CREATE OR REPLACE VIEW vw_emp_20

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

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 이름 [(파라미터 , ...)]

BEGIN

내용

END;

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

**실습

1. 사번이 7788 인 사원의 급여를 3500 으로 수정하는 프로시저

CREATE OR REPLACE PROCEDURE sp_emp_sal

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) 해당 변수와 같은타입으로 써라

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)

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 외부로 값을 보내기위한

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)

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);

설예의 블로그

2015년 10월 30일 금요일

151008 - integrity / KEY / view / procedure

댓글 없음:

댓글 쓰기