폴랑 - 티스토리

오라클 날짜계산

** 날짜계산

-- '2011-01-20' 과 당일 사이의 달의 수를 NUMBER형 타입으로 반환
-- MONTHS_BETWEEN(A,B) = A-B/30
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('2011-01-20','YYYY-MM-DD')) FROM DUAL;

-- 특정일의 달수 더한 날
SELECT ADD_MONTHS(SYSDATE, 10) FROM DUAL;

-- 특정일의 다음주 요일
SELECT NEXT_DAY(SYSDATE,'FRIDAY') FROM DUAL;

-- 특정일의 해당 월의 마지막 날
SELECT LAST_DAY(SYSDATE) FROM DUAL;

-- 특정일의 반올림(오후면 다음날..)
SELECT ROUND(SYSDATE,'DD') FROM DUAL;

-- 특정일의 전주 토요일(해당 전주의 마지막 날)에해당하는 날짜
SELECT TRUNC(SYSDATE,'WW') FROM DUAL;

-- 특정일의 주 일요일(해당 주의 첫째 날)에해당하는 날짜
SELECT TRUNC(SYSDATE,'D') FROM DUAL;


** WHERE 조건에 날짜칼럼을 기준으로 함.
/* 어제 */ 날짜칼럼
BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE - 1) + 0.99999421
/* 오늘 */ 날짜칼럼
BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 0.99999421
/* 내일 */ 날짜칼럼
BETWEEN TRUNC(SYSDATE + 1) AND TRUNC(SYSDATE + 1) + 0.99999421
/* 금주 */ 날짜칼럼
BETWEEN TRUNC(SYSDATE + 1) - TO_CHAR(SYSDATE, 'D') AND TRUNC(SYSDATE + 1) - TO_CHAR(SYSDATE, 'D') + 6.99999421
/* 다음주 */ 날짜칼럼
BETWEEN TRUNC(SYSDATE + 8) - TO_CHAR(SYSDATE, 'D') AND TRUNC(TRUNC(SYSDATE) + 14.99999421) - TO_CHAR(SYSDATE, 'D')
/* 금월 */ 날짜칼럼
BETWEEN TRUNC(SYSDATE + 1) - TO_CHAR(SYSDATE, 'DD') AND TRUNC(LAST_DAY(SYSDATE)) + 0.99999421
/* 전월 */ 날짜칼럼
BETWEEN TRUNC(ADD_MONTHS(SYSDATE, -1) + 1) - TO_CHAR(SYSDATE,'DD') AND TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))) + 0.99999421
/* 다음달 */ 날짜칼럼
BETWEEN ADD_MONTHS(TRUNC(SYSDATE), 1) - TO_CHAR(SYSDATE, 'DD') + 1 AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE), 1) + 0.99999421)


** 특정일 까지의 간격을 년, 개월, 일로 표현하기
SELECT
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20000101', 'YYYYMMDD'))/12) "년",
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20000101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20000101', 'YYYYMMDD'))/12) * 12) "개월",
TRUNC((MONTHS_BETWEEN(SYSDATE,TO_DATE('20000101', 'YYYYMMDD')) -
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('20000101', 'YYYYMMDD')))) * 30.5) "일"
FROM DUAL;

** 당월의 주차 구하기..
SELECT
'20000101' AS "날짜", CEIL((TO_NUMBER(SUBSTRB('20000101', -2, 2)) + 7 - TO_NUMBER(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD'), 'D'))) / 7) AS "월별 주차"
FROM DUAL;

** 시간 계산 SQL
SELECT TRUNC(TO_DATE('20110101012345', 'YYYYMMDDHH24MISS') - TO_DATE('20110501213344', 'YYYYMMDDHH24MISS')) || ' DAY ' ||
TRUNC(MOD((TO_DATE('20110101012345', 'YYYYMMDDHH24MISS') - TO_DATE('20110501213344', 'YYYYMMDDHH24MISS')), 1) * 24)|| ' HOUR ' ||
TRUNC(MOD((TO_DATE('20110101012345', 'YYYYMMDDHH24MISS') - TO_DATE('20110501213344', 'YYYYMMDDHH24MISS')) * 24, 1) * 60)|| ' MINUTE ' ||
TRUNC(ROUND(MOD((TO_DATE('20110101012345', 'YYYYMMDDHH24MISS') - TO_DATE('20110501213344', 'YYYYMMDDHH24MISS')) * 24 * 60, 1) * 60))|| ' SEC '
" TIME INTERVAL "
FROM DUAL ;

ORA-12571: TNS: 패킷 라이터 실패 

// 안년하세요 김영대(http://www.howto.pe.kr) 입니다
// 자료를 찾아보니 Oracle 의 실수라고 하네요
// 아래 두가지 답변을 보시고 테스트 해보세요

--------------------------- 첫번째 --------------------------------
You are right, ORA-12571 is a common error associated with SQL*Net V2,
SQL*Net thinks the connection is broken while you are inserting lots of
rows into the Oracle database. The default timeout period is 10 secs.
In the Listener.ora you can set a longer timeout period with the line
CONNECT_TIMEOUT_LISTENER = 300 , which means 5 minutes. Myself and others
have encountered this error with the TCP/IP protocol. You raised an
interesting point, that DECNET works ok. without this parameter being
set. But I am a bit surprised that an INSERT of 500 records would cause
this error, normally I would expect many many more.

---------------------------- 두번째 -------------------------------
I often get the same messages.  I talk to Oracle support and didn't get
much help.  They put blame on faulty network equipment.  What they
suggested is to edit the sqlnet.ora file on the server and the client.
Change the SQLNET.EXPIRE_TIME = 0 and increase
CONNECT_TIMEOUT_LISTENER=300.

CONNECT_TIMEOUT_LISTENER = 0 (타임아웃 없음?) 

※oracle에서는 날짜함수에(sysdate) 산술연산이 가능합니다.

1일->1

1시간->1/24

1분->1/24/60

1초->1/24/60/60

SYSDATE + 1/24/60/60 > MY_DATA_TYPE_VAL

-- *** 변환함수, case문 ***
-- 1. to_char : 숫자를 문자로 변환 또는 날짜를 문자로 변환.

select 3600000,
        to_char(3600000, '$9,999,999,999'), -- 화폐단위
        to_char(3600000, 'L9,999,999,999')  -- 화폐단위(local, 원화, 컴퓨터에 지정된 기본 통화)
from dual;

select ename "사원명", sal,
        lpad( ltrim( to_char( nvl2(comm, sal+comm, sal)*12, '$9,999,999,999' ) ), 7, ' ') "연봉", -- 숫자는 오른쪽 정렬, 문자와 날짜는 왼쪽 정렬
        -- ltrim( to_char( nvl2(comm, sal+comm, sal)*12, '$9,999,999,999' ) ) "연봉",
        hiredate
from emp;

select to_char(sysdate, 'year'), to_char(sysdate, 'month'), to_char(sysdate, 'day')
from dual;

select to_char(sysdate, 'yyyy'), to_char(sysdate, 'mm'), to_char(sysdate, 'dd'),
        to_char(sysdate, 'hh am'), to_char(sysdate, 'hh pm'), to_char(sysdate, 'hh24'),
        to_char(sysdate, 'mi'), to_char(sysdate, 'ss')
from dual;

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
from dual;

select to_char(sysdate, 'ddd'), -- 2012년 1월 1일부터 현재까지의 날자수
        to_char(to_date('2012-01-03'), 'ddd') - 2012년 1월 1일부터 3일까지의 날자수
from dual;

select to_char(sysdate, 'dd') -- 이번달 첫째날부터 현재까지의 날자수
from dual;

select to_char(sysdate, 'd') -- 이번주 첫째날(일요일)부터 현재까지의 날자수
from dual;                    -- 1:일요일, 2:월요일, 3:화요일 ... 7:토요일

select to_char(sysdate, 'sssss') -- 오늘 0시 0분부터 현재까지의 초수
from dual;

select to_char(sysdate, 'q') -- 1월~3월 : 1분기, 4월~6월 : 2분기, 7월~9월 : 3분기, 10월~12월 : 4분기
from dual;

select to_char(sysdate, 'day'), -- 요일
        to_char(sysdate, 'dy'),  -- 요일 약자
        to_char(sysdate, 'd')    -- 날수
from dual;

-- 2. to_date : 문자를 날짜로 변환
select to_date('2012-08-10 14:30:23', 'yyyy-mm-dd hh24:mi:ss')
from dual;

-- 3. to_number : 문자(숫자형식을 가지는 문자)를 숫자로 변환
select to_number('       987')
from dual;

select to_char(sysdate, 'yyyy'),
        extract(year from sysdate), -- 숫자 형태로 출력(연월일만 가능, 시분초는 불가함)
        to_char(sysdate, 'mm'),
        extract(month from sysdate)
from dual;

-- 현재일로부터 1년 2개월 3일 4시간 5분 6초 뒤를 나타내세요
select to_char(sysdate,  'yyyy-mm-dd hh24:mi:ss'),
        to_char(
        ad_months(sysdate, 1 * 12 + 2) + 3 + 4/24 + 5/(24*60) + 6/(24*60*60)
        , 'yyyy-mm-dd hh24:mi:ss')
from dual;

select to_char(
        sysdate + to_yminterval('01-02') -- 연월만 더해짐
        + to_dsinterval('003 04:05:06')   -- 일시분초 더해짐
        , 'yyyy-mm-dd hh24:mi:ss')
from dual;

-- *** case 문 *** --
-- case when then else end
-- 조건값과 비교값이 = 관계일 때
select case 5-2  when 2 then '5-2=2 입니다'
                  when 3 then '5-2=3 입니다'
                  else '나는 수학을 몰라요'
        end
from dual;

-- 조건값이 없고 비교값이 >, < 관계일 때, 참이나 거짓
select case when 10>20 then '10은 20보다 큽니다'
             when 10=20 then '10과 20은 같습니다'
             else '나는 수학을 몰라요'
        end
from dual;

-- 첫번째 케이스문과 동일
select decode(5-2, 2, '5-2=2 입니다',
                    3, '5-2=3 입니다',
                    '나는 수학을 몰라요')
from dual;

-- emp테이블에서 사원명과 성별을 출력하되 성별은 '남', '여'로 나타내세요.
select ename,
        case substr(jubun, 7, 1) when '1' then '남' when '3' then '남' else '여' end "성별1",
        decode( substr(jubun, 7, 1), '1', '남', '3', '남', '여') "성별2",
        case when substr(jubun, 7, 1) in ('1', '3') then '남' else '여' end "성별3"
from emp;

--  사원명, 주민번호, 성별, 현재나이를 보여주기

select ename "사원명", jubun "주민번호",
        decode( substr(jubun, 7, 1), '1', '남', '3', '남', '여') "성별",
        extract(year from sysdate)
         - to_number(case when substr(jubun, 7, 1) in ('3', '4') then '20' || substr(jubun, 1, 2) else '19' || substr(jubun, 1, 2) end) + 1 "현재나이"       
from emp;
/*
select   extract(year from sysdate)
         - to_number(case when substr(jubun, 7, 1) in ('3', '4') then '20' || substr(jubun, 1, 2) else '19' || substr(jubun, 1, 2) end) + 1 "현재나이"       
from emp;
*/

select ename "사원명", jubun "주민번호",
-- 현재 나이는 (현재 년도 - 태어난 년도 + 1)
       to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end "태어난년도",
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1 "현재나이",
      to_char(add_months(sysdate, (60-
             (
             extract(year from sysdate)
             - (to_number(substr(jubun, 1, 2))
             + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1
             )
            )
           *12), 'yyyy') "60세가되는년도"
from emp;

-- 60세가 되어지는 년도
select ename "사원명", jubun "주민번호",
        to_number(to_number(substr(jubun, 1, 2)) + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 60 "정년"
from emp;

-- 35세 25년 뒤 to_char(add_months(sysdate, (60-35)*12), 'yyyy')
-- 28세 32년 뒤 to_char(add_months(sysdate, (60-28)*12), 'yyyy')
-- to_char(add_months(sysdate, (60-현재나이)*12), 'yyyy')
-- to_char(add_months(sysdate,
--         (60
--            -(
--              (extract(year from sysdate)
--            - (to_number(substr(jubun, 1, 2))
--            + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1
--            )
--         *12), 'yyyy')
select

from emp;

-- sys에서 grant create view to scott;

create view view_emp
as
select ename "사원명", jubun "주민번호",
-- 현재 나이는 (현재 년도 - 태어난 년도 + 1)
       extract(year from sysdate) "현재년도",
       to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end "태어난년도",
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1 "현재나이"
from emp;

select *
from view_emp;

create view view_emp2
as
select ename "사원명", jubun "주민번호",
       case substr(jubun, 7, 1) when '1' then '남' when '3' then '남' else '여' end  "성별",
-- 현재 나이는 (현재 년도 - 태어난 년도 + 1)
       to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end "태어난년도",
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1 "현재나이"
from emp;

select *
from view_emp2;

-- sys에서 revoke create view from scott;

create view view_emp3
as
select empno, ename, sal
from emp;
--- error

-- *** inline view *** --
-- create view 권한이 없을 때 등 사용
select "T".*
from
(
select empno as "사원번호", ename, sal "기본급"
from emp
) "T"
-- as 생략가능
where "T".기본급 >= 3000

select "T".*,
         to_char(add_months(sysdate, (60-"T".현재나이)*12), 'yyyy') "60세가되는년도"
from
(
select ename "사원명", jubun "주민번호",
       to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end "태어난년도",
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1 "현재나이"
from emp
) "T"

-- *** 순위함수 ***
select ename "사원명", sal "기본급", deptno "부서번호"
from emp
order by ename asc;
-- 영어 > 한글, 대문자 > 소문자

select ename "사원명", sal "기본급", deptno "부서번호"
from emp
order by "사원명" asc;
-- asc 생략가능, 디폴트

-- 칼럼 앨리아스로도 가능

select ename "사원명", sal "기본급", deptno "부서번호"
from emp
order by 1;

-- 칼럼 순서로도 가능

select ename "사원명", sal "기본급", deptno "부서번호"
from emp
order by 2 desc;

select ename "사원명", sal "기본급", deptno "부서번호"
from emp
order by 3, 2 desc;

select ename "사원명", sal "기본급", deptno "부서번호",
        rank() over(partition by deptno order by sal desc) "동일부서내등수",
        rank() over(order by sal desc) "전체등수"
from emp
order by 3;

-- emp 테이블에서 기본급여가 제일 많은 순서대로 1등부터 5등까지만 추출하세요
select "T".*
from
(
select ename "사원명", sal "기본급",
        rank() over(order by sal desc) "전체등수"
from emp
) "T"
where "T".전체등수 <= 5
-- rank()는 where 절에서 사용불가, 따라서 인라인 뷰로

select ename, sal,
        rank() over(order by sal desc),
        dense_rank() over(order by sal desc)
from emp
-- dense_rank() 순위(partition by 사용할 수 있음), rank()는 석차

--
-- *** 그룹함수(집계함수) *** --
-- 그룹함수(집계함수)는 널 값을 무조건 제외하고 계산한다
select sum(sal)  -- 합계
from emp

select avg(sal)  -- 평균
from emp

select max(sal)  -- 최대치
from emp

select min(sal)  -- 최소치
from emp

select count(sal)  -- 행의 갯수
from emp

select sum(comm) -- null값이 있음
from emp

select avg(comm)  -- 평균
from emp

select max(comm)  -- 최대치
from emp

select min(comm)  -- 최소치
from emp

select count(comm)  -- 행의 갯수
from emp

select sum(sal)/count(sal), avg(sal)
from emp

select sum(comm)/count(comm), avg(comm) -- 널 값 제외하기 때문에, 전체 직원의 평균은 아님
from emp

select avg(nvl(comm, 0))  -- 널을 0으로 간주하고(nvl 함수 사용) 계산하면 전체 사원의 평균 구해짐
from emp

select count(sal), count(comm), count(nvl(comm, 0))  -- 해당 칼럼의 널값으로 인해 서로 다른 결과가 나옴
from emp

select count(*)  -- 어떤 테이블의 행의 갯수 참조하기 위해서 자주 사용됨
from emp

-- 부서별로 기본급의 함 조회하기

select deptno, sum(sal)
from emp
group by deptno

select job, sum(sal)
from emp
group by job

-- emp테이블에서 부서번호별 sal의 합을 구하되 그 합이 9000 이상인 것만
-- 부서번호, sal의 합을 나타내세요
select deptno, sum(sal)
from emp
group by deptno
having sum(sal) >= 9000
order by 1

-- where 조건절은 특정 행에 대해서만 메모리에 올리는 조건임

-- 부서번호, 기본급대여폭, 인원수
--   20        1600         2
--   10         800         3

--   20      1650-> 1600   
--   20      1603-> 1600
--   10       800->  800
--   10       890->  800
--   10       875->  800

select deptno, trunc(sal, -2), count(*)
from emp
group by deptno, trunc(sal, -2)
order by 1, 2
-- trunc(sal, -2) 백단위, trunc(sal, 2) 소수점 둘째자리

-- 연령대별 성별 인원수 구하기, 카페에 소스를 '과제'로 올리기

/*
        decode( substr(jubun, 7, 1), '1', '남', '3', '남', '여') "성별",
        extract(year from sysdate)
         - to_number(case when substr(jubun, 7, 1) in ('3', '4') then '20' || substr(jubun, 1, 2) else '19' || substr(jubun, 1, 2) end) + 1 "현재나이"       

*/
select "T".연령, "T".성별, count(*)
from
(
select trunc(
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1, -1) "연령",
        decode( substr(jubun, 7, 1), '1', '남', '3', '남', '여') "성별"
from emp
) "T"
group by 연령, 성별
order by 1, 2

/*
select jubun, trunc(
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1, -1) "연령",
        decode( substr(jubun, 7, 1), '1', '남', '3', '남', '여') "성별"
from emp
*/

-- emp 테이블에서 연령대별 인원수에 따른 비율을 추출하세요
-- <결과물>
-- 연령대   인원수   비율
--  0         2        14.3%
-- 10         2        14.3%
-- 20         4        28.6%
-- 30         3        21.4%
-- 40         2        14.3%
--100         1         7.1%

select "T".연령, count(*) "인원수", round((count(*)/(select count(*) from emp)) * 100, 1) || '%' "비율"
from
(
select trunc(
       extract(year from sysdate)
       - (to_number(substr(jubun, 1, 2))
       + case when substr(jubun, 7, 1) in ('1','2') then 1900 else 2000 end) + 1, -1) "연령"
from emp
) "T"
group by  "T".연령
order by 1

--

http://jm2y.tistory.com/133

출처: http://greatkim91.tistory.com/52

ORACLE - ABOUT ROWNUM & TIP(PAGING)

잡다한기록 2008/03/28 11:42

"올바른 성장과 따뜻한 나눔"이 있는 넥스트리

이번 호의 Ask Tom 컬럼은 지금까지와는 조금 다른 내용을 담고 있습니다. 필자는 오라클 데이터베이스에서 Top-N 쿼리와 페이지네이션(pagination) 쿼리를 구현하는 방법에 대해 자주 질문을 받곤 합니다. 하나의 컬럼을 통해 이러한 질문에 한꺼번에 대답하기 위한 방편으로, <어떤책>의 내용을 인용하기로 했습니다. 컬럼의 포맷에 맞게 책의 내용이 다소 수정되었음을 참고하시기 바랍니다.

Tom Kyte

결과 셋의 제한

ROWNUM은 오라클 데이터베이스가 제공하는 마술과도 같은 컬럼입니다. 이 때문에 많은 사용자들이 문제를 겪기도 합니다. 하지만 그 원리와 활용 방법을 이해한다면 매우 유용하게 사용할 수 있습니다. 필자는 주로 두 가지 목적으로 ROWNUM을 사용합니다.

• Top-N 프로세싱:
  이 기능은 다른 일부 데이터베이스가 제공하는 LIMIT 구문과 유사합니다.
• 쿼리 내에서의 페이지네이션(pagination):
  특히 웹과 같은 "stateless" 환경에서 자주 활용됩니다. 필자는 asktom.oracle.com 웹 사이트에서도 이 테크닉을 사용하고 있습니다.

두 가지 활용 방안을 설명하기 전에, 먼저 ROWNUM의 동작 원리에 대해 살펴 보기로 하겠습니다.

ROWNUM의 동작 원리

ROWNUM은 쿼리 내에서 사용 가능한 (실제 컬럼이 아닌) 가상 컬럼(pseudocolumn)입니다. ROWNUM에는 숫자 1, 2, 3, 4, ... N의 값이 할당됩니다. 여기서 N 은 ROWNUM과 함께 사용하는 로우의 수를 의미합니다. ROWNUM의 값은 로우에 영구적으로 할당되지 않습니다(이는 사람들이 많이 오해하는 부분이기도 합니다). 테이블의 로우는 숫자와 연계되어 참조될 수 없습니다. 따라서 테이블에서 "row 5"를 요청할 수 있는 방법은 없습니다. "row 5"라는 것은 존재하지 않기 때문입니다.

또 ROWNUM 값이 실제로 할당되는 방법에 대해서도 많은 사람들이 오해를 하고 있습니다. ROWNUM 값은 쿼리의 조건절이 처리되고 난 이후, 그리고 sort, aggregation이 수행되기 이전에 할당됩니다. 또 ROWNUM 값은 할당된 이후에만 증가(increment) 됩니다. 따라서 아래 쿼리는 로우를 반환하지 않습니다.

select * from t where ROWNUM > 1;

첫 번째 로우에 대해 ROWNUM > 1의 조건이 True가 아니기 때문에, ROWNUM은 2로 증가하지 않습니다. 아래와 같은 쿼리를 생각해 봅시다.

select ..., ROWNUM from t where group by having order by ;

이 쿼리는 다음과 같은 순서로 처리됩니다.

1. FROM/WHERE 절이 먼저 처리됩니다.
2. ROWNUM이 할당되고 FROM/WHERE 절에서 전달되는 각각의 출력 로우에 대해 증가(increment) 됩니다.
3. SELECT가 적용됩니다.
4. GROUP BY 조건이 적용됩니다.
5. HAVING이 적용됩니다.
6. ORDER BY 조건이 적용됩니다.

따라서 아래와 같은 쿼리는 에러가 발생할 수 밖에 없습니다.

select * from emp where ROWNUM <= 5 order by sal desc;

이 쿼리는 가장 높은 연봉을 받는 다섯 명의 직원을 조회하기 위한 Top-N 쿼리로 작성되었습니다. 하지만 실제로 쿼리는 5 개의 레코드를 랜덤하게(조회되는 순서대로) 반환하고 salary를 기준으로 정렬합니다. 이 쿼리를 위해서 사용되는 가상코드(pseudocode)가 아래와 같습니다.

ROWNUM = 1 for x in ( select * from emp ) loop exit when NOT(ROWNUM <= 5)
OUTPUT record to tempROWNUM = ROWNUM + 1 end loop SORT TEMP

위에서 볼 수 있듯 처음의 5 개 레코드를 가져 온후 바로 sorting이 수행됩니다. 쿼리에서 "WHERE ROWNUM = 5" 또는 "WHERE ROWNUM > 5"와 같은 조건은 의미가 없습니다. 이는 ROWNUM 값이 조건자(predicate) 실행 과정에서 로우에 할당되며, 로우가 WHERE 조건에 의해 처리된 이후에만 increment 되기 때문입니다.

올바르게 작성된 쿼리가 아래와 같습니다.

select * from ( select * from emp order by sal desc ) where ROWNUM <= 5;

위 쿼리는 salary를 기준으로 EMP를 내림차순으로 정렬한 후, 상위의 5 개 레코드(Top-5 레코드)를 반환합니다. 아래에서 다시 설명되겠지만, 오라클 데이터베이스가 실제로 전체 결과 셋을 정렬하지 않습니다. (오라클 데이터베이스는 좀 더 지능적인 방식으로 동작합니다.) 하지만 사용자가 얻는 결과는 동일합니다.

ROWNUM을 이용한 Top-N 쿼리 프로세싱 일반적으로 Top-N 쿼리를 실행하는 사용자는 다소 복잡한 쿼리를 실행하고, 그 결과를 정렬한 뒤 상위의 N 개 로우만을 반환하는 방식을 사용합니다. ROWNUM은 Top- N쿼리를 위해 최적화된 기능을 제공합니다. ROWNUM을 사용하면 대량의 결과 셋을 정렬하는 번거로운 과정을 피할 수 있습니다. 먼저 그 개념을 살펴보고 예제를 통해 설명하기로 하겠습니다.

아래와 같은 쿼리가 있다고 가정해 봅시다.

select ... from ... where ... order by columns;

또 이 쿼리가 반환하는 데이터가 수천 개, 수십만 개, 또는 그 이상에 달한다고 가정해 봅시다. 하지만 사용자가 실제로 관심 있는 것은 상위 N개(Top 10, Top 100)의 값입니다. 이 결과를 얻기 위한 방법에는 두 가지가 있습니다. 클라이언트 애플리케이션에서 쿼리를 실행하고 상위 N 개의 로우만을 가져오도록 명령 쿼리를 인라인 뷰(inline view)로 활용하고, ROWNUM을 이용하여 결과 셋을 제한 (예: SELECT * FROM (your_query_here) WHERE ROWNUM <= N) 두 번째 접근법은 첫 번째에 비해 월등한 장점을 제공합니다. 그 이유는 두 가지입니다.

첫 번째로, ROWNUM을 사용하면 클라이언트의 부담이 줄어듭니다. 데이터베이스에서 제한된 결과 값만을 전송하기 때문입니다.
두 번째로, 데이터베이스에서 최적화된 프로세싱 방법을 이용하여 Top N 로우를 산출할 수 있습니다. Top-N 쿼리를 실행함으로써, 사용자는 데이터베이스에 추가적인 정보를 전달하게 됩니다. 그 정보란 바로 "나는N 개의 로우에만 관심이 있고, 나머지에 대해서는 관심이 없다"는 메시지입니다. 이제, 정렬(sorting) 작업이 데이터베이스 서버에서 어떤 원리로 실행되는지 설명을 듣고 나면 그 의미를 이해하실 수 있을 것입니다.

샘플 쿼리에 위에서 설명한 두 가지 접근법을 적용해 보기로 합시다.

select * from t order by unindexed_column;

여기서 T가 1백만 개 이상의 레코드를 저장한 큰 테이블이라고, 그리고 각각의 레코드가 100 바이트 이상으로 구성되어 있다고 가정해 봅시다. 그리고 UNINDEXED_COLUMN은 인덱스가 적용되지 않은 컬럼이라고, 또 사용자는 상위 10 개의 로우에만 관심이 있다고 가정하겠습니다. 오라클 데이터베이스는 아래와 같은 순서로 쿼리를 처리합니다.
1. T에 대해 풀 테이블 스캔을 실행합니다.
2. UNINDEXED_COLUMN을 기준으로 T를 정렬합니다. 이 작업은 "full sort"로 진행됩니다.
3. Sort 영역의 메모리가 부족한 경우 임시 익스텐트를 디스크에 스왑하는 작업이 수행됩니다.
4. 임시 익스텐트를 병합하여 상위 10 개의 레코드를 확인합니다.
5. 쿼리가 종료되면 임시 익스텐트에 대한 클린업 작업을 수행합니다.

결과적으로 매우 많은 I/O 작업이 발생합니다. 오라클 데이터베이스가 상위 10 개의 로우를 얻기 위해 전체 테이블을 TEMP 영역으로 복사했을 가능성이 높습니다. 그럼 다음으로, Top-N 쿼리를 오라클 데이터베이스가 개념적으로 어떻게 처리할 수 있는지 살펴 보기로 합시다.

select * from (select * from t order by unindexed_column) where ROWNUM < :N;

오라클 데이터베이스가 위 쿼리를 처리하는 방법이 아래와 같습니다.
1. 앞에서와 마찬가지로 T에 대해 풀-테이블 스캔을 수행합니다(이 과정은 피할 수 없습니다).
2. :N 엘리먼트의 어레이(이 어레이는 메모리에 저장되어 있을 가능성이 높습니다)에서 :N 로우만을 정렬합니다. 상위N 개의 로우는 이 어레이에 정렬된 순서로 입력됩니다. N +1 로우를 가져온 경우, 이 로우를 어레이의 마지막 로우와 비교합니다. 이 로우가 어레이의 N +1 슬롯에 들어가야 하는 것으로 판명되는 경우, 로우는 버려집니다. 그렇지 않은 경우, 로우를 어레이에 추가하여 정렬한 후 기존 로우 중 하나를 삭제합니다. Sort 영역에는 최대 N 개의 로우만이 저장되며, 따라서 1 백만 개의 로우를 정렬하는 대신N 개의 로우만을 정렬하면 됩니다. 이처럼 간단한 개념(어레이의 활용, N개 로우의 정렬)을 이용하여 성능 및 리소스 활용도 면에서 큰 이익을 볼 수 있습니다. (TEMP 공간을 사용하지 않아도 된다는 것을 차치하더라도) 1 백만 개의 로우를 정렬하는 것보다 10 개의 로우를 정렬하는 것이 메모리를 덜 먹는다는 것은 당연합니다. 아래의 테이블 T를 이용하면, 두 가지 접근법이 모두 동일한 결과를 제공하지만 사용되는 리소스는 극적인 차이를 보임을 확인할 수 있습니다.

create table tas
select dbms_random.value(1,1000000) id, rpad('*',40,'*' ) data
from dual connect by level <= 100000;
begin dbms_stats.gather_table_stats(user, 'T');
end;
/

Now enable tracing, viaexec dbms_monitor.session_trace_enable(waits=>true);
And then run your top-N query with ROWNUM:

select * from (select * from t order by id) where rownum <= 10;

마지막으로 상위 10 개의 레코드만을 반환하는 쿼리를 실행합니다.

declare cursor c is select * from t order by id;
l_rec c%rowtype;
begin open c;
for i in 1 .. 10 loop fetch c into l_rec;
exit when c%notfound; end loop;
close c;
end;
/

이 쿼리를 실행한 후, TKPROF를 사용해서 트레이스 결과를 확인할 수 있습니다. 먼저 Top-N 쿼리 수행 후 확인한 트레이스 결과가 Listing 1과 같습니다.
Code Listing 1: ROWNUM을 이용한 Top-N 쿼리

select * from(select * from t order by id) where rownum <= 10
call count cpu elapsed disk query current rows
-------- -------- ------- ------- ------- -------- -------- ------
Parse 1 0.00 0.00 0 0 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 2 0.04 0.04 0 949 0 10
-------- -------- ------- ------- ------- -------- -------- ------
total 4 0.04 0.04 0 949 0 10Rows Row Source Operation
----------------- ---------------------------------------------------
10 COUNT STOPKEY (cr=949 pr=0 pw=0 time=46997 us)
10 VIEW (cr=949 pr=0 pw=0 time=46979 us)
10 SORT ORDER BY STOPKEY (cr=949 pr=0 pw=0 time=46961 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400066 us)

이 쿼리는 전체 테이블을 읽어 들인 후, SORT ORDER BY STOPKEY 단계를 이용해서 임시 공간에서 사용되는 로우를 10 개로 제한하고 있습니다. 마지막 Row Source Operation 라인을 주목하시기 바랍니다. 쿼리가 949 번의 논리적 I/O를 수행했으며(cr=949), 물리적 읽기/쓰기는 전혀 발생하지 않았고(pr=0, pw=0), 불과 400066 백만 분의 일초 (0.04 초) 밖에 걸리지 않았습니다. 이 결과를 Listing 2의 실행 결과와 비교해 보시기 바랍니다.

Code Listing 2: ROWNUM을 사용하지 않은 쿼리

SELECT * FROM T ORDER BY ID
call count cpu elapsed disk query current rows
-------- -------- ------- ------- ------- -------- -------- ------
Parse 1 0.00 0.00 0 0 0 0Execute 2 0.00 0.00 0 0 0 0Fetch 10 0.35 0.40 155 949 6 10
-------- -------- ------- ------- ------- -------- -------- ------
total 13 0.36 0.40 155 949 6 10Rows Row Source Operation
----------------- ---------------------------------------------------
10 SORT ORDER BY (cr=949 pr=155 pw=891 time=401610 us)
100000 TABLE ACCESS FULL T (cr=949 pr=0 pw=0 time=400060 us)
Elapsed times include waiting for the following events:Event waited on Times
------------------------------ ------------
direct path write temp 33direct path read temp 5

결과가 완전히 다른 것을 확인하실 수 있습니다. "elapsed/CPU time"이 크게 증가했으며, 마지막 Row Source Operation 라인을 보면 그 이유를 이해할 수 있습니다. 정렬 작업은 디스크 상에서 수행되었으며, 물리적 쓰기(physical write) 작업이 "pw=891"회 발생했습니다. 또 다이렉트 경로를 통한 읽기/쓰기 작업이 발생했습니다. (10 개가 아닌) 100,000 개의 레코드가 디스크 상에서 정렬되었으며, 이로 인해 쿼리의 실행 시간과 런타임 리소스가 급증하였습니다.

ROWNUM을 이용한 페이지네이션 필자가 ROWNUM을 가장 즐겨 사용하는 대상이 바로 페이지네이션(pagination)입니다. 필자는 결과 셋의 로우 N 에서 로우 M까지를 가져오기 위해 ROWNUM을 사용합니다. 쿼리의 일반적인 형식이 아래와 같습니다.

select * from (
select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from (
your_query_goes_here, with order by
) a where ROWNUM <= :MAX_ROW_TO_FETCH
)where rnum >= :MIN_ROW_TO_FETCH;

여기서, FIRST_ROWS(N)는 옵티마이저에게 "나는 앞부분의 로우에만 관심이 있고, 그 중 N 개를 최대한 빨리 가져오기를 원한다"는 메시지를 전달하는 의미를 갖습니다.
:MAX_ROW_TO_FETCH는 결과 셋에서 가져올 마지막 로우로 설정됩니다. 결과 셋에서 50 번째 ~ 60 번째 로우만을 가져오려 한다면 이 값은 60이 됩니다.
:MIN_ROW_TO_FETCH는 결과 셋에서 가져올 첫 번째 로우로 설정됩니다. 결과 셋에서 50 번째 ~ 60 번째 로우만을 가져오려 한다면 이 값은 50이 됩니다.

이 시나리오는 웹 브라우저를 통해 접속한 사용자가 검색을 마치고 그 결과를 기다리고 있는 상황을 가정하고 있습니다. 따라서 첫 번째 결과 페이지(그리고 이어서 두 번째, 세 번째 결과 페이지)를 최대한 빨리 반환해야 할 것입니다. 쿼리를 자세히 살펴 보면, (처음의 :MAX_ROW_TO_FETCH 로우를 반환하는) Top-N 쿼리가 사용되고 있으며, 따라서 위에서 설명한 최적화된 기능을 이용할 수 있음을 알 수 있습니다. 또 네트워크를 통해 클라이언트가 관심을 갖는 로우만을 반환하며, 조회 대상이 아닌 로우는 네트워크로 전송되지 않습니다.

페이지네이션 쿼리를 사용할 때 주의할 점이 하나 있습니다. ORDER BY 구문은 유니크한 컬럼을 대상으로 적용되어야 합니다. 유니크하지 않은 컬럼 값을 대상으로 정렬을 수행해야 한다면 ORDER BY 조건에 별도의 조건을 추가해 주어야 합니다. 예를 들어 SALARY를 기준으로 100 개의 레코드를 정렬하는 상황에서 100 개의 레코드가 모두 동일한 SALARY 값을 갖는다면, 로우의 수를 20-25 개로 제한하는 것은 의미가 없을 것입니다. 여러 개의 중복된 ID 값을 갖는 작은 테이블을 예로 들어 설명해 보겠습니다.

create table t 2 as 3
select mod(level,5) id, trunc(dbms_random.value(1,100)) data 4
from dual 5 connect by level <= 10000;
Table created.

ID 컬럼을 정렬한 후 148-150 번째 로우, 그리고 148-151 번째 로우를 쿼리해 보겠습니다.

select * from (
select a.*, rownum rnum from (
select id, data from t order by id
) a where rownum <= 150
) where rnum >= 148;

ID DATA RNUM
------- ---------- -----------
0 38 148
0 64 149
0 53 150

select * from (
select a.*, rownum rnum from (
select id, data from t order by id
) a where rownum <= 151
) where rnum >= 148;

ID DATA RNUM
------- ---------- -----------
0 59 148
0 38 149
0 64 150
0 53 151

로우 148의 경우 DATA=38의 결과가 반환되었습니다. 두 번째 쿼리에서는 DATA=59의 결과가 반환되었습니다. 두 가지 쿼리 모두 올바른 결과를 반환하고 있습니다. 쿼리는 데이터를 ID 기준으로 정렬한 후 앞부분의 147 개 로우를 버린 후 그 다음의 3 개 또는 4 개의 로우를 반환합니다. 하지만 ID에 중복값이 너무 많기 때문에, 쿼리는 항상 동일한 결과를 반환함을 보장할 수 없습니다. 이 문제를 해결하려면 ORDER BY 조건에 유니크한 값을 추가해 주어야 합니다. 위의 경우에는 ROWID를 사용하면 됩니다.

select * from (
select a.*, rownum rnum from (
select id, data from t order by id, rowid
) a where rownum <= 150
) where rnum >= 148;

ID DATA RNUM
------- ---------- -----------
0 45 148
0 99 149
0 41 150

select * from (
select a.*, rownum rnum from (
select id, data from t order by id, rowid
) a where rownum <= 151
) where rnum >= 148;

ID DATA RNUM
------- ---------- -----------
0 45 148
0 99 149
0 41 150
0 45 151

이제 쿼리를 반복 실행해도 동일한 결과를 보장할 수 있게 되었습니다. ROWID는 테이블 내에서 유니크한 값을 가집니다. 따라서 ORDER BY ID 조건과 ORDER BY ROWID 기준을 함께 사용함으로써 사용자가 기대한 순서대로 페이지네이션 쿼리의 결과를 확인할 수 있습니다.

ROWNUM 개념 정리

지금까지 ROWNUM에 관련하여 아래와 같은 개념을 설명하였습니다.

• ROWNUM의 할당 원리와 잘못된 쿼리 작성을 피하는 방법
• ROWNUM이 쿼리 프로세싱에 미치는 영향과 웹 환경의 페이지네이션을 위한 활용 방안
• ROWNUM을 이용하여 Top N쿼리로 인한 TEMP 공간의 사용을 피하고 쿼리 응답 속도를 개선하는 방법

출처명 : 한국오라클 (http://stillrabbit.blogspot.com/2008/01/rownum-2007-8-27-14100-ask-tom.html)
작성자: StillRabbit


* 공개에 문제가 있다면 알려주시기 바랍니다. 트랙백을 걸 수 없어 퍼다 올립니다.

출처: http://jhbench.tistory.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-%EA%B0%9D%EC%B2%B41-%EC%9D%B8%EB%8D%B1%EC%8A%A4Index

출처: http://www.zetswing.com/bbs/board.php?bo_table=ORACLE_TIP&wr_id=21

PL/SQL (ProcedureLanguage/SQL) 정의

Oracle 에서 스토어드 프로시져를 개발할때 사용하는 언어(문법)입니다.

PL/SQL은 ANSI SQL 보다 확장된 SQL로써 좀더 다양한 처리를 구현할수 있습니다.

* PL/SQL과 스토어드 프로시져는 같은 의미로 해석함

스토어드 프로시져 사용목적

일반 SQL 실행

흐름 : 클라이언트에서 서버로 단일 쿼리를 날려 실행함

단점 : 여러개의 SQL를 실행시 실행할 SQL 갯수만큼 실행

스토어드 프로시져 SQL 실행

흐름 : 서버에 스토어드 프로시져를 먼저 생성후 클라이언트에서 호출하여 실행함

장점 : 여러개의 SQL을 실행시 하나의 스토어드 프로시져에 담아 컴파일하여 서버에 생성한후 한번만 호출하여 실행

※ 스토어드 프로시져 생성후 커밋처리를 해줘야 한다.

※ 스토어드 프로시져 생성시 정적쿼리에서 사용하는 테이블, 컬럼이 해당 DB에 존재하지 않으면 에러를 발생한다. 단 동적쿼리의 테이블과 컬럼은 체크하지 않는다.

※ 스토어드 프로시져 생성시 다른 스토어드 프로시져를 호출하는 명령이 있을때 해당 스토어드 프로시져가 현재 존재안하면 에러를 발생한다.

스토어드 프로시져 사용이점

1. 프로그램 의존성이 낮고 독립성이 높다.

PRO-C, SQC, 쉘스크립트에서 스토어드 프로시져 수정시 스토어드 프로시져만 컴파일하면 된다. PRO-C, SQC, 쉘스크립트 파일은 재컴파일을 안해도 된다.

오라클 스토어드 프로시저로 할수없는 SQL

DDL(CREATE TABLE, DROP TABLE, ALTER TABLE)

PL/SQL 사용의 장점?

변수를 선언하고 사용할수 있으며 조건문을 사용할수 있다.

예외처리도 가능하며 네트웍트랙픽도 감소시켜주며 프로그래개발을 모듈화할수 있다.

트랜잭션 로직으로 개발가능하다.

서버에 이미 저장되어있으므로 실행속도가 빠르다.

서버에 저장되어 있으므로 보안에 좋다.

PL/SQL 블록 구조

※ PL/SQL 블럭 내부에는 SQL명령문과 PL/SQL명령문이 포함되어있다.

DECLARE

--변수,상수 선언

BEGIN

--실행 가능 SQL문,PL/SQL문

EXCEPTION

--에러처리

END;

※ BEGIN과 END SECTION은 꼭 필수 부이다.

※ SP는 컴파일하기전에 사용된 컬럼과 테이블이 있는지 체크한후에 컴파일을

시작하며 존재하지 않는 컬럼,테이블이 있다면 에러를 발생한다.

PL/SQL 블록에서 사용못하는 SQL문

CREATE,GRANT

※ SELECT,UPDATE,INSERT,DELETE는 사용가능하다.

PL/SQL 에서 여러행을 리턴하는 방법

여러개의 리턴값을 넘길때 "var1 || '/' || var1" 와 같이 구분자를 넣어서 하나로 넘겨서 사용하면 됩니다.

사용예제

CREATE OR REPLACE FUNCTION FUN_EX

(

IN_VAR1 VARCHAR2,

IN_VAR2 VARCHAR2,

IN_VAR2 VARCHAR2) RETURN NUMBER IS

RTN_VAR VARCHAR2(100);

INTO1 VARCHAR2(10);

INTO2 VARCHAR2(10);

INTO3 VARCHAR2(10);

BEGIN

SELECT VAR1, VAR2, VAR3

INTO INTO1, INTO2, INTO3

FROM 테스트

WHERE 조건하나

AND 조건둘

AND.. .;

RTN_VAR := INTO1 || '/' || INTO2 || '/' || INTO3;

-- 구분자는 편의대로 정하시면 됩니다.

RETURN RTN_VAR;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RETURN 0; -- check

WHEN OTHERS THEN

RETURN 0; -- check

END FUN_EX;

PL/SQL 데이타타입 종류

v_hire date;

v_deptno number(2) not null = 0;
/* not null이 붙으면 꼭 초기화를 해야한다. */

v_location varchar2(13) := 'allanta';
/* not null이 없으면 꼭 초기화를 하지 않아도 된다. */

/* 가변길이 문자열 */

v_name char(4) := 'lee';

/* 고정길이 문자열 */

v_address long;

/* 32760 바이트를 저장할수는 문자열 */

v_true boolean;

/* 진리값을 저장한다. */

c_com constant number :=1400;

/* constant는 상이다. */

v_age emp.age%TYPE;

v_age2 v_age%TYPE;
/* %TYPE을 쓰는이유는 TABLE의 컬럼의 데이타형이 달라지거나 제어할 TABLE */
/* 컬럼과 같게하기위해 매번 확인하야하는 번거로움을 없애기 위해 */

/* 큰시스템에서는 프로시저형이 달라지면 다바꾸어야 하기때문에 테이블의 형을 참조

/* 해서 사용하면 테이블구조가 바뀌어도 자동으로 바뀌게 좋다. 대신 제대로 형을 */

/* 참조해야 한다.*/

v_loc loc /* 구조화되지않은 큰데이타 저장 4기가바이트까지 저장 */

PL/SQL 블록 유형

Anonymouse 유형(매번 서버에 전송하여 컴파일하여 실행함)

[declare]

begin

--실행소스

[exception]

end;

Procedure 유형(초이 한번만 서버에 생성하여 컴파일하여 호출하여 실행함)

CREATE OR REPLACE Procedure name

is

begin

--실행소스

[exception]

end;

Function 유형(초이 한번만 서버에 생성하여 컴파일하여 호출하여 실행함)

CREATE OR REPLACE Function name

Return datatype

is

begin

[exception]

end;

Anonymouse PL/SQL 블록 유형

클라이언트에서 매번 PL/SQL블록을 만들어 서버에 전송하여 자동으로 컴파일되어 실행하는 방식으로 여러 SQL문을 하나의 PL/SQL블록으로 만들어 보내면 한번에 여러 SQL문실행이 가능하지만 매번 서버에서 컴파일되기때문에 PROCEDURE,FUNCTION유형보다 성능이 좋지 않다.

Anonymouse PL/SQL 사용예제

create table test
(
a number,
b date
);

select * from test;

/* select문에서 INTO절사용시 1개의 행(로우)만이 into절에 변수에 저장가능하다. */
Declare
/* 2개의 선언 */
a account.a_strday%TYPE;
b account.a_in%TYPE;
begin
/* a_strday,a_in의 값을 a,b에 넣어라 */
select a_strday,a_in
into a,b
from account
where a_type = '뮤직';
end;
/* select문에서 INTO절사용시 1개의 행(로우)만이 into절에 변수에 저장가능하다. */

/* 1개의 행의 컬럼값을 v_empno에 저장한후 test테이블에 대입한후 commit한다. */
Declare
v_empno number;
v_date date := sysdate;
begin
select 1
into v_empno
from dual;
insert into test (a,b) values (v_empno,v_date);
commit;
end;
/* 1개의 행의 컬럼값을 v_empno에 저장한후 test테이블에 대입한후 commit한다. */

/* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */
Declare
v_sale number := 2000;
begin
update test set a = v_sale;
delete from test where a = v_sale;
commit;
end;
/* PL/SQL UPDATE예제(UPDATE,DELETE는 다중행처리 가능) */

스토어드 프로시저 PL/SQL 블록 유형?

스토어드 프로시저란 어떤 업무를 수행 하기 위한 절차를 뜻하며 반복되는 코딩을

모듈화 하기 위하여 함수나 프로시져를 사용합니다.

보통 DB에서는 데이타를 조회하여 가져오고 그 데이타를 받는 언어쪽에서는 데이타를

가공하여 화면에 출력을 합니다. 하지만 DB에서 데이타조회와 가공까지 하면 좀더 빠르게

화면 출력이 되며 서버부하를 줄일수 있습니다.

ORACLE의 PROCEDURE을 이용하면 서버부하를 줄이며 좀더 빠른 화면출력이 되며 여러

쿼리들을 하나의 프로시저,함수로 만들어 한번에 실행시킬수 있습니다.

예를 들어 인터넷을 통해 극장표를 구매하는 프로시저로 표현 한다면

[극장표 구매 Procedure 시작 ]

1. 예매 사이트에 접속 한다.
2. 예매할 영화 선택 한다.
3. 예매 일자와 시간을 선택 한다.
4. 예매 매수를 선택 한다.
5. 위 과정이 모두 끝났으면 결재를 한다.

[극장표 구매 Procedure 종료 ]

위와 같이 어떤 프로세스 절차를 기술해 놓을것을 프러시저 라고 합니다.

Procedure에서 Procedure를 호출하는 방법

A프로시져에서 "프로시져명(변수, 변수2);" 명령으로 B프로시져를 호출할수 있다.

PROCEDURE and FUNCTION 의 차이점?

프로시저와 펀션 둘다 파라미터를 받고 리턴값을 지정할수있으나 가장 큰 차이점은

function은 반드시 리턴값을 하나만 가져야 하지만 procedure는 여러개의 리턴값을 가질수 있습니다.

생성된 PROCEDURE & FUNCTION 리스트보기

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE';

SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION';

PROCEDURE & FUNCTION 삭제하기

DROP FUNCTION lee2;

DROP PROCEDURE lee2;

※ 삭제시 아래 메세지가 출력되는 경우

ORA-04021: 객체 KMS.KM_GET_KNOWLEDGE_LIST_PAGE의 잠금 대기중 시간이

초과됐습니다.

library lock 이 발생하여 해당 procedure 를 사용중이거나, 머 library lock 이 발생하면

해당 package, procedure, function 에 대한 작업을 할수 없습니다.

lock 관련 체크 script 를 통해서 체크 해보세요.

생성된 Procedure & Function의 내용보기

select * from user_source;

select * from user_source where name='DATETOSTRDAY';

또는

SELECT * FROM ALL_SOURCE WHERE OWNER = '소유자' AND NAME = '프로시져명'

※ MSSQL, MySQL, DB2도 특정 테이블에 프로시져와 펑션의 내용이 들어있습니다.

PROCEDURE & FUNCTION 실행하기

EXECUTE lee;

--PROCEDURE 실행하기EXEC lee;

--PROCEDURE 실행하기 예제 select function_name(column) from table_name;

PROCEDURE & FUNCTION 기본 문법

create or replace procedure lee
--파라미터 선언부

is
--변수 선언부
begin
--실행부(쿼리적용)
end lee;

create procedure lee
--파라미터 선언부

is
--변수 선언부
begin
--실행부(쿼리적용)
end lee;

LOOP문 :LOOP와 END LOOP사이의 문장을 반복 수행하며 BREAK문으로 빠져나감

WHILE문 : 제어 조건이 TRUE가 아닐 때까지 문장을 반복수행

PROCEDURE 예제

※ 주의할점

1) END; 와 END 프로시저&함수명; 은 같다. 보통 안쓰는게 편합니다.

2) CREATE OR replace PROCEDURE LEE 와 CREATE PROCEDURE LEE의 차이는 전자는 같은 프로시저 이름으로 계속 생성해도 새롭게 내용이 갱신되고 후자는 같은 프로시저명이 있으면 에러를 내고 실행을 멈춥니다.

3)프로시저나 함수 생성시에 에러가 나면 sqlplus에서 show error을 쳐서 에러내용을 확인합니다.

숫자를 받은후 UPDATE쿼리를 합니다.

CREATE OR REPLACE PROCEDURE LEE1
(v_empno IN NUMBER) /* 정수형 파라미터를 v_empno변수로 받는다. */
IS
BEGIN
UPDATE emp SET sal = sal * 1.1
WHERE empno = v_empno;/* query runtime */
COMMIT; /* update query commit */
END LEE1;
/
execute lee1(7369);

숫자를 받은후 UPDATE쿼리를 합니다.

CREATE OR REPLACE PROCEDURE LEE2
(v_empno in emp.sal%type)

/* v_empno변수에 파라미터로 받고 v_empno type은 emp테이블의 sal컬럼과 같은 타입이다. */
IS
BEGIN
UPDATE emp SET sal = sal * 1.1
WHERE empno = v_empno;/* query runtime */
COMMIT; /* update query commit */
END LEE2;
/

execute lee1(7369);

list 테이블에서 사번을 입력받아 COMMISSION_PCT 값을 수정하시오.

COMMISSION_PCT 는 영업사원이 담당한
주문(s_ord) 총금액(s_ord.total)으로 결정한다.
update_comm 라는 procedure 작성하시오.
a. 총금액이 100,000 미만이면 COMMISSION_PCT는 10
b. 총금액이 100,000 이상이고 1,000,000 미만이면 15
c. 총금액이 1,000,000 이상이면 20
d. NULL이면 0

create table list
(
id number,
money number,
comm number
);

create or replace procedure update_comm
(a_id in list.id%type)
is
v_total list.money%type;
v_comm list.comm%type;
begin
select sum(money)
into v_total
from list
where id = a_id;
if v_total < 100000 then
v_comm := 10;
elsif v_total < 1000000 then
v_comm := 15;
elsif v_total >= 1000000 then
v_comm := 20;
else
v_comm := null;
end if;
dbms_output.put_line('사번 : '||to_char(a_id));
dbms_output.put_line('TOTAL : '||to_char(v_total, '999,999,999'));
dbms_output.put_line('커미션 : '||to_char(v_comm,'999.99'));
update list
set comm = v_comm
where id = a_id;
commit;
end;
/

execute update_comm(1);

FUNCTION 예제

사원번호를 입력받아 사원의 월급을 출력합니다.

CREATE OR REPLACE FUNCTION lee3
(id IN NUMBER)
return number
IS
value1 NUMBER;

BEGIN
select sal into value1 from emp where empno=id;
return(value1);
END;
/

select lee3(empno) from emp;

부서번호을 입력받아 부서명을 return 해주는 함수를 만들자.

함수명 : f_deptname
인자 : id(s_dept.id%type)
리턴값 : varchar2

사원정보에 대해서
사번(id), 이름(last_name), 부서번호, 부서명을 조회하자.

create or replace function f_deptname
(a_id in dept.deptno%type)
return varchar2
is
v_dname dept.dname%type;
begin
select dname
into v_dname
from dept
where deptno= a_id;
return(v_dname);
end;
/
select f_deptname(deptno) from dept;

S_EMP 테이블에서 사번을 입력받아 해당 사원의 급여에 따른
세금을 구하시오.
급여가 1000 미만이면 급여의 5%, 급여가 2000 미만이면 7%,
급여가 3000 미만이면 9%, 그 이상은 12%로 세금을 정한다.

create or replace function f_tax

/*
정수형 파라미터 설정
(id in emp.empno%type)
*/
(id in number)
return number

/* 변수선언 */
is
v_sal NUMBER;
/* v_sal s_emp.salary%type; */
v_tax NUMBER;

/* 함수 실행문 */
begin
select sal
into v_sal
from emp
where empno = id;

if v_sal < 1000 then
v_tax := v_sal * 0.05;
elsif v_sal < 2000 then
v_tax := v_sal * 0.07;
elsif v_sal < 3000 then
v_tax := v_sal * 0.09;
else
v_tax := v_sal * 0.12;
end if;
return(v_tax);

end;
/
select f_tax(empno) from emp;

/*
함수명:숫자형식요일리턴함수
함수설명:날짜를 입력받아 요일을 숫자로 리턴하는 함수
함수작성자:이 준식
*/

create or replace FUNCTION DateToNumDay(DateValue IN date)/*받는 변수*/
return number/*리턴 데이타형*/
IS
ShowDay number;/*숫자변수 선언*/
BEGIN
/**********************************/
SELECT decode(to_char(DateValue,'D'),
'2','1',
'3','2',
'4','3',
'5','4',
'6','5',
'7','6',
'1','7') "요일숫자" into ShowDay
from dual;
/**********************************/
return(ShowDay);/*해당 변수를 리턴*/

end;

/

/*
함수명:문자형식요일리턴함수
함수설명:숫자요일을 입력받아 문자로 리턴하는 함수
함수작성자:이 준식
*/

create or replace FUNCTION NumdayToStrDay(NumValue IN number)/*받는 변수*/
return varchar2/*리턴 데이타형*/
IS
StrValue varchar2(10);/*문자변수 선언*/
BEGIN
/**********************************/
SELECT decode((NumValue),
1,'월요일',
2,'화요일',
3,'수요일',
4,'목요일',
5,'금요일',
6,'토요일',
7,'일요일') "요일" into StrValue
from dual;
/**********************************/
return(StrValue);/*해당 변수를 리턴*/

end;
/

/*
함수명:문자형식요일리턴함수
함수설명:날짜를 입력받아 요일을 문자로 리턴하는 함수
함수작성자:이 준식
*/

create or replace FUNCTION DateToStrDay(DateValue IN date)/*받는 변수*/
return varchar/*리턴 데이타형*/
IS
ShowDay varchar(10);/*숫자변수 선언*/
BEGIN
/**********************************/
SELECT decode(to_char(DateValue,'D'),
'1','일요일',
'2','월요일',
'3','화요일',
'4','수요일',
'5','목요일',
'6','금요일',
'7','토요일') "요일" into ShowDay
from dual;
/**********************************/
return(ShowDay);/*해당 변수를 리턴*/

end;

/

/*
함수명:숫자형식콤마문자리턴함수
함수설명:숫자를 입력받아 콤마처리를 문자로 리턴하는 함수
함수작성자:이 준식
*/

create or replace FUNCTION IntToComma(IntValue IN number)/*받는 변수*/
return varchar/*리턴 데이타형*/
IS
ShowDay varchar(20);/*문자변수 선언*/
BEGIN
/**********************************/

SELECT replace(to_char(IntValue,'999,999,999,999'),' ','') into ShowDay

from dual;
/*SELECT trim(to_char(IntValue,'999,999,999,999')) into ShowDay*/

/**********************************/
return(ShowDay);/*해당 변수를 리턴*/
end;
/

/*
함수명:문자형식 숫자,문자,null 체크 출력
함수설명:문자형식을 숫자인지 문자와 숫자인지 null인지 알려준다.
함수작성자:이 준식
*/

create or replace function NumCheck(asString varchar2)
return varchar2
is
nTemp number(1);
begin
select sign(asString)
into nTemp
from dual;

if nTemp is null then
return 'null';
end if;

return 'number';
exception
when others then
return 'string';
end;
/

create or replace function test_01(param1 IN date,param2 IN VARCHAR2)
return varchar2
is
ls_code varchar2(20);
BEGIN
SELECT decode(param2,'a_type',a_type) INTO ls_code from ACCOUNT where A_DATE = param1;
return ls_code;
/* 정상적으로 실행되었으나 조회갯수가 0개면 발생 */
exception when no_data_found then
return '데이타가 없습니다.';
/* return null; */
END test_01;
/

select test_01(sysdate) from dual;
select test_01(to_date('2005-10-18','yyyy-mm-dd'),'a_type') from dual;

create or replace function test_01(param1 IN varchar2)
return varchar2
is
ls_code varchar2(20);
BEGIN
SELECT A_TYPE INTO ls_code from ACCOUNT where A_DATE = to_date(param1,'yyyy-mm-dd');
return ls_code;
/* 정상적으로 실행되었으나 조회갯수가 0개면 발생 */
exception when no_data_found then
return '데이타가 없습니다.';
/* return null; */
END test_01;
/

select test_01(sysdate) from dual;
select test_01('2005-10-18') from dual;

프로시져는 SQL문에서 호출할수 없지만 함수는 SQL문에서 호출할수 있다.

PL/SQC 자동실행 방법

윈도우의 경우

보조프로그램 > 시스템도구 > 예약된 작업에서 .bat 파일을 만든후 등록함

유닉스의 경우

쉘스크립트 파일을 만든후 크론에 등록하거나 nohup으로 실행

오라클의 기능을 이용한 경우

dbms_job 패키지를 이용한다.