'knowhow/Oracle' 카테고리의 글 목록 (2 Page)

ROW_NUMBER() 사용법

knowhow/Oracle 2009. 10. 6. 18:13

어떻게 작성하다 보니 오라클 관련으로 글이 참 많아 진다.

오늘은 ROW_NUMBER() 에 관하여 작성해 본다.

이게 참 유용하게 쓰인다.

물론 순서를 사용하기 위해서는 rownum을 많이 사용하기는 한다.

하지만 order by의 취양점이 있다.

예를 들어

SELECT rownum , aaa,bbb FROM table 이라고 해서 나온값과

SELECT rownum , aaa,bbb FROM table ORDER BY aaa 해서 나온값이 다르다.

또한 rownum을 순서로 이용하려면.
SELECT rownum , aaa,bbb
FROM (SELECT aaa,bbb
   FROM table ORDER BY aaa
)
이렇게 써야 원하는 결과를 얻을 수 있다.

이해 반해 ROW_NUMBER() 조건 절을 두어 그 안에서의 순서를 보여줄 수 있다
즉 각각의 데이터에 순서를 붙이기 위해서 유용하게 사용된다.

SELECT aaa, bbb, ROW_NUMBER() OVER (PARTITION BY aaa ORDER BY bbb) as ccc
FROM table

이렇게 작성 하면 aaa 그룹별로 bbb의 순서에 따라서 순서값을 얻을 수 있다.

결과값
aaa bbb ccc
--------    -------- --------
10 a    1
10 b    2
10 c 3
20 a 1
20 c 2
20 d 3
20 e 4
20 f    5
30 a 1
30 d 2

요다할아범

,

오라클 고급 쿼리 - connect by

knowhow/Oracle 2009. 9. 23. 15:01

편의상 짧은 어투 양해바라며...

후... 강좌 올만에 올리는군요...그동안 블로그 관리에 소홀히 했는데 이제부턴 열심히 해야겠습니다.

오라클에서 제공하는 쿼리중 좀더 고급스러운 쿼리를 사용해 보도록하자.

처음으로 살펴볼 쿼리는 이른바 계층적 쿼리이다.

CONNECT BY 절을 사용하는데 이 계층적 쿼리는 오라클만이 가능 기능으로써,

데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.

scott의 emp 테이블을 기억하는가?

그 사원테이블에는 사원번호 와 함께 사원의 메니저의 번호(fk) 를 참조하는 외부키 컬럼이 있다.

바로 self join을 사용하는 가장 흔한 예라 하겠다.

필자는 emp테이블로 설명을 하려 했으나, 역시나 지겨운 관계로 새로 테이블을 만들었다.

쇼핑몰이나 기타 솔루션에서 흔히 보게 되는 카테고리 정보 테이블을 예로 들면서 강좌를 진행하도록 하겠다.

우선 DB 데이블의 schema부터 보도록하자.

1. 카테고리 정보 테이블(PRT_CATE)

DDL

-- Create table
create table PRT_CATE
(
NO NUMBER not null,
CATE_NAME VARCHAR2(100) not null,
BASE_CATE_NO NUMBER,
CATE_MEMO VARCHAR2(500),
CATE_VIEW_CNT NUMBER default 0
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PRT_CATE.NO
is 'pk';
comment on column PRT_CATE.BASE_CATE_NO
is '상위 카테고리 의 FK';
comment on column PRT_CATE.CATE_MEMO
is '카테고리의 설명.';
comment on column PRT_CATE.CATE_VIEW_CNT
is '카테고리 열람 횟수';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT_CATE
add constraint PRT_CATE_PK primary key (NO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

2. 상품 테이블(PRT)

-- Create table
create table PRT
(
NO NUMBER not null,
PRT_NAME VARCHAR2(100) not null,
PRT_PRICE NUMBER not null,
PRT_CATE_NO NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PRT.NO
is 'PK';
comment on column PRT.PRT_NAME
is 'NAME';
comment on column PRT.PRT_PRICE
is '가격';
comment on column PRT.PRT_CATE_NO
is '상품이 속한 카테고리 FK';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT
add constraint PRT_PK primary key (NO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

3. R-R

역시나 필자의 귀차니즘에 의한 엄청나게 허술한 그림이 돋보인다..(예전의 열정은 사라졌다. ㅎ)

위의 schema를 쭉 본 독자들이라면 거의 모두다 파악이 될것이다.

주목할 점은 바로 저, self join을 해야할 PRT_CATE테이블이다.

자 독자라면 여기서 카테고리의 계층 구조를 쿼리로 나타내어 보라.

(8번PK의 DB카테고리의 계층구조는 컴퓨터 > 소프트웨어 > DB 이다.)

우선 필자는 이렇게 처리하였다.

---------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE CATE_LISTING
   (p_cateName VARCHAR2, p_level NUMBER DEFAULT 0)
AS
BEGIN
   --PRINT
   DBMS_OUTPUT.put_line(lpad(' ',p_level * 3,' ') || p_cateName);

   FOR CATE_ROW IN
   (
   SELECT * FROM PRT_CATE C
   WHERE C.BASE_CATE_NO IN (
   SELECT C2.NO FROM PRT_CATE C2
   WHERE C2.CATE_NAME = p_cateName
   )
   ORDER BY C.NO
   )
   LOOP

   -- PROCEDURE CALL
   CATE_LISTING(CATE_ROW.CATE_NAME,p_level + 1);

   END LOOP;

END ;

-- EXECUTE
CALL CATE_LISTING('컴퓨터',0);

---------------------------------------------------------------------------------------------

결과>

--------------

컴퓨터
소프트웨어
DB
Tools
주변제품
---------------

재귀 프로시져 기법으로 처리하였다.

커서등을 사용하는것도 하나의 방법이 될수 있겠다.

하지만, 너무 복잡하지않은가?

자 이쯤에서 오라클이 자랑하는(?) 기능중 계층적 쿼리를 사용해 보도록하자.

필자는 똑같은 결과를 이렇게 바꾸었다.

---------------------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
START WITH C.CATE_NAME = '컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

---------------------------------------------------------------------------------------------

결과>

오, 놀랍지않은가? 엄청나게 코드가 줄었다.(그렇다면 퍼포먼스는?? 오라클 도큐먼트를 살펴보길 바란다.)

우선 START WITH 절부터 보도록 하자.

SELECT 구문의 START WITH 절은 계층 구조가 어떤 행으로부터 시작하는지 지정하는 절이다.

일종의 WHERE절이라 생각해도 좋다.

START WITH <조건>

필자는 예제에서

START WITH C.CATE_NAME = '컴퓨터'

라고 작성하였다.

바로 카테고리 이름이 '컴퓨터' 인 레코드부터 계층적 쿼리를 수행하라는 의미이다.

START WITH절은 기본적으로 WHERE절에서 사용가능한 sql을 모두 사용할수 있다.

이를 테면

---------------------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
--START WITH C.CATE_NAME = '컴퓨터'
START WITH C.CATE_NAME IN
(SELECT CATE_NAME FROM PRT_CATE WHERE CATE_NAME = '컴퓨터' OR CATE_NAME = '가전제품')
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

--------------------------------------------------------------------------------

와 같이 바꿀수도 있다.

이때 주의할점은 아래의 결과값과 같이 OR 연산 에 의해 두개의 레코드로 부터 계층쿼리가 시작이 되므로

결과값도 아래와 같이 두세트로 나온다는것에 주의하기 바란다.

(사실 이러한 경우때문에 가능하면 명료하고 구체적인 , 결과 레코드가 적은 조건을 사용하여야 쿼리 퍼포먼스가 향상된다. )

결과값>

다음은

CONNECT BY PRIOR 절을 살펴보도록하자.

CONNECT BY 절은 각각의 행들이 어떻게 연결되어야하는지 (여기서는 JOIN을 뜻한다.) 정보를 작성한다.

무슨말이냐하면, 계층적 구조 에서 각 행의 연결 관계를 설정하는것인데...

사원테이블의 사원번호와 , 사원테이블의 사수번호가 여기에 해당된다 하겠다.

그렇다면 어떤 컬럼과 어떤 컬럼이 상위이고 하위인지 이러한 계층정보는 어떻게 설정하는 것일까?

바로 이때 사용하는 키워드가 PRIOR 이다.

필자가 작성한 예제를 보면

CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

와 같이 작성하였는데..

우선 PRIOR 키워드를 빼고 생각해보자.

CONNECT BY

C.NO = C.BASE_CATE_NO;

이와 같이 되는데 이때 NO 컬럼과 BASE_CATE_NO이 계층적 연결 관계에 있다라고 설정하는것이다

(SELF JOIN이라 생각해도 좋다.)

이제 PRIOR를 생각해보자.

CONNECT BY

PRIOR C.NO = C.BASE_CATE_NO;

OUTER JOIN을 생각해보라.

C.NO (+) = C.BASE_CATE_NO; 와 같이, PRIOR 키워드의 위치에 집중하자.

바로 C.NO 쪽에 위치하고있다.!

그렇다면 어느쪽 컬럼에 PRIOR가 위치한다는것은 무엇을 의미할까?

이것을 말로 설명하기가 매우 어렵다. 말주변의 바닥... ㅜㅜ;;

"NO컬럼을 참조하는 BASE_CATE_NO 컬럼이 속한 레코드를 모두 찾아라" 정도로 이해하면 될듯싶다.

이렇게 쿼리를 작성하였기때문에 상위 '컴퓨터' 카테고리에 해당하는 하위 카테고리를 찾았고, 또 그 하위 카테고리의

하위 카테고리를 찾을 수 있게 된것이다.

(PRIOR 키워드의 위치를 바꾸어서 실습해보면 이해에 도움이 된다.)

자 이쯤에서 다음으로 넘어가자.

알면 유용한 키워드 LEVEL !

필자는 위의 예제에서 LEVEL 키워드 사용예를 위해 LEVEL키워드를 추가하였다.

--------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME = '컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

--------------------------------------------------------------------------------

결과값>

눈치빠른 독자라면 LEVEL키워드의 쓰임을 알것이다.

LEVEL은 오라클에서 실행되는 모든 쿼리내에서 ROWNUM과 더불어 가상-컬럼 이라 할수 있는데,

계층적 쿼리 트리내에서 어느한 위치,또는 단계(LEVEL)에 위치하는가를 나타내는 정수값 컬럼이다.

그렇다면 당근, 계층적 쿼리가 아닌 일반 쿼리에서 LEVEL컬럼의 값이 모두 0 으로 나올것이다.

자, LEVEL키워드를 어디에 사용할까??

예를들면 2단계까지의 카테고리 정보만을 출력하라 라는 식의 요구사항일때 유용하다.

예상되는 쿼리는

WHERE LEVEL < 3 일것같지만 사실 LEVEL키워드는 CONNECT BY절에 위치하여야한다.

--------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME = '컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO AND LEVEL < 3;

--------------------------------------------------------------------------------

결과값>

이로써 오라클이 제공하는 고급쿼리중 CONNECT BY 절에 대해 마칠까 한다.

부족한 강좌 끝까지 읽어주셔서 감사합니다.

[출처] [DEV.WON] 오라클 고급쿼리(1) - CONNECT BY|작성자 데브원

요다할아범

,

쿼리로 소계, 합계 구하기

knowhow/Oracle 2009. 7. 27. 18:48

. ROLLUP의 단계적 이해
: 소계, 합계를 같이 구할 때 ROLLUP 사용한다.

a. 전체 합
SELECT SUM(salary)
FROM employees
WHERE department_id < 50;

SUM(SALARY)
-----------
54800

b. 부서별/직급별 합
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id, job_id;

DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
   10 AD_ASST 4400
   20 MK_MAN 13000
   20 MK_REP    6000
   30 PU_MAN 11000
   30 PU_CLERK 13900
   40 HR_REP    6500

c. 부서별 합
SELECT department_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY department_id;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
   10    4400
   20 19000
   30 24900
   40    6500

d. a,b,c 의 결과를 union all한 결과와 비교
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);

DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
   10 AD_ASST    4400
   10    4400 -->> DEPARTMENT_ID별 SUM
   20 MK_MAN 13000
   20 MK_REP 6000 -->> DEPARTMENT_ID별 SUM
   20    19000
   30 PU_MAN 11000
   30 PU_CLERK 13900
   30    24900    -->> DEPARTMENT_ID별 SUM
   40 HR_REP 6500
   40    6500 -->> DEPARTMENT_ID별 SUM
   54800    -->> DEPARTMENT_ID별 총계
2. 컬럼의 구분은 어떻게 ?
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)

DEPTNO JOB    SUM(SAL)
10 CLERK    1300
10 MANAGER    2450
10 PRESIDENT 5000
10    8750 ---->> DEPTNO별 SUM
20 CLERK    1900
20 ANALYST    6000
20 MANAGER    2975
20    10875    ---->> DEPTNO별 SUM
30 CLERK    950
30 MANAGER    2850
30 SALESMAN 5600
30    9400 ---->> DEPTNO별 SUM
29025    ---->> 총계

소계, 합계를 구분하고자 할때 GROUPING(컬럼) 또는 GROUPING_ID(컬럼1, 컬럼2)
를 사용한다.GROUPING() 함수는 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집합계산에 참여하면 0을 반환

SELECT DEPTNO, JOB, SUM(SAL),
   GROUPING(DEPTNO) A,
   GROUPING(JOB) B,
   GROUPING_ID(DEPTNO,JOB) C
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)

DEPTNO JOB SUM(SAL) A B C
10 CLERK 1300 0 0 0
10 MANAGER 2450 0 0 0
10 PRESIDENT 5000 0 0 0
10 8750 0 1 1 ---->> DEPTNO별 SUM
20 CLERK 1900 0 0 0
20 ANALYST 6000 0 0 0
20 MANAGER 2975 0 0 0
20 10875    0 1 1 ---->> DEPTNO별 SUM
30 CLERK 950    0 0 0
30 MANAGER 2850 0 0 0
30 SALESMAN 5600 0 0 0
30 9400 0 1 1 ---->> DEPTNO별 SUM
   29025    1 1 3 ---->> 총계

요다할아범

,

TABLESPACE 및 USER 생성 스크립트

knowhow/Oracle 2009. 6. 1. 10:16

- TABLESPACE

# Windows
DROP TABLESPACE koras INCLUDING CONTENTS
CREATE TABLESPACE koras
DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\koras.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M
DEFAULT STORAGE
(INITIAL 10K
   NEXT    10K
   MINEXTENTS 2
   MAXEXTENTS 50
   PCTINCREASE 50)

# Linux
DROP TABLESPACE trackview INCLUDING CONTENTS
CREATE TABLESPACE trackview
DATAFILE '/opt/oracle/product/10.2.0/oradata/trackview.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M
DEFAULT STORAGE
(INITIAL 10K
   NEXT    10K
   MINEXTENTS 2
   MAXEXTENTS 50
   PCTINCREASE 50)

- USER

DROP USER koras CASCADE;
CREATE USER koras IDENTIFIED BY koras#123
DEFAULT TABLESPACE koras
TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE, DBA TO koras;

요다할아범

,

한국건설자원협회 프로젝트 진행시 오라클 암호화 패키지 사용

knowhow/Oracle 2009. 3. 17. 00:58

먼저 SYS 계정에 아래의 패키지 생성

CREATE OR REPLACE PACKAGE SYS.DBMS_CRYPTO AS

---------------------------------------------------------------------------
--
-- PACKAGE NOTES
--
-- DBMS_CRYPTO contains basic cryptographic functions and
-- procedures. To use correctly and securely, a general level of
-- security expertise is assumed.
--
-- VARCHAR2 datatype is not supported. Cryptographic operations
-- on this type should be prefaced with conversions to a uniform
-- character set (AL32UTF8) and conversion to RAW type.
--
-- Prior to encryption, hashing or keyed hashing, CLOB datatype is
-- converted to AL32UTF8. This allows cryptographic data to be
-- transferred and understood between databases with different
-- character sets, across character set changes and between
-- separate processes (for example, Java programs).
--
---------------------------------------------------------------------------

-------------------------- ALGORITHM CONSTANTS ----------------------------
-- The following constants refer to various types of cryptographic
-- functions available from this package. Some of the constants
-- represent modifiers to these algorithms.
---------------------------------------------------------------------------

-- Hash Functions
HASH_MD4 CONSTANT PLS_INTEGER    := 1;
HASH_MD5 CONSTANT PLS_INTEGER    := 2;
HASH_SH1 CONSTANT PLS_INTEGER    := 3;

-- MAC Functions
HMAC_MD5 CONSTANT PLS_INTEGER := 1;
HMAC_SH1 CONSTANT PLS_INTEGER := 2;

-- Block Cipher Algorithms
ENCRYPT_DES    CONSTANT PLS_INTEGER    := 1; -- 0x0001
ENCRYPT_3DES_2KEY CONSTANT PLS_INTEGER    := 2; -- 0x0002
ENCRYPT_3DES CONSTANT PLS_INTEGER    := 3; -- 0x0003
ENCRYPT_AES    CONSTANT PLS_INTEGER    := 4; -- 0x0004
ENCRYPT_PBE_MD5DES CONSTANT PLS_INTEGER    := 5; -- 0x0005
ENCRYPT_AES128 CONSTANT PLS_INTEGER    := 6; -- 0x0006
ENCRYPT_AES192 CONSTANT PLS_INTEGER    := 7; -- 0x0007
ENCRYPT_AES256 CONSTANT PLS_INTEGER    := 8; -- 0x0008

-- Block Cipher Chaining Modifiers
CHAIN_CBC    CONSTANT PLS_INTEGER    := 256; -- 0x0100
CHAIN_CFB    CONSTANT PLS_INTEGER    := 512; -- 0x0200
CHAIN_ECB    CONSTANT PLS_INTEGER    := 768; -- 0x0300
CHAIN_OFB    CONSTANT PLS_INTEGER    := 1024; -- 0x0400

-- Block Cipher Padding Modifiers
PAD_PKCS5    CONSTANT PLS_INTEGER    := 4096; -- 0x1000
PAD_NONE CONSTANT PLS_INTEGER    := 8192; -- 0x2000
PAD_ZERO CONSTANT PLS_INTEGER    := 12288; -- 0x3000
PAD_ORCL CONSTANT PLS_INTEGER    := 16384; -- 0x4000

-- Stream Cipher Algorithms
ENCRYPT_RC4 CONSTANT PLS_INTEGER := 129; -- 0x0081

-- Convenience Constants for Block Ciphers
DES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_DES
+ CHAIN_CBC
+ PAD_PKCS5;

DES3_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_3DES
+ CHAIN_CBC
+ PAD_PKCS5;

AES_CBC_PKCS5 CONSTANT PLS_INTEGER := ENCRYPT_AES
+ CHAIN_CBC
+ PAD_PKCS5;

----------------------------- EXCEPTIONS ----------------------------------
-- Invalid Cipher Suite
CipherSuiteInvalid EXCEPTION;
PRAGMA EXCEPTION_INIT(CipherSuiteInvalid, -28827);

-- Null Cipher Suite
CipherSuiteNull EXCEPTION;
PRAGMA EXCEPTION_INIT(CipherSuiteNull, -28829);

-- Key Null
KeyNull EXCEPTION;
PRAGMA EXCEPTION_INIT(KeyNull, -28239);

-- Key Bad Size
KeyBadSize EXCEPTION;
PRAGMA EXCEPTION_INIT(KeyBadSize, -28234);

-- Double Encryption
DoubleEncryption EXCEPTION;
PRAGMA EXCEPTION_INIT(DoubleEncryption, -28233);

---------------------- FUNCTIONS AND PROCEDURES ------------------------

------------------------------------------------------------------------
--
-- NAME: Encrypt
--
-- DESCRIPTION:
--
-- Encrypt plain text data using stream or block cipher with user
-- supplied key and optional iv.
--
-- PARAMETERS
--
-- plaintext - Plaintext data to be encrypted
-- crypto_type - Stream or block cipher type plus modifiers
-- key - Key to be used for encryption
-- iv - Optional IV for block ciphers. Default all zeros.
--
-- USAGE NOTES:
--
-- Block ciphers may be modified with chaining type (CBC most
-- common) and padding type (PKCS5 recommended). Of the four
-- common data formats, three have been provided: RAW, BLOB,
-- CLOB. For VARCHAR2 encryption, callers should first convert
-- to AL32UTF8 character set and then encrypt.
--
-- Encrypt(UTL_RAW.CAST_TO_RAW(CONVERT(src,'AL32UTF8')),typ,key);
--
-- As return type for encrypt is RAW, callers should consider
-- encoding it with RAWTOHEX or UTL_ENCODE.BASE64_ENCODE to make
-- it suitable for VARCHAR2 storage. These functions expand
-- data size by 2 and 4/3, respectively.
--
-- To improve readability, callers should define their own
-- package level constants to represent the ciphersuites used
-- for encryption and decryption.
--
-- For example:
--
-- DES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
-- + DBMS_CRYPTO.CHAIN_CBC
-- + DBMS_CRYPTO.PAD_PKCS5;
--
--
-- STREAM CIPHERS (RC4) ARE NOT RECOMMENDED FOR STORED DATA ENCRYPTION.
--
--
------------------------------------------------------------------------

FUNCTION Encrypt (src IN    RAW,
   typ IN    PLS_INTEGER,
   key IN    RAW,
   iv IN    RAW    DEFAULT NULL)
RETURN RAW;

PROCEDURE Encrypt (dst IN OUT NOCOPY BLOB,
   src IN    BLOB,
   typ IN    PLS_INTEGER,
   key IN    RAW,
   iv IN    RAW    DEFAULT NULL);

PROCEDURE Encrypt (dst IN OUT NOCOPY BLOB,
   src IN    CLOB CHARACTER SET ANY_CS,
   typ IN    PLS_INTEGER,
   key IN    RAW,
   iv IN    RAW    DEFAULT NULL);

------------------------------------------------------------------------
--
-- NAME: Decrypt
--
-- DESCRIPTION:
--
-- Decrypt crypt text data using stream or block cipher with user
-- supplied key and optional iv.
--
-- PARAMETERS
--
-- cryptext - Crypt text data to be decrypted
-- crypto_type - Stream or block cipher type plus modifiers
-- key - Key to be used for encryption
-- iv - Optional IV for block ciphers. Default all zeros.
--
-- USAGE NOTES:
-- To retrieve original plain text data, Decrypt must be called
-- with the same cipher, modifiers, key and iv used for
-- encryption. If crypt text data was converted to hex or
-- base64 prior to storage, it must be decoded using HEXTORAW or
-- UTL_ENCODE.BASE64_DECODE prior to decryption.
--
------------------------------------------------------------------------

FUNCTION Decrypt (src IN    RAW,
   typ IN    PLS_INTEGER,
   key IN    RAW,
   iv IN    RAW    DEFAULT NULL)
   RETURN RAW;

PROCEDURE Decrypt (dst IN OUT NOCOPY BLOB,
   src IN    BLOB,
   typ IN    PLS_INTEGER,
   key IN    RAW,
   iv IN    RAW    DEFAULT NULL);

PROCEDURE Decrypt (dst IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   src IN    BLOB,
   typ IN    PLS_INTEGER,
   key IN    RAW,
   iv IN    RAW    DEFAULT NULL);

------------------------------------------------------------------------
--
-- NAME: Hash
--
-- DESCRIPTION:
--
-- Hash source data by cryptographic hash type.
--
-- PARAMETERS
--
-- source - Source data to be hashed
-- hash_type - Hash algorithm to be used
--
-- USAGE NOTES:
-- SHA-1 (HASH_SH1) is recommended. Consider encoding returned
-- raw value to hex or base64 prior to storage.
--
------------------------------------------------------------------------

FUNCTION Hash (src IN RAW,
typ IN PLS_INTEGER)
RETURN RAW DETERMINISTIC;

FUNCTION Hash (src IN BLOB,
typ IN PLS_INTEGER)
RETURN RAW DETERMINISTIC;

FUNCTION Hash (src IN CLOB CHARACTER SET ANY_CS,
typ IN PLS_INTEGER)
RETURN RAW DETERMINISTIC;

------------------------------------------------------------------------
--
-- NAME: Mac
--
-- DESCRIPTION:
--
-- Message Authentication Code algorithms provide keyed message
-- protection.
--
-- PARAMETERS
--
-- source - Source data to be mac-ed
-- mac_type - Mac algorithm to be used
-- key - Key to be used for mac
--
-- USAGE NOTES:
-- Callers should consider encoding returned raw value to hex or
-- base64 prior to storage.
--
------------------------------------------------------------------------
FUNCTION Mac (src IN RAW,
typ IN PLS_INTEGER,
key IN RAW)
RETURN RAW;

FUNCTION Mac (src IN BLOB,
typ IN PLS_INTEGER,
key IN RAW)
RETURN RAW;

FUNCTION Mac (src IN CLOB CHARACTER SET ANY_CS,
typ IN PLS_INTEGER,
key IN RAW)
RETURN RAW;

------------------------------------------------------------------------
--
-- NAME: RandomBytes
--
-- DESCRIPTION:
--
-- Returns a raw value containing a pseudo-random sequence of
-- bytes.
--
-- PARAMETERS
--
-- number_bytes - Number of pseudo-random bytes to be generated.
--
-- USAGE NOTES:
-- number_bytes should not exceed maximum RAW length.
--
------------------------------------------------------------------------
FUNCTION RandomBytes (number_bytes IN PLS_INTEGER)
RETURN RAW;

------------------------------------------------------------------------
--
-- NAME: RandomNumber
--
-- DESCRIPTION:
--
-- Returns a random Oracle Number.
--
-- PARAMETERS
--
-- None.
--
------------------------------------------------------------------------
FUNCTION RandomNumber
RETURN NUMBER;

------------------------------------------------------------------------
--
-- NAME: RandomInteger
--
-- DESCRIPTION:
--
-- Returns a random BINARY_INTEGER.
--
-- PARAMETERS
--
-- None.
--
------------------------------------------------------------------------
FUNCTION RandomInteger
RETURN BINARY_INTEGER;

PRAGMA RESTRICT_REFERENCES(DEFAULT, WNDS, RNDS, WNPS, RNPS);

END DBMS_CRYPTO;
/

그리고

오라클 암호화 DBMS_CRYPTO (10g 이상)
------------------------------------------------------------------------------------------

▶ 권한부여
SQL> grant execute on DBMS_CRYPTO to [계정명];

▶ Create Function (일반계정용)
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION KORAS.ENCRY(strValue VARCHAR2, strEncKey VARCHAR2)
RETURN RAW
IS
input_raw RAW(1024);
key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(strEncKey);
v_out_raw RAW(1024);
AES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
input_raw := UTL_I18N.STRING_TO_RAW(strValue, 'AL32UTF8');
v_out_raw := DBMS_CRYPTO.ENCRYPT(
src => input_raw,
typ => AES_CBC_PKCS5,
key => key_raw);

RETURN v_out_raw;

END ENCRY;
/

CREATE OR REPLACE FUNCTION KORAS.DECRY(v_in_raw RAW, strEncKey VARCHAR2)
RETURN VARCHAR2
IS
key_raw RAW(16) := UTL_RAW.CAST_TO_RAW(strEncKey);
output_raw RAW(1024);
v_out_string VARCHAR2(1024);
AES_CBC_PKCS5 CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
output_raw := DBMS_CRYPTO.DECRYPT(
src => v_in_raw,
typ => AES_CBC_PKCS5,
key => key_raw);
v_out_string := UTL_I18N.RAW_TO_CHAR(output_raw, 'AL32UTF8');

RETURN v_out_string;

END DECRY;

돌려 주면 됨

요다할아범

,

'knowhow/Oracle'에 해당되는 글 17건

ROW_NUMBER() 사용법

오라클 고급 쿼리 - connect by

쿼리로 소계, 합계 구하기

TABLESPACE 및 USER 생성 스크립트

한국건설자원협회 프로젝트 진행시 오라클 암호화 패키지 사용

공지사항

카테고리

태그목록

글 보관함

달력

링크

요다할아범

LATEST FROM OUR BLOG

LATEST COMMENTS

BLOG VISITORS

티스토리툴바

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31