개발자인생

오라클에서는 Full Outer Join을 하기위해 

양쪽 컬럼에  컬럼(+) = 컬럼(+)를 쓰면 오류가 난다..

그러다 보니 ansi-sql 문법으로 Full Outer Join을 해야한다...

결과값

with : 오라클은 with문을 이용하여 임시 테이블 같은 기능을 쓸수 있다.

Coalesce : 오라클의 NVL 대용으로 쓸수 있는 ansi-sql 함수다.

가끔 SQL작성시  예전에 쓰던 copy_t 테이블이 필요한 경우가 있다.

connect by level 을 이용해서 copy_t 테이블을 구현할 수 있다.

스트링 값이 컬럼에 포함되어 있는 지 여부를 확인할 때 사용됨.

포함되는 위치값을 반환한다.

1. sys계정으로 암호화 하여 아래 권한을 준다.

grant execute on dbms_crypto to public;

2. 생성한다...  계정이 USER01 이면

CREATE OR REPLACE package body USER01.crypt_ase256

IS

    c_aeskey char(32) := '12345678901234567890123456789012';

    /**

     * encryption type이 아래와 같이 DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5일 경우

     * output_string의 길이는 (int(input_string/16)+1)*32 이다.

     * 즉 input_string의 길이가 100일 경우 output_string의 길이는 224이다.

     *

     * key_string는 32자리로 고정되어야 한다.

     */

    function aes_encrypt (p_str in varchar2, p_keystr in varchar2 := null) return varchar2

    is

        output_string       VARCHAR2(4000);

        encrypted_raw       RAW(2000);

        key_bytes_raw       RAW (32) ;

        encryption_type     PLS_INTEGER :=

                            DBMS_CRYPTO.ENCRYPT_AES256

                            + DBMS_CRYPTO.CHAIN_CBC

                            + DBMS_CRYPTO.PAD_PKCS5;

    begin

        if (p_keystr is not null) and (lengthb(p_keystr) = 32) then

            key_bytes_raw := UTL_RAW.CAST_TO_RAW(CONVERT(p_keystr,'AL32UTF8','US7ASCII'));

        else

            key_bytes_raw := UTL_RAW.CAST_TO_RAW(CONVERT(c_aeskey,'AL32UTF8','US7ASCII'));

        end if ;

        encrypted_raw := DBMS_CRYPTO.ENCRYPT(SRC => UTL_I18N.STRING_TO_RAW(p_str, 'AL32UTF8'), TYP => encryption_type, KEY => key_bytes_raw);

        output_string := RAWTOHEX(encrypted_raw);

        return output_string;

    end aes_encrypt;

    /**

     * key_string는 32자리로 고정되어야 한다.

     */

    function aes_decrypt (p_str in varchar2, p_keystr in varchar2 := null) return varchar2

    is

        output_string       varchar2(2000);

        decrypted_raw       raw(2000);

        key_bytes_raw       raw (32);

        decryption_type     PLS_INTEGER :=

                                DBMS_CRYPTO.ENCRYPT_AES256

                                + DBMS_CRYPTO.CHAIN_CBC

                                + DBMS_CRYPTO.PAD_PKCS5;

    begin

        if (p_keystr is not null) and (lengthb(p_keystr) = 32) then

            key_bytes_raw := UTL_RAW.CAST_TO_RAW(CONVERT(p_keystr,'AL32UTF8','US7ASCII'));

        else

            key_bytes_raw := UTL_RAW.CAST_TO_RAW(CONVERT(c_aeskey,'AL32UTF8','US7ASCII'));

        end if;

        decrypted_raw := DBMS_CRYPTO.DECRYPT(SRC => HEXTORAW(p_str), TYP => decryption_type, KEY => key_bytes_raw);

        output_string := CONVERT(UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw),'US7ASCII','AL32UTF8');

        return output_string;

    end aes_decrypt;

end crypt_ase256;

3. 아래와 같이  사용

select crypt_ase256.aes_encrypt('asssss') from dual