18. 사용자 기능

사용자가 미리 데이터베이스에 등록해 필요할 때마다 사용하는 기능으로 함수, 프로시저, 트리거, 뷰, 시퀀스를 설명한다.

사용자 기능은 일반적인 SQL과 프로그램 기능을 제공하는 PL/SQL을 사용한다.

< 사용자 기능의 간단한 설명 >

•

함수는 특정 명령을 실행하고 값을 반환하는 기능이다.

•

프로시저는 여러 작업을 한 번에 처리하는 기능이다.

•

트리거는  데이터베이스를 조작할 때 자동 실행하는 기능이다.

•

뷰는 가상 테이블로,연관 테이블의 내용이 바뀌면 자동 반영하는 기능이다.

•

시퀀스는 자동으로 일련번호를 만드는 기능이다.

18.1 함수

함수는 반복 작업이나 복잡한 작업을 수행하고 결과값을 반환하는 기능이다.

•

 CREATE [ OR REPLACE ] FUNCTION 함수명 (매개변수1, 형식1, 매개변수2, 형식2,...)

RETURN 반환형식 IS 변수 변수형식(크기);

BEGIN

실행 SQL 구문 INTO 변수;

RETURN 변수;

[EXCEPTION 예외처리;]

END 함수명;

•

DROP FUNCTION 함수명;

CREATE OR  REPLACE FUNCTION 함수명 :

함수명에 해당하는 함수를 만들거나 기존 함수의 내용을 바꾼다.

함수명(매개변수1 형식1, 매개변수2 형식2, ...) :

외부에서 받을 함수의 매개변수와 데이터 형식을 설정한다.

RETURN 반환형식: 반환값의 형식을 설정

IS 변수 변수형식(크기) : 변수를 선언하고 변수와 형식(크기)를 설정한다.

BEGIN ... END 함수명; : 실행할 SQL과 결괏값을 넣을 변수 및 예외처리를 한다.

실행 SQL구문 INTO 변수 : SQL구문을 실행한 결괏값을 변수에 저장한다.

RETURN 변수; : 변수값을 반환한다.

EXCEPTION 예외처리 : 예외처리를 한다. (일반적인 예외 처리 방식은 작업을 중단하고 작업 전 상태로 돌아가는 것이다.)

/ : 명시적인 함수의 끝을 나타낸다.

10.

DROP FUNCTION  함수명 : 함수명에 해당하는 함수를 삭제한다.

예제1. 일수와 ‘HHMMSS’ 형식의 시각 문자열을 매개변수로 입력해 초로 바꾸는 함수를 만든다.

CREATE OR REPLACE FUNCTION FN_DT_TMTOSEC(p_day NUMBER, p_tm CHAR)
RETURN NUMBER IS ret_sec NUMBER(10);
BEGIN
	SELECT (p_day*86400) + TO_NUMBER(TO_CHAR(TO_DATE(p_tm,'HH24:MI:SS'),'SSSSS')) INTO ret_sec
	FROM DUAL;

	RETURN ret_sec;

END FN_DT_TMTOSEC;
SQL
복사

예제2. FN_DT_TMTOSEC() 함수로 당일 ‘070800’시간을 초로 바꾸고, 다음날 ‘002030’ 시간을 초로 바꾸고, 현재시간을 24시간 단위로 확인한 후 초로 바꾼다.

SELECT FN_DT_TMTOSEC(0,'070800'),
	FN_DT_TMTOSEC(1,'002030'),
	TO_CHAR(SYSDATE,'HH24:MI:SS') AS CUR_TM,
	FN_DT_TMTOSEC(0, TO_CHAR(SYSDATE,'HH24:MI:SS')) AS CUR_SEC
FROM DUAL;
SQL
복사

예제3. 수치 초를 매개변수로 입력해 ‘HHMMSS’ 형식으로 바꾸는 함수를 만든다. (FN_DT_SECTOTM)

CREATE OR REPLACE FUNCTION FN_DT_SECTOTM(p_sec NUMBER)
RETURN CHAR IS ret_tm CHAR(6);

BEGIN
	SELECT(
		LPAD(FLOOR(MOD(p_sec,86400)/3600),2,'0') ||
		LPAD(FLOOR(MOD(MOD(p_sec,86400),3600)/60),2,'0')||
		LPAD(FLOOR(MOD(MOD(MOD(p_sec,86400),3600),60)),2,'0')
	) INTO ret_tm
	FROM DUAL;

	RETURN ret_tm;

END FN_DT_SECTOTM;
/
SQL
복사

MOD()함수로 28,500초를 시간으로 바꾸면 ‘07’시다. 다음은 바꾸는 과정

SELECT FLOOR(MOD(28500,86400)/3600)
FROM DUAL;
SQL
복사

예제4. 현재 시간에서 300초 이전과 300초 이후를 ‘HHMMSS’ 형식으로 구한다.

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') AS CUR_TM,
	FN_DT_SECTOTM(FN_DT_TMTOSEC(0,
		TO_CHAR(SYSDATE, 'HH24MISS'))-300) AS MINUS_SEC,
	FN_DT_SECTOTM(FN_DT_TMTOSEC(0,
		TO_CHAR(SYSDATE, 'HH24MISS'))+300) AS PLUS_SEC
FROM DUAL;
SQL
복사

예제5. 일수, ‘HHMMSS’형식의 시간, 기준 시각에서 +/- 방향으로 이동한 시간(초)을 매개변수로 입력해 일수와 시각을 기준으로 이동한 시각을 ‘HHMMSS’ 형식으로 구하는 함수를 만든다.

CREATE OR REPLACE FUNCTION FN_DT_OFFSETTM(p_day NUMBER,
	p_tm CHAR,p_offset_sec NUMBER)

RETURN CHAR IS ret_offset_tm CHAR(6);

BEGIN
	SELECT(
		LPAD(FLOOR(MOD(S1.SEC,86400)/3600),2,'0')|| --시
		LPAD(FLOOR(MOD(MOD(S1.SEC,86400),3600)/60),2,'0')|| --분
		LPAD(FLOOR(MOD(MOD(MOD(S1.SEC,86400),3600),60)),2,'0') -- 초
		) INTO ret_offset_tm
	FROM(
		SELECT (p_day*86400) + TO_NUMBER(TO_CHAR(TO_DATE(p_tm,'HH24:MI:SS'),'SSSSS')) + (p_offset_sec)
			AS SEC
		FROM DUAL
		) S1;

RETURN (ret_offset_tm);
END FN_DT_OFFSETTM;
/
SQL
복사

예제6. 현재시간에서 300초 이전 시간과 300초 이후의 시간을 ‘HHMMSS’ 형식으로 구한다.

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') AS CUR_TM,
	FN_DT_OFFSETTM(0,TO_CHAR(SYSDATE,'HH24MISS'),-300) AS MINUS_SEC,
	FN_DT_OFFSETTM(0,TO_CHAR(SYSDATE,'HH24MISS'),300) AS PLUS_SEC
FROM DUAL;
SQL
복사

예제7. 일수, ‘HHMMSS’ 형식의 시간, 기준 시각에서 +/- 방향으로 이동한 초 단위 시간을 매개변수로 입력해 일수와 시각을 기준으로 이동한 시각의 경과일수를 구하는 함수를 만든다.

CREATE OR REPLACE FUNCTION FN_DT_OFFSETTMDNO(p_day NUMBER, p_tm CHAR, p_offset_sec NUMBER)

RETURN NUMBER IS ret_offset_dno NUMBER(3);

BEGIN
SELECT FLOOR(S1.SEC/86400) INTO ret_offset_dno
FROM (
	SELECT (p_day*86400) + TO_NUMBER(TO_CHAR(TO_DATE(p_tm,'HH24:MI:SS'),'SSSSS'))+ (p_offset_sec)
		AS SEC
	FROM DUAL
	)S1;

RETURN ret_offset_dno;

END FN_DT_OFFSETTMDNO;
/
SQL
복사

예제8. 현재 시간에서 43,200초 이전과 43,200초 이후의 경과일수를 구한다.

SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') AS CUR_TM,
	FN_DT_OFFSETTMDNO(0,TO_CHAR(SYSDATE,'HH24MISS'),-43200) AS MINUS_DNO,
	FN_DT_OFFSETTMDNO(0,TO_CHAR(SYSDATE,'HH24MISS'),43200) AS PLUS_DNO
FROM DUAL;
SQL
복사

예제9. ‘YYYYMMDD’ 날짜 문자열을 매개변수로 입력해 ‘YYYY-MM-DD’ 형식으로 바꾸는 함수를 만든다.

CREATE OR REPLACE FUNCTION FN_DT_CNVDTFMT(p_dt CHAR)
RETURN CHAR IS ret_fmt_dt CHAR(10);

BEGIN
	SELECT SUBSTR(p_dt,1,4) || '-' || SUBSTR(p_dt,5,2) || '-' ||
		SUBSTR(p_dt,7,2) INTO ret_fmt_dt
	FROM DUAL;

RETURN ret_fmt_dt;

END FN_DT_CNVDTFMT;
/
SQL
복사

예제10. ‘20190820’을 ‘YYYY-MM-DD’ 형식으로 바꾼다.

SELECT FN_DT_CNVDTFMT('20190820')
FROM DUAL;
SQL
복사

예제11. 시각을 나타내는 ‘HHMMSS’ 문자열을 매개변수로 입력해 ‘HH:MM:SS’ 형식으로 바꾸는 함수를 만든다.

CREATE OR REPLACE FUNCTION FN_DT_CNVTMFMT(p_tm CHAR)
RETURN CHAR IS ret_fmt_tm CHAR(8);

BEGIN
	SELECT DECODE(p_tm,NULL,NULL,SUBSTR(p_tm,1,2)||':'||SUBSTR(p_tm,3,2)||':'||
		SUBSTR(p_tm,5,2)) INTO ret_fmt_tm
	FROM DUAL;

RETURN ret_fmt_tm;

END FN_DT_CNVTMFMT;
/
SQL
복사

예제12. ‘164530’ 시간 문자열을 ‘HH:MM:SS’ 형식으로 바꾼다. (FN_DT_CNVTMFMT 사용)

‘234500’ 시간을 초로 바꾼 후 3,600초를 더해 ‘HH:MM:SS’ 형식으로 바꾼다. (FN_DT_OFFSETTM, FN_DT_CNVTMFMT 사용)

SELECT FN_DT_CNVTMFMT('164530')
FROM DUAL;

SELECT FN_DT_CNVTMFMT(FN_DT_OFFSETTM(0,'234500',3600))
FROM DUAL;
SQL
복사

예제13. ‘YYYYMMDDHHMMSS’ 형식을 매개변수로 입력해 ‘YYYY-MM-DD HH:MM:SS’ 형식으로 바꾸는 함수를 만든다.

CREATE OR REPLACE FUNCTION FN_DT_CNVDTTMFMT(p_dttm CHAR)

RETURN CHAR IS ret_fmt_dttm CHAR(23);

BEGIN
	SELECT SUBSTR(p_dttm,1,4) || '-' || SUBSTR(p_dttm,5,2) || '-' || 
		SUBSTR(p_dttm,7,2) || ' ' ||
		SUBSTR(p_dttm,9,2) || ':' || SUBSTR(p_dttm,11,2) || ':' || 
		SUBSTR(p_dttm,13,2) INTO ret_fmt_dttm
	FROM DUAL;

RETURN ret_fmt_dttm;

END FN_DT_CNVDTTMFMT;
/
SQL
복사

예제14. ‘20190820070800’ 일시 문자열을 ‘YYYY-MM-DD HH:MM:SS’ 형식으로 바꾼다.

SELECT FN_DT_CNVDTTMFMT('20190820070800')
FROM DUAL;
SQL
복사

예제15. 기준 문자열에 포함된 문자수를 구하는 함수를 만든다. (예를들어, ‘ABCDAC’에서 ‘A’이면 2, ‘C’ 이면 2, ‘B’이면 1을 구한다.)

CREATE OR REPLACE FUNCTION FN_GET_STR_COUNT(p_str CHAR, p_find_str CHAR)

RETURN NUMBER
IS
	ret_sales_count NUMBER(3);
	loc NUMBER(3);

BEGIN
	ret_sales_count := 0; -- 오라클 변수 선언자 ':='
	loc := 1;

	WHILE (1=1) -- 무한루프
	LOOP
		loc := INSTR(p_str,p_find_str,loc); -- 1부터 시작해서 찾는 문자열의 위치를 구한다.
		EXIT WHEN loc = 0; -- 만약 찾는 문자열이 없다면 반복문을 나온다.

		loc := loc + LENGTH(p_find_str); -- 문자열의길이+문자열의시작위치로 새로운 loc을 만든다.
		ret_sales_count := ret_sales_count + 1; -- 위의 결과로 찾는 문자열을 하나씩 찾아간다.
	END LOOP;

	RETURN ret_sales_count;

END FN_GET_STR_COUNT;
/
SQL
복사

WHILE 참조건 반복문은 EXIT 조건 구문이 중간에 없으면 “LOOP ... END LOOP” 내 명령을 무한반복 한다.

예제16. 기준 문자열에서 문자 개수를 구하는 함수를 확인한다.

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS 
WHERE OBJECT_TYPE = 'FUNCTION'; -- 만든 함수를 검색
SQL
복사

USER_OBJECTS 테이블에는 객체(함수, 프로시저, 트리거, 시퀀스, 뷰 등) 정보가 포함돼 있다. 필드로 객체명(OBJECT_NAME), 서브객체명(SUBOBJECT_NAME), 객체ID(OBJECT_ID), 객체형식(OBJECT_TYPE), 생성일시(CREATED), 상태(STATUS) 등이 있다.

객체형식으로 FUNCTION은 함수, PROCEDURE은 프로시저, TRIGGER는 트리거, SEQUENCE는 시퀀스, VIEW는 뷰를 나타낸다.

예제17. ‘1Day SQL Example : 123 : 오라클’ 문자열에서 ‘:’ 문자의 개수를 구한다.

SELECT FN_GET_STR_COUNT('1Day SQL Example : 123 : 오라클',':')
FROM DUAL;
SQL
복사

예제18. FN_GET_STR_COUNT() 함수를 삭제하고 확인한다.

DROP FUNCTION FN_GET_STR_COUNT;

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'FUNCTION';
SQL
복사

18.2 프로시저

프로시저는 여러 데이터베이스 작업으로 이뤄진 하나의 업무를 실행하는 기능이다.

프로시저는 PL/SQL 을 통해 만들어진다. 자주 사용하는 SQL을 프로시저로 만든 뒤 필요 할때마다 호출, 사용하여 작업 효율을 늘릴 수 있다. 프로시저는 결과 값을 반환하는 것이 아니라 로직만 처리하는 서브 프로그램이다.

•

CREATE OR REPLACE PROCEDURE 프로시저명(매개변수1 형식1, 매개변수2 형식2, ...)

변수 변수형식(크기);

BEGIN

SQL 구문;

[EXCEPTION 예외처리;]

END 프로시저명;

•

 CREATE OR REPLACE PROCEDURE 프로시저명(매개변수1 형식1, 매개변수2 형식2, ...)

변수;

CURSOR 커서명 IS SELECT SQL 구문;

BEGIN

OPEN 커서명;

FETCH 커서명 INTO 변수;

SQL 구문;

CLOSE 커서명;

[EXCEPTION 예외처리;]

END 프로시저명;

프로시저 실행 : EXEC 프로시저명;

프로시저 삭제 : DROP PROCEDURE 프로시저명;

•

CREATE OR REPLACE PROCEDURE 프로시저명 :

프로시저 만들거나 내용 바꾸기

•

프로시저명(매개변수1 형식1, 매개변수2 형식2, ...):

외부에서 받을 프로시저의 ‘매개변수’와 ‘데이터 형식’을 설정한다.

•

IS 변수 변수형식(크기):

변수와 형식(크기)를 설정한다.

•

 BEGIN ... END 프로시저명:

실행할 SQL과 예외처리를 수행한다.

•

CURSOR 커서명 IS SELECT SQL 구문:

SELECT 구문을 커서로 선언한다.

•

OPEN 커서명:

‘커서명’에 해당하는 커서를 오픈한다.

•

FETCH 커서명 INTO 변수:

커서로 지정한 SQL을 실행하고 결괏값을 구해 ‘변수’에 입력한다.

•

CLOSE 커서명:

커서를 닫는다.

•

EXCEPTION 예외처리:

예외처리를 수행한다.

•

/ :

명시적인 프로시저 끝을 나타낸다.

•

 EXEC 프로시저명 : 

프로시저를 실행한다.

•

 DROP PROCEDURE 프로시저명:

프로시저를 삭제한다.

커서 : 특정 SQL 문장을 처리한 결과를 담고 있는 영역(PRIVATE SQL 이라는 메모리 영역)을 가리키는 일종의 포인터로, 커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있다.

SQL 문은 집합적 언어이므로 임의의 SQL문이 처리된 결과의 로우수는 한개 이상이다.

커서의 종류에는 묵시적 커서와 명시적 커서가 있다. 묵시적 커서란 말 그대로 오라클 내부에서 자동으로 생성되어 사용하는 커서로, PL/SQL 블록에서 실행하는 SQL문장(INSERT,UPDATE,MERGE,DELETE,SELECT INTO)이 실행될 때마다 자동으로 만들어져 사용된다.

반면, 명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 의미한다.

커서는 ‘커서열기(OPEN) - 패치(FETCH) - 커서닫기(CLOSE)’ 의 3단계로 진행된다. 다만, 명시적 커서는 커서를 선언하는 부분이 추가되고 이후의 세 단계를 직접 구현해야 하지만, 묵시적 커서는 커서 선언도 필요 없고 위 3단계도 자동으로 처리된다.

묵시적 커서의 정보를 참조할때는 SQL로 시작되는 속성명을 사용해 참조할 수 있어 묵시적 커서를 SQL 커서라고도 한다.

명시적 커서를 사용하려면 ‘커서 선언 - 커서 열기 - 패치 단계에서 커서 사용 - 커서 닫기’ 4단계 작업이 필요하다.

커서 선언

CURSOR 커서명 (매개변수1, 매개변수2,..)

SELECT 문장;

커서 열기

커서를 선언한 뒤 해당 커서를 사용하려면 먼저 커서를 열어야 한다.

OPEN 커서명(매개변수1, 매개변수2,...);

패치 단계에서 커서 사용

커서를 열고 난 후에야 SELECT 문의 결과로 반환되는 로우에 접근할 수 있다. 결과 집합의 로우수는 1개 이상이므로 개별 로우에 접근하기 위해서는 반복문을 사용해야 하는데, LOOP, WHILE, FOR문에서 모두 사용가능하다.

LOOP

FETCH 커서명 INTO 변수1,변수2,...

EXIT WHEN 커서명%NOTFOUND;

END LOOP;

—> FETCH INTO를 통해 커서에서 반환되는 각 칼럼 값을 변수에 할당할 수 있다. 이때, 변수는 반환된 칼럼 수와 타입이 일치해야 한다. 즉, 커서를 선언할 때 연결했던 SELECT문의 칼럼 수만큼 변수를 명시해야 하고 타입도 맞춰줘야 한다. 또, 반복문을 사용하므로 해당 커서의 참조가 모두 끝났을 때 반복문을 빠져나와야 하는데, 이때 ‘커서명%NOTFOUND’ 커서 속성을 사용해야 루프를 벗어난다.

<커서 속성>

커서 속성	설명
%FOUND	커서의 결과에 값이 존재하면 참이다.
%NOTFOUND	커서의 결과에 값이 존재하지 않으면 참이다.
%ROWCOUNT	커서에서 구한 레코드의 수를 반환한다.
%ISOPEN	커서가 열려있는 상태라면 참이다.

~ 커서 속성을 사용하는 PL/SQL 구문의 예

CURSOR CUR_TEST IS SQL구문;
BEGIN
	IF CUR_TEST%ISOPEN -- 기존에 커서가 오픈되어 있으면
	THEN CLOSE CUR_TEST; -- 커서 닫기
	END IF;

	OPEN CUR_TEST;
	LOOP
		FETCH CUR_TEST INTO row_info;
		EXIT WHEN CUR_TEST%NOTFOUND; --커서값이 없으면 반복문 나가기
		<작업 SQL;>
	END LOOP;
END;
SQL
복사

커서 닫기

CLOSE 커서명;

예제1. ‘YYYY’ 형식(문자)의 연도를 매개변수로 입력해 포인트 테이블에서 해당 연도의 포인트 합계를 계산해 포인트정보 테이블에 입력하는 프로시저를(pr_ins_pnt_info) 만든다.

CREATE OR REPLACE PROCEDURE PR_INS_PNT_INFO(p_year CHAR)

IS
	tot_sales_count NUMBER;

BEGIN
	/* 기존 포인트 정보 삭제 */
	DELETE 
	FROM TB_POINT_INFO
	WHERE YEAR = p_year;

	/* 포인트 정보 입력 */
	INSERT INTO TB_POINT_INFO
	SELECT p_year AS YEAR,
		CUSTOMER_CD,
		SUM(POINT) AS POINT,
		RANK OVER(ORDER BY SUM(POINT) DESC) AS RANK,
		SUBSTR(MAX(REG_DTTM),1,8) AS LAST_PNT_DT
	FROM TB_POINT
	WHERE SUBSTR(REG_DTTM,1,4) = p_year
	GROUP BY CUSTOMER_CD;

/* 데이터베이스에 적용 */
COMMIT;

/* 출력 메세지 */
SELECT COUNT(YEAR) INTO tot_sales_count
FROM TB_POINT_INFO
WHERE YEAR=p_year;


DBMS_OUTPUT.PUT_LINE('결과 :' || p_year ||
	':' || tot_sales_count || ' 개 포인트 정보 입력 완료');

/* 예외 처리 */
EXCEPTION WHEN OTHERS THEN -- 알아서 예외 처리

ROLLBACK; -- commit 전으로 돌아감
DBMS_OUTPUT.PUT_LINE('결과: '||p_year||' 포인트 입력 오류!!!');

END PR_INS_PNT_INFO;
/


SQL
복사

예외처리 구문

EXCEPTION WHEN 예외명1 THEN 예외처리 구문1

WHEN 예외명2 THEN 예외처리 구문2

...

WHEN OTHERS THEN 예외처리 구문n;

예외처리 구문은 CASE문과 비슷한 구조를 갖는다. WHEN 다음에 오는 예외명은 아무 이름이나 사용할 수는 없고 시스템 예외 중 일부와 사용자가 직접 정의한 예외명을 사용할 수 있다. 구체적인 예외명을 알 수 없을 때 OTHERS를 사용하면 되는데, 이렇게 하면 오라클 시스템에서 알아서 자동으로 예외를 잡아준다. 물론 자동으로 잡아주기 때문에 처리할 수 있는 예외는 시스템 예외에 한정된다.

예제2. 포인트 합을 포인트정보 테이블에 입력하는 프로시저를 확인한다.

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE='PROCEDURE';
SQL
복사

예제3. PR_INS_PNT_INFO() 프로시저를 실행하고 결괏값을 확인한다.

SET SERVEROUT ON;
EXEC PR_INS_PNT_INFO('2018'
SET SERVEROUT OFF;
SQL
복사
결과 :2018:5 개 포인트 정보 입력 완료


PL/SQL 프로시저가 성공적으로 완료되었습니다.

기본적으로 PL/SQL은 결과물을 보여주지 않는다. SET SERVEROUT ON/OFF 는 서버에서 실행한 결과를 화면에 보일지를 설정한다. ON상태에서 프로시저를 실행하고 결과를 보이고 다시 OFF 상태로 결과가 화면에 보이지 않게 만든다.

예제4. 연도를 매개변수로 입력해 포인트정보 테이블에서 해당 연도 데이터를 검색하는 프로시저를 만든다.

CREATE OR REPLACE PROCEDURE PR_DSP_PNT_INFO(p_year CHAR)

IS
	/* 커서 선언*/
	CURSOR CUR_PNT_INFO(c_year IN VARCHAR2)
	IS
	SELECT YEAR,
		RANK,
		CUSTOMER_CD,
		POINT,
		LAST_POINT_DT
	FROM TB_POINT_INFO
	WHWERE YEAR = c_year;

	/* 프로시저 변수 선언*/
	n_tot_sales_count NUMBER;

BEGIN
	n_tot_sales_count := 0;

	DBMS_OUTPUT.PUT_LINE('---------------------------');
	DBMS_OUTPUT.PUT_LINE('포인트 통계 출력');
	DBMS_OUTPUT.PUT_LINE('---------------------------');

	FOR ROW_INFO IN CUR_PNT_INFO(p_year)
	LOOP
		DBMS_OUTPUT.PUT_LINE(ROW_INFO.YEAR || ',' ||
			ROW_INFO.RANK || ',' || ROW_INFO.CUSTOMER_CD || ',' ||
			TO_CHAR(ROW_INFO.POINT,'999,999,9999') || ',' ||
			FN_DT_CNVDTFMT(ROW_INFO.LAST_POINT_DT));
		n_tot_sales_count := n_tot_sales_count + 1;
	END LOOP;

	IF (n_tot_sales_count > 0) THEN
		DBMS_OUTPUT.PUT_LINE('---------------------------');
		DBMS_OUTPUT.PUT_LINE('출력 고객:' || n_tot_sales_count);
	ELSE
		DBMS_OUTPUT.PUT_LINE('검색된 데이터가 없습니다.');
	END IF;

END PR_DSP_PNT_INFO;
/
SQL
복사

예제5. 포인트정보 테이블에서 데이터를 보이는 프로시저를 확인한다.

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE='PROCEDURE';
SQL
복사

예제6. PR_DSP_PNT_INFO() 프로시저를 실행하고 결괏값을 확인한다.

SET SERVEROUT ON;
EXEC PR_DSP_PNT_INFO('2018')
SET SERVEROUT OFF;
SQL
복사

예제7. PR_INS_PNT_INFO(), PR_DSP_PNT_INFO() 프로시저를 삭제하고 확인한다.

DROP PROCEDURE PR_INS_PNT_INFO;
DROP PROCEDURE PR_DSP_PNT_INFO;

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECT
WHERE OBJECT_TYPE = 'PROCEDURE';
SQL
복사

18.3 트리거

트리거는 데이터베이스에서 입력,수정,삭제 작업이 발생하면 미리 만들어 둔 SQL을 자동으로 실행하는 기능이다. 즉, 사용자가 직접 호출하는 것이 아니다.

트리거는 전체 트랜잭션 작업에 대해 발생되는 트리거와 각행에 대해 발생되는 트리거가 있다.

트리거가 사용되는 경우는 기본 정보를 삭제할 때 연관 테이블 정보를 삭제하거나, 상세 정보를 바꿀 때 기본 정보가 바뀌는 경우 많이 사용한다. 즉, 트리거를 사용하면 연관 테이블의 관계에서 부정합 데이터를 줄일 수 있다.

CREATE OR REPLACE TRIGGER 트리거명

BEFORE | AFTER

INSERT | UPDATE | DELETE ON 테이블

[FOR EACH ROW]

[WHEN 조건]

DECLARE

변수 변수형식(크기)

BEGIN

실행 SQL 구문;

[EXCEPTION 예외처리;]

END 트리거명;

예제1.고객 테이블의 고객코드를 삭제하는 명령을 실행하면 포인트 테이블 에서 해당 고객코드를 먼저 삭제하고, 고객 테이블에서 고객코드를 삭제하는 트리거를 만든다.

CREATE OR REPLACE TRIGGER TG_DEL_CUST_INFO -- 트리거 형성

BEFORE DELETE ON TB_CUSTOMER -- TB_CUSTOMER 에서 삭제하기 전에

FOR EACH ROW -- 행에 대응하는 트리거를

BEGIN

DELETE TB_POINT -- TB_POINT에서 먼저 삭제
WHERE CUSTOMER_CD = :OLD.CUSTOMER_CD; -- 삭제 대상 고객코드(:OLD.CUSTOMER_CD) 와 같다면 삭제

END TG_DEL_CUST_INFO;
/
SQL
복사

예제2. 고객 테이블의 고객코드를 삭제하는 트리거를 확인한다.

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER'
AND OBJECT_NAME = 'TG_DEL_CUST_INFO';
SQL
복사

예제3. 데이터를 삭제하기 전 고객 테이블과 포인트 테이블을 ‘2018087’ 고객코드로 검색해 내용을 확인한다.

SELECT * FROM TB_CUSTOMER WHERE CUSTOMER_CD = '2018087';
SELECT * FROM TB_POINT WHERE CUSTOMER_CD = '2018087';
SQL
복사

예제4. 고객 테이블에서 고객코드가 ‘2018087’인 고객을 삭제하고, 고객 테이블과 포인트 테이블에서 삭제한 고객 데이터를 확인한다.

DELETE
FROM TB_CUSTOMER
WHERE CUSTOMER_CD = '2018087';

SELECT * FROM TB_CUSTOMER WHERE CUSTOMER_CD = '2018087';
SELECT * FROM TB_POINT WHERE CUSTOMER_CD = '2018087';
SQL
복사
트리거로 인해 삭제할 때 TB_POINT에서 먼저 삭제하고 그 다음 TB_CUSTOMER에서 삭제

예제5. TG_DEL_CUST_INFO 트리거를 삭제하고 확인한다.

DROP TRIGGER TG_DEL_CUST_INFO;

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER'
AND OBJECT_NAME = 'TG_DEL_CUST_INFO';
SQL
복사

예제6. 바뀐 내용을 데이터베이스에 반영한다.

SQL
복사
트리거를 실행해 고객 테이블과 포인트 테이블에서 삭제한 내용을 커밋 명령으로 데이터베이스에 반영한다.

18.4 뷰

뷰(VIEW)는 설정한 연관 테이블의 내용이 바뀌면 자동으로 바뀐 값을 반영하는 가상 테이블이다. 뷰를 응용하면 최종 결과를 구하기 위한 중간 테이블이나 원본 필드를 숨긴 보안 테이블을 만들 수 있다.

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰명

실행 SQL 구문(WHERE 조건)

[WITH CHECK OPTION | WITH READ ONLY] ;

DROP VIEW 뷰명;

•

FORCE | NOFORCE 

◦

FORCE : 기본 테이블이 없어도 뷰를 생성한다.

◦

NOFORCE : 기본 테이블이 없으면 뷰를 생성하지 않는다.

•

WITH CHECK OPTION : WHERE 절에 지정한 조건에 맞는 데이터만 입력한다.

•

WITH READ ONLY : 뷰의 속성을 일기 전용으로 지정한다.

예제1. 고객 테이블의 고객코드, 고객명, 성별, 생년월일, 전화번호 필드와 포인트 테이블의 일련번호, 포인트 내용, 포인트 필드로 구성된 뷰를 만든다.

CREATE OR REPLACE FORCE VIEW VW_PNT_STAT_INFO

AS

SELECT CU.CUSTOMER_CD,
	CU.CUSTOMER_NM,
	DECODE(CU.MW_FLG,'M','남','여') AS MW_FLG, -- 비교함수. 첫번째 인자가 두번째값과 같으면 세번째 인자로,다르면 네번째 인자로 변환
	CU.BIRTH_DAY,
	REPLACE(CU.PHONE_NUMBER,'-','') AS PHONE_NUMBER, -- 첫번째 인자값에 두번째 인자값에 해당하는 문자를 세번째 인자값으로 대체
	CP.SEQ_NO,
	CP.POINT_MEMO,
	CP.POINT
FROM TB_CUSTOMER CU, TB_POINT CP -- 조인
WHERE CU.CUSTOMER_CD = CP.CUSTOMER_CD
ORDER BY CU.CUSTOMER_CD, CP.SEQ_NO;

WITH READ ONLY; -- 뷰의 속성을 읽기 전용으로 지정
/
SQL
복사

예제2. 고객 정보와 포인트 정보를 가지는 뷰를 확인한다.

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW'
AND OBJECT_NAME = 'VW_PNT_STAT_INFO';
SQL
복사

예제3. 뷰의 내용을 확인한다.

SELECT *
FROM VW_PNT_STAT_INFO;
SQL
복사

예제4. 뷰의 바뀐 내용을 반영하기 전 VW_PNT_STAT_INFO 뷰에서 고객명이 ‘이혜옥’, ‘한찬희’, ‘나경숙’인 고객 데이터를 확인한다.

SELECT *
FROM VW_PNT_STAT_INFO
WHERE CUSTOMER_NM IN ('이혜옥','한찬희','나경숙');
SQL
복사

예제5. 고객 테이블에서 ‘이혜옥’, ‘한찬희’ 고객은 전화번호 구분자를 ‘-’ 문자로 바꾸고, 포인트 테이블에서 ‘나경숙’ 고객은 일련번호가 2인 데이터를 삭제한다.

UPDATE TB_CUSTOMER -- 테이블의 내용 바꾸기
SET PHONE_NUMBER = '003-1287-9734' -- 이혜옥 고객의 전화번호 형태 바꾸기(기존은 '_')
WHERE CUSTOMER_CD = '2018254'; -- 이혜옥 고객


UPDATE TB_CUSTOMER
SET PHONE_NUMBER = '002-1202-5563' -- 한찬희 고객의 전화번호 형태 바꾸기(기존은 '=')
WHERE CUSTOMER_CD = '2019167' -- 한찬희 고객

DELETE
FROM TB_POINT
WHERE CUSTOMER_CD = '2017053' -- 나경숙 고객의 고객코드
AND SEQ_NO = 2; -- 일련번호가 2인 행
SQL
복사

예제6. 고객 테이블의 ‘이혜옥’, ‘한찬희’ 고객의 바뀐 내용과 포인트 테이블에서 삭제한 ‘나경숙’ 고객의 내용을 데이터베이스에 반영한다.

SQL
복사

예제7. VW_PNT_STAT_INFO 뷰에서 고객명이 ‘이혜옥’,’한찬희’,’나경숙’인 고객을 검색해 바뀐 내용을 확인한다.

SELECT *
FROM VW_PNT_STAT_INFO
WHERE CUSTOMER_NM IN ('이혜옥','한찬희','나경숙');
SQL
복사
결론은 기존의 테이블들로 만든 뷰는 기존의 테이블 내용을 바꾸고 저장하면 뷰의 내용도 따라서 반영한다.

예제8. VW_PNT_STAT_INFO 뷰를 삭제하고 확인한다.

DROP VIEW VW_PNT_STAT_INFO;

SELECT OBJECT_NAME,
	OBJECT_TYPE,
	CREATED,
	STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW'
AND OBJECT_NAME = 'VW_PNT_STAT_INFO';
SQL
복사

18.5 시퀀스

시퀀스(SEQUENCE)는 초깃값에서 최댓값까지 일정 간격의 증갓값을 가지는 번호를 만든다. 테이블 작업에서 시퀀스 값을 기본키로 설정하면 중복되지 않는 레코드를 입력할 수 있다.

CREATE SEQUENCE 시퀀스명

[START WITH 초깃값]

[INCREMENT BY 증갓값]

[MAXVALUE 최댓값 | NOMAXVALUE]

[MINVALUE 최소값 | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE | NOCACHE] ;

ALTER SEQUENCE 시퀀스명

[INCREMENT BY 증갓값]

[MAXVALUE 최댓값 | NOMAXVALUE]

[MINVALUE 최소값 | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE | NOCACHE] ;

시퀀스명.CURRVAL

시퀀스명.NEXTVAL

DROP SEQUENCE 시퀀스명;

•

CYCLE | NOCYCLE : 최댓값을 초과하면 초깃값부터 다시 설정할지를 설정한다.

•

CACHE | NOCACHE : 속도 개선을 위해 시퀀스 정보를 메모리에서 관리할지 설정한다.

•

ALTER SEQUENCE 시퀀스명 : 시퀀스 설정을 변경한다.

•

시퀀스명.CURRVAL : 현재 시퀀스 값을 취득한다.

•

시퀀스명.NEXTVAL : 다음 시퀀스 값을 취득한다.

예제1. 중복 없는 판매 데이터 입력을 위해 초깃값을 1로 하고 9,999,999,999 까지 1씩 증가하는 일련번호 시퀀스를 만든다.

CREATE SEQUENCE SEQ_PAN_SEQ_NO
	START WITH 1
	INCREMENT BY 1
	MAXVALUE 9999999999
	NOMINVALUE
	NOCYCLE -- 최댓값까지만 사용한다.
	NOCACHE; -- 시퀀스 정보를 메모리에서 관리하지 않는다.
SQL
복사

예제2. SEQ_PAN_SEQ_NO 시퀀스의 현재 값과 다음 값을 검색한다.

SELECT SEQ_PAN_SEQ_NO.CURRVAL -- 현재 값 검색
FROM DUAL;

SELECT SEQ_PAN_SEQ_NO.NEXTVAL -- 다음 값 검색
FROM DUAL;
SQL
복사
시퀀스를 처음 만든 후 바로 현재 값을 구할 수 없다. 다음값으로는 1을 검색해준다.

기본적으로 처음 시퀀스의 현재 값은 시퀀스의 다음 값을 검색한 후 시퀀스 값을 1로 만든 후에 가능하다.

예제3. SEQ_PAN_SEQ_NO 시퀀스의 현재 값을 검색한다.

SELECT SEQ_PAN_SEQ_NO.CURRVAL
FROM DUAL;
SQL
복사
1이 나온다.

예제4. 9월 판매 테이블에 SEQ_PAN_SEQ_NO 시퀀스의 일련번호를 부여해 데이터를 입력한다.

INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'냉장고','2018254',4);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'세탁기','2019001',10);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'세탁기','2019167',2);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'세탁기','2019069',6);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'냉장고','2018254',3);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'선풍기','2019167',4);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'냉장고','2019001',2);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'냉장고','2019069',1);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'선풍기','2018254',3);
INSERT INTO TB_SALES_09 VALUES(SEQ_PAN_SEQ_NO.NEXTVAL,'선풍기','2019001',5);
SQL
복사

예제5. 9월 판매 테이블을 검색해 입력한 결과를 확인한다.

SELECT *
FROM TB_SALES_09;
SQL
복사

사실, 일련번호는 중복되지 않는 것에 의미가 있기 때문에 1에서 시작하든, 2에서 시작하든 100에서 시작하든 의미가 없다.

예제6. 일련번호로 입력한 내용을 데이터베이스에 반영한다.

SQL
복사

예제7. 시퀀스를 삭제한다.

DROP SEQUENCE SEQ_PAN_SEQ_NO;
SQL
복사