오라클 스터디

PL/SQL 익명 블록과 서브 프로그램의 차이

PL/SQL 블록에 이름을 지정하고 생성해서 저장해 두었다가 필요할 경우에 호출ㅇ해서 사용할 수 있도록 하는데 이를 서브프로그램 또는 프로그램 단위라 부른다.

대표 적인 유형으로 프로시저(PROCEDURE), 함수(FUNCTION), 패키지(PACKAGE), 트리거(TRIGGER) 가 있다

1. PROCEDURE (프로시저)

특정 작업을 처리하는 이름있는 PL/SQL BLOCK

프로시저 생성 - create procedure

프로시저 삭제 - drop procedure

프로시저 수정 - alter procedure

PROCEDURE 생성 문법 :

- OR REPLACE : 생성하고자 하는 PROCEDURE 가 기존에 동일 이름으로 존재할 경우, 기존의 내용을 현재 내용으로 수정하는 옵션, 이 옵션은 해당 이름의 PROCEDURE를 삭제한 후 다시 생성

- PROCEDURE_name : 생성하고자 하는 PROCEDURE 명으로 스키마 내에서는 유일하게 명명 되어야 한다

- parameter : PROCEDURE 를 실행할 때 호출 환경과 PROCEDURE 간 값을 주고 받을 때 사용되는 파라미터(매개변수)로 모드(IN,OUT,IN OUT) 에 따라 역할이 다르게 수행

PROCEDURE 생성시 선언부에 선언된 파라미터를 형식 파라미터

PROCEDURE 실행시 형식 파라미터에 실제 값이나 변수를 할당/대응 하는 파라미터를 실행 파라미터

- mode : 매개변수의 역할을 결정짓는 것

- IN 모드 : 사용자로부터 값을 입력받아 PROCEDURE 로 전달해 주는 역할을 하는 모드 (기본값으로 생략가능)

- OUT 모드 : PROCEDURE 에서 호출환경(SQL PLUS, 다른 프로시저 등)으로 값을 전달하는 역할

이 모드로 설정된 매개변수는 프로시저 내에서는 읽을 수 없으며, 값을 저장하기만 하는 지역변수처럼 사용 

            호출 환경에서는 이 매개변수로부터 값을 전달받기 위해 환경변수가 선언되어 있어야 함

      - IN OUT 모드 : 설정된 매개변수는 호출환경과 프로시저 간에 값을 주고 받는 지역변수로 사용되며 읽기,쓰기가능

생성된 PROCEDURE 의 내용을 확인할 때는 USER_SOURCE 딕셔너리를 활용

실습 1. 부서번호가 20번 인 사람들의 job 을 'CLERK' 으로 변경하는 PROCEDURE

실습 2. 사번을 입력 받아 급여를 인상하는 PROCEDURE

※ DB로 가서 데이터를 PL/SQL로 가져올 때 사용하는 변수는 3번째 줄과 4번째줄 사이 IS 와 BEGIN 사이에 입력하고,

2번 줄의 변수는 사용자로부터 데이터를 입력 받아서 PL/SQL 로 가져오는 역할을 하는 변수

실습 3. 사번을 입력 받아 그 사원의 이름과 급여를 출력하는 PROCEDURE

실습 4. OUT 모드 파라미터 사용 예

위에서 작성한 PROCEDURE 를 호출해서 값을 가져오는 익명 블록 PL/SQL 문을 작성

위 예에서 5번 줄을 보면 프로시저를 호출 할 때 값을 주는 것을 알 수 있습니다.

또는 아래와 같이 DBMS_OUTPUT.PUT_LINE 문장으로 사용도 가능

또 다른 방법으로는 별도의 변수를 선언해서 값을 수행해서 받은 후 출력할 수 도 있습니다.

2. FUNCTION (함수)

함수 생성 : CREATE FUNCTION

함수 삭제 : DROP FUNCTION

함수 수정 : ALTER FUNCTION

에러 발생시 : SHOW ERRORS 확인

함수 예제 1. 부서번호를 입력 받아 최고 급여액을 출력하는 함수

함수 예제 2. 부서번호를 입력 받은 후 해당 부서의 인원수를 구해주는 함수

함수 예제 3. 부서번호를 입력 받아 부서별로 평균 급여를 구해주는 함수

함수 예제 4. 사원번호를 입력 받아 해당 사원의 부서명을 알려주는 함수

함수 예제 5. 생성된 함수 조회하기

3. ORACLE PACKAGE (패키지) 

연관성이 높은 함수나 PROCEDURE 를 하나의 그룹으로 묶어서 관리하고 사용하는 개념

PACKAGE 는 PACKAGE 선언부(Spec)와 PACKAGE 몸체부(body) 로 구성

- PACKAGE 선언부 : 해당 패키지에 사용될 함수나 프로시저, 변수 등에 대한 정의를 선언

- PACKAGE 몸체부 : 선언부에서 선언된 함수나 ㅡㅍ로시저 등이 실제 CODE로 구현되는 부분

만약 생성된 PACKAGE의 선언부가 변경되었다면 무조건 PACKAGE 몸체부는 재 생성해야 하며, 패키지를 참조(호출)하는 서브 프로그램들도 재 번역(recompile)해야한다. 반대로 패키지 몸체부만 변경되는 경우라면 몸체부만 재생성하면 된다.

1. 패키지 실행 하기

생성된 패키지 오브젝트에 대한 실행 권한을 가진 사용자만이 패키지를 호출하여 실행 할 수 있다

2. 패키지 삭제

패키지 선언부와 몸체부 모두 삭제 : DROP PACKAGE PACKAGE_name ;

패키지 몸체부만 삭제 : DROP PACKAGE BODY PACKAGE_name ;

사용 예 1. Emp table 에서 총 급여 합계와 평균 급여를 구하는 emp_sal_mgr PACKAGE 입니다.

생성된 패키지 실행

위 패키지 실행결과가 맞는지 sql을 직접 수행해서 검증

사용 예2. member 테이블에 등록되어 있는 회원들의 성별과 아이디 검색과 비밀번호를 검색해주는 패키지

사용자 정보가 들어있는 member 테이블을 사용하여 회원관리를 하는 PL/SQL 패키지를 생성

이 패키지는 아래와 같은 프로시저로 구성

- 사용자 이름을 입력 받은 후 남자인지 여자인지를 구분하는 pro_gender 프로시저

- 사용자 이름과 주민번호를 입력 받아 회원의 아이디를 찾아주는 pro_find_id 프로시저

- 사용자 아이디와 연상단어를 입력 받아 회원의 비밀번호를 알려주는 pro_fin_pw 프로시저

- pro_gender PROCEDURE 테스트

- pro_find_id PROCEDURE 테스트

이름과 주민번호를 입력하면 아이디를 검색해주는 기능

- pro_find_pw PROCEDURE 테스트

4. TRIGGER (트리거)

테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL블록(또는 프로시저)으로 관련된 특정 사건(EVENT)이 발생될 때마다 묵시적(자동) 으로 해당 PL/SQL TRIGGER 블록이 실행됨

간단한 예로 테이블에 상품이 입고되면(EVENT 발생) 재고 테이블에 자동으로 재고가 증가하게 만드는것 

1) 주요 트리거 유형

(1) 단순 DML TRIGGER

- BEFORE TRIGGER

테이블에서 DML 이벤트를 TRIGGER 하기 전에 TRIGGER 본문을 실행 

즉 어떤 작업을 하기전에 TRIGGER를 먼저 수행 한 후 작업을 하는 경우

- AFTER TRIGGER

테이블에서 DML 이벤트를 TRIGGER 한 후에 TRIGGER 본문을 실행

- INSTEAD OF TRIGGER

TRIGGER 문 대신 TRIGGER 본문을 실행, 다른 방법으로는 수정이 불가능한 뷰에 사용

DML TRIGGER

- 문장 TRIGGER : 대상이 모든 행으로 영향을 받는 행이 없더라도 TRIGGER가 한 번은 실행 됨

예를들어 특정 테이블에 데이터를 입력할 수 있는 시간을 지정하는 경우 어떤 데이터가 들어오던지 간에 TRIGGER 를 적용

- 행 TRIGGER : 특정 행이 TRIGGER 이벤트의 영향을 받을 경우 해당 TRIGGER가 실행되고 영향을 받는 행이  없을 경우 TRIGGER 가 실행되지 않는다.

얘를들어 제품번호가 100번인 제품만 입력안되게 막고 싶을 경우 다른 행은 TRIGGER 와 상관없지만 100번 제품은 TRIGGER 가 적용되어 입력이 안됨

행 TRIGGER가 실행될 때 PL/SQL 런타임 엔진은 두개의 데이터 구조를 생성하고 채움

- OLD : TRIGGER 가 처리한 레코드의 원래 값(기존값) 을 저장

- NEW : 새로 입력되거나 변경되는 값을 의미

(2) 혼합 TRIGGER (11g 부터 추가)

여러가지 TRIGGER 를 하나로 만든 것으로 PL/SQL 의 패키지와 비슷한 개념

혼합 TRIGGER 가 주로 사용되는 시점

- 실행되는 문장 앞에

- 실행되는 문장이 영향을 주는 각 행 앞에

- 실행되는 문장이 영향을 주는 각 행 뒤에

- 실해오디는 문장 뒤에

혼합 TRIGGER 생성시 주의사항

- 혼합 TRIGGER 는 DML TRIGGER여야 하며 테이블이나 뷰에 정의해야 한다

- 혼합 TRIGGER의 본문은 PL/SQL 에서 작성한 혼합 TRIGGER 블록이어야 한다

- 혼합 TRIGGER 본문에는 초기화 블록이 포함될 수 없으므로 예외 섹션이 있을수 없다

- 한 섹션에서 발생하는 예외는 해당 섹션에서 처리되어야 한다

- :OLD 및 :NEW 는 선언, BEFORE STATEMENT 또는 AFTER STATEMENT 섹션에 나타날수 없다

- BEFORE EACH ROW 섹션만 :NEW 값을 변경할 수 있다

- FOLLOWS 절을 사용하지 않으면 혼합 TRIGGER 의 실행 순서가 일정하지 않는다

2) TRIGGERR 구조

트리거는 트리거가 실행되는 시점(Timing), 트리거를 실행시키는 사건(Event), 트리거와 관련된 테이블/뷰/스키마/데이터베이스 그리고 트리거 몸체부(body)로 구성

3) TRIGGER 관리

(1) 활성화/비활성화 하기

SQL>ALTER TRIGGER TRIGGER_name DISABLE | ENABLE ;

(2) 특정 테이블에 속한 TRIGGER의 활성화/비활성화

SQL>ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS ;

(3) TRIGGER 수정 후 다시 컴파일

SQL>ALTER TRIGGER tirgger_name COMPILE ;

(4) TRIGGER 삭제

SQL>DROP TRIGGER trigger_name ;

(5) TRIGGER 관련 권한

※ TRIGGER 를 생성, 변경 및 삭제할 수 있는 권한

- GRANT CREATE TRIGGER TO SCOTT ;

- GRANT ALTER ANY TRIGGER TO SCOTT ;

- GRANT DROP ANY TRIGGER TO SCOTT ;

※ 데이터베이스에서 TRIGGER를 생성할 수 있는 권한

- GRANT ADMIN IS TER DATABASE TRIGGER TO SCOTT ;

※ EXCUTE 권한 (TRIGGER 가 실행하는 스키마에 포함되지 않은 객체를 참조하는 경우)

4) TRIGGER 예제

(실습을 하기전 scott 사용자에게 create trigger 권한을 할당)

SYS>GRANT create trigger TO scott ;

예 1) 테이블에 데이터를 입력할 수 있는 시간 지정하기

(테이블 전체가 대상이므로 문장 레벨 TRIGGER 사용)

테스트를 위해 연습용 테이블 t_order 테이블 생성

데이터를 입력할 때 입력시간이 18:40 분에서 18:50 분일 경우만 입력을 허용하고 그 외는 에러발생 트리거 생성

18:40 ~ 18:50 에 입력 할 경우

18:40 ~ 18:50 이외의 시간에 입력 할 경우

예 2) 테이블에 입력될 데이터 값을 지정하고 그 값 이외에는 에러를 발생시키는 TRIGGER 를 생성

신규 내용을 입력하는데 앞에서 생성한 문장레벨 TRIGGER로 인해 입력이 안됨

따라서 앞의 실습에서 만든 TRIGGER 삭제

올바른 제품코드 입력

틀린 제품코드(C200) 입력

예 3) TRIGGER 의 작동 조건을 WHEN 절로 더 구체적으로 지정

ORD_CODE 가 'C500'  인 제품에 대해서만 19:30 부터 19:35 까지 입력을 허용하는 TRIGGER

다른 제품코드느 시간 관계없이 정상적으로 입력 가능

예 4) 특정 테이블에 입력할 수 있는 계정을 지정하기

(테이블 자체가 대상이므로 문장 TRIGGER 를 사용)

실습을 위해 새로운 테이블을 생성

테스트를 위해 새로운 사용자 계정 USER1을 생성하고 필요한 권한을 할당

예 5. 기존 테이블(t_test1) 에 데이터가 업데이트 될 때 기존 내용을 백업테이블(t_test2)으로 옮겨놓는 TRIGGER 를 생성. 삭제되는 특정 행이 TRIGGER의 대상이므로 행 레벨 TRIGGER를 사용

위 예에서 5번 라인에 :OLD.no , :OLD,name 은 update 되면서 이전 데이터를 의미

컴파일 에러 - PL/SQL 블록이 PARSE 되는 동안 오타 등으로 인해 발생되는 에러

런타임 에러 - PL/SQL 블록이 실행되는 동안에 발생되는 에러, 이 런타임 에러를 오라클에서 예외라고 부름

오라클의 예외 종류 - 오라클에서 미리 제공하는 오라클 예외

   - 사용자에 의해 정의되는 사용자 정의 예외

오라클 예외 : 오라클이 정의한 상황에서 자동적으로 발생 

- 이름이 정해진 예외 : Predefined ORACLE Exception

- 이름이 정해지지 않은 예외 : Non-predefined ORACLE Exception

사용자 정의 예외 : 선언부에서 exception 형으로 예외를 선언한 후 실행부 또는 예외처리부에서 RAISE문을 통해 

  명시적으로 예외 발생

예외 처리 사용하기

예외를 처리하기 위해서 PL/SQL 블록에는 예외처리부(EXCEPTION) 라는 부분이 있는데 이부분에 처리문장을 적으면 됨

문법 : 

EXCEPTION

WHEN exception1 [OR exception2 ...] THEN

statement1 ;

statement2 ;

....

[WHEN exception3 [OR exception4 ...] THEN

statement3 ;

statement4 ;

... ]

[WHEN OTHERS THEN

statementN ;

statementN+1 ;

... ]

- exception N

실행부에서 발생한 예외의 이름들로 해당되는 WHEN 절 안의 문장들을 수행

- OTHERS 

이전의 WHEN 에 해당하는 예외를 처리하지 않고 가장 마지막에 기술되는 WHEN 에 해당하는 예외 만 사용

예외처리 사용 예 1: 오라클에서 사전 정의된 예외 사용하기

사원명이 'A' 로 시작하는 사원을 조회하여 묵시적 커서를 사용하여 출력하되 여러 건의 데이터가 나올 경우 에러발생하는 예외처리 생성

SQL>SELECT ename

FROM emp

WHERE ename LIKE 'A%' ;

-> ENAME

    -------

ALLEN

ADAMS    -- A 로 시작하는 사원 2명 검색

사원의 데이터가 2인데 명시적 커서를 사용해 PL/SQL을 사용해야 하지만 묵시적 커서를 사용하여 TOO_MANY_ROWS 라는 예외가 발생, 이 경우 예외 처리 기능을 사용하여 보다 좋게 처리 하는 예

SQL>DECLARE

v_ename    emp.ename%TYPE ;

BEGIN

SELECT ename    INTO v_ename

FROM emp

WHERE ename LIKE 'A%' ;

DBMS_OUTPUT.PUT_LINE ('사원명은'||v_ename||' 입니다') ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(' 해당 사원이 없습니다') ;

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(' 사원이 두 명 이상입니다') ;

END ;

/

Enter value for dno : 10

해당 부서에 사원이 존재하므로 삭제할 수 없습니다

위에 굵은 글자가 예외 처리 부 이며 위와 같이 발생시킬 예외들을 지정해 주면 됨

예외처리 사용 예 2 : PRAGMA 를 사용하여 에러메시지 변경하기

PRAGMA 란 키워드는 특정 에러코드와 예외 메시지를 연결해주는 역할

두 개의 테이블이 서로 Foreign Key 가 설정되어 있는 상태에서 부모테이블에서 부서 번호를 입력 받아 해당 부서를 삭제하되 자식 테이블에 해당 부서에 소속된 사원이 있을 경우 '해당 부서에 사원이 존재하므로 삭제할 수 없습니다 ' 라는 예외를 발생시키는 예제.

SQL> DECLARE

2        sawon_exist EXCEPTION ;

3        PRAGMA EXCEPTION_INIT(sawon_exist,-2292) ;

4    BEGIN

5        DELETE FROM dept

6        WHERE deptno=&dno ;

7        COMMIT ;

8    EXCEPTION

9        WHEN sawon_exist THEN

10      DBMS_OUTPUT.PUT_LINE('해당 부서에 사원이 존재하므로 삭제할 수 없습니다') ;

11  END ;

12   /

-> PRAGMA EXCEPTION_INIT는 컴파일러에게 예외 이름을 Oracle 오류 번호와 연관시키도록 지시

이렇게 하면 모든 내부 예외를 이름으로 참조하고 이 예외에 대한 특정 처리기를 작성

3 번째 줄 끝에 -2292 는 에러 발생시 나오는 오라클 에러 번호

예외처리 사용 예 3: RAISE 를 사용하여 예외 상황 처리하기

아래의 예는 empno 를 입력 받은 후 emp 테이블에서 해당 사원을 지우는 작업을 수행

단, 없는 사원번호를 입력할 경우 '사원이 없습니다' 라는 예외 메시지 출력

SQL> DECLARE

no_empno EXCEPTION ;

   BEGIN

DELETE FROM EMP

WHERE empno = &empno ;

IF SQL%NOTFOUND THEN

RAIS no_empno ;

END IF ;

   EXCEPTION   

WHEN no_empno THEN

DBMS_OUTPUT.PUT_LINE('조회한 사번의 사원은 없습니다') ;

   END ;

          /

   Enter value for empno : 7901

   조회한 사번의 사원은 없습니다

예외처리 사용 예 4: RAISE_APPLICATION_ERROR 프로시저 사용하기

앞에서 사용한 3가지 예는 모두 예외 처리부에서 각각의 예외를 처리하는 방식

지금 살펴보는 예는 RAISE_APPLICATION_ERROR 프로시저를 사용하여 사용자가 특정 상황에 발생할 에러를 정의하고 예외 처리부를 사용하지 않고 실행부에서 즉시 예외처리 하는 방식.

이때 사용자가 임의로 지정 가능한 에러번호는 20000 ~ 20999 번 까지

SCOTT> SET VERIFY OFF

SCOTT> BEGIN

DELETE FROM emp WHERE empno = &empno ;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20001, '존재하지 않는 사번입니다') ;

END IF ;

END ;

/

Enter value for empno : 7901

BEGIN

*

ERROR at line 1 :

ORA-20001 : 존재하지 않는 사번입니다

ORA-06512 : at line 4

4번 라인에 RAISE_APPLICATION_ERROR 프로시저를 사용하여 에러메시지를 정의.

위 에러 부분을 보면 RAISE_APPLICATION_ERROR 프로시저에 기록된 에러메시지가 보이는 것을 확인 할 수 있다

예외처리 사용 예 5: SQLCODE 와 SQLERRM 사용하기

어떤 에러가 발생했는지 확인하기위해 SQLCODE 와 SQLERRM 을 사용

아래 예는 사용자로부터 empno 를 입력받아서 해당 empno 의 ename을 화면에 출력하는 코드

이 때 사용자가 없는 empno 를 입력할 경우 발생하는 Error code 와 Error 내용을 확인하기 위해 SQLCODE 와 SQLERRM을 사용

SCOTT>SET VERIFY OFF

SCOTT>DECLARE

name    emp.ename%TYPE ;

v_code    NUMBER ;

v_errm    VARCHAR2(64) ;

BEGIN

SELECT ename INTO name FROM emp WHERE empno = &eno ;

 EXCEPTION

   WHEN OTHERS THEN

v_code := SQLCODE ;

v_errm := SUBSTR(SQLERRM,1,64) ;

DBMS_OUTPUT.PUT_LINE('The error code is '||v_code||'-'||v_errm) ;

END ;

/

Enter value for eno : 7902

-> 존재하는 empno 라서 에러가 생기지 않음

SCOTT>/

Enter value for eno : 7900

The error code is 100-ORA-01403:no data found   <-- 존재하지 않는 번호라서 에러 발생

커서 - 묵시적 커서(Implicit Cusor) : 필요할 경우 오라클이 자동적으로 선언하여 사용한 후 자동적으로 정리

 - 명시적 커서(Explicit Cursor) : 사용자가 정의한 커서른 선언하여 사용하고, 커서의 사용이 끝난 후에는 별도의 정리                                                작업을 수행해줘야 함

묵시적 커서

- 세션 내에 단 한 개만이 선언되어 사용되었다가 문장이 종료됨과 동시에 정리, 1개의 데이터만 저장됨

묵시적 커서 속성

- SQL%ROWCOUNT

해당 커서에서 실행한 총 행의 개수(가장 마지막 행이 몇 번째 행인지 카운트)를 반환

- SQL%FOUND

해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE 값을 반환하고 없을경우 FALSE 값을 반환

- SQL%NOTFOUND

해당 커서 안에 아직 수행해야 할 데이터가 없을 경우 TRUE 값을 반환하고 있을경우 FALSE 값을 반환

- SQL%ISOPEN

현재 묵시적 커서가 메모리에 OPEN 되어 있을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가지는 속성

사용 예)

SCOTT>DECLARE

BEGIN

DELETE emp

WHERE sal < 1000 ;

DBMS_OUTPUT.PUT_LINE('1000 이하 : '||sql%rowcount||'건 삭제되었습니다') ;

DELETE emp

WHERE sal BETWEEN 1000 AND 2000 ;

DBMS_OUTPUT.PUT_LINE('1000~2000 사이 : '||sql%rowcount||'건 삭제 되었습니다') ;

END ;

/

-> 1000 이하 : 2 건 삭제 되었습니다

     1000 ~2000 사이 : 6 건 삭제 되었습니다

명시적 커서

사용자가 선언하여 생성 후 사용하는 SQL 커서로, 여러 개의 행을 처리하고자 할 경우 사용

명시적 커서 속성

- 커서이름%ROWCOUNT

FETCH 문에 의해 읽혀진 데이터의 총 행 수를 가지는 속성. 가장 마지막에 처리된 행이 몇 번째 인지 반환

- 커서이름%FOUND

FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 있을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가짐

- 커서이름%NOTFOUND

FETCH 문이 수행되었을 경우, 읽혀진(FETCH) 행이 없을 경우에는 TRUE 값을, 그렇지 않을 경우 FALSE 값을 가짐

- 커서이름%ISOPEN

명시적 커서가 메모리에 확보(선언) 되어 있을 경우에는 TRUE 값을, 그렇지 않을 경우에는 FALSE 값을 가짐

묵시적 커서와 FOR LOOP 문 활용하기

SCOTT>DECLARE

BEGIN

DBMS_OUTPUT.PUT_LINE('학과명과 학과의 위치 안내 입니다') ;

DBMS_OUTPUT.PUT_LINE('---------------------------------') ;

FOR dept_cur IN (SELECT dname,build

                                     FROM department 

                   WHERE build IS NOT NULL

 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(dept_cur.dname||'-->'||dept_cur.build||'에 있습니다') ;

END LOOP ;

END ;

/

-> 학과명과 학과의 위치 안내 입니다

-------------------------------------

기계공학과 --> 기계실험관 에 있습니다

멀티미디어공학과 --> 멀티미디어관 에 있습니다

문헌정보학과 --> 인문관 에 있습니다

.... 

명시적 커서 처리 단계

1) 명시적 커서 선언 (Declaration)

PL/SQL의 선언부(DECLARE)에 다른 변수와 마찬가지로 선언되어야 하며, 형식은 아래와 같다

- 문법 :

CURSOR 커서명 IS 커서에 담고 싶은 내용을 가져오는 서브쿼리

- 커서명 : 명시적 커서의 이름

- 서브쿼리 : 처리하고자 하는 데이터를 검색하는 SELECT 문장을 이부분에 기술

2) 명시적 커서 오픈(OPEN)

선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져 온다는 뜻. 

- 문법 :

OPEN 커서 이름 ;

- 커서이름 : 열고자 하는 명시적 커서의 이름

3) 명시적 커서로부터 데이터 읽어서 변수로 할당하기(FETCH)

명시적 커서의 데이터들로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 FETCH문을 사용

- 문법 :

FETCH 커서 이름 INTO 변수

- 커서이름 : 읽어(FETCH) 오고자 하는 명시적 커서의 이름으로 반드시 OPEN 되어 있어야 FETCH 가능

- 변수들 : 명시적 커서로부터 읽어온(FETCH) 데이터 행(레코드) 을 PL/SQL 블록 내에서 처리하기 위해서   

               변수에 저장해서 사용. 

4) 명시적 커서 닫기(CLOSE)

명시적 커서의 정리 작업을 하는 명령으로, 명시적 커서가 다 사용된 후에는 반드시 '닫기'를 해야 함.

커서를 닫는다 함은 작업이 끝난 메모리 공간을 반환하고 정리한다는 뜻

- 문법 :

CLOSE 커서 이름 ;

- 커서이름 : 닫고자 하는 명시적 커서의 이름으로 Close 된 명시적 커서는 더 이상 사용하지 않는다

사용 예)

SCOTT>DECLARE

vempno NUMBER(4) ;

vename VARCHAR2(20) ;

vsal NUMBER(7) ;

     (커서 선언)     CURSOR c1 IS

      SELECT empno, ename, sal

            FROM emp

      WHERE deptno=20 ;

BEGIN

     (커서 오픈)    OPEN c1 ;

            DBMS_OUTPUT.PUT_LINE('번호 이름  급여') ;

LOOP

    (데이터 추출)  FETCH c1 INTO vempno, vename, vsal ;

            EXIT WHEN c1%NOTFOUND ;

DBMS_OUTPUT.PUT_LINE(vempno||'  '||vename||' '||vsal) ;

            END LOOP ;

     (커서 닫기)    CLOSE c1 ;

      END ;

/

명시적 커서와 Cursor FOR LOOP 문 활용

문법 :

FOR record_name IN cursor_name LOOP

-- 명시적 커서의 OPEN, FETCH 가 자동적으로 수행됨.

statement1 ;

statement2 ;

....

END LOOP ; -- 루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE 됨.

- record_name : 커서로부터 FETCH 된 하나의 레코드를 저장하기 위한 커서 레코드 변수, 자동으로 만들어짐

- cursor_name : 처리하고자 하는 명시적 커서명으로, 선언부에서 정의되어야 함. 

명시적 CURSOR 와 CURSOR FOR LOOP 문 사용 예 :

emp 테이블에서 empno, ename 값을 가져와서 커서에 저장 후 출력하세요.

SCOTT>DECLARE

 CURSOR emp_cur IS   -- 커서 선언

       SELECT empno, ename

       FROM emp ;

    BEGIN

       FOR emp_rec IN emp_cur      -- 커서의 데이터를 저장할 emp_rec 변수 선언

        LOOP

          DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '||emp_rec.ename) ;

        END LOOP ;

    END ;

  /

파라미터 Explicit Cursor

- 명시적 커서를 선언 한 후 OPEN 할 때 값을 바꾸어서 수행해야 할 경우, 커서를 OPEN 할 때 필요한 값만 파라미터로 

전달해서 반복 수행 할 수 있는 방법

문법 :

CURSOR cursor_name

[(parameter_name dataTYPE ,...)] 

IS select-statement ;

- parameter_name : 파라미터 변수명으로 변수선언과 동일하며 여러 개의 파라미터를 지정할 수 있다

파라미터 Explicit Cursor 사용 예 :

SCOTT>DECLARE

CURSOR prof_cur (v_deptno IN NUMBER)

IS SELECT *

FROM professor

WHERE deptno=v_deptno ;

v_prof professor%ROWTYPE ;

BEGIN

DBMS_OUTPUT.PUT_LINE('============================') ;

DBMS_OUTPUT.PUT_LINE('101번 학과 교수님 번호와 이름 출력') ;

DBMS_OUTPUT.PUT_LINE('----------------------------') ;

OPEN prof_cur(101) ;

LOOP

FETCH prof_cur INTO v_prof ;

EXIT WHEN prof_cur%NOTFOUND ;

DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은 '||v_prof.name||' 입니다') ;

END LOOP ;

CLOSE prof_cur ;

DBMS_OUTPUT.PUT_LINE('============================') ;

DBMS_OUTPUT.PUT_LINE('102번 학과 교수님 번호와 이름 출력') ;

DBMS_OUTPUT.PUT_LINE('----------------------------') ;

OPEN prof_cur(102) ;

LOOP   

FETCH prof_cur INTO v_prof ;

EXIT WHEN prof_cur%NOTFOUND ;

DBMS_OUTPUT.PUT_LINE(v_prof.profno||' 번 교수님 이름은 '||v_prof.name||' 입니다') ;

END LOOP ;

CLOSE prof_cur ;

END ;

/

Cursor 와 Select....FOR UPDATE 문장

명시적 커서를 선언해 데이터를 조회한 후 변경을 하려는 중에 다른 세션에서 현 세션이 작업하는 내용을 변경 할 수 있다.

예를 들어 조회 할 시점에 A라는 데이터였지만 조회나 변경하는 중에 다른 세션이 B 라는 데이터로 바꿀수 있다는 뜻

그래서 커서를 선언 할 때 커서에 있는 행들에 대해 잠금을 수행.

1) 기본 문법 

이 실습을 위해 두 개의 세션에서 작업 실행

-세션 1-                                                                    -세션 2-

SCOTT>SELECT empno,ename,sal                                SCOTT>UPDATE emp

FROM emp                                                                  SET sal = 1000

WHERE empno = 7900                                                  WHERE empno = 7900 ;

FOR update ;                                                    -> 대기 상태로 반응 없습니다.

EMPNO    ENAME    SAL

------   -------   ----

    7900     JAMES    950

위와 같이 세션 1 에서 select 구문에 for update를 함께 쓰면 해당 데이터에 lock이 설정되어 다른 세션에서 dml 작업을 할 경우 대기상태로 들어감.

이 상태에서 세션 1 에서 commit 이나 rollback 명령을 수행하면 세션 2 에서 작업이 진행됨

2) Cursor 에서 SELECT... FOR update 사용하기

문법 :

SELECT ...

FROM ...

FOR UPDATE [OF column_reference][NOWAIT|WAIT n] ;

위 옵션중 NOWAIT 옵션은 다른 세션에서 행을 잠글 경우 즉시 오라클 에러를 발생 시켜주는 옵션.

다른 세션에서 해당 행에 LOCK 을 설정했을 경우 NOWAIT 옵션을 사용하면 즉시 에러를 발생시킴

그러나 만약 NOWAIT 옵션을 사용하지 않을 경우 LOCK 이 확보 될 때 까지 무한 대기상태

WAIT n = n자리에 초를 설정할 수 있으며 해당 초가 지난 후에도 LOCK을 확보할 수 없으며 에러발생

Cursor 에서 FOR UPDATE 구문 사용 예 :

DECLARE

CURSOR emp_cur IS

SELECT empno, ename, sal

FROM emp

WHERE deptno = 20

FOR UPDATE     -- 커서 선언시에 FOR UPDATE 로 행을 잠금

NO WAIT ;

....

BEGIN

....

OPEN emp_cur ;

....

UPDATE emp

SET sal = sal * 2

WHERE CURRENT OF emp_cur          -- 커서 선언 시에 잠긴 행 갱신

....

CLOSE emp_cur ;

END ;

1. 조건문 (IF 문)

유형 1. IF ~ END IF 문장

조건이 여러 개 일 경우에 사용하는 가장 기본적인 IF 문장

문법 : 

IF (조건) THEN

실행문장 ;

END IF ;

예 : EMP 테이블에서 EMPNO, ENAME, DEPTNO, DNAME 을 출력

단 DNAME 의 값은 아래와 같다

DEPTNO 가 10 이면 'ACCOUNT' ,

DEPTNO 가 20 이면 'RESEARCH' ,

DEPTNO 가 30 이면 'SALES' ,

DEPTNO 가 40 이면 'OPERATIONS' 로 출력

SCOTT>DECLARE

vempno emp.empno%TYPE ;

vname emp.ename%TYPE ;

vdeptno emp.deptno%TYPE ;

vdname VARCHAR2(20) := null ;

BEGIN

SELECT empno, ename, deptno

INTO vempno, vename, vdeptno

FROM emp

WHERE empno=7900 ;

IF (vdeptno = 10) THEN  --IF 문 시작

vdname :='ACCOUNT' ;

END IF ; --IF 문 끝

IF (vdeptno = 10) THEN

vdname :='RESEARCH' ;

END IF ;

IF (vdeptno = 10) THEN

vdname :='SALES' ; 

END IF ;

IF (vdeptno = 10) THEN

vdname :='OPERATIONS' ;

END IF ;

DBMS_OUTPUT.PUT_LINE (vempno||'  '||vename||'  '||vdeptno||'  'vdname) ;

END ;

/

위처럼 조건이 여러 개 일 경우 매번 조건을 새로 시작해야 하기에 사용함에 있어 불편함이 많다.

그래서 조건이 2개 이상일 경우 다음의 유형 2의 방법을 더 많이 사용

유형 2.IF ~THEN ~ELSIF ~END IF 문장

문법 :

IF (조건) THEN

실행문장 ;

   ELSIF (조건) THEN

실행문장 ;

   ELSIF (조건) THEN

실행문장 ;

END IF ;

예 : MP 테이블에서 EMPNO, ENAME, DEPTNO, DNAME 을 출력

단 DNAME 의 값은 아래와 같다

DEPTNO 가 10 이면 'ACCOUNT' ,

DEPTNO 가 20 이면 'RESEARCH' ,

DEPTNO 가 30 이면 'SALES' ,

DEPTNO 가 40 이면 'OPERATIONS' 로 출력

SCOTT>DECLARE

vempno emp.empno%TYPE ;

vname emp.ename%TYPE ;

vdeptno emp.deptno%TYPE ;

vdname VARCHAR2(20) := null ;

BEGIN

SELECT empno, ename, deptno

INTO vempno, vename, vdeptno

FROM emp

WHERE empno=7900 ;

IF (vdeptno = 10) THEN

vdname :='ACCOUNT' ;

ELSIF (vdeptno = 20) THEN

vdname :='RESEARCH' ;

ELSIF (vdeptno = 30) THEN

vdname :='SALES' ;

2. CASE 문

IF 문장과 같이 비교 조건이 여러 가지 일 경우 훨씬 더 간결하고 간단하게 조건을 파악해서 분기시킬 수 있는 제어문

문법 :

CASE [조건]

WHEN 조건 1 THEN 결과 1

WHEN 조건 2 THEN 결과 2

...

WHEN 조건 n THEN 결과 n

[ELSE 기본값]

END ;

CASE 내부의 여러 조건들을 구분할 때 ,(콤마) 를 사용하지 않음

사용 예 1 : 사용자로부터 사원 번호를 입력 받은 후 EMP 테이블에서 EMPNO, ENAME, DEPTNO, DNAME 을 출력하되

     DEPTNO 가 10 이면 'ACCOUNT' ,

     DEPTNO 가 20 이면 'RESEARCH' ,

     DEPTNO 가 30 이면 'SALES' ,

     DEPTNO 가 40 이면 'OPERATIONS' 로 출력

SCOTT>DECLARE

2    v_empno    emp.empno%TYPE ;

3    v_ename    emp.ename%TYPE ;

4    v_deptno    emp.deptno%TYPE ;

5    v_dname    varchar2(10) ;

6    BEGIN

7    SELECT empno, ename, deptno

8    INTO    v_empno, v_ename, v_deptno

9    FROM emp

10   WHERE empno = &empno ;

11   v_dname := CASE v_deptno

12                     WHEN 10 THEN 'ACCOUNT'

13                     WHEN 20 THEN 'RESEARCH'

14                     WHEN 30 THEN 'SALES'

15                     WHEN 40 THEN 'OPERATIONS'

16                    END ;

17    DBMS_OUTPUT.PUT_LINE (v_empno||'  '||v_deptno||'  'v_dname) ;

18    END ;

19    /

Enter value for empno : 7900

7900    JAMES    30    SALES

위 예에서 11~ 16줄의 CASE 부분을 아래와 같이 변형해도 결과는 동이랗게 나옴

11    v_dname := CASE

12                        WHEN v_deptno = 10 THEN 'ACCOUNT'

13                        WHEN v_deptno = 20 THEN 'RESEARCH'

14                        WHEN v_deptno = 30 THEN 'SALES'

15                        WHEN v_deptno = 40 THEN 'OPERATIONS'

16                       END ;

3. 반복문

PL/SQL 에서 변수를 사용하는 이유

- 변수는 데이터의 임시 저장 영역이다.

- 저장된 값을 조작하기 위해 사용

- 저장된 값을 반복해서 재 사용할 수 있다.

PL/SQL 에서 사용될 변수의 규칙 

- 반드시 문자로 시작해야만 한다.

- 문자나 숫자, 특수문자를 포함할 수 있다.

- 변수명은 30 bytes 이하여야 한다.

- 예약어를 포함하면 안된다

주요 변수들의 종류

단순 변수

-Scalar(스칼라) 변수 와 Reference 변수

Scalar 변수 - 단일 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수, 즉 정수, 실수 이런 형태를 직접 지정

Reference 변수 - 데이터 형을 다른 컬럼에서 참조 후 지정하는 방식

(1) Scalar 변수

문법 : Identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expr] ;

- Identifier : 변수의 이름으로 다른 변수와 구별되는 식별자의 역할을 하며 블록 내에서는 유일해야 한다.

- CONSTANT : 읽기전용 변수, 즉 상수로 선언하기 위한 키워드로 기본적으로 초기값이 반드시 지정되어야 한다.

    (값이 없을 경우 생략 가능) 예. V_RATE CONSTANT NUMBER := 0.2 ; 

- Datatype : 오라클에서 허용하는 데이터 타입은 모드 지원 

- %TYPE 을 사용하여 테이블내의 컬럼과 동일한 데이터 형을 선언할 수 있음

- %ROWTYPE 을 사용하여 테이블의 레코드 구조와 동일 형태의 레코드를 선언할 수 있음

- NOT NULL : 항상 값을 가지도록 제약을 주는 키워드로 이 값을 줄 경우 초기값이 반드시 지정되어야 하며 없을 경우                      생략 가능    예.V_NAME VARCHAR2(14) NOT NULL := '이순신' ;

- := 또는 DEFAULT : 변수에 기본값을 할당하기 위한 키워드   예. V_DATE DATE DEFAULT SYSDATE ;

- expr : 변수에 부여할 기본값을 의미하며 단순한 ㄱ밧에서부터 다른 변수, 수식, 함수가 올 수 있다.

주요 스칼라 변수 선언 예

Vno number(5,3) <- 숫자를 저장하는 변수로 총 5자리이며 소수점이하 3자리를 의미

Vname varchar2(10) <- 문자를 저장하는 변수로 총 10바이트의 길이를 저장할 수 있다

Vday date <- 날짜를 저장하는 변수

(2) 주요 Scalar 변수의 데이터 타입

- CHAR[(최대길이)] : 고정 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장. 기본 최소값=1

- VARCHAR2 (최대길이) : 가변 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장. 기본값 X

- NUMBER [(전체자리수, 소수점이하 자리수)] : 전체 자리수와 소수점 이하의 자리수를 가진 숫자

 전체 자리수의 범위는 1부터 38까지, 소수점 이하 자리수 범위는 -84 부터 127 까지

- BINARY_INTEGERR : -2,147,483,647 ~ 2,147,483,647 사이의 정수를 저장하는 타입

- PLS_INTERGER : -2,147,483,647 ~ 2,147,483,647 사이의 부호 있는 정수에 대한 기본 유형

NUMBER 값보다 저장 공간이 적게 필요하고 연산 속도가 더 빠름

- BOOLEAN : 논리적 계산에 사용 가능한 세 가지 값(TRUE, FALSE, NULL) 중 하나를 저장하는 기본 유형

- BINARY_FLOAT : IEEE 754 형식의 부동 소수점 수를 나타냄. 값을 저장하기 위해 5바이트가 필요

- BINARY_DOUBLE : IEEE 754 형식의 부동 소수점 수를 나타냄. 값을 저장하기 위해 9바이트가 필요

- DATE : 날짜 및 시간에 대한 기본 유형

- TIMESTAMP : DATE 데이터 유형을 확장하고 연도, 월, 일, 시, 분, 초 및 소수로 표시되는 초 단위를 저장

구문 : TIMESTAMP[(precision)]으로 나타내고 선택적 파라미터인 precision 은 초 필드의 소수부분 자릿수를 지정

   자릿수를 지정하려면 0-9 범위의 정수를 사용해야 함. 기본값은 6

Reference 변수 (참조 변수)

변수가 지정되어야 할 정확한 데이터 형태를 모를 경우 해당 데이터가 들어 있는 컬럼의 정보를 참조하게끔 설정

예 ) Vno emp.empno%TYPE <- emp 테이블의 empno와 동일한 데이터형으로 선언

Vname emp.empno%TYPE <- emp 테이블의 ename 과 동일한 데이터형으로 선언

Vrow emp%ROWTYPE <- emp 테이블의 여러 컬럼을 한꺼번에 저장할 변수로 선언

ROWTYPE 변수는 하나의 테이블에 여러 컬럼의 값을 한꺼번에 저장할 수 있는 변수를 의미

(1) TYPE 변수를 사용하여 데이터 조회

emp3 테이블에서 empno가 7900 번인 사원의 empno, ename, sal 을 조회하여 화면에 출력

-실습을 위해 emp테이블을 복사해서 emp3 테이블을 생성

SCOTT>CREATE TABLE emp3 

2    AS

3    SELECT empno,ename,sal

4    FROM emp ;

SCOTT>SET SERVEROUTPUT ON ;

SCOTT>DECLARE

2    vno emp3.empno%TYPE ;

3    vname emp3.ename%TYPE ;

4    vsal emp3.sal%TYPE ;

5    BEGIN

6    SELECT empno,ename,sal

7    INTO vno,vname,vsal

8    FROM emp3

9    WHERE empno=7900 ; 

10  DBMS_OUTPUT.PUT_LINE(vno||'  '||vname||'   '||vsal) ;

11  END ;

12  /

-> 7900 JAMES 950

(2) ROWTYPE 변수를 사용하여 데이터 출력

SCOTT>DECLARE

2    v_row emp3%ROWTYPE ;

3    BEGIN

4    SELECT * INTO v_row

5    FROM emp3

6    WHERE empno=7900 ;

7    DBMS_OUTPUT.PUT_LINE(v_row_empno||'**'||v_row.ename||'**'||v+row.sal_ ;

8    END ;

9    /

-> 7900**JAMES**950

(3) ROWTYPE 변수를 활용한 데이터의 입력

SCOTT>CREATE TABLE row_test

2    (no NUMBER,

3     name VARCHAR2(10),

4     hdate DATE) ;

SCOTT>CREATE TABLE row_test2

2    AS SELECT * FROM row_test ; 

SCOTT>INSERT INTO row_test VALUES (1,'AAA',SYSDATE) ;

SCOTT>INSERT INTO row_test VALUES (2,'BBB',SYSDATE) ;

SCOTT>INSERT INTO row_test VALUES (3,'CCC',SYSDATE) ;

SCOTT>COMMIT ;

SCOTT>DECLARE

2    v_record row_test%ROWTYPE ;

3    BEGIN

4    SELECT * INTO v_record

5    FROM row_test

6    WHERE no=1 ;

7    INSERT INTO row_test2

8    VALUES v_record ; 

9    END ;

10   /

SCOTT>SELECT * FROM row_test2 ;

NO    NAME    HDATE

---  ------  -------

   1     AAA    27-MAR-12

(4) ROWTYPE 변수를 활용한 데이터의 변경

SCOTT>DECLARE

2    v_record row_test%ROWTYPE ;

3    BEGIN

4    SELECT * INTO v_record

5    FROM row_test

6    WHERE no=1 ;

7    UPDATE row_test2

8    SET row=v_record

9    WHERE no=1 ;

10   END;

11    /

SCOTT>SELECT * FROM row_test2 ;

NO    NAME    HDATE

---  ------  ----------

   1    DDD     27-MAR-12     <- 원래 'AAA' 에서 'DDD' 로 바뀜

변수 예제 1

%TYPE 변수를 사용하여 emp, dept 테이블을 조인하여 empno=7900인 사람의 정보를 4개의 변수에 넣은 후 empno, ename, deptno, dname을 출력

SCOTT>DELCARE

2    v_empno emp.empno%TYPE ; 

3    v_ename emp.ename%TYPE ; 

4    v_deptno dept.deptno%TYPE ;

5    v_dname dept.dname%TYPE ;

6    BEGIN

7    SELECT e.empno, e.ename, d.deptno, d.dname

8    INTO v_empno, v_ename, v_deptno, v_dname

9    FROM emp e,dept d

10   WHERE e.empno=7900 

11   AND e.deptno=d.deptno ;

12   DBMS_OUTPUT.PUT_LINE(v_empno||'  '||v_ename||'  '||v_deptno||'  '||v_dname) ;

13   END ;

14   /

-> 7900 JAMES 30 SALES

변수 예제 2

사용자로부터 두 개의 숫자를 입력 받아서 합을 구하시오

SCOTT>DECLARE

2    v_no1 NUMBER := &no1 ;

3    v_no2 NUMBER := &no2 ;

4    v_sum NUMBER ;

5    BEGIN

6    v_sum := v_no1 + v_no2 ;

7    DBMS_OUTPUT.PUT_LINE('첫 번째 수:'||v_no1||', 두번째 수 :'||v_no2||', 합은 :'||v_sum||' 입니다') ;

8    END ;

9    /

Enter value for no1 : 20

Enter value for no2 : 40

-> 첫 번째 수 : 20, 두번째 수 : 40, 합은 : 60 입니다

복합 변수 ( 조합 변수)

변수 하나 안에 사용자가 원하는 여러 가지 다른 유형의 데이터를 포함해서 사용자가 원하는 새로운 형태로 만듬

복합변수 - Record Type 변수 와 Table Type (컬렉션 타입) 변수로 나눌 수 있음

Record Type 변수 : 데이터 타입이 다른 여러 건을 저장할 경우

Table Type (컬렉션 타입) 변수 : 동일한 데이터 타입의 데이터를 여러 건 저장하고 싶을 경우 

(1) Record Type 복합 변수

- 크게 %ROWTYPE 을 사용하는 테이블 기반의 Record Type과 사용자가 원하는 컬럼만을 지정해서 만드는 

프로그래머 정의 기본 Record Type 으로 구분

PL/SQL Record 정의와 선언

1. TYPE type_name IS RECORD ( field_declaration[, field_declaration]...) ;

2. Identifier type_name

Record Type 변수의 선언 및 사용 예 )

프로그래머 정의 Record Type 변수 사용 예 1 :

Record type 변수를 활용하여 부서번호가 30 번인 부서의 부서번호와 부서명과 지역명을 Record type 변수에 저장한 후 출력. 단 record type 의 type 명은 dept_record_type 로 지정하겠음.

SCOTT>DECLARE

2    TYPE dept_record_type IS RECORD

3    (deptno dept.deptno%TYPE,

4     dname dept.dname%TYPE,

5     loc       dept.loc%TYPE) ;

6     v_dept dept_record_type ;

7    BEGIN

8    SELECT deptno, dname, loc

9    INTO v_dept

10   FROM dept

11   WHERE deptno = 30 ;

12   DBMS_OUTPUT.PUT_LINE('부서번호  부서명  위치') ;

13   DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'    '||

14                                         v_dept.dname||'    '||

15                                         v_dept.loc) ;

16    END ;

17    /

부서번호    부서명    위치

-------   ------   --------

30             SALES    CHICAGO

프로그래머 정의 Record Type 변수 사용 예 2 :

emp2 테이블을 사용하여 사용자로부터 사원 번호를 입력 받은 후 아래와 같이 사원번호, 사원이름, 직급, 생일, 연락처, 급여를 출력, 단 직급이 없는 사원은 직급을 사원으로 표시해서 출력

Enter value for empno : 20000102

사원번호 : 20000102

사원명 : 김설악

직급 : 사원

생일: 22-MAR-83

연락처 : 031)234-5678

급여 : 30000000

SCOTT>DECLARE

2    TYPE e2_rec_type IS RECORD (

3    vempno    emp2.empno%TYPE,

4    vname      emp2.name%TYPE,

5    vposition   emp2.position%TYPE,

6    vbirth        emp2.birthday%TYPE,

7    vtel           emp2.tel%TYPE,

8    vpay         emp2.pay%TYPE) ;

9    v_e2_record    e2_rec_type ;

10   v_empno emp2.empno%TYPE := '&empno' ;

11   BEGIN

12   SELECT empno,name,NVL(position,'사원'),birthday,tel,pay

13   INTO v_e2_record

14   FROM emp2

15   WHERE empno=v_empno ;

16   DBMS_OUTPUT.PUT_LINE('사원번호:'||v_e2_record.vempno) ;

17   DBMS_OUTPUT.PUT_LINE('사  원 명:'||v_e2_record.vname) ;

18   DBMS_OUTPUT.PUT_LINE('직      급:'||v_e2_record.vposition) ;

19   DBMS_OUTPUT.PUT_LINE('생      일:'||v_e2_record.vbirth) ;

20   DBMS_OUTPUT.PUT_LINE('연  락 처:'||v_e2_record.vtel) ;

21   DBMS_OUTPUT.PUT_LINE('급      여:'||v_e2_record.vpay) ;

22   END ;

23    /

(2) Table Type 변수 (컬렉션 타입)

대부분의 경우 1 개의 컬럼만 저장을 하지만 Row Type 을 사용하여 Record Type 과 같이 여러 가지 유형의 데이터 컬럼을 가질 수도 있음. 그러나 권장방법은 아님

컬렉션 타입의 종류

- 연관 배열

- 중첩 테이블

- VARRAY

연관 배열의 특성

- 연관 배열은 변수 선언 당시 채워지지 않으며 키나 값을 포함하지 않으므로 선언에서 연관 배열을 초기화 할  수 없습니다.

- 연관 배열을 채우려면 명시적 실행문이 필요

- 연관 배열의 크기에 제약이 없음. 따라서 새 행이 추가됨에 따라 연관 배열이 증가하도록 행 수가 동적으로 늘어날 수 있다.

컬렉션 타입 정의와 선언

문법 :

1. TYPE type_name IS TABLE OF {column_type|variable%type|table.column%type}[NOT NULL]|table%ROWTYPE[INDEX BY BINARY_INTEGER] ;

2. Identifier type_name

- Table Type 변수 사용 예 1 :

Table Type 변수를 사용하여 사원번호가 7499 인 사원의 이름을 조회해서 해당 변수에 저장 한 후 출력.

SCOTT>DECLARE

2    t_name    VARCHAR2(20) ;

3    TYPE tbl_emp_name IS TABLE OF

4    emp.ename%TYPE

5    INDEX BY BINARY_INTEGER ;

6    v_name tbl_emp_name ;

7    BEGIN

8    SELECT ename INTO t_name

9    FROM emp 

10   WHERE empno=7499 ;

11   v_name(0) := t_name ;

12   DBMS_OUTPUT.PUT_LINE(v_name(0)) ;

13   END ;

14    /

- Table Type 변수 사용 예2 : 커서를 사용해서 생성하는 방법

professor 테이블의 rowtype 을 가지는 prof_table_type 이라는 이름으로 새로운 타입을 생성 한 후 v_prof_row 라는 변수에 할당. 그리고 v_prof_row 변수의 첫 번째 행 (0번 행) 에 데이터를 입력 한 후 화면에 출력

SCOTT>DECLARE

2    TYPE prof_table_type IS TABLE OF professor%rowtype

3    INDEX BY binary_integer ;

4    v_prof_row prof_table_type ;

5    BEGIN

6    v_prof_row(0).profno := 9000 ;

7    v_prof_row(0).name := 'imsy' ;

8    v_prof_row(0).id := 'imsy_id' ;   

9    v_prof_row(0).position := 'imsy' ;

10  v_prof_row(0).pay := '000' ;  

11   v_prof_row(0).hiredate := sysdate ;

12   DBMS_OUTPUT.PUT_LINE(v_prof_row(0).profno||' '||v_prof_row(0).name||' '||v_prof_row(0).id||' '||

13                                       v_prof_row(0).position||' '||v_prof_row(0).pay||' '||v_prof_row(0).hiredate) ; 

14   END ;

15   /

-> 9000 imsy imsy_id imsy 0 01-JUL-13

- Table Type 변수 사용 예 3 : For 반복문을 사용하여 변수에 여러 건의 데이터를 입력하는 방법

SCOTT>DECLARE

2    TYPE e_table_type IS TABLE OF

3    emp.ename%TYPE

4    INDEX BY BINARY_INTEGER ;

5    tab_type e_table_type ;

6    a BINARY_INTEGER := 0 ;

7    BEGIN

8    FOR i IN (SELECT ename FROM emp) LOOP

9        a := a+1 ;

10       tab_type(a) := i.ename ;

11   END LOOP ;

12   FOR J IN 1..a LOOP

13   DBMS_OUTPUT.PUT_LINE(tab_type(j)) ;

14    END LOOP ;

15    END ;

16    /

-> SMITH

     ALLEN

     WARD

    JONES

....

비 PL/SQL 변수

PL/SQL 안에서만 사용되는 것이 아니라 외부에서도 사용될 수 있는 변수

SQL 환경에서도 쓸 수 있고 PL/SQL 에서도 사용 할 수 있음

- 바인드 변수

호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 함. VARIABLE 키워드를 사용하여 생성

사용 예 ) 교수번호가 1001 인 교수의 연봉을 계산하여 바인드 변수에 할당 한 후 출력

SCOTT>VARIABLE v_bind NUMBER ;   --SQL 에서 변수를 선언

SCOTT>BEGIN

2    SELECT (pay*12)+NVL(bonus,0) INTO :v_bind

3    FROM professor

4    WHERE profno=1001 ;

5    END ;

6    /

SCOTT>PRINT v_bind ;  --SQL에서 바인드 변수에 담긴 값을 출력

V_BIND

------

  6700

PRINT 문장으로 매번 확인이 번거로울 경우 SET AUTOPRINT ON ;  명령어를 사용하면 자동으로 바인드 변수값을 출력

PL/SQL 과 SQL 에서 사용되는 주요 데이터 타입 정리

PL/SQL - Procedural Language/SQL 

PL/SQL 의 런타임 구조

PL/SQL 실행 시 실행 원리

PL/SQL 기본 구조

PL/SQL 은 기본적으로 블록(BLOCK) 구조를 가지고 있다.

블록의 기본 구성은 선언부(DECLARE), 실행부(BEGIN), 예외처리부(EXCEPTION)로 구성

그 블록 안에 블록을 포함시킬 수 있는 데 포함된 블록을 Nested Block(중첩 블록) 이라고 부름

블록의 유형에는 크게 Anonymous PL/SQL Block(익명블록) 과 Stored PL/SQL Block(저장된 블록) 이 있다.

익명블록 = 일회성으로 사용할 경우

저장된 블록 = 서버에 파싱해서 자장해 놓고 주기적으로 반복해서 사용할 경우

※ PL/SQL은 기본적으로 처리된 문장을 화면에 출력하지 않는다.

그래서 화면에 출력하기 위해서 아래와 같은 사전작업이 필요

SQL>SET SERVEROUTPUT ON ;      

오류 내용을 보고 싶을 경우

SQL>SET ERRORS ;

PL/SQL 기본 실행 예 )

emp 테이블에서 empno 가 7900 번인 사원의 사번과 이름을 화면에 출력하시오

SCOTT>DECLARE

v_ename emp.ename%TYPE ;

BEGIN

SELECT ename into v_ename

FROM emp

WHERE empno=&empno ;

DBMS_OUTPUT.PUT_LINE ('사원명은'||v_ename||'입니다') ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(' 해당 사원이 없습니다 ') ;

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE(' 사원이 두 명 이상입니다 ') ;

END ;

/

Enter value for empno : 7900

-> 사원명은 JAMES 입니다

Enter value for empno : 8000

-> 해당 사원이 없습니다

1 > PL/SQL 문 내에서의 SQL 문장 사용하기

- END 키워드는 트랜잭션의 끝이 아니라 PL/SQL 블록의 끝을 나타냄

- PL/SQL 은 DDL 문을 직접 지원하지 않는다

- PL/SQL 은 GRANT 또는 REVOKE 와 같은 DCL 문을 직접 지원하지 않는다

1. PL/SQL 내에서의 SELECT 문장 사용하기

문법 : 

SELECT select_list

INTO {variable_name[, variable_name]...| record_name}

FROM table

[WHERE condition] ;

사용 예 1) Professor 테이블에서 교수번호가 1001 번인 교수의 교수번호와 급여를 조회 한 후 변수에 저장해서 출력

SCOTT> DECLARE

2    v_profno professor.profno%TYPE ;

3    v_pay     professor.pay%TYPE ;

4    BEGIN

5    SELECT profno, pay, INTO v_profno, v_pay

6    FROM professor

7    WHERE profno=1001 ;

8    DBMS_OUTPUT.PUT_LINE (v_profno||' 번 교수의 급여는 '||v_pay||' 입니다') ;

9    END ;

10    /

-> 1001 번 교수의 급여는 550 입니다

사용 예 2)emp2 테이블을 사용하여 사원번호를 입력 받아서 사원의 사번과 이름, 생일을 출력하세요.

SCOTT> DECLARE

2    v_empno emp2.empno%TYPE ;

3    v_name emp2.name%TYPE ;

4    v_birth emp2.birthday%TYPE ;

5    BEGIN

6    SELECT empno,name,birthday INTO v_empno,v_name,v_birth

7    FROM emp2

8    WHERE empno=&empno;

9    DBMS_OUTPUT.PUT_LINE (v_empno||' '||v_name||' '||v_birth) ;

10   END ;

11    /

ENTER value for empnno : 20000102

-> 20000102 김설악 22-MAR-83

사용자에게 값을 입력 받아서 변수에 할당 할 때 사용하는 기호 = &(엠퍼센트)

2 > PL/SQL 내에서의 DML 문장 사용하기

INSERT , UPDATE, DELETE, MERGE 문장을 이용하여 PL/SQL 블록 내에서 데이터를 변경 할 수 있다.

(1) INSERT 문장 수행하기 예 1 :

- 이 작업을 하기 위해 테스트용 테이블 pl_test 와 시퀀스를 아래와 같이 먼저 생성

SCOTT>CREATE TABLE pl_test

2    (no number,

3     name varchar2(10)) ;

SCOTT>CREATE SEQUENCE pl_seq ;

- PL/SQL 에서 INSERT 를 수행

SCOTT>BEGIN 

2    INSERT INTO pl_test

3    VALUES(pl_seq.NEXTVAL,'AAA') ;

4    END ;   

5    /

PL/SQL procedure successfully completed.

SCOTT>/

PL/SQL procedure successfully completed.

SCOTT>SELECT * FROM pl_test ;

NO   NAME 

---  -----

   1    AAA

   2    AAA

SCOTT>commit ;

Commit complete

(2) INSERT 문장 수행하기 예 2 :

- 작업을 수행하기 전에 pl_test2 테이블 생성

SCOTT>CREATE TABLE pl_Test2

2    (no number,

3     name varchar2(10),

4     addr varchar2(10) ) ;

사용자로부터 번호(no) , 이름(name), 주소(addr) 값을 입력 받은 후 pl_test2 테이블에 입력하는 PL/SQL 문장을 작성

SCOTT>SET VERIFY OFF

SCOTT>DECLARE

2    v_no number :='&no' ;

3    v_name varchar2(10) := '&name' ;

4    v_addr varchar2(10) := '&addr' ;

5    BEGIN

6    INSERT INTO pl_test2 VALUES(v_no, v_name, v_addr) ;

7    END ;

8    /

Enter value for no : 10

Enter value for name : AAA

Enter value for addr : 서울

SCOTT>SELECT * FROM pl_test2 ;

NO    NAME    ADDR

---   -----   ------

  10    AAA       서울

(3) PL/SQL 에서 UPDATE 수행

SCOTT>BEGIN

2    UPDATE pl_test

3    SET name='BBB'

4    WHERE no = 2 ;

5    END ;

6    /

SCOTT>SELECT * FROM pl_test ;

NO    NAME

---   -----

   1    AAA   

   2    BBB

SCOTT>commit ;

(4) PL/SQL 에서 DELETE 수행

SCOTT>BEGIN

2    DELETE FROM pl_test

3    WHERE no=1 ;

4    END ;

5    /

SCOTT>SELECT * FROM pl_test ;

NO    NAME

---   -----

   2    BBB

SCOTT>commit ;

(5) PL/SQL 에서 MERGE 작업 수행

- 작업을 수행하기 위해 연습용 테이블 pl_merge1 과 pl_merge2 를 생성

SCOTT>CREATE TABLE pl_merge1

2    (no number,

3     name varchar2(10) ) ;

SCOTT>CREATE TABLE pl_merge2

2    AS SELECT * FROM pl_merge1 ;

SCOTT>INSERT INTO pl_merge1 VALUES(1,'AAA') ;

SCOTT>INSERT INTO pl_merge1 VALUES(2,'BBB') ;

SCOTT>INSERT INTO pl_merge2 VALUES(1,'CCC') ;

SCOTT>INSERT INTO pl_merge2 VALUES(3,'DDD') ;

SCOTT>commit ;

SCOTT>SELECT * FROM pl_merge1 ;

NO    NAME

---   -----

   1     AAA

   2     BBB

SCOTT>SELECT * FROM pl_merge2 ;

NO    NAME

---   -----

   1     CCC

   3     DDD

SCOTT> BEGIN

2    MERGE INTO pl_merge2 m2

3    USING pl_merge1 m1

4    ON(m1.no = m2.no)

5    WHEN MATCHED THEN

6    UPDATE SET

7    m2.name = m1.name

8    WHEN NOT MATCHED THEN

9    INSERT VALUES(m1.no , m1.name) ;

10   END ;

11    /

SCOTT>SELECT * FROM pl_merge1 ;

NO    NAME

---   -----

   1     AAA

   3     DDD

   2     BBB     <- 이줄 추가되어 있음

PL/SQL 에서의 렉시칼 (Lexical)

렉시칼 이란 PL/SQL 안에 사용되는 문자들을 의미하며 식별자, 구분자, 리터럴, 주석등으로 구성

식별자 - PL/SQL 객체에게 부여되는 이름 (테이블 이름이나 변수명 등)

아래와 같은 경우 식별자를 쌍따옴표로 묶어서 사용할 수 있다

- 식별자의 대소문자 구분이 필요한 경우

- 공백과 같은 문자를 포함할 경우

- 예약어를 사용해야 할 경우

구분자 - 특별한 의미를 지닌 기호, 예를 들어 SQL 문장을 끝낼 때는 끝내는 의미를 가진 ; 을 사용

리터럴 - 변수에 할당되는 모든 값, 식별자가 아닌 모든 문자, 숫자, 부울 또는 날짜 값

- 문자 리터럴 : 모든 문자열 리터럴은 데이터 유형이CHAR 또는 VARCHAR2 이므로 문자 리터럴이라고 함

- 숫자 리터럴 : 정수 또는 실수 값을 나타냄

- 부울 리터럴 : 부울 변수에 할당된 값, TRUE, FALSE 및 NULL 은 부울 리터럴이거나 키워드

- 주석 : 코드의 설명이나 해설등을 기록해 두는 것

PL/SQL 에서의 사용되는 주요 연산자들

PL/SQL 에서 블록 구문 작성시 주의사항

1) 문자 리터럴이나 날짜 리터럴 사용시 반드시 홀 따옴표로 묶어서 표시

2) 문장에서의 주석은 한 줄일 경우 --(하이픈 두개) 를 써서 표시하고 여러 줄 일 경우 /* */ 기호를 사용해서 표시

3) 프로시져 내에서는 단일행 함수만을 사용해야 하며 DECODE 함수나 그룹함수는 사용할 수 없음

4) 데이터의 형 변환에 주의