2015. 5. 26. 17:30

[Oracle] Procedure 예외처리 (SQLCODE, SQLERM)

http://pino93.tistory.com/373

 

 

 

SQLCODE, SQLERRM

-       WHEN OTHERS문으로 트랩(Trap) 되는 오류들의 실제 오류 코드와 설명을 볼 때 사용한다

-       SQLCODE : 실행된 프로그램이 성공적으로 종료하였을 때는 오류번호 0을 포함하며, 그렇지 못할 경우에는 해당 오류코드 번호를 포함한다.

-       SQLERRM : SQLCODE에 포함된 오라클 오류 번호에 해당하는 메시지를 가진다

 

SQLCODE Value

Description

0

오류 없이 성공적으로 종료

1

사용자 정의 예외 번호

+100

DATA NOT FOUND 예외 번호

음수

위의 부분을 제외한 오라클 서버 에러 번호

 


  ================================================
    * Oracle Community OracleClub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 운영자 : 김정식 (oramaster _at_ empal.com)
  ================================================ 

 

 

 

 

2012. 12. 21. 11:07

[Oracle] PL/SQL - SELECT INTO

SQL> L

1 DECLARE

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL

7 WHERE EMPNO='98002';

8

9 dbms_output.put_line('<<<조회결과>>>');

10 dbms_output.put_line(' 이름 : ' || t_personnel.emp_name);

11 dbms_output.put_line(' 주소 : ' || t_personnel.address);

12 EXCEPTION

13 WHEN NO_DATA_FOUND THEN

14 dbms_output.put_line('<<< ERROR >>>');

15 dbms_output.put_line(' 에러내용 : 조회된 데이타가 없습니다.!!');

16 WHEN TOO_MANY_ROWS THEN

17 dbms_output.put_line('<<< ERROR >>>');

18 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.!!');

19 WHEN OTHERS THEN

20 dbms_output.put_line('<<< ERROR >>>');

21 dbms_output.put_line('에러코드 : ' || SQLCODE );

22 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

23* END;

SQL> /

<<<조회결과>>>

이름 : 이대명

주소 : 경기도 군포시 궁내동

 

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

 

 

SQL> L

1 declare

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL;

7

8 dbms_output.put_line('<<<조회결과>>');

9 dbms_output.put_line('이름 : ' || t_personnel.emp_name );

10 dbms_output.put_line('주소 : ' || t_personnel.address);

11 EXCEPTION

12 WHEN NO_DATA_FOUND THEN

13 dbms_output.put_line('<<<ERROR>>>');

14 dbms_output.put_line('에러내용 : 조회된 데이타가 없습니다.');

15 WHEN TOO_MANY_ROWS THEN

16 dbms_output.put_line('<<<ERROR>>>');

17 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.');

18 WHEN OTHERS THEN

19 dbms_output.put_line('<<<ERROR>>>');

20 dbms_output.put_line('에러코드 : ' || SQLCODE );

21 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

22* END;

SQL> /

<<<ERROR>>>

에러내용 : 조회 데이타가 너무 많습니다.

 

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

 

 

속성

결과값(조회 1)

결과값(조회 0)

비고

SQL%NOTROUND

False

True

조회된 데이터가 없으면 True

SQL%FOUND

True

False

조회된 데이터가 있으면 True

SQL%ROWCOUNT

n

0

조회된 데이터 건수 (row )

SQL%ISOPEN

False

False

PL/SQL은 실행한 후 바로 Implicit cursor를 닫기 때문에 항상 False로 평가됨

 

 

SQL> L

1 DECLARE

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL

7 WHERE EMPNO='98002';

8

9 IF SQL%ROWCOUNT = 1 THEN

10 dbms_output.put_line('<<<조회결과>>>');

11 dbms_output.put_line('이름 : ' || t_personnel.emp_name);

12 dbms_output.put_line('주소 : ' || t_personnel.address);

13 ELSIF SQL%ROWCOUNT = 0 THEN

14 dbms_output.put_line('<<<ERROR>>>');

15 dbms_output.put_line('에러내용 : 조회된 데이타가 없습니다.');

16 ELSIF SQL%ROWCOUNT > 1 THEN

17 dbms_output.put_line('<<<ERROR>>>');

18 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.');

19 END IF;

20

21 EXCEPTION

22 WHEN OTHERS THEN

23 dbms_output.put_line('<<<ERROR>>>');

24 dbms_output.put_line('에러코드 : ' || SQLCODE );

25 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

26 END;

27 /

<<<조회결과>>>

이름 : 이대명

주소 : 경기도 군포시 궁내동

 

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

 

2012. 11. 5. 14:11

[Oracle] PL/SQL - 패키지 (Package)

패키지

 

- 서로 관련된 스키마 오브젝트들을 묶어 캡슐화 한 것.

 

- 컴파일되어 스키마 오브젝트로 데이터딕셔너리에 저장된다.

 

- 비슷한 작업을 수행하는 프로시저, 함수 등을 함께 묶어 사용한다.

 

- 패키지 자체는 호출해도 사용하지 못한다. 내부의 특정 서브 프로그램(프로시저,함수 등)을 호출해야 한다.

 

- "패키지.서브프로그램" 형식으로 호출한다.

 

- 패키지 구성 : 서브 프로그램 명세(Specification) + 서브 프로그램 본문(Body)

 

- 패키지 명세가 바뀌면 그 패키지를 참조하는 모든 스토어드 서브 프로그램을 다시 컴파일해야 한다.

 

- 패키지 본문은 바뀌어도 컴파일이 필요없다.

 

 

패키지를 사용하는 이유

 

- 보다 효율적인 모듈 단위로 구성하여 이해하기 쉽다.

 

- 패키지의 public 변수와 커서는 세션이 열려 있는 동안 지속되므로 세션에서 실행되는 모든 커서와 프로시저는 공유할 수 있다.

 

- 여러개의 오브젝트를 한번에 메모리로 올리기 때문에 성능 향상에 도움을 준다.

 

- 스토어드 프로시저와 함수를 갖고 있는 라이브러리를 사용해서 코드 재사용을 증진시켜 불필요한 코딩을

줄인다.

 

 

패키지 명세

 

- 패키지의 이름과 인수의 이름, 데이터형을 public으로 선언하는 부분.

 

- 패키지에 선언된 오브젝트는 패키지 어디에서나 접근 가능하다. (패키지에 대해 전역적)

 

- 응용 프로그램이 스토어드 서브 프로그램을 실행해야 하는 모든 정보는 패키지 명세에 들어 있다.

 

- , 패키지 내에 어떤 오브젝트들이 있는지 미리 이름과 형식을 알리는 것임.

 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE [OR REPLACE] PACKAGE 패키지이름

[AUTHID {CURRENT_USER | DEFINER} {IS | AS}

[패키지 본문 오브젝트 선언]

END 패키지이름;

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

- AUTHID : 패키지 실행시 호출할 권한의 유형.

 

- CURRENT_USER : 현재 사용자(호출자)

 

- DEFINER : 생성자

 

- 패키지 본문 오브젝트 선언 : 패키지에서 생성될 오브젝트의 목록을 적는 부분

 

 

패키지 본문

 

- 패키지 본문에는 명세에서 선언한 실제 오브젝트의 코드가 들어간다.

 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE [OR REPLACE] PACKAGE BODY 패키지이름 {IS | AS}

[패키지 본문 오브젝트 선언]

BEGIN

프로그램문장

END 패키지이름;

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

 

패키지 서브 프로그램 호출

 

1. 사용자가 서브 프로그램에 대한 실행 권한을 가지고 있는지 확인한다.

 

2. Dictionary View에서 프로시저가 VALID 상태인지 확인한다. INVALID 라면 실행 전에 자동으로 재컴파일한다.

 

3. 패키지의 서브 프로그램을 실행한다.

 

4. (.)으로 패키지와 서브 프로그램을 구분하여 사용한다.

- 패키지.

- 패키지.오브젝트

- 패키지.서브프로그램

 

[예제1] id10 의 나이와 마일리지를 변경하는 프로시저를 포함하는 패키지 작성

 

1. 아래처럼 USERID 'id10' 인 홍길동이 있다.

 

2. 패키지 명세를 작성하고 실행한다. (패키지 이름 : change_pkg)

 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE OR REPLACE PACKAGE change_pkg AS
    PROCEDURE change_ages (i_id IN VARCHAR2);
    PROCEDURE change_mileage (i_id IN VARCHAR2);
END change_pkg;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

3. 패키지 몸체(Body)를 작성하고 실행시킨다. (change_ages, change_mileage)

 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE OR REPLACE PACKAGE BODY change_pkg AS

i_id VARCHAR2(10);

 

PROCEDURE change_ages (i_id IN VARCHAR2) IS
BEGIN
    UPDATE member
    SET AGE=100
    WHERE userid = i_id;
END change_ages;

 

PROCEDURE change_mileage (i_id IN VARCHAR2) IS
BEGIN
    UPDATE member
    SET MILEAGE=777
    WHERE userid = i_id;
END change_mileage;

 

END change_pkg;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

4. 패키지의 프로시저를 실행시킨다.

 

SQL> exec change_pkg.change_ages('id10');

SQL> exec change_pkg.change_mileage('id10');

 

 

2012. 11. 5. 11:42

[Oracle] PL/SQL - 프로시저 (Procedure)

프로시저 (procedure) 와 패키지 (package)

 

- 프로그램 유지 보수와 실행을 보다 쉽게 하기 위해서 프로그램 코드를 논리적으로 구성한 것.

 

- 1개 패키지 내에 여러개의 프로시저가 존재한다.

 

 

스토어드 프로시저 (stored procedure)

 

- 데이터베이스 내에 컴파일되어 저장된 프로시저.

 

- 데이터베이스에 저장된 프로시저는 오브젝트로 관리된다.

 

 

프로시저를 사용하는 이유

 

- 특정 문제나 작업을 해결하기 위해서 사용한다.

 

- 특정 기능을 담당하는 모듈(module) 단위로 되어 있다.

 

- 재사용이 가능하다.

 

- 스토어드 프로시저를 통해서만 데이터를 액세스할 수 있어 보안이 향상된다.

 

- 프로시저는 공유된 메모리 자원을 이용한다.

 

 

프로시저와 함수

 

- 둘은 출력의 형식이 다르다.

 

- 프로시저 : 많은 양의 정보를 처리한다.

 

- 함수 : 하나의 값을 Return 한다.

 

 

프로시저 생성

 

- 프로시저를 생성할 때 오라클은 컴파일하는 동안 접근하는 오브젝트(Table,View )가 존재하는지 검사하고, 접근 권한이 있는지를 검사한다.

 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE OR REPLACE PROCEDURE 프로시저이름 (IN/OUT/INOUT)

AS

PL/SQL 본문코드

END 프로시저이름

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

- OR REPLACE : 프로시저가 이미 존재할 경우 덮어 쓴다. 항상 사용하는 것이 좋다.

 

- IN : 호출되는 프로시저에 값을 전달한다는 것을 지정

 

- OUT : 프로시저를 호출한 프로그램에게 값을 반환한다는 것을 지정

 

- INOUT : IN + OUT. 프로시저에 값을 전달하고 결과를 프로그램에게 반환한다는 것을 지정.

 

 

RETURN

 

- 프로시저가 끝나기 전에 마칠 경우에만 사용한다.

 

 

컴파일 / 재컴파일

 

- 스토어드 프로시저를 명시적으로 재컴파일 하려면 ALTER PROCEDURE 명령을 사용한다.

 

- ALTER PROCEDURE 는 패키지 내에 있는 프로시저가 아닌 독립된 프로시저에서만 사용 가능하다.

 

- 프로시저가 컴파일되면, 다음 실행시 컴파일 할 필요가 없어 부하를 줄일 수 있고, 컴파일 오류를 제거할 수 있다.

 

- 오라클은 부모 오브젝트가 명시적으로 다시 컴파일될 때 종속된 모든 오브젝트를 자동으로 재컴파일 한다. 따라서 부모 오브젝트를 컴파일 할 경우 관련된 모든 오브젝트가 모두 다시 컴파일이 일어나므로 시스템에 부하를 줄 수 있다.

 

 

프로시저 실행

 

- execute 명령으로 스토어드 프로시저를 실행할 수 있다.

 

[예제1] 사용자가 지정한 나이를 가진 사람의 나이를 100 으로 변경하는 스토어드 프로시저

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CREATE OR REPLACE PROCEDURE change_ages (i_age IN integer)
AS
BEGIN
    UPDATE member
    SET AGE=100
    WHERE age = i_age;
END change_ages;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

- 프로시저 실행

SQL> execute change_ages(10);

 

- 결과 :

 

age = 100 으로 변경됨

 

 

파라미터

 

- 프로시저는 파라미터를 사용하여 정보를 전달한다.

 

- 프로시저를 호출할 때는 프로시저의 파라미터에 대해 값을 전달해야 한다.

 

- Actual Parameter : 프로시저에 전달된 파라미터 (실제 파라미터)

 

- Internal or Formal Parameter : 프로시저 내부에 선언된 파라미터 (내부 또는 형식 파라미터)

 

- Actual Parameter 의 데이터형과 Internal Parameter 의 데이터형이 같아야 한다.

 

 

스토어드 프로시저 확인

 

- 스토어드 프로시저에 관한 정보를 제공하는 데이터 딕셔너리 뷰

 

ALL_ERRORS : 현재 사용자가 액세스 가능한 모든 오브젝트에 대한 오류 목록

 

ALL_SOURCE : 현재 사용자가 액세스 가능한 모든 오브젝트의 텍스트 소스

 

USER_OBJECTS : 현재 사용자가 액세스 가능한 모든 오브젝토 목록

 

USER_ERRORS : 현재 사용자에게 속한 오브젝트의 오류 목록

 

USER_OBJECT_SIZE : 현재 사용자의 모든 PL/SQL 오브젝트

 

USER_SOURCE : 현재 사용자에게 속한 모든 오브젝트의 텍스트 소스

 

DBA_OBJECTS : 데이터베이스에 저장된 모든 오브젝트 목록

 

DBA_ERRORS : 데이터베이스에 저장된 모든 오브젝트에 대한 오류 목록

 

DBA_OBJECT_SIZE : 데이터베이스에 있는 모든 PL/SQL 오브젝트

 

DBA_SOURCE : 데이터베이스 전체에 대한 정보

 

- 어떤 오브젝트가 INVALID 상태인지 확인하고 다시 컴파일 할 필요가 있는지 확인할 수 있다.

 

- INVALID 인 함수나 프로시저는 잘못된 구문이 있을 수 있다.

 

SQL> select object_name, object_type, status from user_objects where status = 'INVALID';

 

 

프로시저 삭제

 

DROP PROCEDURE 프로시저이름;

 

 

프로시저 오버로딩

 

- 한 패키지에서 동일한 이름의 프로시저에 각각 다른 인수를 사용해서 호출할 수 있다.

 

- 서로 다른 데이터형을 갖고 있는 인수를 가지고 동일한 프로시저를 여러 번 실행해야할 경우 유용하다.

 

 

재귀 프로시저

 

- 자기 자신을 호출하는 프로시저

 

 

'Database / Sql' 카테고리의 다른 글

[Oracle] 테이블 권한주기  (0) 2012.11.06
[Oracle] PL/SQL - 패키지 (Package)  (0) 2012.11.05
[Oracle] PL/SQL - 블럭 구조  (0) 2012.10.31
[Oracle] PL/SQL - 개념과 예제  (0) 2012.10.31
[Oracle] JOIN DELETE  (0) 2012.07.21
2012. 10. 31. 16:59

[Oracle] PL/SQL - 블럭 구조

PL/SQL을 마스터하기 위해서는 블럭 구조와 블럭의 다양한 종류, 블럭의 사용법을 이해하는 것이 필수적이다.

 

 

Anonymous Block (이름 없는 블럭)

 

- 아무런 이름이 없는 블럭으로 프로시저나 함수, 트리거의 본문을 형성하지 않는 블럭

 

- 이는 SQL*Plus 스크립트의 일부로 인라인으로 사용될 수 있으며, 오류 처리 목적으로 프로시저와 함수 블럭 내부에 포함될 수도 있다.

 

- DECLARE 라는 예약어를 사용하여 시작하고 다음 예약어인 BEGIN 사이에 변수를 선언한다.

 

- BEGIN 예약어는 블럭의 절차적 부분이 시작된다는 것을 알려준다. 프로그램 코드가 이 부분에 온다.

 

- EXCEPTION 예약어는 블럭에서 예외 처리 코드를 갖는다.

 

**************************************************************************************************

DECLARE

변수 선언문;

BEGIN

프로그램 코드

EXCEPTION

오류처리 코드

END;

**************************************************************************************************

 

 

Function Block (함수 블럭)

 

- DECLARE 예약어 대신 그 자리에 함수 헤더가 온다.

 

- 함수 헤더는 함수 이름과 파라미터들을 기술하고, 반환값의 형을 나타낸다.

 

- 인수목록 : 함수에 대한 입력 파라미터와 출력 파라미터 목록을 기술한다.

 

- RETURN : 반환값에 대한 데이터형을 기술한다.

 

**************************************************************************************************

FUNCTION 이름 [(인수목록)]

RETURN 데이터형 {IS, AS}

변수 선언문;

BEGIN

프로그램 코드

(EXCEPTION

오류처리 코드)

END;

**************************************************************************************************

 

DECLARE

temp NUMBER;

 

FUNCTION iifn(boolean_expression IN BOOLEAN,
        true_number IN NUMBER,
        false_number IN NUMBER)
RETURN NUMBER IS

 

BEGIN
    IF boolean_expression THEN
        RETURN true_number;

 

    ELSIF NOT boolean_expression THEN
        RETURN false_number;


    ELSE
        RETURN NULL;


    END IF;
END;

 

BEGIN
    DBMS_OUTPUT.PUT_LINE(
iifn(2>1,1,0));
    DBMS_OUTPUT.PUT_LINE(
iifn(2>3,1,0));

temp := iifn(null, 1, 0);

IF temp IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('NULL');
ELSE
    DBMS_OUTPUT.PUT_LINE(temp);
END IF;

END;
/

 

>> 결과

1

0

NULL

**************************************************************************************************

 

 

Procedure Block (프로시저 블럭)

 

- 함수와 비슷하다.

 

- 함수와 다른점은 함수는 값을 반환하고 식에 사용할 수 있지만, 프로시저는 값을 반환하지 않으며 식에 활용할 수 없다.

 

**************************************************************************************************

PROCEDURE 이름 [(인수목록)] {IS, AS}

변수 선언문;

BEGIN

프로그램 코드

(EXCEPTION

오류처리 코드)

END;

**************************************************************************************************

 

DECLARE
    first_number NUMBER;
    second_number NUMBER;

 

PROCEDURE swapn(num_one IN OUT NUMBER,
        num_two IN OUT NUMBER) IS
    temp_num NUMBER;
BEGIN
    temp_num := num_one;
    num_one := num_two;
    num_two := temp_num;
END;

 

BEGIN
    first_number := 10;
    second_number := 20;
    DBMS_OUTPUT.PUT_LINE('First Number =' || TO_CHAR(first_number));
    DBMS_OUTPUT.PUT_LINE('Second Number =' || TO_CHAR(second_number));

 

    DBMS_OUTPUT.PUT_LINE('Swapping...');
    swapn(first_number, second_number);

 

    DBMS_OUTPUT.PUT_LINE('First Number =' || TO_CHAR(first_number));
    DBMS_OUTPUT.PUT_LINE('Second Number =' || TO_CHAR(second_number));
END;
/

 

>> 결과

First Number =10

Second Number =20

Swapping...

First Number =20

Second Number =10
**************************************************************************************************

 

 

중첩 블럭

 

- Block 내에 Block 이 들어감

 

- 변수는 블럭 내부에서만 사용 가능하다. 상위 레벨의 변수는 하위 레벨에서 모두 사용 가능하다.

 

- BEGIN 내에 정의

 

********************************************************************************************************************

DECLARE
    error_flag BOOLEAN := false;

BEGIN
    DBMS_OUTPUT.PUT_LINE('100~1000 counting');

    DECLARE
        hundreds_counter NUMBER(1,-2);
    BEGIN
        hundreds_counter := 100;

        LOOP
            DBMS_OUTPUT.PUT_LINE(hundreds_counter);
            hundreds_counter := hundreds_counter + 100;

            IF hundreds_counter > 1000 THEN
                EXIT;
            END IF;
        END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
        error_flag := true;
    END;

    IF error_flag THEN
        DBMS_OUTPUT.PUT_LINE('Can not count.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Count Done.');
    END IF;
END;
/

 

>> 결과

100~1000 counting

100

200

...

900

Can not count.
**************************************************************************************************

 

 

Trigger Block (트리거 블럭)

 

- 트리거란 어떤 동작이나 사건이 발생했을 때 샐행되는 코드를 정의하는데 사용된다.

 

- 처음에 나타나는 구문은 트리거의 종류, 트리거와 연결된 테이블, 트리거가 발생해야 할 때를 말해주는 것이다.

 

- 예를 들어 특정 User CREATE, DROP, ALTER 등의 DDL 문을 실행하게 되면 트리거에서 사용을 막게 할 수 있다.

 

**************************************************************************************************

CREATE [OR REPLACE] TRIGGER 트리거이름

{BEFORE|AFTER} 동사목록 ON 테이블이름

[[REFERENCING 상호관계이름] FOR EACH ROW [WHEN (조건)]]

DECLARE

선언문

BEGIN

PL/SQL코드

END;

**************************************************************************************************

 

- 동사목록 : 트리거를 발생시키는 SQL 동사

 

- 테이블이름 : 트리거가 정의되는 테이블

 

- 조건 : 트리거 실행 조건

 

- 선언문 : 변수, 레코드, 커서 선언문으로 구성

 

- PL/SQL코드 : 트리거가 발생할 때 실행되는 PL/SQL 코드

 

 

2012. 10. 31. 11:23

[Oracle] PL/SQL - 개념과 예제

PL/SQL 이란?


- Procedural Language / Sturctured Query Language

- SQL에 프로그래밍 언어 설계 기능을 절차적으로 추가한 것

- 오라클사가 데이터베이스에서 SQL에 대해 절차적 로직을 실행하는 방법을 제공하기 위해 개발한 절차적 언어

- 데이터 캡슐화, 예외 처리, 정보 숨김, 객체 지향 등의 현대 S/W 공학 기능 제공

- SQL문을 블록 구조 및 프로시저 단위 코드에 포함시킬 수 있는 강력한 트랜잭션 처리 언어


PL/SQL 처리과정

- 오라클 선행 컴파일러에서 PL/SQL 블록(코드)를 제출하면 Oracle Server 내의 PL/SQL 엔진이 이를 처리한다.

- PL/SQL 엔진은 블록 내의 SQL문을 분리하여 하나씩 SQL문 실행자로 전송한다.

- PL/SQL 코드는 Oracle Server 에 저장할 수 있으며, 이름 앞에 Stored를 붙여 부른다. (Stored Procedure)

- Oracle Developer 와 같은 오라클 툴에는 Oracle Server의 엔진과는 별도로 자체 PL/SQL 엔진이 존재한다.


※ 패키지 확인

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS

          WHERE OWNER = 'SYS' AND OBJECT_TYPE = 'PACKAGE';

 

 

PL/SQL Block 구조


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * DECLARE
     변수 선언문;
BEGIN
     프로그램 코드; 
EXCEPTION
     예외 처리문;
END; 
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 

- 변수선언문 : 블럭에서 사용할 변수를 정의. 커서 정의와 중첩된 PL/SQL 프로시저 및 함수도 여기서 정의.

- 프로그램코드 : 블럭을 구성하는 PL/SQL 문.

- 예외처리문 : 런타임 오류나 예외 발생시 트리거되는 프로그램 코드.

- 각 블럭에서 문장의 끝을 알리기 위해 반드시 세미콜론(;)을 붙인다.

- BEGIN 내에 다른 블럭을 내포할 수 있다.

 


[예제1] 간단한 작성과 실행 


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * DECLARE
     x    NUMBER;
BEGIN
    x    := 100;
END;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
- 맨 끝의 '/' 슬래시는 SQL*Plus에게 PL/SQL 코드의 입력이 끝났음을 알리고 이를 오라클 데이터베이스로 보내는 역할.

 

 

[예제2] 결과 출력하기

 

- 출력은 DBMS_OUTPUT 패키지가 담당한다.

 

- 패키지 안에 dbms_output.put_line 프로시저 사용.

 

- 출력을 SQL*Plus 에서 보려면

 

SQL> SET SERVEROUTPUT ON 을 먼저 실행해야 한다.

 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
DECLARE

x    NUMBER;

BEGIN

x    := 65400;

dbms_output.put_line('The variable x = ');

dbms_output.put_line(x);

END;

/

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

- 내용을 /export/home/oracle/plsql/test.sql 파일에 저장하고 불러들일 수도 있다. 기본확장자는 .sql 이다.

 

- 간단한 내용은 vi editor를 통해 수정하면 되겠지만, Buffer는 바로 직전의 SQL문만 저장하고 있기 때문에 긴 PL/SQL 프로시저는 반드시 텍스트파일로 만들어서 실행시키는 형식을 취하도록 한다.

 

$ vi /export/home/oracle/plsql/test.sql

내용기입

:wq

SQL> @/export/home/oracle/plsql/test

 

SQL> @파일명      -> 실행결과만 나온다.

SQL> RUN 파일명  -> 프로시저의 내용과 결과가 같이 나온다.

 

 

[예제3] 함수 작성하기


- PL/SQL을 사용하여 Stored 함수와 Stored 프로시저를 작성한다. 방금까지 작성했던 코드를 Stored 함수로 캡슐화 시키면 그것을 한번만 컴파일하고 데이터베이스에 저장해 놓았다가 나중에 다시 사용할 수 있다.

- DB내에 stored 함수를 만들어 놓으면 나중엔 그 함수만 불러다 쓰면 된다.
  
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * CREATE OR REPLACE FUNCTION TEST_FUNC
RETURN NUMBER AS
    x    NUMBER;
BEGIN
    x    := 100;
    RETURN x;
END;
/
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


- OR REPLACE 는 동일한 함수가 존재하면 덮어쓰라는 의미이다.

- 1~2줄 CREATE 부터 AS 까지가 DECLARE 부분이다.

- 함수를 생성하는 것은 오라클이고 SQL*Plus는 결과만 보여줄 뿐이다.

 


[결과 확인]


- 함수를 작성하고 컴파일을 하였으니 이제 사용만 하면 된다. 함수이지만 ( )를 사용하지 않는 이유는 인수가 없기 때문.

SQL> SELECT TEST_FUNC FROM DUAL;


- 함수 생성시 STATUS가 INVALID 로 되어 있어 사용할 수 없기 때문에 코드에 잘못된 부분이 있는지 확인하고 수정 후 VALID 상태로 변경해야 한다. 오래 사용하지 않는 함수나 프로시저, 패키지도 INVALID 상태가 될 수 있다.

 

 

2011. 11. 14. 16:35

[Oracle] PL/SQL PROCEDURE Sample

CREATE OR REPLACE PROCEDURE NOSUN.CREATE_BAEHAD
(V_LSTSEQ VARCHAR2, V_BAENUM VARCHAR2) AS

  C_JBDATE INVLOG.JBDATE%TYPE;
  C_MAINNB INVLOG.MAINNB%TYPE;

  S_CNT NUMBER;
  S_DLVGUB CHAR(1);

  CURSOR C_LIST IS
    SELECT A1.MAINNB, A1.BSTSEQ, A1.DLVAMT
       FROM INVLOG A1
     WHERE A1.MAINNB = V_LSTSEQ;   
BEGIN
  S_CNT := 0;

  UPDATE INVLOG
       SET BAENUM = V_BAENUM
   WHERE BSTSEQ = V_LSTSEQ;            

  OPEN C_LIST;
  LOOP
    <<start_point>>
       
    FETCH C_LIST INTO C_MAINNB, C_BSTSEQ, C_DLVAMT;
    EXIT WHEN C_LIST%NOTFOUND;

    IF C_DLVAMT <> 0 THEN
      S_DLVGUB := '1';
    ELSE
      S_DLVGUB := '2';
    END IF;          

    BEGIN
      SELECT APPCOD, RERAT1, RERAT2
           INTO S_APPCOD, S_RERAT1, S_RERAT2
         FROM RATTRF
       WHERE APPCOD = (CASE WHEN C_BTOCHK = 'Y' THEN 'BTOC' ELSE 'ZZ00' END);

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        V_OUTPUT := '운송장번호 : '||C_MAINNB||'에 대한 운송요율이 등록되있지 않습니다.';
        EXIT;                               
    END;
 
    GOTO START_POINT;

  END LOOP;
  CLOSE C_LIST;
 
  UPDATE BEHEAD
     SET JNGCHK = 'Y'
   WHERE BAENUM = V_BAENUM;      

END CREATE_BAEHAD;
/


'Database / Sql' 카테고리의 다른 글

[Oracle] ORA-01476 : 제수가 0  (0) 2011.11.16
[Oracle] RANK() 함수 Sample  (0) 2011.11.15
[Oracle] PL/SQL FUNCTION Sample  (0) 2011.11.14
[Oracle] SAVEPOINT & TRANSACTION  (0) 2011.09.02
[Oracle] FLASHBACK  (0) 2011.09.02
2011. 11. 14. 16:25

[Oracle] PL/SQL FUNCTION Sample

CREATE OR REPLACE FUNCTION NOSUN.GET_RELCAR
( V_STRDAT IN VARCHAR2,  V_SUNCOD IN VARCHAR2 )
RETURN VARCHAR2 IS

    O_RELCAR VARCHAR2(30);
    S_CARNO4 VARCHAR2(4);

    CURSOR C_1 IS
      SELECT DISTINCT CARNO4
          FROM INVLOG
        WHERE BADATE= V_STRDAT
            AND SUNCOD= V_SUNCOD;
BEGIN

    OPEN C_1;
    LOOP
        FETCH C_1 INTO S_CARNO4;
        EXIT WHEN C_1%NOTFOUND;

        BEGIN
            O_RELCAR := TRIM(O_RELCAR || ' ' || S_CARNO4);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
            O_RELCAR := '';
        END;
    END LOOP;
    CLOSE C_1;

RETURN O_RELCAR;

END GET_RELCAR;
/

'Database / Sql' 카테고리의 다른 글

[Oracle] RANK() 함수 Sample  (0) 2011.11.15
[Oracle] PL/SQL PROCEDURE Sample  (0) 2011.11.14
[Oracle] SAVEPOINT & TRANSACTION  (0) 2011.09.02
[Oracle] FLASHBACK  (0) 2011.09.02
[Oracle] BIN$... 으로 시작하는 테이블  (0) 2011.08.31
2010. 6. 29. 14:24

[Oracle] PL/SQL

PL/SQL 이란 ?
    -  PL/SQL 은 Oracle’s Procedural Language extension to SQL. 의 약자 입니다.
    -  SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며, 오라클 자체에      
        내장되어 있는 Procedure Language입니다
    -  DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항입니다.
    -  PL/SQL 문은 블록 구조로 되어 있고 PL/SQL 자신이 컴파일 엔진을 가지고 있습니다
    
PL/SQL의 장점 
    -  PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB 로 보내서 처리하므로 수행속도를
        향상 시킬수 있습니다.
    -  PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.
    -  보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있습니다.
    -  Variable, Constant, Cursor, Exception을 정의하고, SQL문장과 Procedural 문장에서 사용합니다.
    -  단순, 복잡한 데이터형태의 변수를 선언합니다.
    -  테이블의 데이터 구조와 DataBase의 컬럼럼에 준하여 동적으로 변수를 선언 할 수 있습니다.
    -  Exception 처리 루틴을 이용하여 Oracle Server Error를 처리합니다.
    -  사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리 가능 합니다.


1. 프로시져
    -  특정 작업을 수행할수 있고, 이름이 있는 PL/SQL 블록으로서. 매개 변수를 받을수 있고, 반복적으로 
       사용할수 있습니다. 보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 데이터
       베이스에 저장하기 위해 생성합니다. 
    
     * CREATE OR REPLACE 구문을 사용하여 생성합니다. 
     * IS 로 PL/SQL의 블록을 시작합니다. 
     * LOCAL 변수는 IS 와 BEGIN 사이에 선언합니다.     
    

2. 함수(Function)
    -  보통 값을 계산하고 결과값을 반환하기 위해서 함수를 많이 사용 합니다.
    -  대부분 구성이 프로시저와 유사 하지만 IN 파라미터만 사용 할 수 있습니다.
    -  반드시 반환될 값의 데이터 타입을 RETURN문에 선언해야 합니다.
    -  또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 합니다.

3. package
    -  패키지(package)는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로지져와 함수들의
        집합 입니다
    -  패키지는 선언부와 본문 두 부분으로 나누어 집니다.  

4. 트리거
    -  INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 묵시적으로 수행되는 PROCEDURE 입니다.
    -  Trigger는 TABLE과는 별도로 DATABASE에 저장됩니다.
    -  Trigger는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있습니다.

2009. 4. 14. 10:21

[Oracle] PL/SQL에서 엔터(enter)키 역할 사용하는 방법

Chr(13) : 캐리지 리턴(carriage return)

Chr(10) : 라인피드(new line)


Chr(13)은 동일한 줄의 첫번째 자리에 커서를 위치 (캐리지 리턴)

Chr(10)은 현재 커서가 위치한 곳에서 아래로 한줄 내리는 기능 (라인피드)

 

 

예제) replace(text,'\n', chr(13)||chr(10)) -> text에서 \n을 보면 엔터키로 역할 변경