[Oracle] 제약조건(INTEGRITY), Object(view, rownum, Sequence), PL/SQL

JH·2023년 4월 10일
0

Oracle

목록 보기
4/6

1. TIL

A. Integrity (강제 제약 설정)

1. 제약 사항 (제약 조건)

PRIMARY KEY(PK) : UNIQUE(중복 불허) + NOT NULL(반드시 값이 있어야 함)

FOREIGN(FK) : 외래키 (종속 참조)

제약조건 선언 위치

  • 컬럼 레벨 단위 : 컬럼선언 라인에 제약조건 설정

  • 테이블 레벨 단위 : 모든 컬럼 선언 직후 별도로 제약조건 설정

  • 테이블 복제시 제약조건의 반영이 되지 않음
    tip : WHERE 1 = 0; 을 사용하면 테이블의 구조만 복사

오라클 TABLE

user_constraints : 제약조건 정보 보유 table, 개발자가 table의 데이터값 직접 수정 불가

SELECT * FROM user_constraints;

이미 존재하는 TABLE의 제약조건 수정

  • 추가 : ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼명);
  • 삭제 : ALTER TABLE 테이블명 CASCADE CONSTRAINT;
  • 활성화 : ALTER TABLE 테이블명 DISABLE CONSTRAINT;
  • 비활성화 : ALTER TABLE 테이블명 ENABLE CONSTRAINT;

EX) PK 제약 조건 명시

CREATE TABLE EMP02(
	empno NUMBER(4) CONSTRAINT emp02_empno_nn NOT NULL,
	ename VARCHAR(10)
); 
-- INSERT INTO EMP02 (ename) VALUES ('multi');
-- 제약 조건 때문에 ORA-01400 에러 발생
-- empno NUMBER(4) UNIQUE : UNIQUE를 사용하면 오라클 엔진이 고유한 값을 지정해줌

EX) PK 컬럼 레벨

CREATE TABLE EMP02(
	empno NUMBER(4) CONSTRAINT emp02_pk PRIMARY KEY,
	ename VARCHAR(10)
);
INSERT INTO EMP02 VALUES (1, 'multi');

EX) PK 테이블 레벨

CREATE TABLE EMP02(
	empno NUMBER(4),
	ename VARCHAR(10)
);
ALTER TABLE EMP02
ADD CONSTRAINT emp02_pk PRIMARY KEY (emp02);

EX) FK 컬럼 레벨

CREATE TABLE EMP02(
	empno NUMBER(4) PRIMARY KEY,
	ename VARCHAR(10) NOT NULL,
	deptno NUMBER(4) CONSTRAINT emp02_deptno_fk REFERENCES DEPT(deptno)
);

EX) FK 테이블 레벨

CREATE TABLE EMP02(
	empno NUMBER(4) PRIMARY KEY,
	ename VARCHAR(10) NOT NULL,
	CONSTRAINT emp02_deptno_fk FOREIGN KEY (deptno) REFERENCES DEPT(deptno)
);

EX) 존재하는 테이블에 제약조건 추가

ALTER TABLE DEPT01
ADD CONSTRAINT dept01_pk PRIMARY KEY (deptno);

ALTER TABLE EMP01
ADD CONSTRAINT emp01_pk PRIMARY KEY (empno);

ALTER TABLE EMP01
ADD CONSTRAINT emp01_deptno_fk FOREIGN KEY (deptno) REFERENCES DEPT(deptno);

EX) 제약 조건 삭제

DELETE FROM DEPT01
WHERE deptno = 10;
-- 자식의 참조 대상이 되기 떄문에 에러가 발생함

EX) 참조되는 컬럼 데이터라 하더라도 삭제

ALTER TABLE DEPT01
ADD CONSTRAINT dept01_pk PRIMARY KEY (deptno);

ALTER TABLE EMP01
ADD CONSTRAINT emp01_pk PRIMARY KEY (empno);

ALTER TABLE EMP01
ADD CONSTRAINT emp01_deptno_fk FOREIGN KEY (deptno) REFERENCES DEPT01(deptno) ON DELETE CASCADE;
-- DELETE 뒤에 SET NULL 을 사용하면 부모가 삭제 되더라도 자식은 데이터 유지됨(NULL)

DELETE FROM DEPT01
WHERE deptno = 10;

EX) 데이터의 유효 유무 검증 제약조건

CREATE TABLE EMP01(
	ename VARCHAR2(10),
	gender char(1) CONSTRAINT emp01_gender_ck CHECK(gender IN ('M', 'F')));
INSERT INTO EMP01 VALUES ('lee', 'T');

Optional : DEFAULT를 사용하면 insert시에 데이터를 생략해도 설정값이 기본값이 됨

CREATE TABLE EMP01(grade CHAR(1) DEFAULT 'S');

B. Object

1. view

실제 존재 하는 table 기반으로 가상의 논리적인 table
단일 view, 복합 view, 인라인 view가 있음
SELECT * FROM user_views; : 구조 확인

--
CREATE VIEW DEPT01_V AS SELECT * FROM DEPT01;
SELECT * FROM DEPT01_V;
INSERT INTO DEPT01_V VALUES(50, 'DEV', 'R.O.K');
SELECT * FROM DEPT01_V;
SELECT * FROM DEPT01;
-- 원본인 DEPT01 테이블까지 변경되므로 유의해야함

2. rownum

검색시 검색된 데이터 순서대로 rownum값 자동 반영(1부터 시작)

SELECT ename, sal
FROM (SELECT ename, sal
        FROM EMP
        ORDER BY sal DESC)
WHERE rownum < 4;

3. sequence

순차적인 순법을 자동으로 반영할수 있는 매우 유용한 기술

  • 중복 불가, 고유한 값이므로 PK로 사용 가능
CREATE SEQUENCE seq_test_no1
START WITH 10 
INCREMENT BY 2
MAXVALUE 40;

INSERT INTO seq_test VALUES (seq_test_no1.nextval, 10);
INSERT INTO seq_test VALUES (seq_test_no1.nextval, 16);

4. index

DB 자체적으로 빠른 검색 기능 부여
초보자가 잘못 사용하면 DB가 망가질 수 있음
CREATE INDEX idx_emp01_empno ON emp01(empno);

C. PL/SQL

Oracle DB만의 프로그래밍 개발 방법

장점: 여러 번 사용하는 sql 다수 문장을 하나로 묶을 수 있음
DB 자체의 언어로 컴파일 후 사용하기 때문에 실행 속도가 향상될 수 있음

필수 셋팅 : set serveroutput on;

1. 구조 (예외처리 포함)

declare
	no integer;
begin
	no := 10;
    no := no/0;
    dbms_output.put_line('결과1 : ' || no);
    
	exception 
		WHEN others THEN
			dbms_output.put_line('예외 처리!');
            
    dbms_output.put_line('결과2 : ');
    dbms_output.put_line('결과3 : ');
end;
/

2. 중첩 Block

declare
	v_global VARCHAR2(10) := 'global';
begin
	declare
		v_local VARCHAR2(10) := 'local';
	begin
		dbms_output.put_line('g - : ' || v_global);
		dbms_output.put_line('l - : ' || v_local);
	end;
		dbms_output.put_line('g2 - : ' || v_global);
end;
/

3. %type, %rowtype

%type : table의 컬럼 타입 활용
%rowtype : record(row)의 모든 컬럼 타입 활용

declare
	v_rows emp01%rowtype;
begin
	SELECT *
		INTO v_rows
	FROM EMP01
	WHERE empno = 7369;

	dbms_output.put_line(v_rows.empno || ' ' || v_rows.ename);
end;
/
declare
	v_rows emp%rowtype;
begin
	SELECT *
		INTO v_rows
	FROM EMP01
	WHERE empno = 7369;

	INSERT INTO EMP02 VALUES v_rows;
	COMMIT; --INSERT는 DML이므로 COMMIT을 해야함
end;
/

3. 조건식

단일 조건식 : if then ~ end if;
다중 조건식 : if then ~ elsif then ~ end if;

정적 데이터 조건식

declare
	v_emp01 emp01%rowtype;
	total_sal NUMBER(8, 2);
begin
	SELECT sal, comm
		INTO v_emp01.sal, v_emp01.comm
	FROM emp01
	WHERE ename = 'SMITH';

	if v_emp01.comm is null then
		v_emp01.comm := 0;
	end if;

	total_sal := (v_emp01.sal * 12) + v_emp01.comm;
	dbms_output.put_line(total_sal);
end;
/

동적(가변) 데이터 조건식 (&v)

declare
	v_empno NUMBER(4) := &empno;
	v_deptno emp.deptno%type;
	v_dname VARCHAR2(10);
begin
	SELECT deptno
		INTO v_deptno
	FROM EMP
	WHERE empno = v_empno;

	if v_deptno = 10 then 
		v_dname := 'ACCOUNTING';
	elsif v_deptno = 20 then
		v_dname := 'RESEARCH';
	else
		v_dname := 'NONE';
	end if;
    
    dbms_output.put_line(v_empno || '의 부서명은 ' || v_dname);
end;
/

2. 에러

CASCADE, START WITH을 사용하면 실행은 되지만 올바르지 못한 문법이라고 밑줄이 그어짐 아직 해결하지 못한 문제

값이 NULL이 되는 상황, 종속 관계에서 수정 되는 상황에서 ORA -관련 예외가 자주 발생함

3. 보완 해야 할 것

%type, %rowtype을 활용한 조건식은 조금 어렵게 느껴졌으므로 보완해야함

맨 앞의 제약조건은 DB에서 아주 중요하므로 다시보고 또 보면서 익혀야함

객체의 view ~ index는 Optional 한 부분이므로 이해만 하기

4. 느낀점

DB는 그저 저장하고 불러오고 수정하는 것만 할 것이라고 생각했다.
하지만 DB도 파면 팔수록 다양하고 깊어지는 것 같다.

방향성을 잃지 않게 Java와 DB의 연결을 생각하면서 그 얼개를 잘 따라가야겠다.

profile
JH
잘해볼게요
이전 포스트

[Oracle] 서브쿼리(Sub Query), DDL, DML

다음 포스트

[Oracle] Loop, JDBC

0개의 댓글