PLT 6.2 PL/SQL에서의 변수 사용
SQL과 절차적인 문장 안에서 PL/SQL로써 변수를 선언할 수 있고 그것을 사용할 수 있다. 변수는 자료를 일시적으로 저장하고 변경하고 검증하기 위해 하나 또는 그 이상의 변수를 선언하여 사용한다. 또한 변수는 데이터베이스를 액세스하지 않고 계산이나 다른 데이터 조작에 사용할 수 있다. 이러한 변수는 일단 선언되면 다른 선언적 문장을 포함한 다른 문장에서 간단하게 그것을 반복적으로 참조하여 사용할 수 있다.
%TYPE와 %ROWTYPE을 사용하여 변수를 선언하면 테이블의 구조가 변경(데이터형과 길이)되어도 Application에서는 실행 시간에 테이블을 참조하여 변수가 정의되므로 데이터의 독립성, 유지비용 절감을 제공하고, 새로운 업무 요구에 충족시키기 위해 데이터베이스 변경에 따라 프로그램의 적응, 수정되는 것을 허용한다. PS/SQL에서 변수 처리는 다음과 같이 이루어집니다.
1. 선언 섹션 내에서 변수를 선언하고 초기화하여 사용합니다.
2. 실행 섹션에서 변수에 대한 새 값을 할당 합니다.
3. 매개변수를 통해 PL/SQL 블록으로 값을 전달합니다.
4. 출력 변수를 통해 결과를 봅니다.
변수 유형
모든 PL/SQL 변수는 저장 포맷, 제약 조건, 값의 유효 범위를 지정하는 데이터형을 가지고 있다. PL/SQL은 변수, 상수, 포인터를 선언하기 위해 사용할 수 있는 4가지 데이터형(Scalar, Composite, Reference, LOB(large objects)을 지원합니다.
¨ Scalar : 주로 단일 값을 보유합니다. 주요 데이터형은 ORACLE SERVER 테이블의 열 유형에 대응하는 것들입니다.
¨ Composite : 레코드 같은 조합 데이터형은 PL/SQL 블록에서 조작되고 정의되는 필드 그룹을 허용합니다.
¨ Reference : 참조 데이터형은 pointer라 불리며 다른 프로그램 항목을 지시하는 값을 보유합니다.
¨ LOB(large objects) : LOB 데이터형은 locator라 불리며 라인 밖에서 지정된 큰 객체의 위치를 지정하는 값을 보유합니다.
변수 선언
PL/SQL 블록에서 그것을 참조하기 전에 선언 섹션에서 모두 PL/SQL 식별자를 선언할 필요가 있습니다. 초기값을 할당하기 위해 옵션을 가집니다. 변수를 선언하기 위해 변수에 대한 값을 할당할 필요는 없습니다. 선언에서 다름 변수를 참조한다면 이전 문장에서 개별적으로 그것들을 반드시 선언해 놓아야 합니다.
|
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expression];
identifier 변수의 이름
CONSTANT 변수의 값을 변경할 수 없도록 제약합니다.
datatype Scalar, Composite, Reference, LOB(large objects)
NOT NULL 값을 포함해야만 하도록 하기 위해 변수를 제약 합니다.
Expression Literal, 다른 변수, 연산자나 함수를 포함하는 표현식 |
n 이름 지정 규약을 사용할 수 있습니다.(예:v_name는 변수를 나타내고 c_name는 상수를 나타낸다)
n NOT NULL로 지정된 변수를 초기화 합니다.
n 지정 연산자(:=)를 사용하거나 예약어 DEFAULT를 사용하여 식별자를 초기화 합니다.
n 한 라인에 하나의 식별자만 선언 합니다.
n 상수 선언에서 CONSTRAINT는 형 지정자 보다 먼저 기술되어야 한다.
|
DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_loc VARCHAR2(13) := ‘
v_com CONSTANT NUMBER := 1400; |
이름 지정 규칙
n 하나의 블록에서 동일 이름의 변수를 선언할 수 없습니다.
n 블록이 다르면 동일 이름을 선언할 수 있습니다. 객체들이 동시에 존재하는 곳에서는 현재 블록에서 정의된 객체들만이 사용될 수 있습니다.
n 변수에 대한 이름을 블록에서 사용되는 테이블 열의 이름과 동일하게 선택해서는 안됩니다. PL/SQL 변수가 SQL 명령에서 사용되고 열과 동일 이름을 가지면 ORACLE SERVER은 참조되는 열로 간주 합니다.
변수의 값 지정
변수의 값을 지정하거나 재지정하기 위해 PL/SQL 지정 문자를 사용합니다. 지정 연산자(:=)의 좌측에 새 값을 받기 위한 변수를 적습니다.
|
identifier := expression; |
스칼라 데이터 형
|
데이터 형 |
설 명 |
|
VARCHAR2(n) |
변수 길이 문자 데이터에 데한 기본형은 32767Byte까지 입니다. VARCHAR2변수와 상수에 대한 디폴트 크기는 없습니다. |
|
NUMBER(p,s) |
고정(fixed)과 유동(floating)포인트 숫자에 대한 기본형 |
|
DATE |
날짜와 시간에 대한 기본형. DATE값은 지정 이후의 초 단위로 날에 대한 시간을 포함합니다. 날짜의 범위는 BC |
|
CHAR(n) |
고정 길이 문자에 대한 기본형은 32767바이트까지 입니다. 지정하지 않는다면 디폴트 길이는 1로 설정됩니다. |
|
LONG |
고정 길이 문자에 대한 기본형은 32760바이트까지 입니다.
LONG 데이터베이스 열의 최대 폭은 2147483647바이트입니다. |
|
LONG RAW |
이진 데이터와 바이트 문자열에 대한 기본형은 32760Byte까지 입니다. LONG RAW 데이터는 PL/SQL에 의해 해석되지 않습니다. |
|
BOOLEAN |
계산에 사용되는 3가지 가능한 값 가운데 기본형(TRUE,FALSE,NULL) |
|
BINARY_INTEGER |
-2147483647~2147483647사이의 정수에 대한 기본 형 |
|
PLS_INTEGER |
-2147483647~2147483647사이의 signed정수에 대한 기본형으로
PLS_INTEGER 값은 NUMBER와 BINARY_INTEGER값보다 적은 기억장치를 필요로 합니다. |
LONG 데이터 형은 LONG값의 최대 길이가 32767 바이트인 것을 제외하고는 VARCHAR2와 유사합니다. 그러므로 32760바이트보다 더 긴 값은 LONG 데이터베이스 열에서 LONG PL/SQL 변수로 사용할 수 없습니다.
%TYPE 속성
변수의 데이터 형과 정밀도를 직접 코딩하기 보다는 이전에 선언된 다른 변수 또는 데이터베이스 열에 맞추어 변수를 선언하기 위해 %TYPE속성을 사용할 수 있습니다. 변수에 저장되는 값이 데이터베이스의 테이블에서 오거나 변수가 테이블에 쓰여지기로 되었다면 %TYPE 속성은 자주 사용됩니다. 변수 선언에서 필요한 데이터형 대신에 속성을 사용하려면 데이터베이스 테이블과 열 이름을 접두어로 사용합니다. 또한 이전에 선언된 변수를 참조한다면 속성 앞에 변수명을 기술합니다. 데이터베이스 수준에서 테이블의 데이터형을 변경하여도 PL/SQL을 고칠 필요가 없습니다.
|
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_deptno emp.deptno%TYPE := 10; |
BOOLEAN 변수 선언
n TRUE, FALSE, NULL값만을 BOOLEAN 변수에 대해 지정할 수 있습니다.
n 변수는 논리연산자 AND, OR, NOT에 의해 접속 됩니다.
n 변수는 항상 TRUE, FALSE, NULL을 생성 합니다.
n 산술, 문자, 날짜 표현식은 BOOLEAN값을 리턴하기 위해 사용될 수 있습니다.
|
DECLARE
v_sal1 NUMBER(5) := 5000;
v_sal2 NUMBER(5) := 6000;
v_flag BOOLEAN := TRUE;
BEGIN
v_flag := (v_sal1 > v_sal2);
IF v_flag THEN
. . . . . . . . . . |
조합 데이터 형(Composite Datatype)
Composite Datatype은 내부 구성 요소를 갖고 있고 PL/SQL에서 사용할 수 있는 Composite Datatype은 RECORD, TABLE, 중첩 TABLE, VARRAY입니다. RECORD 데이터형은 관련은 있으나 서로 다른 데이터형들을 논리적인 하나의 단위로 묶기 위해 사용하고 TABLE 데이터형은 전체 객체로써 데이터형이 같은 데이터의 모음을 참조하고 조작하기 위해 사용한다. 한번 정의되면 테이블과 레코드는 재이용할 수 있다.
TABLE TYPE
테이블형의 객체는 PL/SQL 테이블이라 불립니다. PL/SQL 테이블은 행에 대해 배열처럼 액세스하기 위해 기본키를 사용합니다. 배열과 유사하고 PL/SQL 테이블을 액세스하기 위해 BINARY_INTEGER데이터형의 기본키와 PL/SQL 테이블 요소를 저장하는 스칼라 또는 레코드 데이터형의 열을 포함하여야 한다. 또한 이들은 동적으로 자유롭게 증가할 수 있습니다.
|
TYPE table_type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE} [NOT NULL]
[INDEX BY BINARY_INTEGER];
identifier table_type_name;
table_type_name 테이블형의 이름
column_type VARCHAR2,DATE,NUMBER과 같은 스칼라 데이터 형
identifier 전체 PL/SQL 테이블을 나타내는 식별자의 이름 |
|
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE job_table_type IS TABLE OF emp.job%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
job_table job_table_type;
i BINARY_INTEGER := 0;
BEGIN
FOR k IN (SELECT ename,job FROM emp) LOOP
i := i + 1;
ename_table(i) := k.ename;
job_table(i) := k.job;
END
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(ename_table(j),12) ||
RPAD(job_table(j),9));
END
END; |
RECORD TYPE
PL/SQL RECORD TYPE은 데이터베이스의 테이블 ROW와 다르고 3GL에서의 RECORD나 STRUCTURE와 유사하다. PL/SQL RECORD는 Scalar, PL/SQL RECORD, PL/SQL TABLE 데이터 타입 중 하나 이상의 요소를 갖고 있어야 하며, 다른 데이터 타입을 가질 수도 있다. 또한 FIELD(ITEM)들의 집합을 하나의 논리적 단위로 처리할 수 있게 해 주므로 테이블의 ROW를 읽어올 때 편리하다.
|
TYPE type_name IS RECORD
(field_name1 {scalar_datatype|record_type} [NOT NULL] [{:= | DEFAULT} expr],
(field_name2 {scalar_datatype|record_type} [NOT NULL] [{:= | DEFAULT} expr],
. . . . . . .);
identifiee_name type_name;
type_name RECODE 형의 이름, 이 식별자는 RECODE를 선언하기 위해 사용한다.
field_name RECODE내의 필드명 |
|
ACCEPT p_ename PROMPT '조회하고자 하는 사원의 이름을 입력하시오 : '
DECLARE
TYPE emp_record_type IS RECORD(
v_empno emp.empno%TYPE,
v_ename emp.ename%TYPE,
v_job emp.job%TYPE,
v_mgr emp.mgr%TYPE,
v_hiredate emp.hiredate%TYPE,
v_sal emp.sal%TYPE,
v_comm emp.comm%TYPE,
v_deptno emp.deptno%TYPE,
v_g_flag emp.g_flag%TYPE);
emp_record emp_record_type;
v_ename emp.ename%TYPE := '&p_ename';
BEGIN
SELECT *
INTO emp_record
FROM emp
WHERE ename = UPPER(v_ename);
DBMS_OUTPUT.PUT_LINE('사원번호 : ' || TO_CHAR(emp_record.v_empno));
DBMS_OUTPUT.PUT_LINE('이 름 : ' || emp_record.v_ename);
DBMS_OUTPUT.PUT_LINE('담당업무 : ' || emp_record.v_job);
DBMS_OUTPUT.PUT_LINE('메 니 저 : ' || TO_CHAR(emp_record.v_mgr));
DBMS_OUTPUT.PUT_LINE('입사일자 : ' || TO_CHAR(emp_record.v_hiredate));
DBMS_OUTPUT.PUT_LINE('급 여 : ' ||
LTRIM(TO_CHAR(emp_record.v_sal,'$999,990.00')));
DBMS_OUTPUT.PUT_LINE('보 너 스 : ' ||
LTRIM(TO_CHAR(NVL(emp_record.v_comm,0),'$999,990')));
DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(emp_record.v_deptno));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '의 자료는 없습니다.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '자료가 2건 이상입니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러 입니다.');
END;
/ |
n 각 RECORD는 필요한 만큼 많은 필드를 가질 수 있다.
n RECORD는 초기값을 지정할 수 있고 NOT NULL로 정의될 수 있다.
n 초기값이 없는 필드는 NULL로 초기화 됩니다.
n DEFAULT 키워드는 필드를 정의할 때 사용될 수 있습니다.
n 임의의 블록 서브 프로그램,패키지의 선언 부분에 RECORD형을 정의하고 사용자 정의 RECORD를 선언할 수 있다.
n 중첩 RECORD를 선언하고 사용할 수 있다. RECORD는 다른 RECORD의 구성 요소가 될 수 있다.
%ROWTYPE의 속성
데이터베이스의 테이블 또는 VIEW의 일련의 열을 RECORD로 선언하기 위하여 %ROWTYPE를 사용합니다. 데이터베이스 테이블 이름을 %ROWTYPE 앞에 접두어를 붙여 RECORD를 선언하고 FIELD는 테이블이나 VIEW의 COLUMN명과 데이터 타입과 LENGTH을 그대로 가져올 수 있다.
|
Identifier reference%ROWTYPE;
identifier RECORD에 대해 지정된 이름
reference RECORD의 기초가 되는 테이블,VIEW,CURSOR,변수 명을 기술 |
n 알지 못하는 데이터베이스 COLUMN의 개수와 데이터 형식을 모르게 지정할 수 있다.
n 실행 시 변경되는 데이터베이스 COLUMN의 개수와 데이터 형식을 지정할 수 있다.
n SELECT 문장으로 행을 검색할 때 유리하다.
|
ACCEPT p_ename PROMPT '조회하고자 하는 사원의 이름을 입력하시오 : '
DECLARE
emp_record emp%ROWTYPE;
v_ename emp.ename%TYPE := '&p_ename';
BEGIN
SELECT *
INTO emp_record
FROM emp
WHERE ename = UPPER(v_ename);
DBMS_OUTPUT.PUT_LINE('사원번호 : ' || TO_CHAR(emp_record.empno));
DBMS_OUTPUT.PUT_LINE('이 름 : ' || emp_record.ename);
DBMS_OUTPUT.PUT_LINE('담당업무 : ' || emp_record.job);
DBMS_OUTPUT.PUT_LINE('메 니 저 : ' || TO_CHAR(emp_record.mgr));
DBMS_OUTPUT.PUT_LINE('입사일자 : ' || TO_CHAR(emp_record.hiredate));
DBMS_OUTPUT.PUT_LINE('급 여 : ' ||
LTRIM(TO_CHAR(emp_record.sal,'$999,990.00')));
DBMS_OUTPUT.PUT_LINE('보 너 스 : ' ||
LTRIM(TO_CHAR(NVL(emp_record.comm,0),'$999,990')));
DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(emp_record.deptno));
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '의 자료는 없습니다.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('&p_ename' || '자료가 2건 이상입니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러 입니다.');
END;
/ |
LOB Datatype 변수
LOB Datatype 변수는 Oracle 데이터형으로 구조화 되지 않은 데이터(턱스트,그래픽,이미지,비디오 클립,소리 웨이브 폼 같은) 블룩을 4기가 바이트 크기까지 저장할 수 있습니다. LOB Datatype은 데이터에 대한 랜덤 액세스를 지원 합니다.
n CLOB(character large object) 데이터형은 데이터베이스 내의 단일 바이트 문자 데이터의 대형 블록을 저장하기 위해 사용한다.
n BLOB(binary large object) 데이터형은 행의 안팎에 데이터베이스 내의 대형 이진 객체를 저장하기 위해 사용됩니다.
n BFILE(binary file) 데이터형은 데이터베이스 외부의 운영 시스템 파일의 대형 이진 객체를 저장하기 위해 사용됩니다.
n NCLOB(national language character large object) 데이터형은 데이터베이스 내의 단일 바니트,또는 고정 길이의 멀티바이트NCHAR데이터를 행의 안팎에 저장하기 위해 사용됩니다.
바인드 변수
바인드 변수는 호스트 환경에서 선언된 변수이며, 실행 시간 값을, 그것이 숫자이든지 또는 문자이든지 임의의 다른 변수처럼 그것을 사용할 수 있는 하나 이상의 PL/SQL프로그램의 내부나 외부에서 전달하기 위해 사용합니다. 문장이 프로시저, 함수, 패키지 안에 있지 않는다면, 호출 환경 또는 호스트에서 선언된 변수를 PL/SQL문장에서 참조할 수 있습니다. 이것은 선행 컴파일러 프로그램에서 선언된 호스트 언어 변수, Develover/2000Forms어플리케이션의 화면 필드, SQL*Plus 바인드 변수를 포함합니다. SQL*Plus환경에서 바인드 변수의 현재 값을 출력하기 위해 PRINT명령을 사용합니다.
|
SQL> VARIABLE return_code NUMBER
SQL> DECLARE
2 BEGIN
3 :return_code := 100;
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> PRINT return_code
RETURN_CODE
-----------
100 |
Non-PL/SQL 변수 참조
호스트 변수를 참조하기 위해 선언된 PL/SQL변수와 호스트 변수를 구별하기 위해 콜론(:)으로 참조 접두어를 기술하여야 한다.
|
:host_var1 := v_sal;
:global_val1 := ‘YES’;
|
'DataBase > Oracle' 카테고리의 다른 글
| Chapter 6 PL SQL(Procedural Language/SQL) - 4 (0) | 2008.04.17 |
|---|---|
| Chapter 6 PL SQL(Procedural Language/SQL) - 3 (0) | 2008.04.17 |
| Chapter 6 PL SQL(Procedural Language/SQL) - 1 (0) | 2008.04.17 |
| PLT 5.8 SUBQUERY (0) | 2008.04.17 |
| PLT 5.7 Join (0) | 2008.04.17 |