Neo's Programming

ROWID를 이용하지 않고 커서의 WHERE CURRENT OF 를 이용하여 업데이트와 삭제가 일어나도록 하는 작업이다.

이는 반드시 참조 커서가 있어야하며 FOR문 안에 업데이트 또는 삭제 쿼리가 존재해야한다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST

IS

CURSOR v_cursor IS

SELECT * FROM TABLE;

BEGIN

FOR list IN v_cursor LOOP

UPDATE TABLE

SET NO = 1

WHERE CURRENT OF v_cursor;

END LOOP;

EXCEPTION

WHEN OTHERS THEN 작업 내용

END;

FOR문에서의 커서는 자동적으로 OPEN, FETCH, CLOSE가 일어난다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST

IS

CURSOR v_cursor IS

SELECT * FROM TABLE;

BEGIN

FOR list IN v_cursor LOOP

처리 내용

END LOOP;

EXCEPTION

WHEN OTHERS THEN 처리 내용

END;

커서 상에서 인자 값도 당연히 넣을 수가 있다.

v_cursor(인자) 설정을 하고 FOR문 상에서도 v_cursor(인자) 로 적용시켜서 동작시키면 된다.

개발자에 의해 정의되는 커서이다.

커서의 순서로는 OPEN->FETCH->CLOSE 순으로 일어난다.

먼저 예제를 보자.

CREATE OR REPLACE PROCEDURE TEST

IS

CURSOR v_cursor IS

SELECT * FROM TABLE;

v_no TABLE.NO%TYPE;

BEGIN

OPEN v_cursor;

FETCH v_cursor INTO v_no;

CLOSE v_cursor;

EXCEPTION WHEN OTHERS THEN 처리내용

END;

위에서 OPEN은 커서를 열어주는 역할을 한다. 커서 내부에서 검색이 일어나고 아무런 결과를 얻지 못다허라도 에러는 발생하지 않는다.

FETCH는 현재 데이터 행을 OUTPUT 변수에 반환하며 커서의 SELECT의 컬럼 수와 OUTPUT 변수 수가 동일해야하며 타입 역시 마찮가지이다. 이들은 한 ROW씩 데이터를 FETCH한다.

CLOSE는 마지막으로 커서를 닫아주는 역할을 한다.

암시적 커서는 오라클 또는 PL/SQL 실행에 의해 처리되는 SQL 작업소라고 생각을 하면 된다.

이들은 자동적으로 커서가 OPEN과 CLOSE가 일어나며 속성은 다음과 같다.

사용하는 방법은 다음 예제를 보면 알 수 있다.

CREATE OR REPLACE PROCEDURE TEST

IS

v_data TABLE.NO%TYPE;

BEGIN

SELECT NO

INTO v_data

FROM TABLE

WHERE NO = 1

IF 속성 THEN

처리내용

END IF

END;

암시적 속성의 경우 사용자가 직접 커서를 컨트롤 하는 것이 아니기 때문에 위와 같이 속성 값을 이용하여 체크가 가능하다.

반복문에는 FOR, LOOP, WHILE 세가지가 있다.

반복문 형태를 보면 다음과 같다.

FOR list IN (SELECT * FROM TABLE) LOOP

END LOOP;

FOR의 경우 list와 같은 부분의 인덱스는 자동 선언이 됨으로 신경 쓸 것은 되지 않는다.

LOOP

EXIT WHEN 조건;

END LOOP;

LOOP의 경우 FOR과는 달리 해당하는 마지막 조건이 없다. 그래서 꼭 EXIT WHEN을 써줘야한다.

WHILE 조건 LOOP

EXIT WHEN 조건;

END LOOP;

위와 같이 세가지 형태로 쓸 수 있다.거의 형태를 보면 VB에서 많이 본 것과 별반 다를 것이 없다.

조건문의 경우도 프로그램 코드상과 거의 유사하다.

SELECT NO FROM TABLE

IF NO = 1 THEN

ELSIF THEN

ELSE

END IF;

이러한 형태로 이용을 한다.

TABLE OF RECORD는 TABLE과 RECORD의 복합적인 기능을 하는 것이다.

선언은 TABLE변수 선언과 비슷하면서 데이터 타입은 %ROWTYPE으로 선언하면 된다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST(data IN TABLE.NO%TYPE)

IS

TYPE v_table IS TABLE OF TABLE%ROWTYPE

INDEX BY BINARY_INTEGER;

i BINARY_INTEGER := 0;

table v_table;

BEGIN

FOR list IN (SELECT * FROM TABLE WHERE NO = data) LOOP

i := i+1;

table(i).NO = list.NO;

END LOOP;

END;

PL/SQL 레코드는 여러개의 데이터 타입을 가지는 집합체이다. 개별적으로 필드 이름을 부여 가능하며 선언 시 초기화 역시 가능하다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST(data IN TABLE.NO%TYPE)

IS

TYPE v_record IS RECORD

(v_no NUMBER,

 v_NAME VARCHAR(10)

)

rec v_record;

BEGIN

SELECT NO, NAME

INTO rec.v_no, rec.v_name

FROM TABLE

WHERE NO = data;

END;

PL/SQL 상의 테이블은 일종의 1차원 배열형태이다.

테이블 크기에 제한이 없을 뿐 아니라 데이터가 들어옴에 따라 그 ROW 수 역시 증가를 하며, BINARY_INTEGER 타입의 INDEX로 순서가 정해진다. 또한 하나의 테이블에는 한 개의 컬럼 데이터를 저장한다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST(data IN TABLE.NO%TYPE)

IS

TYPE v_data IS TABLE OF TABLE.NO%TYPE

INDEX BY BINARY_INTEGER;

v_table v_data;

i BINARY_INTEGER := 0;

BEGIN

FOR list IN (SELECT NO FROM TABLE WHERE NO = data) LOOP

v_table(I) := list.NO;

END LOOP;

END;

로우 타입은 하나 이상의 데이터 값을 지니는 타입으로 배열과 같은 형태이다.

이는 테이블, 뷰의 컬럼 데이터형, 크기 등을 동일하게 사용 가능하며 앞에 나오는 것은 테이블 이름을 뜻한다.

주로 컬럼들의 수나 그 타입을 알지 못하는 경우 이용을 한다.

다음은 그 예제이다.

CREATE OR REPLACE PROCEDURE TEST(data IN TABLE.NO%TYPE)

IS

v_data TABLE%ROWTYPE;

BEGIN

SELECT NO INTO v_data.NO FROM TABLE WHERE NO = data;

END;

스칼라 데이터 타입에는 일반 변수형과 %TYPE을 이용하는 방법이 있다.

먼저 일반 변수형을 보면

v_data CONTANT NUMBER(1,1) := 1.1; --상수

v_data CHAR(1);

위와 같은 형태로 각기 변수 타입에 의해서 정의를 하여 쓰면 된다.

또 다른 방법으로 NOT NULL을 이용하여 NULL 값이 들어가지 않게 초기화 하는 방법이 있는데 그건 다음과 같다.

v_data BOOLEAN NOT NULL := TRUE;

%TYPE의 경우는 다음과 같다. 그 전에 특징을 알아보면 해당하는 컬럼의 타입을 모르는 경우나 코딩 후 그 타입이 바뀌었을 경우 또다시 수정 할 수 없어서 편리함이 있다고 할 수 있다.

다음이 그 예이다.

v_data TABLE.data%TYPE;