ORACLE 내장함수

 

* abs() - 절대값구하기
array(배열) - 배열을 반환
asc() - 문자의 ANSI문자값을 반환
atn() - 아크 탄젠트로 반환
cbool() - boolean형식으로 반환
cbyte() - byte형식으로 반환
ccur() - currency형식으로 반환
cdate() - date형식으로 반환
cdbl() - double형식으로 반환
* chr() - asc()함수의 반대, ANSI문자를 일반문자로 변환
* cint() - interger형식으로 반환
clng() - long형식으로 반환
cos(각도) - 주어진 각도의 코사인 값을 반환
** createobject() - 자동화 인터페이스를 통해 다른 응용프로그램이나
프로그래밍 도구에 나타나는 객체에 대한 참조를 만들어 반환
cstr() - string형식으로 반환
** date() - 현재의 날자를 반환
dateserial(년,월,일) - 주어진 년,월,일에 대한 date형식을 반환
exp() - 주어진 수에대한 자연로그(승수로 거듭제곱하는 수) 밑인 e를 반환
filter(inputstring,값[,include[,비교]])) - inputstring는 검색할 문자열의 1차원 배열
include는 true나 false값을 가지는데, true로 설정되면 값발견시 그값을 가진
배열을 반환하고,flase로 설정되면 검색에 맞지않는 부분의 배열을 반환
비교는 비교형식을 나타내는 숫자값입니다
fix() - 주어진 수의 정수를 반환
* formatcurrency() - 원화 형식으로 반환 예) \1,000
formatdatetime() - 날자와 시간으로 형식화하여 반환
* formatnumber(숫자,소수자릿수) - 숫자로 형식화된 식을 반환 소숫점 몇째자리...
* formatpercent(수,소수자리수) - 퍼센트형식으로 반환 기본 소수2째자리까지.

hex() - 주어진 수를 16진수로 변환
hour() - 0과23 사이의 시간을 나타낸다
inputbox(프롬프트[,제목][,기본값][,xpos][,ypos][,helpfile,context]) - 대화상자
* instr([시작,]문자열1,문자열2[,비교]) - 시작은 문자열 검색 시작위치, 문자열1은 검색할
문자이며, 문자열2는 찾아야할 문자, 비교는 비교상수를 나타냄
* instrrev() - instr()과 반대로 문자 끝에서부터 검색
* int() - 주어진 수의 정수부분을 반환

isarray() - 주어진 변수가 배열인지 판단하는 boolean값을 반환
isdate() - 주어진 식이 날짜로 전환 될수 있는지 나타내는 boolean값을 반환
isempty() - 주어진 변수가 초기화 되었는지 나타내는 boolean값을 반환
isnull() - 주어진 식이 유효한 데이터가 있는지 없는지 boolean값을 반환
isnumberic() - 주어진 식이 숫자로 값이 정해질 수 있는지 나타내는 boolean값 반환
* isobject() - 자동화 객체를 참조하는지 여부를 나타내는 boolean값을 반환
join(목록[,구분기호]) - 배열에 포함되어 있는 여러 문자열을 결합하여 만든 문자열을 반환
모록은 1차원 배열이름, 구분기호는 문자열을 구분하기위해 사용하고 기본값은 "" 공백
lbound(배열이름[,차원]) - 주어진 배열에서 사용할 수 있는 가장 작은 첨자를 반환
배열이름은 배열 변수의 이름, 차원은 배열의 차원중 가장 작은 값을 나타냄
* lcase() - 주어진 문자를 소문자로 반환
** left(문자열,수) - 주어진 문자를 왼쪽으로부터 주어진 수만큼의 문자열을 반환
* len() - 주어진 문자열의 문자 개수를 세는 함수

loadpicture(그림파일의이름) - 그림객체를 반환
log() - 주어진수의 자연 로그를 반환
ltime() - 앞에 공백이 없는 문자열을 반환
** mid(문자열,시작[,길이]) - 주어진 문자열에서 주어진 위치에서부터의 길이만큼의 문자를 반환
minute(시간) - 시간에서 분값(0~59)을 반환
month(날짜) - 달의 값(1~12)을 반환
* monthname(달) - 주어진달의 숫자표현을 문자표현으로 바꾸어준다
msgbox(프롬프트[,단추][,제목][,helpfile,context]) - 프롬프트는 대화상자 안의 내용
단추는 단추모양,제목은 제목표시줄이름, helpfile은 도움말위치,context는 도움말 문맥번호
* now() - 현재 날자와 시간을 반환
oct() - 주어진 숫자의 8진수 값을 나타내는 문자를 반환
** replace(문자열,찾을문자열,바꿀문자열[,시작[,횟수[,비교]]]) - 문자열은 바꿀 대상, 찾을문자열은
문자열에서 바꾸어야 할 문자, 바꿀문자열은 찾은 문자열을 대치할 문자열, 시작은 검색시작위치
횟수는 바꾸는 횟수, 비교는 바꿀 때의 비교형식
** right(문자열,수) - 주어진 문자의 오른쪽으로 부터 주어진 수만큼의 문자를 반환
rnd(수) - 난수를 반환 수는 생략가능
round(수) - 반올림한 수를 반환, 소수 첫째자리 반올림
rtrim(문자열) - 뒤에 공백이 없는 문자열을 반환
scriptengine() - 사용중인 스크립트 언어를 나타낼때 사용
scriptenginebuildversion() - 사용중인 스크립트 엔진의 버전 수를 반환
scriptenginemajorversion() - 사용중인 스크립트 엔진의 주 버전 수를 반환
scriptengineminorversion() - 사용중인 스크립트 언어의 보조 버전 수를 반환
* second(시간) - 시스템 시간중 초값(0~59)을 반환
sgn(수) - 주어진 숫자의 부호를 나타내는 정수를 반환 0보다크면 1, 같으면 0, 작으면 -1을 반환
sin() - 주어진 라디안 값의 사인값을 반환
space(수) - 주어진 수만큼의 공백이 포함된 문자열을 반환
* split(문자열[,구분기호[,수[,비교]]]) - 문자열은 배열에 포함될 문자열, 구분기호는 문자열 구분하기
위한기호를 표시. 생략시 "" 공백, 수는 반환될 문자열의 수를 나타냄, 비교는 바꿀때의 비교형식
sqr(수) - 주어진 수의 제곱근을 반환
strcomp(문자열1,문자열2[,비교]) - 두개의 문자열을 비교하여 결과값을 반환
strreverse(문자열) - 주어진 문자열을 뒤에서부터 읽은 문자열을 반환
* string(수,문자) - 주어진 문자가 주어진 수만큼의 반복된 문자열을 반환
tan(수) - 주어진 라디안 값의 수 만큼의 탄젠트 값을 반환
* time() - 현재 시간을 반환
timeserial(시, 분, 초) - 주어진 시간,분,초에 대한 시간 값을 반환
timeval!!ue(시간) - 시간이 포함된 date형식을 반환해주는 함수
trim(문자열) - 앞뒤 공백이 없는 문자열을 반환해 줄 때 사용
typename(변수이름) - 주어진 변수의 정보를 나타내는 문자열 반환
ubound(배열이름[,차원]) - 배열의 차원으로 가장 큰 첨자를 반환
* ucase(문자열) - 주어진 문자를 대문자로 바꾸어 줍니다
vartype(변수) - 변수의 형식을 나타내는 값을 반환
weekday(날짜[,firstday of week]) - 요일을 나타내는 정수를 반환, firstday for week는 첫재요일을 숫자로
weekdayname(요일,약어, firsrday of week) - 요일은 숫자, 약어는 약어의 유무의 부울값
* year() - 현재의 년도를 나타낼때 쓰임

신고
Posted by 나비 나비:D
출처 :http://cafe.naver.com/2007jsp.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=580

-- 먼저 로그인을 합니다.

% sqlplus /as sysdba

-- 오라클 시작 및 종료

SQL>startup restrict;    open까지 실행하고 일반사용자는 사용할 수 없음
SQL>startup force;       자동종료후 다시시작
SQL>startup nomount;
SQL>alter database mount;
SQL>alter database open;

-- 간단하게 하고 싶다!!!!
SQL>startup
SQL>shutdown abort

-- 디비에 뭐가 있나??

SQL>select * from v$database;

SQL>select * from all_users; 현재 생성된 사용자
SQL>select * from v$sga;

SQL>show sga   메모리 사용보기
SQL>show parameter;
SQL>select * from dba_users;

-- 사용자 생성, 변경, 삭제
%sqlplus system/manager
SQL>create user 사용자indetified by 암호
     2>default tablespace system  ( <- 별로 안좋은 설정. tablespace 만들고 한다. )
     3>temporary tablespace temp

     4>quota 1M on system;

변경
SQL>alter user 사용자 identified by 암호;

삭제
SQL>drop user 사용자 cascade; (가지고 있는 모든객체화 함께 삭제)

권한과 룰
create session, table, index, view, sequence, cluster
drop  " 
alter  "

grant [시스템권한] to [사용자명] with admin option;


보통은 사용자 추가후...

grant resource, connect to 사용자;  로 사용한다고 하더라

-- 전체테이블보기
select table_name from dba_tables

--제약조건 확인
SQL>select * from user_constrainsts where table_name='EMP';

-- 각테이블의 컬럼내용보기
SQL>desc [table_name];

-- 똑같은 구조의 테이블작성
SQL>create table emp_temp as select * from emp;

-- user 테이블스페이스

테이블스페이스 생성
SQL>create tablespace insa datafile '/disk2/oracle/insa_01.dbf'(절대경로/파일이름) size 1M;

테이블스페이스 크기추가
SQL>alter tablespace insa add datafile '/disk2/oracle/insa_02.dbf' size 1M;

데이터 파일 크기 변경
SQL>alter tablespace insa datafile '/disk2/oracle/insa_01.dbf' resize 3M;

데이터 파일 자동변경
SQL>alter tablespace insta datafile '/disk2/oracle/insa_02.dbf'
     2>autoextend on next 1M maxsize 10M;

테이블 스페이스삭제
SQL>drop tablespace insa including contents;
SQL>exit 한후에 디렉토리의 화일을 직접삭제...

-- 기본테이블스페이스
SQL>create user gracian identified by gracian1234 default tablespace system;

현재 기본테이블스페이스확인
SQL>select username, default_tablespace from dba_users;

 로컬리 테이블스페이스
SQL>create tablespace account datafile '/disk1/oracle/account_01.dbf' size 1M
     2>extent management local uniform size 50k;

신고
Posted by 나비 나비:D
CLOB을 VARCHAR로 변환
dbms_lob.substr을 사용한다.

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

select dbms_lob.substr( x, 4000, 1 ) from T;

PLSQL에서는 long형 변수에 할당하여 변환할 수 있다.


declare
    my_var long;
begin
    for x in ( select X from t )
    loop
       my_var := dbms_lob.substr( x.X, 32000, 1 );
       ....
신고
Posted by 나비 나비:D
출처 : http://gipsi.egloos.com/968963
Oracle Package 사용법-VB
CREATE OR REPLACE PACKAGE pk_test AS
TYPE t_cur IS REF CURSOR RETURN test%ROWTYPE;
PROCEDURE sp_test(v_cur OUT t_cur);
END pk_test;
/

CREATE OR REPLACE PACKAGE BODY pk_test AS
PROCEDURE sp_test(v_cur OUT t_cur) IS
BEGIN
OPEN v_cur FOR SELECT * FROM test;
END sp_test;
END pk_test;
/



Set Spcmd = New ADODB.Command
Spcmd.ActiveConnection = cnn
Spcmd.CommandType = adCmdStoredProc
Spcmd.CommandText = "pk_test.sp_test" '패키지명.프로시져명
Spcmd.Parameters(0).Direction = adParamOutput '받는 파라미터명
Set rst = Spcmd.Execute



* 다른 방법
-- 패키지머리 생성(정의)
CREATE OR REPLACE PACKAGE pk_test AS
TYPE t_cur IS REF CURSOR; --리턴받을 변수에 타입선언
PROCEDURE sp_test(i_table in varchar2,v_cur OUT t_cur); --수행할 프로시져 선언
END pk_test;
/

-- 패키지몸체 생성
CREATE OR REPLACE PACKAGE BODY pk_test AS
PROCEDURE sp_test(i_table in varchar2,v_cur OUT t_cur) IS
BEGIN
OPEN v_cur FOR 'SELECT * FROM '||i_table; --다이내믹 커서사용
END sp_test;
END pk_test;
/


VB사용시 예제>>
Private cnn As ADODB.Connection
Private rst As New ADODB.Recordset

Set Spcmd = New ADODB.Command '새 객체 생성
Spcmd.ActiveConnection = cnn '연결
Spcmd.CommandType = adCmdStoredProc '프로시져형태
Spcmd.CommandText = "pk_test.sp_test" '패키지프로시져명
Spcmd.Parameters.Append Spcmd.CreateParameter("i_table", adVarChar,
adParamInput, 10, "emp") '입력파라미터
Spcmd.Parameters(1).Direction = adParamOutput '출력파라미터
Set rst = Spcmd.Execute '수행
신고
Posted by 나비 나비:D

제목

ORACLE - SQL 레코드를 XML로 쉽게 변환하기

작성자

DEV.WON ( http://blog.naver.com/jadin1 )

작성일

2007-06-07

배포

PUBLIC (, 출처는 밝혀 주세요)

들어가기전 잡담.)

날씨가 더워지네요.. 더불어 4학년인 필자도 슬슬 졸업이 다가옵니다. 4학년1학기^^;;;

필자는 요즘 RoR에 빠졌습니다.

프레임워크 XML세팅에 슬슬 짜증이나던 찰나에, 에자일 방법론 이론에 가장 충실할거같다는 생각이 들어 접하게 된

Ruby On Rails .. Ruby의 언어적 이질감이 처음에는 압박이었으나, 모 나름대로 잼있네요..

솔직히 Java의 강력함과 오픈소스...뒤에 은근슬쩍생산성에 대한회의감이 드는 이유는 몰까요?

DB 엔티티 하나 고치게 되면 쭉이어지는 xml 설정값 , 어트리뷰트 들의 코드 수정들..ㅜㅜ

솔직히 그동안 필자는 왠지 인터프리터 방식의 스크립트에 대해 '몬가 부족하다' 혹은 '성능상 안좋을꺼야' 라고 생각했었는데요

스크립트이자, 객체지향 언어인 Ruby의 간결함과 Rails의 강력함에 매료되버렸습니다.

웹2.0 에 나름 충실한 Ajax에 대한 지원도 괜찮았구요.

나중에 시간이 되면 오라클 연동 RoR에 대해 강좌를 만들 생각입니다.

아 한참 다른데로 빠졌네요... 오늘은 MS-SQL의 SELECT ..... FOR XML AUTO 에 상응하는 오라클의 기능을 알아보도록하죠.

이게 무슨기능이냐구요? 바로 SQL 쿼리에 의해 반환된 레코드 집합을 XML로 표현하는겁니다.

요즘 프레임워크 설정부터 , 어플리케이션 인프라 Configuration , 웹서비스는 물론, Ajax,flex 등의 web rich client 등등..

XML의 사용빈도는 점점 증가 하고 있습니다.

이 시점에서 오라클에서 제공하는 XML 지원 패키지에 대해 알아보는 시간을 갖도록하죠.

편의상 짧은 어투 양해바랍니다..^^;

시작)

SELECT 절에 의해 생성된 레코드를 Java 나 pro* c , .net 등의 미들 tier 에서 별도의 XML파싱 작업을 하지않고,

오라클에서 제공하는 패키지를 이용하여 손쉽게 XML으로 표현할수 있다.

9i와 10g에서 XML을 다루는 패키지가 다르니 주의하기바란다.

SELECT * FROM PRT 라는 SQL에 의해 반환되는 레코드는 아래와 같다.

이제 필자가 작성한 10gXML제네릭 함수를 보도록하자.

(9i, 8i 사용자들은 아래의 코드에서 DBMS_XMLQUERY 부분을 XMLGEN으로 바꾸면 된다.

독자중 XMLGEN 패키지가 없는 분들은 oracle.com에서 xsu12.tar.gz 를 다운받아 압축을 푼후, xsu12.jar를

오라클 스키마에 올리도록한다.

이때 사용법은 loadjava -verbose -resolve -user scott/tiger xsu12.jar 이다. (기타 자세한 사용법은 loadjava 을 검색하라.)

(혹은 http://blog.naver.com/jadin1/70018342609이 아티클을 참고하길 바란다.)

함수코드)

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

create or replace function GET_XML_RECORD
(xmlSQL IN VARCHAR2,
encodingType IN VARCHAR2 := 'UTF-8') return CLOB is



Ctx dbms_xmlquery.ctxHandle; -- SQL에서 XML로 컨버팅된 객체 핸들
xmlRecord CLOB; --리턴될 값

begin

--컨텍스트 얻어오기
Ctx := dbms_xmlquery.newContext(xmlSQL);

-- encoding 설정
dbms_xmlquery.setEncodingTag(Ctx,encodingType);

-- xml 생성
xmlRecord := dbms_xmlquery.getXML(Ctx);


RETURN xmlRecord;


end GET_XML_RECORD;
-----------------------------------------------------------------------------------------

사용예)

SELECT GET_XML_RECORD('SELECT * FROM PRT','EUC-KR') FROM DUAL;

결과값)


마지막으로 오라클 도큐먼트에서 도움이 될만한 부분 남깁니다.
DBMS_XMLQUERY패키지..

GETXML

Creates the new context, executes the query, gets the XML back and closes the context. This is a convenience function. The context doesn't have to be explicitly opened or closed. The options are described in the following table.

Syntax Description
FUNCTION getXML(  sqlQuery IN VARCHAR2,  metaType IN NUMBER := NONE) RETURN CLOB; 

This function uses a SQL query in string form.

FUNCTION getXML(  sqlQuery IN CLOB,  metaType IN NUMBER := NONE) RETURN CLOB; 

This function uses a SQL query in CLOB form.

FUNCTION getXML(  ctxHdl IN ctxType,  metaType IN NUMBER := NONE) RETURN CLOB; 

This function generates the XML document based on a SQL query used to initialize the context.

PROCEDURE getXML(  ctxHdl IN ctxType,  xDoc IN CLOB,  metaType IN NUMBER := NONE); 

This procedure generates the XML document based on the SQL query used to initialize the context.

Parameter IN / OUT Description
ctxHdl 
(IN) 

Context handle.

metaType 
(IN) 

XML metadata type (NONE, DTD, or SCHEMA).

sqlQuery 
(IN) 

SQL query.

xDoc 
(IN) 

CLOB into which to write the generated XML document.

위에서 특히나, GETXML 함수에 metaType을 지정하면 meta 정보 (DTD, Schema)가 포함되어 반환됩니다.
아주 유용하죠..
다시금 오라클의 강력함과 동시에 오라클에서 점점 B2B, EAI 에 적합한 XML Service에 비중을 많이 두는것 같은 기분이 듭니다.
그럼 부족한 강좌 끝까지 읽어주셔서 감사합니다.
<
신고
Posted by 나비 나비:D
오라클 내장 함수
오라클의 함수에는 단일행 함수복수행 함수(그룹함수)가 있다.


숫자 함수(number function)
ABS ACOS ASIN ATAN
ATAN2 BITAND CEIL COS
COSH EXP FLOOR LN
LOG MOD POWER ROUND(number)
SIGN SIN SINH SQRT
TAN TANH TRUNC(number) WIDTH_BUCKET

문자 함수(character function)
CHR CONCAT INITCAP LOWER
LPAD LTRIM NLS_INITCAP NLS_LOWER
NLSSORT NLS_UPPER REPLACE RPAD
RTRIM SOUNDEX SUBSTR TRANSLATE
TREAT TRIM UPPER ASCII
INSTR LENGTH, LENGTHB, LENGTHC, LENGTH2, LENGTH4

날짜와 날짜 처리함수(date, datetime function)
ADD_MONTHS CURRENT_DATE CURRENT_TIMESTAMP DBTIMEZONE
EXTRACT(datetime) FROM_TZ LAST_DAY LOCALTIMESTAMP
MONTHS_BETWEEN NEW_TIME NEXT_DAY NUMTODSINTERVAL
NUMTOYMINTERVAL ROUND(date) SESSIONTIMEZONE SYS_EXTRACT_UTC
SYSDATE SYSTIMESTAMP TO_DSINTERVAL TO_TIMESTAMP
TO_TIMESTAMP_TZ TO_YMINTERVAL TRUNC(date) TZ_OFFSET

데이터 형변환 함수(conversion function)
ASCIISTR BIN_TO_NUM CAST CHARTOROWID
COMPOSE CONVERT DECOMPOSE HEXTORAW
NUMTODSINTERVAL NUMTOYMINTERVAL RAWTOHEX RAWTONHEX
ROWIDTOCHAR ROWIDTONCHAR TO_CHAR(character) TO_CHAR(datetime)
TO_CHAR(number) TO_CLOB TO_DATE TO_DSINTERVAL
TO_LOB TO_MULTI_BYTE TO_NCHAR(character) TO_NCHAR(datetime)
TO_NCHAR(number) TO_NCLOB TO_NUMBER TO_SINGLE_BYTE
TO_YMINTERVAL TRANSLATE ... USING UNISTR
참조로 형변환 관계표를 참조하면 편리하다.
기타함수(miscellaneous single row function)
BFILENAME COALESCE DECODE DEPTH
DUMP EMPTY_BLOB
EMPTY_CLOB
EXISTSNODE EXTRACT(XML)
EXTRACTVALUE GREATEST LEAST NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID NLS_CHARSET_NAME NULLIF NVL
NVL2 PATH SYS_CONNECT_BY_PATH SYS_CONTEXT
SYS_DBURIGEN SYS_EXTRACT_UTC SYS_GUID SYS_TYPEID
SYS_XMLAGG SYS_XMLGEN UID UPDATEXML
USER USERENV VSIZE XMLAGG
XMLCOLATTVAL XMLCONCAT XMLFOREST XMLSEQUENCE
XMLTRANSFORM XMLELEMENT CASE

그룹함수 집계(Aggregate) 함수
AVG CORR COUNT COVAR_POP
COVAR_SAMP CUME_DIST DENSE_RANK FIRST
GROUP_ID GROUPING GROUPING_ID LAST
MAX MIN PERCENTILE_CONT PERCENTILE_DISC
PERCENT_RANK RANK REGR function STDDEV
STDDEV_POP STDDEV_SAMP SUM VAR_POP
VAR_SAMP VARIANCE GROUPING SETS
분석(Analytic) 함수
AVG CORR COUNT COVAR_POP
COVAR_SAMP CUME_DIST DENSE_RANK FIRST
FIRST_VALUE LAG LAST LAST_VALUE
LEAD MAX MIN NTILE
PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC RANK
RATIO_TO_REPORT REGR_(linear regression) function ROW_NUMBER STDDEV
STDDEV_POP STDDEV_SAMP SUM VAR_POP
VAR_SAMP VARIANCE TOP_N 분석

윈도우(windowing) 분석 함수

윈도우 분석함수부분 합을 계속 더해 나감, sum은 전체 합계를 구함


객체 참조 함수
DEREF MAKE_REF REF REFTOHEX VALUE
PseudoColumn을 의미하는 것
ROWIDEach row in the database has an address
ROWNUM테이블에서 select 되어진 행의 순서번호
LEVEL테이블에서 행(row)의 계층관계를 가리키는 일련번호 순서
신고
Posted by 나비 나비:D
1. Oracle Client를 설치하고, 연결할 Oracle을 에 대한 설정을 한다.

2. DB connect Function(여기서 XXX 부분에 Oracle Client에서 설정한 값을 입력한다.)
  - DB 연결이 필요한 부분에 아래 function을 호출한다.

Private Sub ConnectDB()
    Set adoOraCon = New ADODB.Connection
   
    With adoOraCon
        .ConnectionString = "Provider=MSDAORA.1;Data Source=XXX;User ID=XXX;Password=XXX;Persist Security Info=True"
        .ConnectionTimeout = 60
        .Open
    End With
End Sub

3. 모듈에  다음 function을 입력한다.
Option Explicit
Public adoOraCon As ADODB.Connection

'   Procedure : GetRecordSet
' Description : 인자로 넘어오는 쿼리를 실행하고, 결과값을 RecordSet으로 반환
'   Parameter : szSql(쿼리)
'Return Value : Recordset
Public Function GetRecordSet(ByVal szSql As String) As ADODB.Recordset
   
    Dim adoRs As ADODB.Recordset
   
    Set adoRs = New ADODB.Recordset
   
    adoRs.Open szSql, adoOraCon, adOpenKeyset, adLockBatchOptimistic
       
    'Recordset 반환
    Set GetRecordSet = adoRs

    Set adoRs = Nothing
       
End Function

'   Procedure : ExecuteQuery
' Description : 인자로 넘어오는 쿼리를 실행하고 성공여부를 반환
'   Parameter : szSql(쿼리)
'Return Value : True/False
Public Function ExecuteQuery(szSql As String) As Boolean
   
    On Error GoTo ErrHandler
   
    adoOraCon.Execute szSql
   
    ExecuteQuery = True
   
    Exit Function
   
ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Source & vbCrLf & Err.Description, vbExclamation, "쿼리 수행 오류"
        ExecuteQuery = False
        Err.Clear
    End If
End Function

4. 원하는 쿼리를 만들어 모듈의 function을 실행한다.
  - 일반적으로 값을 가지고 오는 Select Query이면, GetRecordSet를 실행하고,
  - insert, delete와 같은 데이터 조작 쿼리인 경우에는 ExecuteQuery를 실행한다.

  예) SELECT Query
    Dim strSQL As String
    Dim adoRs As ADODB.Recordset
   
    '//IMPORTANT : 조회 쿼리 생성
    strSQL = "SELECT * FROM TEST_TAB ORDER BY A_COL"
   
    Set adoRs = GetRecordSet(strSQL)

    While Not adoRs.EOF
        '//IMPORTANT : 가지고 온 결과를 처리하는 부분
        adoRs.MoveNext
    Wend
   
    adoRs.Close
    Set adoRs = Nothing

  예) Execute Query
  Dim strSQL As String
  strSQL = "INSERT INTO TEST_TAB(a_col, b_col, c_col, d_col) VALUES ('" & strName & "', '" & strKorean & "', '" & strMath & "', '" & strEnglish & "')"
       
  If ExecuteQuery(strSQL) = False Then
      Msgbox "실패"
  Else
      Msgbox "성공"
  End If
신고
Posted by 나비 나비:D
 

Oracle 구독자

Microsoft® SQL Server™ 2000에는 Intel 컴퓨터 상의 SQL Server 게시에 대한 Oracle 구독을 지원하는 ODBC 드라이버와 OLE DB 공급자가 포함되어 있습니다. SQL Server 2000 설치 프로그램은 드라이버를 자동으로 설치합니다.

참고   Oracle ODBC와 OLE DB 구독자에 복제하려면 Oracle 또는 소프트웨어 공급업체로부터 적절한 Oracle SQL*Net 드라이버를 구해야 합니다. 그런 다음, 이 드라이버를 게시자와 배포자에 설치해야 합니다.

Oracle 구독자에 대한 복제 제한 사항

Oracle ODBC 구독자에 복제할 때는 다음과 같은 제한 사항이 적용됩니다.

  • Oracle 구독자에는 이름에 공백이 있는 테이블의 복제가 만들어지지 않습니다. 복제는 실패하며 Oracle 오류 ORA-00903: 잘못된 테이블 이름이 발생합니다.

  • date 데이터 형식은 작은 datetime입니다(범위: 4712 B.C. - 4712 A.D.).

    Oracle에 복제하는 경우에는 복제된 열의 SQL Server datetime 항목이 이 범위 안에 있는지 확인합니다.

  • 복제된 테이블에는 long raw에 매핑되는 text 또는 image 데이터 형식의 열이 하나만 있어야 합니다.

  • datetime 데이터 형식은 char4에 매핑됩니다.

  • floatreal 데이터 형식에 대한 SQL Server 2000의 범위는 Oracle 범위와 다릅니다.

다음 표에서는 복제를 위한 데이터 형식을 Oracle 구독자에 매핑합니다.

SQL Server 2000 데이터 형식 Oracle 데이터 형식
bigint NUMBER
binary LONG RAW NOT NULL
bit NUMBER (1, 0)
char VARCHAR2 (900) NOT NULL
datetime DATE
decimal NUMBER (255, 3) NOT NULL
float FLOAT NOT NULL
image LONG RAW
int NUMBER (255, 3) NOT NULL
money NUMBER (255, 3) NOT NULL
nchar VARCHAR2 (2000) NOT NULL
ntext LONG
numeric NUMBER (255, 3) NOT NULL
nvarchar VARCHAR2 (2000) NOT NULL
real FLOAT NOT NULL
smallint NUMBER (255, 3) NOT NULL
smalldatetime DATE NOT NULL
smallmoney NUMBER (255, 3) NOT NULL
sql_variant LONG
sysname CHAR(255)
text LONG
timestamp RAW (255)
tinyint NUMBER (255, 3) NOT NULL

Oracle 데이터 형식 정의

다음은 Oracle 데이터 형식 정의 목록입니다.

Oracle 데이터 형식 정의
CHAR <=2000
DATE 4712 B.C. 1월 1일 - 4712 A.D. 12월 31일
DECIMAL Number와 동일
FLOAT Number와 동일
INTEGER Number와 동일
LONG <=2GB
LONG RAW 원시 데이터. Long과 동일
LONG VARCHAR Long과 동일
NUMBER 1.0E-130부터 9.99..E125까지
SMALLINT Number와 동일
RAW 원시 이진 데이터 <=255바이트
ROWID 고유 값
VARCHAR2 <=4000바이트
VARCHAR Varchar2와 동일
BLOB 대용량 이진 개체 <=4GB
COB 대용량 문자 개체 <=4GB
NCLOB Clob(멀티바이트용)과 동일
BFILE 이진 연산 파일에 대한 포인터
신고
Posted by 나비 나비:D

[Oracle] Qry Tunning

2007.12.17 17:36
출처 카페 > http://sqlplus... / 네오
원본 http://cafe.naver.com/neocip/178

1. 시스템을 파악

   - 데이터의 성격을 명확히 알아야만 한다.
   - 튜닝 방법을 선택하기 위한 체크리스트를 작성한다.
   - 사용하는 SQL 쿼리를 분석한다.
     1) SQL*PLUS 에서 "set autotrace on" 명령을 수행하고 TKPROF를 이용하여 분석한다.
     2) 문제가되는 SQL 문의 explain plan을 조회한다.
     3) "set timing on" 명령을 수행하여 실행 시간을 검사한다.

   ** 한 세션만 트레이스를 거는 방법
      1) select sid, serial# from v$session where username='SCOTT'; 해당 유저의 id를 알아낸다.
      2) execute dbms_system.set_sql_trace_in_session({SID},{Serial#},TRUE);


2. Analyze 를 한다.
   Optimizer의 파싱연산을 더욱 정확하게 수행토록 유도할 수 있다.

   -Analyze table [table 명] compute statistics;   테이블 전체 연산.
   -Analyze table [table 명] estimate statistics;  테이블이 큰 경우 1064row 까지만 연산.
   -Analyze table [table 명] delete statistics;    이전 연산결과 삭제.
   - 한 유저의 모든 테이블을 분석할경우
     exec dbms_utility.analyze_schema('SCOTT','COMPUTE');


3. Hint 를 사용한다.

   - table Full Scan을 유도
     select /*+ FULL(table_name) */ column1, 2 .. from table_name ... ;

   - index 사용을 유도
     select /*+ INDEX(table_name index_name1, 2 .. ) */ column1,2 .. from table_name ... ;

   - table 을 순서대로 조인하도록 유도
     select /*+ ORDERED */ column1,2 .. from table1, table2 ... ;    

   - Cost-based 연산시 또는 모든 row 를 캐쉬하도록 유도(인덱스를 사용 않하도록 유도)
     select /*+ ALL_ROWS */ .... ;

   - Cost-based 연산시 응답속도를 빠르게 하기위해 (인덱스 걸린 컬럼 쿼리시)
     select /*+ FIRST_ROWS */ .... ;


4. 인덱스를 생성/삭제 한다.

   EX1) 인덱스가 항상 좋은가 ??
       EMP 테이블은 10만건의 데이터가 있고 DEPT_NO=10 것은 2만5천건
       존재한다고 할때 수행시간 비교.

    select ENAME from EMP where DEPT_NO=10;
      
   1) 인덱스가 없을때 : 약 55초 소요
   2) 인덱스가 있을때 : 약 70초 소요
   3) dept_no, ename 의 복합 인덱스가 있을때 : 약 0.1초 소요.
      (단, ename은 unique)

   ** 인덱스가 걸리는 컬럼은 중복값이 적은것이 좋다. 만일 전체의 25% 이상의 중복
      값을 가지는 값을 쿼리시 시간이 더 걸리게 된다.


   EX2) 인덱스와 힌트의 적절한 사용....
       EMP 테이블은 1만건의 데이터가 있고, DEPT_NO에 인덱스가 걸려있고,
       DEPT_NO>9990 것은 5천건 존재한다고 할때 수행시간 비교.

    select ENAME, DEPT_NO from EMP where DEPT_NO>9990;
     -> 약 53초 소요

    select /*+ FULL(EMP) */ ENAME, DEPT_NO from EMP where DEPT_NO>9990;
     -> 약 4초 소요


   EX3) join 테이블의 쿼리시 속도비교
        EMP 테이블은 10만건, DEPT 테이블은 10건의 데이터가 있을때.

    select ENAME, DEPT_NO from EMP, DEPT
    where EMP.DEPT_NO = DEPT.DEPT_NO and EMP_NO=5 and DEPT_NO=10 ;
     -> 약 4분 소요 된다면

    select /*+ ORDERED */ ENAME, DEPT_NO from EMP, DEPT
    where EMP.DEPT_NO = DEPT.DEPT_NO and EMP_NO=5 and DEPT_NO=10 ;
     -> 약 15초 소요
 
    select ENAME, DEPT_NO from EMP where EMP_NO=5 and EXISTS
             ( select 'X' from DEPT where EMP.DEPT_NO = DEPT.DEPT_NO
                          and DEPT_NO=10 ) ;
     -> 약 8초 소요

     ** Exists, Union 등의 연산자는 속도를 개선시켜줌.


5. 테이블을 조정한다.



=> union 을 쓰면 프로그램 속도가 아주 저하될 수도 있으므로 피한다.(인덱스를 탄다면 문제없음.)

=> IN 대신 Exists를 사용하면 속도를 개선한다.

신고
Posted by 나비 나비:D

[Oracle]CREATE TABLE

2007.12.17 09:47

CREATE TABLE A1_Prof(

             PID        char(4) PRIMARY KEY,

             PName   char(10) not null,

             PDept    char(15)

);

 

CREATE TABLE A1_Student(

             ID          char(9)   PRIMARY KEY,

             Name     char(10) not null,

             Dept      char(15) not null,

             Grade    number(5) not null,

             PID        char(4)   REFERENCES A1_Prof(PID)

);

 

CREATE TABLE A1_Curriculum(

             SubjectID            char(10) PRIMARY KEY,

             SubjectName      char(20) not null

);

 

CREATE TABLE A1_Score(

             ID                        char(9),

             SubjectID            char(10),

             Score                 number(5)  Default 0 not null,

             PRIMARY KEY (ID, SubjectID),

             FOREIGN KEY (ID) REFERENCES A1_Student(ID),

             FOREIGN KEY (SubjectID) REFERENCES A1_Curriculum(SubjectID)

);

 

 

CREATE TABLE A1_Customer_Info(

             CID        char(6)  PRIMARY KEY,

             Name     char(10) not null,

             Address             char(20) ,

             Tel        char(15) ,

             Bday      date       not null,

             SID        char(14)not null,

             Job        char(10) ,

             Sex        char(1)  not null,

             Married  number(1)

);

 

CREATE TABLE  A1_Customer_OnlineInfo( 

             CID        char(6) ,

             LID        char(20) not null,

             LPW       char(15) not null,

             Email     char(20) ,

             Jday       date not null,

             PRIMARY KEY (CID),

             FOREIGN KEY (CID) REFERENCES A1_Customer_Info(CID) 

);

 

CREATE TABLE A1_Product(

             PID        int PRIMARY KEY,

             Name     char(15) not null,

             Cor        char(20) not null,

             UnitPrice int  not null,

             Holding   int  not null,

             Photo     char(20)

);

 

CREATE TABLE A1_Region(

             RID        number(5) PRIMARY KEY,

             LRegion char(10) not null,

             SRegion char(10) not null

);

 

CREATE TABLE A1_Store(

             SID        number(5) PRIMARY KEY,

             Name     char(20) not null,

             RID        number(5)  REFERENCES A1_Region(RID)

);

 

CREATE TABLE A1_Sales(

             SaleID    number(5) PRIMARY KEY,

             PID        number(5)REFERENCES A1_Product(PID),

             SID        number(5)REFERENCES A1_Store(SID),

             CID        char(6)REFERENCES  A1_Customer_Info(CID),

             SDay      date  not null

);

 

 

CREATE TABLE A1_sales_return(

             returnday            date not null,

             returnreason       char(30) not null,

             saleid                 number(5) REFERENCES A1_store(sid)

);

 

CREATE TABLE A1_customer_score(

             cid char(6) REFERENCES A1_customer_info(cid),

        score number(6)  default 0 not null

);

 

 
 
신고
Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2017/10   »
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        
Total : 882,092
Today : 83 Yesterday : 146