2012. 11. 27. 17:05

[Oracle] PL/SQL PROCEDURE Scripts Sample 설명

CREATE OR REPLACE PROCEDURE SAMPLE_PROCE AS

 

-- SELETE 문의 결과를 TEMP_CUR 라는 CURSOR 에 넣는다.
CURSOR TEMP_CUR IS
SELECT BWCODE, BWNAME, BWADDS
  FROM TEMP_TAB;

 

-- TEMP_CUR의 타입을 그대로 사용할 수 있는 NOW_CUR 변수선언
NOW_CUR TEMP_CUR%ROWTYPE;

 

BEGIN

 

-- 커서 사용을 위해서 연다.
OPEN TEMP_CUR;

 

-- 반복문 시작
LOOP

 

-- 커서의 ROW를 NOW_CUR로 보낸다.
FETCH TEMP_CUR INTO NOW_CUR;

-- DBMS_OUTPUT.put_line('LOOP 시작');

 

-- 커서가 끝나면 반복문을 종료한다.
EXIT WHEN TEMP_CUR%NOTFOUND;

 

--조건문 사용
IF (NOW_CUR.BWCODE  <> 'A') THEN


ELSIF -- 또는 ELSE IF

 

ELSE

  
END IF;

 


INSERT INTO NEW_TAB ( BWCODE, BWNAME, BWADDS )
VALUES ( NOW_CUR.BWCODE, NOW_CUR.BWNAME, NOW_CUR.BWADDS );

 

COMMIT;


END LOOP;


-- 커서를 닫는다.
CLOSE TEMP_CUR;


EXCEPTION 

WHEN OTHERS THEN

-- 에러 발생시 에러 메시지 출력

-- 토드의 DBMS_OUTPUT 에서 확인 할 수 있다.

DBMS_OUTPUT.put_line('ERROR');

ROLLBACK

END;

 

 

실행은 ~~

EXEC SAMPLE_PROCE;

 

 

디버깅은 ~~

CREATE OR REPLACE PROCEDURE SAMPLE_PROCE 부분을 DECLARE 로 바꾸고
중간중간마다 DBMS_OUTPUT.put_line(...) 를 이용해서 처리한다.

 

 

 

 

 

 

* 참고로 SET SERVEROUTPUT ON; 작성 후 실행해야 DBMS_OUTPUT 나옴.

 

 

Trackback 0 Comment 0
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 - 프로시저 (Procedure)  (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
Trackback 0 Comment 0
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에 관해서만 정의될 수 있습니다.

Trackback 0 Comment 0