21-12-15 데이터베이스 오라클 시퀀스 DML

 

시퀀스 

 

 

1) 시퀀스의 현재 값과 다음 값

 

SELECT 시퀀스이름.CURRVAL, 시퀀스이름.NEXTVAL FROM DUAL;

-> 실행하면 시퀀스를 호출해서 가지고 오기 때문에 시퀀스번호 3번까지 정보가 있다고 하면 CURVAL 값은 4가되고 또 한번 실행하면 CURVAL 값은 5가 된다. 

 

 

2) 시퀀스의 수정 

 

ALTER SEQUENCE 시퀀스이름 수정할내용 
ALTER SEQUENCE 시퀀스이름 MAXVALUE 최댓값;

 

시퀀스를 최대 14까지 증가하도록 수정

 

 

3) 시퀀스의 삭제

 

DROP SEQUENCE 시퀀스이름;

 

 

4) 시퀀스 재생성

 

CREATE SEQUENCE 시퀀스이름 START WITH 시작값 INCREMENT BY 증가량;

 

CURRENTVAL, NEXTVAL을 사용한 뒤 시작 값을 그 다음 값으로 조절해 이어서 생성될 수 있도록 한다 

 

 

 

DML (DATA MANAGEMENT LANGUAGE)

 

테이블에 레코드를 조작(추가, 수정, 삭제, 조회)하기 위한 명령어 

INSERT, UPDATE, DLELTE, SELECT가 DML에 속한다.

 

 

INSERT INTO

 

1) 샘플 테이블 추가

 

CREATE TABLE EXAM01(
	DEPNO NUMBER(2),
	DANME VARCHAR2(14),
	LOC VARCHAR2(14)
)

 

 

2) 레코드 추가 

 

INSERT INTO 테이블이름 (필드명1, 필드명2, 필드명3 ...) VALUES (값1, 값2, 값3, ...)

 

필드명과 값은 필요한 만큼 입력한다 생성된 컬럼 전체에 입력할 경우에는 필드명을 따로 입력하지 않아도 된다.  이 경우에는 필드의 모든 컬럼의 값을 빠짐없이 순서대로 나열해야한다.

 

INSERT INTO EXAM01 (DEPNO, DNAME, LOC) VALUES (10, 'ACCOUNT', 'NEWYORK');
INSERT INTO EXAM01 VALUES(30, 'SALES', 'CHICHAGO');
INSERT INTO EXAM01(DEPNO, DNAME) VALUES (20, 'MARKETING');
INSERT INTO EXAM01 VALUES (40, 'OPERATION', NULL);

 

 

컬럼의 값을 비워두고 싶다면 비워두고 싶은 컬럼명을 기재하지 않거나 NULL로 지정한다.

 

 

예시 

 

-MEMBERLIST

INSERT INTO MEMBERLIST 
VALUES (MEMBER_SEQ.NEXTVAL,'추신수','010-1234-4567', '84/07/07',240,'20/10/01','M', 28);
INSERT INTO MEMBERLIST 
VALUES (MEMBER_SEQ.NEXTVAL,'류현진','010-7891-2463', '87/03/25',300,'20/10/01','M', 35);
INSERT INTO MEMBERLIST 
VALUES (MEMBER_SEQ.NEXTVAL,'손흥민','010-7891-2463', '99/03/25',100,'20/10/01','M', 25);
INSERT INTO MEMBERLIST 
VALUES (MEMBER_SEQ.NEXTVAL,'이청용','010-7891-2463', '86/02/04',270,'20/10/01','M', 36);
INSERT INTO MEMBERLIST 
VALUES (MEMBER_SEQ.NEXTVAL,'이영표','010-7891-2463', '82/03/16',140,'20/10/01','M', 31);
INSERT INTO MEMBERLIST 
VALUES (MEMBER_SEQ.NEXTVAL,'최지만','010-2345-7321', '83/04/14',340,'20/10/01','F', 29);

SELECT * FROM MEMBERLIST;

 

 

시퀀스의 CURRNUM을 호출 할 때 생성된 4,5번은 사용되지 못하고 바로 6번으로 이어진다.

 

 

BOOKLIST

 

INSERT INTO BOOKLIST(NUM, SUBJECT, MAKEYEAR, INPRICE, RENTPRICE, GRADE) 
VALUES (BOOK_SEQ.NEXTVAL, '좀비', 2020, 12000, 2500, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '일곱해의 마지막', 2020, 12150, 2000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '공생 멸종 진화', 2105, 1, 2000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '악의', 2008, 14000, 1500, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '거미여인의 키스', 2000, 12150, 1000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '리버보이', 2020, 12150, 2000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '미루기의 천재들', 2007, 9000, 1000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '자살가게', 2007, 9000, 1000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '향수', 2020, 14800, 3000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '안나라수마나라', 2011, 11500, 1500, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '그리고 아무도 없었다', 2002, 10000, 1000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '침팬지 폴리틱스', 2004, 18000, 1000, 'ALL');
INSERT INTO BOOKLIST VALUES (BOOK_SEQ.NEXTVAL, '철학의 위안', 2014, 15000, 2000, 'ALL');

 

 

 

RENTLIST

INSERT INTO RENTLIST VALUES ('2021/12/01', RENT_SEQ.NEXTVAL, 14, 3, 100);
INSERT INTO RENTLIST VALUES ('2021/12/01', RENT_SEQ.NEXTVAL, 15, 1, 0);
INSERT INTO RENTLIST VALUES ('2021/12/02', RENT_SEQ.NEXTVAL, 21, 6, 100);
INSERT INTO RENTLIST VALUES ('2021/12/03', RENT_SEQ.NEXTVAL, 22, 7, 200);
INSERT INTO RENTLIST VALUES ('2021/12/04', RENT_SEQ.NEXTVAL, 20, 2, 100);
INSERT INTO RENTLIST VALUES ('2021/12/04', RENT_SEQ.NEXTVAL, 16, 4, 100);
INSERT INTO RENTLIST VALUES ('2021/12/04', RENT_SEQ.NEXTVAL, 17, 5, 100);
INSERT INTO RENTLIST VALUES ('2021/12/05', RENT_SEQ.NEXTVAL, 19, 10, 100);
INSERT INTO RENTLIST VALUES ('2021/12/05', RENT_SEQ.NEXTVAL, 15, 3, 100);
INSERT INTO RENTLIST VALUES ('2021/12/05', RENT_SEQ.NEXTVAL, 18, 8, 200);

 

RENTLIST는 MEMBERLIST와 BOOKLIST의 NUM을 참조하고 있으므로 두 테이블에 없는 NUM 값을 입력하면 오류가 발생한다.

 

오류 때문에 수정하다보니 IDX값이 좀 ,,,

 

 

※만약 value too large for column 이라는 에러가 나온다면 컬럼의 크기에 비해 데이터가 더 큰 경우로 modify를 이용해 컬럼의 크기를 키우거나 데이터의 길이를 줄여야한다.

 

 

UPDATE SET

 

데이터의 변경 수정

 

UPDATE 테이블명 SET 변경내용 WHERE 검색조건

 

명령문에 WHERE절은 생략이 가능하고 생략한 경우 모든 레코드가 수정된다. 

WHERE문은 검색조건을 설명하는 구문으로 필드명(비교-관계연산자) 조건값으로 이루어진다. 자바에서 IF문의 조건문과 같은 형식이다.

WHERE AGE >= 29 :  전체 레코드 중 AGE가 29 이상인 레코드를 대상으로 함

WHERE 조건문에서 어떤 문자 혹은 문자열을 포함하는 자료를 찾고 싶다면 LIKE 함수를 이용한다 . 

 

UPDATE EMP SET JOB="MANAGER" WHERE ENAME LIKE 'J%';

 

-> J로 시작하는 사원의 JOB을 'MANAGER'로 변경

J% : J로 시작

%J% : 문자열 중 J를 포함

%J : J로 끝남

_J% : 문자열의 2번째 글짜가 J임 (=> 원하는 위치에 검색할 문자가 오게끔 _를 늘린다 ['__J%'는 3번째에 J가 오는 문자열 검색])

 

 

※이때 % 를 와일드카드 문자라고 한다.

 

 

예시

 

UPDATE EXAM01 SET DEPNO=30;
UPDATE EXAM01 SET DEPNO=10 WHERE DNAME='ACCOUNT';
UPDATE EXAM01 SET DEPNO=20 WHERE DNAME='SALES'; 
UPDATE EXAM01 SET DEPNO=30 WHERE DNAME='OPERATION'; 
UPDATE EXAM01 SET DEPNO=40 WHERE DNAME='MARKETING'; 
UPDATE EXAM01 SET LOC='BOSTON' WHERE DEPNO=30; 
UPDATE EXAM01 SET LOC='LA' WHERE DEPNO='40';

 

수정 전

수정 후

 

※ 명령어들은 대소문자 구분하지 않더라도 컬럼명, 데이터값 등은 대소문자를 구분하므로 주의할 것

-> UPDATE 의 경우에는 컬럼명을 잘못 썻을 때 에러는 나지 않고 값이 변경되지 않는다

 

 

예시2

 

UPDATE BOOKLIST SET GRADE=18 WHERE SUBJECT='악의';
UPDATE BOOKLIST SET INPRICE=14000 WHERE SUBJECT LIKE '공%';
UPDATE BOOKLIST SET INPRICE=14000 WHERE SUBJECT LIKE '%화';

 

 

 

한번에 여러개의 컬럼을 수정하고 싶다면 SET 바꿀정보1, 바꿀정보2 처럼 콤마로 이어서 작성한다.

 

UPDATE BOOKLIST SET RENTPRICE=RENTPRICE*1.1, SUBJECT = SUBJECT || ' 20+' WHERE GRADE = '18';

 

 

 

※ " || " 는 SQL에서의 이어붙이기 기호이다.

 

 

 

DELETE FROM 

 

데이터의 삭제

 

DELETE FROM 테이블명 WHERE 조건 	-- 조건부 삭제
DELETE FROM 테이블 명 		-- 전체 삭제

 

조건은 UPDATE문의 조건문과 같은 식으로 작성하면 된다.

 

만약 다른 테이블이 참조하고 있는 컬럼의 데이터를 삭제하려고면 참조 무결성 규칙이 위반되어 에러가 발생한다.

(ORA-02292: ~~~ - child record found)

(A 테이블의 1번 데이터를 B테이블이 사용하고 있고 2번데이터는 사용하고 있지 않다면 1번 데이터를 삭제하려고할 때만 오류가 발생하고 2번 데이터는 정상적으로 삭제된다.)

 

-> 해결 방안

외래키 제약조건을 삭제후 다시 생성한다. 다시 생성시에 옵션을 추가해 참조되는 값이 삭제되면 참조하는 값도 같이 삭제되게 구성한다 (A테이블의 1번 데이터를 삭제하면 B 테이블에 1번 데이터를 사용하고 있는 ROW도 함께 삭제)

 

ALTER TABLE RENTLIST DROP CONSTRAINT RENT_FK1; --삭제 
ALTER TABLE RENTLIST ADD CONSTRAINT RENT_FK1 FOREIGN KEY(BOOKNUM)
REFERENCES BOOKLIST(NUM) ON DELETE CASCADE; --재생성

 

ON DELETE CASCADE : 기본테이블(BOOKLIST)의 데이터가 삭제되면 참조하는 테이블(RENTLIST) 데이터도 함께 삭제하는 옵션 *****참조되는 값의 삭제가 아니라 수정은 적용되지 않는다*****

MySQL에서는 ON UPDATE CASCADE 사용이 가능하나 오라클에서는 사용할 수 없다. 

값이 수정될 때 트리거 설정을 통해 값이 함께 변경되도록 할 수 있다. 

 

 

SELECT

데이터의검색 

 

 

* 사용자가 관리하고 있는 테이블 목록 검색 

 

SELECT * FROM TAB;	 --단순검색
SELECT * FROM TABS;	 --상세검색

 

SELECT * TAB 결과 

SELECT * TABS 결과. 캡쳐보다 더 많은 정보들이 나온다 

 

 

* CMD 창에서 테이블 정보 확인하기

 

DESC DEPT;
DESC MEMBERLIST; 
DESC RENTLIST;

 

cmd창에서 sqlplus 입력후 DB 아이디 비밀번호로 로그인 한후 위 코드를 입력하면 아래와 같이 테이블의  결과를 볼 수 있다.

 

 

 

SELECT를 테이블 상세 검색으로 사용할 때는 SELECT 와 FROM 사이에 조회하고자 하는 컬럼 이름들을 , 로 구분지어 지목한다. " * "을 쓰면 모든 컬럼이 검색 대상이 되고 연산식(ex : SAL * 1.1)도 들어갈 수 있다. 

 

SELELCT NUM, SUBJECT, MAKEYEAR FROM BOOKLIST

 

-> 여러 컬럼을 연결해서 작성했을 때 

 

뒤에 WHERE절을 이용해 조건에 맞는 행만 골라 낼 수도 있다. 

 

여러 조건을 붙여서 검색할 때는 콤마가 아닌 AND로 연결한다 

 

SELECT 찾을컬럼(여러개라면 콤마로 연결) FROM 테이블명 WHERE 조건1 AND 조건2 AND 조건3 ... ;

 

ex)

 

보너스 포인트가 250점 이상이고 82년 이후에 출생한 회원

 

SELECT * FROM MEMBERLIST WHERE BPOINT>=250 AND BIRTH>='1982/01/01';

 

 

 

 

*검색 결과의 별칭 AS

 

검색된 결과의 속성명을 지정할 때 사용하는 명령어이다

별칭이 될 이름은 그냥 작성하거나 " " 큰따옴표 안에 작성하며 ' ' 작은 따옴표는 허용하지 않는다.

 

SELECT EMPNO||'-'||ENAME AS EMP_INFO FROM EMP 
SELECT EMPNO AS "사원번호", ENAME AS 사원성명 FROM EMP

 

첫번째 줄은 EMPNO-ENAME (ex : 20210101-홍길동) 의 결과를 EMP_INFO라는 컬럼명을 가지도록 출력, 

두번째 줄은 EMPNO와 ENAME을 검색해 각각 사원번호, 사원성명 이라는 컬럼명으로 출력한다.

 

첫번째 줄 결과

 

두번째 줄 결과

 

 

검색 결과의 중복 제거

 

DISTINCT

 

SELECT DISTINCT 컬럼명 FROM 테이블명