카테고리 없음

Oracle PL/SQL 소개

JangGeonWu 2022. 8. 17. 12:09




Oracle의 꽃이라 불리는 PL/SQL의 기본을 기록하고자 한다.


PL/SQL

  • SQL의 DML문과 Query문, 그리고 절차형 언어인 IF, LOOP 등을 사용하여 절차적으로 프로그래밍을 가능하게 한 강력한 트랜잭션 언어

다음은 PL/SQL의 특징이다.

  1. 프로그램 개발의 모듈화: 블록과 서브블록 단위로 프로그램을 정의한다.
  2. 변수 선언: 테이블과 칼럼의 데이터 타입을 기반으로 하는 유동적인 변수를 선언한다.
  3. 절차형 언어의 사용: IF문 = 조건에 따른 실행, LOOP문 = 반복실행, Explict Cursor = 여러 행 검색
  4. 에러 처리: Exception 처리 루틴을 통해 에러를 처리
  5. 이식성: PL/SQL은 Oracle에 내장되어 있어 어떤 호스트로도 프로그램을 이식할 수 있다.
  6. 성능 향상: 여러 SQL 문장을 블록으로 묶고 한번에 블록 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.


PL/SQL의 기본 블록 구성도

기본적인 PL/SQL 블럭 구조는 세 부분으로 구성되는데, 블럭의 각 부분은 다음과 같다.

DECLARE
(선언부)
BEGIN
(실행부)
EXCEPTION
(에러처리부)
END;
부분 설명 포함 여부
선언부 실행부에서 참조할 모든 변수와 상수, 커서, EXCEPTION(사용자 정의 에러)를 선언한다 옵션
실행부 데이터를 처리할 PL/SQL무늘 기술 필수
에러 처리부 에러가 발생했을 때 수행될 문장을 기술 옵션


   

PL/SQL 프로그램 작성 요령

  1. 블럭 내에서는 한 문장이 종료할 때마다 세미콜론을 붙인다.
  2. END 뒤에 ;를 꼭 붙여 '하나의 블럭이 끝났다'는 것을 명시한다.
  3. 편집기를 통해 파일로 작성해도 되고, SQL 프롬프트에서 작성해도 된다.
  4. SQL *Plus환경에서는 DECLARE나 BEGIN이라는 키워드로 PL/SQL 블록이 시작하는 것을 알 수 있다.
  5. CREATE 명령이 실행되기 위해서는 /가 필요하다.


PL/SQL 프로그램 종류

이름 용도
FUNCTION 값을 리턴
PROCEDURE 행동만, 값을 리턴하지 않음
TRIGGER 이벤트에 의해 자동적으로 실행된다
PACKAGE 변수, 상수, 서브프로그램등의 항목을 묶어놓은 객체

각 프로그램의 구조

  1. FUNCTION의 구조
CREATE [OR REPLACE] FUNCTION func_name(prarmeter_name, parameter_type, parameter_datatype)
RETURN return_type
IS 변수선언
BEGIN 실행문선언
    RETURN 값;
EXCEPTION 예외처리
END;
  • 헤더의 *"IS"* 키워드 다음에 DECLARE는 기본 BLOCK 구조에서 생략되어야 한다. 그래서 변수선언을 *"IS"* 키워드 다음에 선언하는 것.
  • parameter_type : in, out, inout


  1. PROCEDURE의 구조 
    CREATE [OR REPLACE] PROCEDURE proc_name
(parameter_name, parameter_type, parameter_datatype)
IS 변수선언
BEGIN 실행문선언
EXCEPTION 예외처리
END;
  • 'RETURN' 키워드가 없는 걸 볼 수 있다.


  1. Parameter 타입 및 선언 방법 
    CREATE OR REPLACE PROCEDURE p_name
(
 v_num in number,
 v_tax out number
)
IS
BEGIN
 v_tax := v_num*0.07;
END;
  • paramter_type에서 in은 입력되는 값, out은 출력되는 값, inout은 입력되고 출력도 되는 값


PL/SQL 실행

    • FUNCTION 실행 예시
SQL>EXECUTE :a := tax(100)
    • PROCEDURE 실행 예시
SQL>EXECUTE P_TAX(100, :a)




다음 예제를 보면서 이해해보자.

1. SQL>VARIABLE a NUMBER
2. SQL>EXECUTE :a := tax(100)
   PL/SQL 처리가 정상적으로 완료되었습니다.

3. SQL>EXECUTE P_TAX(100, :a)
     A
------
     7
  1. a라는 NUMBER 타입의 변수를 선언한다.
  2. FUNCTION을 실행해 리턴되는 값을 a에 기록한다. 이때 ':='가 대입 연산자이다.
  3. 파라미터 100, :a을 적용한 프로시저를 실행한다.


변수 선언

  • 변수들을 사용하기 위해서, DECLARE에서 선언을 해야 한다. 
    IDENTIFIER_NAME [CONSTANT] DATATYPE [NOT NULL] [:=VALUE];
파라미터 의미
CONSTANT 상수라는 뜻으로, 값을 변동시킬 수 없다
NOT NULL NULL을 허용하지 않겠다는 의미
  • 초기값은 할당 연산자(:=)를 사용해 정의하며, 초기값을 정의하지 않으면 NULL값을 가진다.


데이터 타입에 따른 변수의 '종류'는 아래와 같다.

 

종류 뜻
Scalar 1개의 값
Composite 복합 변수
Reference %TYPE, %ROWTYPE처럼 참조되는 값
LOB(Large OBject) Large Object 타입 처리를 위한 변수
비PL/SQL 변수 BINT, HOST 등 SQL*PLUS 변수 등

 



scalar 타입 변수

  • scalar 데이터 형태는 1개의 값을 가지며, 숫자-문자-날짜-BOOLEAN(참, 거짓)으로 나눌 수 있다.



    scalar에 해당하는 데이터 타입은 아래와 같다.
    데이터 타입 설명
    binary_integer 일반적인 정수
    pls_integer 위보다 저장 공간을 적게 사용하고 number나 binary_integer보다 빠름
    number(a,b) 고정 및 부동 소수점
    char(n) 고정길이 문자
    long 가변길이 문자
    varchar2(n) 가변길이 문자, 길이를 정의할 수 있다
    date 날짜 및 시각
    boolean 논리(TRUE, FALSE, NULL)

"어... 그런데 pls_integer 설명... 저게 뭔소리야?"라고 생각할 수 있으므로 짧게 짚고 넘어가자면,
number와 binary_integer는 라이브러리를 이용하고, pls_integer는 기계적 연산을 이용하기 때문에 계산 속도가 차이나게 된다.
(당연히 기계적 연산이 더 빠름)
뭐... 빠른건 좋지만, PL/SQL 내에서만 이용가능하기 때문에 Oracle 9i 버전까지만 많이 사용되었다.

%TYPE attribute

  • 변수의 데이터 타입을 '칼럼'에 맞추어 선언하기 위해서 사용된다.

백문이 불여일견, %TYPE의 구성을 보면서 분석해보자.

DECLARE
 v_name s_emp.name%TYPE;
 v_sal  s_emp.sal%TYPE;
 v_num  NUMBER(5,2);
 v_total_num    v_num%TYPE;
  • 첫번째와 두번째 줄에서는 s_emp 테이블의 name, sal 칼럼의 데이터 타입을 v_name, v_sal이라는 변수에 적용했다.
  • 네번째 줄에서는 v_num 변수의 데이터 타입을 v_total_num에 적용했다. v_num의 데이터 타입이 NUMBER(5,2)이므로 v_total_num의 데이터 타입 역시 NUMBER(5,2)가 된다.


%ROWTYPE attribute

  • 테이블이나 뷰의 컬럼들로 구성된 record를 선언할 때 %ROWTYPE attribute를 이용하면, Record 내의 Field는 테이블이나 View의 컬럼명과 데이터 타입을 그대로 가져온다.
  • 이 타입은 SELECT 구문으로 하나의 행을 검색하거나 Explict Cursor를 사용해 다중 행을 검색할 때 많이 사용된다.

사용법은 아래와 같다.

DECLARE
    dept_record s_dept%ROWTYPE;

이렇게 변수를 지정하면

...
BEGIN 
  SELECT 'A' 
      INTO dept_record.col1
    FROM DUAL;
END;

이런식으로 사용이 가능하다.

Composite Type 변수

  • composite type은 'TABLE'과 'RECORD'로 나뉜다.

1. TABLE

  • TABLE 타입은 '1차원 배열'의 형태와 유사하며, 데이터베이스의 테이블과는 관련이 없다.
  • '배열'의 크기는 정해저 있지 않다(동적 할당)
  • TABLE을 색인하는데 사용되는 'BINARY_INTEGER 타입의 Primary key'와 'TABLE 요소를 저장하는 Scalar 데이터 타입', 총 2개의 구성요소가 있다.

다음은 TABLE TYPE을 선언하는 방식이다.

DECLARE
TYPE type_name IS TABLE OF scalar_datatype
[NOT NULL]
    INDEX BY BINARY_INTEGER;
-- 이 위까지가 TABLE Type 정의하는 것 --   
identifier_name typename;
-- 이건 정의한 TABLE Type을 사용하는 것 --
  • 아 맞다, Reference 데이터타입이라도 참조된 데이터타입이 'scalar' 데이터타입에 해당되기만 하면 된다.

바로 아래 예시처럼

DECLARE
    TYPE name_table_type IS TABLE OF s_emp.name%TYPE
        INDEX BY BINARY_INTEGER;

name_table  name_table_type; -- 정의한 composite type 변수를 사용

2. RECORD

  • RECORD 타입은 '구조체'와 유사하다.
  • 데이터 타입은 'SCALAR', 'COMPOSITE(RECORD, TABLE)' 중 하나 이상의 요소를 가지고 있어야 하며, 다른 데이터타입은 가질 수 없다.
  • 주로 테이블의 ROW를 읽어 올 때 사용된다.

다음은 RECORD TYPE를 선언하는 방식이다.

DECLARE
TYPE type_name IS RECORD(
    field_name1 datatype [not null, := 표현식],
    field_name2 datatype [not null, := 표현식], ...);

identifier_name typename;
)
  • record type 내에, 여러 종류의 field가 존재하는 것을 볼 수 있다.

예시

DECLARE
 TYPE emp_record_type IS RECORD(
    id  NUMBER(7),
    name    VARCHAR2(25),
    start_date DATE NOT NULL := SYSDATE   
 );

 emp_record emp_record_type;

기타 변수

    1. LOB
      • LOB 변수를 이용해서 구조화되지 않은 데이터 블럭들인 '텍스트', '그래픽', '이미지', '비디오', '사운드' 등을 4GB까지 저장할 수 있다.
      • 이러한 LOB 데이터 타입은 데이터에 대한 임의(Random) 액세스도 가능하다.
        변수명 설명
        BLOB 대형 이진 데이터를 DB 내에 저장한다(사진, 용량 감당 가능할 때)
        BFILE 대형 이진 데이터를 DB 외에 저장한다(영화, 용량 감당이 안될 때)
        CLOB 단일 바이트 문자 데이터(텍스트)를 데이터베이스에 저장한다(C가 char를 의미)
    2. BIND 변수
      • 호스트 환경에서 선언한 변수로서 RUN TIME 값을 하나 이상의 PL/SQL 프로그램과 주고 받는데 사용한다.
      • 이 변수는 SQL*PLUS 환경에서 변수를 사용하기 위해 VARIABLE 명령어를 사용한다.
SQL>VARIABLE a NUMBER

                 또한, 바인드 변수의 값을 나타내기 위해 PRINT 문을 사용한다.

SQL> PRINT a

      3. HOST 변수

  • 참조 대상에 콜론을 접두어로 붙여 pl/sql의 변수와 구분을 하여 사용한다.
  • 예시


  • VARIABLE b_avg NUMBER -- SQL*PLUS 환경에서 변수 사용 ACCEPT b_math PROMPT 'Input math value :' ACCEPT b_eng PROMPT 'Input english value :' -- 프롬프트로부터 값을 입력받는다 DECLARE v_math NUMBER(9,2) := &b_math; v_eng NUMBER(9,2):= &b_eng; -- 프롬프트로부터 입력받은 값을 v_math와 v_eng에 대입 BEGIN :b_avg := (v_math + v_eng)/2; -- b_avg는 PL/SQL의 변수이므로 콜론(:)이 앞에 붙는다. END; / PRINT b_avg -- 바인드 변수인 b_avg를 출력

변수 사용, 변수 영역

    • 변수에 값을 정의하거나 재정의하기 위해서, PL/SQL 할당 연산(:=)을 사용한다.
변수 := 값;
  • PL/SQL에서는 SQL에서 사용되는 논리, 산술, 연결 연산자와 추가된 지수연산자(**)를 사용할 수 있다.
DECLARE X INTEGER
BEGIN
    DECLARE Y NUMBER
    BEGIN ...
    END;
...
END;

X는 전역변수, Y는 지역변수처럼 생각하면 된다.

마지막으로, 'FUNCTION 선언 및 사용'과 'PROCEDURE 선언 및 사용' 예제를 보며 마무리 하겠다.

  1. FUNCTION
    0. CREATE OR REPLACE FUNCTION sum_dept
    1. (v_dept_id IN s_emp.dept_id%TYPE)
    2. RETURN s_emp>salary%TYPE -- 밑의 사용 예시를 보아, NUMBER 형임을 예측 가능
    3. IS
    4.     v_sum_salary    s_emp.salary%TYPE;
    5. BEGIN
    6.     SELECT SUM(salary)
    7.     INTO v_sum_salary
    6.     FROM s_emp
    6.     WHERE dept_id = v_dept_id;
    8.     RETURN(v_sum_salary);
    9. END;
    0. sum_dept라는 FUNCTION 생성
    1. 파라미터 v_dept_id를 선언, IN으로 보아 해당 값은 '입력값'임을 알 수 있다.
    2. 리턴되는 데이터 타입을 정의..하는 김에 데이터 변수명도 정의

    4. 함수 내에서 쓰이는 변수를 선언
    
    6. s_emp에서 dept_id가 v_dept_id(입력된 id값)인 salary의 총합을 출력해 v_sum_salary에 입력한다.
    7. 의 INTO는 변수에 값을 저장하기 위해 쓰인다.
    8. 변수 v_sum_salary라는 값을 리턴한다.

 

 2. PROCEDURE

 

CREATE OR REPLACE PROCEDURE show_dept
(v_id s_dept.dept_id%TYPE)
IS
    v_dept  s_dept%ROWTYPE;
BEGIN
    SELECT *
    INTO v_dept
    FROM s_dept
    WHERE dept_id = v_id;
    DBMS_OUTPUT.PUT_LINE('부서명: '|| v_dept.name);
    DBMS_OUTPUT.PUT_LINE('지역번호: '|| v_dept.region_id);
END;
/

 

SQL> EXECUTE show_dept(110);
부서명: 기획부
지역번호: 1
PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

 

 

 

 

 

  • 지금까지 PL/SQL 블록(FUNCTION, PROCEDURE) 구성 및 변수 선언에 대하여 알아보았다.