변수

변수 선언

• DECLARE(선언부)에서 변수 선언하기
• 변수명 다음에 데이터 타입 기술

문법

identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expression]

• identifier: 변수명(식별자)
• CONSTNAT: 상수로 지정, 초기값 반드시 할당 필요
• datatype:자료형
• expression: Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

예시

1. 변수 타입 명시적 작성

   -- 변수 1개 선언
   DECLARE NAME VARCHAR2(10);
   DECLARE NAME VARCHAR2(10) := 'JM';
   DECLARE NAME VARCHAR2(10) DEFAULT 'JM';
   
   -- 변수 여러 개 선언
   DECLARE
   	NAME VARCHAR2(20);
   	AGE NUMBER(3);
   	GENDER VARCHAR2(50) DEFAULT '여';

   -- 변수 선언 후 사용
   DECLARE NAME VARCHAR2(10) := 'JM';
   BEGIN
   	DBMS_OUTPUT.PUT_LINE('JM' || NAME); -- 출력
   END;

2. 테이블/뷰의 컬럼 속성(데이터 타입)을 받아 오는 방식

   1. %ROWTYPE
      • 테이블/뷰의 컬럼 속성을 그대로 받아오는 방식
      • 사용법: 변수명 테이블명%ROWTYPE

      DECLARE
      	DATA EMPLOYEE%ROWTYPE;
      BEGIN
      	SELECT * INTO DATA
      	FROM EMPLOYEE
      	WHERE EMPLOYEE_NO = 1234;
      	DBMS_OUTPUT.PUT_LINE(DATA.EMPLOYEE_NAME || ',' || DATA.DEPT_NO);
      END;

   2. %TYPE
      • 테이블의 컬럼 속성을 지정하여 받아오는 방식
      • 사용법: 변수명 테이블명.컬럼명%TYPE

      DECLARE
      	V_EMPLOYEE_NAME EMPLOYEE.EMPLOYEE_NAME%TYPE;
      	V_DEPT_NO EMPLOYEE.DEPT_NO%TYPE;
      BEGIN
      	SELECT EMPLOYEE_NAME, DEPT_NO INTO V_EMPLOYEE_NAME, V_DEPT_NO
      	FROM EMPLOYEE
      	WHERE EMPLOYEE_NO = 1234;
      	DBMS_OUTPUT.PUT_LINE(V_EMPLOYEE_NAME || ',' || V_DEPT_NO);
      END;

변수 대입

명시적 방식

• := 사용
  - identifier := expression

SELECT문 이용

- `INTO``절 필요
	- 조회 결과 값을 저장할 변수 기술
- 1개의 행만 저장 가능
- SELECT문의 컬럼들은 INTO절의 변수와 1:1로 대응해야한다.
	- 개수, 데이터 타입, 길이

SELECT select_list
INTO {variable_name1[, variable_name2, ..] | record_name}
FROM table_name
WHERE condition;

바인드 변수

• PL/SQL 외부에서도 사용할 수 있는 변수
• Host 환경에서 생성되어 저장된다.
  - Host 환경에서 PL/SQL에게 사용자 값을 전달하거나 전달받기 위해 선언된 변수
• = 비 PL/SQL 변수 = 호스트 변수
• 바인드 변수를 참조하기 위해 변수명의 접두어로 콜론 :을 붙인다.

선언

1. VAR[IABLE] 사용
2. Toad, SQL Developer 등의 툴이 선언
3. DECLARE 내부 선언
4. 프로그램 파라미터에서 선언

문법

VAR[IABLE] 변수명 [NUMBER | CHAR(n) | VARCHAR2(2)]

예시

VARIABLE V_SALARY NUMBER; -- 선언
EXEC :V_SALARY := 10000;  -- 할당
SELECT :V_SALARY AS COL1 FROM EMPLOYEE; -- COL1열에 10000이 입력된 결과가 출력된다.

치환 변수

• 입력 프롬프트에서 블록 내부로 직접 값을 전달하는 변수
• 블록 내부에서 치환 변수를 사용하기 위해 접두어로 앰퍼샌드 &를 붙인다.

문법

SET VERIFY OFF; -- 치환되기 전/후의 값을 보일 것인지 여부, DEFUALT=ON
ACCEPT 변수명 PROMPT '값을 등록하세요: ';

예시

V_NAME VARCHAR2(20) := &P_NAME;

SQL

특징

• PL/SQL의 SQL은 DML과 트랜잭션 제어 명령을 지원한다.
  - COMMIT, ROLLBACK 명령어로 트랜잭션 제어
• 암시적인 커서 속성으로 DML 결과를 결정한다.
  - SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN
• DDL(CREATE, ALTER, DROP), DCL(GRANT, REVOKE)은 지원하지 않는다.
• 세미콜론 ;으로 개별 SQL문 종료

DML

SELECT

• DB에서 한 행의 데이터를 추출

• 값은 단일 집합만 리턴할 수 있다.
  - 0개 혹은 2개 이상의 행이 리턴되면 에러 발생
  - 표준 예외: NO_DATA_FOUND, TOO_MANY_ROWS
  - 예외처리부에서 위 에러 처리 가능

• INTO절을 사용(필수)해서 변수에 값을 할당한다.

• SELECT절의 컬럼과 INTO절의 출력변수는 동일해야 한다.
  - 개수, 위치, 데이터타입

• 예시

  1. 일반 SELECT문

     SELECT select_list
     INTO {variable_name[, variable_name, ..] record_name}
     FROM table
     WHERE condition;

  2. TOO_MANY_ROWS 발생

     DECLARE
        V_SUM_SALARY EMPLOYEE.SALARY%TYPE;
        V_DEPT_NO EMPLOYEE.DEPT_NO%TYPE;
     BEGIN
        SELECT DEPT_NO, SUM(SALARY)
        INTO V_DEPT_NO, V_SUM_SALARY
        FROM EMPLOYEE
        GROUP BY DEPT_NO;
        :SALARY := V_SUM_SALARY;
        :DEPT := V_DEPT_NO;
     END;
     /

     • SUM 같은 그룹 함수는 PL/SQL 구문에서는 사용 못 한다. SQL에서만 가능
     • %TYPE, &ROWTYPE으로 동적으로 데이터타입 지정

INSERT

DECLARE
	V_EMPLOYEE_NO EMPLOYEE.EMPLOYEE_NO%TYPE;
BEGIN
	SELECT EMPLOYEE_NO_SEQUENCE.NETVAL
	INITO V_EMPLOYEE_NO
	FROM DUAL;
	
	INSERT INTO EMPLOYEE(EMPLOYEE_NO, NAME, JOB, DEPT_NO)
	VALUES(V_EMPLOYEE_NO, 'JM', 'ENGINEER', 5);
END;
/

UPDATE

DECLARE
	V_SALARY_INCREASE EMPLOYEE.SALARY%TYPE := 2000;
BEGIN
	UPDATE EMPLOYEE
	SET SALARY = SALARY + V_SALARY_INCREASE
	WHERE JOB = 'ENGINEER';
END;
/

DELETE

DECLARE
	V_DEPT_NO EMPLOYEE.DEMPT_NO%TYPE := 10;
BEGIN
	DELETE FROM EMPLOYEE
	WHERE DEPT_NO = V_DEPT_NO;
END;
/

---

참조

• [Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)
• PL/SQL이란?
• (SQL) 바인드변수란 무엇일까?
• ORACLE 바인딩 변수, 치환 기능