잉여생활~ ㅋ.,ㅋ

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() - 현재의 년도를 나타낼때 쓰임

오라클에서는 왜 MSSQL에서 사용하는 임시 테이블을 만들수없을까
고민했던 적이 있었습니다. 임시테이블 만들수 있습니다. 있구요.
 
임시 테이블을 생성하되 duration은 transaction단위로 한다는 것을 기억해야 합니다.
아래의 순서를 따라 하시면 임시 테이블을 만드실 수 있습니다.
 
1. 임시 테이블생성
SQL> create global temporary table temp_tab (col1 number, col2 char(15))
     on commit delete rows ;
    
--> on commit delete rows 를 잘 기억해 두세요.
 [Commit 을 하면 삭제작업을 하는것으로 설정한다는 말입니다.]

2. 임시 테이블에 인덱스 만들기
SQL> create index temp_tab_n1 on  temp_tab(col1);
 
3. 임시 테이블에 데이터 인서트
SQL> insert into temp_tab values ( 1 , '구미지엔') ;
 
 ★ 주의 : Insert/Update 했다고 Commit 하시면 안됩니다.
 
4. 임시 테이블에서 데이터 가져오기
SQL> select * from temp_tab ;
     COL1 COL2
--------- ------------
              1 구미지엔
 
5. 임시 테이블 관찰
생김새를 자세히 보시려면 토드(Toad)에서 desc temp_tab 하시고

스크립트를 살펴보시면 기존에는 있던 Tablespace 구절이 빠져 있는것을 알 수 있습니다.
이는 임시테이블이나 인덱스가 메모리에 생성된다는 것을 말합니다.
DBA 업무 하시는 분들은 다이나믹 퍼포먼스 뷰 보시면(user_objects 등) 알수 있습니다.

6. 임시 테이블의 데이터를 메모리에서 내리기
SQL> commit ;
Commit complete.
 
7. 임시 테이블을 다시 보면
   duration이 transaction단위이기 때문에 commit을 수행하면 모든 data가 사라집니다.

일반적으로, Customizing Tablespace 에 만들어 둔 영구테이블들은
레포트가 실행 될때마다 테이블에 데이터가 계속 쌓이거나
테이블의 내용을 모두 지우거나 Truncate 한후
데이터를 다시 입력하는 번거러움이 있는 줄로 압니다.
 
그때 마다의 물리적인 I/O(삭제작업 시) 발생은 서버에 상당한 부하를 주겠죠

(물론 데이터 수십건 처리하는 트랜잭션이라면 부하를 주진 않겠죠. )

그래서 메모리를 사용하면 물리적인 I/O 가 메모리에서 발생하므로

다른 사용자 트랜잭션에 영향을 거의 주지 않겠죠.
또하나 , Disk 보다는 메모리의 속도가 빠르다.
 
위의 일반적인 방법과 임시테이블 사용에는 서로 장단점이 있겠습니다만,
실행 속도는 비교가 안되겠죠.(제 생각)

<임시테이블 관련 자료 출처 : 한국오라클>

출처 :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;

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 );
       ....

출처 : http://gipsi.egloos.com/968963 Oracle Package 사용법-VB

출처 : http://www.planetsourcecode.com/URLSEO/vb/Discussion/AskAProShowPost!asp/lngTopicId!33566/TopicCategory!XML_XSL+Questions/Flag!2/lngWId!1/blnURLSEO!true/Posting.htm

This isn't a question but a solution I found something that is worth a share here at this forum.

It had been a tedious job to handle CLOB data returned from Oracle 8i Stored Procedure into VB6 \ Visual Basic 6 using Microsoft ODBC. The support provided for handling CLOB is ONLY for SQL QUERIES where you fire to SELECT\INSERT\DELETE\UPDATE a CLOB COLUMN from a table, but NOBODY has really bothered about a RETURN VARIABLE as CLOB from ORACLE STORED PROCEDURE. Search on internet but it's useless.

I had this particular Stored Proc that was accepting a Key and depending on that Key, it would FETCH Data, and Make an XML string. This XML would extend upto 40-50K characters which is really large for VARCHAR2 \ LONG datatype to single handedly manage in oracle. Hence I had to use CLOB, but in VB (ADO 2.8) there was no associated DATATYPE or data-structre support that would accept this return value from my stored proc. (adVariant was most perfect but that is useless too)

.... (Poor Me I would bang my head against PC monitor that would show me sites providing help on CLOB Table, Oracle ODBC for CLOB or even CLOB JDBC support in JAVA but they would make me more helpless)

This is what I was doing

# Oracle Code


PROCEDURE GetXML
(temp IN NUMBER,
OutputXML OUT CLOB)
IS
--.....Variable Declaration
BEGIN
OutputXML :=EMPTY_CLOB();
DBMS_LOB.CREATETEMPORARY(OutputXML,TRUE);

stCurrentXMLNode := ''; --Load XML Denotion
--Code to get data, make XML and store into CLOB variable goes here....
DBMS_LOB.WRITEAPPEND(OutputXML, LENGTH(stCurrentXMLNode),stCurrentXMLNode); --Finally complete Loading Data in OUTPUT CLOB
COMMIT;
END GetXML;

But VB 6 was not supporting this OUT CLOB parameter OutputXML and would throw Microsoft ODBC inconsistent Datatype or Access Violation Error for every datatype I tried (VB adDataypes)

Then what I did is divided this collected CLOB in distinct ARRAY (Table) of VARCHAR2 variables of some fixed size(say 4000) Using this code

CREATE OR REPLACE PACKAGE BODY MYPACKEGE
AS
TYPE tblXML IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; --VRACHAR2 ARRAY Table

PROCEDURE GetXML
(temp IN NUMBER,
OutputXML OUT tblXML)
IS
--.....Variable Declaration
BEGIN
OutputXML :=EMPTY_CLOB();
DBMS_LOB.CREATETEMPORARY(OutputXML,TRUE);

stCurrentXMLNode := ''; --Load XML Denotion
--Code to get data, make XML and store into CLOB variable goes here....
DBMS_LOB.WRITEAPPEND(OutputXML, LENGTH(stCurrentXMLNode),stCurrentXMLNode); --Finally complete Loading Data in OUTPUT CLOB

--********************************************************---
output_var := ' '; -- NonEmpty String
offset_var := 0; -- Offset To start chopping with
amount_var := 4000; -- Fixed SIze Varchar2

nCount := 0;
WHILE output_var <> ''
LOOP
DBMS_LOB.READ(OutputXML,amount_var,offset_var,output_var);
--Load character Chunk 'output_var' Cut from previous statement into Array element
OutputXML(nCount) := output_var;
nCount := nCount + 1;
offset_var:= amount_var + offset_var + 1;
END LOOP;
--********************************************************---
COMMIT;
END GetXML;
End MYPACKAGE;

Now use following VB 6 code to load this into a Recordset, then loope thru recordset and load it into a String (max size 65K characters) or variant(max size 2G Characaters)

# VB Code

Dim objCmd As New ADODB.Command
Dim objRS As New ADODB.Recordset

Dim vXML As Variant
With objCmd
.ActiveConnection = "{Microsoft ODBC DSN \ ConnectionString}"
.CommandType = adCmdText
.CommandTimeout = 30
.CommandText = "{call MYPACKAGE.GetXML(?, {resultset 10000, OutputXML})}"
.Parameters.Append .CreateParameter("temp", adNumeric, adParamInput, , 1234) 'Input Paramneter

Set objRS = .Execute 'Load Output VARCHAR2 Array into RecordSet

vXML = ""
While Not objRS.EOF
vXML = vXML & objRS(0).Value 'Loading Large CLOB (by looped appending) into a Variant
objRS.MoveNext
Wend
End With

vXML variable has your CLOB now ....

What Say ...

<