251104 TIL

절차적 언어(Procedure Language)

• 함수나 모듈을 만들어서 문제에 대한 해결을 순서에 맞게 호출하여 수행하는 방식

PL/SQL(Procedure Language Structured Query Language)

• SQL을 확장한 절차적 언어(Procedure Language)
• 프로시저(procedure) 생성자 제공
  • 변수, 상수, 데이터 유형(레코드, 배열)
  • 조건문, 반복문
  • 한 번 작성하여 여러 번 실행할 수 있는 재사용 가능한 프로그램 단위

PL/SQL 이점

모듈식 프로그램 개발

• 블록 내의 관련 명령문을 논리적으로 그룹화 할 수 있다.
• 블록을 더 큰 블록 내부에 중첩하여 강력한 프로그램을 작성할 수 있다.
• 코드를 쉽게 유지 관리 및 디버그 할 수 있다.

오라클 도구와 통합

• ERP, HR, CRM, SCM, …

이식성

• 운영체제나 플랫폼에 상관없이 오라클 서버가 실행되는 모든 환경에서 실행 가능

예외 처리

• 오류가 발생했을 경우 처리할 수 있음

익명(Anonymous) 블록 구조

• 객체 프로그램이 아님(데이터베이스에 저장되지 않음)

DECLARE(선택)
        선언부분(변수, 상수, 명시적 커서, 사용자 정의 예외사항)
BEGIN(필수)
        실행부분(SQL, 로직구현)
EXCEPTION(선택)
        예외사항(실행부분에서 발생한 오류에 대한 처리)
END;(필수)
/

출력 기능 프로그램 : dbms_output.put_line

BEGIN
    dbms_output.put_line('오늘 하루도 열심히 공부하자!!');
END;
/

SQL Developer 출력 설정

• 보기 > DBMS 출력

• + 버튼 클릭 → 접속 선택

• PL/SQL 실행

SQL*Plus 출력 설정

• set serveroutput on

날짜 출력

BEGIN
    dbms_output.put_line('today''s : ' || to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
    dbms_output.put_line('tomorrow''s : ' || to_char(sysdate + 1, 'yyyy-mm-dd hh24:mi:ss'));
END;
/

BEGIN
    dbms_output.put_line(q'[today's : ]' || to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
    dbms_output.put_line(q'[tomorrow's : ]' || to_char(sysdate + 1, 'yyyy-mm-dd hh24:mi:ss'));
END;
/

변수

• 데이터를 임시로 저장하는 메모리 영역
• 문자로 시작해야 함
• 문자, 숫자, 특수문자(_, #, $) 포함 가능
• 변수명 길이는 30자 이하만 가능
• 예약어는 사용 불가
• 변수 선언시에 NOT NULL, CONSTANT(상수)로 지정된 변수에는 꼭 초기값을 할당해야 함
• 변수에 값을 할당하는 연산자 : :=, default
• 가독성 및 코드 유지 관리 효율을 높이기 위해서 각 행마다 하나씩 식별자(변수, 상수)를 선언
  • 예) v_id, v_no number; → 오류 발생
  • v_id number;
    v_no number;

예약어 조회

SELECT *
FROM v$reserved_words
WHERE reserved = 'Y';

변수 출력

• 변수 선언 후 실행부에서 초기화

DECLARE
    v_name varchar(30);
BEGIN
    dbms_output.put_line('My name is ' || v_name);
    v_name := 'james';
    dbms_output.put_line('My name is ' || v_name);
END;
/

• 변수 선언 시 초기화

DECLARE
    v_name varchar(30) := 'liam';
BEGIN
    dbms_output.put_line('My name is ' || v_name);
    v_name := 'james';
    dbms_output.put_line('My name is ' || v_name);
END;
/

Scalar data type

DECLARE
    /* scalar data type : 단일값만 보유하는 변수 */
    v_a number(5);
    v_b number(3) := 100;
    v_c varchar2(10) NOT NULL := 'oracle';
    v_d CONSTANT date default sysdate;
    v_e CONSTANT number(3) := 20;
BEGIN
    v_a := 200;
    dbms_output.put_line(v_a);
    dbms_output.put_line(v_b);
    dbms_output.put_line(v_c);
    dbms_output.put_line(v_d);
    dbms_output.put_line(v_e);
END;
/

• 단일값만 보유하는 변수
• CONSTANT : 상수
  • 다른 값으로 재할당 불가 → 오류 발생

Local Variable(지역 변수)

• 선언된 블록 프로그램에서만 수행하는 변수

DECLARE
    /* local variable(지역변수) : 선언된 블록 프로그램에서만 수행하는 변수 */
    v_sal number := 10000;
    v_comm number := 1000;
    v_total number;
BEGIN
    v_total := v_sal + v_comm;
    dbms_output.put_line(v_total);
END;
/

SELECT * FROM hr.employees WHERE salary > v_total;
-- v_total 변수는 local variable(지역 변수)이기 때문에 블록 프로그램 바깥쪽에서 사용 불가

Bind Variable(바인드 변수)

• 호스트 환경에서 생성(프로그램 바깥쪽에서 선언)
• 호스트 변수
• variable(var) 키워드를 사용해서 선언
• 바인드변수는 SQL문과 PL/SQL 블록(익명 블록) 에서 사용 가능
• 바인드 변수를 사용할 때는 콜론(:) 키워드를 바인드 변수 앞에 붙여서 사용
• global variable(전역변수) 처럼 사용하고 싶을 때 사용
• global variable(전역변수) : 프로그램 어디서든지 사용하는 변수, 세션이 열려있는 동안에는 어디서든지 사용 가능

var b_total number

DECLARE
    v_sal number := 10000;
    v_comm number := 1000;
BEGIN
    :b_total := v_sal + v_comm;
    dbms_output.put_line(:b_total);
END;
/

print :b_total
print b_total

SELECT * FROM hr.employees WHERE salary > :b_total;

바인드 변수의 필요성

• Shared Pool 메모리 비우기

ALTER SYSTEM FLUSH SHARED_POOL;

• SQL 실행

SELECT * FROM hr.employees WHERE employee_id = 100;

• Shared Pool에 캐시된 SQL 커서 정보 조회

SELECT sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
FROM v$sql
WHERE sql_text like '%employees%'
AND sql_text not like '%v$sql%';

• PARSE_CALLS : 파싱 요청 횟수
• LOADS : 하드 파싱 횟수
• EXECUTIONS : 실행 횟수
• Soft Parsing이 발생하면 LOADS 는 증가하지 않음

SELECT * FROM hr.employees WHERE employee_id = 100;
SELECT * FROM hr.employees WHERE employee_id = 101;
SELECT * FROM hr.employees WHERE employee_id = 102;
SELECT * FROM hr.employees WHERE employee_id = 103;
SELECT * FROM hr.employees WHERE employee_id = 104;
SELECT * FROM hr.employees WHERE employee_id = 105;

SELECT sql_id, sql_text, parse_calls, loads, executions, hash_value, plan_hash_value
FROM v$sql
WHERE sql_text like '%employees%'
AND sql_text not like '%v$sql%';

• 리터럴 값을 사용할 경우 실행 계획을 공유하지 못하고 각각 하드 파싱이 발생함
• 메모리, CPU 사용량 증가
• PLAN_HASH_VALUE 가 같은 쿼리들은 실행 계획이 같은 쿼리이기 때문에 바인드 변수를 사용하면 실행계획 재사용 가능
• 실행 계획 조회

SELECT * FROM table(dbms_xplan.display_cursor('fsg55tcss0s6m'));
SELECT * FROM table(dbms_xplan.display_cursor('7s0rxjt3f50a9'));

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------------

• 바인드 변수 사용

SELECT * FROM hr.employees WHERE employee_id = :b_id;

• 여러 번 실행해도 하드 파싱 발생 X

SQL*Plus에서 바인드 변수 사용법

• var 키워드로 바인드 변수 선언
• execute(exec) 로 바인드 변수 초기화

바인드 변수 선언시 주의점

• number 타입에는 사이즈를 지정하면 X

• 문자형에는 사이즈 지정해줘야 함 (지정 안 하면 1byte)

PL/SQL의 SQL 함수

프로시저 문에서 사용할 수 있는 함수

• 단일행 함수(decode, nvl2 제외)

DECLARE
    v_last_name varchar2(10) := 'hong';
    v_first_name varchar2(30) := 'gil dong';
BEGIN
    dbms_output.put_line(upper(v_last_name));
    dbms_output.put_line(initcap(v_first_name));
END;
/

DECLARE
    v_last_name varchar2(10) := initcap('hong');
    v_first_name varchar2(30) := initcap('gil dong');
BEGIN
    dbms_output.put_line(v_last_name || ' ' || v_first_name);
END;
/

DECLARE
    v_sal number := 10000;
    v_comm number;
    v_total number;
BEGIN
    v_total := v_sal * 12 + v_sal * nvl(v_comm, 0) * 12;
    dbms_output.put_line(v_total);
END;
/

DECLARE
    v_sal number := 10000;
    v_comm number := 0.1;
    v_total number;
BEGIN
    v_total := coalesce(v_sal * 12 + v_sal * v_comm * 12, v_sal * 12);
    dbms_output.put_line(v_total);
END;
/

DECLARE
    v_begin_date date := to_date('2025-01-01', 'yyyy-mm-dd');
    v_end_date date := to_date('2025-11-04', 'yyyy-mm-dd');
BEGIN
    dbms_output.put_line(v_end_date - v_begin_date);
    dbms_output.put_line(trunc(months_between(v_end_date, v_begin_date)));
    dbms_output.put_line(add_months(v_end_date, 4));
    dbms_output.put_line(next_day(v_end_date, '금요일'));
    dbms_output.put_line(last_day(v_end_date));
END;
/

프로시저 문에서 사용할 수 없는 함수

• decode, nvl2, 그룹 함수

중첩 블록

/* MAIN BLOCK, OUTER BLOCK */
DECLARE
    v_outer_variable varchar2(30) := 'outer variable';
BEGIN
    /* SUB BLOCK, INNER BLOCK */
    DECLARE
        v_inner_variable varchar2(30) := 'inner variable';
    BEGIN
        dbms_output.put_line(v_inner_variable);
        dbms_output.put_line(v_outer_variable);
    END;

    -- dbms_output.put_line(v_inner_variable); 
    -- PLS-00201: identifier 'V_INNER_VARIABLE' must be declared
    dbms_output.put_line(v_outer_variable);
END;
/

• sub block에서 선언된 v_inner_variable 변수는 main block에서 사용 불가 → 오류 발생

DECLARE
    v_father_name varchar2(20) := 'Patrick';
    v_date_of_birth date := to_date('1960-01-01', 'yyyy-mm-dd');
BEGIN
    DECLARE
        v_child_name varchar2(20) := 'Mike';
        v_date_of_birth date := to_date('1990-01-01', 'yyyy-mm-dd');
    BEGIN
        dbms_output.put_line('Father''s Name : ' || v_father_name);
        dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
        dbms_output.put_line('Child''s Name : ' || v_child_name);
        dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
    END;

    dbms_output.put_line('Father''s Name : ' || v_father_name);
    dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
END;
/

• main block과 sub block에 선언된 변수명이 동일할 경우 sub block 안에서는 sub block에서 선언된 변수의 우선순위가 더 높음

<<레이블>>

• 블록에 지정되는 이름

<<outer>>
DECLARE
    v_father_name varchar2(20) := 'Patrick';
    v_date_of_birth date := to_date('1960-01-01', 'yyyy-mm-dd');
BEGIN
    DECLARE
        v_child_name varchar2(20) := 'Mike';
        v_date_of_birth date := to_date('1990-01-01', 'yyyy-mm-dd');
    BEGIN
        dbms_output.put_line('Father''s Name : ' || v_father_name);
        dbms_output.put_line('Date of Birth : ' || outer.v_date_of_birth);
        dbms_output.put_line('Child''s Name : ' || v_child_name);
        dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
    END;

    dbms_output.put_line('Father''s Name : ' || v_father_name);
    dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
END;
/

• 레이블 지정 후 레이블명.변수명 으로 sub block 안에서 main block의 변수 사용 가능

<<outer>>
DECLARE
    v_sal number := 60000;
    v_comm number := v_sal * 0.2;
    v_message varchar2(50) := 'eligible for commission';
BEGIN
    DECLARE
        v_sal number := 50000;
        v_comm number := 0;
        v_total number := v_sal + v_comm;
    BEGIN
        v_message := 'Clerk not ' || v_message;
        outer.v_comm := v_sal * 0.3;
        dbms_output.put_line('******* sub block *******');
        dbms_output.put_line(v_sal);
        dbms_output.put_line(v_comm);
        dbms_output.put_line(v_total);
        dbms_output.put_line(v_message);
    END;

    dbms_output.put_line('******* main block *******');
    dbms_output.put_line(v_sal);
    dbms_output.put_line(v_comm);
    -- dbms_output.put_line(v_total);
    dbms_output.put_line('Salesman ' || v_message);
END;
/

조건 제어문

• 조건에 따라 선별적으로 작업을 수행할 수 있음
• PL/SQL 블록 내에서 명령문의 논리적 흐름을 변경할 수 있음
• BOOLEAN은 참(TRUE), 거짓(FALSE)

IF문

• 조건을 평가하여 명령문을 수행

IF 조건 THEN
    참
END IF;

IF 조건 THEN
    참
ELSE
    거짓
END IF;

IF 조건1 THEN
    참
ELSIF 조건2 THEN 
    참
ELSIF 조건3 THEN
    참
...
ELSE 
    거짓    
END IF;

비교 연산자

• =, >, >=, <, <=, <>, !=, ^=

논리 연산자

• NOT, AND, OR

기타 비교 연산자

• IN,BETWEEN, LIKE

NULL 연산자

• IS NULL, IS NOT NULL

예시

• boolean형 변수 사용

DECLARE
    v_flag boolean := true;
BEGIN
    IF v_flag THEN
        dbms_output.put_line('참');
    END IF;
END;
/

DECLARE
    v_flag boolean;
BEGIN
    IF v_flag THEN
        dbms_output.put_line('참');
    ELSE
        dbms_output.put_line('거짓');
    END IF;
END;
/

• NULL 비교 연산자

DECLARE
    v_num number;
BEGIN
    IF v_num IS NULL THEN
        dbms_output.put_line('참');
    ELSE
        dbms_output.put_line('거짓');
    END IF;
END;
/

DECLARE
    v_num number;
BEGIN
    IF v_num IS NOT NULL THEN
        dbms_output.put_line('참');
    ELSE
        dbms_output.put_line('거짓');
    END IF;
END;
/

• 비교 연산자

DECLARE
    v_num1 number := 10;
    v_num2 number := 20;
BEGIN
    IF v_num1 >= v_num2 THEN
        dbms_output.put_line(v_num1 - v_num2);
    ELSIF v_num1 < v_num2 THEN
        dbms_output.put_line(v_num2 - v_num1);
    END IF;
END;
/

• NVL2 함수 구현

DECLARE
    v_sal number := :b_sal;
    v_comm number := :b_comm;
    v_annual_salary number;
BEGIN
    IF v_comm IS NOT NULL THEN
        v_annual_salary := v_sal * 12 + v_sal * v_comm * 12;
        dbms_output.put_line(v_annual_salary);
    ELSE
        v_annual_salary := v_sal * 12;
        dbms_output.put_line(v_annual_salary);
    END IF;
END;
/

• decode 함수, IF문으로 조건에 따른 변수 초기화 불가

DECLARE
    v_result char(1) := decode(); -- 오류 발생
BEGIN
    v_result := IF ...; -- 오류 발생
END;
/

CASE 표현식

• 조건을 평가하여 값을 반환

DECLARE
    v_grade char(1) := upper(:b_grade);
    v_appraisal varchar2(30);
BEGIN
    v_appraisal := CASE v_grade
                        WHEN 'A' THEN '참잘했어요'
                        WHEN 'B' THEN '잘했어요'
                        WHEN 'C' THEN '다음에 잘해요'
                        ELSE 
                            '니가 사람이야!!'
                    END;
    dbms_output.put_line('등급은 ' || v_grade || ' 평가는 ' || v_appraisal);
END;
/

DECLARE
    v_grade char(1) := upper(:b_grade);
    v_appraisal varchar2(30);
BEGIN
    v_appraisal := CASE WHEN v_grade = 'A' THEN '참잘했어요'
                        WHEN v_grade IN ('B', 'C') THEN '잘했어요'
                        WHEN v_grade = 'D' THEN '다음에 잘해요'
                        ELSE 
                            '니가 사람이야!!'
                    END;
    dbms_output.put_line('등급은 ' || v_grade || ' 평가는 ' || v_appraisal);
END;
/

DECLARE
    v_grade char(1) := upper(:b_grade);
    v_appraisal varchar2(30);
BEGIN
    IF v_grade = 'A' THEN
        v_appraisal := '참잘했어요'
    ELSIF v_grade IN ('B', 'C') THEN
        v_appraisal := '잘했어요'
    ELSIF v_grade = 'D' THEN
        v_appraisal := '다음에 잘해요'
    ELSE
        v_appraisal := '니가 사람이야!!'
    END IF;

    dbms_output.put_line('등급은 ' || v_grade || ' 평가는 ' || v_appraisal);
END;
/