#PL/SQL

김유미·2021년 12월 23일
0

2021년 12월 23일 목

프롤로그

오늘은 PL/SQL에 대해 배웠습니다. 오늘 배우면서 어제 조사할 때는 이해하지 못했던 프로시저나 패키지에 대해 약간은 이해할 수 있게 된 것 같습니다. 수업시간에 이해는 했지만 배운내용이 많아서 다시 한번 적으면서 정리해보도록 하겠습니다.

PL/SQL

  • Procedural Language/Structured Query Language의 줄임말로 데이터베이스 응용 프로그램을 작성하는 데 사용하는 오라클의 SQL 전용 언어
  • SQL 문에 변수, 제어, 입출력 등의 프로그래밍 기능을 추가하여 SQL만으로 처리하기 복잡한 문제를 해결하는 용도로 사용
  • SQL언어는 비절차 언어이기 때문에 쿼리문들간에 연결점이 없음. PL/SQL을 사용하여 연결점을 만들어 프로그램으로 동작하게 하는 언어

<PL/SQL Block의 종류>

  • 1) 익명블록 : 이름이 없는 PL/SQL 블록 -- 재사용 불가능
  • 2) 이름있는 블록
    • 프로시저 : 이름이 있는 PL/SQL 블록
    • 트리거 : 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록
    • 함수 : 반환값이 존재하는 PL/SQL 블록
      -- 반환값 ex. 합계값
    • 패키지 : 하나 이상의 프로시저, 함수 등의 묶음

<구조>

  • 선언부
    • DECLARE로 시작
    • 실행부와 예외처리부에서 사용할 각종 변수, 상수, 커서 등을 선언
    • 각 문장의 끝에 반드시 세미콜론(;)을 찍어야함
  • 실행부
    • 실제 로직을 처리하는 부분으로 각종 문장(일반 SQL문, 조건문, 반복문 등)을 넣음
    • DML문(SELECT, INSERT, DELETE)만 사용가능
  • 예외처리부 (생략가능)
  • EXCEPTION절로 시작
  • 실행부에서 로직 처리 중 오류 발생시 처리할 내용 기술
    -- 실습할 때 생략하고 실습

<DECLARE 선언부>

  • 변수 (스칼라, 참조형)
  • 상수 (변하지 않는 수)

<BEGIN 실행부>

  • DML 실행문

  • 조건, 반복 등 연산

  • 출력

  • 실습 예제

    DECLARE
    v_bookname varchar2(50);
    v_bookname book.bookname%type;
    --변수명 테이블명.컬럼명%type -> 컬럼과 똑같은 타입으로 변수의 타입 설정
    -- 한 컬럼의 타입
    BEGIN
    select bookname
    into v_bookname
    from book
    where bookid = 5;
    dbms_output.put_line('책이름 : ' || v_bookname);
    END;
    /

<IF 조건문>

  • 실습예제

    DECLARE
    v_price book.price%type;
    BEGIN
    select price
    into v_price --실제가격조회
    from
    book
    where bookid = 2;
    IF v_price > 10000 THEN
    update book
    set price = price-1000
    where bookid = 2;
    ELSIF v_price < 10000 THEN
    update book
    set price = price+1000
    where bookid = 2;
    END IF;
    select price
    into v_price --변경된 값
    from book
    where bookid = 2;
    commit;
    dbms_output.put_line(v_price);
    END;
    /

<LOOP문>

  • 반복문
  • 종류
    1. loop문 : 실행문을 반복하다 조건식이 만족되면 LOOP문을 종료하고 빠져나옴

    LOOP
    실행문
    EXIT WHEN 조건식
    IF 조건식 THEN
    EXIT
    END IF;
    END LOOP;

  1. for loop문 : 반복의 횟수가 결정되어있는 LOOP문

FOR 변수(index IN 최소값..최대값) 1....10
LOOP
실행문
END LOOP;

  1. while loop문 : 처음부터 조건식이 존재하는 LOOP문
    a<10

WHILE 조건식
LOOP
실행문
END LOOP;

  • 예제) 1~10 합계를 구하시오

    DECLARE
    v_sum number :=0;
    i number := 1;
    BEGIN
    WHILE i <= 10
    LOOP
    v_sum := v_sum + i;
    i := i+1;
    END LOOP;
    dbms_output.put_line('합계 : '|| v_sum);
    END;
    /

저장프로시저

  • PL/SQL 을 주기적으로 사용해야 할 때 이름을 줘서 오라클에 저장 해 두는 PL/SQL 프로그램
  • 생성
  • 실행

    EXECUTE 프로시저 이름

  • 입력을 받는 프로시저 생성
  • 실행

    EXECUTE (입력변수)

  • 예제
  • 실행

    EXECUTE PRO_BOOK_COUNT('축구');

트리거(TRIGGER)

  • 특정 상황이나 동작에 의해 이벤트가 발생할 경우 자동으로 실행되는 PL/SQL을 말함

암호화

  • 데이터베이스의 내용을 암호화 하는 것
  • 주민등록번호, 신용카드번호 등 개인정보를 데이터베이스에 저장하는 경우, 해킹 등으로 유출될 것을 대비하여 DB 내용을 암호화 하는 것
  • 암호화 (encryption), 복호화(decryption)
  • 양방향 암호화 방식
  • 대칭키 방식(AES)/ 비대칭키 방식(RSA)
  • 단방향 암호화 방식
  • SHA, md5

<예제>

/
CREATE OR REPLACE PACKAGE CRYPTO
IS
FUNCTION ENCRYPT (input_string IN VARCHAR2
, key_data IN VARCHAR2 := 'kitrikitrikey123') -- key
RETURN RAW;
FUNCTION DECRYPT (input_string IN VARCHAR2
, key_data IN VARCHAR2 := 'kitrikitrikey123') -- key
RETURN VARCHAR2;
END CRYPTO;
/

CREATE OR REPLACE PACKAGE BODY CRYPTO
IS
SQLERRMSG VARCHAR2(255);
SQLERRCDE NUMBER;

--암호화 함수
FUNCTION encrypt (input_string IN VARCHAR2
, key_data IN VARCHAR2 := 'kitrikitrikey123') -- key
RETURN RAW
IS
input_raw RAW(1024);
--평문 암호화키 RAW 타입으로 변환
key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(key_data);
v_out_raw RAW(1024);
--AES 알고리즘, CBC 체인, PKCS5 패딩방식 사용
AES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
IF input_string IS NULL THEN
RETURN NULL;
end IF;

--암호화 할 문자열 RAW 타입으로 변환
input_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
v_out_raw := DBMS_CRYPTO.ENCRYPT(
src => input_raw,
typ => AES_CBC_PKCS5,
key => key_raw);
--암호화된 RAW 타입 데이터 리턴
RETURN v_out_raw;
END encrypt;

--복호화 함수
FUNCTION decrypt (input_string IN VARCHAR2
, key_data IN VARCHAR2 := 'kitrikitrikey123') -- key
RETURN VARCHAR2
IS
--평문 암호화키 RAW 타입으로 변환
key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(key_data);
output_raw RAW(1024);
v_out_string VARCHAR2(1024);
--AES 알고리즘, CBC 체인, PKCS5 패딩방식 사용
AES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;

BEGIN
IF input_string IS NULL THEN
RETURN NULL;
end IF;

output_raw := DBMS_CRYPTO.DECRYPT(
src => input_string,
typ => AES_CBC_PKCS5,
key => key_raw);
--복호화 한 RAW 데이터 UTF-8 형식의 문자열로 변환
v_out_string := UTL_I18N.RAW_TO_CHAR(output_raw, 'AL32UTF8');
--복호화된 문자열 타입 데이터 리턴
RETURN v_out_string;
END decrypt ;
END CRYPTO;
/

SELECT crypto.encrypt('1234')
FROM dual;

SELECT crypto.decrypt('3B6E230C91E6E927B2382491EA625827')
FROM dual;

--------------양방향 암호화

INSERT INTO customer
VALUES(6, '홍길동', '대한민국 서울시', crypto.encrypt('010-1234-5678'));

ALTER TABLE customer MODIFY phone varchar2(70);

SELECT custid, name, address, crypto.decrypt(phone)
FROM customer
WHERE custid = 6;

--------------단방향 암호화
SELECT STANDARDHASH('1', 'SHA256')
FROM dual;

SELECT dbms_crypto.hash(to_clob('1'),4)
FROM dual;

0개의 댓글