2022-02-07 TCL / Object / PL_SQL

GGAE99·2022년 2월 8일
sql
0

진도

목록 보기
18/43

오늘은 TCL / OBJECT / PL_SQL에 대해 알아볼 것 이다.

TCL

TCL에 들어가기 전에 먼저 TRANSCATION을 먼저 알아보자.

TRANSCATION (트랜잭션)

  • 한꺼번에 수행되어야 할 최소의 작업 단위
  • 하나의 트랜잭션으로 이루어진 작업들은 반드시 한꺼번에 완료가 되어야 하며,
    그렇지 않은 경우 한꺼번에 취소 되어야 한다.

이게 TRANSACTION이고, TCL은 TRANSACTION 제어 언어이다.
TCL은 TRANSACTION 작업을 적용, 취소 할 때 사용하는 SQL이다.
어떤 것으로 어떻게 제어하는지 그 종류를 알아보자.

TCL 종류
1. COMMIT

  • 트랜잭션 작업이 정상 완료 되면 변경 내용을 저장 -> 모든 SAVEPOINT는 삭제
  1. ROLLBACK
  • 트랜잭션 작엄을 모두 취소하고 최근 COMMIT 시점으로 이동
  1. SAVEPOINT
  • 현재 트랜잭션 작업 시점의 이름을 지정 / 하나의 트랜잭션 안에서 구역을 나눌 수 있음
  1. ROLLBACK TO
  • 트랜잭션 작업을 취소하고 SAVEPOINT 시점으로 이동

COMMIT

-- COMMIT
CREATE TABLE USER_TBL(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20)    UNIQUE,
    USER_PW VARCHAR2(20) NOT NULL
);

INSERT INTO USER_TBL VALUES(1,'USER01','1234');
INSERT INTO USER_TBL VALUES(2,'USER02','1234');
INSERT INTO USER_TBL VALUES(3,'USER03','1234');
COMMIT; 
--TCL (COMMIT, ROLLBACK 시점은 TABLE데이터에 변화가 발생했을때 -> INSERT, UPDATE, DELETE 쿼리 등장 후)

이렇게 테이블을 완성하고, 데이터의 변화를 준 후, COMMIT해준다.
ROLLBACK도 마찬가지다.
데이터의 변화를 준 뒤, COMMIT하지 않고 ROLLBACK을 하면 가장 최근에 COMMIT했던 부분으로 데이터가 복구된다. COMMIT이후에 추가한 데이터는 사라지게된다.

ROLLBACK

-- ROLLBACK
CREATE TABLE USER_TBL(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20)    UNIQUE,
    USER_PW VARCHAR2(20) NOT NULL
);

INSERT INTO USER_TBL VALUES(1,'USER01','1234');
COMMIT;
INSERT INTO USER_TBL VALUES(2,'USER02','1234');
INSERT INTO USER_TBL VALUES(3,'USER03','1234');
ROLLBACK;

이러면 두번째 세번째 데이터를 집어넣고 다시 그 데이터를 지우겠다는 뜻이다. 없었던 일로.

SAVEPOINT / ROLLBACK TO

-- SAVEPOINT
INSERT INTO USER_TBL VALUES (4, ‘test4’, ‘pass4’); --데이터 추가
SAVEPOINT sp1 									   --작업 임시저장
INSERT INTO USER_TBL VALUES (5, ‘test5’, ‘pass5’); -- 데이터 추가
SELECT * FROM USERTBL; 							   -- 테이블 확인
ROLLBACK to sp1; 								   --sp1 시점 적용
SELECT * FROM USERTBL							   -- 테이블 확인

이러면 아까 만들어뒀던 테이블에 데이터를 추가하는걸 sp1이라는 SAVEPOINT로 임시저장 한다는 뜻 이다. 그리고 ROLLBACK을 이용해서 최근 COMMIT 시점이 아닌 sp1의 시점으로 돌아갈수 있다.

여기서 ROLLBACK TO sp1을 안하고 그냥 ROLLBACK하면, 그냥 가장 최근 COMMIT시점으로 간다.
왜냐하면, SAVEPOINT는 작업 증간상태로 이동만 했을 뿐 트랜잭션 작업이 종료된 것은 아니기 때문이다.

이 다음으로 살펴볼 것은 Object이다.

Object

OBJECT는 다양한 기능을 사용하게 해준다. 그 종류가 여러가지 있다.

Object 종류
1. VIEW
2. SEQUENCE
3. INDEX
4. SYNONYM

VIEW 부터 차근차근 살펴보자.

VIEW

VIEW

  • SELECT 쿼리의 실행 결과를 화면에 저장한 논리적인 가상 테이블
  • 테이블과 다르게 실질적으로 데이터를 저장하고 있진 않지만, 사용자는 테이블을 사용하는 것과 동일하게 사용 가능
  • 물리적인 실제 테이블과의 링크 개념
  • 테이블 복사와의 가장 큰 차이점은 VIEW에서 데이터 수정 시 실제 테이블에 수정데이터 반영
    (테이블 복사 시에는 원본테이블에 영향 없음 / 얕은 복사 같은 느낌)

일단 사용자가 VIEW를 사용하기 위해, 해당하는 사용자에 권한을 부여해주어야한다.
이때, ADMIN계정에서 권한을 부여한다.

GRANT CREATE VIEW TO '사용자 계정 이름';

ADMIN에서 사용자 계정의 이름을 입력해 GRANT해준다.
그 후 VIEW를 설정해준다.

CREATE VIEW 'VIEW 이름'
AS
SELECT EMP_NAME,EMAIL,SALARY  -- 넣을 컬럼 이름
FROM EMPLOYEE;				-- 참조할 테이블

이와 같은 형태로 작성한다.
UPDATE를 이용해 VIEW의 컬럼 값을 조정해주면, 원본 테이블 값도 바뀐다.
원본 테이블 값을 UPDATE하면 똑같이 VIEW 값도 바뀐다.

SEQUENCE

SEQUENCE는 순차적으로 정수 값을 자동으로 생성하는 객체로, 자동 번호 발생기의 역할을 한다.

CREATE SEQUENCE SEQ_ID		-- 새로 생성할 SEQUENCE 이름
START WITH 100				-- SEQUENCE가 시작할 숫자의 위치
INCREMENT BY 1				-- SEQUENCE가 올라가는 정도 값
MAXVALUE 150				-- SEQUENCR가 올라가는 최대 값
NOCYCLE						-- 순환하지 않음
;

이게 SEQUENCE의 기본 생성 방법이다. 여기서 INCREMENT, MAXVALUE. NOCYCLE만 다시 보자.

  • INCREMENT BY 숫자 -> 다음 값에대한 증감치 인데, 음수로 사용하면 감소한다. (DEFAULT 1)
  • MAXVALUE 숫자 / NOMAXVALUE -> 최대값을 설정해 주는 것, NOMAXVALUE는 DEFAULT값
    최대 (10^27-1) 까지 범위 지정 가능
  • MINIVALUE 숫자 / NOMINVALUE -> 최솟값을 설정해 주는 것, NOMINVALUE는 DEFAULT값
    최소 (-10^26) 까지 범위 지정 가능
  • CYCLE / NOCYCLE -> CYCLE은 시퀀스 최댓값 도달 시 START WITH 값으로 돌아감.
    NOCYCLE은 순환 안함

이제 어떻게 이 SEQUENCE값을 동작하게 하냐를 알아보자.
이게 반복문처럼 자동으로 해주는게 아니다.

  • NEXTVAL
    NEXTVAL 은 SEQUENCE의 값을 설정해준만큼 올려주는 역할을 한다.
    하지만 처음 NEXTVAL을 초기 설정값을 출력해준다.
  • CURRVAL
    현재 머물러있는 SEQUENCE의 값을 출력해준다.
    NEXTVAL을 이용해 초기 설정값을 출력한 이후에 사용해야한다. 처음부터 사용 못한다.
INSERT INTO KH_MEMBER VALUES(SEQ_ID.NEXTVAL,'USER01','1234',SEQ_ID.NEXTVAL);

이렇게 사용한다.

SEQUENCE를 기본값으로 생성하는 방법은 다음과 같다.

CREATE SEQUENCE ID_SEQ;

이렇게 SEQUENCE를 생성하면 시작값이 1이고 1씩 증가하는 SEQUENCE가 만들어진다.

INDEX

  • INDEX란
    SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 오라클 객체로
    내부구조는 B*트리 형식으로 구성
  • 장점
    검색속도가 빨라지고 시스템에 걸리는 부하를 줄여서 시스템 전체 성능 향상
  • 단점
    인덱스를 위한 추가 저장공간 필요
    인덱스 생성 시간 필요
    데이터 변경작업이 자주 일어나는 경우 오히려 성능저하
  • 효율적인 사용
    전체 데이터 중 10~15% 이내의 데이터를 검색하는 경우
    두 개 이상의 컬럼이 WHERE절이나 JOIN조건으로 자주 사용되는 경우
    한번 입력된 데이터의 변경이 자주 일어나지 않는 경우
    한 테이블에 저장된 데이터 용량이 매우 클 경우

INDEX는 위와 같이 정리할 수 있다.

CREATE INDEX EMP_IDX ON EMPLOYEE(EMP_NAME,EMP_NO,HIRE_DATE);

위와 같은 방식으로 INDEX를 생성해주면 된다.

SYNONYM

SYNONYM
사용자가 다른 사용자의 객체를 참조할 때 [사용자ID].[테이블명]으로 표기한다.
길게 표현되는 것을 동의어(SYNONYM)으로 설정하고 간단하게 사용 가능하다.

SYNONYM또한 ADMIN 계정에서 사용권한을 주어야한다.

GRANT CREATE SYNONYM TO ABC;
-- ABC 계정에 SYNONYM 권한을 부여

과 같은 식으로 권한을 줄 수 있다.
SYNONYM은 공개 동의어 / 비공개 동의어 2가지 종류가 있다.

  • 공개 동의어
    권한을 주는 사용자가 정한 동의어
    모든 사용자가 사용할 수 있음
  • 비공개 동의어
    객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어
    해당 사용자만 사용할 수 있음

코드로 표현하면 다음과 같다.

-- 표현식
CREATE SYNONYM 동의어이름 FOR 테이블명;
-- 공개 동의어
CREATE PUBLIC SYNONYM EMP FOR EMPLOYEE;
-- 비공개 동의어
CREATE SYNONYM EMP FOR EMPLOYEE;

-- 사용
SELECT * FROM EMP;

이렇게 PUBLIC을 앞에 붙여주면 공개 동의어이다.

이렇게 Object는 마무리해본다.
다음은 PL/SQL이다.

PL/SQL

  • PL/SQL
    Procedural Language extension to SQL의 약자
    오라클 자체에 내장되어 있는 절차적 언어
    SQL의 단점을 보완하여 SQL 문장내에서 변수의 정의, 조건처리, 반복처리 등을 지원
    SET SERVEROUTPUT ON; 을 통해 출력하는 내용을 보여주도록 설정해야함 (DEFAULT OFF)
SET SERVEROUTPUT ON;      -- PL/SQL을 이용해서 출력하는 내용을 화면에 보여주도록 설정

DECLARE    	  --선언부는 선택사항
	[선언부]		--변수나 상수를 선언
BEGIN			--실행부는 필수사항
	[실행부]		-- 제어문,반복문,함수 정의 등 로직 기술
EXCEPTION		--예외처리부는 선택사항
	[예외처리부]	--예외사항 발생 시 해결하기 위한 문장 기술
END;			--블록종료
/				--PL/SQL 종료 및 실행

위와 같은 방식으로 진행된다.

선언부에 넣을 변수에 종류부터 알아보자.

PL/SQL 변수의 종류
1. 일반(스칼라 변수)
기존 SQL 자료형과 유사 값을 대임(:=)하고 변경하여 사용이 가능
2. 상수
일반변수와 유사하나 CONSTANT 키워드가 자료형 앞에 붙고 선언 시 값을 할당해주어야 하며 변경이 불가능함
3. %TYPE
이전에 선언된 다른 변수 또는 테이블의 컬럼 자료형에 맞추어 선언하기 위한 변수
4. %ROWTYPE
%TYPE과 유사하게 참조할 테이블의 컬럼데이터 타입을 자동으로 가져오나 1개의 컬럼이 아니라 여러 개의 컬럼 값을 자동으로 가져옴
5. 레코드 / RECORD
%ROWTYPE이 참조할 테이블의 컬럼 데이터 타입을 자동으로 가져오는 반면, 레코드는 직접적으로 컬럼타입을 지정

1. 일반 스칼라 변수

-- 변수 선언 후 출력
DECLARE
    TEST_NO NUMBER;     -- 숫자형 변수 TEST_NO 선언
    TEST_STR VARCHAR2(30);   -- 문자형 변수 TEST_STR 선언
BEGIN
    TEST_NO :=100;          -- TEST_NO 변수에 100 대입
    TEST_STR :='HELLO';     -- TEST_STR 변수에 'HELLO' 대입
    DBMS_OUTPUT.PUT_LINE('TEST_STR : '|| TEST_STR);	--PL/SQL의 출력문
    DBMS_OUTPUT.PUT_LINE('TEST_NO : '|| TEST_NO);
END;

2. 상수

DECLARE
    TEST_NAME VARCHAR2(20);            		   --스칼라변수(일반변수) // 변환 가능
    TEST_STR CONSTANT VARCHAR2(20):= '상수';    --상수형 변수 선언후 초기화 // 고정함수
BEGIN
    TEST_NAME := '테스트1';		-- 스칼라변수 변환
    DBMS_OUTPUT.PUT_LINE('TEST_NAME :'|| TEST_NAME);
    DBMS_OUTPUT.PUT_LINE('TEST_STR :'|| TEST_STR);
    TEST_NAME :='TEST2';		-- 스칼라변수 변환2
     TEST_STR :='TEST3';		-- 요건 오류뜨니까 지우면 댐 / 상수라서 변환이 안됨
    DBMS_OUTPUT.PUT_LINE('TEST_NAME :'|| TEST_NAME);
END;
/

3. %TYPE 변수

DECLARE
   --  TEST1   VARCHAR2(20);		--요건 원래 변수 선언하던 스칼라 방식
   --  TEST2   NUMBER;
   TEST1   EMPLOYEE.EMP_NAME%TYPE; --EMPLOYEE테이블의 EMP_NAME과 동일한 자료형
   TEST2   EMPLOYEE.EMAIL%TYPE;    --EMPLOYEE테이블의 EMAIL과 동일한 자료형
BEGIN
    SELECT EMP_NAME,EMAIL
    INTO TEST1, TEST2
    FROM EMPLOYEE WHERE EMP_ID = '&사번';		-- '&~~' 은 ~~를 입력받고싶을때 사용함
    DBMS_OUTPUT.PUT_LINE('이름 :'|| TEST1);
    DBMS_OUTPUT.PUT_LINE('이메일 :'|| TEST2);
END;
/

4. %ROWTYPE

DECLARE
    TEST1       EMPLOYEE%ROWTYPE;       --EMPLOYEE테이블의 모들 컬럼 자료형을 한번에 선언
BEGIN
    SELECT EMP_ID, EMP_NAME, EMAIL, SALARY
    INTO TEST1.EMP_ID, TEST1.EMP_NAME, TEST1.EMAIL, TEST1.SALARY
    FROM EMPLOYEE WHERE EMP_ID ='&사번';
    DBMS_OUTPUT.PUT_LINE('사번 : '||TEST1.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : '||TEST1.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('이메일 : '||TEST1.EMAIL);
    DBMS_OUTPUT.PUT_LINE('급여 : '||TEST1.SALARY);
    DBMS_OUTPUT.PUT_LINE('테스트 : '||TEST1.EMP_NO);
END;
/

5. RECORD

DECLARE
    -- RECORD타입 변수 생성
    TYPE MY_RECORD IS RECORD(
        MY_NAME EMPLOYEE.EMP_NAME%TYPE,
        MY_EMAIL EMPLOYEE.EMAIL%TYPE,
        MY_SALARY EMPLOYEE.SALARY%TYPE
    );
    -- 생성된 RECORD타입 변수를 선언
    MY_INFO     MY_RECORD;
BEGIN
    SELECT EMP_NAME, EMAIL, SALARY
    INTO MY_INFO.MY_NAME, MY_INFO.MY_EMAIL, MY_INFO.MY_SALARY
    FROM EMPLOYEE WHERE EMP_ID='&사번';
    DBMS_OUTPUT.PUT_LINE('이름 : '||MY_INFO.MY_NAME);
    DBMS_OUTPUT.PUT_LINE('이메일 : '||MY_INFO.MY_EMAIL);
    DBMS_OUTPUT.PUT_LINE('급여 : '||MY_INFO.MY_SALARY);
END;
------------------------------------------------------------
만약 RECORD타입 변수의 개수가 SELECT의 개수와 같다면 다음과 같이 설정도 가능
BEGIN
    SELECT EMP_NAME, EMAIL, SALARY
    INTO MY_INFO
    -- INTO MY_INFO.MY_NAME, MY_INFO.MY_EMAIL, MY_INFO.MY_SALARY
    -- 이렇게 길게 안해도 괜찮음
/

이렇게 해서 오늘은 PL/SQL과 Object, TCL에 대해 알아봤다.
필자는 미루다가 지금 이 글을 마쳐서 바로 다음글을 쓰러 갈거다... 조금 지친다...
다음 글도 PL/SQL에 관한 내용이 나온다...!
다음 글을 쓰러 가보겠다! 안뇽!

profile
GGAE99
이전 포스트

2022-02-04 DDL2

다음 포스트

2022-02-08 PL-SQL2 / TRIGGER / ERD

0개의 댓글