다물8

이런 기능이 있군요..

http://niflheim.tistory.com/52

SELECT * FROM employees AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';

예제: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm#sthref1460

IP 변경하기:

예 :12.154.28.10==> 012.154.028.010

select ip_addr,

(ltrim(to_char(to_number(substr(ip_addr,1,instr(ip_addr,'.',1)-1)),'000'))||'.'||
ltrim(to_char(to_number(substr(ip_addr,instr(ip_addr,'.',1)+1,instr(ip_addr,'.',instr(ip_addr,'.',1)+1) - (instr(ip_addr,'.',1)+1))),'000')) ||'.'||
ltrim(to_char(to_number(substr(ip_addr,instr(ip_addr,'.',instr(ip_addr,'.',1)+1)+1, instr(ip_addr,'.',-1) - (instr(ip_addr,'.',instr(ip_addr,'.',1)+1)+1))),'000')) ||'.'||
ltrim(to_char(to_number(substr(ip_addr,instr(ip_addr,'.',-1)+1)),'000'))) as new_addr

from dual;

ORA-01722: 수치가 부적합합니다

CREATE OR REPLACE FUNCTION isNumber(v_data VARCHAR2) RETURN VARCHAR2 IS
v_return VARCHAR2(1);

n_value NUMBER;

BEGIN
v_return := 'Y';

BEGIN
n_value := to_number(v_data);

EXCEPTION WHEN VALUE_ERROR THEN
v_return := 'N';
return v_return;
END;

--DBMS_OUTPUT.PUT_LINE('Function Get_Rate is ended!');

RETURN v_return ;

END;
/

오라클 함수 정리된 곳...

http://www.statwith.pe.kr/ORACLE/functions001.htm

오라클에서 값에 _가 있는것을 조회할 떄

그냥 쓰면 1개의 문자로 대치되지만 아래 처럼 조회하면 그냥 문자로 인식함.

SELECT * FROM USER_TAB_COMMENTS
WHERE TABLE_NAME LIKE '%/_DAY' ESCAPE '/'

SELECT  DISTINCT OWNER, TABLE_NAME
FROM ALL_COL_COMMENTS
WHERE   COMMENTS LIKE '%????%'  ESCAPE '?'

sh에서 오라클 프로시져를 실행하는 예제입니다.

sqlplus -s ID/PASSWORD << MARK > ./log/exeUpdateEtcNor_$1_$THIS_DATE.log
set pause off
set linesize 500
set pagesize 100
set timing on
set serveroutput on

!date;echo "*******<< 작업 시작 >>**********************************************************************************"

!echo "현재일자:"$THIS_DATE

!echo "Step1.프로시져 실행."
DECLARE
AS_CALL_START_DATE VARCHAR2(8);
BEGIN
AS_CALL_START_DATE := '$1';
sp_update_etcnor ( AS_CALL_START_DATE );
COMMIT;
END;
/

!date;echo "*******<< 작업 종료 >>**********************************************************************************"
!echo ""
!echo ""
MARK

블로그: http://blog.naver.com/orapybubu?Redirect=Log&logNo=40048824725

A table 과 b table 를 조인하여 A table의 특정필드에 b table의 특정 필드의 값을 Update 할경우

update /*+ bypass_ujvc */
( SELECT a.colnm ra , b.colnm rb
FROM a, b
WHERE a.key= b.key
) set ra = rb;

ALTER SESSION ENABLE PARALLEL DML;
UPDATE (SELECT /*+ BYPASS_UJVC FULL(A) PARALLEL(A 4) FULL(B) PARALLEL(B 4) */
A.MOVE_TYPE, B.GUBUN
FROM TB_BCDBETCNOR PARTITION (P20090918) A, TB_BCDANUMBERMOVE B
WHERE B.APPLY_MONTH = '200909'
AND A.ROOTID IN ('BFK','BKL','BKR','BKT','BKV','BLK','BMK','BSL','BST','BTK','BVK','DKL','IKL',
'ILK','LKL','LKT','LLK','LTK','TKL','TKR','TKT','TKV','TLK','TMK','TST','TTK','TVK')
AND A.CLD_A_NO || A.CLD_K_NO || A.CLD_S_NO = B.RCP_SVC_NO
AND A.CALL_START_DATE BETWEEN B.START_DATE AND B.END_DATE
)
SET A.MOVE_TYPE = B.GUBUN;