나의 향기에 젖다...

프로시저 (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 프로시저이름;

프로시저 오버로딩

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

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

재귀 프로시저

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

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;

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 상태가 될 수 있다.

## 1 ##

DELETE 
  FROM 테이블1 A
 WHERE EXISTS
            (

 SELECT 1
   FROM 테이블2 B
 WHERE A.테이블1_키 = B.테이블2_키
);

DELETE
  FROM T_CLASS_DIAG A
 WHERE EXISTS

(
 SELECT 1
   FROM T_PORTFOLIO_HISTORY
  WHERE LECTURE_ID = A.LECTURE_ID
      AND USER_ID = A.USER_ID

);

## 2 ##

DELETE

  FROM SG2EXAMT T1
 WHERE (T1.PATNO,T1.ORDDATE,T1.RESVTYPE)
          = (SELECT PATNO,ORDDATE,RESVTYPE
                FROM SG1RESVT T2
               WHERE T2.CUSTCODE =  '125000'
                   AND T2.ACPTDATE = TO_DATE('20100329','YYYYMMDD')
             ); 

클라이언트를 설치하는 입장에서 서버의 버전은 중요하지 않다.

만약 서버가 10g라해도 과감히 11g 클라이언트를 설치해도 무방하다.

오히려 11g 클라이언트가 더 잘된다.

닷넷에서 오라클 연결 시에도 다른거 필요없이 오라클 클라이언트만 깔아주면 된다.

일단 오라클 사이트에가서 클라이언트 프로그램을 다운로드 한다.

http://www.oracle.com/technology/software/products/database/oracle11g/111060_win64soft.html

### 오라클 삭제 ###

1. 시작 메뉴에서 오라클 삭제

시작 -> Oracle 메뉴 -> Universal installer -> 제품설치 해제 -> 모두 체크 제거

2 . 레지스트리 삭제
시작 -> 실행 -> regedit
1) - HKEY_Local_Machine\software\Oracle폴더 삭제.
2) - HKEY_Local_Machine\system\ControlSet001\Services\Oracle 관련서비스(폴더) 삭제
3) - HKEY_Local_Machine\system\ControlSet002\Services\Oracle 관련서비스 삭제
4) - HKEY_Local_Machine\system\CurrentControlSet\Services\Oracle 관련서비스 삭제
=> Oracle 관련 값은 모두 제거해야 한다.

3. Reboot

4. 환경 변수에서 오라클 관련 모두 삭제(내컴에서 오른 버튼 속성에 고급)

5. 탐색기에서
- 설치 파티션 : \Oracle폴더 (기본 C:\oracle)
- 부트 파티션 : \Program files\Oracle폴더

부트 파티션 삭제 시 oci.dll이 삭제 되지 않을 수 있다. 그때는 도스 커맨드에서

net stop msdtc 를 수행하여 msdtc 서비스를 멈춘다음, delete를 시도하여 보면, 삭제가 된다.
이후, net start msdtc를 통해서 다시 서비스를 가동할 수 있다.

### 오라클 설치 ###

* 받은 파일의 압축을 풀고 install에서 setup을 실행한다.

* 관리자가 필요한 사람은 관리자를 설치하고 그냥 런타임을 선택한다.

* 베이스 디렉토리는 C:\oracle 이라고 한다

* 프로그램 설이 디렉토리는 C:\oracle\product\버전...(기본으로 나오는 패스를 사용)

* 설치가 끝나면 리부팅 한번 해준다.(별 필요없지만 왠지 해주면 좋다)

* 기존의 trsnames.ora 파일 있더라도 갖다 놓지 않는다. 버전이 올라가면서 약간..다르다.

* 시작메뉴에서 오라클로 가서 Net Manager 를 수행한 후 서비스를 하나 등록한다.

* 등록하고 NetManager를 종료하면 저장할까 물어볼때 저장한다.

* 그러면 오라클 폴더의 Network/Admin 폴더 아래에 trsnames.ora 파일이 생성된다.

* 파일을 열어보면 나름대로 규칙을 가지게 되어있는데 그 형태를 유지해서 기존 서비스들도 만들어 넣는다.

* 다시 NetManager를 띄워서 하나의 서비스를 선택하고 왼쪽 툴바에 테스트 버튼을 눌러서 접속 테스트를 해본다. (접속 테스트 할때 아뒤랑 패스워드를 제대로 넣어줘야 테스트가 가능하다.)

닷넷에서 접속이 되는 지 확인 하기.

* windows 폴더 아래에 assembly 폴더를 탐색기로 열어보면 Oracle.DataAccess 파일이 보인다. 버전을 확인해 보면 Version=2.111.6.0 대충 이렇다.

* web.config 파일을 열어서 <add assembly="Oracle.DataAccess, Version=2.111.6.0, Culture=neutral, PublicKeyToken=89B483F429C47342" /> 를 추가 해 준다.