oracle
다중입력, rollback관련 연습
말없는채플린씨
2009. 10. 8. 12:37
제약을 이용한 테이블 생성
CREATE TABLE STUDENT(
STUD_ID CHAR(5) CONSTRAINTS STUDENT_STUD_ID_PK PRIMARY KEY
CONSTRAINTS STUDENT_STUD_ID_CK CHECK(LENGTH(STUD_ID)=5),
STUD_NAME VARCHAR2(10) CONSTRAINTS STUDENT_STUD_NAME_NN NOT NULL
CONSTRAINTS STUDENT_STUD_NAME_CK CHECK(LENGTH(STUD_NAME)>=3 AND LENGTH(S
TUD_NAME)<=7);
SELECT TABLE_NAME,OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TO_CHAR(LAST_CHANG,'YY/MM/DD') LC
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN('STUDENT1','EMP');
SQL> SELECT TABLE_NAME,OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TO_CHAR(LAST_CHANGE,'YY/MM/DD')
2 LC
3 FROM USER_CONSTRAINTS
4 WHERE TABLE_NAME IN('STUDENT1','EMP');
제약조건을 검사
1 SELECT TABLE_NAME,OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TO_CHAR(LAST_CHANGE,'YY/MM/DD')
2 LC, STATUS
3 FROM USER_CONSTRAINTS
4* WHERE TABLE_NAME IN('STUDENT1','EMP')
/////STATUS는 제약조건을 바꿀 수 있는지를 확인하는 여부이다
2개를 하나의 프라이머리 키로 만들어 주어봐
엔티티 안에는 하나의 프라이머리키를 만들잖아
하지만 이렇게 2개의 프라이머리키를 내쪽에서 포린키로 하는거야
2
SQL> CREATE TABLE SCORE(
2 STUD_ID CHAR(5) CONSTRAINTS SCORE_STUD_ID_NN NOT NULL,
3 SUB_ID NUMBER(3) CONSTRAINTS SCORE_SUB_ID_NN NOT NULL,//선언하면서 포린키로 선언할 순 없어
SQL> CREATE TABLE SCORE(
2 STUD_ID CHAR(5) CONSTRAINTS SCORE_STUD_ID_NN NOT NULL,
3 SUB_ID NUMBER(3) CONSTRAINTS SCORE_SUB_ID_NN NOT NULL,
4 SCORE NUMBER(3) CONSTRAINTS SCORE_SORE_CK CHECK(SCORE>=0 AND SCORE<=100),
5 GRADE CHAR(1) CONSTRAINTS SCORE_GRADE_CK CHECK(GRADE IN('A','B','C','D','F')),
6 //여기서 프라이머리 키를 주는거야
SQL> CREATE TABLE SCORE(
2 STUD_ID CHAR(5) CONSTRAINTS SCORE_STUD_ID_NN NOT NULL,
3 SUB_ID NUMBER(3) CONSTRAINTS SCORE_SUB_ID_NN NOT NULL,
4 SCORE NUMBER(3) CONSTRAINTS SCORE_SORE_CK CHECK(SCORE>=0 AND SCORE<=100),
5 GRADE CHAR(1) CONSTRAINTS SCORE_GRADE_CK CHECK(GRADE IN('A','B','C','D','F')),
6 CONSTRAINTS SCORE_PK PRIMARY KEY(STUD_ID,SUB_ID),//이런식으로 2개를 한꺼번에 프라이머리로 하는거야
7 CONSTRAINTS SCORE_FK1 //여기서는 포린키를 넣어주는거지
1* INSERT INTO SCORE VALUES('12346',11,80,'B')
SQL> /
1 개의 행이 만들어졌습니다.
SQL> ED
file afiedt.buf(이)가 기록되었습니다
1* INSERT INTO SCORE VALUES('99123',20,80,'B')
SQL> /
INSERT INTO SCORE VALUES('99123',20,80,'B')
*
1행에 오류:
ORA-02291: 무결성 제약조건(SCOTT.SCORE_FK2)이 위배되었습니다- 부모 키가 없습니다
INSERT로 데이터를 넣을때 STUDENT1에 해당하는 사람과
SUBJECT에 해당되는 사람이 같이 들어가야 SCORE에 들어가게 되어있다
다른 사람과 없는 학번 그리고 없는 과목 코드를 넣으면 안된다
외래키가 없으면 래퍼런스 할 수 없기때문에 FOREIN KEY가 NOT NULL로 변경
STUDENT1, SUBJECT가 바로 부모키가 되는것이다
그래서 SCORE의 태이블에서 위 테이블에 없는 사람이나
과목을 입력하면 안되는것이다
SQL> SELECT ST.STUD_NAME,SB.SUB_NAME,SC.SCORE,SC.GRADE
2 FROM STUDENT1 ST,SUBJECT SB,SCORE SC
3 WHERE ST.STUD_ID = SC.STUD_ID AND SB.SUB_ID = SC.SUB_ID;
STUD_NAME SUB_NAME SCORE G
---------- ---------- ---------- -
윤여준 JAVA 90 A
신민아 FLEX 80 B
김시향 ORCLE 70 C
이병헌 JSP 70 C
//컬럼 FORMAT때문에 이렇게 된다
//조인하여 새로운 테이블을 만든거야 SCORE만을 출력하면 어떤학생이
어떤 과목에서 어떤 점수를 받는지 모르잖아
//조인할때 SCORE 태이블을 보면 학생번호와 과목번호를 가지고 있으니깐
조인할때 이것을 참조해서 하면 되는거지
제약을 삭제하거나 수정을하는것
NOT NULL할때는 ㅣ수정을 할 수 있다
SQL> ALTER TABLE STUDENT1 ADD AGE NUMBER(3);
테이블이 변경되었습니다.
SQL> DESC STUDENT1;
이름 널? 유형
----------------------------------------------------- -------- ------------------------------------
STUD_ID NOT NULL CHAR(5)
STUD_NAME NOT NULL VARCHAR2(10)
AGE NUMBER(3)
//ALTER TABLE을 이용하여 삽입
SQL> ALTER TABLE STUDENT1 ADD CONSTRAINTS STUDENT1_AGE_CK CHECK(AGE>20 AND AGE<=50);
테이블이 변경되었습니다.
//제약사항을 빠트리고 안쓴경우 이렇게 제약을 다시 하는거다
1* ALTER TABLE STUDENT1 DROP CONSTRAINTS STUDENT1_AGE_CK
SQL> /
테이블이 변경되었습니다.
//CK를 주었을때 제약조건을 지워줄때 이렇게 된다
SQL> ALTER TABLE STUDENT1 MODIFY AGE NUMBER(3) CONSTRAINTS STUDENT1_AGE_NN NOT NULL;
테이블이 변경되었습니다.
//이렇게 태이블에 제약조건을 추가시킬때(특히 NOT NULL추가시)
MODIFY를 해주면된다
이때 만약 STUDENT1에 데이터가 들어가 있으면 바뀌지 않는다
NOT NULL의 키를 추가해서 넣어주었는데 기존의 데이터는
그걸 무시하고 들어가있는 상태이기 때문이다
SQL> ALTER TABLE STUDENT1 DROP COLUMN GENDER;
테이블이 변경되었습니다.
//테이블의 컬럼을 삭제하는 방법이다
SQL> RENAME STUDENT1 TO SOS;
테이블 이름이 변경되었습니다.
//테이블 명을 변경하는것이다
DESC STUDENT1을 해도 검색이 안되고 SOS로 해야 된다
ROLLBACK 이 가능한것은 DELETE
ROLLBACK 이 불가능한것은 TRUNCATE 이다
SQL> DELETE FROM PROF;
4 행이 삭제되었습니다.
SQL> SELECT * FROM PROF;
선택된 레코드가 없습니다.
SQL> ROLLBACK;
롤백이 완료되었습니다.
SQL> SELECT * FROM PROF;
PROFNUM PROFNAME SUBJECT HAK
-------------------- -------------------- -------------------- --------------------
201 신민아 MAHAK C_001
202 김태희 MECHHAK C_002
203 이연희 JUNSANHAK C_004
204 이효리 AIRSAHAK C_003
//DELETE는 ROLLBACK이 된다
SQL> TRUNCATE TABLE PROF;
테이블이 잘렸습니다.
SQL> SELECT * FROM PROF;
선택된 레코드가 없습니다.
SQL> ROLLBACK;
롤백이 완료되었습니다.
SQL> SELECT * FROM PROF;
선택된 레코드가 없습니다.
//TRUNCATE를 사용하면 ROLLBACK이 되지 않는다
메모리를 아주 삭제하기 때문에
SQL> DROP TABLE PROF;
테이블이 삭제되었습니다.
안에 들어간 컬럼을 삭제하는것이 아니라
아주 테이블을 없애주는것..
이것도 ROLLBACK안된다
데이터 조작어
CREATE DROP TRUNCATE는 논리적인 트랜젝션이 아니다
그래서 ROLLBACK이 안된다
NULL과 관련된 함수
NVL, NVL2, CORLESS, IS NULL참 ,IS NOT NULL아니면
SQL> CREATE TABLE EMP1(
2 EMPNO NUMBER(4) PRIMARY KEY,
3 ENAME VARCHAR2(10) NOT NULL,
4 JOB VARCHAR2(9));
테이블이 생성되었습니다.
SQL> ED
file afiedt.buf(이)가 기록되었습니다
1 CREATE TABLE EMP2(
2 EMPNO NUMBER(4) PRIMARY KEY,
3 ENAME VARCHAR2(10) NOT NULL,
4* JOB VARCHAR2(9))
SQL> /
테이블이 생성되었습니다.
SQL> INSERT ALL
2 INTO EMP1 VALUES(EMPNO,ENAME,JOB)
3 INTO EMP2 VALUES(EMPNO,ENAME,JOB)
4 SELECT EMPNO,ENAME,JOB
5 FROM EMP
6 WHERE SAL>=3000;
6 개의 행이 만들어졌습니다.
SQL> SELECT * FROM EMP1;
EMPNO ENAME JOB
---------- ---------- ---------
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
SQL> SELECT * FROM EMP2;
EMPNO ENAME JOB
---------- ---------- ---------
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
CREATE를 해서 2개의 테이블을 만들고
EMP에서 연봉이 3000이상인 들을 이 2개의
테이블에 넣어주는 것이다
1 INSERT ALL
2 WHEN SAL>3000 THEN
3 INTO EMP1 VALUES(EMPNO,ENAME,JOB,COMM,SAL)
4 WHEN SAL>1000 THEN
5 INTO EMP2 VALUES(EMPNO,ENAME,JOB,SAL)
6 SELECT EMPNO,ENAME,JOB,COMM,SAL
7* FROM EMP
SQL> /
13 개의 행이 만들어졌습니다.
SQL> SELECT * FROM EMP1;
EMPNO ENAME JOB DEPTNO SAL
---------- ---------- --------- ---------- ----------
7839 KING PRESIDENT 5000
SQL> SELECT * FROM EMP2;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
12 개의 행이 선택되었습니다.
//EMP1에는 COMM이 있는것이 들어가는것이고
//EMP2에는 COMM이 없고 조건에 의해서 1000이상 3000이하의
값만 들어간다
즉 조건을 만들어주는것이다
SQL> INSERT FIRST
2 WHEN SAL>3000 THEN
3 INTO EMP1 VALUES(EMPNO,ENAME,JOB,SAL)
4 WHEN SAL>1000 THEN
5 INTO EMP2 VALUES(EMPNO,ENAME,JOB,SAL)
6 SELECT EMPNO,ENAME,JOB,SAL
7 FROM EMP;
12 개의 행이 만들어졌습니다.
SQL> SELECT * FROM EMP1;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
SQL> SELECT * FROM EMP2;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
11 개의 행이 선택되었습니다.
//FIRST 할 경우 EMP1에 첫번째 조건에 의해서 데이터가
들어가고
EMP2에 들어갈 경우에는 EMP1에 들어간 데이터값은 제외되고
EMP2에 들어가기 때문에 11개의 행이 들어가게 되는것이다
SQL> INSERT ALL
2 WHEN SAL>3000 THEN
3 INTO EMP1 VALUES(EMPNO,ENAME,JOB,SAL)
4 WHEN SAL>1000 THEN
5 INTO EMP2 VALUES(EMPNO,ENAME,JOB,SAL)
6 SELECT EMPNO,ENAME,JOB,SAL
7 FROM EMP;
13 개의 행이 만들어졌습니다.
SQL> SELECT * FROM EMP1;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
SQL> SELECT * FROM EMP2;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
12 개의 행이 선택되었습니다.
//ALL했을경우 EMP1에 첫번째 조건에 맞는 사람이 들어가면
EMP2에서도 그 조건에 해당되는 사람이 또 들어가는것
1 INSERT ALL
2 INTO EMP3(EMPNO,ENAME,JOB) VALUES(EMPNO,ENAME,JOB)
3 INTO EMP3(EMPNO,ENAME,SAL) VALUES(EMPNO,ENAME,SAL)
4 INTO EMP3(EMPNO,ENAME,MGR) VALUES(EMPNO,ENAME,MGR)
5 SELECT EMPNO,ENAME,JOB,MGR,SAL
6 FROM EMP
7* WHERE SAL>2000
SQL> /
18 개의 행이 만들어졌습니다.
SQL> SELECT * FROM EMP3;
EMPNO ENAME JOB MGR SAL
---------- ---------- --------- ---------- ----------
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
7566 JONES 7839
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7902 FORD 7566
18 개의 행이 선택되었습니다.
하나의 테이블에 여러가지를 가져온다
즉 분석하기 위해서..
하나의 행을.. 계속 나눈다는 의미라고 하는데..
난 잘 몰것다..