210413

정혜린·2021년 4월 13일

풀스텍 국비교육

목록 보기

26/84

제약조건(***)
- 테이블의 해당 컬럼에 잘못된 값이 입력/변경/삭제되는 것을 방지하기 위해
설정되는 조건(무결성 제약조건)
- 결함이 없는 데이터를 관리하기 위함
  
  1) 종류
  <1> PRIMARY KEY (기본키)
  - 하나의 테이블에 하나만 존재하며 각 행을 식별하기 위한 용도로 사용
  - NULL값을 허용하지 않고 중복값을 허용안함(NOT NULL + UNIQUE KEY)
    <2> FOREIGN KEY (외래키,참조키)
  - 부모테이블의 특정 컬럼(기본키)를 참조하는 컬럼
  - 부모테이블에 참조하는 값이 없으면 오류가 발생한다.
    <3> UNIQUE KEY(유일키)
  - 컬럼의 모든 값이 유일해야 하는 경우에 사용. 중복데이터를 허용하지 않음
  - NULL은 허용됨
    <4> CHECK 제약조건
  - 조건에 맞는 데이터만 입력되도록 조건을 부여하는 제약조건
    <5> NOT NULL 조건
  - NULL값을 허용하지 않는 컬럼에 설정
```
예1) 제약조건에 이름을 부여하지 않은 경우
```
    CREATE TABLE DEPT1
    (
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNAME VARCHAR2(15) DEFAULT '인사부',
    LOC CHAR(9) CHECK(LOC IN('서울', '부산'))
    );
INSERT INTO DEPT1 VALUES(1, '부서1', '서울'); -- 에러
->PK컬럼은 중복값을 가질 수 없음

INSERT INTO DEPT1(DEPTNO, LOC) VALUES(2, '부산'); -- DNAME은 DEFAULT값(인사부)가 저장됨

INSERT INTO DEPT1(DEPTNO, LOC) VALUES(3, '대구'); -- 체크제약조건dp 위배

##USER_CONSTRAINTS:사용자가 만든 제약조건에 대한 정보를 갖는 데이터 딕셔너리
DESC USER_CONSTRAINTS; -- USER_CONSTRAINTS 컬럼 구조보기
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;
```
예2) 제약조건에 이름을 부여하기 - 컬럼레벨과 테이블레벨이 있다.
```
--컬럼레벨
CREATE TABLE DEPT2
(
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT2_DEPTNO PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);
INSERT INTO DEPT2 VALUES(1, '부서1', '종로');
COMMIT;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;

--테이블레벨
CREATE TABLE EMP2
(
EMPNO NUMBER(4),
ENAME VARCHAR2(15) CONSTRAINT NNNAME NOT NULL,
DEPTNO NUMBER(2),
CONSTRAINT PK_EMP2_EMPNO PRIMARY KEY(EMPNO),
CONSTRAINT FK_EMP2_DEPTNO FOREIGN KEY(DEPTNO REFERENCES DEPT2
);
--> EMP2 테이블에 2명의 사원정보를 추가해보세요.
INSERT INTO EMP2 VALUES(100, '홍길동', 1);
INSERT INTO EMP2 VALUES(101, '홍순자', 2); -- 부모테이블에 2번부서가 존재하지 않으므로 오류!
INSERT INTO EMP2 VALUES(101, '홍순자', 1);
```
예3) 두 개이상의 컬럼이 PK로 설정된다면 테이블레벨로 설정해야 한다.
```
CREATE TABLE MYSTUDENT
(
SNUM NUMBER(5), -- 학번
SUBJECT VARCHAR2(10), -- 과목명
SCORE NUMBER(3), -- 점수
CONSTRAINT PK_STUDENT PRIMARY KEY(SNUM, SUBJECT) -- 두개의 컬럼을 PK로 지정
);
INSERT INTO MYSTUDENT VALUES(1, '영어', 100);
INSERT INTO MYSTUDENT VALUES(1, '수학', 100);
INSERT INTO MYSTUDENT VALUES(2, '영어', 100);
INSERT INTO MYSTUDENT VALUES(2, '영어', 60); -- (에러) 학생번호+과목명 컬럼에 중복데이터가 있으므로 PK제약조건 위배!
COMMIT;

예4)
CREATE TABLE DEPT3
(
DEPTNO NUMBER(3) CONSTRAINT PK_DEPT3 PRIMARY KEY,
DNAME VARCHAR2(10) NOT NULL,
LOC VARCHAR2(10),
CONSTRAINT UK_DEPT3 UNIQUE(DNAME),
CONSTRAINT CK_DEPT3 CHECK(LOC IN('서울', '부산'))
);
INSERT INTO DEPT3 VALUES(10, '부서1', '서울');
INSERT INTO DEPT3 VALUES(10, '부서1', '대구'); -- (에러) 체크제약조건 위배
INSERT INTO DEPT3 VALUES(11, '부서1', '서울'); -- (에러) 유니크제약조건 위배

2) 제약조건 삭제/추가
```
# 제약조건 삭제
- ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
예1) ALTER TABLE DEPT3 DROP CONSTRAINT PK_DEPT3;
# 제약조건 추가
- ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼명);
예2) ALTER TABLE DEPT3 ADD CONSTRAINT PK_DEPT3 PRIMARY KEY(DEPTNO);
```
시퀀스

연속되는 숫자값을 자동으로 증감시키는 일련번호를 발생시키는 객체
형식)
CREATE SEQUENCE 시퀀스명
[ INCREMENT BY N ][ START WITH N ]
[ MAXVALUE N| NOMAXVALUE N ][ MINVALUE N| NOMINVALUE N ]
[ CYCLE | NOCYCLE ][ CACHE | NOCACHE ]
삭제
DROP SEQUENCE 시퀀스명;
함수
nextval : 다음값 얻어오기
currval : 현재값 얻어오기

예1)
CREATE DEQUENCE MY_SEQ;
SELECT MY_SEQ.NEXTVAL FROM DUAL; -- 현재 발생된 값

예2)
CREATE TABLE BOARD
(
NUM NUMBER(5) PRIMARY KEY,
TITLE VARCHAR2(100),
CONTENT VARCHAR2(100)
);
CREATE SEQUENCE BOARD1_SEQ;
INSERT INTO BOARD VALUES(BOARD1_SEQ.NEXTVAL, '오라클', '테스트1');
INSERT INTO BOARD VALUES(BOARD1_SEQ.NEXTVAL, '자바', '테스트2');

-시퀀스 삭제
DROP SEQUENCE BOARD1_SEQ;

CREATE SEQUENCE MY_SEQ1
INCREMENT BY 2
START WITH !100;

SELECT MY_SEQ1.NEXTVAL FROM DUAL;

뷰(View)
- 뷰는 테이블에 대한 가상테이블로써 select로 조회된 데이터들의 가상테이블이다.
  
  1) 형식
  CREATE [OR REPLACE] VIEW 뷰이름
  AS
  서브쿼리;
  [ WITH CHECK OPTION ][ WITH READ ONLY ]
  
  2) 사용이유
  - 보안 설정
  - 복잡하고 자주 사용되는 질의를 뷰를 통해서 쉽게 사용하기 위해
예1)
CREATE VIEW EMP1
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP;

scott 사용자에게 뷰생성 권한 부여하기

CONN system/java1234
GRANT CREATE VIEW TO scott;

SELECT*FROM EMP1;

뷰에서 기본적으로 DML 작업이 가능하다.

INSERT INTO EMP1 VALUES(100, '김자바', 1000, 10);

예2)
#WITH READ ONLY => 뷰에서 DML 작업을 못함
CREATE OR REPLACE VIEW EMP1
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WITH READ ONLY;

INSERT INTO EMP1 VALUES(101, '김자바', 1000, 10); -- 에러

예3)
#WITH CHECK OPTION -> 뷰의 조건에 맞는 데이터만 DML이 가능
CREATE OR REPLACE VIEW VEMP
AS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO=10
WITH CHECK OPTION;

INSERT INTO VEMP VALUES(200, '김철수', 2000, 10);
INSERT INTO VEMP VALUES(201, '김영희', 2000, 20); -- 에러
==> 뷰의 조건(부서번호가 10번)이 맞지 않으므로 오류!

3) 뷰의 종류
<1> 단일뷰 : 하나의 테이블로 생성되는 뷰(기본적으로 DML작업이 됨)
<2> 복합뷰 : 두 개이상의 테이블로 생성되는 뷰(조인된 뷰).DML작업을 못함

4) 뷰 삭제하기
형식) DROP VIEW 뷰이름;
DROP VIEW VEMP;
SELECT VIEW_NAME FROM USER_VIEWS;

Q1) EMP테이블과 DEPT테이블을 이용해서 이름,직업,부서번호,부서명을 조회하는
뷰를 만들고 사용/삭제해 보세요.
CREATE VIEW VEMP
AS
SELECT ENAME, JOB.E.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;

SELECT*FROM VEMP;
SELECT VIEW_NAME FROM USER_VIEWS;

[2] PL/SQL

- 구조적인 SQL언어와 달리 SQL를 절차적인 방식에 의해 사용하도록 설계한 언어.
- PL/SQL의 단위는 블록이며 하나의 블록은 아래와 같은 세부분으로 나뉜다.

(1) 선언부 : 변수나 상수를 선언하는 부분 
(2) 실행부 : 실제 처리할 로직을 담당하는 부분
(3) 예외처리부 : 실행부에서 처리하던 중 발생하는 각종 오류에 대한 처리 부분

1) PL/SQL의 구조

   DECLARE            -- 선언부
    변수선언
   BEGIN              -- 실행부
    실행구문
   EXCEPTION          -- 예외처리
예외처리구문	
   END;
   /  -- PL/SQL실행

   예1) 
SET SERVEROUTPUT ON -- 출력모드 설정
DECLARE
	MSG VARCHAR2(20); -- 변수선언
BEGIN
	MSG:='안녕하세요';	
	DBMS_OUTPUT.PUT_LINE('메시지:'||MSG); -- DBMS_OUTPUT.PUT_LINE는 System.out.println 같은 역할
END;
/	

   예2)
   DECLARE
NUM1 NUMBER(5);
NUM2 NUMBER(5);
NUM3 NUMBER(5);
   BEGIN
NUM1:=&NUM1;
NUM2:=&NUM2;
NUM3:=NUM1+NUM2;
DBMS_OUTPUT.PUT_LINE(NUM1||'+'||NUM2||'='||NUM3);
  END;
  /

   2) IF ~ ELSE 문
       형식1)
IF 조건식 THEN
  실행문;
ELSE
  실행문;
END IF;

예1)
DECLARE
	NUM1 NUMBER(3);
	NUM2 NUMBER(3);
	NUM3 NUMBER(3);
BEGIN
	NUM1:=&NUM1;
	NUM2:=&NUM2;
	IF NUM1>NUM2 THEN
		NUM3:=NUm1;
	ELSE
		NUM3:=NUM2;
	END IF;
	DBMS_OUTPUT.PUT_LINE('큰 수'||NUM3);
END;
/
Enter value for num1: 1
old 6: NUM1:=&NUM1;
new 6: NUM1:=2;
Enter value for num2: 2
old 7: NUM2:=&NUM2;
new 7: NUM2:=2;
큰 수 2

Q1) 임의의 정수를 입력 받아 짝수인지 홀수인지 출력해보세요. (나머지값 MOD연산자 사용)
DECLARE
	NUM1 NUMBER(3);
BEGIN
	NUM1:=&NUM1;
	IF NUM1 MOD 2=0 THEN
		DBMS_OUTPUT.PUT_LINE(NUM1||' 은 짝수');
	ELSE
		DBMS_OUTPUT.PUT_LINE(NUM1||' 은 홀수');
	END IF;
END;
/

Q2) 두 정수를 입력 받아 평균이 80 이상이면 합격 아니면 불합격 출력하기
DECLARE
	NUM1 NUMBER(3);
	NUM2 NUMBER(3);
	AVER NUMBER(5,2);
BEGIN
	NUM1:=&NUM1;
	NUM2:=&NUM2;
	AVER:=(NUM1+NUM2)/2;
	DBMS_OUTPUT.PUT_LINE('평균:'||AVER);
	IF AVER>=80 THEN
		DBMS_OUTPUT.PUT_LINE('합격');
	ELSE
		DBMS_OUTPUT.PUT_LINE('불합격');
	END IF;
END;
/

      형식2)
IF 조건식 THEN 
  실행문;
ELSIF 조건식 THEN
  실행문;
ELSIF 조건식 THEN
  실행문;
  ..
END IF;

  3) FOR문
    
-형식
FOR 변수 IN 초기값..마지막값 LOOP
	반복수행할 문장;

END LOOP;

예1) 1부터 100까지 합 구하기
DECLARE
	TOT NUMBER(5):=0;
BEGIN
	FOR I IN 1..100 LOOP
		TOT:=TOT+I;
		DBMS_OUTPUT.PUT(I||' ');
	END LOOP;
	DMBS_OUTPUT.put_line(' ');
	dbms_output.put_line('1부터 100까지 합:'||TOT);
END;
/

        Q1) 1부터 100까지 수 중 3의 배수출력하고 3의 배수합 구해서 출력하기
DECLARE
	TOT NUMBER(5):=0;
BEGIN
	FOR I IN 1..100 LOOP
	IF I MOD 3=0 THEN
		TOT:=TOT+I;
		DBMS_OUTPUT.PUT(I||' ');
	END IF;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('1부터 100까지 3의 배수 합:'||TOT);
END;
/

Q2) 단 입력받아 구구단 출력하기
DECLARE
	NUM1 NUMBER(5):=&NUM1;
BEGIN
	DBMS_OUTPUT.PUT_LINE('['||NUM1||'단]');
	FOR I IN 1..9 LOOP
		DBMS_OUTPUT.PUT_LINE(NUM1||'*'||I||'='||NUM1*I||' ');
	END LOOP;
END:
/

Q3) 구구단 출력하기(2단부터 9단까지)
BEGIN
	FOR I IN 2..9 LOOP
		DBMS_OUTPUT.PUT('['||I||'단]');
		FOR J IN 1..9 LOOP
			DBMS_OUTPUT.PUT(I||'*'||J||'='||I*J||' ');
		END LOOP;
		DBMS_OUTPUT.PUT_LINE('');
	END LOOP;
END;

   - EXIT : FOR문을 강제로 빠져나가기
    
   예2) 정수입력받아 소수 판별하기
DECLARE
	NUM1 NUMBER(5);
BEGIN
	NUM1:=&NUM1;
	IF NUM1=2 THEN
		DBMS_OUTPUT.PUT_LINE(NUM1||'은 소수입니다.');
	END IF;
	FOR I IN 2..NUM1-1 LOOP
		IF NUM1 MOD I=0 THEN
			DBMS_OUTPUT.PUT_LINE(NUM1||'은 소수가 아닙니다.');
			EXIT:
		END IF;
		IF I =NUM1-1 THEN
			DBMS_OUTPUT.PUT_LINE(NUM1||'은 소수입니다.');
			EXIT;
		END IF;
	END LOOP;
END;
/

정혜린

'ㅅ'

이전 포스트

210412

다음 포스트

210413

풀스텍 국비교육

scott 사용자에게 뷰생성 권한 부여하기

뷰에서 기본적으로 DML 작업이 가능하다.

210412

210414

0개의 댓글

관련 채용 정보