DB] oracle 에 있는 sign() 의 기능

오라클에 있는  SIGN이라는 함수의 기능은 원하는 결과값(?!)이 양수 / 음수 / 0 이냐에 따라서 값을 반환해주는 함수.

예)
SELECT SIGN(1+1) a, SIGN(1-1)  b, SIGN(1-5) c
FROM DUAL;

결과
---------------------------

a | b | c
==========
1 | 0 | -1


추가적인 예)
필드의 값이 100 이상인 것의 합과 미만인 것의 합을 구하는 쿼리.
출처 : http://ssiso.net/cafe/club/club1/board1/content.php?board_code=oracle%7Coratip&idx=31597&club=oracle

SELECT SUM(CASE SIGN(A-100)

                         WHEN 0 THEN A

                         WHEN 1 THEN A

                         ELSE 0

                     END)                      AS OVER100,

             SUM(CASE SIGN(A-100)

                         WHEN -1 THEN A

                         ELSE 0

                     END)                      AS DOWN100

    FROM DUAL;

DBMS/오라클] 오라클 10g용db_crypto함수

--[EDIT BY SINU]

conn / as sysdba

create user sinu
identified by sinu;

grant resource, connect to sinu;
grant execute on DBMS_CRYPTO to sinu;

conn sinu/sinu

CREATE OR REPLACE PACKAGE pkg_crypto
IS
    FUNCTION encrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := '12345678'
    ) RETURN RAW;
   
    FUNCTION decrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := '12345678'
    ) RETURN VARCHAR2;

END pkg_crypto;
/


CREATE OR REPLACE PACKAGE BODY pkg_crypto
IS
    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;
   
    FUNCTION encrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
     RETURN RAW
    IS
   
        key_data_raw        RAW(64);
        converted_raw       RAW(64);
        encrypted_raw       RAW(64);

    BEGIN

        converted_raw := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');

        encrypted_raw :=
             DBMS_CRYPTO.ENCRYPT(
                 src => converted_raw ,
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);
       
        RETURN encrypted_raw;

    END encrypt;
   
    FUNCTION decrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
     RETURN VARCHAR2
    IS
        converted_string    VARCHAR2(64);
        key_data_raw        RAW(64);
        decrypted_raw    VARCHAR2(64);

    BEGIN

        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');
       
        decrypted_raw :=
            DBMS_CRYPTO.DECRYPT(
                 src => input_string ,
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);

        converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

        RETURN converted_string;

    END decrypt ;
END pkg_crypto;
/

col card_number format a64
create table card_info ( id number, card_number varchar2(64) ) ;

insert into card_info values ( 1 , pkg_crypto.encrypt('1234567812345678')) ;
insert into card_info values ( 2 , pkg_crypto.encrypt('12345678')) ;
commit;

select * from card_info ;
select id , pkg_crypto.decrypt(card_number) card_number
from card_info;

insert into card_info values ( 3 , pkg_crypto.encrypt('1234567812345678','00000000')) ;
select id , pkg_crypto.decrypt(card_number,'00000000') card_number from card_info where id = 3  ;

select * from card_info ;

drop package body pkg_crypto;
drop package pkg_crypto;
drop table card_info;

conn / as sysdba
drop user sinu cascade;

--[EDIT BY SINU]