[ORACLE] PL/SQL

 

# PL/SQL (Procedural Language/SQL)

- SQL명령어에 절차적 언어(C,JAVA 등등)형식을 도입한 언어.
- 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원

​

​

[기본SY]

DECLARE (안에 선언할게 없으면 생략가능)
      [변수,상수,CURSOR,USER_DEFINE Exception 선언]
BEGIN
      [SQL, 반복분, 조건문실행]
EXCEPTION (예외할게 없으면 생략 가능)
      [예외처리]
END;

​

# SUBPROGRAM

- 이름이 있는 PL/SQL 블록
- 나중에 실행할 동작들을 미리 저장
​

종류 - PROCEDURE(동작수행),FUNCTION(값 반환),PACKAGE(연관된 프로시저 함수 묶은거)

​

 
 

​
# PROCEDURE

- JAVA의 VOID 메서드와 비슷하다(절차적으로 수행되는 명렁을 모듈화시킴)

​

[기본SY]

​
CREATE OR REPLACE PROCEDURE name
      [매개변수]
IS 
      [변수의 선언]
BEGIN --> 필수 
      [PL/SQL Block] -- SQL문장, PL/SQL제어 문장 
      [EXCEPTION] --> 선택
       -- error가 발생할 때 수행하는 문장
END; --> 필수

# PROCEDURE 실전 예제 (PROCEDURE 생성)
  

CREATE OR REPLACE PROCEDURE transfer_account   // 이름지정, 프로시저를 만들거나 대체한다.

        ​(sid              IN             account.id%TYPE, 

         rid               IN             account.id%TYPE, 

         money         IN             account.balance%TYPE,   

         error_code    IN OUT      varchar2, 

         error_msg     IN OUT       varchar2  )

​//  매개변수선언.  변수명,형식,타입

//  형식: IN - 받아옴 OUT-내보내줌

//  타입: 메모리에서 해당 칼럼의 타입형식,크기를 다받아온다(따라서 크기는 따로지정X)

​

IS
      vlog   t_log.content%TYPE; 

      vbalance  account.balance%TYPE;

​// 지역변수. 변수명,타입(만약 타입을 직접지정시, 크기도 같이 지정해줘야함)

​ 

BEGIN

​

  vlog := sid || ' 계좌에서' || rid || ' 계좌로 ' || money || '를 출금시도합니다';   // vlog 지역변수에 내용을 담음

  insert into t_log values (log_seq.nextval, vlog);                          //  t_log테이블에 vlog변수안에 값을 넣어줌

  savepoint t1;    // 세이브포인트 지점 (롤백해도 로그는 있어야되므로 여기서함)
             BEGIN
             // BEGIN 안에 BEGIN(블락)-내부블락안의 내용에대한 예외만 따로 처리하기위해 사용.
                     SELECT balance INTO vbalance
                     FROM account
                     WHERE id = sid;
                     // account테이블의 id가 sid일때 balance칼럼의 레코드를 vbalance지역변수에 담음  
             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                     rollback to t1;
                     commit;
                     error_code := 'failure';
                     error_msg := '해당계좌가 존재하지 않습니다.';
                     WHEN TOO_MANY_ROWS THEN
                     rollback to t1;
                     commit;
                     error_code := 'failure';
                     error_msg := '복수의 계좌가 존재합니다.';
             END;

             // 내부 BEGIN 안의 SELECT에 대해서만 예외처리.​​

​    if vbalance < money then
    error_code := 'failure';
    error_msg := '송금잔액이 부족합니다';
    //  매개변수에 내용을 담음(OUT)

​    return;
    end if;

    update account1 set balance=balance-money
    where id = sid;
    update account1 set balance=balance+money
    where id = rid;
    vlog := sid || ' 게좌에서' || rid || ' 계좌로 ' || money || '를 출금완료하였습니다';
    insert into t_log values (log_seq.nextval, vlog);
    error_code := 'success';
    error_msg := '송금이 정상완료되었습니다.';
    commit;

EXCEPTION
//  메인 BEGIN의 내용 예외처리
      WHEN OTHERS THEN
      rollback to t1;
      commit;
      error_code := 'failure';
      error_msg := '기타에러가 발생했습니다.';

​
END;
/

​

​

​

​# PROCEDURE 실전 예제 2 (PROCEDURE 호출(PL/SQL))

​

DECLARE
        v_error_cd  varchar2(7)  := 'inputcd';
        v_error_msg varchar2(60) := 'input test error message';
//  변수선언.  변수이름,타입

//  내용 보낼수 있고 받을수 있다.(프로시저의 맴버변수가 in out이라서)

​

BEGIN
    transfer_account('1111','2222',990,v_error_cd, v_error_msg);
    dbms_output.put_line(v_error_cd || ' ' || v_error_msg);

END;
/

​

※set serverout on
- dbms_output.put_line 은 DBMS에서만 출력되는데
- dbms에서 출력될 내용을 SQL에서 출력(CMD)하려면 이 명령어를해줘야됨
- 디버깅 목적(개발할때 SQL에서 확인하기 위해서 잠시 씀)

​

​

# [SQLplus 명령어 중 몇개]

​
- 우리는 자바에서 UI 로 만들어서 얻어올거기 때문에 잘 안쓴다

​

ACCEPT
-사용자가 입력하는 내용(값)을 읽어 치환변수에 저장

​
VARIABLE
-Global 변수를 선언

​

EX)

ACCEPT p_sal PROMPT 'Enter the salary:'            // p_sal 변수에 내용 넣어서(Enter the salary:1000) 저장
VARIABLE g_year_sal NUMBER                           // g_year_sal 변수 선언
DECLARE
       v_sal NUMBER := &p_sal;                           // v_sal 에 p_sal에 있는 1000(치환됨) 받음
BEGIN
       :g_year_sal := v_sal*2;                              // g_year_sal에 값 받음
END;
/
PRINT g_year_sal                                            // 2000

​
# [제어문: FOR IN LOOP]

​

DECLARE
   v_num    NUMBER  DEFAULT 10;
BEGIN
      IF v_num =10 THEN
      DBMS_OUTPUT.PUT_LINE('NUMBER : '|| v_num);
      FOR  i  IN  1..v_num  LOOP                                           //  1..v_num --> 1부터 v_num 까지
      DBMS_OUTPUT.PUT( i || ' ');                                       // System.out.print 효과
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('');                                       // System.out.println 효과
      ELSE
      DBMS_OUTPUT.PUT_LINE('There is no value');
      END IF;
END;
/

​

​

​

# FUNCTION

​
- 값을 반환하는 PL/SQL . 보통 프로시저에서 호출한다.
- 프로시저문법에 RETURN 만 추가됨

​

​

 #  FUNCTION 실전 예제 

 
 
CREATE OR REPLACE FUNCTION f_emp_info

        (p_dept_id s_emp.dept_id%TYPE)
        RETURN NUMBER

IS
        v_sal      s_emp.salary%TYPE;

BEGIN
        SELECT AVG(salary)
        INTO   v_sal
       FROM s_emp
       WHERE dept_id = p_dept_id;
       RETURN v_sal;

END;
/

# RECORD TYPE 변수 선언

DECLARE
         TYPE emp_record_type IS RECORD                // RECORE 타입 선언
         (last_name VARCHAR2(25),
          first_name VARCHAR2(25),
          gender CHAR(1));
         // 필드 선언
   
         employee_record   emp_record_type;               // 변수 선언
​BEGIN
    SELECT last_name, first_name, 'F'
    INTO employee_record
    FROM s_emp
    WHERE  id = 1;
    DBMS_OUTPUT.PUT_LINE(employee_record.last_name);
    DBMS_OUTPUT.PUT_LINE(employee_record.first_name);
    DBMS_OUTPUT.PUT_LINE(employee_record.gender);
END;
/ 

 

# ROWTYPE 변수 선언

- RECORD TYPE 과 비슷하지만 훨씬 효율적이라서 더 많이 쓴다.

DECLARE
    employee_record   s_emp%ROWTYPE;            // s_emp테이블에 있는 모든 로우의 타입을 받아 올 수 있다. 
BEGIN
    SELECT *
    INTO employee_record
    FROM s_emp
    WHERE  id = 1;
    DBMS_OUTPUT.PUT_LINE(employee_record.last_name);
    DBMS_OUTPUT.PUT_LINE(employee_record.first_name);
    DBMS_OUTPUT.PUT_LINE(employee_record.salary);
END;
/

​

​

​

# TABLE TYPE 변수 선언

DECLARE
    TYPE name_table_type IS TABLE OF
     VARCHAR2(25) INDEX BY BINARY_INTEGER;
    first_name_table     name_table_type;
BEGIN
    first_name_table(1) := 'Scott';
    first_name_table(10) := 'Michelle';
    first_name_table(100) := 'Susan';
    DBMS_OUTPUT.PUT_LINE(first_name_table(1));
    DBMS_OUTPUT.PUT_LINE(first_name_table(10));
    DBMS_OUTPUT.PUT_LINE(first_name_table(100));
    DBMS_OUTPUT.PUT_LINE(first_name_table(2));
END;
/

​

​
# TABLE TYPE + ROWTYPE 변수 선언

DECLARE
    TYPE emp_table_type IS TABLE OF s_emp%rowtype
       INDEX BY BINARY_INTEGER;
   
     v_emp_table     emp_table_type;
BEGIN
    SELECT *
    INTO v_emp_table(1)
    FROM s_emp
    WHERE  id = 1;
    SELECT *
    INTO v_emp_table(2)
    FROM s_emp
    WHERE  id = 2;
    DBMS_OUTPUT.PUT_LINE('1번직원정보');
    DBMS_OUTPUT.PUT_LINE(v_emp_table(1).first_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_table(1).last_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_table(1).salary);
    DBMS_OUTPUT.PUT_LINE('2번직원정보');
    DBMS_OUTPUT.PUT_LINE(v_emp_table(2).first_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_table(2).last_name);
    DBMS_OUTPUT.PUT_LINE(v_emp_table(2).salary);
END;
/

# EXCEPTION

 [EXCEPTION 종류]
Predefined 오라클 서버 예외
: PL/SQL에서 자주 발생하는 약 20여 가지의 에러에 대해
미리 예외 이름이 정의된 것으로 다른 이름으로 재정의해서 사용할 수도 있다.

​

Non-Predefined 오라클 서버 예외
: 미리 정의되어 있지 않은 오라클 서버 에러로 선언부에 선언해 놓으면
오라클 서버가 에러 발생 시에 해당 예외를 발생시켜 준다.

​

User-Defined 예외
: 개발자가 정한 조건이 만족되지 않은 경우 선언부에 선언하고 명시적으로 발생시켜 준다.

​

DECLARE   
            v_emp_record  s_emp%ROWTYPE;
            e_too_long_string EXCEPTION;       // Non-Predefined 오라클 서버 예외

                                                           //  (서버에러지만 정의되어있지 않아서 선언해줌)
                                                           //   User-Defined 예외가 아님~!!!
            PRAGMA EXCEPTION_INIT(e_too_long_string, -12899);     //  이런에러 발생하면 안에꺼 던진다.
BEGIN
     SELECT *
     INTO v_emp_record
     FROM s_emp where id = 1;
     insert into account1 values ('ttttttttttttt',222222);      // 에러남(타입이 char(4)라서 너무 김.)
EXCEPTION
    WHEN too_many_rows THEN
    DBMS_OUTPUT.PUT_LINE('There are too many rows');
    WHEN no_data_found THEN
    DBMS_OUTPUT.PUT_LINE('There are no data founded');
    WHEN  e_too_long_string THEN
    DBMS_OUTPUT.PUT_LINE('문자열  exception occurred' );
    WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(‘기타  exception occurred' );
END;
/

​

​

​

#  CURSOR
- PL/SQL 블락안에서는 레코드가 하나만 나와야 되므로 복수의 레코드를 처리하기 위해서 사용.
- SQL 명령문을 실행하기 위한 작업 공간

​

​

[커서의 유형]

​

Implicit 커서(묵시적 커서) :
모든 SQL 문장(PL/SQL블록의 SQL 포함)에 대해 오라클 서버에 의해 자동으로 만들어짐

​

Explicit 커서(명시적 커서) :
프로그래머가 선언
블록의 실행 가능한 부분에서 특정 명령을 통해 조작함 
복수의 레코드를 select 할때만 사용

​

​

[Explicit 커서 속성]

%ISOPEN
: 커서가 OPEN되어 있으면 TRUE를 리턴

​
%NOTFOUND
: 더이상 FETCH할 행이 없으면 TRUE를 리턴

​
%FOUND
: FETCH할 행이 남아 있으면 TRUE를 리턴

​
%ROWCOUNT
: 이제까지 FETCH된 전체 행의 수

​

​

​
#  CURSOR 실전 예제 (커서 사용)

​

CREATE OR REPLACE PROCEDURE p_emp_info

          (p_dept_id  s_emp.dept_id%TYPE)

IS
      CURSOR  c_emp_sal  
      IS
            SELECT last_name, salary  
            FROM s_emp
           WHERE dept_id = p_dept_id;

      // CURSOR 선언  

      v_sal              s_emp.salary%TYPE;
      v_last_name    s_emp.last_name%TYPE;

​BEGIN
        OPEN c_emp_sal;            // CURSOR 호출(이때 SELECT문이 메모리에 올라감)
        LOOP
        FETCH  c_emp_sal  INTO v_last_name, v_sal ;
        IF  c_emp_sal%NOTFOUND 
       THEN EXIT;
       END IF;
       DBMS_OUTPUT.PUT_LINE(c_emp_sal%rowcount || ' ' || v_last_name || ' ' || v_sal);
       END LOOP;
       CLOSE c_emp_sal;
END;
/

[PROCEDURE 호출]

​

SQL 명령어

begin P_emp_infor(31);
end;
/

call P_emp_infor(31);
(오라클 9i부터 사용 가능)

SQLPLUS 명령어
execute P_emp_infor(31)

​

​
# 실전 예제 2 (커서와 레코드 타입)

 

CREATE OR REPLACE PROCEDURE p_emp_info

          (p_dept_id s_emp.dept_id%TYPE)

IS
          CURSOR  c_emp_sal  
          IS
          SELECT last_name, salary 
          FROM s_emp
          WHERE dept_id = p_dept_id;
          // CURSOR 선언 
  
         emp_record c_emp_sal%ROWTYPE;
         // 커서명%ROWTYPE(커서에 SELECT가 가져온 결과행 다 담는 레코드 타입 변수 선언 가능)

BEGIN
         OPEN c_emp_sal;                        // CURSOR 호출(이때 SELECT문이 메모리에 올라감)
         LOOP
         FETCH  c_emp_sal  INTO emp_record ;
         IF  c_emp_sal%NOTFOUND  THEN
         EXIT;
         END IF;
         DBMS_OUTPUT.PUT_LINE(c_emp_sal%rowcount || ' ' ||   

                                             emp_record.last_name || ' ' || emp_record.salary);
         END LOOP;
         CLOSE c_emp_sal;

END;
/

 # 실전 예제 3 (CURSOR FOR 루프)

CREATE OR REPLACE PROCEDURE p_emp_info
            (p_dept_id s_emp.dept_id%TYPE)
IS
            CURSOR  c_emp_sal  
            IS
            SELECT last_name, salary  
            FROM s_emp
            WHERE dept_id = p_dept_id;

BEGIN
         FOR  emp_record  IN  c_emp_sal  LOOP
         // FOR 변수 IN 커서명 LOOP 하면 변수생성,FETCH,OPEN,CLOSE 다됨.. 개쩜..다 이거씀..
         DBMS_OUTPUT.PUT_LINE(emp_record.last_name || ' '
                            || emp_record.salary);
         END LOOP;
END;
/