[펌] 오라클자료 - PL/SQL

블로그 > 하루...
http://blog.naver.com/dewlast/140001355787

PL/SQL REFERENCE MANUAL  (출처 : 오라클)

제 13장 PL/SQL

SQL은 국제표준화 기구들에 의해서 최소한의 표준 문법이 정의되어 있고 DBMS를 제공하는 각 벤더들은 이 표준을 따르고 있으며 부가적인 명령을 추가하여 SQL 제품을 출시하고 있다. 이러한 SQL은 절차적인(procedural) 언어라고 하는 BASIC, COBOL, C, Visual BASIC 등과 달리 비절차적인 구조로 구성이 되어 있다. 절차적인 언어들은 if, for, while 등과 같은 일련의 명령어들을 조합해서 데이터들을 가공하고 제어할 수 있는 반면, 비절차적인 언어인 SQL은 하나의 명령어 단위로 데이터를 관리하고 있는 DBMS에게 데이터를 요청하고, 변경 의뢰하는 방식으로 운영되기 때문에 여러개의 SQL 명령을 조합해서 하나의 프로그램으로 구성할 수 없다는 제한사항이 있다.

이와 같은 SQL의 제한사항을 해소하는 일반적인 방법으로 Visual BASIC으로 작성된 프로그램 내에서 SQL 명령어로 DBMS에게 데이터를 요청해서 읽혀진 데이터들을 Visual BASIC 명령어로 가공하여 사용하는 Client/Server 방식을 사용하게 된다. 이러한 방법은 비절차적인 언어인 Visual BASIC으로 작성된 프로그램 내에서 비절차적인 SQL 명령을 사용하여 데이터를 읽어들인 다음 Visual BASIC 명령어로 데이터를 표현하거나 가공함으로서 Client는 데이터를 요청, 가공하는 역할을 하게되고, Server는 데이터를 제공하고 유지하는 역할을 하게 된다.

하지만, 경우에 따라서 일련의 SQL 명령어들을 if, for, while 등과 같은 절차적인 언어에서 사용하는 명령어와 결합하여 함수나 프로그램을 구성해서 이것을 DBMS가 처리해 줄 수 있다면 SQL의 효율성이 높일 수 있을 것이다.

PL/SQL(Procedural Language/SQL)은 SQL의 명령어들을 대부분 그대로 사용하면서 if, for, while 등과 같은 비절차적인 언어 등을 추가하여 절차적인 프로그래밍이 가능하도록 SQL을 확장시킨 오라클사의 고유 제품이다.

13.1 PL/SQL 개요

PL/SQL로 작성된 프로그램에는 비절차적인 SQL 명령어와 절차적인 명령어들이 함께 존재하게 됨으로 PL/SQL 엔진은 그림 13-1과 같이 SQL 명령어와 Non-SQL 명령어를 따로 분리되어 SQL 명령어는 SQL 엔진으로 처리되고 Non-SQL 명령어는 PL/SQL 엔진에서 처리된다.

PL/SQL로 작성된 프로그램이 이름 없이 스크립트(script) 형태로 작성되었다면 오라클 DBMS에 의해 실행만 가능한 반면, 특정한 이름으로 저장하게 되면 하나의 오라클 개체로서 DBMS에 의해서 관리되므로 언제든지 DBMS에게 요청하여 실행시킬 수 있다.

PL/SQL은 명령어를 사용하면서 절차적인 프로그램이 가능하도록 지원함으로서 다음과 같은 몇 가지 장점을 제공한다.

◈ 모듈화된 프로그램 개발

º 자주 사용되는 기능을 함수로 구성하여 다른 프로그램에서 실행

º 블록 내에서 논리적으로 관련된 문장들의 그룹화

º 강력한 프로그램을 작성하기 위해 서브 블록들을 큰 블록에 포함

º 복잡한 문제에 대한 프로그래밍이 적절히 나뉘어진 모듈들의 집합으로 구성

◈ 절차적 언어 구조로 된 프로그램 작성

º 조건에 따라 일련의 문장을 실행 (IF)

º 루프에서 반복적으로 일련의 문장을 실행 (LOOP)

◈ CURSOR

º Cursor를 이용한 Multi-row 질의와 개별 row에 대한 처리

º 데이터베이스의 테이블과 Record를 기반으로 하는 dynamic한 변수 선언이 가능

◈ EXCEPTION, ERROR 처리

º Exception 처리 루틴을 이용하여 오라클 서버 에러를 처리

º 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리

◈ TRIGGER 처리

º 특정 사건(event) 이전 또는 이후에 지정한 일련의 특정 명령이 자동 실행

PL/SQL의 주요한 장점 중에 하나는 함수 프로그램을 작성하여 오라클 개체로 저장시켜 놓고 다른 프로그램에서 명령어처럼 실행할 수 있어서 프로그램을 단순화시킬 수 있고 Client/Server 환경에서는 Client에서는 Server에 PL/SQL로 작성된 개체명만 호출하면 됨으로 네트워크의 통신량을 줄일 수 있다. 또한, DBMS에 의해서 개별적인 개체로 관리되기 때문에 사용 권한에 대한 제어가 용이하다. 그리고, 커서(cursor) 기능을 사용하여 복수개의 행을 읽은 후에 하나씩 패치(fetch)하여 처리할 수 있으며, 예외(Exception) 기능을 이용하여 프로그램 내에서 예외적인 상황이 발생하면 프로그램이 비정상적으로 종료되지 않고 착오 사항을 인지하여 지정한 일련을 명령을 실행시킬 수 있다. Trigger 기능은 테이블에 대해서 변경이 이루어지는 이전 또는 이후를 자동적으로 인지하여 지정한 일련의 명령어들을 처리할 수 있게 한다.

13.2 PL/SQL 블록

PL/SQL 블록 구조는 3개 부분으로 나누어 정의된다. 첫 번째가 선언(declare) 부로 실행부에서 사용할 모든 변수와 상수 등을 선언하는 부분이다. 선언부는 정의하지 않아도 되는 부분이다. 두 번째는 블록의 핵심 부분인 실행부로서 데이터 처리를 위한 SQL 명령문과 PL/SQL 제어문이 포함되는 부분이다. 실행부는 반드시 명시되어야 하는 부분이다. 끝으로 예외(exception)부로 실행부분에서 Error나 비정상적인 조건이 발생했을 때 처리할 내용을 명시하는 부분이다.

표 13- 1 PL/SQL 블록 구성

부 분	기술 내용
DECLARE (선언부)	⼘선택적으로 명시 ⼘변수, 상수 선언 ⼘Cursor, 사용자가 선언할 Exception
BEGIN (실행부)	⼘반드시 명시 ⼘SQL과 PL/SQL 제어문
EXCEPTION (예외 처리부)	⼘선택적으로 명시 ⼘Error 또는 비정상조건 발생시 실행할 내용
END	⼘반드시 명시

13.2.1 변수 선언

DECLARE 절에서의 변수 선언은 기본적으로 변수명과 데이터형을 정의하고 문장 끝에 세미콜론(;)으로 종료시켜 주면 된다. 기타 선택적으로 변수에 대한 초기 값을 지정하기 위해서는 데이터형 뒤에 " := 10" 형태로 값을 지정하거나 산술식을 명시하면 된다. 만약 초기 값을 상수로 사용하길 원한다면 변수명 뒤에 CONSTANT라고 기술해주면 된다.

PL/SQL에서 사용하는 데이터형은 기본적으로 테이블 생성시 선언하는 데이터형을 똑같이 사용하고 있으며, 추가적으로 %TYPE 속성, %ROWTYPE 속성을 사용한다. 기본적인 데이터형을 사용하는 예는 표 13-2와 같고 %TYPE 속성, %ROWTYPE 속성을 사용하는 예는 표 13-3과 같다.

DECLARE 절의 변수 선언에 대한 문법은 다음과 같다.

구문법

Identifier [CONSTANT] Datatype [NOT NULL] [:= Value| DEFAULT 수식];

Identifier	변수나 상수 명
CONSTANT	상수로 사용할 경우 명시
Datatype	데이터 형식
Value	초기 값

표 13-2 변수 선언 예

DECLARE h_name CHAR(10) NOT NULL; rec_count NUMBER(3) := 0; score_average NUMBER(4,1); tax_rate CONSTANT NUMBER(3,1) := 10.0; in_date DATE := SYSDATE + 7; val_valid BOOLEAN NOT NULL := TRUE;

PL/SQL에서는 변수를 선언할 때 이미 선언된 변수나 특정 테이블의 칼럼이 갖는 데이터형을 상속받아 선언할 수 있다. 이렇게 하기 위해 %TYPE 속성을 이용한다.

표 13-3 예에서와 같이 %TYPE 속성은 상속받을 변수나 칼럼명 바로 뒤에 %TYPE라고 명시하게되면 해당 변수나 칼럼의 데이터형을 상속받게 된다. 테이블의 칼럼으로부터 상속을 받을 경우는 컬럼명 앞에 테이블명을 명시하여야 한다. 여기에서 데이터형을 상속받는다는 말은 상속한 변수나 칼럼의 데이터형이 바뀔 경우 상속받은 변수도 함께 바뀐다는 의미이다.

표 13-3 %TYPE 속성 사용 예

DECLARE e_name emp.ename%TYPE; v_name e_name%TYPE; row_emp emp%ROWTYPE;

PL/SQL에서는 변수를 선언할 때 레코드(record) 형태로 선언할 수 있다. 이때 사용하는 속성이 %ROWTYPE 속성으로서 특정 테이블의 row로부터 모든 칼럼명과 데이터형을 상속받는다. 상속받은 레코드 변수는 실행절에서 레코드 변수 전체로 사용하던지 특정 칼럼만을 사용할 수 있다. 특정 칼럼명만을 사용할 때는 표 13-4의 예에서와 같이 변수명.칼럼명 형태로 사용한다.

표 13-4 %ROWTYPE 속성 사용 예

DECLARE row_emp emp%ROWTYPE; BEGIN SELECT * INTO row_emp FROM emp WHERE empno = 7369; row_emp.deptno := 10;

13.2.2 제어 구조

PL/SQL에서 사용하는 제어 명령어는 IF 문과 LOOP 문이다. LOOP 문은 다시 LOOP 문을 빠져나가는 형태에 따라 기본 LOOP 문과 WHILE LOOP 문, FOR LOOP 문이 각각 존재한다.

⼆ LOOP

LOOP 문은 LOOP와 END LOOP 사이에 반복되는 문장을 배치하는 구조로 구성된다. LOOP와 END LOOP 사이에 EXIT 문 또는 [EXIT WHEN 조건]을 삽입함으로서 LOOP 문을 빠져나갈 수 있게 된다.

구문법

LOOP Statement-1; Statement-2; ...... EXIT [WHEN Condition]; END LOOP;

실습을 위해 다음과 같이 test_loop 테이블을 생성하자.

SQL> CREATE TABLE test_table (

record_number INT NOT NULL,

current_date DATE,

CONSTRAINT pk_recnum PRIMARY KEY (record_number));

⼧실습예제: 위에서 생성한 test_table 테이블에 LOOP 문을 사용하여 10개의 행을 생성(insert) 해보자.

SQL	DECLARE max_records CONSTANT INT := 10; i INT := 0; BEGIN LOOP i := i + 1; INSERT INTO test_table (record_number, current_date) VALUES(i, sysdate); EXIT WHEN i >= max_records; END LOOP; COMMIT; END; /

PL/SQL 처리가 정상적으로 완료되었습니다.

PL/SQL 블록은 SQL*Plus에서 SQL과 같이 입력하고 편집, 실행이 가능하다.

위의 실습문제를 입력하고 실행할 때는 맨 끝에 /를 해주면 SQL*Plus의 버퍼에 저장되면서 실행이 된다.

실습을 해보면 알겠지만 위 실습문제를 실행시켰을 때 "PL/SQL 처리가 정상적으로 완료되었습니다." 라고 메시지가 뿌려짐을 알 수 있다. 따라서 실행 결과를 확인하기 위해서는 test_table 테이블을 직접 select해 보아야 한다.

⼆ WHILE ... LOOP

WHILE LOOP 문은 LOOP 문에서 loop를 빠져나가는 조건을 LOOP 절 안에 EXIT 문을 쓰는 것 대신에 LOOP 문을 "WHILE 조건 LOOP"문으로 명시하는 점이 다르다. 그러므로 WHILE LOOP을 진입하기 전에 조건을 비교하여 조건이 맞으면 LOOP절 안에 있는 문장들을 수행한다.

구문법

WHILE Exit_Condition LOOP Statement-1; Statement-2; ...... END LOOP;

아래 실습예제의 결과를 보기 위해서는 다음 SQL*Plus 명령문을 먼저 실행시켜야 한다.

SQL> SET SERVEROUTPUT ON

위에서 SQL*Plus의 SERVEROUTPUT 환경변수를 활성화시키게 되면 오라클 서버가 실행한 결과를 모니터를 통해서 볼 수 있게 된다.

⼧실습예제: WHILE LOOP 문을 사용하여 다음 실습을 하여보자.

SQL

DECLARE i INT := 0; sel_row test_table%ROWTYPE; output_line VARCHAR2(80); BEGIN DBMS_OUTPUT.ENABLE; WHILE i < 10 LOOP i := i + 1; SELECT * INTO sel_row FROM test_table WHERE record_number = i; output_line := to_char(sel_row.record_number, '99') || ' ' || to_char(sel_row.current_date,'yy/mm/dd'); DBMS_OUTPUT.PUT_LINE(output_line); END LOOP; END; /

1 00/08/19 2 00/08/19 3 00/08/19 4 00/08/19 5 00/08/19 6 00/08/19 7 00/08/19 8 00/08/19 9 00/08/19 10 00/08/19 PL/SQL 처리가 정상적으로 완료되었습니다.

위의 실습 예제에서 DBMS_OUTPUT.PUT_LINE(output_line); 문장은 output_line 변수에 저장된 내용을 모니터를 통해 출력하라는 명령이고, 그 이전에 DBMS_OUTPUT.ENABLE; 문장은 DBMS_OUTPUT명령을 사용할 수 있도록 활성화시키는 명령이다.

⼆ FOR ... LOOP

FOR LOOP 문은 LOOP 실행 조건을 변수와 함께 하한 값과 상한 값을 지정해주는 방법이다. FOR LOOP 문을 사용할 때는 다른 LOOP 문과 달리 선언부에 조건문에서 사용할 변수를 선언할 필요가 없다. 조건문에 REVERSE라고 선언하면 Index 값은 End_value 값을 Start_value 값이 될 때까지 1씩 감소시키면서 실행된다.

구문법

FOR Index IN [REVERSE] Start_value .. End_value LOOP Statement-1; Statement-2; ...... END LOOP;

⼧실습예제: 이전에 WHILE LOOP로 실습한 내용을 FOR LOOP 문으로 바꾸어 실습하여 보자.

SQL

DECLARE sel_row test_table%rowtype; output_line varchar2(80); BEGIN DBMS_OUTPUT.ENABLE; FOR i IN 1..10 LOOP SELECT * INTO sel_row FROM test_table WHERE record_number = i; output_line := to_char(sel_row.record_number, '99') || ' ' || to_char(sel_row.current_date,'yy/mm/dd'); DBMS_OUTPUT.PUT_LINE(output_line); END LOOP; END; /

⼆ IF

PL/SQL에서의 IF 문은 절차적인 언어에서의 IF 문과 유사하다.

구문법

IF Condition THEN Statement-1; ...... [ELSIF Condition THEN Statement-2; ......] [ELSE Statement-3; ......] END IF;

IF문 다음에 또 다른 조건을 사용하고자 할 때는 IF 대신에 ELSIF를 반복해서 사용하면 되고 마지막에는 ELSE를 사용하면 된다. 이때 주의할 점은 ELSEIF가 아니라 ELSIF란 점과 마지막에 ELSE는 단 한 번밖에 사용할 수 없다는 점이다.

13.2.3 예외사항 처리

문법적으로 이상이 없는 PL/SQL 블록을 실행하는 중에 착오가 발생하면 실행이 중단되는 경우가 있는데 이러한 경우에 상황별로 조치할 수 있는 예외처리부분을 작성해 넣을 수 가 있다.

예외처리부에서 사용할 수 있는 예외 상황은 크게 오라클 서버가 자동적으로 인지하는 미리 정의된 Oracle Server 예외와 사용자가 정의 해놓고 사용하는 사용자 정의 예외가 있다.

(1) 미리 정의된 Oracle Server 예외

미리 정의된 예외란 오라클 서버가 인지한 착오 상황으로서 우리가 자주 접하게 되는 오라클 착오 번호 ORA_00001과 같은 착오에 해당한다.

미리 정의된 Oracle Server 예외 유형은 표 13-5와 같다.

표 13-5 미리 정의된 Oracle Server 예외

예외 이름	발생 상황
DUP_VAL_ON_INDEX	유일값 중복 Error
INVALID_NUMBER	Data type 불일치 Error
NO_DATA_FOUND	데이터를 반환하지 않은 SELECT 문
TOO_MANY_ROWS	두 행 이상을 반환한 SELECT 문
VALUE_ERROR	대입되는 값이 길이 초과
ZERO_DEVIDE	0으로 나누기 시도

구문법

EXCEPTION WHEN Exception_name_1 [OR Exception_name_2 ... ] THEN Statement-1; ...... [WHEN Exception_name_3 [OR Exception_name_4 ... ] THEN Statement-2; ......] [WHEN OTHERS THEN Statement-3; ......]

여기에서 Exception_name 부분에는 미리 정의된 오라클 서버 예외나 사용자가 정의한 예외 명을 써주면 된다.

WHEN OTHERS THEN 다음에는 명시적으로 선언되지 않은 모든 예외에 대한 처리 루틴을 포함시키면 된다.

만약, 예외가 발생하면 오라클 서버는 블록을 벗어나기 전에 하나의 예외 처리만 수행한다. 다시 말해 복수 개의 예외가 발생할 수 있는 블록이라면 가장 먼저 발생된 예외 상황에 대해서만 예외 처리가 된다는 의미이다.

⼧실습예제: 다음의 예외처리에 대한 예제를 실행시켰을 때 어떤 메시지가 출력될 지 예측하여 보시오.

Ⰱ, Ⰲ, Ⰳ를 차례로 활성화시키면서 실행시킨 결과를 예측하여 보시오

SQL

DECLARE I int :=1; sel_row test_table%rowtype; BEGIN --Ⰱ UPDATE test_table SET record_number = 'A' -- WHERE record_number = 1; --Ⰲ INSERT INTO test_table VALUES(1, SYSDATE); --Ⰳ SELECT * INTO sel_row FROM test_table -- WHERE record_number = 21; SELECT * INTO sel_row FROM test_table; EXCEPTION when DUP_VAL_ON_INDEX then dbms_output.put_line('유일성 중복!'); when INVALID_NUMBER then dbms_output.put_line('Data type 불일치!'); when NO_DATA_FOUND then dbms_output.put_line('일치 자료 없음!'); when TOO_MANY_ROWS then dbms_output.put_line('둘 이상 검색!'); when OTHERS then dbms_output.put_line('!!!'); END; /

(2) 사용자 정의 예외

사용자 정의 예외처리는 오라클 서버에 의해 발생될 수 없는 예외 상황에 대해서 사용자가 임의로 정의하여 강제로 발생되게 하는 방법이다.

예외명은 DECLARE절에서 선언하고, 예외가 발생해야할 조건은 실행부분에 정의하면 된다. 예외 상황 발생시 처리해야할 루틴은 미리 정의된 Oracle Server 예외에서와 같이 명시해주면 된다. 단지 사용자가 정의한 예외명을 적어주면 된다.

구문법

DECLARE Exception_name EXCEPTION; ...... BEGIN ...... RAISE Exception_name; ...... EXCEPTION WHEN Exception_name THEN Statement-1; ......

⼧실습예제: test_table의 행(Row)의 수를 검사한 다음 행의 수가 5이상이면 예외 처리하는 블록을 작성하시오.

SQL

DECLARE row_cnt int :=0; output_line varchar2(80); exception_row_over_5 EXCEPTION; BEGIN SELECT COUNT(*) INTO row_cnt FROM test_table; IF row_cnt > 5 THEN RAISE exception_row_over_5; END IF; EXCEPTION WHEN exception_row_over_5 THEN output_line := 'Row_Count= ' || row_cnt; dbms_output.put_line(output_line); END; /

13.2.4 커서

위의 예외처리에서 보았듯이 PL/SQL에서는 복수개의 행을 SELECT할 경우에 TOO_MANY_ROWS 예외가 발생하므로 한 행씩 차례대로 읽어들여 처리하지 못하게 된다. PL/SQL에서 이와 같은 문제를 해결할 수 있는 기능을 갖는 것이 커서(cursor) 이다.

커서는 질의에 의해 반환된 행들을 한 행씩 차례대로 이동시켜 가며 처리할 수 있게 한다.

기본적으로 커서는 PL/SQL 블록 내에서 DECLARE절에서 커서를 선언하고, BEGIN절에서 커서를 열어서, 커서를 이용하여 데이터 행을 추출하여 처리하고, 마지막으로 커서 닫기 순으로 사용한다.

커서 선언

DECLARE CURSOR Cursor_name IS; Select_statement;

커서 선언은 DECLARE절에 CURSOR문 다음에 커서 명을 명시하고 그 뒤에 IS와 함께 SELECT절을 명시하면 된다. 이 때의 SELECT절은 WHERE절이나 BY절을 사용할 수 있다.

BEGIN절에서 커서를 사용할 때는 먼저 커서를 OPEN 하고, FETCH문을 이용하여 한 행씩 변수로 읽어들여 처리를 하면 된다. 이 때 계속해서 다음 행을 읽어들이기 위해서 LOOP절 안에 FETCH문을 위치시키게 된다.

FETCH 작업이 모두 끝나면 커서를 닫으면 된다.

커서 사용 (1)

BEGIN ...... OPEN Cursor_name; LOOP FETCH Cursor_name INTO variable1 [,variable2 ... ]; EXIT WHEN Cursor_name%NOTFOUND; ...... END LOOP CLOSE Cursor_name; ......

⼧실습예제: test_table을 읽어서 "1 : yyyy/mm/dd" 형태로 출력되도록 PL/SQL 블록을 작성하시오.

SQL

DECLARE row_test test_table%ROWTYPE; output_line varchar2(80); CURSOR test_cursor IS SELECT * FROM test_table; BEGIN OPEN test_cursor; LOOP FETCH test_cursor INTO row_test; EXIT WHEN test_cursor%NOTFOUND; output_line := row_test.record_number || ' : ' || TO_CHAR(row_test.current_date, 'yyyy/mm/dd'); dbms_output.put_line(output_line); END LOOP; CLOSE test_cursor; END; /

1 : 2000/08/19 2 : 2000/08/19 3 : 2000/08/19 4 : 2000/08/19 5 : 2000/08/19 6 : 2000/08/19 7 : 2000/08/19 8 : 2000/08/19 9 : 2000/08/19 10 : 2000/08/19 PL/SQL 처리가 정상적으로 완료되었습니다.

위 예제에서 test_cursor%NOTFOUND는 커서의 상태를 구하는 방법으로서 결과는 True 또는 False로 반환된다. 커서의 속성들은 표 13-6과 같다.

표 13-6 커서 속성

속 성	반환 유형	반환 내용
%ISOPEN	Boolean	커서가 open되어 있으면 True
%NOTFOUND	Boolean	더 이상 fetch할 레코드가 없으면 True
%FOUND	Boolean	더 이상 fetch할 레코드가 있으면 True
%ROWCOUNT	Number	현재까지 fetch한 행의 수

앞에 예제에서는 FETCH를 반복하기 위해서 LOOP문을 사용하였는데 이것을 다음과 같이 FOR LOOP 문으로 대치할 수 있다.

커서 사용 (2)

BEGIN ...... FOR variable IN Cursor_name LOOP ...... END LOOP; ......

이와 같이 FOR LOOP 문에 커서명을 명시하게 되면 Fetch하여 저장할 변수나 레코드 변수를 별도로 DECLARE절에서 선언할 필요가 없고, BEGIN절에서 커서를 OPEN하고 CLOSE할 필요가 없으므로 PL/SQL 블록이 아주 단순해진다.

⼧실습예제: 앞의 실습예제를 FOR LOOP를 사용하는 방법으로 PL/SQL 블록을 바꾸어보시오.

SQL	DECLARE output_line varchar2(80); CURSOR test_cursor IS SELECT * FROM test_table; BEGIN FOR row_test IN test_cursor LOOP output_line := row_test.record_number || ' : ' || TO_CHAR(row_test.current_date, 'yyyy/mm/dd'); dbms_output.put_line(output_line); END LOOP; END; /

⼆ WHERE절에서의 커서 사용

PL/SQL에서는 WHERE절에서도 커서를 사용할 수 있다.

WHERE절에서 커서를 사용하기 위해서는 커서를 선언할 때 SELECT절 뒤에 FOR UPDATE문을 추가하여야 한다.

WHERE절에서 커서 사용

DECLARE CURSOR Cursor_name IS; Select_statement FOR UPDATE; BEGIN ...... WHERE CURRENT OF Cursor_name; ......

WHERE절에서의 커서 사용은 현재 커서가 가리키고 있는 행에 대해 UPDATE나 DELETE 작업을 할 때 유용하다.

⼧실습예제: test_table에서 record_number가 5보다 큰 행의 current_date에 7일을 더하여 갱신하는 PL/SQL 블록을 커서를 이용하여 작성하시오.

SQL	DECLARE output_line varchar2(80); CURSOR test_cursor IS SELECT * FROM test_table WHERE record_number > 5 FOR UPDATE; BEGIN FOR row_test IN test_cursor LOOP UPDATE test_table SET current_date = current_date + 7 WHERE CURRENT OF test_cursor; END LOOP; END; /

⼆ Parameter가 있는 커서 사용

PL/SQL에서는 커서의 매개변수를 통해서 커서에 값을 전달할 수 있다.

Parameter가 있는 커서 사용

DECLARE CURSOR Cursor_name (parameter_name Data_type) IS; Select_statement; BEGIN ...... OPEN Cursor_name(parameter_name); ...... CLOSE Cursor_name;

커서 parameter로의 값 전달은 커서를 OPEN할 때이다.

커서는 parameter를 통해 전달받은 값을 이용해서 SELECT절을 수행하게 되므로 커서를 융통성 있게 사용할 수 있는 효과가 있다.

⼧실습예제: test_table에서 parameter가 있는 커서를 이용해서 current_date가 SYSDATE보다 큰 행만을 출력하는 PL/SQL 블록을 작성하시오.

SQL

DECLARE output_line varchar2(80); CURSOR test_cursor(p_date DATE) IS SELECT * FROM test_table WHERE current_date > p_date; BEGIN FOR row_test IN test_cursor(SYSDATE) LOOP output_line := row_test.record_number || ' : ' || TO_CHAR(row_test.current_date, 'yyyy/mm/dd'); dbms_output.put_line(output_line); END LOOP; END; /

6 : 2000/08/26 7 : 2000/08/26 8 : 2000/08/26 9 : 2000/08/26 10 : 2000/08/26 PL/SQL 처리가 정상적으로 완료되었습니다.

13.3 서브프로그램

PL/SQL에서는 프로시저(Procedure)나 함수(Function)를 선언하여 사용할 수 있다. 선언되는 함수나 프로시저는 PL/SQL 블록 내에 위치할 수도 있고, 별도의 이름을 부여하여 오라클 개체로 저장하여 관리 될 수도 있다.

다음은 프로시저를 오라클 개체로 저장하여 다른 프로그램에서 호출하여 실행하는 예를 소개한다.

⼆ Stored Procedure

Stored Procedure는 오라클 개체로 관리되므로 프로시저 생성시 CREATE 구문을 사용하고 한다. CREATE문 다음에 PL/SQL 블록이 위치하게 되고 프로시저 작성이 끝나면 컴파일 되어 프로시저 이름으로 저장된다. 이 때의 PL/SQL 블록은 DECLEAR 문을 생략하고 바로 변수만 하여야 한다.

Stored Procedure

CREATE [OR REPLACE] PROCEDURE Procedure_name [(Parameter, ........)] IS PL/SQL_Block;

프로시저가 컴파일 될 때 에러가 발생하면 에러를 수정한 후 다시 컴파일 해야 하는데 이때 CREATE 문에 OR REPLACE를 명시하지 않으면 프로시저 이름 중복 에러가 발생한다. 이것은 컴파일 에러가 발생하더라도 프로시저가 등록되기 때문으로 다시 컴파일 할 때는 OR REPLACE문을 포함시켜야 한다. 그렇지 않으면 해당 프로시저를 삭제하고 다시 생성해야 한다.

프로시저를 삭제하는 명령은 다음과 같다.

DROP PROCEDURE Procedure_name

프로시저를 생성할 때 데이터를 전달받고 반환할 parameter를 선언할 수도 있고 선언하지 않아도 된다. parameter 선언 방법은 다음과 같다.

Parameter 선언

[(Parameter_name [IN | OUT | IN OUT] Datatype, ........)]

여기에서 IN은 실행 환경으로부터 프로시저로 값을 전달받는 parameter이고 OUT은 프로시저로부터 실행 환경으로 값을 전달하는 parameter이다. IN OUT은 IN과 OUT 기능을 모두 포함하는 parameter이다.

⼧실습예제: test_table의 행의 수를 출력해주는 proc_test_count 프로시저를 생성해보자.

SQL	CREATE OR REPLACE PROCEDURE proc_test_count IS output_line varchar2(80); rec_cnt int := 0; BEGIN SELECT COUNT(*) INTO rec_cnt FROM test_table; output_line := 'Record Count = ' || rec_cnt; dbms_output.put_line(output_line); END; /

프로시저가 생성되었습니다.

Procedure를 실행한 후에 컴파일시 발생한 착오사항을 확인하려면 다음과 같이 SQL*Plus 명령을 사용하면 된다.

SQL> SHOW ERRORS

프로시저가 정상적으로 컴파일 되었을 때 "프로시저가 생성되었습니다." 라는 메시지만 반환된다. 이것은 프로시저가 정상적으로 컴파일 되어 오라클 서버에 의해 Procedure_name으로 관리된다는 의미이다.

따라서 생성된 프로시저를 실행시키기 위해서는 다음과 같이 프로시저를 실행시켜야 한다.

SQL> EXECUTE proc_test_count

Record Count = 10

PL/SQL 처리가 정상적으로 완료되었습니다.

생성된 프로시저를 확인하는 명령은 다음과 같다.

SQL> SELECT text FROM user_source

WHERE name = 'PROC_TEST_COUNT';

이 飁 Procedure_name은 반드시 대문자로 명시해 주어야 한다.

⼧실습예제: 앞의 실습예제에서 생성된 proc_test_count 프로시저를 호출하는 PL/SQL 블록을 작성하시오.

SQL	DECLARE BEGIN proc_test_count; END; /

Record Count = 10 PL/SQL 처리가 정상적으로 완료되었습니다.

⼆ Stored Function

Function는 다음 두 가지 점에서 Procedure와 다르다.

⼘Function은 값을 반드시 반환하고 프로시저는 선택적이다.

⼘Function은 식의 일부로서 사용된다.

Function의 생성 방법은 Procedure와 유사하고 반드시 반환될 데이터형을 명시해야 한다는 점이 다르다.

Function은 한 가지 값만을 반환함으로 parameter 선언부에는 OUT, IN OUT은 사용하면 안되고 IN만을 선택적으로 사용해야 한다.

Stored Function

CREATE [OR REPLACE] FUNCTION Function_name [(Parameter, ........)] RETURN datatype IS PL/SQL_Block;

Function의 PL/SQL_Block문에는 값을 반환하기 위해서 다음과 같이 RETURN()문을 사용하여야 한다.

RETURN( {Variable | Expression});

RETURN되는 parameter는 변수나 수식 또는 상수 중에 하나를 사용할 수 있다.

⼧실습예제: 함수로 전달된 값과 test_table의 record_number 값을 비교하여 record_number가 큰 행의 수를 반환하는 func_test_count 함수를 작성하시오.

SQL	CREATE OR REPLACE FUNCTION func_test_count (rec_num IN INT) RETURN INT IS rec_cnt int := 0; BEGIN SELECT COUNT(*) INTO rec_cnt FROM test_table WHERE record_number > rec_num; RETURN(rec_cnt); END; /

프로시저가 생성되었습니다.

⼧실습예제: func_test_count함수의 실행 결과를 출력하는 PL/SQL 블록을 작성하시오.

SQL	DECLARE output_line varchar2(80); check_num int := 5; BEGIN output_line := 'Check Count = ' || func_test_count(check_num); dbms_output.put_line(output_line); END; /

Check Count = 5 PL/SQL 처리가 정상적으로 완료되었습니다.