251106 TIL

SELECT last_name, salary, hire_date
FROM hr.employees
WHERE employee_id = 100;

BEGIN
    SELECT last_name, salary, hire_date
    FROM hr.employees
    WHERE employee_id = 100;
END;
/
--PLS-00428: an INTO clause is expected in this SELECT statement

PLSQL의 SQL문

• SELECT 문을 사용하여 데이터베이스에 있는 데이터를 조회
• DML문을 사용하여 데이터베이스에 있는 데이터를 입력, 수정, 삭제, 병합
• COMMIT, ROLLBACK, SAVEPOINT 문을 사용하여 트랜잭션을 제어

SQL CURSOR

• CURSOR : SQL 문 실행 메모리 영역(parse, bind, execute, fetch)
• implicit cursor(암시적 커서) : 오라클 서버가 SQL문(SELECT, DML) 처리하기 위해 내부적으로 커서를 생성하고 관리
• explicit cursor(명시적 커서) : 개발자가 명시적으로 커서를 선언하고 관리

implicit cursor(암시적 커서)

• 오라클 서버가 SQL문(SELECT, DML) 처리하기 위해 내부적으로 커서를 생성하고 관리

SELECT … INTO …

• INTO 절이 FETCH절
• 반드시 1개 행만 FETCH 해야 함
  • 0개 : NO_DATA_FOUND 오류 발생
  • 2개 이상 : TOO_MANY_ROWS 오류 발생

DECLARE
    v_name varchar2(30);
    v_sal number;
    v_date date;
BEGIN
    SELECT last_name, salary, hire_date
    INTO v_name, v_sal, v_date
    FROM hr.employees
    WHERE employee_id = 100;

    dbms_output.put_line(v_name || ' ' || v_sal || ' ' || v_date);
END;
/

• 1건이 아닌 데이터 fetch 시 오류 발생

-- ORA-01403: no data found
DECLARE
    v_name varchar2(30);
    v_sal number;
    v_date date;
BEGIN
    SELECT last_name, salary, hire_date
    INTO v_name, v_sal, v_date
    FROM hr.employees
    WHERE employee_id = 300;

    dbms_output.put_line(v_name || ' ' || v_sal || ' ' || v_date);
END;
/

-- ORA-01422: exact fetch returns more than requested number of rows
DECLARE
    v_name varchar2(30);
    v_sal number;
    v_date date;
BEGIN
    SELECT last_name, salary, hire_date
    INTO v_name, v_sal, v_date
    FROM hr.employees
    WHERE department_id = 20;

    dbms_output.put_line(v_name || ' ' || v_sal || ' ' || v_date);
END;
/

%type

• 테이블명.컬럼이름%TYPE : 테이블 컬럼의 데이터 타입과 사이즈를 사용하겠다는 의미
• 변수명%TYPE : 변수의 타입과 사이즈를 사용하겠다는 의미
• 컬럼의 타입을 몰라도 사용이 가능하고, 타입이 변경되더라도 변경 작업이 필요하지 않기 때문에 유지 관리에 용이

DECLARE
    v_lname employees.last_name%type;
    v_fname v_lname%type;
    v_sal employees.salary%type;
    v_date employees.hire_date%type;
BEGIN
    SELECT last_name, first_name, salary, hire_date
    INTO v_lname, v_fname, v_sal, v_date
    FROM hr.employees
    WHERE employee_id = 100;

    dbms_output.put_line(v_lname || ' ' || ' ' || v_fname || ' ' || v_sal || ' ' || v_date);
END;
/

문제

-- [문제10] 사원 번호를 입력값으로 받아서 last_name, salary, hire_date 정보를 출력하는 프로그램을 작성하세요.
/*
이름 : KING
급여 : ￦24,000.00
입사일 : 2003년 6월 17일
*/
DECLARE
    v_last_name employees.last_name%type;
    v_salary employees.salary%type;
    v_hire_date employees.hire_date%type;
BEGIN
    SELECT last_name, salary, hire_date
    INTO v_last_name, v_salary, v_hire_date
    FROM hr.employees
    WHERE employee_id = :b_employee_id;

    dbms_output.put_line('이름 : ' || v_last_name);
    dbms_output.put_line('급여 : ' || to_char(v_salary, 'FML999G999D99'));
    dbms_output.put_line('입사일 : ' || to_char(v_hire_date, 'yyyy"년" fmmm"월" dd"일"'));
    dbms_output.put_line('입사일 : ' || to_char(v_hire_date, 'DL'));
    dbms_output.put_line('입사일 : ' || to_char(v_hire_date, 'DS'));
END;
/

• FM : 공백이나 0을 제거해줌
• DL : 지역 설정(NLS_TERRITORY, NLS_LANGUAGE)에 따른 긴 날짜 형식 제공
• DS : 지역 설정(NLS_TERRITORY, NLS_LANGUAGE)에 따른 짧은 날짜 형식 제공

DML(INSERT, UPDATE, DELETE ,MERGE)

BEGIN
    INSERT INTO hr.test(id, name, day) VALUES(1, 'ORACLE', SYSDATE);
END;
/

COMMIT; -- or ROLLBACK;

• 프로그램이 끝나더라도 트랜잭션은 진행 중 → COMMIT or ROLLBACK을 프로그램 내 또는 외부에서 처리해줘야 함

BEGIN
    INSERT INTO hr.test(id, name, day) VALUES(:b_id, :b_name, to_date(:b_day, 'yyyy-mm-dd'));
END;
/

• 바인드 변수는 number 또는 character 타입만 받을 수 있으므로 날짜 형식의 경우 문자형으로 받은 후 바인드 변수에 to_char()를 적용해 날짜형으로 변환

암시적 커서 속성

sql%rowcount

• DML문에 의해 영향을 받은 행의 수를 리턴하는 속성, 정수값

-- 입력 행 수
BEGIN
    INSERT INTO hr.test(id, name, day)
    SELECT employee_id, last_name, hire_date
    FROM hr.employees;

    dbms_output.put_line(sql%rowcount || ' 행이 입력되었습니다.');
END;
/

COMMIT;

-- 수정 행 수
BEGIN
    UPDATE hr.test
    SET day = sysdate
    WHERE id in (100, 200);

    dbms_output.put_line(sql%rowcount || ' 행이 수정되었습니다.');

    ROLLBACK; -- 테스트 목적이므로 결과만 보고 롤백
END;
/

sql%found

• DML 문에 의해 영향을 받은 행이 있으면 TRUE, 없으면 FALSE로 평가하는 속성, BOOL

sql%notound

• DML 문에 의해 영향을 받은 행이 없으면 TRUE, 있으면 FALSE로 평가하는 속성, BOOL

BEGIN
    UPDATE hr.test
    SET day = sysdate
    WHERE id = 300;

    IF sql%found THEN
        dbms_output.put_line(sql%rowcount || ' 행이 수정되었습니다.');
    ELSE
        dbms_output.put_line('사원이 존재하지 않습니다');
    END IF;

    ROLLBACK;
END;
/

BEGIN
    DELETE FROM hr.test WHERE day < to_date('2005-01-01', 'yyyy-mm-dd');

    IF sql%found THEN
        dbms_output.put_line(sql%rowcount || ' 행이 삭제되었습니다.');
    ELSE
        dbms_output.put_line('사원이 존재하지 않습니다');
    END IF;

    ROLLBACK;
END;
/

문제

-- [문제11] 사원번호를 입력값으로 받아서 근속연수가 20년 이상이면 10% 인상급여로 수정하는 프로그램을 생성해주세요.
/*
100사원의 입사일은 2003-06-17 근속연수는 21년입니다.
100사원의 이전 급여는 24000, 수정된 급여는 26400
*/

DECLARE
    v_date varchar(30);
    v_year number;
    v_sal emp.salary%type;
    v_new_sal v_sal%type;
BEGIN
    SELECT to_char(hire_date, 'yyyy-mm-dd'), trunc(months_between(sysdate, hire_date) / 12), salary
    INTO v_date, v_year, v_sal
    FROM hr.emp
    WHERE employee_id = :b_id;

    dbms_output.put_line(:b_id || '사원의 입사일은 ' || v_date || ' 근속연수는 ' || v_year || '년입니다.');

    IF v_year >= 20 THEN
        UPDATE hr.emp
        SET salary = salary * 1.1
        WHERE employee_id = :b_id;

        SELECT salary
        INTO v_new_sal
        FROM hr.emp
        WHERE employee_id = :b_id;

        dbms_output.put_line(:b_id || '사원의 이전 급여는 ' || v_sal || ', 수정된 급여는 ' || v_new_sal || '입니다.');
    ELSE
        dbms_output.put_line(:b_id || '사원의 급여는 수정할 수 없습니다.');
    END IF;
    ROLLBACK;
END;
/

조합 데이터 유형

• 스칼라 유형(scalar data type) : 단일 값을 보유하는 변수
• 스칼라 유형과 달리 다중 값을 보유할 수 있는 변수
• 레코드(record) : 서로 다른 데이터 유형의 값을 저장
• 배열(array) : 동일한 데이터 유형의 값을 저장
  • index by table
  • nested table
  • varray

레코드(record)

-- record 사용 전
DECLARE
    v_dept_id departments.department_id%type;
    v_dept_name departments.department_name%type;
    v_dept_mgr departments.manager_id%type;
    v_dept_loc departments.location_id%type;
BEGIN
    SELECT department_id, department_name, manager_id, location_id
    INTO v_dept_id, v_dept_name, v_dept_mgr, v_dept_loc
    FROM hr.departments
    WHERE department_id = 10;

    dbms_output.put_line('부서번호 : ' || v_dept_id);
    dbms_output.put_line('부서이름 : ' || v_dept_name);
    dbms_output.put_line('부서장 : ' || v_dept_mgr);
    dbms_output.put_line('부서위치 : ' || v_dept_loc);
END;
/

-- record 사용 후
DECLARE
    /* 레코드 유형 선언, field 구성 */
    TYPE dept_record_type IS RECORD(
        dept_id departments.department_id%type,
        dept_name departments.department_name%type,
        dept_mgr departments.manager_id%type,
        dept_loc departments.location_id%type
    );

    v_rec dept_record_type;

BEGIN
    SELECT department_id, department_name, manager_id, location_id
    INTO v_rec
    FROM hr.departments
    WHERE department_id = 10;

    dbms_output.put_line('부서번호 : ' || v_rec.dept_id);
    dbms_output.put_line('부서이름 : ' || v_rec.dept_name);
    dbms_output.put_line('부서장 : ' || v_rec.dept_mgr);
    dbms_output.put_line('부서위치 : ' || v_rec.dept_loc);
END;
/

%rowtype

• 테이블 또는 뷰에서 컬럼 및 데이터 타입을 가지고 레코드 타입을 선언
• 레코드 타입을 손쉽게 사용 가능
• 컬럼의 개수, 타입을 알 필요 없음
• SELECT *
• 행 레벨 INSERT, UPDATE

DECLARE
    v_rec departments%rowtype;

BEGIN
    SELECT *
    INTO v_rec
    FROM hr.departments
    WHERE department_id = 10;

    dbms_output.put_line('부서번호 : ' || v_rec.department_id);
    dbms_output.put_line('부서이름 : ' || v_rec.department_name);
    dbms_output.put_line('부서장 : ' || v_rec.manager_id);
    dbms_output.put_line('부서위치 : ' || v_rec.location_id);
END;
/

중첩 레코드

DECLARE
    TYPE rec_type IS RECORD(
        sal number,
        minsal number default 1000,
        day date,
        rec employees%rowtype);
    v_rec rec_type;
BEGIN
    v_rec.sal := v_rec.minsal + 500;
    v_rec.day := sysdate;

    SELECT *
    INTO v_rec.rec
    FROM hr.employees
    WHERE employee_id = 100;

    dbms_output.put_line(v_rec.sal);
    dbms_output.put_line(v_rec.minsal);
    dbms_output.put_line(v_rec.day);
    dbms_output.put_line(v_rec.rec.last_name);
END;
/

행 레벨 INSERT

DECLARE
    v_rec employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = 100;

    INSERT INTO hr.retired_emp VALUES v_rec;
END;
/

행 레벨 UPDATE

DECLARE
    v_rec employees%rowtype;
BEGIN
    SELECT *
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = 100;

    v_rec.first_name := upper(v_rec.first_name);
    v_rec.last_name := upper(v_rec.last_name);
    v_rec.commission_pct:= 0.1;
    v_rec.hire_date := sysdate;
    v_rec.department_id := 10;

    UPDATE hr.retired_emp
    SET row = v_rec
    WHERE employee_id = 100;
END;
/

• row 키워드 사용