[PL/SQL] View, Sequence, Cursor, with check option, with read only
2024.07.22 - [오라클] - [PL/SQL] 기본 구조
[PL/SQL] 기본 구조
기본 구조DECLARE 선언 부분(옵션) - 변수, 상수, cursor와 사용자 정의 exception등 선언BEGIN 실행 부분(필수) - 처리할 명령문들 절차적으로 기술 - sql, 반복문, 조건문 EXCEPTION 예외 처리 부분(옵션) - 오
chantleman.tistory.com
1. View (가상 테이블)
- 하나 이상의 테이블에서 데이터를 조회하는 방법을 정의하는 쿼리 결과를 가리킴
📌 VIEW를 사용하면 좋은 상황
- 차트로 데이터 변동을 보여줄 때
- 복잡한 쿼리를 반복적으로 작성할 필요 없이, 이미 정의된 VIEW를 사용하여 쉽게 조회하고 시각화 가능
- VIEW는 실시간 데이터를 반영하므로 최신 데이터를 바탕으로 변동 사항을 차트로 표현할 수 있음
- 보안 목적
- 특정 컬럼만 보여주고 나머지 데이터는 감추어야 할 때 유용
- 임시 데이터 저장
- 테이블로 보관할 필요는 없지만 자주 참조해야 하는 데이터를 잠시 저장할 때 사용
✅ VIEW 생성 방법
create or replace view 뷰이름(컬럼명1, 컬럼명2, ...)
as
select 조회할 컬럼명1, 컬럼명2, ...
from 원본 테이블
where 조건(뷰 정의 조건);
🔄 VIEW 데이터 변경
- VIEW 데이터를 변경하면 원본 테이블의 데이터도 변경됨
- 반대로 원본 테이블이 변경되면 VIEW의 데이터도 변경됨
🔧 VIEW 옵션
1️⃣ WITH CHECK OPTION
- VIEW에서 데이터 수정 시, 해당 데이터가 VIEW의 정의 조건을 만족해야 함
- 특정 조건을 위반하는 데이터를 삽입하거나 수정하는 것을 방지
만약 뷰가 특정 조건을 기반으로 데이터를 필터링한다면, WITH CHECK OPTION을 사용하여 그 조건을 위반하는 데이터를 삽입하거나 수정할 수 없게 만들 수 있음
예를 들어 9시~17시까지 특정 기능을 사용 못하게 막아놓고 싶다면 with check option을 사용하면 된당
2️⃣ WITH READ ONLY
- VIEW를 읽기 전용 모드로 설정하여 데이터 수정 불가
- 사용자가 실수로 데이터를 변경하는 것을 방지하고 싶을 때 유용
CREATE OR REPLACE VIEW v_mem01
AS
SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE mem_mileage >= 3000
WITH READ ONLY;
📌 이 두 옵션은 view에 적용되는 것이므로 view 데이터를 수정할때는 제한이 있지만 원본 테이블에는 아무런 제한이 없음
2. SEQUENCE (연속된 숫자 자동 생성)
- 자동으로 증가하는 숫자를 생성하는 객체
✅ SEQUENCE 생성
CREATE SEQUENCE seq_lprod
START WITH 10;
- start with n
📌 SEQUENCE 사용법
INSERT INTO lprod(lprod_id, lprod_gu, lprod_nm)
VALUES (seq_lprod.NEXTVAL, 'P501', '농산물');
처음 시퀀스가 생성되고 나면
- NEXTVAL : 다음 값 가져오기 ( 시퀀스명.nextval → 다음 값 )
- CURRVAL : 현재 값 가져오기 ( 시퀀스명.currval → 현재 값 )
- 시퀀스는 캐시에 만들어져 성능을 향상시킴
3. CURSOR (커서)
- 쿼리 결과를 저장하는 메모리 공간을 가리키는 포인터
📌 CURSOR가 필요한 이유
- SELECT문이 여러 개의 행을 반환하면 일반 변수로 처리 불가능
- 커서를 사용하면 여러 개의 데이터를 한 번에 처리 가능
✅ CURSOR 기본 구조
DECLARE
CURSOR 커서명 IS SELECT문;
BEGIN
OPEN 커서명;
FETCH 커서명 INTO 변수명;
CLOSE 커서명;
END;- declare - sql 영역 생성
- open - 결과 행 집합 식별
- fetch - 현재 행을 변수에 로드
- close - 결과 행 집합 해제
📌 CURSOR 예제
ACCEPT P_DID PROMPT '부서코드(xx) : '
DECLARE
L_EID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_ENAME VARCHAR2(100);
L_HDATE DATE;
L_SALARY NUMBER := 0;
CURSOR CUR_EMP(B_DID EMPLOYEES.DEPARTMENT_ID%TYPE) IS
SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = B_DID;
BEGIN
OPEN CUR_EMP(&P_DID);
LOOP
FETCH CUR_EMP INTO L_EID, L_ENAME, L_HDATE, L_SALARY;
EXIT WHEN CUR_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('사원번호 : ' || L_EID);
DBMS_OUTPUT.PUT_LINE('사원명 : ' || L_ENAME);
END LOOP;
CLOSE CUR_EMP;
END;
4. PL/SQL (오라클 프로그래밍 언어)
- 오라클에서 제공하는 프로그래밍 언어로, 절차적 로직을 구현할 수 있음
✅ PL/SQL 기본 구조
DECLARE
변수 선언;
BEGIN
실행할 SQL 문;
EXCEPTION
예외 처리;
END;📌 상수 선언
DECLARE
L_HEIGHT NUMBER := 20;
L_RADIUS NUMBER := 29.5;
L_PI CONSTANT NUMBER := 3.1415926;
L_AREA NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('정사각형 넓이: ' || L_HEIGHT * L_HEIGHT);
DBMS_OUTPUT.PUT_LINE('원의 넓이: ' || L_RADIUS * L_RADIUS * L_PI);
END;📌 예외 처리
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('예외 발생: ' || SQLERRM);
📌 실행 순서
- SQL: from - where - select 순으로 실행
- PL/SQL: select into - from - where 순으로 실행
📌 함수와 트리거
자주 사용되는 쿼리나 로직을 함수로 만들어 재사용할 수 있음
트리거에서는 DCL(ROLLBACK, COMMIT)명령을 사용할 수 없음
예를 들어 매출 현황을 구현할 때
자바에서 날짜 입력받으면 그것에 대한 데이터가 나오게 할 때 전부 다 쿼리로 구현하는 방법보다는
미리 함수로 만들어서 필요할때마다 함수만 호출할 수 있도록 할 수 있음
🔥 정리
| 개념 | 설명 |
| VIEW | 가상 테이블로, 원본 데이터 변경시 자동 반영 |
| SEQUENCE | 자동으로 증가하는 숫자 생성 |
| CURSOR | 쿼리 결과를 저장하는 포인터, 다중 행 처리 가능 |
| PL/SQL | 오라클에서 사용하는 절차적 언어 |
캐시(Cache)와 버퍼(Buffer)의 차이
- 캐시(Cache): 자주 사용되는 데이터를 저장하여 빠르게 접근할 수 있도록 함
- 버퍼(Buffer): 데이터 전송 속도 차이를 조정하여 원활한 데이터 흐름을 보장함
캐시(Cache)란?
자주 사용하는 데이터를 임시 저장하여 속도를 높이는 고속 메모리로, 주로 CPU와 메모리 간 속도 차이를 줄이기 위해 사용됨
캐시의 특징
✅ 목적: 반복해서 접근하는 데이터를 미리 저장하여 성능 향상
✅ 위치: CPU, 메모리, 디스크
✅ 작동 방식: 데이터가 자주 사용되면 캐시에 저장 → 이후 빠르게 제공
✅ 사용 예시: CPU 캐시, 웹 브라우저 캐시, 디스크 캐시
📌 예시
- CPU 캐시: CPU가 자주 사용하는 명령어나 데이터를 저장하여 처리 속도 향상
- 웹 브라우저 캐시: 방문한 웹페이지의 일부 데이터를 저장하여 다시 방문할 때 로딩 속도 단축
- 디스크 캐시: 하드디스크의 데이터를 메모리에 일부 저장하여 파일 접근 속도 증가
버퍼(Buffer)란?
데이터 전송 속도 차이를 보완하는 임시 저장 공간으로, 특히 입출력(I/O) 작업에서 발생하는 속도 차이를 해결하기 위해 사용됨
버퍼의 특징
✅ 목적: 데이터가 빠르게 생성되거나 전송될 때, 처리 속도를 맞추기 위해 저장
✅ 위치: 입출력 장치 (네트워크, 프린터, 오디오/비디오 스트리밍 등)
✅ 작동 방식: 데이터가 한꺼번에 처리되기 어려울 때 버퍼에 임시 저장 → 차례로 전송
✅ 사용 예시: 프린터 버퍼, 네트워크 버퍼, 스트리밍 버퍼
📌 예시
- 프린터 버퍼: 문서를 한꺼번에 출력하지 않고 버퍼에 저장 후 순차적으로 출력
- 오디오/비디오 스트리밍 버퍼: 영상이 끊기지 않도록 데이터를 미리 저장 후 재생
- 네트워크 버퍼: 인터넷에서 데이터를 전송할 때 일시적으로 저장하여 원활한 송수신 보장
캐시 vs 버퍼 비교 정리
| 구분 | 캐시 | 버퍼 |
| 목적 | 자주 사용되는 데이터를 빠르게 제공하여 성능 향상 | I/O 작업에서 속도 차이를 조정하고, 데이터 흐름을 원활하게 함 |
| 위치 | CPU, 메모리, 디스크와 같은 빠른 저장 장치 | I/O 장치, 네트워크 장치, 프린터 등 데이터 입출력 장치 |
| 동작 방식 |
데이터가 자주 사용되거나 빠르게 접근해야 할 때 캐시에 저장 | 데이터가 느리거나 간헐적으로 처리될 때 임시저장 공간으로 사용 |
| 사용 예시 |
CPU 캐시, 웹 브라우저 캐시, 디스크 캐시 | 프린터 버퍼, 네트워크 버퍼, 오디오/비디오 스트리밍 버퍼, |
캐시와 버퍼의 위치 관계
컴퓨터에서 캐시와 버퍼는 다음과 같은 흐름으로 동작합니다.
CPU - (캐시) - 주메모리 - (버퍼) - 외부기억장치- 캐시(Cache)는 CPU와 주메모리 간 속도 차이를 줄여줌
- 버퍼(Buffer)는 주메모리와 외부기억장치 간 속도 차이를 줄여줌
📌 추가 개념
- SELECT 문과 버퍼: 데이터베이스에서 SELECT 문을 실행하면, 데이터가 버퍼에 저장되었다가 일정 크기가 차면 화면에 출력됨
- 시퀀스와 캐시: 데이터베이스 시퀀스(sequence)는 미리 생성된 값을 캐시에 저장하여 성능을 최적화함
예제)
위 텍스트 파일 다운받아서 오라클에서 실행시키기
1) 회원 테이블에서 마일리지가 3000이상인 회원들의 회원번호, 회원명, 마일리지로 VIEW 생성
create or replace view v_mem01(mid, name, mileage)
as
select mem_id, mem_name, mem_mileage
from member
where mem_mileage>=3000;
select * from v_mem01;
회원번호 e001인 회원의 마일리지를 6500 -> 2000으로 변경
update v_mem01
set mileage=2000
where mid='e001';
VIEW를 변경하면 원본 테이블 MEMBER 테이블의 데이터도 바뀜
원본 테이블 변경
update member
set mem_mileage=6700
where mem_id='e001';
원본 테이블이 변경되면 VIEW의 데이터도 변경됨
2) WITH CHECK OPTION
create or replace view v_mem01
as
select mem_id as 회원번호, mem_name as 회원명, mem_mileage as 마일리지
from member
where mem_mileage>=3000
with check option;
이렇게 with check option을 주고 update하면
update v_mem01
set 마일리지=2000
where 회원번호='e001';
view check option 위배 에러 에러 발생!!
마일리지가 3000이상되게 view를 만들어놨는데 3000미만으로 바꾸려고 해서 에러가 나는 것임
(3000이상으로 바꾸면 에러 안남)
하지만 원본 테이블을 update하면 바뀜
(3000미만으로 변경하면 원본과 view에서 변경되면서 view에서 사라짐)
update member
set mem_mileage=5700
where mem_id='e001';
select * from v_mem01;
3) WITH READ ONLY
create or replace view v_mem01
as
select mem_id, mem_name , mem_mileage
from member
where mem_mileage>=3000
with read only;
update v_mem01
set mem_mileage=3000
where mem_id='e001';
read-only 에러 발생
읽기전용으로 해놨는데 변경하려고 해서 에러가 난 것임
마찬가지로 원본 테이블을 바꾸면 view도 같이 바뀜
update member
set mem_mileage=6000
where mem_id='e001';
4) sequence
create sequence seq_lprod
start with 10;
시퀀스명.nextval
insert into lprod(lprod_id, lprod_gu, lprod_nm)
values (seq_lprod.nextval, 'P501', '농산물');
insert into lprod(lprod_id, lprod_gu, lprod_nm)
values (seq_lprod.nextval, 'P503', '임산물');
insert into lprod(lprod_id, lprod_gu, lprod_nm)
values (seq_lprod.nextval, 'P502', '수산물');
select * from lprod;
PL/SQL
| 구분 | 내용 |
| anonymouse block | 단순 스크립트에서 실행되는 블록 서버에 저장되지 않는다. (실행할때마다 컴파일해야됨) |
| stored procedure | 자주 실행되거나 복잡한 비즈니스 로직을 미리 작성하여 서버에 저장하여 사용한다. |
| user function | procedure와 유사하며, 실행결과를 반환한다. |
| package | 여러 procedure, function 및 변수 등을 하나로 묶는다. |
| trigger | 테이블이나 뷰에 insert, update, delete등이 수행 전 또는 수행 후 자동 실행되는 procedure 예) 구매하고나면 재고 변경후 마일리지 쌓기 |
procedure와 function은 비슷하지만 다름!
function은 return value가 있지만 procedure는 없음
PL/SQL 기본 구조
declare
선언영역 -- 변수, 상수, 커서(view) 선언
begin
실행영역 -- 비지니스 로직을 이용하여 문제해결
:
[exception
-- 예외처리명령;]
end;
변수 : 하나의 변수에는 하나의 값만 저장할 수 있음
배열 : 하나의 변수에 여러개의 값 저장할 수 있음. 하지만 값들이 다 같은 타입이어야하고 값만 저장할 수 있음
스트럭처 : 여러 타입의 변수를 묶을 수 있음
클래스 : 참조타입으로 힙의 주소를 전달하기 때문에 값이 아닌 주소가 참조됨
자바에서는 FINAL로 상수 선언. 오라클에서는 CONSTANT 예약어로 상수 선언
변수명 [CONSTANT] 타입명[:=크기값];
<스칼라변수>
변수명 타입 := 초기값
배정연산자( 변수선언할때)
<참조형>
테이블명과 컬럼명을 알고있으면 다 쓸 수 있음
| 데이터 타입 | 설명 |
| 변수명 테이블명.컬럼명%TYPE | 해당 테이블의 해당컬럼의 타입과 동일하게 지정 |
| 변수명 테이블명%ROWTYPE | 해당 테이블의 모든 컬럼과 동일하게 지정 |
키보드로 부서번호를 입력받아 해당부서에 근무하는 사원들의 사원번호, 사원명, 입사일, 급여를 출력하는 익명블록을 작성
입력 명령어 : accept
ACCEPT P_DID PROMPT '부서코드(xx): ' --입력값이 P_DID라는 변수에 저장됨
DECLARE
L_EID EMPLOYEES.EMPLOYEE_ID%TYPE; --컬럼 참조 타입 : 테이블명과 컬럼명을 알고있으면 변수를 똑같은 타입으로 설정해줌
L_ENAME VARCHAR2(100);
L_HDATE DATE;
L_SALARY NUMBER:=0; --숫자는 초기값 설정해줘야함. 안하면 병목현상 발생
BEGIN
SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME, HIRE_DATE, SALARY
INTO L_EID, L_ENAME, L_HDATE, L_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = &P_DID; --참조할때는 &붙이기
-- DBMS_OUTPUT.PUT :출력하는 명령어 DBMS_OUTPUT.PUT_LINE : 줄바꿈
DBMS_OUTPUT.PUT_LINE('사원번호 : '|| L_EID);
DBMS_OUTPUT.PUT_LINE('사원명 : '|| L_ENAME);
DBMS_OUTPUT.PUT_LINE('입사일자 : '|| L_HDATE);
DBMS_OUTPUT.PUT_LINE('급여 : '|| L_SALARY);
DBMS_OUTPUT.PUT_LINE('--------------------------');
EXCEPTION WHEN OTHERS THEN -- 예외처리
DBMS_OUTPUT.PUT_LINE('예외발생: ' || SQLERRM); --SQLERRM: 에러 메시지를 갖고있는 시스템 변수
END;
exception when others then으로 예외처리 (자바에서 exception 클래스와 같은 느낌. 모든 에러를 다 처리해줌)
보기 - DBMS 출력
+ 버튼 클릭 - 지금 접속해있는 계정과 같은 걸로 접속
20 입력
에러 발생
한개의 변수에는 한개의 값만 저장되는데,
한개의 변수에 여러개의 값을 넣으려고 했기때문에 에러가 발생한다. (여러개의 행을 변수 하나가 커버 못해서)
그럴때 필요한 것이 커서 (view) !
선언부에 커서 선언
cursor 커서명 is
select문;
실행영역에서
open 커서명;
fetch 커서명 into {변수리스트 | 레코드명};
close 커서명;
닫혀있던 문을 다시 열수 있음
-> 결과집합안으로 데이터를 꺼낼 수 있음
select절이 into가 사용돼있기 때문
제일 앞에 있는 select절에 into 사용 (서브쿼리에는 사용 x)
ACCEPT P_DID PROMPT '부서코드(xx) : '
DECLARE
L_EID EMPLOYEES.EMPLOYEE_ID%TYPE;
L_ENAME VARCHAR2(100);
L_HDATE DATE;
L_SALARY NUMBER:=0;
CURSOR CUR_EMP(B_DID EMPLOYEES.DEPARTMENT_ID%TYPE) IS
SELECT EMPLOYEE_ID,FIRST_NAME||' '||LAST_NAME,HIRE_DATE,SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=B_DID;
BEGIN
OPEN CUR_EMP(&P_DID);
DBMS_OUTPUT.PUT_LINE('부서번호 : '||&P_DID);
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
LOOP
FETCH CUR_EMP INTO L_EID,L_ENAME,L_HDATE,L_SALARY; --FETCH:줄단위로 읽기
EXIT WHEN CUR_EMP%NOTFOUND; -- 없으면 TRUE -> EXIT
DBMS_OUTPUT.PUT_LINE('사원번호 : '||L_EID);
DBMS_OUTPUT.PUT_LINE('사원명 : '||L_ENAME);
DBMS_OUTPUT.PUT_LINE('입사일자 : '||L_HDATE);
DBMS_OUTPUT.PUT_LINE('급여 : '||L_SALARY);
DBMS_OUTPUT.PUT_LINE('-----------------------------------');
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('예외발생 : '||SQLERRM);
END;
상수
CONSTANT 예약어 사용
DECLARE
L_HEIGHT NUMBER:=20;
L_RADIUS NUMBER:=29.5;
L_PI CONSTANT NUMBER:=3.1415926;
L_AREA NUMBER:=0;
BEGIN
DBMS_OUTPUT.PUT_LINE('정사각형 넓이: '|| L_HEIGHT*L_HEIGHT);
DBMS_OUTPUT.PUT_LINE('원의 넓이: '|| L_RADIUS*L_RADIUS*L_PI);
END;
