2021년 12월 23일 목
프롤로그
오늘은 PL/SQL에 대해 배웠습니다. 오늘 배우면서 어제 조사할 때는 이해하지 못했던 프로시저나 패키지에 대해 약간은 이해할 수 있게 된 것 같습니다. 수업시간에 이해는 했지만 배운내용이 많아서 다시 한번 적으면서 정리해보도록 하겠습니다.
<PL/SQL Block의 종류>
<구조>
<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문>
LOOP
실행문
EXIT WHEN 조건식
IF 조건식 THEN
EXIT
END IF;
END LOOP;
FOR 변수(index IN 최소값..최대값) 1....10
LOOP
실행문
END LOOP;
WHILE 조건식
LOOP
실행문
END LOOP;
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;
/
EXECUTE 프로시저 이름
EXECUTE (입력변수)
EXECUTE PRO_BOOK_COUNT('축구');
<예제>
/
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;