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;
- for loop문 : 반복의 횟수가 결정되어있는 LOOP문
FOR 변수(index IN 최소값..최대값) 1....10
LOOP
실행문
END LOOP;
- 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;
