[ SQL ] Oracle PL/SQL #3 _ 제어문 & 반복문

Programming/SQL 기초

[ SQL ] Oracle PL/SQL #3 _ 제어문 & 반복문

yul_S2 2022. 12. 11. 15:47

PL/SQL 을 통한 쿼리의 확장

1. 저장 프로시저

(1) 저장 프로시저

저장 프로시저(Stored Procedure)는 개발자가 만든 PL/SQL 문을 데이터베이스에 저장하고 필요한 경우에 여러 번 호출하여 사용할 수 있다.
저장 프로시저는 자주 사용되는 쿼리문을 모듈화시켜서 필요할 때마다 호출하여 사용할 수 있다.
저장 프로시저를 사용하면 복잡한 데이터 조작어를 필요할 때마다 다시 입력할 필요 없이 간단하게 호출만 해서 실행 결과를 얻을 수 있다.
저장 프로시저를 사용하면 성능도 향상되고 호환성 문제도 해결된다.
저장 프로시저를 사용하면 여러 클라이언트가 업무 규칙을 공유할 수 있다.
저장 프로시저는 다양한 클라이언트 응용 프로그램에서 서버에 보낼 SQL 문들을 미리 모아서 서버에서 관리하는 데이터로 저장해 두므로 여러 클라이언트에서 서버에 저장해 둔 SQL 문을 함께 사용할 수 있다.
저장 프로시저는 업무처리 규칙이 바뀌었을 때도 모든 클라이언트 응용 프로그램이 서버에 바뀐 업무처리 규칙을 공유할 수 있으므로 클라이언트마다 따로 조치를 처리해 줄 필요가 없다.
저장 프로시저는 속도문제에 있어서 다음과 같은 장점:
-- 저장 프로시저는 서버에서 SQL 구문 검사를 끝난 상태에서 대기하고 있다가 호출만 되면 바로 실행을 한다는 것인데 저장 프로시저로 처리하지 않으면 일일이 SQL 문을 보내서 구문이 실행될 때마다 SQL 구문 검사가 매번 실행되어 속도가 느려진다.
-- 저장 프로시저는 네트워크에서 오고 가는 긴 SQL 문의 네트워크 트래픽도 줄일 수 있는데 복잡한 SQL 코딩은 그 라인이 몇백 라인도 될 수 있으며 많은 SQL 이 네트워크에서 오간다면 네트워크 트래픽을 무시하지 못하지만, 저장 프로시저는 저장 프로시저의 이름만 호출하기 위해 네트워크에서 왔다 갔다 하므로 비교적 훨씬 적은 데이터가 네트워크상에서 움직인다.
-- 오라클에서 지원하는 저장 프로시저는 다른 프로그래밍 언어에서 사용하는 함수의 개념과 비슷하다.
-- 저장 프로시저는 클라이언트 응용 프로그램에서 반복적으로 같은 처리를 할 때 미리 SQL 문을 서버에 저장해 두고 클라이언트에서는 단순히 저장 프로시저를 매개변수와 함께 호출만 해주면 서버에서 해당 저장 프로시저를 읽어서 곧바로 실행하게 된다.
-- PL/SQL 에서 바인드 변수를 사용하여 출력할 수 있다.

바인드 변수명

바인드 변수는 PL/SQL 외부에서도 사용할 수 있는 변수를 의미한다.
바인드 변수는 호스트 환경에서 생성되어 데이터를 저장하기 때문에 호스트 변수라고 한다.
variable 명령어를 사용하여 PL/SQL 블록에서도 사용할 수 있다.
바인드 변수는 PL/SQL 블록이 실행된 후에도 액세스할 수 있다.
바인드 변수는 print 명령어를 이용하여 출력할 수 있다.
바인드 변수는 :(콜론)을 덧붙여 사용한다.
저장 프로시저의 매개변수와 바인드 변수의 이름은 같을 필요는 없지만, 데이터 타입은 반드시 같아야 한다.

(2) 저장 프로시저의 생성

저장 프로시저를 생성하려면 create procedure 문 다음에 새롭게 생성하고자 하는 프로시저명을 선언한다.
replace 명령어는 같은 이름으로 저장 프로시저를 생성할 때 기존 저장 프로시저를 삭제하고 새롭게 선언한 내용으로 재생성한다.
is 명령어는 저장 프로시저의 환경에 대한 존재를 확인하며 is 명령어를 생략하면 컴파일 오류가 발생한다.
PL/SQL 의 제어문을 통해서 저장 프로시저를 제어할 수가 있다.
drop procedure 문으로 저장 프로시저를 삭제 drop procedure 프로시저명;
생성된 저장 프로시저를 제거하기 위해서는 drop procedure 문 다음에 제거하고자 하는 프로시저명을 선언한다.
저장 프로시저는 어떤 값을 전달받아서 그 값에 의해서 서로 다른 결과물을 구하게 된다.
값을 저장 프로시저에 전달하기 위해서 프로시저명 다음에 ( )(퍼렌씨시스) 안에 전달받을 값을 저장할 인자인 매개변수를 기술한다.
저장 프로시저에 값을 전달해 주기 위해서 지정한 모드(mode)로 인자인 매개변수에 전달한다.
프로그래밍에서 모드는 특정한 방식을 의미한다

create[or replace] procedure 프로시저명(인자 1 [mode] 데이터 타입,
인자 2 [mode] 데이터 타입,…인자 N [mode] 데이터 타입)
is
begin
조건에 만족할 경우 실행되는 문 1;
조건에 만족할 경우 실행되는 문 2;
᎒
조건에 만족할 경우 실행되는 문 N;
end;

< in 모드 >

in 모드는 데이터를 전달받을 때 사용하며 매개변수에 입력한 값을 전달하는 것이다.
저장 프로시저에서 사용된 매개변수는 저장 프로시저를 호출할 때 값을 저장 프로시저 내부에서 받아서 사용할 수도 있는데 저장 프로시저 호출 시 넘겨준 값을 받아오기 위한 매개변수는 in 모드를 지정한다.

< out 모드 >

out 모드는 수행된 결과를 받아갈 때 사용하며 매개변수에 저장된 값을 호출하는 것이다.
저장 프로시저에 구한 결과값을 얻어 내기 위해서는 매개변수는 out 모드를 지정한다.

< inout 모드 >

inout 모드는 in 모드와 out 모드의 두 가지 목적에 모두 사용된다.

ex )

#1 저장 프로시저 생성을 위한 테스트 테이블을 복사한다

CREATE TABLE EMPCOPY
AS
SELECT * FROM EMP;

--Table EMPCOPY이(가) 생성되었습니다.

#2 생성한 테이블 정보를 조회한다

SELECT * FROM EMPCOPY;

#3 저장 프로시저를 생성한다.

CREATE OR REPLACE PROCEDURE DEL_ALL
IS
BEGIN
    --EMPCOPY 테이블의 저장된 데이터 삭제
    DELETE FROM EMPCOPY;
    --트랜잭션 작업이 성공하여 완료
    COMMIT;
END;

-- Procedure DEL_ALL이(가) 컴파일되었습니다.

#4 저장 프로시저를 실행한다.

EXECUTE DEL_ALL;

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

#5 저장 프로시저 실행을 확인한다.

SELECT * FROM EMPCOPY;

(3) 저장 프로시저의 조회

user_source 시스템 테이블인 데이터 사전으로 어떤 저장 프로시저가 생성되어 있는지와
해당 프로시저의 내용이 무엇인지 확인할 수 있다.
user_source 시스템 테이블의 컬럼 의미:
 name 컬럼 : 저장 프로시저의 프로시저명을 저장한다.
 type 컬럼 : 저장 프로시저의 타입을 저장한다.
 line 컬럼 : 행의 라인 번호를 저장한다.
 text 컬럼 : 저장 프로시저 코드를 저장한다.

ex )

#1 저장 프로시저의 구조를 확인한다.

DESC USER_SOURCE;

#2 저장 프로시저 코드 확인

SELECT NAME, TYPE, LINE, TEXT FROM USER_SOURCE;

(4) 저장 프로시저의 매개변수

ex )

#1 저장 프로시저 생성을 위한 테스트 테이블을 복사한다.

CREATE TABLE EMPCOPY2
AS
SELECT * FROM EMP;

-- Table EMPCOPY2이(가) 생성되었습니다.

#2 생성한 테이블 정보를 조회한다.

SELECT * FROM EMPCOPY2;

#3 매개변수가 존재하는 저장 프로시저를 생성한다

CREATE OR REPLACE PROCEDURE DEL_ENAME(SONENAME EMPCOPY2.ENAME % TYPE)
IS
BEGIN
DELETE FROM EMPCOPY2 WHERE ENAME LIKE SONENAME;
COMMIT;
END;

--Procedure DEL_ENAME이(가) 컴파일되었습니다.

#4 저장 프로시저를 실행한다.

EXECUTE DEL_ENAME('S%');

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

※ SQL Developer 에서 저장 프로시저 확인

⎼SQL Developer 에서 저장 프로시저를 실행하면 다음 그림과 같이 확인할 수 있다

(5) 바인드 변수를 통한 부서정보 조회

ex )

#1 부서 정보를 조회하는 저장 프로시저를 생성한다.

--매개변수가 존재하는 저장 프로시저 생성
CREATE OR REPLACE PROCEDURE DEPT_SELECT(
-- IN 모드로 DEPT 테이블의 DEPTNO컬럼에 입력한 값을 VDEPTNO 매개변수에 전달
VDEPTNO IN DEPT.DEPTNO%TYPE,
-- OUT 모드로 DEPT 테이블의 DNAME컬럼값을 받아와서 VDANME 매개변수에서 호출
VDNAME OUT DEPT.DNAME%TYPE,
-- OUT 모드로 DEPT 테이블의 LOC컬럼값을 받아와서 VLOC 매개변수에서 호출
VLOC OUT DEPT.LOC%TYPE)
IS
BEGIN
    -- SELECT 문으로 수행한 결과값을 INTO 문 뒤에 선언한 변수에 저장
    SELECT DNAME, LOC INTO VDNAME, VLOC FROM DEPT
    WHERE DEPTNO=VDEPTNO;
END;

--Procedure DEPT_SELECT이(가) 컴파일되었습니다.

#2 바인드 변수로 데이터를 저장하고 부서번호로 부서 정보를 조회한다.

VARIABLE VDENAME VARCHAR2(30);
VARIABLE VLOC VARCHAR2(30);
-- 바인드 변수를 호출하는 저장 프로시저를 실행하고 바인드 변수는 :(콜론)을 덧붙여 사용
EXECUTE DEPT_SELECT(40,:VDNAME, :VLOC);
-- 바인드 변수는 PRINT 명령어로 출력할 수 있고, PRINST 명령어로 출력
PRINT VDNAME;
PRINT VLOC;

컬럼에 저장된 값을 호스트 환경에서 생성하여 데이터를 저장하기 위해 바인드 변수를 선언한다.
variable 명령어를 사용하여 PL/SQL 블록에서도 사용할 수 있다.
저장 프로시저의 매개변수와 바인드 변수의 이름은 같을 필요는 없지만, 데이터 타입은 반드시 같아야 한다.

(6) 바인드 변수를 통한 사원 정보 조회

ex )

#1 사원정보를 조회하는 저장 프로시저를 생성한다.

CREATE OR REPLACE PROCEDURE SEL_EMPNO(
SONEMPNO IN EMP.EMPNO%TYPE,
SONENAME OUT EMP.ENAME%TYPE,
SONSAL OUT EMP.SAL%TYPE,
SONJOB OUT EMP.JOB%TYPE)
IS
BEGIN
    SELECT ENAME, SAL, JOB INTO SONENAME, SONSAL, SONJOB
    FROM EMP
    WHERE EMPNO=SONEMPNO;
END;

--Procedure SEL_EMPNO이(가) 컴파일되었습니다.

#2 바인드 변수로 데이터를 저장하고 사원 번호로 사원정보를 조회한다

--컬럼에 저장된 값을 호스트 환경에서 생성하여 데이터를 저장하기 위해 바인드 변수를 선언
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_SAL NUMBER;
VARIABLE VAR_JOB VARCHAR2(9);
-- 바인드 변수를 호출하는 저장 프로시저를 실행하고 바인드 변수는 :(콜론)을 덧붙여 사용
EXECUTE SEL_EMPNO(7369,:VAR_ENAME, :VAR_SAL, :VAR_JOB);
-- 바인드 변수는 print 명령어로 출력할 수 있으므로 print 명령어로 출력
PRINT VAR_ENAME;
PRINT VAR_SAL;
PRINT VAR_JOB;

SMITH

VAR_SAL
----------
800

VAR_JOB
--------------------------------------------------------------------------------
CLERK

(7) 저장 프로시저의 insert 데이터 조작어

ex )

#1 dept 테이블에 데이터를 입력할 저장 프로시저를 생성한다

CREATE OR REPLACE PROCEDURE DEPT_INSERT(
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달
VDEPTNO IN DEPT.DEPTNO%TYPE,
-- in 모드로 dept 테이블의 dname 컬럼에 입력한 값을 vdname 매개변수에 전달
VDNAME IN DEPT.DNAME%TYPE,
-- in 모드로 dept 테이블의 loc 컬럼에 입력한 값을 vloc 매개변수에 전달
VLOC IN DEPT.LOC%TYPE)
IS
BEGIN
INSERT INTO DEPT VALUES(VDEPTNO,VDNAME,VLOC);
END;

--Procedure DEPT_INSERT이(가) 컴파일되었습니다.

#2 dept 테이블에 데이터를 입력할 저장 프로시저를 실행한다.

EXECUTE DEPT_INSERT(70,'기획실','서울');

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

#3 dept 테이블에서 입력한 저장 프로시저 실행을 확인한다.

SELECT * FROM DEPT;

(8) 저장 프로시저의 update 데이터 조작어

#1 dept 테이블에 데이터를 수정할 저장 프로시저를 생성한다.

CREATE OR REPLACE PROCEDURE DEPT_UPDATE(
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달
VDEPTNO IN DEPT.DEPTNO%TYPE,
-- in 모드로 dept 테이블의 dname 컬럼에 입력한 값을 vdname 매개변수에 전달
VDNAME IN DEPT.DNAME%TYPE,
-- in 모드로 dept 테이블의 loc 컬럼에 입력한 값을 vloc 매개변수에 전달
VLOC IN DEPT.LOC%TYPE)
IS
BEGIN
UPDATE DEPT SET DEPTNO=VDEPTNO, DNAME=VDNAME, LOC=VLOC
WHERE DEPTNO=VDEPTNO;
END;

--Procedure DEPT_UPDATE이(가) 컴파일되었습니다.

#2 dept 테이블에 저장 프로시저로 데이터를 수정한다.

EXECUTE DEPT_UPDATE(50,'기획실','부산');

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

#3 dept 테이블에서 수정한 저장 프로시저 실행을 확인한다

SELECT * FROM DEPT;

(9) 저장 프로시저의 delete 데이터 조작어

#1 dept 테이블에 데이터를 삭제할 저장 프로시저를 생성한다

CREATE OR REPLACE PROCEDURE DEPT_DELETE(
-- in 모드로 dept 테이블의 deptno 컬럼에 입력한 값을 vdeptno 매개변수에 전달
VDEPTNO IN DEPT.DEPTNO%TYPE)
IS
BEGIN
DELETE FROM DEPT WHERE DEPTNO=VDEPTNO;
END;

--Procedure DEPT_DELETE이(가) 컴파일되었습니다.

#2 dept 테이블에 저장 프로시저로 데이터를 삭제한다.

EXECUTE DEPT_DELETE(50);

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

#3 dept 테이블에서 삭제한 저장 프로시저 실행을 확인한다.

SELECT * FROM DEPT;

2. 저장함수

저장 함수(Stored Function)는 자주 사용되는 쿼리문을 모듈화시켜서 필요할 때마다 호출하여 사용한다.
저장 함수는 저장 프로시저와 거의 유사한 용도로 사용하며 차이점은 저장 함수는 실행 결과를 반환할 수 있다는 점이다.
저장 함수를 생성하려면 create function 문 다음에 새롭게 생성하고자 하는 함수명을 선언한다.
replace 명령어는 같은 이름으로 저장 함수를 생성할 때 기존 저장 함수를 삭제하고 새롭게 선언한 내용으로 재생성한다.
return 명령어로 함수가 되돌려 받게 되는 데이터 타입과 되돌려 받을 값을 기술한다.
is 명령어는 저장 프로시저의 환경에 대한 존재를 확인하며 is 명령어를 생략하면 컴파일 오류가 발생한다.
생성된 저장 함수를 제거하기 위해서는 drop function 문 다음에 제거하고자 하는 함수명을 선언한다.
값을 저장 함수에 전달하기 위해서 함수명 다음에 ( )(퍼렌씨시스) 안에 전달받을 값을 저장할 인자인 매개변수를 기술한다.
저장 함수에 값을 전달해 주기 위해서 지정한 모드(mode)로 인자인 매개변수에 전달한다.
프로그래밍에서 모드는 특정한 방식을 의미한다.

create[or replace] function 함수명(인자 1 [mode] 데이터 타입,
인자 2 [mode] 데이터 타입, … 인자 N [mode] 데이터 타입)
return 데이터 타입;
is
begin
조건에 만족할 경우 실행되는 문 1;
조건에 만족할 경우 실행되는 문 2;
᎒
조건에 만족할 경우 실행되는 문 N;
end;

저장 함수는 호출 결과를 얻어오기 위해서 호출 방식에서도 저장 프로시저와 차이
: execute :변수명 := 함수명(인자 리스트);
drop function 문으로 저장 함수를 삭제
: drop function 함수명;
저장 함수에서 사용하는 모드의 형태는 다음과 같다.
(1) in 모드: 데이터를 전달받을 때 사용하며 매개변수에 입력한 값을 전달하는 것이다.
(2) out 모드: 수행된 결과를 받아갈 때 사용하며 매개변수에 저장된 값을 호출하는 것이다.
(3) inout 모드: in 모드와 out 모드의 두 가지 목적에 모두 사용된다.

(1) 급여 200% 보너스 지급

#1 사원의 급여에 200% 보너스를 지급하는 저장 함수를 생성한다.

-- 매개변수가 존재하는 저장 함수를 생성한다.
CREATE OR REPLACE FUNCTION CAL_BONUS(
-- in 모드로 emp 테이블의 empno 컬럼에 입력한 값을 sonempno 매개변수에 전달
SONEMPNO IN EMP.EMPNO%TYPE)
    -- number 데이터 타입의 값을 반환한다.
    RETURN NUMBER
IS
    SONSAL NUMBER(7,2);
BEGIN
    SELECT SAL INTO SONSAL
    FROM EMP
    WHERE EMPNO = SONEMPNO;
    -- 조회 결과로 얻어진 급여로 200% 보너스를 구해서 함수의 결과값으로 반환
    RETURN(SONSAL*2);
END;

-- Function CAL_BONUS이(가) 컴파일되었습니다.

#2 바인드 변수로 데이터를 저장하고 급여를 조회한다.

--컬럼에 저장된 값을 호스트 환경에서 생성하여 데이터를 저장하기 위해 바인드 변수를 선언
VARIABLE VAR_RES NUMBER;
-- 바인드 변수를 호출하는 저장 함수를 실행하고 바인드 변수는 :(콜론)을 덧붙여 사용
EXECUTE :VAR_RES :=CAL_BONUS(7369);
-- 바인드 변수는 print 명령어로 출력할 수 있으므로 print 명령어로 출력
PRINT VAR_RES;

VAR_RES
----------
1600

3. 커서

데이터베이스의 커서(Cursor)는 일련의 데이터에 순차적으로 액세스할 때 검색과 현재 위치를 포함하는 데이터 요소이다.
일반적으로 데이터베이스는 같은 종류의 데이터를 많이 축적하고 있으며 사용자가 요청하는 데이터는 1 건 또는 여러 건이 될 수 있다.
커서가 오픈되면 DB 버퍼 캐시에서 필요한 컬럼만 빼서 Cursor Area 에 복사하여 가져다 놓는다.
커서를 통해 Cursor Area 에 있는 데이터 위치를 찾아가서 데이터를 꺼내고 꺼낸 데이터를 PL/SQL 변수에 담는다.
사용자가 요청하는 데이터가 여러 건일 때 커서를 사용하여 처리할 수 있다.
PL/SQL 에서 처리 결과가 여러 개의 행으로 구해지는 select 문을 처리하려면 커서를 이용한다
오라클은 커서를 사용하여 여러 행을 조회하며 오라클 커서의 형태
-- 묵시적 커서 : 오라클에서 자동으로 선언해주는 SQL 커서다.
-- 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서다.
오라클에서는 커서의 속성을 통해 커서의 상태를 알려준다.
커서의 속성을 이용해서 커서를 제어해야 하는데 속성의 종류
(1) %rowcount 속성
-- %rowcount 속성은 커서가 얻어 온 총 행의 개수이다.
-- 가장 마지막 행이 몇 번째 행인지 카운트하고 해당 커서에서 실행한 총 행의 개수를 반환한다.
(2) %found 속성
-- %found 속성은 해당 커서 안에 아직 수행해야 할 데이터가 있을 때 true 를 반환하고 없을 때 false 를 반환한다.
(3) %notfound 속성
-- %notfound 속성은 해당 커서 안에 수행해야 할 데이터가 없을 때 true 를 반환하고 있을 때 false 를 반환한다.
(4) %isopen 속성
-- %isopen 속성은 현재 묵시적 커서가 메모리에 오픈되어 있을 때는 true 를 그렇지 않을 때는 false 를 반환한다

cursor 커서명
is
조건에 만족할 경우 실행되는 문;
begin
open 커서명;
fetch 커서명 into 변수명;
close 커서명;
end;

cursor 커서명
cursor 명령어 다음에 새롭게 생성하고자 하는 커서명을 선언한다.
is 명령어
커서의 환경에 대한 존재를 확인하며 is 명령어를 생략하면 컴파일 오류가 발생한다.
open 커서명;
생성한 커서를 연다.
질의를 수행하고 조회 조건을 충족하는 모든 행으로 구성된 결과 세트를 생성하기 위해 커서를 open 명령어로 커서를 연다.
fetch 커서명 into 변수명;
구성된 결과 세트에서 컬럼 단위로 데이터를 읽어 들이고 인출 한 후에 커서는 결과 세트에서 다음 행으로 이동한다.
fetch 명령어는 행에 대한 정보를 얻어 와서 into 명령어 뒤에 선언한 변수에 저장한 후에 다음 행으로 이동한다.
얻어진 여러 개의 컬럼에 대한 결과값을 모두 처리하려면 반복문에 fetch 명령어를 기술해야 한다.
close 커서명;
사용한 커서를 닫는다.
close 명령어는 커서를 사용할 수 없게 하고 결과 세트를 해제한다.
select 문이 처리되고 완성된 후에는 커서를 닫고 필요하다면 커서를 다시 열 수도 있다.

(1) 명시적 커서로 dept 테이블의 모든 데이터 조회

#1 dept 테이블의 모든 데이터를 조회할 프로시저를 생성한다.

CREATE OR REPLACE PROCEDURE CURSOR_CALL
IS
    -- %rowtype 속성으로 dept 테이블의 모든 컬럼을 참조하는 레퍼런스 sonemp
    SONDEPT DEPT%ROWTYPE;
    -- soncursor 커서를 생성한
    CURSOR SONCURSOR
    IS
    SELECT*FROM DEPT;
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 부서명 지역명');
    DBMS_OUTPUT.PUT_LINE('---------------------------');
    -- 생성한 soncursor 커서를 연다.
    OPEN SONCURSOR;
    -- end loop 문에서 제어권을 전달받고 반복
    LOOP
        -- soncursor 커서에서 행을 정보를 획득하고 저장하고 다음 행으로 이동
        FETCH SONCURSOR INTO SONDEPT.DEPTNO,SONDEPT.DNAME,SONDEPT.LOC;
        -- 해당 커서 안에 수행해야 할 데이터가 없을 때 루프를 종료
        EXIT WHEN SONCURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(SONDEPT.DEPTNO||''||SONDEPT.DNAME||''||SONDEPT.LOC);
    -- 제어권을 loop 문으로 전달
    END LOOP;
    -- 사용한 soncursor 커서를 닫기
    CLOSE SONCURSOR;
END;

--Procedure CURSOR_CALL이(가) 컴파일되었습니다.

#2 저장 프로시저 실행을 확인한다.

--화면 출력기능 활성화
SET SERVEROUTPUT ON
--저장 프로시저 실행 , dept테이블 모든 컬럼 출력
EXECUTE CURSOR_CALL;

4. 트리거

트리거(Trigger)는 데이터베이스가 미리 정해 놓은 조건을 만족하거나 어떤 동작이 수행되면 자동으로 수행되는 동작을 말한다.
오라클에서의 트리거는 어떤 이벤트가 발생하면 이벤트로 테이블이 자동으로 변경되게 하려고 사용한다.
트리거는 테이블의 데이터가 변경되면 자동으로 수행되므로 이 기능을 이용하며 여러 가지 작업을 할 수 있다.
트리거는 특정 동작을 이벤트로 하여 그 동작으로 인해서만 실행되는 저장 프로시저이다.
트리거를 생성하려면 create trigger 문 다음에 새롭게 생성하고자 하는 트리거명을 선언한다.
replace 명령어는 같은 이름으로 트리거를 생성할 때 기존 트리거를 삭제하고 새롭게 선언한 내용으로 재생성한다.
트리거는 자동으로 수행되므로 트리거를 사용자가 직접 실행시킬 수는 없다.
트리거는 연관된 작업을 처리하는 데 있어서 여러 번 프로시저를 호출해서 실행할 필요가 없으므로 복잡성을 줄일 수 있다는 장점이 있다.
프로젝트 수행 시 개발자들이 복잡한 업무를 숙지하지 않아도 되기 때문에 프로젝트를 안정적으로 수행할 수 있다는 장점도 있지만, 오류가 발생하면은 수정하기가 어려운 복잡성을 가지므로 주의해서 사용해야 한다.
트리거의 타이밍의 형태
-- before 타이밍 : insert, update, delete 문이 실행되기 전에 트리거를 실행한다.
-- after 타이밍 : insert, update, delete 문이 실행되고 난 후에 전에 트리거를
실행한다.

create[or replace] trigger 트리거명
{before|after}
{insert|update|delete} on 테이블명
begin
조건에 만족할 경우 실행되는 문;
end;

(1) 메시지 출력

#1 트리거 사용을 위한 테이블을 생성한다.

CREATE TABLE EMP_SON(
    EMPNO NUMBER(4) PRIMARY KEY,
    ENAME VARCHAR2(12),
    JOB VARCHAR2(21)
);

--Table EMP_SON이(가) 생성되었습니다.

#2 트리거를 생성한다

-- 트리거를 생성
CREATE OR REPLACE TRIGGER TRG_SON
-- insert 문이 실행되고 난 후에 트리거를 실행.
AFTER
-- emp_son 테이블에 데이터가 입력할 때 이벤트를 발생
INSERT ON EMP_SON
BEGIN
DBMS_OUTPUT.PUT_LINE('신입사원이 입사했습니다.');
END;

--Trigger TRG_SON이(가) 컴파일되었습니다.

#3 트리거를 실행한다.

-- 화면 출력기능을 활성화
SET SERVEROUTPUT ON
INSERT INTO EMP_SON VALUES(1, '홍길동', '대리');

--신입사원이 입사했습니다.

--1 행 이(가) 삽입되었습니다.