PL/SQL 변수

PL/SQL 에서 변수를 사용하는 이유

- 변수는 데이터의 임시 저장 영역이다.

- 저장된 값을 조작하기 위해 사용

- 저장된 값을 반복해서 재 사용할 수 있다.

PL/SQL 에서 사용될 변수의 규칙 

- 반드시 문자로 시작해야만 한다.

- 문자나 숫자, 특수문자를 포함할 수 있다.

- 변수명은 30 bytes 이하여야 한다.

- 예약어를 포함하면 안된다

주요 변수들의 종류

단순 변수

-Scalar(스칼라) 변수 와 Reference 변수

Scalar 변수 - 단일 값을 가지는 변수의 데이터 형을 직접 지정해 주는 변수, 즉 정수, 실수 이런 형태를 직접 지정

Reference 변수 - 데이터 형을 다른 컬럼에서 참조 후 지정하는 방식

(1) Scalar 변수

문법 : Identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expr] ;

- Identifier : 변수의 이름으로 다른 변수와 구별되는 식별자의 역할을 하며 블록 내에서는 유일해야 한다.

- CONSTANT : 읽기전용 변수, 즉 상수로 선언하기 위한 키워드로 기본적으로 초기값이 반드시 지정되어야 한다.

    (값이 없을 경우 생략 가능) 예. V_RATE CONSTANT NUMBER := 0.2 ; 

- Datatype : 오라클에서 허용하는 데이터 타입은 모드 지원 

- %TYPE 을 사용하여 테이블내의 컬럼과 동일한 데이터 형을 선언할 수 있음

- %ROWTYPE 을 사용하여 테이블의 레코드 구조와 동일 형태의 레코드를 선언할 수 있음

- NOT NULL : 항상 값을 가지도록 제약을 주는 키워드로 이 값을 줄 경우 초기값이 반드시 지정되어야 하며 없을 경우                      생략 가능    예.V_NAME VARCHAR2(14) NOT NULL := '이순신' ;

- := 또는 DEFAULT : 변수에 기본값을 할당하기 위한 키워드   예. V_DATE DATE DEFAULT SYSDATE ;

- expr : 변수에 부여할 기본값을 의미하며 단순한 ㄱ밧에서부터 다른 변수, 수식, 함수가 올 수 있다.

주요 스칼라 변수 선언 예

Vno number(5,3) <- 숫자를 저장하는 변수로 총 5자리이며 소수점이하 3자리를 의미

Vname varchar2(10) <- 문자를 저장하는 변수로 총 10바이트의 길이를 저장할 수 있다

Vday date <- 날짜를 저장하는 변수

(2) 주요 Scalar 변수의 데이터 타입

- CHAR[(최대길이)] : 고정 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장. 기본 최소값=1

- VARCHAR2 (최대길이) : 가변 길이의 문자를 저장하며 최대 32,767 바이트 값을 저장. 기본값 X

- NUMBER [(전체자리수, 소수점이하 자리수)] : 전체 자리수와 소수점 이하의 자리수를 가진 숫자

 전체 자리수의 범위는 1부터 38까지, 소수점 이하 자리수 범위는 -84 부터 127 까지

- BINARY_INTEGERR : -2,147,483,647 ~ 2,147,483,647 사이의 정수를 저장하는 타입

- PLS_INTERGER : -2,147,483,647 ~ 2,147,483,647 사이의 부호 있는 정수에 대한 기본 유형

NUMBER 값보다 저장 공간이 적게 필요하고 연산 속도가 더 빠름

- BOOLEAN : 논리적 계산에 사용 가능한 세 가지 값(TRUE, FALSE, NULL) 중 하나를 저장하는 기본 유형

- BINARY_FLOAT : IEEE 754 형식의 부동 소수점 수를 나타냄. 값을 저장하기 위해 5바이트가 필요

- BINARY_DOUBLE : IEEE 754 형식의 부동 소수점 수를 나타냄. 값을 저장하기 위해 9바이트가 필요

- DATE : 날짜 및 시간에 대한 기본 유형

- TIMESTAMP : DATE 데이터 유형을 확장하고 연도, 월, 일, 시, 분, 초 및 소수로 표시되는 초 단위를 저장

구문 : TIMESTAMP[(precision)]으로 나타내고 선택적 파라미터인 precision 은 초 필드의 소수부분 자릿수를 지정

   자릿수를 지정하려면 0-9 범위의 정수를 사용해야 함. 기본값은 6

Reference 변수 (참조 변수)

변수가 지정되어야 할 정확한 데이터 형태를 모를 경우 해당 데이터가 들어 있는 컬럼의 정보를 참조하게끔 설정

예 ) Vno emp.empno%TYPE <- emp 테이블의 empno와 동일한 데이터형으로 선언

Vname emp.empno%TYPE <- emp 테이블의 ename 과 동일한 데이터형으로 선언

Vrow emp%ROWTYPE <- emp 테이블의 여러 컬럼을 한꺼번에 저장할 변수로 선언

ROWTYPE 변수는 하나의 테이블에 여러 컬럼의 값을 한꺼번에 저장할 수 있는 변수를 의미

(1) TYPE 변수를 사용하여 데이터 조회

emp3 테이블에서 empno가 7900 번인 사원의 empno, ename, sal 을 조회하여 화면에 출력

-실습을 위해 emp테이블을 복사해서 emp3 테이블을 생성

SCOTT>CREATE TABLE emp3 

2    AS

3    SELECT empno,ename,sal

4    FROM emp ;

SCOTT>SET SERVEROUTPUT ON ;

SCOTT>DECLARE

2    vno emp3.empno%TYPE ;

3    vname emp3.ename%TYPE ;

4    vsal emp3.sal%TYPE ;

5    BEGIN

6    SELECT empno,ename,sal

7    INTO vno,vname,vsal

8    FROM emp3

9    WHERE empno=7900 ; 

10  DBMS_OUTPUT.PUT_LINE(vno||'  '||vname||'   '||vsal) ;

11  END ;

12  /

-> 7900 JAMES 950

(2) ROWTYPE 변수를 사용하여 데이터 출력

SCOTT>DECLARE

2    v_row emp3%ROWTYPE ;

3    BEGIN

4    SELECT * INTO v_row

5    FROM emp3

6    WHERE empno=7900 ;

7    DBMS_OUTPUT.PUT_LINE(v_row_empno||'**'||v_row.ename||'**'||v+row.sal_ ;

8    END ;

9    /

-> 7900**JAMES**950

(3) ROWTYPE 변수를 활용한 데이터의 입력

SCOTT>CREATE TABLE row_test

2    (no NUMBER,

3     name VARCHAR2(10),

4     hdate DATE) ;

SCOTT>CREATE TABLE row_test2

2    AS SELECT * FROM row_test ; 

SCOTT>INSERT INTO row_test VALUES (1,'AAA',SYSDATE) ;

SCOTT>INSERT INTO row_test VALUES (2,'BBB',SYSDATE) ;

SCOTT>INSERT INTO row_test VALUES (3,'CCC',SYSDATE) ;

SCOTT>COMMIT ;

SCOTT>DECLARE

2    v_record row_test%ROWTYPE ;

3    BEGIN

4    SELECT * INTO v_record

5    FROM row_test

6    WHERE no=1 ;

7    INSERT INTO row_test2

8    VALUES v_record ; 

9    END ;

10   /

SCOTT>SELECT * FROM row_test2 ;

NO    NAME    HDATE

---  ------  -------

   1     AAA    27-MAR-12

 

(4) ROWTYPE 변수를 활용한 데이터의 변경

SCOTT>DECLARE

2    v_record row_test%ROWTYPE ;

3    BEGIN

4    SELECT * INTO v_record

5    FROM row_test

6    WHERE no=1 ;

7    UPDATE row_test2

8    SET row=v_record

9    WHERE no=1 ;

10   END;

11    /

SCOTT>SELECT * FROM row_test2 ;

NO    NAME    HDATE

---  ------  ----------

   1    DDD     27-MAR-12     <- 원래 'AAA' 에서 'DDD' 로 바뀜

변수 예제 1

%TYPE 변수를 사용하여 emp, dept 테이블을 조인하여 empno=7900인 사람의 정보를 4개의 변수에 넣은 후 empno, ename, deptno, dname을 출력

SCOTT>DELCARE

2    v_empno emp.empno%TYPE ; 

3    v_ename emp.ename%TYPE ; 

4    v_deptno dept.deptno%TYPE ;

5    v_dname dept.dname%TYPE ;

6    BEGIN

7    SELECT e.empno, e.ename, d.deptno, d.dname

8    INTO v_empno, v_ename, v_deptno, v_dname

9    FROM emp e,dept d

10   WHERE e.empno=7900 

11   AND e.deptno=d.deptno ;

12   DBMS_OUTPUT.PUT_LINE(v_empno||'  '||v_ename||'  '||v_deptno||'  '||v_dname) ;

13   END ;

14   /

-> 7900 JAMES 30 SALES

변수 예제 2

사용자로부터 두 개의 숫자를 입력 받아서 합을 구하시오

SCOTT>DECLARE

2    v_no1 NUMBER := &no1 ;

3    v_no2 NUMBER := &no2 ;

4    v_sum NUMBER ;

5    BEGIN

6    v_sum := v_no1 + v_no2 ;

7    DBMS_OUTPUT.PUT_LINE('첫 번째 수:'||v_no1||', 두번째 수 :'||v_no2||', 합은 :'||v_sum||' 입니다') ;

8    END ;

9    /

Enter value for no1 : 20

Enter value for no2 : 40

-> 첫 번째 수 : 20, 두번째 수 : 40, 합은 : 60 입니다

복합 변수 ( 조합 변수)

변수 하나 안에 사용자가 원하는 여러 가지 다른 유형의 데이터를 포함해서 사용자가 원하는 새로운 형태로 만듬

복합변수 - Record Type 변수 와 Table Type (컬렉션 타입) 변수로 나눌 수 있음

Record Type 변수 : 데이터 타입이 다른 여러 건을 저장할 경우

Table Type (컬렉션 타입) 변수 : 동일한 데이터 타입의 데이터를 여러 건 저장하고 싶을 경우 

(1) Record Type 복합 변수

- 크게 %ROWTYPE 을 사용하는 테이블 기반의 Record Type과 사용자가 원하는 컬럼만을 지정해서 만드는 

프로그래머 정의 기본 Record Type 으로 구분

PL/SQL Record 정의와 선언

1. TYPE type_name IS RECORD ( field_declaration[, field_declaration]...) ;

2. Identifier type_name

Record Type 변수의 선언 및 사용 예 )

프로그래머 정의 Record Type 변수 사용 예 1 :

Record type 변수를 활용하여 부서번호가 30 번인 부서의 부서번호와 부서명과 지역명을 Record type 변수에 저장한 후 출력. 단 record type 의 type 명은 dept_record_type 로 지정하겠음.

SCOTT>DECLARE

2    TYPE dept_record_type IS RECORD

3    (deptno dept.deptno%TYPE,

4     dname dept.dname%TYPE,

5     loc       dept.loc%TYPE) ;

6     v_dept dept_record_type ;

7    BEGIN

8    SELECT deptno, dname, loc

9    INTO v_dept

10   FROM dept

11   WHERE deptno = 30 ;

12   DBMS_OUTPUT.PUT_LINE('부서번호  부서명  위치') ;

13   DBMS_OUTPUT.PUT_LINE(v_dept.deptno||'    '||

14                                         v_dept.dname||'    '||

15                                         v_dept.loc) ;

16    END ;

17    /

부서번호    부서명    위치

-------   ------   --------

30             SALES    CHICAGO

프로그래머 정의 Record Type 변수 사용 예 2 :

emp2 테이블을 사용하여 사용자로부터 사원 번호를 입력 받은 후 아래와 같이 사원번호, 사원이름, 직급, 생일, 연락처, 급여를 출력, 단 직급이 없는 사원은 직급을 사원으로 표시해서 출력

Enter value for empno : 20000102

사원번호 : 20000102

사원명 : 김설악

직급 : 사원

생일: 22-MAR-83

연락처 : 031)234-5678

급여 : 30000000

SCOTT>DECLARE

2    TYPE e2_rec_type IS RECORD (

3    vempno    emp2.empno%TYPE,

4    vname      emp2.name%TYPE,

5    vposition   emp2.position%TYPE,

6    vbirth        emp2.birthday%TYPE,

7    vtel           emp2.tel%TYPE,

8    vpay         emp2.pay%TYPE) ;

9    v_e2_record    e2_rec_type ;

10   v_empno emp2.empno%TYPE := '&empno' ;

11   BEGIN

12   SELECT empno,name,NVL(position,'사원'),birthday,tel,pay

13   INTO v_e2_record

14   FROM emp2

15   WHERE empno=v_empno ;

16   DBMS_OUTPUT.PUT_LINE('사원번호:'||v_e2_record.vempno) ;

17   DBMS_OUTPUT.PUT_LINE('사  원 명:'||v_e2_record.vname) ;

18   DBMS_OUTPUT.PUT_LINE('직      급:'||v_e2_record.vposition) ;

19   DBMS_OUTPUT.PUT_LINE('생      일:'||v_e2_record.vbirth) ;

20   DBMS_OUTPUT.PUT_LINE('연  락 처:'||v_e2_record.vtel) ;

21   DBMS_OUTPUT.PUT_LINE('급      여:'||v_e2_record.vpay) ;

22   END ;

23    /

(2) Table Type 변수 (컬렉션 타입)

대부분의 경우 1 개의 컬럼만 저장을 하지만 Row Type 을 사용하여 Record Type 과 같이 여러 가지 유형의 데이터 컬럼을 가질 수도 있음. 그러나 권장방법은 아님

컬렉션 타입의 종류

- 연관 배열

- 중첩 테이블

- VARRAY

연관 배열의 특성

- 연관 배열은 변수 선언 당시 채워지지 않으며 키나 값을 포함하지 않으므로 선언에서 연관 배열을 초기화 할  수 없습니다.

- 연관 배열을 채우려면 명시적 실행문이 필요

- 연관 배열의 크기에 제약이 없음. 따라서 새 행이 추가됨에 따라 연관 배열이 증가하도록 행 수가 동적으로 늘어날 수 있다.

컬렉션 타입 정의와 선언

문법 :

1. TYPE type_name IS TABLE OF {column_type|variable%type|table.column%type}[NOT NULL]|table%ROWTYPE[INDEX BY BINARY_INTEGER] ;

2. Identifier type_name

- Table Type 변수 사용 예 1 :

Table Type 변수를 사용하여 사원번호가 7499 인 사원의 이름을 조회해서 해당 변수에 저장 한 후 출력.

SCOTT>DECLARE

2    t_name    VARCHAR2(20) ;

3    TYPE tbl_emp_name IS TABLE OF

4    emp.ename%TYPE

5    INDEX BY BINARY_INTEGER ;

6    v_name tbl_emp_name ;

7    BEGIN

8    SELECT ename INTO t_name

9    FROM emp 

10   WHERE empno=7499 ;

11   v_name(0) := t_name ;

12   DBMS_OUTPUT.PUT_LINE(v_name(0)) ;

13   END ;

14    /

- Table Type 변수 사용 예2 : 커서를 사용해서 생성하는 방법

professor 테이블의 rowtype 을 가지는 prof_table_type 이라는 이름으로 새로운 타입을 생성 한 후 v_prof_row 라는 변수에 할당. 그리고 v_prof_row 변수의 첫 번째 행 (0번 행) 에 데이터를 입력 한 후 화면에 출력

SCOTT>DECLARE

2    TYPE prof_table_type IS TABLE OF professor%rowtype

3    INDEX BY binary_integer ;

4    v_prof_row prof_table_type ;

5    BEGIN

6    v_prof_row(0).profno := 9000 ;

7    v_prof_row(0).name := 'imsy' ;

8    v_prof_row(0).id := 'imsy_id' ;   

9    v_prof_row(0).position := 'imsy' ;

10  v_prof_row(0).pay := '000' ;  

11   v_prof_row(0).hiredate := sysdate ;

12   DBMS_OUTPUT.PUT_LINE(v_prof_row(0).profno||' '||v_prof_row(0).name||' '||v_prof_row(0).id||' '||

13                                       v_prof_row(0).position||' '||v_prof_row(0).pay||' '||v_prof_row(0).hiredate) ; 

14   END ;

15   /

-> 9000 imsy imsy_id imsy 0 01-JUL-13

- Table Type 변수 사용 예 3 : For 반복문을 사용하여 변수에 여러 건의 데이터를 입력하는 방법

SCOTT>DECLARE

2    TYPE e_table_type IS TABLE OF

3    emp.ename%TYPE

4    INDEX BY BINARY_INTEGER ;

5    tab_type e_table_type ;

6    a BINARY_INTEGER := 0 ;

7    BEGIN

8    FOR i IN (SELECT ename FROM emp) LOOP

9        a := a+1 ;

10       tab_type(a) := i.ename ;

11   END LOOP ;

12   FOR J IN 1..a LOOP

13   DBMS_OUTPUT.PUT_LINE(tab_type(j)) ;

14    END LOOP ;

15    END ;

16    /

-> SMITH

     ALLEN

     WARD

    JONES

....

비 PL/SQL 변수

PL/SQL 안에서만 사용되는 것이 아니라 외부에서도 사용될 수 있는 변수

SQL 환경에서도 쓸 수 있고 PL/SQL 에서도 사용 할 수 있음

- 바인드 변수

호스트 환경에서 생성되어 데이터를 저장하므로 호스트 변수라고도 함. VARIABLE 키워드를 사용하여 생성

사용 예 ) 교수번호가 1001 인 교수의 연봉을 계산하여 바인드 변수에 할당 한 후 출력

SCOTT>VARIABLE v_bind NUMBER ;   --SQL 에서 변수를 선언

SCOTT>BEGIN

2    SELECT (pay*12)+NVL(bonus,0) INTO :v_bind

3    FROM professor

4    WHERE profno=1001 ;

5    END ;

6    /

SCOTT>PRINT v_bind ;  --SQL에서 바인드 변수에 담긴 값을 출력

V_BIND

------

  6700

PRINT 문장으로 매번 확인이 번거로울 경우 SET AUTOPRINT ON ;  명령어를 사용하면 자동으로 바인드 변수값을 출력

PL/SQL 과 SQL 에서 사용되는 주요 데이터 타입 정리