PL/SQL - DAY 1

BUMSOO·2024년 7월 2일
PL/SQL오라클 DBA

PL/SQL

  • Procedure Language Structured Query Language
  • SQL을 확장한 절차적 언어를 나타냅니다.
  • 프로시저(PROCEDURE) 생성자를 제공한다.
    • 변수, 상수, 데이터 유형(레코드, 배열)
    • 조건문, 반복문
    • 한번 작성하면 여러번 실행할 수 있는 재사용 가능한 프로그램 단위
  • PL/SQL 이점
    • 모듈식 프로그램 개발
    • 오라클 도구와 통합(ERP,HR,SCM,CRM...)
    • 이식성
    • 예외처리

PL/SQL 프로그램은 블록구조형식

  • 익명(ANONYMOUS) 블록구조
    • 객체 프로그램이 아니다.(DB저장되어 있지 않다.)
DECLARE(선택)
	선언부분(변수,상수,명시적커서,사용자 정의한 예외사항)
BEGIN(필수)
	실행부분(SQL,로직구현)
EXCEPTION(선택)
	예외사항 : 실행 부분에서 발생한 오류에 대한 처리방법
END;(필수)
/
BEGIN
    DBMS_OUTPUT.PUT_LINE('오늘 하루도 열심히 공부하자!!'); --문장의 끝은 꼭 ;로 닫아주기
    DBMS_OUTPUT.PUT_LINE('2~3주 기간 동안에는 PL/SQL 개발자 마음으로 출근하세요..');
END; -- 프로시저문
/

  • print와 같은 기능
    DBMS_OUTPUT.PUT_LINE() - 하나의 문장으로만 출력 가능하다.

  • SQLPLUS 에서는 꼭 환경을 설정해야 한다.
    SQL > SET SERVEROUTPUT ON

변수

  • 데이터를 임시로 저장하는 메모리 영역
  • 문자로 시작해야 한다.
  • 문자,숫자,특수문자(_,#,$)포함할 수 있다.
  • 변수이름의 길이는 30자 이하만 가능하다.
  • 예약어는 사용못한다.
    SELECT * FROM v$reserved_words; - 예약어 확인쿼리(dba에서 실행)
  • 변수선언시에 NOT NULL, CONSTANT(상수)로 지정된 변수에는 꼭 초기값을 할당해야 한다.
  • 변수에 값을 할당하는 연산자는 := 또는 default
  • 변수는 변하는 값들을 받을 수 있다.
  • 상수(CONSTANT)는 한번 받은 값만 사용해야한다.
  • 변수 또는 상수는 하나의 행에 하나씩 선언해야한다.
    예) v_id, v_no number; -- 오류발생
         v_id number;
         v_no number;
DECLARE
	v_name varchar2(20);
BEGIN
	DBMS_OUTPUT.PUT_LINE('MY name is' || v_name);
    v_name := '홍길동';
    DBMS_OUTPUT.PUT_LINE('MY name is' || v_name);
    v_name := '김범수';
    DBMS_OUTPUT.PUT_LINE('MY name is' || v_name);
END;
/

DECLARE
	v_name varchar2(20) := '이문세'; -- 초기값 설정
BEGIN
	DBMS_OUTPUT.PUT_LINE('MY name is' || v_name);
    v_name := '홍길동';
    DBMS_OUTPUT.PUT_LINE('MY name is' || v_name);
    v_name := '김범수';
    DBMS_OUTPUT.PUT_LINE('MY name is' || v_name);
END;
/

DECLARE
    v_event varchar2(30) := q'[Father's Day!]';
BEGIN
    DBMS_OUTPUT.PUT_LINE(q'[3rd Sunday in June is : ]'|| v_event);
    v_event := q'[Mother's Day!]';
    DBMS_OUTPUT.PUT_LINE(q'[2rd Sunday in May is : ]' || v_event);
END;
/

  • Scalar Data type : 단일값만 보유하는 변수
  • local variable : 선언된 블록 프로그램에서만 수행하는 변수
DECLARE
    v_a number(7);
    v_b number(3) := 100;
    v_c varchar2(10) not null := 'oracle'; -- not null시 꼭 초기값 할당
    v_d constant date default sysdate; -- 상수(constant)시 초기값 설정
    v_e constant number(3) := 10;
BEGIN
    v_a := 20;
    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);
    --v_d := sysdate+1; -- 오류발생, 상수에는 새로운 값을 입력할 수 없다.
END;
/

바인드 변수(bind variable)

  • 호스트 환경에서 생성한다.
  • 호스트 변수라고도 한다.
  • 익명블록구조 바깥쪽에서 선언된 변수를 의미
  • global variable : 프로그램 어디서든 사용하는 변수
  • variable(var) 키워드를 이용해서 선언한다.
  • 바인드 변수는 SQL문, 익명블록 구조에서 사용할 수 있다.
  • 바인드 변수를 사용할때 콜론(:)키워드를 바인드 변수 이름앞에 입력해야한다.
  • session 종료시 삭제된다.
  • 입력값과 출력값 역할을 한다.
-- bind 변수 number 타입에는 사이즈 명시하면 안된다.

variable b_total number 

DECLARE 
    /* scalar data type */
    /*    local variable : 선언된 블록 프로그램에서만 수행하는 변수 */
    v_sal number := 20000;
    v_comm number := 100;
BEGIN
    :b_total := v_sal+v_comm; --bind변수 앞에 : 붙이기, session종료 시 까지 변수 저장
    DBMS_OUTPUT.PUT_LINE(:b_total);
END;
/

SELECT *
FROM hr.employees
WHERE salary > :b_total;
  • SQL PLUS
    print b_total

excute

  • bind 변수를 선언할 수 있는 명령어
  • 익명블록 구조 밖에서 선언 해야 할때 사용
var b_id number
execute :b_id := 100

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

BEGIN
	:b_id := 101;
END;

SELECT * FROM hr.employees WHERE employee_id = :b_id; -- 조건으로 bind 변수를 사용하는 이유는 실행계획을 공유한다.
/
  • 바인드 변수를 사용하면 실행계획을 공유하여 소프트 파싱을 유도한다.
var b_total number
var b_sal number
var b_comm number

execute :b_sal := 1000
exec :b_comm := 10

DECLARE
    v_sal number := :b_sal;
    v_comm number := :b_comm;
BEGIN 
    :b_total := v_sal + v_comm;
END;
/

PL/SQL의 SQL함수

  • 프로시저문에서 사용할 수 있는 함수 : 단일행 함수
  • 프로시저문에서 사용할 수 없는 함수 : decode,그룹함수
DECLARE 
    v_last_name varchar2(10) := 'hong'; --프로시저문
    v_first_name varchar2(10) := 'gil dong';
BEGIN
    DBMS_OUTPUT.PUT_LINE(upper(v_last_name) ||' ' || upper(v_first_name)); --프로시저문 , 단일행함수 upper
    v_last_name := upper(v_last_name);
END;

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

v_sal := sum(v_sal); -- 오류발생
v_flag := decode(); -- 오류발생
DECLARE 
    v_begin date := to_date('2024-06-10','yyyy-mm-dd');
    v_end date := to_date('2024-11-13','yyyy-mm-dd');
BEGIN
    dbms_output.put_line(v_end - v_begin);
    dbms_output.put_line(trunc(months_between(v_end,v_begin)));
    dbms_output.put_line(add_months(v_begin,5));
    dbms_output.put_line(next_day(v_end,'금요일'));
    dbms_output.put_line(last_day(v_end));
END;
/

DECLARE 
    warranty_time interval year(3) to month;
    test_time interval day(3) to second;
BEGIN
    warranty_time := to_yminterval('10-11');
    dbms_output.put_line(sysdate + warranty_time);
    warranty_time := interval '100' year;
    dbms_output.put_line(to_char(sysdate + warranty_time,'yyyy-mm-dd'));
    warranty_time := interval '8' month;
    dbms_output.put_line(to_char(sysdate + warranty_time,'yyyy-mm-dd'));
    warranty_time := interval '100-2' year to month;
    dbms_output.put_line(to_char(sysdate + warranty_time,'yyyy-mm-dd'));
    test_time := '134 00:00:00';
    dbms_output.put_line(to_char(sysdate + test_time,'yyyy-mm-dd'));
    test_time := interval '134 00:00:00' day to second;
    dbms_output.put_line(to_char(sysdate + test_time,'yyyy-mm-dd'));
END;
/

변수 범위

  • main block에서 선언된 변수는 자신의 프로그램 어디서든 사용할 수 있다.
  • sub block에서 선언된 변수는 자신의 block 안에서만 사용해야 한다.
  • 우선권은 자기 block안에 선언된 변수에서 찾고 없으면 outer block에서 찾는다.
/* main block, outer block*/
DECLARE 
    v_outer_variable varchar2(20) := 'main block';
    
BEGIN
    /* sub block, inner block*/
    DECLARE
        v_inner_variable varchar2(20) := 'sub block';    
    BEGIN
        dbms_output.put_line(v_inner_variable);
        dbms_output.put_line(v_outer_variable);
        --자기 block에서 찾아보고 없으면 메인 block에서 찾음
    END;
	--dbms_output.put_line(v_inner_variable); --sub block에서 선언된 변수임으로 오류발생
END;

DECLARE 
    v_father_name varchar2(20) := 'Patrick';
    v_date_of_birth date := to_date('1960-01-01','yyyy-mm-dd');
    
BEGIN
    /* sub block, inner block*/
    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 of Birth : ' || v_child_name);
        dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
    END;
    dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
END;
/

<<>>레이블

  • main block과 sub block의 변수이름이 동일한 경우 main block에 <<레이블이름>> 을 달아주면 구분할 수 있다.
<<outer>>
DECLARE 
    v_father_name varchar2(20) := 'Patrick';
    v_date_of_birth date := to_date('1960-01-01','yyyy-mm-dd');
    
BEGIN
    /* sub block, inner block*/
    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 of Birth : ' || v_child_name);
        dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
    END;
    dbms_output.put_line('Date of Birth : ' || v_date_of_birth);
END;
/

[17일차 후기]
오늘은 PL/SQL을 배운 첫날 이었다. 그동안의 SQL이라고 하면 정제되어있는 데이터에서 결과적으로 추출하고 보여주기만 비절차적 언어 였다면, 확실히 PL/SQL은 프로그래밍적 요소가 들어가서 인지 코딩 하는 느낌이 들었다. DBMS_OUTPUT.PUT_LINE을 해서 문장을 출력했을 때는 마치 처음 파이썬에서 print를 사용하여 "HELLO WORLD" 를 떠올리게 만들었다. PL/SQL를 이용하여 여러가지 프로그램을 만들 수 있는데 그중 오늘 배운것은 익명블록구조의 프로그램을 배웠다. 예전 ADSP를 공부할때 봤던 구조였는데 그 당시에는 이해하지 못해 넘어갔었는데 오늘이 되서야 어느정도 구조를 알게 되었다. SQL에서는 할 수 없었던 변수 선언을 드디어 하게 되었는데, 역시 모든 프로그램 기본은 비슷한지 여기서도 지역변수, 전역변수의 개념이 나왔다. DECLARE 안에서 선언하는 변수는 지역 변수였고, 그 밖에서 (호스트부분)에서 선언하는 변수는 PL/SQL에서는 bind 변수 라는 개념으로 가지고 있었다. 이부분에서 인상 깊었던건 다른 프로그램에서는 하지 않는 bind변수 앞에는 반듯이 :(콜론)을 붙여야 한다는 점이었다. 이 bind 변수를 배움으로써 수업 초반에는 이해 하지 못했던 실행계획의 하드파싱, 소프트 파싱에 대해 이해하게 되었다. 우리가 SELECT문을 실행하게 되면 실행계획이 만들어 지는데 같은 PLAN_HASH_VALUE을 가지더라도 조건이 상수로 들어가면 하드파싱이 생긴다. 그럼 결국 shared pool의 메모리를 차지하게 되어 오라클 성능에 악영향을 끼치는데 이러한 하드파싱을 소프트파싱으로 전환시켜주는 방법중 하나가 바로 조건에 변수를 사용하는 것이다. 이러한 변수는 SQL로는 할 수 없고 PL/SQL로 해줘야 하는 부분이었다. 나는 오늘 이걸 이해 한것만으로도 만족한다. 마지막부분에서 배운 변수의 범위 부분은 다른 언어에서도 비슷한 개념으로 가지고 있는 것 같아 이해하기 어렵지는 않았다. 여기서 또 귀여웠던건 오라클만의 문법 레이블(<<>>) 가지고 같은 변수명이 있을경우 구분해 준다는 부분이었다.

profile
BUMSOO
이전 포스트

SQL 마무리 시험

다음 포스트

PL/SQL - DAY 2

0개의 댓글