22.plsql 변수

/*
<PL/SQL>
    오라클 자체에 내장되어 있는 절차적 언어로 SQL 문장 내에서 변수의 정의, 조건 처리(IF), 반복 처리(LOOP, FOR, WHILE) 등을 지원한다.
        (다수의 SQL 문을 순서대로 실행 할 수 있다)
        
        [PL/SQL의 구조]
            1) 선언부(DECLAER SECTION)
                DECLARE로 시작, 변수나 상수를 선언 및 초기화하는 부분이다.
                생략가능
            2) 실행부(EXECUTABLE SECTION) 
                BEGIN로 시작, SQL 문, 제어문(조건, 반복문) 등의 로직을 기술하는 부분이다.
            3) 예외 처리부(EXCEPTION SECTION) 
                EXCEPTION로 시작, 예외 발생 시 해결하기 위한 구문을 기술하는 부분이다.
                생략가능

DECLARE
    선언부
    - 변수선언
    - 각 문장의 끝은 세미콜론;
    - 생략가능
BEGIN
    실행부
    - 로직처리
    - 조건문, 반복문
    - DML문만 사용 가능
EXCEPTION
    예외처리
    - 오류발생시 실행
    - 생략가능
*/

-- 환경설정을 해주지 않으면 출력되지 않는다
-- 프로시저를 사용하여 출력하는 내용을 화면보여주도록 설정하는 환경변수
SET SERVEROUTPUT ON;
-- 출력기능 OFF
SET SERVEROUTPUT OFF;

-- 실행부
-- 선언부, 예외처리부 생략 가능
-- 블록 명칭이 생략 된 경우 익명블록이라고 함
BEGIN
DBMS_OUTPUT.PUT_LINE('안녕하세요!');
END;
/ 
/*
한페이지에 여러개의 PL/SQL문장이 있는경우 /를 이용해서 구분
하나의 PL/SQL문장의 끝을 나타내줌
*/

/*
1) 변수선언 -> 변수명 데이터타입 := 초기값;
    - 초기값을 할당하지 않는 경우 null값 할당
2) 상수선언 -> 상수명 CONSTANT 데이터타입 := 상수값;
    - 한 번 값을 할당하면 변경 불가
*/
-- 선언부
DECLARE
    -- 변수명 변수타입
    vi_num NUMBER;
-- 실행부
BEGIN
    -- vi_num :=100;
    DBMS_OUTPUT.PUT_LINE('vi_num : '||vi_num);
END;
/

DECLARE
    -- CONSTANT : 상수는 선언과 동시에 초기화
    pi CONSTANT NUMBER := 3.14;
BEGIN
    -- 상수는 값 변경 불가
    -- pi := 10;
    DBMS_OUTPUT.PUT_LINE('pi : '||pi);
END;
/
/*
3) 변수 데이터 타입
    (1) SQL 타입(NUMBER, CHAR, VARCHAR2 DATE 등)
     - 변수의 타입을 SQL타입을 이용하여 지정
       -> 변수명 타입;
    (2) PL/SQL타입
     - 변수의 타입을 테이블의 컬럼의 데이터 타입을 참조하여 지정
       -> 변수명 테이블명.컬럼명%TYPE;
     - 변수의 타입을 테이블의 모든 컬럼을 참조하여 지정
       -> 변수명 테이블명%ROWTYPE;
4) DML 문 
    (1) SELECT INTO : 테이블에서 특정 값을 선택해 변수에 할당할 경우 사용
    (2) &를 넣으면 값을 입력 받는 창이 나옴 ex) WHERE emp_id = '&emp_id';
*/

/* EX)
    변수선언 : radius, pi(상수)
*/
DECLARE
    -- 선언과 동시에 초기화 가능
    radius NUMBER := 5;
    -- 선언과 동시에 초기화 하지 않으면 오류 발생
    pi CONSTANT NUMBER := 3.14;
BEGIN
    -- 변수이므로 중간에 값 변경 가능
    radius := 10;
    DBMS_OUTPUT.PUT_LINE('원의 둘레 : '||radius*pi*2);
END;
/
/*
문제1)
 eid, ename을 변수로 선언하고 초기화하여 아래와 같이 출력
 eid : 999
 ename : 나애리
*/
DECLARE
    eid NUMBER := 999;
    ename VARCHAR2(10) := '나애리';
BEGIN

    DBMS_OUTPUT.PUT_LINE('eid : '||eid);
    DBMS_OUTPUT.PUT_LINE('ename : '||ename);

END;
/

RENAME employee TO emp;
RENAME department TO edpt;

-- SQL문을 이용해서 조회된 결과를 변수에 할당
SELECT emp_id, emp_name
FROM emp
WHERE emp_id = 200;

DECLARE
    eid NUMBER;
    ename VARCHAR2(10);
BEGIN
    SELECT emp_id, emp_name
    INTO eid, ename -- 제시된 컬럼과 변수의 개수와 타입이 일치해야한다.
    FROM emp
    -- 다수의 행이 반환 된 경우 오류 발생
    WHERE emp_id = &사번; -- &사번 : 사용자로부터 입력받은 값으로 대체
    
    DBMS_OUTPUT.PUT_LINE('eid : '||eid);
    DBMS_OUTPUT.PUT_LINE('ename : '||ename);
END;
/

/*
    1. 파라메터로 입력받는 방법
        사번에 대한 데이터값 입력 창이 뜨고, 사용자로부터 입력 받은 값으로 변경해준다.
        WHERE emp_id = &사번;
    2. SELECT문의 조회결과를 변수에 담는 방법
        SELECT emp_id, emp_name
        INTO eid, ename  <- 조회되는 컬럼의 개수와 타입이 일치해야함
*/

DECLARE
    eid EMP.EMP_ID%TYPE;
    ename EMP.EMP_NAME%TYPE;
BEGIN
    SELECT emp_id, emp_name
    INTO eid, ename
    FROM emp
    WHERE emp_id = &사번; 
    
    DBMS_OUTPUT.PUT_LINE('eid : '||eid);
    DBMS_OUTPUT.PUT_LINE('ename : '||ename);
END;
/

/*
< PL/SQL 선언부(DECLAER SECTION) >
        변수 및 상수를 선언해 놓는 공간
        선언과 동시에 초기화도 가능
    
    < 변수 및 상수의 타입 >
        1) 일반 타입 변수 
            - SQL 타입 (NUMBER, CHAR, VARCHAR2, DATE 등)
        2) 레퍼런스 타입 변수 
            - PL/SQL 타입 (테이블의 컬럼타입을 참조)
        3) ROW 타입 변수
            - PL/SQL 타입 (하나의 테이블의 모든 컴럼의 값을 한꺼번에 저장할 수 있는 변수)
        
    1) 일반 타입 변수의 선언 및 초기화
            [표현법]
                변수명 [CONSTANT] 자료형(크기) [:= 값];
      자료형 : NUMBER, CHAR, VARCHAR2, DATE 등 SQL타입
    
     2) 레퍼런스 타입 변수 선언 및 초기화
            [표현법]
                변수명 테이블명.컬럼명%TYPE;
            
            - 변수의 타입을 지정하는데
                테이블의 컬럼의 데이터 타입을 참조 하여 지정
*/

-- 문제) 사원명을 입력받아서 사원의 사번, 사원명, 급여정보를 각각 eid, ename, sal 변수에 대입 후 출력
-- job_name 출력
DECLARE
    eid EMP.EMP_ID%TYPE;
    ename EMP.EMP_NAME%TYPE;
    sal VARCHAR2(15);
    job_name JOB.JOB_NAME%TYPE;
BEGIN
    SELECT emp_id, emp_name, TO_CHAR(salary,'999,999,999'), job_name
    INTO eid, ename, sal, job_name
    FROM emp
    JOIN job USING(job_code)
    WHERE emp_name='선동일'; -- 문자타입인 경우 '(홑따옴표)'로 감싸줌
    
    DBMS_OUTPUT.PUT_LINE('eid : '||eid);
    DBMS_OUTPUT.PUT_LINE('ename : '||ename);
    DBMS_OUTPUT.PUT_LINE('salary : '||sal);
    DBMS_OUTPUT.PUT_LINE('salary : '||job_name);
END;
/

/*
     3) ROW 타입 변수 선언 및 초기화
            [표현법]
                변수명 테이블명%ROWTYPE;
                
            - 하나의 테이블의 여러 컴럼의 값을 한꺼번에 저장할 수 있는 변수를 의미한다.
            - 모든 컬럼을 조회하는 경우에 사용하기 편리하다.
             
            * ERROR : 테이블이름과 같은 변수명은 오류를 발생
*/
DECLARE
    e EMP%ROWTYPE;
BEGIN
    SELECT * INTO e
    FROM emp
    WHERE emp_id=200;
    
    -- 변수명.컬럼명으로 접근 가능
    DBMS_OUTPUT.PUT_LINE('사번 : '||e.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('사원명 : '||e.EMP_NAME);
END;
/
-- 학생 백업 테이블 생성(테이블 구조만 복사)
CREATE TABLE tb_student_bk
AS SELECT * FROM tb_student WHERE 1=2;

/*
실습
    변수선언
        student_info : TB_STUDENT테이블의 모든 컬럼정보를 담고 있습니다
        
    1. 학번이 A213046인 학생의 정보를 조회하여 변수에 담기
    2. 학생의 정보를 TB_STUDENT_BK테이블에 입력

*/
SELECT *
FROM tb_student
WHERE student_no = 'A213046';

DECLARE
    student_info tb_student%ROWTYPE;
BEGIN
    SELECT * INTO student_info
    FROM tb_student
    WHERE student_no = 'A213046';
    
    INSERT INTO tb_student_bk VALUES student_info;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT||'건 처리 되었습니다.');
END;
/
SELECT * FROM tb_student_bk;