MS949(EUC-KR)에서 UTF-8 변경시 DBMS 컬럼 사이즈 변경 작성

Database/Oracle 2019. 10. 16. 13:15

MS949(euc-kr)에서 한글의 경우는 2byte를 사용하고

UTF-8로 변경시 한글은 3byte를 사용함.(ASCII 경우(영문,숫자)는 기존대로 1byte만 사용함)

** DBMS 변경시 관련 스크립트 작성 참조(작업전 백업은 필수!!)

ORACLE, Tibero만 해당됨

1. 기존 DB에서(MS949) UTF-8 캐릭터셋 사이즈를 미리 조정한다.

1-1. 스크립트 생성

SELECT 'ATLER TABLE '|| table_name ||' MODIFY('|| LOWER(column_name) ||' VARCHAR2('|| LEAST(CEIL(data_length*1.5/10) * 10, 4000) ||'));' AS sqltext

FROM USER_TAB_COLUMNS

WHERE data_type LIKE 'VARCHAR%'

AND data_length > 1

AND data_length < 4000;

1-2. 작성된 스크립트 확인

> 숫자 또는 영문자만 들어가는 필드는 제외한다.

예) VARCHAR2 형식으로 저장되는 날짜형식등

> 시스템 관련 테이블의 컬럼은 제외

예) SYS_XXXXX

1-3. 작성된(1-1) 스크립트 내용을 기존(MS949)에 적용함

2. 사이즈가 변경된 기존(MS949) DB를 백업한다.

3. 백업한 기존(MS949) DB를 이관할 DB(UTF-8)에 임포트한다.

순서정리 : 기존DB 사이즈 조정 스크립트 작성 > 필요없는 컬럼 제외 > 다시 기존 DB에 스크립 적용 > 기존 DB 백업 > 신규 DB(UTF-8) 임포트

저작자표시

유효하지않음

,

crontab에서 plsql 실행

Database/Oracle 2009. 9. 6. 19:09

[참조] smartsql.tistory.com

sqlplus 실행시 @를 붙여 별도의 sql문을 실행하면서 해도 되지만
파일을 별도로 관리해야 되는 번거로움 때문에 쉘스크립트상에서 실행하기로 했음

crontab 에 등록시
4 19 * * * /oracle/shell/call_proc_profit_dist.sh >> /oracle/shell/log/proc_profit_dist.log

file: call_proc_profit_dist.sh

#!/bin/bash
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/10.2.0
export ORACLE_OWNER=oracle
export ORACLE_SID=kumc
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export NLS_LANG=KOREAN_KOREA.KO16MSWIN949
export NLS_NCHAR=KOREAN_KOREA.UTF8
export NLS_DATE_FORMAT=YYYYMMDD
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/demo:$ORAClE_HOME/oracm/lib:/lib:/usr/lib:/usr/local/lib
export LDPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export PATH=$ORACLE_HOME/JRE/bin:$ORACLE_HOME/bin:.:$PATH
export TEMPDIR=/tmp
export THREADS_FLAG=native
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/oracm/bin
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:$ORACLE_HOME/network/jlib
export EDITOR=vi
export LANG=ko_KR.eucKR
export PATH ORACLE_BASE ORACLE_HOME ORACLE_OWNER ORACLE_SID  TNS_ADMIN NLS_LANG LD_LIBRARY_PATH ORA_NLS33  PATH LD_ASSUME_KERNEL TEMPDIR THREADS_FLAG PATH CLASSPATH EDITOR LANG NLS_DATE_FORMAT DISABLE_HUGETLBFS LDPATH


sqlplus -s kumc_cost/kumc_cost << __END__
	WHENEVER SQLERROR EXIT 1
	WHENEVER OSERROR EXIT 1
	SET TIMING ON
	SET SERVEROUTPUT ON
	SET PAGESIZE 0
	SET FEEDBACK OFF
	SET VERIFY OFF
	SET HEADING OFF
	SET ECHO OFF

	DECLARE
		vMsg	VARCHAR2(4000)	:= NULL;
		vSQL	VARCHAR2(4000)	:= NULL;
		vCheck	BOOLEAN			:= TRUE;
		vSeq	NUMBER;
	BEGIN
		DBMS_OUTPUT.ENABLE('10000000000');

		SELECT	cpi_seq
		INTO	vSeq
		FROM	COST_PROCESS_INFO
		WHERE	cpi_status = 'P';

		PROC_PROFIT_DIST(vCheck, vMsg);
		--vSQL := 'CALL PROC_PROFIT_DIST(:vCheck, :vMsg)';
		--EXECUTE IMMEDIATE vSQL USING IN vCheck, OUT vMsg;
		--DBMS_OUTPUT.PUT_LINE(vMsg);
		
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.PUT_LINE('진행중인 절차가 없습니다.!!');
	END;
	/
__END__


EXITCODE=$?
export EXITCODE

if [ "$EXITCODE" -ne 0 ]
then
	echo "Error: SQL*Plus exit code : $EXITCODE"
	echo ""
	echo "===== Procedure run aborted at `date` !!! ===="
	echo ""
	exit 1
fi

echo ""
echo "===== Procedure Finished at `date` !!! ===="
echo ""

call_proc_profit_dist.sh

유효하지않음

,

ORACLE 백업 펄스크립트

Database/Oracle 2009. 3. 23. 20:36

==> 본인의 글 입니다. 이 글을 다른곳에 게재하는 경우 본문의 출처를 밝혀주시기 바람니다.

거래처 솔라리스인 관계로 리눅스에서 작업했던 bash쉘 기능이 전부 먹히지 않아
그냥 Perl 스크립트로 만들어 보았습니다..

file: ora_backup.pl

#!/usr/bin/perl

use DBI;
use Time::Local;
use Error qw(:try);
use Archive::Zip qw(:ERROR_CODES :CONSTANTS);

BEGIN
{
	$ENV{ORACLE_OWNER}		= "oracle9";
	$ENV{ORACLE_BASE}		= "/home/$ENV{ORACLE_OWNER}";
	$ENV{ORACLE_HOME}		= "$ENV{ORACLE_BASE}/product/9i";
	$ENV{TNS_ADMIN}			= "$ENV{ORACLE_HOME}/network/admin";
	$ENV{ORA_NLS33}			= "$ENV{ORACLE_HOME}/ocommon/nls/admin/data";
	$ENV{ORACLE_SID}		= "NEXTBSC";
	$ENV{ORACLE_TNSNAME}	= "NEXTBSC";
	$ENV{NLS_LANG}			= "KOREAN_KOREA.KO16MSWIN949";
	$ENV{NLS_NCHAR}			= "KOREAN_KOREA.UTF8";
	$ENV{LD_LIBRARY_PATH}	= "$ENV{ORACLE_HOME}/lib:$ENV{ORACLE_HOME}/lib32:$ENV{ORACLE_HOME}/rdbms/demo:$ENV{ORACLE_HOME}/oracm/lib:/lib:/usr/lib:/usr/local/lib";
	$ENV{LD_RUN_PATH}		= "$ENV{ORACLE_HOME}/lib";
	$ENV{NLS_DATE_FORMAT}	= "YYYYMMDD";
	$ENV{TEMPDIR}			= "/tmp";
	$ENV{THREADS_FLAG}		= "native";
}

=comment
foreach my $key(keys %ENV)
{
	print $key."=". $ENV{$key}."\n";
}
=cut

##-------------------------------------------------------------------------------------------------------
## 삭제할 파일 일자 정의(3일전)
##-------------------------------------------------------------------------------------------------------
($year, $month, $day) = (localtime(time - (60*60*24*3)))[5,4,3];
my $rmdate			  = sprintf("%04d%02d%02d", $year+1900, $month+1, $day);


##-------------------------------------------------------------------------------------------------------
##백업할 파일 일자 정의(당일)
##-------------------------------------------------------------------------------------------------------
($year, $month, $day) = (localtime(time))[5,4,3];
my $date			  = sprintf("%04d%02d%02d", $year+1900, $month+1, $day);
#my $date			  = sprintf("%02d", $day);


##-------------------------------------------------------------------------------------------------------
## 기본 변수 선언. 
##-------------------------------------------------------------------------------------------------------
my $user		= "qes_bsc";
my $bakdir		= "/data1/oracle9i_dmp_files/";
my $fullpath	= $bakdir.$date		."_". $user ."_full.zip";
my $onepath		= $bakdir.$date		."_". $user ."_one.zip";
my $rmfullpath	= $bakdir.$rmdate	."_". $user ."_full.zip";
my $rmonepath	= $bakdir.$rmdate	."_". $user ."_one.zip";
my @nums		= (0..30);


#-------------------------------------------------------------------------------------------------------
# FULL 백업 ....
#-------------------------------------------------------------------------------------------------------
print "\nFULL 백업중.....\n";
my	$command  = "$ENV{ORACLE_HOME}/bin/exp $user/$user\@$ENV{ORACLE_TNSNAME} grants=y ";
	$command .= " file=(";
	for(@nums)
	{
		$command .= $bakdir . $user ."_full_". sprintf("%02s", $_) .".dmp,";
	}
	$command  = substr($command, 0, length($command)-1);
	$command .= " )";
	$command .= " filesize=2G";
	$command .= " log=". $bakdir . $user .".log";

system $command;


##-------------------------------------------------------------------------------------------------------
## 테이블 단위 백업....
##-------------------------------------------------------------------------------------------------------
try
{
	print "\n테이블 단위 백업중.....\n";
	my $conn	= DBI->connect("DBI:Oracle:NEXTBSC", $user, $user, {AutoCommit => 0, RaiseError => 1, PrintError => 0 }) || die "Error :$DBI::errstr";
	my $SQL		= "SELECT tname FROM tab WHERE tname NOT LIKE 'BIN%' ";
	my $stmt	= $conn->prepare($SQL);
	$stmt->execute();

	while(@row = $stmt->fetchrow_array)
	{
		my $command = "";

		if(@row["TNAME"] == "NG01_REPORT_INFO")
		{
			$command  = "$ENV{ORACLE_HOME}/bin/exp qes_bsc/qes_bsc\@$ENV{ORACLE_TNSNAME} grants=y ";
			$command .= " tables=". @row["TNAME"];
			$command .= " file=(";
			for(@nums)
			{
				$command .= $bakdir . @row["TNAME"] ."_one_". sprintf("%02s", $_) .".dmp,";
			}
			$command  = substr($command, 0, length($command)-1);
			$command .= " )";
			$command .= " filesize=2G";
		}
		else
		{
			$command  = "$ENV{ORACLE_HOME}/bin/exp qes_bsc/qes_bsc\@$ENV{ORACLE_TNSNAME} grants=y ";
			$command .= " tables=". @row["TNAME"];
			$command .= " file=". $bakdir . @row["TNAME"] ."_one_". ".dmp";
		}
		system $command;
	}

	$stmt->finish;
	$conn->disconnect;
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## FULL 백업 압축....
##-------------------------------------------------------------------------------------------------------
try
{
	opendir(DIR, $bakdir);
	@files = grep(/_full_\d{2}/, readdir(DIR));
	print $bakdir.@files;
	closedir(DIR);

	if(@files > 0)
	{
		my $zip		= Archive::Zip->new();
		my $comp	= $zip->addDirectory($date);
		$comp->desiredCompressionMethod(COMPRESSION_DEFLATED);
		$comp->desiredCompressionLevel(COMPRESSION_LEVEL_BEST_COMPRESSION);
		print "FULL 백업을 압축중입니다.....!!!\n";

		foreach my $file(sort @files)
		{
			print $file. "\n";
			$comp	= $zip->addFile($bakdir . $file, $date ."/". $file) || die 'write error';
			$check	= true;
		}
		$zip->writeToFileNamed($fullpath) == AZ_OK;
		system "split -b 680m ". $fullpath ." ". $fullpath.".";
	}
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## 테이블 단위 압축....
##-------------------------------------------------------------------------------------------------------
try
{
	opendir(DIR, $bakdir);
	@files = grep(/_one_(\d{2})?/, readdir(DIR));
	closedir(DIR);
	print $bakdir.@files;

	if(@files > 0)
	{
		my $zip		= Archive::Zip->new();
		my $zip		= Archive::Zip->new();
		my $comp	= $zip->addDirectory($date);
		$comp->desiredCompressionMethod(COMPRESSION_DEFLATED);
		$comp->desiredCompressionLevel(COMPRESSION_LEVEL_BEST_COMPRESSION);
		print "\n테이블 단위 백업을 압축중입니다.....!!!\n";

		foreach my $file(sort @files)
		{
			print $file. "\n";
			$comp	= $zip->addFile($bakdir . $file, $date ."/". $file) || die 'write error';
		}
		$zip->writeToFileNamed($onepath) == AZ_OK;
		system "split -b 680m ". $onepath ." ". $onepath.".";
	}
}
catch Error with
{
	my $ex = shift;
	print $ex;
}
finally 
{
	#close_the_garage_door_already();
};


##-------------------------------------------------------------------------------------------------------
## 삭제할 파일
##-------------------------------------------------------------------------------------------------------
system "rm -f ". $rmfullpath;
system "rm -f ". $rmonepath;
system "rm -f ". $bakdir ."*.dmp";


__END__
##-------------------------------------------------------------------------------------------------------
## 실행전에 필용한 팩키지 목록들....
##-------------------------------------------------------------------------------------------------------
##perl -e "use Archive::Zip"
##perl -MCPAN -e "install DBD::Oracle"
##perl -MCPAN -e "install Archive::Zip"
##perl -MCPAN -e shell
##cpan>i /archive::zip/
##cpan>install Archive::Zip
##cpan>quit
##windows --> copy /b XXXX_full_zip.aa + XXXX_full_zip.ab  XXXX_full.zip
##solaris --> cat XXXX_full_zip.?? >  XXXX_full.zip
##linux   --> XXXX_full_zip.* >  XXXX_full.zip

##__author__  = "park17@gmail.com"
##__version__ = "0.1"
##__date__    = "2007-09-29"
##__file__    = "ora_backup.pl"

2G가 이상되면 unzip 명령어가 먹지 않네요..
64비트 옵션을 주고 컴파일 하면 된다고 하는데 안되더라구요..
그래서 귀찮아서 그냥 이것도 Perl 스크립트로 만들었습니다.

file: unzip.pl

#!/usr/bin/perl

use strict;
use Archive::Zip qw(:ERROR_CODES);

my $zip = Archive::Zip->new();
my $zipName = shift(@ARGV);

if($zip->read($zipName) != AZ_OK)
{
    die "Read of $zipName failed\n";
}
else
{
    foreach my $member($zip->members())
    {
        print $member->fileName(), ", ", $member->uncompressedSize(), ":", $member->compressedSize(), "\n";
        $zip->extractMember($member->fileName());
    }
}

유효하지않음

,

procedures 백업하기

Database/Oracle 2009. 3. 12. 14:54

먼저 오라클 클럽에 올라와 있는 글을 조금 참조했음을 알려드립니다.

==> 본인의 글 입니다. 이 글을 다른곳에 게재하는 경우 본문의 출처를 밝혀주시기 바람니다.

procedures_backup.sql

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

SET SERVEROUTPUT ON SIZE 1000000
SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512
SET TERMOUT OFF
SET TERMOUT ON
!rm -rf './proc_backup';
!mkdir './proc_backup';
PROMPT
PROMPT PROCEDURES export Start...!!!!
PROMPT
SET TERMOUT OFF
SPOOL procedures_exp.sql
PROMPT SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF PAGES 0
--PROMPT SET LINESIZE 512;
--LINES 512

DECLARE
    TYPE objectElements IS VARRAY(5) OF VARCHAR2(20);
    VObjectType objectElements	:= objectElements('PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'TYPE');
    vObjectExt  objectElements	:= objectElements('prc', 'spc', 'bdy', 'fnc', 'tps');
	vObjectName	VARCHAR2(50)	:= '';
	vExportDir	VARCHAR2(20)	:= './proc_backup/';
	--vFileHandle	UTL_FILE.FILE_TYPE;
    
    CURSOR cur_procedures(v_object_type VARCHAR2)
    IS
        SELECT  name  --, DBMS_METADATA.GET_DDL(v_object_type, name) text
        FROM    USER_SOURCE
        WHERE   TYPE = v_object_type
        GROUP   BY name;

	--
	-- DBA 권한시 수정하여 사용.(procedures_dump.sql 파일도 수정) 
	--
	/*
    CURSOR cur_procedures(v_owner VARCHAR2, v_object_type VARCHAR2)
    IS
		SELECT  object_name name --, DBMS_METADATA.GET_DDL(v_object_type, object_name, owner) 
		FROM	dba_objects
		WHERE	owner	 	= v_owner
		AND		object_type = v_object_type
		ORDER BY owner
	*/
        
BEGIN
	--DBMS_OUTPUT.ENABLE('10000000000');
     
	FOR i IN VObjectType.FIRST .. VObjectType.LAST
    LOOP
        FOR list_procedures IN cur_procedures(VObjectType(i))
        LOOP
			vObjectName := LOWER(list_procedures.name || '.' || vObjectExt(i));
			DBMS_OUTPUT.put_line('SET TERMOUT ON');
			DBMS_OUTPUT.put_line('PROMPT ' || VObjectType(i) || ' -->> ' || vExportDir || vObjectName);
			DBMS_OUTPUT.put_line('SET TERMOUT OFF');
			DBMS_OUTPUT.put_line('spool ' || vExportDir || vObjectName);
			DBMS_OUTPUT.put_line('@procedures_dump ' || VObjectType(i) || ' ' || list_procedures.name);
			DBMS_OUTPUT.put_line('spool off');

			--vFileHandle := UTL_FILE.fopen(vExportDir, list_procedures.name || '.' || vObjectExt(i), 'W'); 
			--UTL_FILE.put_line(vFileHandle, list_procedures.text); 
			--UTL_FILE.fclose(vFileHandle); 
        END LOOP;
	END LOOP;
	
/*
EXCEPTION 
	WHEN NO_DATA_FOUND THEN 
		DBMS_OUTPUT.put_line('NO_DATA_FOUND');
		UTL_FILE.fclose(vFileHandle); 
		NULL;
	WHEN UTL_FILE.INVALID_PATH THEN 
		DBMS_OUTPUT.put_line('UTL_FILE.INVALID_PATH'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN UTL_FILE.READ_ERROR THEN 
		DBMS_OUTPUT.put_line(' UTL_FILE.READ_ERROR'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN UTL_FILE.WRITE_ERROR THEN 
		DBMS_OUTPUT.put_line('UTL_FILE.WRITE_ERROR'); 
		UTL_FILE.fclose(vFileHandle); 
	WHEN OTHERS THEN 
		DBMS_OUTPUT.put_line('OTHER STUFF'); 
		UTL_FILE.fclose(vFileHandle); 
*/
END;
/
SPOOL OFF;
@procedures_exp.sql;

SET TERMOUT ON
PROMPT
PROMPT PROCEDURES export End...!!!!
PROMPT
SET TERMOUT OFF

QUIT;

procedures_dump.sql

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

SET SERVEROUTPUT ON SIZE 1000000
SET HEAD OFF VERIFY OFF
SET LONG 1000000
COL TEXT FORMAT A132;
	SELECT  TRIM(DBMS_METADATA.GET_DDL('&1', name)) text
	FROM    USER_SOURCE
	WHERE   type = '&1'
	AND		name = '&2'
	GROUP   BY name;

/*
	SELECT  TRIM(DBMS_METADATA.GET_DDL('&2', object_name, '&1')) text
	FROM    dba_objects
	WHERE   owner       = '&1'
	AND     object_type = '&2'
	ORDER BY owner
*/
PROMPT /

procedures_backup.zip

유효하지않음

,

DBMS_scheduler

Database/Oracle 2009. 3. 12. 10:02

[출처] http://radiocom.kunsan.ac.kr/lecture/oracle/package/dbms_scheduler.html

운영체제에서 CRON과 같은 명령어를 통해 사용자가 주기적, 비주기적으로 실행하는 프로그램을 자동으로 실행해주는 기능을 수행하듯이

오라클 RDBMS 기반의 시스템에서는 다양한 stored procedure, package, java class등의 프로그램을 데이터베이스 내에 생성하고, 필요에 따라 실행하게 된다.

이러한 DB 기반의 프로그램을 데이터베이스 네에서 주기적, 비주기적으로 실행해야 하는 경우, 스케쥴러 기능을 사용할 수 있다.

이 기능을 사용하기 위해서는 해당 사용자는 create any job 시스템 권한을 부여 받아야 한다.

DBMS_scheduler 패키지는 4가지 구성요소를 가진다.

program	스케쥴러에 의해 실행될 DB 프로그램
job	스케쥴러에 등록된 작업 유형
schedule	등록된 작업의 작업 일정
window	수행될 작업에 할당될 시스템 자원에 대한 정보

begin
	dbms_scheduler.create_program
	(
		program_name   => 'test_scheduler',
		program_action => '/export/home/work/aaa.exe',
		program_type   => 'executable'
	);
end;
/

새롭게 생성한 프로그램은 scheduler에 의해 사용 가능하도록 활성화 상태이어야 한다.

SQL> execute dbms_scheduler.enable('test_scheduler');

또는 다음과 같이 비활성화시킬 수 도 있다.

SQL> execute dbms_scheduler.disable('test_scheduler');

scheduler 내에 생성되어 있는 프로그램을 삭제한다.

begin
	dbms_scheduler.drop_program
	(
		program_name   => 'test_scheduler',
		force          => false
	);
end;
/

다음은 실행할 프로그램의 스케쥴 계획을 생성한다. 스케쥴 이름과 실행될 시각, 종료시각, 실행주기등을 작성할 수 있다.

begin
	dbms_scheduler.create_schedule
	(
		program_name   => 'hourly_schedule',
		start_date     => systimestamp,
		end_date       => systimestamp + interval '30' day,
		repeat_interval => 'freq=hourly; interval=4',
		comments        => 'every 4 hours'
	); 
end;
/

create_job 프로시져를 이용하면 스케줄과 함깨 실행할 SQL 문장 또는 PL/SQL 블럭을 등록할 수 있다.

begin
	dbms_scheduler.create_job
	(
		job_name   => 'my_emp_job1',
		job_type   => 'PLSQL_BLOCK',
		job_action => 'insert into emp values(7987, "SALLY","ANALYST",
		null,null,null,null,null);',
		start_date     => '09/07/23 17:16:51.927000+09:00',
		repeat_interval => 'freq=daily; interval=2',
		comments        => 'my new job'
	); 
end;
/

다음은 실행될 스케쥴 job을 삭제하는 방법이다.

begin
	dbms_scheduler.drop_job
	(
		job_name   => 'my_emp_job1',
		force      => false
	);
end;
/

이제, check_test라는 PL/SQL 블럭을 생성해서 스케쥴러에 등록한 후, 자동으로 실행해 보자.

create or replace procedure check_test
IS
	v_no     number(4);
	v_name   varchar2(13) := null;
BEGIN
	select	empno, ename into v_no, v_name from scott.emp
	where	empno =7934;
	dbms_output.put_line(v_no||'  '||v_name);
END;
/

BEGIN
	dbms_scheduler.create_job
	(
		job_name    => 'my_emp_job2',
		job_type    => 'PLSQL_BLOCK',
		job_action  => 'BEGIN check_test(); END;',
		schedule_name => 'hourly_schedule'
	);
END;
/
execute dbms_scheduler.enable('my_emp_job2');

scheduler에 의해 활성화된 my_emp_job2를 스케쥴링한다.

execute dbms_scheduler.run_job('my_emp_job2');

BEGIN
	DBMS_SCHEDULER.DROP_JOB
	(
		job_name   => 'my_emp_job2';
		force      => false
	);
END;
/

이번에는 운영체제상에 존재하는 실행파일을 스케쥴러에 등록한 후 자동으로 실행하는 예제

begin
	dbms_scheduler.create_job
	(
		job_name     => 'my_emp_job3',
		job_type     => 'executable',
		job_action   => '/export/home/work/aaa',
		start_date   => systimestamp,
		repeat_interval => 'freq=daily; ingterval=2',
		end_date     => systimestamp + interval '30' day
	);
end;
/

execute dbms_scheduler.enable ('my_emp_job3');
execute dbms_scheduler.run_job('my_emp_job3');
BEGIN
dbms_scheduler.drop_job
(
	job_name => 'my_emp_job3',
	force    => false
);
END;
/

이번에는 실행될 프로그램에 자원관리 기능을 적용하는 방법

begin
	dbms_scheduler.create_window
	(
		window_name   =>'my_window100',
		schedule_name => 'hourly_schedule',
		resource_plan => 'internal_plan',
		duration      => interval '160' minute,
		comments      => 'My window'
	);
END;
/

execute dbms_scheduler.enable ('my_wondow100');
execute dbms_scheduler.run_job('my_emp_job2');

오라클 자료사전의 scheduler와 관련된 자료

col comments format a35
col table_name format a35

select	table_name, comments from dictionary
where	table_name like 'DBA%SCHEDULER%'
order	by table_name;

TABLE_NAME COMMENTS

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

DBA_SCHEDULER_CHAINS All scheduler chains in the database

DBA_SCHEDULER_CHAIN_RULES All rules from scheduler chains in the database

DBA_SCHEDULER_CHAIN_STEPS All steps of scheduler chains in the database

DBA_SCHEDULER_CREDENTIALS All scheduler credentials in the database

DBA_SCHEDULER_GLOBAL_ATTRIBUTE All scheduler global attributes

DBA_SCHEDULER_JOBS All scheduler jobs in the database

DBA_SCHEDULER_JOB_ARGS All arguments with set values of all scheduler jobs in the database

DBA_SCHEDULER_JOB_CLASSES All scheduler classes in the database

DBA_SCHEDULER_JOB_LOG Logged information for all scheduler jobs

DBA_SCHEDULER_JOB_ROLES All scheduler jobs in the database by database role

DBA_SCHEDULER_JOB_RUN_DETAILS The details of a job run

DBA_SCHEDULER_PROGRAMS All scheduler programs in the database

DBA_SCHEDULER_PROGRAM_ARGS All arguments of all scheduler programs in the database

DBA_SCHEDULER_REMOTE_DATABASES List of registered remote databases for jobs

DBA_SCHEDULER_REMOTE_JOBSTATE Remote state of all jobs originating from this database

DBA_SCHEDULER_RUNNING_CHAINS All steps of all running chains in the database

DBA_SCHEDULER_SCHEDULES All schedules in the database

DBA_SCHEDULER_WINDOWS All scheduler windows in the database

DBA_SCHEDULER_WINDOW_DETAILS The details of a window

DBA_SCHEDULER_WINDOW_GROUPS All scheduler window groups in the database

DBA_SCHEDULER_WINDOW_LOG Logged information for all scheduler windows

DBA_SCHEDULER_WINGROUP_MEMBERS Members of all scheduler window groups in the database

22 rows selected.

SQL>

오라클_스케줄러를_이용한_백업정책.pdf

유효하지않음

,

오라클 잡 관리(Oracle job manage)

Database/Oracle 2009. 3. 12. 09:30

[출처] http://ntalbs.tistory.com/3

예전에 database.sarang.net의 오라클 게시판에 DBMS_JOB을 이용해 원하는 작업을 08시, 14시, 20시에 실행시키는 방법을 묻는 질문이 올라왔다. 작업 간격이 규칙적일 때는 문제가 간단하지만 원하는 시간 간격이 불규칙하므로 그냥 JOB을 세 개 등록하면 어떻겠냐고 답했더니 이번에는 이 작업을 평일에만 실행시키게 하고 싶다고 했다. 즉 평일 08시, 14시, 20시에 작업이 실행되도록 하고 싶다는 것이었다. 그럼 문제를 풀기 전에 DBMS_JOB.SUBMIT 프로시저를 살펴보자. DBMS_JOB을 이용해 JOB을 등록시키려면 SUBMIT 프로시저를 사용해야 한다. 파라미터 중 next_date와 interval를 통해 작업 실행 시각을 조절할 수 있다.

DBMS_JOB.SUBMIT (
     job       OUT BINARY_INTEGER,
     what      IN  VARCHAR2,
     next_date IN  DATE DEFAULT sysdate,     -- 실행할 시각
     interval  IN  VARCHAR2 DEFAULT 'null',  -- 다음 실행될 시점을 계산할 수식
     no_parse  IN  BOOLEAN DEFAULT FALSE,
     instance  IN  BINARY_INTEGER DEFAULT any_instance,
     force     IN  BOOLEAN DEFAULT FALSE);

next_date의 디폴트 값은 sysdate이므로 값을 주지 않으면 등록 즉시 실행된다. 그 다음 실행 시각은 JOB이 실행되기 직전 interval에 지정된 수식을 이용해 계산한다. (interval이 NULL일 경우는 작업이 한 번만 실행된다.) 파라미터 이름이 interval이기는 하지만 실제 의미는 interval이 아니라 "다음 실행될 시점을 계산할 수식"인 것이다. 만약 어떤 작업을 1시간에 1번씩 실행시키고 싶다면 interval을 'sysdate+1/24'로 주면 된다. 작업을 시작하기 전에 sysdate+1/24를 통해 다음 실행할 시각을 구하면 작업 시작 시간으로부터 1시간 후인 시각이 된다. 다음 작업 시작 시각을 알고 싶으면 ALL_JOBS의 NEXT_DATE 컬럼을 조회해 확인할 수 있다.

interval	작업 주기
'sysdate + 1/24'	1시간에 1번
'sysdate + 1'	1일에 1번
'sysdate + 7'	7일(일주일)에 한번

그런데 위와 같이 하면 작업 주기만 지정한 것일 뿐이다. 특정 시각에 JOB을 실행시키려면 다음과 같이 하면 된다.

interval	작업 시각
'trunc(sysdate) + 1 + 1/24'	매일 01시에 작업 실행
'trunc(sysdate, ''D'') + 7'	매주 일요일 00시에 작업 실행

interval 파라미터는 문자열로 주어야 하므로 수식 내에 따옴표(single quotation)이 있으면 따옴표를 두 개 써줘야 하는 것에 유의해야 한다. interval 수식이 복잡할 때는 확인하기가 어려울 수 있는데, 그럴 때는 interval 수식으로 직접 쿼리를 작성해 확인할 수 있다.

select trunc(sysdate, 'D') + 7 from dual;

이제 다음과 같이 다양한경우에 대한 interval을 구해보자.

매주 토요일 새벽 1시에 실행
매월 1일 새벽 0시에 실행
매월 말일 밤 11시에 실행
평일(월화수목금) 밤 10시에 실행
불규칙한 시각, 8시, 14시, 20시에 한번씩

...
next_date=>to_date('2007102701','YYYYMMDDHH24'),
interval=>'sysdate + 7'
...

월초나 월말의 경우는 add_months나 last_day를 이용해 구하면 된다.

-- 매월1일 새벽 0시 작업 실행
...
next_date=>add_months(trunc(sysdate,'MM'),1),
interval=>'add_months(trunc(sysdate,''MM''),1)'


-- 매월 말일 밤 11시에 작업 실행
...
next_date=>last_day(trunc(sysdate))+23/24,
interval=>'last_day(trunc(sysdate)+1)+23/24'  -- 말일+1일은 다음달 1일
...

평일만 실행되도록 하기 위해서는 interval이 좀더 복잡해진다.

...
interval=>'trunc(sysdate) + decode(to_char(sysdate,''D''), ''6'', 3, ''7'', 2, 1) + 22/24'
...

요일을 구한 다음 토요일(to_char(sysdate,'D')='6')에는 작업 후 3일 후에, 일요일(to_char(sysdate,'D')='7')에는 작업 후 2일 후에, 평일에는 자업 후 1일 후에 작업이 다시 시작되도록 하면 된다. 이를 위해 DECODE 함수를 활용했다.

불규칙한 시간 간격일 경우에도 작업 시각을 기반으로 DECODE를 활용하면 가능할 것 같다. 그러나 하루 수행 횟수가 서너 번 정도라면 그냥 각 시각마다 실행되도록 서너 개의 JOB을 등록시켜주는 것도 생각해볼 수 있다.

원래 문제는 불규칙한 시각+평일 조건을 만족해야 하므로 하나의 interval 수식으로 해결하려면 수식이 무척 복잡해질 것 같다. interval 수식이 복잡해지면 이해가기도 어려워 진고, 나중에 수정하고 싶을 때 문제가 생길 수도 있다.

참고로 10g부터는 DBMS_JOB 대신 DBMS_SCHEDULER을 쓰도록 권고하고 있다.

[출처] http://blog.naver.com/gnpado/40017478314

.주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다.. JOB을 스케줄링 하기 위해.주기적으로 수행되어지는 JOB을 JOB QUEUE를 사용하여 스케줄링 할 수 있습니다..

JOB을 스케줄링 하기 위해서 ORACLE의 DBMS_JOB 패키지를 이용합니다..

JOB QUEUE PROCESS가 JOB QUEUE 안의 잡을 수행합니다..

JOB으로 등록될 수 있는 것은 PL/SQL 프로시저 또는 패키지가 되며

예를 들어소스 디비의 테이블들에서 타겟 테이블로 데이터를 적재하는 프로시저를 생성했는데

1분단위로 데이터를 타겟 테이블로 적재를 해야 할 때 DBMS_JOBS에 등록하여

스케줄링 할 수 있습니다.

.JOB_QUEUE_PROCESSES 파라미터가 이와 관련된 초기화 파라미터로 0으로 설정되면

JOB QUEUE PROCESS는 시작되지 않으며 JOB QUEUE의 어느 잡도 수행되지 않습니다..

JOB_QUEUE_PROCESSES 파라미터의 MAX값이 설정되어야 오라클 인스턴스 위에서

동시에 잡을 수행할 수 있다. 설정할 수 있는 최고 값은 1000입니다..

JOB_QUEUE_PROCESSES=60 과 같이 설정할 수 있습니다..

.등록되거나 수행되는 잡에 대해서는 DBA_JOBS 또는 USER_JOBS 딕셔너리 뷰를 통해

확인 할 수 있다.

.JOB_QUEUE_PROCESSES 는 다이나믹 하게 DB를 SHUTDOWN하지 않고 ALTER SYSTEM

명령을 이용해서 설정할 수 있습니다.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 20;

.JOB QUEUE안의 JOB을 스케줄링 하기 위해서는 DBMS_JOBS패키지를 사용할 수 있으며

JOB_QUEUE를 사용하기 위해 관련된 DB 권한은 없다.

.다음은 DBMS_JOBS의 패키지를 사용하기 위한 패키지의 프로시져들입니다.

SUBMIT - 잡큐의 잡을 등록합니다.

REMOVE - 잡큐의 잡을 제거합니다.

CHANGE - 잡큐의 잡을 변경합니다.

NEXT_DATE - 잡의 다음 수행시간을 변경합니다.

INTERVAL - 잡 수행 주기를 변경합니다.

WHAT - 잡으로 등록된 프로시저 또는 패키지를 변경합니다.

RUN - 잡을 수동으로 강제로 수행합니다.

.새 JOB을 JOB QUEUE에 등록하기 위해 사용되는 파라미터로 DBMS_JOB.SUBMIT()에 들어가느

파라미터 입니다.

JOB - OUTPUT 파라미터로 생성한 잡에 의해 할당되는 식별자 입니다.

WHAT - JOB QUEUE에 등록되는 PL/SQL 프로시저 또는 패키지 입니다.

NEXT_DATE - 잡이 수행되는 다음 시간입니다.

INTERVAL - 잡이 수행되는 주기로 초 단위까지 지정 가능합니다.

JOB_QUEUE에 등록하는 예제입니다.

VARIABLE jobno NUMBER 
BEGIN 
	DBMS_JOB.SUBMIT
	(
		:jobno, 
		'SP_IN_EMP_SAL;', 
		SYSDATE, 
		'SYSDATE + 1'
	); 
COMMIT; 
END; 
/

위의 PL/SQL문을 SQL PLUS에서 수행합니다.

첫번째 파라미터가 JOB NUMBER가 부여되는 부분이고

두번째 파라미터가 WHAT으로 SP_IN_EMP_SAL이라는 프로시저를 등록했습니다.

세번째 파라미터가 NEXT_DATE이며 4번째 파라미터가 잡 수행 주기로 하루에 한번씩

수행하라는 의미입니다.

DBMS_JOB을 이용하면 특정시간, 특정요일, 특정일, 30초단위, 매분 정각, 매시정각

등 다양하게 잡을 스케줄링 하는 것이 가능합니다.

ETL 수행 할 때도 유용하게 사용할 수 있습니다.

잡 수행 간격 조정의 예

.SYSDATE+ 7 : 7일에 한번씩 잡이 수행됩니다.

.SYSDATE+1/24 : 1시간에 한번씩 잡이 수행됩니다.

.SYSDATE+30/86400 : 30초에 한번씩 잡이 수행됩니다.

.최초 잡 수행시간이 14:02분일 경우 매시 14:02분에 잡을 수행해야 될 경우

=>trunc(SYSDATE,'MI')+1/24

.최조 잡 수행시간이 06시 이고 매 8시간마다 정각에 잡이 수행되어야 될 경우

=>trunc(SYSDATE,'MI')+8/24

.매주 월요일 정각 3시에 잡이 수행되어야 할 경우

=>NEXT_DAY(TRUNC(SYSDATE),'MONDAY')+15/25

.각 분기마다 첫번째 월요일에 잡이 수행되어야 될 경우

=>NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY')

주의> dbms_job으로 잡을 스케줄링하게 될 경우 예를 들어 잡 최초 수행시간이 22일 14:00시이고

매시 정각에 잡이 수행되어야 할 경우 SYSDATE+1/24로 간격을 주게 되면 매 정시에 도는 것이

아니라 잡 수행 시간에 따라 약간씩 늦어지게 되어 14:00:04 => 15:00:07 => 16:00:10 이런식으로

잡 수행시간이 잡히게 됩니다. 따라서 정각에 수행되게 하려면 trunc함수를 이용해서 무조건 분에서

잘라내여 00으로 만들어 준다. trunc(SYSDATE,'MI')

잡의 삭제

 
BEGIN 
	DBMS_JOB.REMOVE(14443); 
END; 
/

14443은 잡 번호 이다. USER_JOBS 데이터 딕셔너리 뷰를 보면 잡 번호를 알 수 있습니다.

잡의 변경

 
BEGIN 
 DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3'); 
 END; 
 /

잡으로 등록된 프로시저/패키지 변경

 
BEGIN 
 DBMS_JOB.WHAT(14144, 
     'SP_IN_EMP_SAL;'); 
END; 
/

잡 다음 수행시간 변경

 
BEGIN 
 DBMS_JOB.NEXT_DATE(14144, TRUNC(SYSDATE,'MI') + 4); 
END; 
/

잡 수행 간격 변경

 
BEGIN 
 DBMS_JOB.INTERVAL(14144, TRUNC(SYSDATE,'MI')+30/1440); 
END; 
/

잡 수행 정지 잡이 BROKEN되면 잡은 수행되지 않으며 강제로 수행 할 시에는 DBMS_JOB.RUN()을 통해 수행합니다..

 
BEGIN 
 DBMS_JOB.BROKEN(14144, TRUE); 
END; 
/

잡큐 정보 VIEWING DBA_JOBS, USER_JOBS, ALL_JOBS를 이용합니다.

 
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN 
FROM DBA_JOBS; 

JOB    NEXT_DATE  NEXT_SEC  FAILURES   B 
------- ---------  --------   --------   - 
9125    01-JUN-01   00:00:00     4       N 
14144   24-OCT-01   16:35:35    0       N 
9127    01-JUN-01   00:00:00    16       Y 
3 rows selected.

DBMS_JOB의 활용예제를 주기적으로 소스테이블에서 타켓 테이블로 적재할 시 어떻게 사용할 수 있는지 간단하게 예를 만들었습니다

   
--수행될 잡의 목록이 들어갈 테이블 
CREATE TABLE JOB_LIST 
(
	JOB_ID VARCHAR2(2), 
	JOB_TYPE VARCHAR2(1), 
	JOB_NAME VARCHAR2(30), 
	JOB_EXEC_HOUR VARCHAR(2), 
	JOB_PARENTID VARCHAR2(2), 
	CONSTRAINTS JOB_LIST_PK PRIMARY KEY(JOB_ID) 
	USING INDEX 
	TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 32K NEXT 32K PCTINCREASE 0) 	NOLOGGING) 
	TABLESPACE CYS_DATA PCTFREE 0 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0
); 
/   

CREATE UNIQUE INDEX JOB_LIST_IDX01 ON JOB_LIST(JOB_NAME,JOB_EXEC_HOUR) 
TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 64K NEXT 64K PCTINCREASE 0); 
/ 

-잡 수행 히스토리 테이블로 하루에 한번씩 JOB_LIST 테이블에서 LOG테이블로 JOB_LIST가 복사된다. 
CREATE TABLE JOB_LOG 
(
	JOB_ID VARCHAR2(2), 
	JOB_EXEC_DATE VARCHAR2(8), 
	JOB_START_TIME DATE, 
	JOB_END_TIME DATE, 
	JOB_DATASTART_TIME DATE, 
	JOB_DATAEND_TIME DATE, 
	JOB_STATUS VARCHAR2(1), 
	JOB_ERR_MSG VARCHAR2(100), 
	CONSTRAINTS JOB_LOG_PK PRIMARY KEY(JOB_ID,JOB_EXEC_DATE) USING INDEX
	TABLESPACE CYS_INDEX PCTFREE 0 STORAGE(INITIAL 128K NEXT 128K PCTINCREASE 0) NOLOGGING, 
	CONSTRAINTS JOB_LIST_FK FOREIGN KEY(JOB_ID) REFERENCES JOB_LIST(JOB_ID)) 
	TABLESPACE CYS_DATA STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) 

 --JOB_ID를 부여하기 위한 시퀀스 
CREATE SEQUENCE JOB_NUM START WITH 1 INCREMENT BY 1 ;

--하루에 한번 DBMS_JOB을 이용하여 JOB_LIST의 JOB들을 JOB_LOG에 INSERT하기 위한 프로시져 
--다음날 수행할 JOB을 LOG로 넣는다. 
CREATE OR REPLACE PROCEDURE SP_IN_JOB_LOG(V_INDATE IN VARCHAR2 DEFAULT NULL) 
AS 
BEGIN 
	INSERT INTO JOB_LOG SELECT JOB_ID, 
		NVL(V_INDATE,TO_CHAR(SYSDATE+1,'YYYYMMDD')), 
		NULL, 
		NULL, 
		NULL, 
		NULL, 
		'N', 
		NULL 
		FROM JOB_LIST; 
	
	COMMIT; 
END SP_IN_JOB_LOG; 

--SP_IN_JOB_LOG 프로시저를 DBMS_JOB에 등록한다. 
DECLARE 
    JOB_NUMBER NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호 
        'SP_IN_JOB_LOG;',  --프로시저명 
         TO_DATE('20050208180000','YYYYMMDDHH24MISS'),  --NEXT_DATE 
        'TRUNC(SYSDATE,''MI'')+1');  --잡 수행 간격(매일 정각 6시) 
END; 

--JOB_LIST에 수행할 프로시져(JOB)을 등록 
--DBA_USERS테이블을 이용해서 24개의 로우를 만들어 낸 후 카테시안 프러덕을 이용 
INSERT INTO JOB_LIST 
SELECT JOB_NUM.NEXTVAL JOB_ID, 
    JOB_TYPE, 
    JOB_NAME, 
    B.CNT JOB_EXEC_HOUR, 
    NULL 
FROM( 
  SELECT NULL JOB_ID, 
      'F' JOB_TYPE, 
      'SP_IN_F_SALE_SUM' JOB_NAME, 
      NULL JOB_EXEC_HOUR, 
      NULL 
  FROM DUAL) A, 
 ( SELECT LPAD(ROWNUM-1,2,'0') CNT FROM DBA_TABLES 
     WHERE ROWNUM<25) B 

COMMIT; 

--JOB_LIST를 JOB_LOG로 INSERT(현재 SP_IN_JOB_LOG 가 다음일을 INSERT하도록 되어 있으므로 해당일을 넣어줌) 
EXEC SP_IN_JOB_LOG('20050208'); 

 --제대로 들어갔는지 확인 
SELECT * FROM JOB_LOG; 

 --SOURCE 테이블을 시간단위로 섬머리 해서 TARGET 테이블로 적재하기 위한 프로시져 
--ERROR없이 매 시간 돌 때는 파라미터 없이 SP_IN_F_SALE_SUM으로 수행 되고 수동으로 어떤 데이터의 
--범위를 적재해야 할 경우 시간의 범위를 파라미터로 넘겨줌 
CREATE OR REPLACE PROCEDURE SP_IN_F_SALE_SUM(V_STARTTIME IN VARCHAR2 DEFAULT NULL, V_ENDTIME IN VARCHAR2 DEFAULT NULL) 
AS 
  D_STARTTIME DATE; 
  D_ENDTIME DATE; 
  V_ERR_MSG VARCHAR2(100); 
BEGIN 
  
 --프로시저가 파라미터 값이 없이 수행될 경우 
 IF V_STARTTIME IS NULL AND V_ENDTIME IS NULL THEN 
  SELECT NVL(JOB_DATAEND_TIME,TRUNC(SYSDATE-1/24,'HH24')) 
  INTO D_STARTTIME 
  FROM JOB_LOG 
  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
             WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
       AND JOB_EXEC_HOUR=TO_CHAR(SYSDATE-1/24,'HH24')) 
  AND JOB_EXEC_DATE=TO_CHAR(SYSDATE,'YYYYMMDD') 
  AND JOB_STATUS='Y'; 

  D_ENDTIME:=TRUNC(SYSDATE,'HH24'); 
 ELSE 
  SELECT NVL(JOB_DATAEND_TIME,TO_DATE(V_STARTTIME,'YYYYMMDDHH24MISS')) 
  INTO D_STARTTIME 
  FROM JOB_LOG 
  WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
             WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
       AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2)) 
  AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8) 
  AND JOB_STATUS='Y'; 

  D_ENDTIME:=TO_DATE(V_ENDTIME,'YYYYMMDDHH24MISS'); 
 END IF; 
    
 --수행되는 프로시저의 START시간을 찍어주고 RUNNING으로 표시 
 UPDATE JOB_LOG 
 SET JOB_START_TIME=SYSDATE, 
  JOB_STATUS='R' 
 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
         WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
      AND JOB_EXEC_HOUR=SUBSTR(V_STARTTIME,9,2)) 
 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
  
 -- DML------ 
  
  --수행되어질 INSERT문 
  
 -- DML----- 
  
 COMMIT; 
  
 --프로시저가 ERROR없이 수행이 끝나면 END 시간과 가져온 데이터의 범위를 찍어줌 
 UPDATE JOB_LOG 
 SET JOB_END_TIME=SYSDATE, 
  JOB_DATASTART_TIME=D_STARTTIME, 
  JOB_DATAEND_TIME=D_ENDTIME, 
  JOB_STATUS='Y' 
 WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
         WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
      AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24')) 
 AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
  
 COMMIT; 
  
EXCEPTION 
   WHEN OTHERS THEN 
      V_ERR_MSG:= SUBSTRB(SQLERRM, 1, 80); 
     
     UPDATE JOB_LOG 
     SET JOB_END_TIME=SYSDATE, 
      JOB_STATUS='E', 
      JOB_ERR_MSG=V_ERR_MSG 
           WHERE JOB_ID=(SELECT JOB_ID FROM JOB_LIST 
                  WHERE JOB_NAME='SP_IN_F_SALE_SUM' 
         AND JOB_EXEC_HOUR=TO_CHAR(D_ENDTIME,'HH24')) 
           AND JOB_EXEC_DATE=SUBSTR(V_ENDTIME,1,8); 
     
     COMMIT; 
END SP_IN_F_SALE_SUM; 
/ 

 --SP_IN_F_SALE_SUM 프로시저를 DBMS_JOB에 등록합니다. 
DECLARE 
    JOB_NUMBER NUMBER; 
BEGIN 
  DBMS_JOB.SUBMIT(JOB_NUMBER,  --JOB번호 
        'SP_IN_F_SALE_SUM;',  --프로시저명 
         TO_DATE('20050209000000','YYYYMMDDHH24MISS'),  --NEXT_DATE 
        'TRUNC(SYSDATE,''MI'')+1/24');  --잡 수행 간격(매시간 정각) 
END;

JOB의 시간이나 간격 등록된 프로시저등을 변경하고자 할 때 DBMS_JOB의 다른 프로시져를 이용해서 변경합니다. 2번째 정리 부분에 설명되어 있습니다.

유효하지않음

,

구글Map 주소로 좌표값얻기 PL/SQL

Database/Oracle 2009. 2. 20. 13:21

네이버는 POST방식도 허용 하던데 구글은 POST로 넘기면 오류메세지에 관련된 XML만 넘어오더군요

그래서 그냥 GET방식으로 처리하니 정상적인 XML값이 넘어오네요..ㅋㅋ

Java로 작업하고 나서 테스트 해볼려고 함 만들었네요..~~

vKey는 구글에서 생성하고 적용하십시요

==> 본인의 글 입니다. 이 글을 다른곳에 게재하는 경우 본문의 출처를 밝혀주시기 바람니다.

/**
  * 구글맵 키 생성 URL
  * http://code.google.com/intl/ko-KR/apis/maps/signup.html
  *
 **/

DECLARE
	vRequest		UTL_HTTP.req;
	vResponse		UTL_HTTP.resp;
	vResponseText	VARCHAR2(4000);
	vErrorText		VARCHAR2(4000);
	vTxt			VARCHAR2(10);
	vAddress		VARCHAR2(1000);
	vCoordinateX	VARCHAR2(100);
	vCoordinateY	VARCHAR2(100);
	vCoordinates	VARCHAR2(100);
	vXml			XMLType;
	i				BINARY_INTEGER	:= 0;
	
	--구글관련 설정내용
	vUrl			VARCHAR2(200) := 'http://maps.google.com/maps/geo';
	vKey			VARCHAR2(300) := 'ABQIAAAApvInj0M12LgrKqPVykdiShTRzhAzSQ3u4E7LYXha2CwyHvWJ_RTAzTyv3xCX_ja0mBuaI1HJQ9QLgQ';
	vQuery			VARCHAR2(200) := '서울구로구구로동222-12';
	vXmlns			VARCHAR2(100) := 'xmlns="http://earth.google.com/kml/2.0';

	--SPLIT 함수호출
	CURSOR STR_SPLIT(str IN VARCHAR2, sp IN VARCHAR2)
	IS
		SELECT COLUMN_VALUE FROM TABLE(SPLIT(str, sp));
		TYPE TABLE_SPLIT IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
	
	aCoordinates	TABLE_SPLIT;

BEGIN

	--vQuery    := convert(vQuery, 'KO16MSWIN949', 'UTF8');
	vUrl      := vUrl || '?q=' || vQuery || CHR(38) || 'gl=KR' || CHR(38) || 'output=xml' || CHR(38) || 'key=' || vKey;

	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line('                         전송한 URL                                ');
	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line(vUrl);
	
	vRequest := UTL_HTTP.begin_request(vUrl,  'GET', 'HTTP/1.1');
	UTL_HTTP.set_header(vRequest, 'Accept-Language',  'ko');
	UTL_HTTP.set_header(vRequest, 'Accept',           'text/xml');
	UTL_HTTP.set_header(vRequest, 'User-Agent',       'Mozilla/4.0');
	UTL_HTTP.set_body_charset('utf8');

	vResponse := UTL_HTTP.get_response(vRequest);

	IF vResponse.status_code = '200'
	THEN
		UTL_HTTP.read_text(vResponse, vResponseText);

		IF vResponseText NOT LIKE 'Result=OK%'
		THEN
			vXml     := XMLType.createXML(TRIM(vResponseText));
			vAddress := NULL;
			i        := 0;

			SELECT vXml.extract('//Response/Placemark/address/text()',           vXmlns).getstringval() INTO vAddress     FROM dual;
			SELECT vXml.extract('//Response/Placemark/Point/coordinates/text()', vXmlns).getstringval() INTO vCoordinates FROM dual;

			IF vAddress IS NOT NULL
			THEN
				FOR COLNAME_LIST IN STR_SPLIT(vCoordinates, ',')
				LOOP
					i := i + 1;
					aCoordinates(i) := COLNAME_LIST.COLUMN_VALUE;
				END LOOP;
				
				IF i > 1 THEN
					vCoordinateX := aCoordinates(1);
					vCoordinateY := aCoordinates(2);
					DBMS_OUTPUT.put_line(CHR(13) || CHR(13));
					DBMS_OUTPUT.put_line('===================================================================');
					DBMS_OUTPUT.put_line('                         주소/좌표 추출                            ');
					DBMS_OUTPUT.put_line('===================================================================');
					DBMS_OUTPUT.put_line('주소   >> ' || vAddress);
					DBMS_OUTPUT.put_line('X 좌표 >> ' || vCoordinateX);
					DBMS_OUTPUT.put_line('Y 좌표 >> ' || vCoordinateY);
				END IF;

			END IF; -- IF vAddress IS NOT NULL End.

			vErrorText := vResponseText;

		END IF; -- IF vResponseText NOT LIKE 'Result=OK%' End.

	ELSE
		vErrorText := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
	END IF; -- IF vResponse.status_code = '200' End.

	DBMS_OUTPUT.put_line(CHR(13) || CHR(13));
	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line('                         전송받은 XML                              ');
	DBMS_OUTPUT.put_line('===================================================================');
	DBMS_OUTPUT.put_line(vErrorText);


	UTL_HTTP.end_response(vResponse);
END;

google_map.zip

유효하지않음

,

ORA-08002: 시퀀스 **.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다

Database/Oracle 2008. 12. 24. 09:47

ORA-08002: 시퀀스 **.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다

NEXTVAL 하기 전까지는 CURRVAL을 동일세션에서 사용할 수 없습니다

굳이 필요하다면 다음과 같이 해야합니다

SELECT LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = '시퀀스명(대문자)'

유효하지않음

,

EXP-00056: ORACLE error

Database/Oracle 2008. 11. 21. 09:47

. exporting table NG01_REPORT_INFO
EXP-00056: ORACLE error 24801 encountered
ORA-24801: illegal parameter value in OCI lob function
. . exporting table NG01_REPORT_LINK 0 rows exported
. . exporting table NG01_SCORE_OF_INFO 72 rows exported
EXP-00002: error in writing to export file
EXP-00056: ORACLE error 1403 encountered
ORA-01403: no data found
. . exporting table NG01_SMS_LOG_INFO
EXP-00015: error on row 96 of table NG01_SMS_LOG_INFO, column SLI_TEXTMSG, datatype 1
EXP-00002: error in writing to export file
EXP-00002: error in writing to export fileerror closing export file 20081120_qes_bsc.dmp
EXP-00000: Export terminated unsuccessfully
$ . . exporting table NG01_PROTEST 142 rows exported
ksh: .: not found
$ . . exporting table NG01_QNA 0 rows exported
ksh: .: not found
$ . . exporting table NG01_REPORT_COMMENT 20138 rows exported
ksh: .: not found
$ . . exporting table NG01_REPORT_INFO
ksh: .: not found

exp qes_bsc/qes_bsc file=(qes_bsc1.dmp,qes_bsc2.dmp,qes_bsc3.dmp) filesize=2G

데이터량이 많은 DB에 대해 export시 다음과 같은 오류가 발생하면 위처럼 dump파일을 여러개 만들면 된다.

유효하지않음

,

정규 표현식을 이용한 SQL 구문의 개선

Database/Oracle 2008. 11. 7. 15:37

Inside Oracle Database 10g

정규 표현식을 이용한 SQL 구문의 개선

저자 - Alice Rischert

Oracle Database 10g의 정규 표현식 기능을 텍스트 데이터 처리를 위한 강력한 도구로 활용할 수 있습니다.

Oracle Database 10g에 추가된 새로운 기능을 이용하여 문자 데이터의 검색, 처리 능력을 극적으로 개선할 수 있습니다. 정규 표현식(regular expression)이라 불리는 이 기능은, 텍스트 패턴을 기술하기 위한 일종의 표기법으로, 이미 오래 전부터 다양한 프로그래밍 언어와 UNIX 유틸리티를 통해 지원되어 왔습니다.

오라클의 정규 표현식은 SQL 함수와 WHERE절 연산자의 형태로 제공됩니다. 정규 표현식에 익숙하지 않은 독자라면, 이 문서를 통해 전혀 새롭고 강력한 기능을 체험하실 수 있을 것입니다. 또 정규 표현식에 이미 친숙한 독자분들은, Oracle SQL 언어의 문맥에 이 기능을 적용하는 방법을 이해하는 기회로 활용하실 수 있습니다.

정규 표현식이란?

정규 표현식은 하나 또는 그 이상의 문자열과 메타문자(metacharacter)로 구성됩니다. 가장 단순한 형태의 정규 표현식은 cat과 같은 단 하나의 문자열로만 구성될 수 있습니다. 이 정규 표현식은 문자 c와 문자 a, 문자 t의 순서를 갖는 패턴 매치 문자열로 cat, location, catalog 등의 문자열과 매치됩니다. 메타문자는 정규 표현식을 구성하는 문자들을 처리하는 방법을 명시하기 위한 알고리즘을 제공합니다. 다양한 메타문자의 의미를 이해한다면, 정규 표현식이 텍스트 데이터를 비교하고 대체하는 용도로 매우 유용하게 활용될 수 있음을 금방 깨닫게 되실 것입니다.

데이터의 검증, 중복 단어의 확인, 불필요한 공백의 제거, 문자의 파싱(parsing) 등 정규 표현식의 활용 방법은 실로 다양합니다. 정규 표현식을 이용하여 전화 번호, 우편 번호, 이메일 주소, 주민등록번호, IP 주소, 파일 이름, 경로 이름 등을 검증할 수도 있습니다. 또 HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능합니다.

Oracle Database 10g에서 정규 표현식 사용하기

오라클에 새로 추가된 기능으로 Oracle SQL REGEXP_LIKE 연산자, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 함수 등이 있습니다. 이 함수와 연산자는 기존의 LIKE 연산자와 INSTR, SUBSTR, REPLACE 함수를 보완하는 효과를 제공합니다. 실제로 새로운 기능들은 기존 연산자 및 함수와 유사하지만 훨씬 강력한 패턴 매칭 환경을 구현하고 있습니다. 검색의 기준이 되는 데이터는 간단한 문자열일 수도 있고 데이터베이스 테이블의 문자 컬럼에 저장된 대량의 텍스트일 수도 있습니다. 정규 표현식을 이용하면 이전에는 생각도 못했던 유연한 방법으로 데이터를 검색, 대체, 검증할 수 있습니다.

정규 표현식의 기본적 예제

새로운 기능을 사용해 보기기 전에, 몇 가지 메타문자의 의미를 이해해 보기로 합시다. 마침표(.)는 정규 표현식에 존재하는 모든 문자(newline 제외)와 매칭됩니다. 예를 들어 정규 표현식 a.b는 문자 a, (newline을 제외한) 임의의 단일 문자, 그리고 문자 b의 순서로 구성된 문자열과 매칭됩니다. 문자열 axb, xaybx, abba는 모두 이 정규표현식에 정의된 패턴을 포함하고 있으므로 매치가 가능합니다. 라인이 a로 시작하여 b로 끝나는, 3 개 문자로 구성된 문자열을 매칭하고자 하는 경우에는 앵커(anchor)가 사용되어야 합니다. 캐럿(^) 메타문자는 라인의 시작을, 달러($) 기호는 라인의 끝을 의미합니다 (표 1 참고). 따라서 정규 표현식 ^a.b$는 aab, abb, axb와 같은 문자열과 매칭됩니다. LIKE 연산자에서 이와 동일한 기능을 수행하려면 a_b 패턴을 사용해야 합니다. 여기서 밑줄 기호(_)는 단문자 와일드카드를 의미합니다.

기본적으로, 정규 표현식의 개별 문자 또는 문자 리스트는 단 한 번만 매칭됩니다. 정규 표현식은 문자가 여러 번 반복 출현되는 조건을 지정하기 위한? 반복 연산자(repetition operator)를 제공합니다("quantifier"라 부르기도 합니다). 문자 a로 시작해서 b로 끝나는 문자열 매칭을 위한 정규 표현식이 아래와 같습니다: ^a.*b$. * 메타문자는 임의의 메타문자(.)가 0 번, 한 번, 또는 여러 번 반복되는 조건에 매칭됩니다. LIKE 연산자에서는 이와 동일한 연산자로 a%b를 지원합니다. 여기서 퍼센트(%) 기호는 임의 문자가 0 번, 한 번, 또는 여러 번 반복됨을 의미합니다.

표 2는 반복 연산자의 전체 목록을 보여 주고 있습니다. 이 표에 제시된 예를 통해 정규 표현식이 기존의 LIKE와일드카드 문자보다 훨씬 뛰어난 유연성을 제공함을 확인할 수 있습니다. 표현식에 괄호를 씌우는 경우, 서브표현식(subexpression)으로 활용됩니다. 서브표현식은 임의의 횟수만큼 반복될 수 있습니다. 예를 들어, 정규 표현식 b(an)*a는 ba, bana, banana, yourbananasplit등과 매치됩니다.

오라클의 정규 표현식은 POSIX(Portable Operating System Interface) 문자 클래스를 지원합니다(표 3 참고). 따라서 검색하는 문자의 유형을 세부적으로 정의하는 것이 가능합니다. 알파벳이 아닌 문자를 검색하는 조건을 LIKE 연산자로 작성한다면, WHERE 절이 훨씬 복잡한 형태로 구현되어야 할 것입니다.

POSIX 문자 클래스는 반드시 대괄호([])로 묶여 져야 합니다. 예를 들어, 정규 표현식 [[:lower:]]는 소문자와 매치되며 d [[:lower:]]{5}는 5 개의 연속적인 소문자와 매치됩니다.

POSIX 문자 클래스와 별도로, 개별 문자를 문자 리스트(character list)에 포함시키는 기능이 제공됩니다. 예를 들어 정규 표현식 ^ab[cd]ef$는 문자열 abcef , abdef와 매치됩니다. 여기서 c 또는 d 두 개의 문자 중 하나가 사용되고 있어야 합니다.

문자 리스트 내부에 위치하는 대부분의 메타문자는 일반 문자로 인식됩니다. 그 예외가 캐럿 (^) 기호와 하이픈 (-)기호입니다. 일부 메타문자는 문맥에 따라 다른 의미를 갖습니다. 이 때문에 정규 표현식이 무척 복잡해 보일 수도 있습니다. 캐럿 ^이 그 한 가지 예입니다. 이 기호를 문자 리스트의 첫 번째 문자로 사용되는 경우에는, 문자 리스트의 반대 조건(negation)을 의미합니다. 따라서[^[:digit:]]은 숫자가 아닌 문자로 구성된 패턴과 매칭되는 반면 ^[[:digit:]] 은 숫자로 시작되는 패턴과 매칭됩니다. 하이픈 (-)은 영역(range)을 의미합니다. 정규 표현식 [a-m]은 a와 m 사이의 임의의 문자와 매칭됩니다. 하지만 [-afg]의 경우처럼 하이픈이 문자 리스트의 첫 번째 문자로 사용된 경우에는 실제 하이픈 문자를 의미합니다

앞에서 괄호를 사용하여 서브표현식을 구현하는 방법을 예시한 바 있습니다. 서브표현식에서는 수직 기호(|)메타문자를 사용하여 여러 개의 대체 문자를 지정할 수 있습니다.

예를 들어, 정규 표현식 t(a|e|i)n은 문자 t와 n 사이에 오는 3 개의 대체 문자를 지정하고 있습니다. tan, ten, tin, Pakistan 등의 문자열은 매치되지만 teen, mountain, tune 등은 매치되지 않습니다. 또 정규 표현식 t(a|e|i)n을 문자 리스트 t[aei]n으로 표현할 수도 있습니다. 표 4는 이러한 메타문자들을 요약하고 있습니다. 지금까지 설명한 것 말고도 다양한 메타문자가 있지만, 여기에서는 본 문서에서 예제로 사용되는 정규 표현식을 이해할 수 있는 정도만 이해하고 넘어가기로 합니다.

REGEXP_LIKE연산자

REGEXP_LIKE오라클 데이터베이스에 적용 가능한 정규 표현식 기능을 제공합니다. 표 5는 REGEXP_LIKE의 문법을 보여주고 있습니다

아래 SQL 쿼리의 WHERE 절에서 사용된 REGEXP_LIKE 연산자는 정규 표현식 [^[:digit:]]을 만족하는 패턴의 ZIP 컬럼을 검색하고 있습니다. 이 조건절을 이용하여, ZIPCODE 테이블로부터 숫자가 아닌 문자를 포함하는 ZIP 컬럼이 포함된 모든 로우를 가져올 수 있습니다.

SELECT zip
FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP
-----
ab123
123xy
007ab
abcxy

이 정규 표현식은 메타문자, 좀 더 정확히 말하면 콜론과 대괄호로 묶인 POSIX 문자 클래스 digit만을 사용하고 있습니다. [^[:digit:]]에서 두 번째로 사용된 대괄호는 문자 클래스 리스트를 묶는 용도로 사용됩니다. 앞에서 설명한 것처럼 POSIX는 문자 리스트를 구성하는 용도로만 사용되므로 이와 같은 처리가 필요합니다.

REGEXP_INSTR 함수

이 함수는 패턴의 시작 위치를 반환하며, 따라서 INSTR 함수와 유사한 형태로 동작합니다. REGEXP_INSTR함수의 사옹 방법은 표 6에서 확인할 수 있습니다. 두 함수의 가장 중요한 차이는 REGEXP_INSTR를 이용하는 경우 특정 문자열이 아닌 패턴을 지정할 수 있으며, 따라서 훨씬 유연한 검색이 가능하다는 사실입니다. 다음 예에서는 REGEXP_INSTR을 사용하여 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234문자열에서 5 개의 숫자로 구성된 우편 번호 패턴의 시작 부분을 반환하고 있습니다. 정규 표현식 [[:digit:]]{5}를 사용하는 경우 우편 번호가 아닌 집 주소 번호의 시작 위치를 얻게 됩니다 (처음으로 검색되는 5 개 연속 숫자 패턴이 10045이기 때문입니다). 따라서 $ 메타문자를 사용하여 표현식의 앵커를 라인 끝부분으로 지정해야 합니다. 이렇게 하면 집 주소 번호에 관계없이 우편 번호의 시작 위치를 얻을 수 있습니다.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
       '[[:digit:]]{5}$')
       AS rx_instr
  FROM dual
  RX_INSTR
----------
        45

좀 더 복잡한 패턴의 작성

앞의 예의 우편 번호 패턴을 확장하여 네 가지 숫자를 포함하는 패턴을 만들어 보기로 합시다. 새로 작성된 패턴이 아래와 같습니다:

[[:digit:]]{5}(-[[:digit:]]{4})?$.

소스 문자열이 5 개 숫자로 종료되든, 또는 "5 개 숫자 + 4 자리 우편 번호" 포맷을 갖든, 패턴의 시작 위치를 얻을 수 있습니다.

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
       ' [[:digit:]]{5}(-[[:digit:]]{4})?$')
    AS starts_at
  FROM dual
STARTS_AT
----------
        44

위의 예에서 괄호로 묶인 서브표현식 (-[[:digit:]]{4})는 반복 연산자 ?로 지정된 조건에 따라 0 회 또는 1 회 반복됩니다. 다시 말하지만, 기존의 SQL 함수를 이용하여 같은 결과를 얻어 내려면 아무리 SQL 전문가라 해도 쉽지 않은 작업이 될 것입니다. 표 7은 정규 표현식을 구성하는 각 문자와 메타문자의 의미를 설명하고 있습니다.

REGEXP_SUBSTR함수

REGEXP_SUBSTR 함수는 SUBSTR 함수와 마찬가지로 문자열의 일부를 추출합니다. 표 8은 새로운 함수의 사용법을 설명하고 있습니다. 아래 예제에서는 [^,]*, 패턴에 매치되는 문자열이 반환됩니다. 정규 표현식은 공백에 이어 사용된 쉼표를 검색하고, 쉼표가 아닌 문자가 0 회 또는 그 이상 반복되는 패턴을 검색([^,]*)한 후 마지막으로 또 다른 쉼표를 검색합니다. 이 패턴은 쉼표로 구분된 문자열(comma-separated values)과 유사한 배열을 갖습니다.

SELECT REGEXP_SUBSTR('first field, second field , third field',
       ', [^,]*,')
  FROM dual
REGEXP_SUBSTR('FIR
------------------
, second field   ,

REGEXP_REPLACE 함수

특정 문자열을 다른 문자열로 대체하는 기존의 REPLACE SQL 함수의 기능을 잠시 되짚어 보겠습니다. 데이터의 텍스트에 필요 이상의 공백 기호가 존재하는 상황에서, 이를 단일 공백 기호로 대체하는 경우를 가정해 봅시다 REPLACE 함수를 사용할 때에는 대체할 공백 기호의 숫자를 정확하게 지정해야 합니다. 하지만, 필요 없는 공백의 수가 일정하리라는 보장은 없습니다. 아래 예는 Joe와 Smith사이에 3 개의 공백 기호가 존재하는 경우를 검색하고 있습니다. REPLACE 함수의 매개변수는 두 개의 공백 기호를 하나의 공백 기호로 대체할 것을 명시하고 있습니다. 하지만 Joe와 Smith 사이에 3 개의 공백 기호가 존재하는 경우에는 여전히 필요 없는 공백이 하나 남게 됩니다.

SELECT REPLACE('Joe   Smith',' ', ' ')
       AS replace
  FROM dual
REPLACE
---------
Joe Smith

REGEXP_REPLACE 한층 개선된 문자열 대체 기능을 제공합니다. 그 사용법은 표 9에서 설명되고 있습니다. 아래 쿼리는 두 개 또는 그 이상의 공백 기호를 하나의 공백 기호로 대체합니다. ( )서브표현식은 하나의 공백 기호를 포함하며 {2,}의 조건에 의해 지정된 대로 2 회 또는 그 이상 반복되는 조건을 명시합니다.

SELECT REGEXP_REPLACE('Joe   Smith',
       '( ){2,}', ' ')
       AS RX_REPLACE
  FROM dual
RX_REPLACE
----------
Joe Smith

백레퍼런스(Backreference)

정규 표현식의 유용한 기능의 하나로 재활용을 위해 서브표현식을 저장하는 기능이 제공됩니다. 이 기능을 백레퍼런스(backreferencing)라 부릅니다(상세한 설명은 표 10 참고). 백레퍼런스를 이용하여 패턴을 새로운 위치에 맞교체하거나 반복적인 단어 또는 문자를 검색하는 등의 고급 대체 기능을 구현할 수 있습니다. 서브표현식과 일치하는 문자열은 임시 버퍼에 저장됩니다. 이 버퍼에는 왼쪽에서 오른쪽 순서로 숫자가 매겨지며 \digit 형태로 표현됩니다. 여기서 digit은 1과 9 사이의 숫자를 의미하며 각 숫자에 해당하는 서브표현식과 매치됩니다.

아래 예제는 서브익스프레션에 대한 백레퍼런스를 이용하여 Ellen Hildi Smith라는 이름을 Smith, Ellen Hildi로 변환하고 있습니다.

SELECT REGEXP_REPLACE(
       'Ellen Hildi Smith',
       '(.*) (.*) (.*)', '\3, \1 \2')
  FROM dual
REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi

위의 SQL 구문은 각각 괄호로 묶인 3 개의 서브표현식을 사용하고 있습니다. 각 서브표현식은 임의의 단일 문자와 매치되는 메타문자(.)와, 임의의 문자(newline 제외)와 0 회 또는 그 이상 매치되는 * 메타문자를 연이어 사용하고 있습니다. 각 서브표현식 사이에 사용된 공백 기호 역시 매치되어야 합니다. 괄호로 묶인 서브표현식에 의해 캡처된 값은 \digit에 의해 참조할 수 있습니다. 따라서 첫 번째 서브표현식에는 \1이, 두 번째 표현식에는\2이 할당됩니다. 이 백레퍼런스들은 함수의 마지막 매개변수 (\3, \1 \2)로 사용되어, 서브문자열을 대체하고 (쉼표와 공백을 포함하는) 포맷으로 표현하는 용도로 활용되고 있습니다. 표 11은 정규 표현식의 개별 컴포넌트에 대한 상세 정보를 설명하고 있습니다.

백레퍼런스는 값을 대체, 포맷하는 용도로 유용하게 활용되며, 서로 인접한 값을 찾기 위해 이용할 수도 있습니다. 다음 예제는 REGEP_SUBSTR 함수를 이용하여 공백 기호로 구분된 알파벳/숫자 값의 중복 사례를 검색하고 있습니다. 이 함수가 반환된 서브문자열을 통해 is 문자열이 반복되고 있음을 알 수 있습니다.

SELECT REGEXP_SUBSTR(
       'The final test is is the implementation',
       '([[:alnum:]]+)([[:space:]]+)\1') AS substr
  FROM dual
SUBSTR
------
is is

매치 매개변수 옵션

앞에서 예시한 예제들을 통해 정규 표현식 연산자와 함수에 매치 매개변수(match parameter)를 추가적으로 활용할 수 있음을 눈치 채셨을 것입니다. 이 매개변수는 대소문자 구분, newline 문자의 매치, 멀티라인 입력의 보존 등을 지원합니다.

정규 표현식의 활용 사례

Y정규 표현식은 단순 쿼리 이외에도 PL/SQL 언어처럼 SQL 연산자, 함수가 사용되는 경우라면 언제든 적용이 가능합니다. 또 정규 표현식을 이용하여 값의 검증, 생성, 추출을 위한 기능을 트리거 형태로 구현할 수 있습니다.

다음 예제는 입력된 데이터에 대해 컬럼 제약 조건을 검증하기 위해 REGEXP_LIKE 연산자를 사용하는 방법을 예시하고 있습니다. 이 쿼리는 INSERT, UPDATE 작업이 발생하는 경우 입력이 사회보장번호의 포맷과 일치하는지 확인합니다. 123-45-6789 또는 123456789과 같은 포맷으로 표현된 사회보장번호는 컬럼의 제약 조건을 만족합니다. 데이터는 3 개의 숫자로 시작하여 하이픈으로 연결된 후, 다시 두 개의 숫자, 하이픈, 마지막으로 4 개의 숫자로 표현됩니다. 또는 사회보장번호를 9 개의 연속적 숫자로 표현하는 것도 가능합니다. 수직 기호(|)는 두 가지 옵션 중 선택이 가능함을 의미합니다.

ALTER TABLE students
  ADD CONSTRAINT stud_ssn_ck CHECK
  (REGEXP_LIKE(ssn,
  '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'))

^, $. 기호는 가장 앞에 또는 가장 뒤에 오는 문자를 무시할 것을 지정하고 있습니다. 정규 표현식이 두 개 이상의 라인으로 분절되거나 표현식에 불필요한 공백 기호가 포함되지 않도록 주의하시기 바랍니다. 표 12는 위 정규 표현식 예제에서 사용된 각각의 컴포넌트에 대해 설명하고 있습니다.

정규 표현식과 기존 SQL 기능의 비교

정규 표현식은 기존에 사용되던 LIKE 연산자와 INSTR, SUBSTR, and REPLACE 함수와 비교했을 때 여러 가지 이점을 제공하고 있습니다. 기존의 SQL 함수는 패턴 매칭을 전혀 지원하지 않고 있습니다. 문자의 매칭을 지원하는 컴포넌트는 LIKE연산자가 유일하며 % , _와일드카드 문자가 지원됩니다. 하지만 LIKE 연산자는 표현식의 반복, 복잡한 대체 패턴, 문자 영역, 문자 리스트, POSIX 문자 클래스 등을 지원하지 않습니다. 또 새로운 정규 표현식 함수는 반복 단어와 패턴의 맞교환(swap)을 지원한다는 장점이 있습니다. 본 문서에서 제시된 예제들이 정규 표현식의 개념과 활용 방법을 이해하는데 도움이 되었기 바랍니다.

툴킷 확장을 위한 유용한 도구

정규 표현식은 복잡한 문제의 해결을 가능하게 하는 매우 강력한 기능입니다. 정규 표현식은 기존 SQL 함수로는 흉내내기 어려운 다양한 기능을 지원합니다. 정규 표현식의 사용법이 다소 복잡해 보이기는 하지만, 그 기본적인 구성 요소만 익혀도 SQL뿐 아니라 다른 프로그래밍 언어에서도 유용한 도구로 활용하실 수 있을 것입니다. 원하는 패턴을 얻기 위해서는 여러 차례의 시행착오가 불가피할 수도 있습니다. 하지만 어느 누구도 정규 표현식의 우아함과 강력한 기능을 결코 무시할 수 없을 것입니다.

Alice Rischert (ar280@yahoo.com)는 콜럼비아 대학 컴퓨터 테크놀로지/애플리케이션 프로그램의 데이터베이스 애플리케이션 개발/설계 트랙 담당 회장입니다. 그녀는 <Oracle SQL Interactive Workbook 2nd edition(Prentice Hall, 2002)>과 조만간 출판 예정인 <Oracle SQL by Example(Prentice Hall, 2003)>의 저자이기도 합니다. Rischert 는 포춘 100대 기업에서 데이터베이스 설계, DBA, 프로젝트 리더로 15 년 간의 경력을 보유하고 있으며 오라클 데이터베이스는 버전 5부터 사용해 온 베테랑입니다.

표 1: 메타문자의 앵커(anchor) 적용

Metacharacter	설명
`^`	라인의 시작 부분에 표현식을 적용
`$`	라인의 끝부분에 표현식을 적용

표 2: 반복 연산자(Repetition Operator, Quantifier)

Quantifier	설명
`*`	0 회 또는 그 이상 횟수로 매치
`?`	0 회 또는 1 회 매치
`+`	1 회 또는 그 이상 횟수로 매치
`{m}`	정확히 m 회 매치
`{m,}`	최소한 m 회 매치
`{m, n}`	최소 m 회, 최대 n 회 매치

표 3: 사전정의된 POSIX 문자 클래스

Character Class	설명
`[:alpha:]`	알파벳 문자
`[:lower:]`	소문자 알파벳 문자
`[:upper:]`	대문자 알파벳 문자
`[:digit:]`	숫자
`[:alnum:]`	알파벳/숫자
`[:space:]`	출력되지 않는 공백 문자(예: carriage return, newline, vertical tab, form feed 등
`[:punct:]`	구두점 기호
`[:cntrl:]`	(출력되지 않는) 컨트롤 문자
`[:print:]`	출력 가능한 문자

표 4: 대체 매칭 및 표현식의 그룹화

Metacharacter		설명
`\|`	Alternation	대체 문자를 구분 (그룹핑 연산자 `()`와 함께 사용하기도 함)
`( )`	Group	반복 연산자 또는 백레퍼런스(backreference)를 위해 대체 유닛을 서브표현식으로 그룹화 ("백레퍼런스" 섹션 참고)
`[char]`	Character list	I문자 리스트. 문자 리스트 내부에 위치하는 대부분의 메타문자는 일반 문자로 인식됨. 그 예외가 캐럿(`^`) 기호와 하이픈(`-`) 기호

표 5: REGEXP_LIKE 연산자

문법	설명
`REGEXP_LIKE(source_string, pattern [, match_parameter])`	`source_string`은 문자 데이터타입 지원 (`CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2`, `NCLOB` 단 `LONG` 은 제외). `pattern`매개변수는 정규 표현식을 참조하는 또 다른 이름. 옵션으로 제공되는 `match_parameter`를 이용하여 newline 문자의 처리, 멀티라인 포맷의 유지, 대소문자 구분 등을 처리할 수 있음.

표 6: REGEXP_INSTR 함수

문법 설명

REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) pattern을 검색하여 패턴의 첫 번째 위치를 반환. 필요한 경우 start_position y을 이용하여 검색을 시작할 위치를 지정할 수 있음. occurrence매개변수는 같은 패턴을 여러 번 검색하고자 하는 경우에 사용(디폴트는 1). return_option은 패턴의 시작 위치를 반환(디폴트 값은 0). 1로 설정되는 경우 매치된 패턴의 다음 문자 위치를 반환.

표 7: 5 자리 숫자 + 4 자리 Zip-Code 표현식에 대한 설명

문법	설명
	매치되어야 하는 빈 공백
`[:digit:]`	POSIX "numeric digit" 클래스
`]`	문자 리스트(character list)의 끝
`{5}`	문자 리스트에 대해 정확히 5 회 반복
`(`	서브표현식(subexpression)의 시작 부분
`-`	하이픈(-) 문자 (문자 리스트 내의 range 메타문자로 사용되지 않았음에 주의)
`[`	문자 리스트의 시작 부분
`[:digit:]`	POSIX `[:digit:]` 클래스
`[`	문자 리스트의 시작 부분
`]`	문자 리스트의 끝 부분
`{4}`	문자 리스트를 정확히 4 회 반복
`)`	서브표현식을 묶는 괄호 기호
`?`	`?` 반복 연산자는 그룹핑된 서브표현식을 0 회 또는 1 회 매치하여 옵션으로 4 자리 코드를 사용할 수 있게 함
`$`	검색 위치를 라인의 끝부분으로 지정하기 위한 앵커 메타문자

표 8: The REGEXP_SUBSTR 함수

문법	설명
`REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]])`	`REGEXP_SUBSTR` 함수는 패턴애 매치되는 서브문자열을 반환.

표 9: TheREGEXP_REPLACE 함수

문법	설명
`REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]])`	매치된 패턴을 지정된 `replace_string`으로 대체하고, 복잡한 검색/대체 작업을 가능하게 하는 함수.

표 10: 백레퍼런스 메타문자 (Backreference Metacharacter)

Metacharacter		설명
`\digit`	Backslash	백슬래시 뒤에 1-9 사이의 숫자를 명시하여, 괄호 처리된 N 번째 서브표현식을 참조하기 위해 사용. (참고: 백슬래시는 정규 표현식에서 다른 의미로 사용되기도 함. 문맥에 따라 Escape 문자를 의미할 수도 있음.

표 11: 패턴-스왑(Pattern-Swap) 정규 표현식의 설명

정규 표현식 아이템	설명
`(`	첫 번째 서브표현식의 시작
`.`	(newline을 제외한) 임의의 단일 문자와 매치
`*`	반복 연산자 - 앞의 `.` 메타문자와 0 회에서 n 회까지 매치
`)`	첫 번째 서브표현식의 마지막. 매치 결과는 `\1` 에 캡처됨(예제에서는 `Ellen`을 캡처)
	공백 기호
`(`	두 번째 서브표현식의 시작
`.`	newline을 제외한 임의의 단일 문자와 매치
`*`	반복 연산자 - 앞의 `.` 메타문자와 0 회에서 n 회까지 매치
`)`	두 번째 서브표현식의 마지막; 매치 결과는 `\2` 에 캡처됨(예제에서는 `Hildi`를 저장).
	공백 기호
`(`	세 번째 서브표현식의 시작
`.`	newline을 제외한 임의의 단일 문자와 매치
`*`	반복 연산자 - 앞의 `.` 메타문자와 0 회에서 n 회까지 매치
`)`	세 번째 서브표현식의 끝 부분. 매치 결과는 `\3` 에 캡처(예제에서는 `Smith`를 저장).

표 12: Social Security Number 정규 표현식의 설명

정규 표현식 아이 템	설명
`^`	라인 문자의 시작(정규 표현식은 매치되기 이전의 문자를 가져 올 수 없음.)
`(`	서브표현식을 시작하고 \| 메타문자를 이용하여 대체 목록을 제공
`[`	문자 리스트(character list)의 시작
`[:digit:]`	POSIX "numeric digit" 클래스
`]`	문자 리스트의 끝 부분
`{3}`	문자 리스트를 정확하게 3 회 반복 적용
`-`	하이픈
`[`	문자 리스트의 시작
`[:digit:]`	POSIX numeric digit 클래스
`]`	문자 리스트의 끝 부분
`{2}`	문자 리스트를 정확하게 2 회 반복 적용
`-`	또 다른 하이픈 기호
`[`	문자 리스트의 시작
`[:digit:]`	POSIX numeric digit 클래스
`]`	문자 리스트의 끝 부분
`{4}`	문자 리스트를 정확하게 4 회 반복 적용
`\|`	대체(alternation) 메타문자 - 첫 번째 선택을 종료하고 두 번째 대체 표현식을 시작
`[`	문자 리스트의 시작
`[:digit:]`	POSIX numeric digit 클래스.
`]`	문자 리스트의 끝 부분
`{9}`	문자 리스트를 정확하게 9 회 반복
`)`	대체를 위해 사용된 서브표현식 그룹을 종료하는 괄호 기호
`$`	라인의 끝부분을 의미하는 앵커 메타문자 (매치된 패턴 이후에 어떤 문자도 뒤따라 올 수 없음)

오라클 정규표현식 예제

REGEXP_SUBSTR

STUDYUNITCODE를 숫자와 문자를 분리하여 나타낸다. (맨 앞에 0은 삭제한다.)

SELECT  
    STUDYUNITCODE,
    REGEXP_SUBSTR(REGEXP_REPLACE(STUDYUNITCODE,'^\0',''),'[[:digit:]]{1,2}') TAPE,
    REGEXP_SUBSTR(STUDYUNITCODE,'[[:alpha:]]{1}') SIDE
FROM TB_STUDYUNIT 
ORDER BY KWON,STUDYUNITCODE

결과값

STUDYUNITCODE	TAPE	SIDE

01A	        1	A
10B	       10	B
12A      	12	A
02B     	 2	B
23A     	23	A
30B     	30	B

페이지정보에서 첫페이지를 찾아서 알려준다.

SELECT SOURCE, REPLACE(REGEXP_SUBSTR(SOURCE,'p[[:digit:]]{1,3}'),'p','') PAGE
FROM QUESTION WHERE ROWNUM < 10;

결과값

SOURCE	                 PAGE

b1: p24	                  24
b1: p19, 24, 32, 46	    19
b3: p5	                   5
b3: p29	                  29
b3: p10-11	           10
b2: p22	                  22
b1: p20 b2: p15 b4: p40  20
b2: p8-9, 30	            8
b3: p16-17	           16

유효하지않음

,

Oracle REGEXP_REPLACE

Database/Oracle 2008. 11. 6. 16:34

SELECT REGEXP_REPLACE(TEST,'(ire_v0[0-2][0-9]*)', 'decode(\1, null, 0, \1)') REG_REP
FROM
(
SELECT '((ire_v001/ire_v002*100)*0.5+(ire_v003/ire_v020*100)*0.5)' TEST
FROM dual
)

--> 결과
((decode(ire_v001, null, 0, ire_v001)/decode(ire_v002, null, 0, ire_v002)*100)*0.5+(decode(ire_v003, null, 0, ire_v003)/decode(ire_v020, null, 0, ire_v020)*100)*0.5)

-- 정규식 테스트(위 내용과 관계없음.)

WITH tmp AS

(

SELECT 1 idx, '(90)abcdefg' txt FROM DUAL UNION ALL

SELECT 2, '(80) abcdefg' FROM DUAL UNION ALL

SELECT 3, '70맒ㄴ;람낢ㄴ' FROM DUAL UNION ALL

SELECT 4, '60 ㄻㅇㄴㄻㄴㅇㄹ' FROM DUAL UNION ALL

SELECT 5, '50 ㅁㄴㄻㄴㄴㅁ 40' FROM DUAL UNION ALL

SELECT 6, 'ㅎㅎㅎㅎㅎㅎㅎㅎ' FROM DUAL UNION ALL

SELECT 7, 'ㅋㅋㅋㅋㅋㅋㅋ 40' FROM DUAL

)

SELECT idx, txt, regexp_replace(txt, '^($[0-9$]+)(.*)', '\1점 \2')

FROM tmp

WHERE regexp_like(txt, '^($[0-9$])');

ELECT oi_sub_linkedcode, oi_sub_linkedcodename

FROM

(

SELECT REGEXP_SUBSTR(oi_sub_linkedcodes, '[^,]+', 1, LEVEL) oi_sub_linkedcode

,REGEXP_SUBSTR(oi_sub_linkedcodenames, '[^,]+', 1, LEVEL) oi_sub_linkedcodename

FROM (SELECT oi_sub_linkedcodes, oi_sub_linkedcodenames FROM NG02_ORGAN_INFO WHERE oi_code = 57)

CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(oi_sub_linkedcodes, '[^,]+')) + 1

)

WHERE oi_sub_linkedcode = '50901240003'

SELECT TRIM(COLUMN_VALUE) str

FROM (SELECT 'aaaa,bbbb,cccc,dddd' str FROM DUAL)

,XMLTABLE(('"' || REPLACE(str, ',', '","') || '"'))

SELECT COLUMN_VALUE oi_sub_linkedcodes

FROM TABLE(SPLIT((SELECT REGEXP_REPLACE(REGEXP_REPLACE('50901240001,50901240002,50901240003,50901240004,50901240005,50901240006,50901240007', '(50901240001|50901240002|50901240004|50901240005|50901240008)', ','), '([^0-9],)', '') FROM DUAL)))

SELECT oi_sub_linkedcodes

FROM

(

SELECT COLUMN_VALUE oi_sub_linkedcodes FROM TABLE(SPLIT('50901240001,50901240002,50901240003,50901240004,50901240005,50901240006,50901240007', ','))

MINUS

SELECT COLUMN_VALUE oi_sub_linkedcodes FROM TABLE(SPLIT('50901240001,50901240002,50901240004,50901240005,50901240008', ','))

)

그 밖에
REGEXP_LIKE
REGEXP_INSTR
REGEXP_SUBSTR 등이 존재하니 참고하시기 바랍니다.

http://www.psoug.org/reference/regexp.html

http://www.adminschool.net/wiki/doku.php?id=dbms:oracle:devel:regexp

유효하지않음

,

9i EXP-00003

Database/Oracle 2008. 10. 8. 13:44

Full Link: http://www.anysql.net/en/oracle/oracle_bug_exp00003.html

When you use old version of exp to export tables with LOB column from Oracle 9.2.0.5 or higher version, you will get an error "EXP-00003 : no storage definition found for segment .....", actually this is an Oracle bug, you could temporary get it resolved by replace a view "exu9tne", as following:

Before exporting, run the following SQL under sys:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

After exporting, run the following to restore the view definition according to Metalink Notes.

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1

유효하지않음

,

Memory Notification: Library Cache Object loaded into SGA

Database/Oracle 2008. 9. 30. 16:25

조치방법: (다음과 같이 조치후 서버 restart)
sqlplus “/as sysdba”
alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
shutdown immediate
startup

원인: (Cause 항목 참조)
해당 오류메시지는 Error 가 아닌 Warning 입니다.
이는 shared pool 영역중 libraray cache 영역의 단편화가 심해 Free Memory를 찾는데 오랜 시간이 걸릴 때
위와 같은 오류가 발생합니다.
새로운 트랜잭션이 발생시 SQL의 파싱결과 등이 저장되는 library cache영역에 단편화로 인한 메시지로
10g R2의 메모리 관리 메커니즘에 따른 메시지로 보시면됩니다.

=======================================================================
Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.
Symptoms
The following messages are reported in alert.log after 10g Release 2 is installed.
       Memory Notification: Library Cache Object loaded into SGA
       Heap size 2294K exceeds notification threshold (2048K)
Changes
Installed / Upgraded to 10g Release 2
Cause
These are warning messages that should not cause the program
responsible for these errors to fail.
They appear as a result of new event messaging mechanism
and memory manager in 10g Release 2. The meaning is that the
process is just spending a lot of time in finding free memory
extents during an allocate as the memory may be heavily fragmented.
Real memory messages should be ORA-403x when a real memory allocation problem
occurs.
Solution
In 10g we have a new undocumented parameter that sets the KGL heap size
warning threshold.   This parameter was not present in 10gR1.
Warnings are written if heap size exceeds this threshold.

Set _kgl_large_heap_warning_threshold to a reasonable high value or zero
to prevent these warning messages. Value needs to be set in bytes.
If you want to set this to 8192 (8192 * 1024) and are using an spfile:
(logged in as "/ as sysdba")
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;
SQL> shutdown immediate SQL> startup
SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608
If using an "old-style" init parameter,
Edit the init parameter file and add
_kgl_large_heap_warning_threshold=8388608
In 10.2.0.2, the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.
References

유효하지않음

,

오라클 내장함수

Database/Oracle 2008. 9. 16. 17:05

1. 문자 함수

1-1) CHR

1-2) CONCAT 함수

1-3) INITCAP 함수

1-4) LOWER 함수

1-5) LPAD 함수

1-6) LTRIM 함수

1-7) NLS_INITCAP 함수

1-8) NLS_LOWER 함수

1-9) NLSSORT 함수

1-10) NLS_UPPER 함수

1-11) REPLACE 함수

1-12) RPAD 함수

1-13) RTRIM 함수

1-14) SOUNDEX 함수

1-15) SUBSTR 함수

1-16) TRANSLATE 함수

1-17) TREAT 함수

1-18) TRIM 함수

1-19) UPPER 함수

1-20) ASCII 함수

1-21) INSTR 함수

1-22) LENGTH 함수

2. 날짜 처리함수(datetime function)

2-1) ADD_MONTHS 함수

2-2) CURRENT_DATE 함수

2-3) URRENT_TIMESTAMP 함수

2-4) DBTIMEZONE 함수

2-5) EXTRACT(datetime) 함수

2-6) FROM_TZ 함수

2-7) LAST_DAY 함수

2-8) LOCALTIMESTAMP 함수

2-9) MONTHS_BETWEEN 함수

2-10) NEW_TIME 함수

2-11) NEXT_DAY 함수

2-12) NUMTODSINTERVAL 함수

2-13) NUMTOYMINTERVAL 함수

2-14) ROUND(date) 함수

2-15) SESSIONTIMEZONE 함수

2-16) SYS_EXTRACT_UTC 함수

2-17) SYSDATE 함수

2-18) SYSTIMESTAMP 함수

2-19) TO_DSINTERVAL 함수

2-20) TO_TIMESTAMP 함수

2-21) TO_TIMESTAMP_TZ 함수

2-22) TO_YMINTERVAL 함수

2-23) TRUNC(date) 함수

2-24) TZ_OFFSET 함수

3.데이터 형 변환 함수(conversion function)

3-1) ASCIISTR 함수

3-2) BIN_TO_NUM 함수

3-3) CAST 함수

3-4) CHARTOROWID 함수

3-5) COMPOSE 함수

3-6) CONVERT 함수

3-7) HEXTORAW 함수

3-8) NUMTODSINTERVAL 함수

3-9) NUMTOYMINTERVAL 함수

3-10) RAWTOHEX 함수

3-11) RAWTONHEX 함수

3-12) ROWIDTOCHAR 함수

3-13) ROWIDTONCHAR 함수

3-14) TO_CHAR(character) 함수

3-15) TO_CLOB 함수

3-16) TO_DSINTERVAL 함수

3-17) TO_LOB 함수

3-18) TO_MULTI_BYTE 함수

3-19) TO_NCHAR(character) 함수

3-20) TO_NCHAR(datetime) 함수

3-21) TO_NCHAR(number) 함수

3-22) TO_NCLOB 함수

3-23) TO_NUMBER 함수

3-24) TO_SINGLE_BYTE 함수

3-25) TO_YMINTERVAL 함수

3-26) TRANSLATE ... USING 함수

3-27) UNISTR 함수

4. 기타함수(miscellaneous single row function)

4-1) BFILENAME 함수

4-2) COALESCE 함수

4-3) DECODE 함수

4-4) DEPTH 함수

4-5) DUMP 함수

4-6) EMPTY_BLOB 함수

4-7) EMPTY_CLOB 함수

4-8) EXISTSNODE 함수

4-9) EXTRACT(XML) 함수

4-10) EXTRACTVALUE 함수

4-11) GREATEST 함수

4-12) LEAST 함수

4-13) NLS_CHARSET_DECL_LEN 함수

4-14) NLS_CHARSET_ID 함수

4-15) NLS_CHARSET_NAME 함수

4-16) NULLIF 함수

4-17) NVL2 함수

4-18) PATH 함수

4-19) SYS_CONNECT_BY_PATH 함수

4-20) SYS_CONTEXT 함수

4-21) SYS_DBURIGEN 함수

4-22) SYS_EXTRACT_UTC 함수

4-23) SYS_GUID 함수

4-24) SYS_XMLAGG 함수

4-25) SYS_XMLGEN 함수

4-26) UID 함수

4-27) USER 함수

4-28) USERENV 함수

4-29) VSIZE 함수

4-30) XMLAGG 함수

4-31) XMLCOLATTVAL 함수

4-32) XMLCONCAT 함수

4-33) XMLFOREST 함수

4-34) XMLELEMENT 함수

5.그룹함수 Aggregate 함수

5-1) AVG* 함수

5-2) CORR* CORR* 함수

5-3) COUNT* 함수

5-4) COVAR_POP 함수

5-5) COVAR_SAMP 함수

5-6) CUME_DIST 함수

5-7) DENSE_RANK 함수

5-8) FIRST 함수

5-9) GROUP_ID 함수

5-10) Grouping 함수

5-11) GROUPING_ID 함수

5-12) LAST 함수

5-13) MAX 함수

5-14) MIN 함수

5-15) PERCENTILE_CONT 함수

5-16) PERCENTILE_DISC 함수

5-17) PERCENT_RANK 함수

5-18) RANK 함수

5-19) REGR_(linear regression) function* 함수

5-20) STDDEV 함수

5-21) STDDEV_POP 함수

5-22) STDDEV_SAMP 함수

5-23) SUM 함수

5-24) VAR_POP 함수

5-25) VAR_SAMP 함수

5-26) VARIANCE 함수

5-27) Grouping sets 함수

6. Analytic 함수

6-1) AVG* 함수

6-2) CORR* CORR* 함수

6-3) COUNT* 함수

6-4) COVAR_SAMP 함수

6-5) CUME_DIST 함수

6-6) DENSE_RANK 함수

6-7) FIRST 함수

6-8) FIRST_VALUE 함수

6-9) LAG 함수

6-10) LAST_VALUE 함수

6-11) LEAD 함수

6-12) NTILE 함수

6-13) RATIO_TO_REPORT 함수

6-14) ROW_NUMBER 함수

7. 객체 참조 함수

7-1) REF 타입

8. PseudoColumn을 의미하는 것

8-1) ROWID 컬럼

8-2) ROWNUM 컬럼

1-1) CHR 함수

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

입력된 수의 바이너리 코드에 해당하는 문자를 반환한다.

【예제】

SQL> select chr(75)||chr(79)||chr(82)||chr(69)||chr(65)

2 from dual;

CHR(7

-----

KOREA

SQL>

1-2) CONCAT 함수

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

입력되는 두 문자열을 연결하여 반환한다.

입력되는 두 문자열의 타입이 다를 경우 다음과 같이 반환된다.

첫 번째 문자열 타입 두 번째 문자열 타입 반환되는 문자열 타입 CLOB NCLOB NCLOB NCLOB NCHAR NCLOB NCLOB CHAR NCLOB NCHAR CLOB NCLOB

【예제】

SQL> select concat('Republic of',' KOREA') from dual;

CONCAT('REPUBLICO

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

Republic of KOREA

SQL>

1-3) INITCAP 함수

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

initcap('string‘) 함수는 입력 문자열 중에서

각 단어의 첫 글자를 대문자로 나머지는 소문자로 변환한 스트링을 반환한다

【예제】

SQL> select initcap('beautiful corea') from dual;

INITCAP('BEAUTI

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

Beautiful Corea

SQL>

1-4) LOWER 함수

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

lower(string) 함수는 입력된 문자열을 소문자로 반환한다.

【예제】

SQL> select lower('Beautiful COREA') from dual;

LOWER('BEAUTIFU

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

beautiful corea

SQL>

1-5) LPAD 함수

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

lpad(char1,n,char2) 함수는

지정된 길이 n에서 문자 char1으로 채우고

남은 공간은 왼쪽부터 char2로 채워서 출력한다.

【형식】

lpad (char1, n [, char2] )

【예제】

SQL> select lpad ('Corea', 12, '*') from dual;

LPAD('COREA'

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

*******Corea

SQL>

1-6) LTRIM 함수

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

LTRIM(문자열, 문자)함수는 문자열중

좌측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.

【형식】

ltrim(char [,set] )

【예제】

SQL> select LTRIM('xyxXxyLAST WORD','xy') from dual;

LTRIM('XYXXX

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

XxyLAST WORD

SQL>

1-7) NLS_INITCAP 함수

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

nls_initcap(‘string’) 함수는 입력 문자열 중에서

각 단어의 첫 글자를 대문자로

나머지는 소문자로 변환한 스트링을 반환한다.

단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.

nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,

여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】

nls_initcap ( char [,'nlsparam'] )

【예제】

SQL> select nls_initcap('beautiful corea', 'nls_sort=binary')

2 from dual;

NLS_INITCAP('BE

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

Beautiful Corea

SQL> select nls_initcap('beautiful corea','nls_sort=XDutch')

2 from dual;

NLS_INITCAP('BE

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

Beautiful Corea

SQL>

1-8) NLS_LOWER 함수

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

nls_lower(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.

단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.

nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,

여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】

nls_lower ( char [,'nlsparam'] )

【예제】

SQL> select nls_lower('CITTA''','nls_sort=XGerman') from dual;

NLS_LO

------

citta'

SQL>

1-9) NLSSORT 함수

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

nlssort(‘string’) 함수는 입력 문자열을 소팅하여 스트링을 반환한다.

단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.

nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,

여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】

nlssort ( char [,'nlsparam'] )

【예제】

SQL> select * from emp

2 order by nlssort(name, 'nls_sort=XDanish');

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1104 jijoe 220 100

1103 kim 250 100

SQL>

1-10) NLS_UPPER 함수

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

nls_upper(‘string’) 함수는 입력 문자열을 모두 소문자로 변환한 스트링을 반환한다.

단어의 white space나 character가 알파뉴메릭이 아니더라도 상관없다.

nlsparam는 ‘nls_sort = sort'와 같은 형식을 가지며,

여기서 sort는 linguistic sort sequence나 binary중의 하나이다.

【형식】

nls_upper ( char [,'nlsparam'] )

【예제】

SQL> select nls_upper('gro?e') from dual;

NLS_U

-----

gro?e

SQL> select nls_upper('gro?e','nls_sort=XGerman')

2 from dual;

NLS_UP

------

grosse

SQL>

1-11) REPLACE 함수

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

이 함수는 문자열에서 지정한 문자를 다른 문자로 치환한다.

치환될 문자를 지정하지 않으면 해당 문자를 삭제한다.

【형식】

replace (char, search_string [, replacement_string] )

【예제】

SQL> select replace('aaabb','a','b') from dual;

REPLA

-----

bbbbb

SQL> select replace('aaabb','a') from dual;

RE

--

bb

SQL>

1-12) RPAD 함수

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

rpad(char1,n,char2) 함수는 지정된 길이 n에서 문자 char1으로 채우고

남은 공간은 오른쪽부터 char2로 채워서 출력한다.

【형식】

rpad (char1, n [, char2] )

【예제】

SQL> select rpad('Corea',12,'*') from dual;

RPAD('COREA'

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

Corea*******

SQL>

1-13) RTRIM 함수

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

RTRIM(문자열, 문자)함수는 문자열중

우측으로부터 특정문자와 일치하는 문자를 제거하고 출력한다.

【형식】

rtrim(char [,set] )

【예제】

SQL> select RTRIM('BROWINGyxXxy','xy') "RTRIM example" from dual;

RTRIM exam

----------

BROWINGyxX

SQL>

1-14) SOUNDEX 함수

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

soundex(‘char’) 함수는 char과 같은 발음의 이름을 표현한다.

【예제】

SQL> select name from emp;

NAME

----------

Cho

Joe

kim

jijoe

SQL> select name from emp

2 where soundex(name) = soundex('jo');

NAME

----------

Joe

SQL>

1-15) SUBSTR 함수

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

substr(str,m,n) 함수는 문자열 str 중에서 특정 위치 m으로부터 특정 길이n 만큼의 문자를 출력한다.

m이 0이나 1이면 문자열의 첫글자를 의미하고,

n이 생략되면 문자열의 끝까지를 의미한다.

m이 음수이면 뒤쪽으로부터의 위치를 의미한다.

SUBSTRB는 character 대신 byte를 사용하고,

SUBSTDC는 unicode를 사용하며,

SUBSTR2는 UCS2 codepoint를 사용하고,

SUBSTR4는 UCS4 codepoint를 사용한다.

【형식】

{SUBSTR|SUBSTRB|SUBSTRC|SUNBSTD2|SUBSTR4}

( string, position [,substring_length] )

【예제】

SQL> select substr('abcdesfg', 3,2) from dual;

SU

--

cd

SQL> select substr('abcdefg',3) from dual;

SUBST

-----

cdefg

SQL> select substr('abcdefg', -3,2) from dual; ☜ 뒤에서 3번째부터 2글자를 의미한다.

SU

--

ef

SQL>

1-16) TRANSLATE 함수

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

TRANSLATE (‘char’,‘from_string’,‘to_string’) 함수는

char 내에 포함된 문자중 from_string에 지정한 모든 각각의 문자를

to_string문자로 각각 변경한다.

【형식】

TRANSLATE ('char','from_string','to_string')

【예제】

SQL> select translate('ababccc','c','d') from dual;

TRANSLA

-------

ababddd

SQL> select translate('2KRW229',

2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

3 '9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX') from dual;

TRANSLA

-------

9XXX999

SQL> select translate('2KRW229',

2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789')

3 from dual;

TRAN

----

2229

SQL>

1-17) TREAT 함수

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

TREAT 함수는 선언된 타입을 변경함으로써, 수퍼타입을 서브타입인 것처럼 처리할 수 있도록 한다.

【형식】

TREAT ( expr AS [ REF] [schema . ] type )

【예제】

SQL> select x.p.empno from person_table p;

select x.p.empno from person_table p

*

ERROR at line 1:

ORA-00904: "X"."P"."EMPNO": invalid identifier

SQL> select treat(x.p as employee).empno empno,

2 x.p.last_name last_name

3 from person_table x;

EMPNO LAST_NAME

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

Seoul

1234 Inchon

5678 Arirang

SQL>

【예제】

SQL> select name, TREAT(VALUE(p) AS employee_t).salary salary

2 FROM person p;

NAME SALARY

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

Bob

Joe 100000

Tim 1000

SQL>

1-18) TRIM 함수

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

이 함수는 LTRIM과 RTRIM 함수를 결합한 형태로

문자값의 왼쪽 또는 오른쪽 부분에 정의한 문자를 절삭하여 출력한다.

LEADING은 LTRIM처럼 문자열 왼쪽의 문자를 지정하여 절삭하고,

TRAILING은 RTRIM처럼 문자열 오른쪽 문자를 지정하여 절삭한다.

BOTH는 왼쪽과 오른쪽 문자를 지정하여 절삭한다.

【형식】

TRIM ([{{{LEADING|TRAILING|BOTH} [trim_char] } | trim_char} FROM]

trim_source )

【예제】

SQL> select trim (0 from 000123400) from dual;

TRIM

----

1234

SQL> select trim(trailing 'a' from 'abca') from dual;

TRI

---

abc

SQL> select trim(leading 'a' from 'abca') from dual;

TRI

---

bca

SQL> select trim(both 'a' from 'abca') from dual;

TR

--

bc

SQL>

1-19) UPPER 함수

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

upper(string) 함수는 입력된 문자열을 대문자로 반환한다.

【예제】

SQL> select upper('Beautiful COREA') from dual;

UPPER('BEAUTIFU

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

BEAUTIFUL COREA

SQL>

1-20) ASCII 함수

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

ASCII

ascii(‘char’) 함수는 주어진 char의 첫 글자의 아스키 값을 반환한다.

char의 타입은 char, varchar2, nchar, nvarchar2중의 하나이어야 한다.

【예제】

SQL> select ascii('Korea') from dual;

ASCII('KOREA')

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

75

SQL> select ascii('K') from dual;

ASCII('K')

----------

75

SQL>

1-21) INSTR 함수

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

이 함수는 문자 스트링 중에서

지정한 문자가 가장 처음 나타나는 위치를 숫자로 출력한다.

【형식】

{INSTR|INSTRB|INSTRC|INSTR2|INSTR4}

( string, substring [, position [,occurrence] ] )

【예제】

SQL> select instr('Corea','e') from dual;

INSTR('COREA','E')

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

4

SQL> select instr('corporate floor','or',3,2) from dual;

INSTR('CORPORATEFLOOR','OR',3,2)

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

14

SQL> select instrb('corporate floor','or',5,2) from dual;

INSTRB('CORPORATEFLOOR','OR',5,2)

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

14

SQL>

1-22) LENGTH 함수

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

LENGTH(char) 함수는 char의 길이를 반환한다.

LENGTHB는 character 대신 byte를 사용하고,

LENGTHC는 unicode를 사용하며,

LENGTH2는 UCS2 codepoint를 사용하고,

LENGTH4는 UCS4 codepoint를 사용한다.

【형식】

{LENGTH| LENGTHB| LENGTHC| LENGTH2| LENGTH4} (char)

【예제】

SQL> select length('Corea') from dual;

LENGTH('COREA')

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

5

SQL> select lengthb('Corea') from dual;

LENGTHB('COREA')

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

5

SQL>

2-1) ADD_MONTHS 함수

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

ADD_MONTHS

ADD_MONTHS(d, n)는 날짜 d에 n 개월을 더한 일자를 반환한다.

【예제】

SQL> select current_date today, add_months(current_date,1) "next month"

2 from dual;

TODAY next mont

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

29-JUL-04 29-AUG-04

SQL>

2-2) CURRENT_DATE 함수

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

이 함수는 현재 session의 날짜 정보를 반환한다.

【예제】

SQL> select current_date from dual;

CURRENT_D

---------

31-JUL-04

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

+09:00

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select current_date from dual;

CURRENT_DATE

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

31-JUL-2004 09:31:57

SQL> alter session set time_zone='-5:0';

Session altered.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

-05:00

SQL>

2-3) URRENT_TIMESTAMP 함수

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

이 함수는 현재 session의 날짜와 시간 정보를 반환한다.

current_timestamp는 time zone까지 출력되지만,

localtimestamp는 time zone은 출력되지 않는다.

【예제】

SQL> select current_timestamp, localtimestamp,

2 current_date from dual;

CURRENT_TIMESTAMP

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

LOCALTIMESTAMP

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

CURRENT_D

---------

04-AUG-04 11.17.40.768776 AM +09:00

04-AUG-04 11.17.40.768776 AM

04-AUG-04

SQL>

2-4) DBTIMEZONE 함수

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

데이터베이스 timezone을 반환한다.

【예제】

SQL> select dbtimezone from dual;

DBTIME

------

-07:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE

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

+09:00

SQL>

2-5) EXTRACT(datetime) 함수

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

특정 날짜/시간 값이나 날짜 값을 가진 표현식으로부터

원하는 날짜 영역을 추출하여 출력한다.

【형식】

EXTRACT ({year|month|day|hour|minute|second|

timezone_hour|timezone_minute|

timezone_region|timezone_abbr}

FROM {datetime_value_expr|interval_value_rxpr})

【예제】

SQL> select extract(year from date '2004-8-2') from dual;

EXTRACT(YEARFROMDATE'2004-8-2')

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

2004

SQL>

2-6) FROM_TZ 함수

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

이 함수는 timestamp 값을 timestamp with time zone 값으로 변환한다.

【형식】

FROM_TZ ( timestamp_value, time_zone_value)

【예제】

SQL> select from_tz(timestamp '2004-8-11 08:00:00','3:00') from dual;

FROM_TZ(TIMESTAMP'2004-8-1108:00:00','3:00')

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

11-AUG-04 08.00.00.000000000 AM +03:00

SQL>

2-7) LAST_DAY 함수

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

이 함수는 지정한 달의 마지막 날을 출력한다.

【형식】

LAST_DAY ( date )

【예제】

SQL> select sysdate, last_day(sysdate) "last day",

2 last_day(sysdate)- sysdate "Days Left"

3 from dual;

SYSDATE last day Days Left

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

04-AUG-04 31-AUG-04 27

SQL>

2-8) LOCALTIMESTAMP 함수

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

이 함수는 timestamp의 현재 날짜와 시각을 출력한다.

current_timestamp는 time zone까지 출력되지만,

localtimestamp는 time zone은 출력되지 않는다.

【형식】

localtimestamp [(timestamp_precision)]

【예제】

SQL> select current_timestamp, localtimestamp,

2 current_date from dual;

CURRENT_TIMESTAMP

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

LOCALTIMESTAMP

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

CURRENT_D

---------

04-AUG-04 11.17.40.768776 AM +09:00

04-AUG-04 11.17.40.768776 AM

04-AUG-04

SQL>

【예제】오류가 발생하는 이유를 잘 이해하자.

SQL> CREATE TABLE local_test(col1 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

SQL> INSERT INTO local_test VALUES

2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));

(TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))

*

ERROR at line 2:

ORA-01830: date format picture ends before converting entire input string

SQL> INSERT INTO local_test VALUES

2 (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

1 row created.

SQL> select * from local_test;

COL1

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

04-AUG-04 11.33.58.183398 AM

SQL>

2-9) MONTHS_BETWEEN 함수

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

MONTHS_BETWEEN(date1,date2) 함수는 date1과 date로 나타내는

날짜와 날짜 사이의 개월 수를 출력한다.

【예제】

SQL> select months_between

2 (to_date('02-02-2004','MM-DD-YYYY'),

3 to_date('01-01-2003','MM-DD-YYYY') ) "Months"

4 FROM dual;

Months

----------

13.0322581

SQL>

2-10) NEW_TIME 함수

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

NEW_TIME(date,zone1,zone2) 함수는 date, zone1 시간대를 zone2 시간대로 출력한다.

여기서 사용되는 zone은 다음 중의 하나이다.

AST,ADT : Atlantic Standard or Daylight Time

BST,BDT : Bering Standard or Daylight Time

CST,CDT : Central Standard or Daylight Time

EST,EDT : Eastern Standard or Daylight Time

GMT : Greenwich Mean Time

HST,HDT : Alaska-Hawaii Standard or Daylight Time

MST,MDT : Mountain Standard or Daylight Time

NST : Newfoundland Standard Time

PST,PDT : Pacific Standard or Daylight Time

YST,YDT : Yukon Standard or Daylight Time

【예제】

SQL> alter session set nls_date_format =

2 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select NEW_TIME(TO_DATE(

2 '11-10-04 01:23:33', 'MM-DD-YY HH24:MI:SS'),

3 'AST', 'PST') FROM DUAL;

NEW_TIME(TO_DATE('11

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

09-NOV-2004 21:23:33

SQL>

2-11) NEXT_DAY 함수

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

NEXT_DAY(date,char) 함수는 date로부터 char로 명시한 가장 최근의 날짜를 출력한다.

【예제】

SQL> select next_day('02-AUG-2004','MONDAY') from dual;

NEXT_DAY('02-AUG-200

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

09-AUG-2004 00:00:00

SQL>

2-12) NUMTODSINTERVAL 함수

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

NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.

char_expr은 다음 중의 하나이다.

‘DAY’

‘HOUR’

‘MINUTE’

‘SECOND’

【예제】

SQL> select numtodsinterval(100,'MINUTE') from dual;

NUMTODSINTERVAL(100,'MINUTE')

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

+000000000 01:40:00.000000000

SQL>

【예제】

SQL> connect scott/tiger

Connected.

SQL> select ename, hiredate,

2 numtodsinterval(100,'day')+hiredate from emp;

ENAME HIREDATE NUMTODSIN

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

SMITH 17-DEC-80 27-MAR-81

ALLEN 20-FEB-81 31-MAY-81

WARD 22-FEB-81 02-JUN-81

JONES 02-APR-81 11-JUL-81

MARTIN 28-SEP-81 06-JAN-82

BLAKE 01-MAY-81 09-AUG-81

CLARK 09-JUN-81 17-SEP-81

SCOTT 19-APR-87 28-JUL-87

KING 17-NOV-81 25-FEB-82

TURNER 08-SEP-81 17-DEC-81

ADAMS 23-MAY-87 31-AUG-87

JAMES 03-DEC-81 13-MAR-82

FORD 03-DEC-81 13-MAR-82

MILLER 23-JAN-82 03-MAY-82

14 rows selected.

SQL>

2-13) NUMTOYMINTERVAL 함수

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

NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.

char_expr은 다음 중의 하나이다.

‘YEAR’

‘MONTH’

【예제】

SQL> select numtoyminterval(30,'month') from dual;

NUMTOYMINTERVAL(30,'MONTH')

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

+000000002-06

SQL>

【예제】

SQL> connect scott/tiger

Connected.

SQL> select ename,hiredate,

2 numtoyminterval(30,'month')+hiredate from emp;

ENAME HIREDATE NUMTOYMIN

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

SMITH 17-DEC-80 17-JUN-83

ALLEN 20-FEB-81 20-AUG-83

WARD 22-FEB-81 22-AUG-83

JONES 02-APR-81 02-OCT-83

MARTIN 28-SEP-81 28-MAR-84

BLAKE 01-MAY-81 01-NOV-83

CLARK 09-JUN-81 09-DEC-83

SCOTT 19-APR-87 19-OCT-89

KING 17-NOV-81 17-MAY-84

TURNER 08-SEP-81 08-MAR-84

ADAMS 23-MAY-87 23-NOV-89

JAMES 03-DEC-81 03-JUN-84

FORD 03-DEC-81 03-JUN-84

MILLER 23-JAN-82 23-JUL-84

14 rows selected.

SQL>

2-14) ROUND(date) 함수

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

이 함수는 날짜를 주어진 형식으로 반올림하는 함수이다.

날짜 형식이 없으면 가장 가까운 날을 출력한다.

【형식】

ROUND( date [,fmt] )

【예제】

SQL> select localtimestamp, round(sysdate,'year') from dual;

LOCALTIMESTAMP

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

ROUND(SYS

---------

04-AUG-04 01.26.24.197977 PM

01-JAN-05

SQL> select localtimestamp,round(sysdate,'day') from dual;

LOCALTIMESTAMP

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

ROUND(SYS

---------

04-AUG-04 01.29.57.839269 PM

08-AUG-04

SQL> select localtimestamp,round(sysdate) from dual;

LOCALTIMESTAMP

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

ROUND(SYS

---------

04-AUG-04 01.30.11.552050 PM

05-AUG-04

SQL>

2-15) SESSIONTIMEZONE 함수

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

이 함수는 현재 세션의 시간대역을 출력한다.

【예제】

SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE

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

CURRENT_TIMESTAMP

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

+09:00

04-AUG-04 01.37.13.355873 PM +09:00

SQL> select sessiontimezone, tz_offset(sessiontimezone) from dual;

SESSIONTIMEZONE

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

TZ_OFFS

-------

+09:00

SQL>

2-16) SYS_EXTRACT_UTC 함수

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

sys_extract_utc(datetime_with_timezone) 함수는

UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.

【예제】

SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;

SYSTIMESTAMP

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

SYS_EXTRACT_UTC(SYSTIMESTAMP)

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

06-AUG-04 02.41.39.258976 PM +09:00

06-AUG-04 05.41.39.258976 AM

SQL>

2-17) SYSDATE 함수

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

이 함수는 오늘 현재 날짜와 시각을 출력한다.

【예제】

SQL> select sysdate, current_timestamp from dual;

SYSDATE

---------

CURRENT_TIMESTAMP

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

04-AUG-04

04-AUG-04 01.51.39.767156 PM +09:00

SQL> select to_char

2 (sysdate, 'MM-DD-YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'MM

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

08-04-2004 13:53:18

SQL>

2-18) SYSTIMESTAMP 함수

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

이 함수는 시스템의 날짜를 출력한다.

【예제】

SQL> select sysdate,systimestamp,localtimestamp from dual;

SYSDATE

---------

SYSTIMESTAMP

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

LOCALTIMESTAMP

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

04-AUG-04

04-AUG-04 01.58.06.346528 PM +09:00

04-AUG-04 01.58.06.346552 PM

SQL>

2-19) TO_DSINTERVAL 함수

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

이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.

【형식】

to_dsinterval ( char [ ‘nlsparam’] )

【예제】

SQL> select sysdate,

2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"

3 from dual;

SYSDATE 3days 17h

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

04-AUG-04 08-AUG-04

SQL>

2-20) TO_TIMESTAMP 함수

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

이 함수는 문자열을 timestamp 형식으로 변환하여 출력한다

【형식】

to_timestamp ( char [,fmt ['nlsparam'] ] )

【예제】

SQL> select to_timestamp('2004-8-20 1:30:00', 'YYYY-MM-DD HH:MI:SS')

2 from dual;

TO_TIMESTAMP('2004-8-201:30:00','YYYY-MM-DDHH:MI:SS')

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

20-AUG-04 01.30.00.000000000 AM

SQL>

2-21) TO_TIMESTAMP_TZ 함수

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

이 함수는 문자열을 timestamp with time zone 형식으로 변환하여 출력한다.

【형식】

to_timestamp_tz ( char [,fmt ['nlsparam'] ] )

【예제】

SQL> select to_timestamp_tz('2004-8-20 1:30:00 -3:00',

2 'YYYY-MM-DD HH:MI:SS TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2004-8-201:30:00-3:00','YYYY-MM-DDHH:MI:SSTZH:TZM')

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

20-AUG-04 01.30.00.000000000 AM -03:00

SQL>

2-22) TO_YMINTERVAL 함수

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

TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.

【예제】

SQL> select sysdate,

2 sysdate+to_yminterval('01-03') "15Months later"

3 from dual;

SYSDATE 15Months

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

04-AUG-04 04-NOV-05

SQL>

2-23) TRUNC(date) 함수

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

이 함수는 날짜를 절삭하여 출력한다.

【형식】

TRUNC ( date [.fmt] )

【예제】

SQL> select trunc(to_date('27-AUG-04','DD-MON-YY'), 'YEAR')

2 from dual;

TRUNC(TO_

---------

01-JAN-04

SQL>

2-24) TZ_OFFSET 함수

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

이 함수는 time zone의 offset 값을 출력한다.

【형식】

TZ_OFFSET ( {‘time_zone_name’ | SESSIONTIMEZONE | DBTIMEZONE |

‘{+|-} hh:mi’ } )

【예제】

SQL> select sessiontimezone, tz_offset('ROK') from dual;

SESSIONTIMEZONE

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

TZ_OFFS

-------

+09:00

SQL>

3-1) ASCIISTR 함수

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

asciistr('string')의 string의 아스키 문자로 반환한다.

A

【예제】

SQL> select ascii('ABACDE') from dual;

☜ ABACDE의 두번째 A는 A에 움라우트(Umlaut)가 붙은 글씨이다.

ASCIIS

------

ABDCDE

SQL>

3-2) BIN_TO_NUM 함수

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

이 함수는 2진수 벡터를 10진수로 변환한다.

SQL> select bin_to_num(1,0,1,0) from dual;

BIN_TO_NUM(1,0,1,0)

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

10

SQL>

3-3) CAST 함수

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

데이터형식이나 collection 형식을 다른 데이터형식이나 다른 collection 형식으로 변환한다.

【예제】데이터형식인 경우

SQL> select current_date from dual;

CURRENT_D

---------

30-JUL-04

SQL> select cast(current_date as timestamp) from dual;

CAST(CURRENT_DATEASTIMESTAMP)

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

30-JUL-04 12.29.15.000000 PM

SQL>

3-4) CHARTOROWID 함수

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

이 함수는 char, varchar2, nchar, ncharvar2형 데이터 타입을 rowid 형 데이터 타입으로 변경한다.

【예제】

SQL> select name from emp

2 where rowid = chartorowid('AAAHZ+AABAAAMWiAAF');

NAME

----------

jijoe

SQL> select rowid,name from emp;

ROWID NAME

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

AAAHZ+AABAAAMWiAAA Cho

AAAHZ+AABAAAMWiAAB Joe

AAAHZ+AABAAAMWiAAC kim

AAAHZ+AABAAAMWiAAF jijoe

SQL>

여기서 rowid의 의미는 다음과 같다.

AAAHZ+ AAB AAAMWi AAA 객체번호 테이블스페이스번호 블록번호 행번호

3-5) COMPOSE 함수

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

입력된 스트링을 unicode로 나타낸다.

【예제】

SQL> select compose('aa' || unistr('\0308') ) from dual;

CO

--

aa

SQL>

3-6) CONVERT 함수

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

입력된 문자열을 지정한 코드로 변환한다.

공용 문자셋은 살펴보자.

US7ASCII US 7-bit ASCII 문자 WE8DEC 서유럽 8비트 문자 WE8HP HP 서유럽 레이져젯 8비트 문자 F7DEC DEC 프랑스 7비트 문자 WE8EBCDIC500 IBM 서유럽 EBCDIC 코드 페이지 500 WE8PC850 IBM PC 코드 페이지 850 WE8ISO8859P1 ISO 8859 서유럽 8비트 문자

【예제】

SQL> select convert('arirang','we8pc850') from dual;

CONVERT

-------

arirang

SQL>

3-7) HEXTORAW 함수

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

HEXTORAW(char) 함수는 char, varchar2, nchar, nvarchar2 따위의 문자로 주어지는

hexadecimal digit을 raw 값으로 변환한다.

【예제】

SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL

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

7D

SQL>

3-8) NUMTODSINTERVAL 함수

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

NUMTODSINTERVAL(n,'char_expr') 함수는 n을 interval day to second로 변환하여 출력한다.

char_expr은 다음 중의 하나이다.

‘DAY’

‘HOUR’

‘MINUTE’

‘SECOND’

【예제】

SQL> select numtodsinterval(100,'MINUTE') from dual;

NUMTODSINTERVAL(100,'MINUTE')

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

+000000000 01:40:00.000000000

SQL>

【예제】

SQL> connect scott/tiger

Connected.

SQL> select ename, hiredate,

2 numtodsinterval(100,'day')+hiredate from emp;

ENAME HIREDATE NUMTODSIN

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

SMITH 17-DEC-80 27-MAR-81

ALLEN 20-FEB-81 31-MAY-81

WARD 22-FEB-81 02-JUN-81

JONES 02-APR-81 11-JUL-81

MARTIN 28-SEP-81 06-JAN-82

BLAKE 01-MAY-81 09-AUG-81

CLARK 09-JUN-81 17-SEP-81

SCOTT 19-APR-87 28-JUL-87

KING 17-NOV-81 25-FEB-82

TURNER 08-SEP-81 17-DEC-81

ADAMS 23-MAY-87 31-AUG-87

JAMES 03-DEC-81 13-MAR-82

FORD 03-DEC-81 13-MAR-82

MILLER 23-JAN-82 03-MAY-82

14 rows selected.

SQL>

3-9) NUMTOYMINTERVAL 함수

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

NUMTOYMINTERVAL(n,'char_expr') 함수는 n을 interval year to month로 변환하여 출력한다.

char_expr은 다음 중의 하나이다.

‘YEAR’

‘MONTH’

【예제】

SQL> select numtoyminterval(30,'month') from dual;

NUMTOYMINTERVAL(30,'MONTH')

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

+000000002-06

SQL>

【예제】

SQL> connect scott/tiger

Connected.

SQL> select ename,hiredate,

2 numtoyminterval(30,'month')+hiredate from emp;

ENAME HIREDATE NUMTOYMIN

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

SMITH 17-DEC-80 17-JUN-83

ALLEN 20-FEB-81 20-AUG-83

WARD 22-FEB-81 22-AUG-83

JONES 02-APR-81 02-OCT-83

MARTIN 28-SEP-81 28-MAR-84

BLAKE 01-MAY-81 01-NOV-83

CLARK 09-JUN-81 09-DEC-83

SCOTT 19-APR-87 19-OCT-89

KING 17-NOV-81 17-MAY-84

TURNER 08-SEP-81 08-MAR-84

ADAMS 23-MAY-87 23-NOV-89

JAMES 03-DEC-81 03-JUN-84

FORD 03-DEC-81 03-JUN-84

MILLER 23-JAN-82 23-JUL-84

14 rows selected.

SQL>

3-10) RAWTOHEX 함수

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

RAWTOHEX(raw) 함수는 raw 값을 hexadecimal 값으로 변환한다.

【예제】

SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL

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

7D

SQL> select rawtohex(raw_col) from test;

RAWTOHEX(RAW_COL)

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

7D

SQL>

3-11) RAWTONHEX 함수

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

RAWTONHEX(raw) 함수는 raw 값을 nvarchar2형 hexadecimal 값으로 변환한다.

【예제】

SQL> create table test(raw_col RAW(10));

Table created.

SQL> insert into test VALUES (HEXTORAW('7D'));

1 row created.

SQL> select * from test;

RAW_COL

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

7D

SQL> select rawtonhex(raw_col) from test;

RAWTONHEX(RAW_COL)

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

7D

SQL>

3-12) ROWIDTOCHAR 함수

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

RAWIDTOCHAR(rowid) 함수는 rowid 값을 varchar2 형식의 데이터로 변환한다.

【예제】

SQL> select rowid from test;

ROWID

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

AAAHbHAABAAAMXCAAA

SQL> select rowid from test

2 where rowidtochar(rowid) like '%AABAA%';

ROWID

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

AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.

AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호

3-13) ROWIDTONCHAR 함수

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

RAWIDTONCHAR(rowid) 함수는 rowid 값을 nvarchar2 형식의 데이터로 변환한다.

【예제】

SQL> select rowid from test;

ROWID

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

AAAHbHAABAAAMXCAAA

SQL> select rowid from test

2 where rowidtochar(rowid) like '%AABAA%';

ROWID

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

AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)

2 from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID

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

36 AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.

AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호

3-14) TO_CHAR(character) 함수

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

이 함수는 nchar, nvarchar2, clob, nclob 형식의 데이터를

데이터베이스 character set으로 변환한다. 즉, 문자로 변환한다.

【형식】

TO_CHAR( nchar| clob | nclob)

【예제】

SQL> select to_char('01110') from dual;

TO_CH

-----

01110

SQL>

3-15) TO_CLOB 함수

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

이 함수는 LOB 컬럼에 있는 NCLOB나 또는 다른 문자 스트링을 CLOB로 변환한다.

【형식】

TO_CLOBR({ lob_column | char})

【예제】

SQL> select to_clob('corea') from dual;

TO_CLOB('COREA')

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

corea

SQL>

3-16) TO_DSINTERVAL 함수

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

이 함수는 문자열을 interval day to second 형식으로 변환하는 함수이다.

【형식】

to_dsinterval ( char [ ‘nlsparam’] )

【예제】

SQL> select sysdate,

2 sysdate+to_dsinterval('003 17:00:00') as "3days 17hours later"

3 from dual;

SYSDATE 3days 17h

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

04-AUG-04 08-AUG-04

SQL>

3-17) TO_LOB 함수

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

TO_LOB(long_column) 함수는 LONG, LONG RAW 컬럼의 데이터를 LOB 값으로 변환한다.

【예제】

SQL> create table test2(zz clob);

Table created.

SQL> insert into test2

2 (select to_lob(p.raw_col) from test p);

SQL>

3-18) TO_MULTI_BYTE 함수

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

TO_MULTI_BYTE(char) 함수는 싱글 바이트 문자를 포함한 모든 문자열을 다중바이트 문자열로 변환한다.

【예제】

SQL> select dump(to_multi_byte('Corea')) from dual;

DUMP(TO_MULTI_BYTE('COREA'))

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

Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225

SQL>

3-19) TO_NCHAR(character) 함수

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

이 함수는 문자스트링, clob, nclob 형식의 데이터를 national character set,

즉 nchar으로 변환한다. 이는 translate ... using 문의 경우와 같다.

【형식】

TO_NCHAR({char|clob|nclob} [,fmt [,'nlsparam']])

【예제】

SQL> select to_nchar('Corea') from dual;

TO_NC

-----

Corea

SQL>

3-20) TO_NCHAR(datetime) 함수

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

이 함수는 date, timestamp, timestamp with time zone, timestamp with local time zone,

interval month to year, interval day to second 형식의 데이터를

nchar 형식의 데이터로 변환한다.

【형식】

TO_NCHAR({datetime|interval} [,fmt [,'nlsparam']])

【예제】

SQL> select to_nchar(sysdate) from dual;

TO_NCHAR(SYSDATE)

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

05-AUG-04

SQL>

3-21) TO_NCHAR(number) 함수

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

이 함수는 숫자를 nvarchar2 형식의 데이터로 변환한다.

【형식】

TO_NCHAR(n [,fmt [,'nlsparam']])

【예제】

SQL> select to_nchar(1234) from dual;

TO_N

----

1234

SQL> select to_nchar(rownum) from test;

TO_NCHAR(ROWNUM)

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

1

SQL>

3-22) TO_NCLOB 함수

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

이 함수는 clob, 문자열 형식의 데이터를 nclob 형식의 데이터로 변환한다.

【형식】

TO_NCLOB({char|lob_column})

【예제】

SQL> select to_nclob('Corea') from dual;

TO_NCLOB('COREA')

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

Corea

SQL>

3-23) TO_NUMBER 함수

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

이 함수는 숫자를 포함하는 char, varchar2, nchar, nvarchar2 형식의

문자 데이터를 number 형식의 숫자 데이터로 변환한다.

【형식】

TO_NUMBER(char [,fmt [,'nlsparam']])

【예제】

SQL> select to_number('1234') from dual;

TO_NUMBER('1234')

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

1234

SQL>

3-24) TO_SINGLE_BYTE 함수

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

TO_SINGLE_BYTE(char) 함수는 다중 바이트 문자열을 single byte 문자로 변환한다.

【예제】

SQL> select dump(to_multi_byte('Corea')) from dual;

DUMP(TO_MULTI_BYTE('COREA'))

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

Typ=1 Len=10: 163,195,163,239,163,242,163,229,163,225

SQL> select dump(to_single_byte('Corea')) from dual;

DUMP(TO_SINGLE_BYTE('COREA'))

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

Typ=1 Len=5: 67,111,114,101,97

SQL> select to_single_byte(chr(65)) from dual;

T

-

A

SQL>

3-25) TO_YMINTERVAL 함수

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

TO_YMINTERVAL ( char ) 함수는 문자열을 interval year to month 형식으로 변환하는 함수이다.

【예제】

SQL> select sysdate,

2 sysdate+to_yminterval('01-03') "15Months later"

3 from dual;

SYSDATE 15Months

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

04-AUG-04 04-NOV-05

SQL>

3-26) TRANSLATE ... USING 함수

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

이 함수는 텍스트 형식의 데이터를 지정한 문자 형식의 데이터로 변환한다.

【형식】

TRANSLATE ( text USING {CHAR_CS|NCHAR_CS} )

【예제】

SQL> select translate('Corea' USING char_cs) from dual;

TRANS

-----

Corea

SQL> select to_nchar('Corea') from dual;

TO_NC

-----

Corea

SQL>

3-27) UNISTR 함수

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

UNISTR('string') 함수는 스트링 형식의 데이터를 nchar 형식의 데이터로 변환한다.

【예제】

SQL> select unistr('abc\00e5\00f1\00f6') from dual;

UNISTR

------

abc??o

SQL> select unistr('Corea') from dual;

UNIST

-----

Corea

SQL>

4-1) BFILENAME 함수

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

서버 파일 시스템 상에 실제로 위치한 LOB 바이너리 파일의 위치한 BFILE locator를 반환한다.

【형식】

bfilename ('디렉토리‘,’파일이름‘)

【예제】BFILE을 insert하는 예

SQL> connect system/manager

SQL> host mkdir /export/home/oracle/bfile

SQL> create directory bfile_dir as '/export/home/oracle/bfile';

Directory created.

SQL> grant read on directory bfile_dir to jijoe;

Grant succeeded.

SQL> connect jijoe/joe_password

SQL> create table bfile_doc (id number, doc bfile);

SQL> insert into bfile_doc

1 values(1111,bfilename('bfile_dir','unix.hwp'));

1 row created.

SQL>

4-2) COALESCE 함수

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

이 함수는 나열된 값 중에서 NULL이 아닌 첫 번째 값을 반환한다.

【예제】

SQL> select coalesce('','','arirang','kunsan') from dual;

COALESC

-------

arirang

SQL>

4-3) DECODE 함수

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

DECODE는 일반적인 프로그래밍 언어의 IF문을 SQL 문자 또는 PL/SQL 안으로 끌어들여 사용하기 위하여 만들어진 오라클 함수이다.

따라서 일반 프로그래밍 언어의 IF문이 수행 할 수 있는 기능을 포함하고 있다.

select시의 decode 사용은 from 절만 빼고 어디에서나 사용할 수 있다.

일반 프로그래밍과 decode 함수를 서로 비교하여 보자.

IF문 Decode 함수 IF A=B THEN RETURN 'T';END IF; DECODE(A,B,'T') IF A=B THENRETURN 'T';ELSIF A=C THENRETURN 'F';ELSERETURN 'X';END IF; DECODE(A,B,'T',C,'F','X')

【형식】

DECODE(검색컬럼,조건1,결과값1,

조건2,결과값2,...,기본값);

【예제】

SQL> connect jijoe/jijoe_password

SQL> create table aa(

2 pid number(12) primary key,

3 addr varchar2(20),

4 name varchar2(10));

SQL> insert into aa values(1234,'kunsan','jijoe')

SQL> insert into aa values(3456,'seoul','sunny')

SQL> select * from aa;

PID ADDR NAME

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

1234 kunsan jijoe

3456 seoul sunny

SQL> select decode(pid,1234,name) name from aa;

NAME

----------

jijoe

SQL>

【예제】

SQL> desc ddd

Name Null? Type

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

NO NUMBER(4)

NAME VARCHAR2(10)

HIRDATE DATE

DEPTNO NUMBER(5)

SQL> select * from ddd;

NO NAME HIRDATE DEPTNO

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

1 student1 01-JAN-04 10

2 student2 01-FEB-04 10

3 student3 01-MAR-04 20

4 student4 01-MAY-04 30

SQL> select count(decode(to_char(hirdate,'MM'),'01',1)) "JAN",

2 count(decode(to_char(hirdate,'MM'),'02',1)) "FEB",

3 count(decode(to_char(hirdate,'MM'),'03',1)) "MAR",

4 count(*) "Total"

5 from ddd

6 where to_char(hirdate,'MM') >= '01' AND

7 to_char(hirdate,'MM') <= '06';

JAN FEB MAR Total

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

1 1 1 4

SQL>

4-4) DEPTH 함수

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

DEPTH( correlation_integer) 함수는 UNDER_PATH나 EQUALS_PATH 조건과 함께 사용되는 보조함수이다.

이 함수는 UNDER_PATH 조건에 상관관계의 수치를 반환한다.

【예제】

SQL> select * from resource_view;

SQL> select path(1), depth(2)

2 from resource_view

3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1

4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;

PATH(1) DEPTH(2)

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

/xml.xsd 1

SQL>

4-5) DUMP 함수

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

지정한 데이터의 위치와 길이 따위를 지정한 형식으로 반환한다.

【형식】

DUMP(expr [,반환형식[,시작위치[,길이]]] )

【예제】

SQL> select dump('Corea', 1016) from dual;

DUMP('COREA',1016)

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

Typ=96 Len=5 CharacterSet=KO16KSC5601: 43,6f,72,65,61

SQL> select dump('Corea', 8,3,2) "Octal" from dual;

Octal

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

Typ=96 Len=5: 162,145

SQL> select dump('Corea',16,3,2) "ASCII" from dual;

ASCII

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

Typ=96 Len=5: 72,65

SQL>

4-6) EMPTY_BLOB 함수

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

EMPTY_BLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,

또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.

【예시】

UPDATE print_media SET ad_photo = EMPTY_BLOB();

4-7) EMPTY_CLOB 함수

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

EMPTY_CLOB () 함수는 LOB 변수를 초기화하기 위하여 쓰이거나,

또는 INSERT 문이나 UPDATE 문에서 empty LOB 위치를 반환한다.

【예시】

UPDATE print_media SET ad_photo = EMPTY_CLOB();

4-8) EXISTSNODE 함수

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

이 함수는 node의 존재여부를 확인하여 그 결과를 반환한다.

0은 노드가 남아 있지 않은 경우이고,

1은 아직 노드가 존재하는 경우이다.

【형식】

EXISTSNODE(XMLType_instance, XPath_string [,namespace_string] )

【예제】

SQL> select * from resource_view;

SQL> select res,any_path

2 from resource_view

3 where existsnode(res, 'xdbconfig.xml') =0;

26 rows selected.

SQL>

4-9) EXTRACT(XML) 함수

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

이 함수는 existsnode와 유사한 함수이다.

【형식】

EXTRACT(XMLType_instance, XPath_string [,namespace_string] )

【예제】

SQL> select * from resource_view;

SQL> select extract(res,'xdbconfig.xml')

2 from resource_view;

26 rows selected.

SQL>

4-10) EXTRACTVALUE 함수

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

이 함수는 existsnode와 유사한 함수로써 node의 스칼라 값을 반환한다.

【형식】

EXTRACTVALUE(XMLType_instance, XPath_string [,namespace_string] )

【예제】

SQL> select * from resource_view;

SQL> select extractvalue(res,'xdbconfig.xml')

2 from resource_view;

26 rows selected.

SQL>

4-11) GREATEST 함수

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

GREATEST (expr,...) 함수는 expr 중에서 가장 큰 값을 반환한다.

【예제】

SQL> select greatest(20,10,30) from dual;

GREATEST(20,10,30)

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

30

SQL>

4-12) LEAST 함수

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

LEAST (expr,...) 함수는 expr 중에서 가장 작은 값을 반환한다.

【예제】

SQL> select least(20,10,30) from dual;

GREATEST(20,10,30)

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

10

SQL> select least('bb','aa','cc') from dual;

GR

--

aa

SQL>

4-13) NLS_CHARSET_DECL_LEN 함수

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

NLS_CHARSET_DECL_LEN (byte_count , char_set_id) 함수는 nchar로 선언된 폭을 반환한다.

【예제】

SQL> select nls_charset_decl_len

2 (200, nls_charset_id('ja16eucfixed')) from dual

NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EUCFIXED'))

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

100

SQL>

4-14) NLS_CHARSET_ID 함수

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

nls_charset_id('text') 함수는 문자셋 이름에 대응하는 ID 번호를 반환한다.

여기서 text는 서버에서 지원되는 CHAR_CS나 NCHAR_CS이다.

【예제】

SQL> select nls_charset_id('ja16euc') from dual;

NLS_CHARSET_ID('JA16EUC')

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

830

SQL>

4-15) NLS_CHARSET_NAME 함수

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

nls_charset_name('number') 함수는 문자섹 ID 번호에 대응하는 문자의 이름을 반환한다.

【예제】

SQL> select nls_charset_name(830) from dual;

NLS_CHA

-------

JA16EUC

SQL> select nls_charset_name(1) from dual;

NLS_CHAR

--------

US7ASCII

SQL>

4-16) NULLIF 함수

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

NULLIF(expr1, expr2) 함수는

expr1과 expr2를 비교하여

같으면 null을 반환하고,

같지 않으면 expr1을 반환한다.

이는 CASE 문으로 쓰면 다음과 같다.

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

【예제】

SQL> select nullif('aa','AA') from dual;

NU

--

aa

SQL> select nullif('aa','aa') from dual;

NU

--

SQL>

4-17) NVL2 함수

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

NVL2(expr1, expr2, expr3) 함수는

expr1이 null이 아니면 expr2를 반환하고,

expr1이 null이면 expr3을 반환한다.

【예제】

SQL> select nvl2('','Corea','Korea') from dual;

NVL2(

-----

Korea

SQL> select nvl2('aa','Corea','Korea') from dual;

NVL2(

-----

Corea

SQL>

4-18) PATH 함수

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

PATH(correlation_path) 함수는 under_path나 equals_path의 보조함수로서,

자원의 관계경로를 반환한다.

【예제】

SQL> select * from resource_view;

SQL> select path(1), depth(2)

2 from resource_view

3 where under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 1)=1?

4 and under_path(res, '/sys/schemas/PUBLIC/www.w3.org/2001', 2)=1;

PATH(1) DEPTH(2)

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

/xml.xsd 1

SQL>

4-19) SYS_CONNECT_BY_PATH 함수

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

SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리에서만 유효하며,

column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.

【예제】

SQL> select sys_connect_by_path(name, '/') from emp

2 start with name='jijoe'

3 connect by prior id=1101;

SYS_CONNECT_BY_PATH(NAME,'/')

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

/jijoe

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

4-20) SYS_CONTEXT 함수

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

이 함수는 namespace와 관계되는 parameter의 값을 반환한다.

【형식】

SYS_CONTEXT('namespace','parameter' [,length])

【예제】

SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')

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

JIJOE

SQL> select sys_context('userenv','lang') from dual;

SYS_CONTEXT('USERENV','LANG')

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

US

SQL>

userenv에서 사용될 parameter는 다음과 같다.

AUDITED_CURSORID AUTHENTICATION_DATA BG_JOB_ID

CLIENT_IDENTIFIER CLIENT_INFO CURRENT_SCHEMA

CURRENT_SCHEMAID CURRENT_SQL CURRENT_USER

CURRENT_USERID DB_DOMAIN DB_NAME

ENTRY_ID EXTERNAL_NAME FG_JOB_ID

GLOBAL_CONTEXT_MEMORY HOST INSTANCE

IP_ADDRESS ISDBA LANG

LANGUAGE NETWORK_PROTOCOL NLS_CALENDAR

NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE

NLS_SORT NLS_TERRITORY OS_USER

PROXY_USER PROXY_USERID SESSION_USER

SESSION_USERID SESSIONID TERMINAL

4-21) SYS_DBURIGEN 함수

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

이 함수는 입력된 argument에 대한 DBURIType의 URL을 반환한다.

【형식】

SYS_DBURIGEN({column|attribute} [rowid],... [,'text()'])

【예제】

SQL> select sys_dburigen(id,name) from emp

2 where name='jijoe';

SYS_DBURIGEN(ID,NAME)(URL, SPARE)

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

DBURITYPE('/PUBLIC/EMP/ROW[ID=''1104'']/NAME', NULL)

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

4-22) SYS_EXTRACT_UTC 함수

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

sys_extract_utc(datetime_with_timezone) 함수는

UTC(coordinated universal time: Greenwich mean time) 시각을 반환한다.

【예제】

SQL> select systimestamp, sys_extract_utc(systimestamp) from dual;

SYSTIMESTAMP

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

SYS_EXTRACT_UTC(SYSTIMESTAMP)

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

06-AUG-04 02.41.39.258976 PM +09:00

06-AUG-04 05.41.39.258976 AM

SQL>

4-23) SYS_GUID 함수

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

sys_guid() 함수는 globally unique identifier를 반환한다.

【예제】

SQL> select sys_guid() from dual;

SYS_GUID()

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

E0F6C6D5767C01ADE034080020B588F4

SQL>

4-24) SYS_XMLAGG 함수

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

이 함수는 sys_xmlgen 문에서 만든 XML 문을 기본적으로 ROWSET 태그를 새로 추가 시킨다.

【형식】

SYS_XMLAGG( expr [fmt] )

【예제】

SQL> select sys_xmlagg(sys_xmlgen(name)) from emp

2 where name like 'j%';

SYS_XMLAGG(SYS_XMLGEN(NAME))

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

<NAME>jijoe</NAME>

</ROWSET>

SQL>

4-25) SYS_XMLGEN 함수

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

이 함수는 지정한 행이나 열을 XML 문으로 만들어 반환한다.

【형식】

SYS_XMLGEN( expr [fmt] )

【예제】

SQL> select sys_xmlgen(name) from emp

2 where name like 'j%';

SYS_XMLGEN(NAME)

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

<NAME>jijoe</NAME>

SQL>

4-26) UID 함수

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

UID 함수는 사용자의 유일한 ID를 정수로 반환한다.

【예제】

SQL> select uid from dual;

UID

----------

93

SQL>

4-27) USER 함수

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

이 함수는 사용자의 이름을 반환한다.

【예제】

SQL> select user,uid from dual;

USER UID

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

JIJOE 93

SQL>

4-28) USERENV 함수

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

USERENV('parameter') 함수는 사용자의 환경에 관한 정보를 반환한다.

parameter는 다음과 같은 것이 있다.

CLIENT_INFO ENTRYID ISDBA LANG

LANGUAGE SESSIONID TERMINAL

【예제】

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.KO16KSC5601

SQL>

4-29) VSIZE 함수

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

VSIZE('expr') 함수는 expr이 표시되는 바이트 수를 반환한다.

【예제】

SQL> select name, vsize(name) from emp

2 where name like 'jijoe';

NAME VSIZE(NAME)

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

jijoe 5

SQL>

$ cat .profile

..........

NLS_LANG=AMERICAN_AMERICA.KO16KSC5601 ☜ 한글 문자셋으로 설정

export NLS_LANG

NLS_LANG=AMERICAN_AMERICA.UTF8 ☜ UNICODE로 설정

export NLS_LANG

$

테이블에서 저장된 데이터가 한글 문자셋인지 유니코드인지 식별하려면 다음과 같이 확인해 볼 수 있다.

select 한글컬럼명, vsize(한글컬럼명) from 테이블명;

여기서 한글 컬럼에 한글이 3글자라면, vsize 결과가

9이면 unicode이고,

6이면 한글 문자셋으로 저장된 것임을 알 수 있다.

【예제】

SQL> select * from test;

ID NAME

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

1113 아리랑

1112 쓰리랑

SQL> select name, vsize(name) from test;

NAME VSIZE(NAME)

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

아리랑 6

쓰리랑 6

SQL>

4-30) XMLAGG 함수

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

이 함수는 xmlelement에 의해서 XML 태그를 만든 문장을 모으는 기능이다.

【형식】

XMLAGG( XMLType_instance [order_by_clause])

【예제】

SQL> select xmlagg(xmlelement("name",e.name)) from emp e;

XMLAGG(XMLELEMENT("NAME",E.NAME))

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

<name>jijoe</name>

SQL>

4-31) XMLCOLATTVAL 함수

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

이 함수는 XML fragment를 만드는 기능이다

【형식】

XMLCOLATTVAL( value_expr [AS c_alias],...)

【예제】

SQL> select xmlcolattval(e.name,e.id,e.salary) from emp e;

XMLCOLATTVAL(E.NAME,E.ID,E.SALARY)

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

<column name="S

<column name="S

<column name="S

<column name="NAME">jijoe</column>

<column name=

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

4-32) XMLCONCAT 함수

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

XMLCONCAT( XMLType_instance,...) 함수는 XMLType instance를 series로 넣어 만드는 기능이다.

【예제】

SQL> select xmlconcat(

2 xmlelement("name",e.name),xmlelement("bonus",e.bonus))

3 from emp e;

XMLCONCAT(XMLELEMENT("NAME",E.NAME),XMLELEMENT("BONUS",E.BONUS))

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

<name>jijoe</name>

SQL>

4-33) XMLFOREST 함수

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

이 함수는 각각의 argument parameter를 XML로 변환한다.

【형식】

XMLFOREST( value_expr [AS c_alias],...)

【예제】

SQL> select xmlelement("emp",

2 xmlforest(e.id, e.name, e.bonus)) from emp e;

XMLELEMENT("EMP",XMLFOREST(E.ID,E.NAME,E.BONUS))

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

<emp>

</emp>

<emp>

</emp>

<emp>

</emp>

<emp>

<NAME>jijoe</NAME>

</emp>

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

4-34) XMLELEMENT 함수

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

이 함수는 XML 태그를 붙이는 기능이다

【예제】

SQL> select xmlelement("name",e.name) from emp e

2 where name like 'j%';

XMLELEMENT("NAME",E.NAME)

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

<name>jijoe</name>

SQL>

5-1) AVG* 함수

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

조건을 만족하는 행(row)의 평균을 값을 반환하며,

aggregate 함수나

analytic 함수로 사용된다.

【형식】

AVG( [DISTINCT | ALL] 컬럼명)

[ [OVER] (analytic 절)]

【예제】aggregate 예

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL> select avg(salary) from emp;

AVG(SALARY)

-----------

240

SQL>

【예제】analytic 예

SQL> select avg(distinct salary) over(partition by bonus)

2 from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)

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

236.666667

250

SQL> select avg(salary) over(partition by bonus order by id

2 rows between 1 preceding and 1 following) as avg from emp;

AVG

----------

245

236.666667

235

250

SQL>

5-2) CORR* CORR* 함수

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

집합 쌍의 상관관계 계수를 반환한다.

【형식】

CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】

SQL> select corr(avg(bonus),max(bonus))

2 from employees

3 group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))

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

1

SQL>

5-3) COUNT* 함수

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

쿼리한 행의 수를 반환한다.

【형식】

COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】

SQL> select count(*) from emp;

COUNT(*)

----------

4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)

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

2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)

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

4

SQL> select salary,count(*)

2 over (order by salary)

3 from emp;

SALARY COUNT(*)OVER(ORDERBYSALARY)

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

220 1

240 2

250 4

SQL>

5-4) COVAR_POP 함수

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

이 함수는 number 쌍의 집합에 대한 population covariance를 반환한다.

【형식】

COVAR_POP(expr1, expr2 [ OVER (analytic 절)] )

【예제】

SQL> select covar_pop(bonus,salary) from emp;

COVAR_POP(BONUS,SALARY)

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

62.5

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-5) COVAR_SAMP 함수

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

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】

COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】

SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)

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

83.3333333

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-6) CUME_DIST 함수

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

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】

CUME_DIST(expr,... WITHIN GROUP (ORDER BY

expr [DESC | ASC] [NULLS {FIRST|LAST}],...)

또는

CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】

SQL> select cume_dist(230) within group

2 (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)

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

.4

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-7) DENSE_RANK 함수

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

그룹 내에서 순위를 반환한다.

【예제】

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL> select dense_rank(230, .05) within group

2 (order by salary, bonus) "Dense Rank"

3 from employees;

Dense Rank

----------

2

SQL>

5-8) FIRST 함수

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

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.

【형식】

집합함수 KEEP (

DENSE_RANK FIRST ORDER BY

expr [DESC|ASC][NULL{FIRST|LAST}],...)

【예제】

SQL> select

2 min(salary) keep (dense_rank first order by salary) "Worst",

3 max(salary) keep (dense_rank last order by salary) "Best"

4 from employees

5 order by id;

Worst Best

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

220 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-9) GROUP_ID 함수

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

GROUP() 함수는 group by로 분리되어 복제된 번호로 복제 횟수를 구분하도록 출력한다.

번호가 0부터 시작되므로 n번 복제되었으면 n-1의 번호가 출력된다.

【예제】

SQL> select dept_no, group_id() from employees

2 group by dept_no;

DEPT_NO GROUP_ID()

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

10 0

20 0

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-10) Grouping 함수

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

Grouping 함수는 Rollup이나 cube 연산자랑 함께 사용하여

grouping 함수에서 기술된 컬럼이 그룹핑시 사용되었는지 보여주는 함수이다.

특별히 연산의 기능은 없으며,

rollup이나 cube 연산 후 출력된 결과에 대한 사용자의 이해를 높이기 위해 사용된다.

즉, grouping 함수를 이용할 경우 출력되는 결과값 중 null 값이 있다면,

이 null 값이 rollup이나 cube 연산의 결과로 생성된 값인지,

원래 테이블상에 null 값으로 저장된 것인지 확인할 수 있다.

. grouping 함수는 인수로 하나의 값만을 가진다.

. grouping 함수에 사용된 인수는 group by 절에 기술된 값 중에 하나와 반드시 일치해야 한다.

. grouping 함수의 결과값으로 0 또는 1을 반환한다.

0은 해당인수로 쓰인 값이 rollup이나 cube 연산에 사용되었음을 나타나고,

1은 사용되지 않았음을 의미한다.

【형식】

SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)

FROM 테이블명

WHERE 조건

GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명,...

HAVING 그룹조건

ORDER BY 컬럼명 또는 위치번호

【예제】

SQL> select grade,deptno,sum(salary),GROUPING(deptno)

2 from aaa

3 group by rollup(grade,deptno);

GRADE DEPTNO SUM(SALARY) GROUPING(DEPTNO)

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

1 10 100 0

1 20 500 0

1 30 300 0

1 900 1

2 10 400 0

2 20 200 0

2 30 600 0

2 1200 1

2100 1

9 rows selected.

SQL>

5-11) GROUPING_ID 함수

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

GROUPING_ID(expr,...) 함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환한다.

【예제】

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL> select sum(salary), grouping_id(dept_no)

2 from employees

3 group by dept_no;

SUM(SALARY) GROUPING_ID(DEPT_NO)

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

500 0

460 0

SQL>

5-12) LAST 함수

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

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.

【형식】

집합함수 KEEP (

DENSE_RANK LAST ORDER BY

expr [DESC|ASC][NULL{FIRST|LAST}],...)

【예제】

SQL> select

2 min(salary) keep (dense_rank first order by salary) "Worst",

3 max(salary) keep (dense_rank last order by salary) "Best"

4 from employees

5 order by id;

Worst Best

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

220 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-13) MAX 함수

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

이 함수는 최대 값을 반환한다.

【형식】

MAX ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】

SQL> select max(salary) over (partition by dept_no)

2 from employees;

MAX(SALARY)OVER(PARTITIONBYDEPT_NO)

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

250

240

SQL> select max(salary) from employees;

MAX(SALARY)

-----------

250

SQL>

5-14) MIN 함수

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

이 함수는 최소 값을 반환한다.

【형식】

MIN ([{DISTINCT|ALL}] expr) [OVER(analytic_clause)]

【예제】

SQL> select min(salary) over (partition by dept_no)

2 from employees;

MIN(SALARY)OVER(PARTITIONBYDEPT_NO)

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

250

220

SQL> select min(salary) from employees;

MIN(SALARY)

-----------

220

SQL>

5-15) PERCENTILE_CONT 함수

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

이 함수는 연속 모델에 대한 inverse distribution function이다.

【형식】

PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])

[OVER (query_partition_cluause)]

【예제】

SQL> select dept_no,percentile_cont(0.5) within group

2 (order by salary DESC)

3 from employees GROUP BY dept_no;

DEPT_NO PERCENTILE_CONT(0.5)WITHINGROUP(ORDERBYSALARYDESC)

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

10 250

20 230

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-16) PERCENTILE_DISC 함수

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

이 함수는 불연속 모델에 대한 inverse distribution function이다.

【형식】

PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [{DESC|ASC}])

[OVER (query_partition_cluause)]

【예제】

SQL> select dept_no,percentile_disc(0.5) within group

2 (order by salary DESC)

3 from employees GROUP BY dept_no;

DEPT_NO PERCENTILE_DISC(0.5)WITHINGROUP(ORDERBYSALARYDESC)

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

10 250

20 240

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-17) PERCENT_RANK 함수

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

이 함수는 CUME_DIST 함수와 유사하게 percent_rank 값을 반환한다.

【형식】

PERCENT_RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]

[NULLS {FIRST|LAST}],...)

또는

PERCENT_RANK() OVER( [query_partition_clause] order_by_clause)

【예제】

SQL> select percent_rank(230,0.05) within group

2 (order by salary,bonus) from employees;

PERCENT_RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)

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

.25

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-18) RANK 함수

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

이 함수는 그룹 내에서 위치를 반환한다.

【형식】

RANK(expr,...) WITHIN GROUP (ORDER BY expr [{DESC|ASC}]

[NULLS {FIRST|LAST}],...)

또는

RANK() OVER( [query_partition_clause] order_by_clause)

【예제】

SQL> select rank(230,0.05) within group

2 (order by salary,bonus) from employees;

RANK(230,0.05)WITHINGROUP(ORDERBYSALARY,BONUS)

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

2

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-19) REGR_(linear regression) function* 함수

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

선형 회귀(linear regression) 함수 ordinary-least squares regression line을 찾도록 한다.

사용되는 회귀함수는 자음 중 하나이다.

REGR_SLOPE REGR_INTERCEPT REGR_COUNT

REGR_R2 REGR_AVGX REGR_AVGY

REGR_SXX REGR_SYY REGR_SXY

【형식】

REGR { REGR_SLOPE|REGR_INTERCEPT|REGR_COUNT|REGR_R2|REGR_AVGX|

REGR_AVGY|REGR_SXX|REGR_SYY|REGR_SXY}

(expr1,expr2) [OVER (analytic_clause)]

【예제】

SQL> select regr_slope(salary,bonus) from employees

REGR_SLOPE(SALARY,BONUS)

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

.533333333

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

5-20) STDDEV 함수

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

이 함수는 standard deviation을 반환한다.

【형식】

STDDEV [{DISTINCT|ALL}] (expr) [OVER (analytic_clause)]

【예제】

SQL> select stddev(salary) from emp;

STDDEV(SALARY)

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

14.1421356

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-21) STDDEV_POP 함수

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

이 함수는 population standard deviation을 반환한다.

【형식】

STDDEV_POP (expr) [OVER (analytic_clause)]

【예제】

SQL> select stddev_pop(salary) from emp;

STDDEV_POP(SALARY)

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

12.2474487

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-22) STDDEV_SAMP 함수

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

이 함수는 cumulative sample standard deviation을 반환한다.

【형식】

STDDEV_SAMP (expr) [OVER (analytic_clause)]

【예제】

SQL> select stddev_samp (salary) from emp;

STDDEV_SAMP(SALARY)

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

14.1421356

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-23) SUM 함수

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

이 함수는 합계를 반환한다.

【형식】

SUM ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】

SQL> select sum(salary) from emp;

SUM(SALARY)

-----------

960

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-24) VAR_POP 함수

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

이 함수는 population variance를 반환한다.

【형식】

VAR_POP (expr) [OVER (analytic_clause)]

【예제】

SQL> select var_pop(salary) from emp;

VAR_POP(SALARY)

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

150

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-25) VAR_SAMP 함수

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

이 함수는 sample variance를 반환한다.

【형식】

VAR_SAMP (expr) [OVER (analytic_clause)]

【예제】

SQL> select var_samp(salary) from emp;

VAR_SAMP(SALARY)

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

200

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-26) VARIANCE 함수

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

이 함수는 variance를 반환한다.

【형식】

VARIANCE ([{DISTINCT|ALL}] expr) [OVER (analytic_clause)]

【예제】

SQL> select variance(salary) from emp;

VARIANCE(SALARY)

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

200

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

5-27) Grouping sets 함수

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

Grouping sets 함수는 Group by의 확장된 형태로 하나의 쿼리문에서 원하는 그룹핑 조건을 여러 개 기술할 수 있으며,

grouping sets 함수 사용이 불가능한 이전 버전에서

복잡한 union all 연산자를 사용하여 기술하던 것을 간단하게

한 문장 안에서 해결할 수 있어 검색시 효율성이 증대 된다.

다시 말해서, grouping sets 함수를 사용하면,

group by ... union all을 사용한 것보다

SQL 문이 간단해지고 또한 실행시 성능이 빨라진다.

【형식】

SELECT 컬럼명,그룹함수(컬럼명), GROUPING(컬럼명)

FROM 테이블명

WHERE 조건

GROUP BY [ROLLUP | CUBE] 그룹핑하고자하는 컬럼명, ...

[GROUPING SETS (컬럼명,컬럼명, ...), ...]

HAVING 그룹조건

ORDER BY 컬럼명 또는 위치번호

【예제】

SQL> select grade,deptno,sum(salary)

2 from aaa

3 group by grouping sets(grade,deptno);

GRADE DEPTNO SUM(SALARY)

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

1 900

2 1200

10 500

20 700

30 900

SQL> select grade,deptno,sum(salary)

2 from aaa

3 group by grouping sets((grade,name),(deptno,name));

GRADE DEPTNO SUM(SALARY)

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

1 100

1 300

1 500

2 200

2 400

2 600

10 100

20 200

30 300

10 400

20 500

30 600

12 rows selected.

SQL>

【예제】Union all을 사용한 경우

SQL> select grade,deptno,sum(salary)

2 from aaa

3 group by grade,deptno

4 union all

5 select grade,deptno,sum(salary)

6 from aaa

7 group by grade,deptno;

GRADE DEPTNO SUM(SALARY)

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

1 10 100

1 20 500

1 30 300

2 10 400

2 20 200

2 30 600

1 10 100

1 20 500

1 30 300

2 10 400

2 20 200

2 30 600

12 rows selected.

SQL>

composite columns란 rollup, cube, grouping sets 절과 같은 기능을 사용하면

표현되는 각 컬럼이 하나가 아닌 복수 개의 컬럼으로 정의되는 경우이며

다음 표를 보고 이해하자.

composite column 문의 경우 group by 문의 경우 group by grouping sets(a,b,c) group by a union allgroup by b union allgroup by c group by grouping sets(a,b,(b,c)) group by a union allgroup by b union allgroup by b,c group by grouping sets((a,b,c)) group by a,b,c group by grouping sets(a,(b),()) group by a union allgroup by b union allgroup by () group by grouping sets(a,rollup(b,c)) group by a union allgroup by rollup(b,c) group by rollup(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a) union allgroup by () group by cube(a,b,c) group by (a,b,c) union allgroup by (a,b) union allgroup by (a,c) union allgroup by (b,c) union allgroup by (a) union allgroup by (b) union allgroup by (c) union allgroup by ()

6-1) AVG* 함수

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

조건을 만족하는 행(row)의 평균을 값을 반환하며,

aggregate 함수나

analytic 함수로 사용된다.

【형식】

AVG( [DISTINCT | ALL] 컬럼명)

[ [OVER] (analytic 절)]

【예제】aggregate 예

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL> select avg(salary) from emp;

AVG(SALARY)

-----------

240

SQL>

【예제】analytic 예

SQL> select avg(distinct salary) over(partition by bonus)

2 from emp;

AVG(DISTINCTSALARY)OVER(PARTITIONBYBONUS)

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

236.666667

250

SQL> select avg(salary) over(partition by bonus order by id

2 rows between 1 preceding and 1 following) as avg from emp;

AVG

----------

245

236.666667

235

250

SQL>

6-2) CORR* CORR* 함수

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

집합 쌍의 상관관계 계수를 반환한다.

【형식】

CORR( expr1, expr2 ) [ [OVER] (analytic 절)]

【예제】

SQL> select corr(avg(bonus),max(bonus))

2 from employees

3 group by dept_no;

CORR(AVG(BONUS),MAX(BONUS))

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

1

SQL>

6-3) COUNT* 함수

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

쿼리한 행의 수를 반환한다.

【형식】

COUNT([*|DISTINCT|ALL] 컬럼명) [ [OVER] (analytic 절)]

【예제】

SQL> select count(*) from emp;

COUNT(*)

----------

4

SQL> select count (distinct dept_no) from employees;

COUNT(DISTINCTDEPT_NO)

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

2

SQL> select count (all dept_no) from employees;

COUNT(ALLDEPT_NO)

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

4

SQL> select salary,count(*)

2 over (order by salary)

3 from emp;

SALARY COUNT(*)OVER(ORDERBYSALARY)

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

220 1

240 2

250 4

SQL>

6-4) COVAR_SAMP 함수

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

이 함수는 number 쌍의 집합에 대한 sample covariance를 반환한다.

【형식】

COVAR_SAMP(expr1, expr2 [ OVER (analytic 절)] )

【예제】

SQL> select covar_samp(bonus,salary) from emp;

COVAR_SAMP(BONUS,SALARY)

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

83.3333333

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

6-5) CUME_DIST 함수

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

이 함수는 그룹 값 내에서 어떤 값의 cumulative distribution(누적분포)을 계산한다.

【형식】

CUME_DIST(expr,... WITHIN GROUP (ORDER BY

expr [DESC | ASC] [NULLS {FIRST|LAST}],...)

또는

CUME_DIST() over ([query_partition_clause] order_by_clause)

【예제】

SQL> select cume_dist(230) within group

2 (order by salary ) from emp;

CUME_DIST(230)WITHINGROUP(ORDERBYSALARY)

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

.4

SQL> select * from emp;

ID NAME SALARY BONUS

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

1101 Cho 250 125

1102 Joe 240 100

1103 kim 250 100

1104 jijoe 220 100

SQL>

6-6) DENSE_RANK 함수

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

그룹 내에서 순위를 반환한다.

【예제】

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL> select dense_rank(230, .05) within group

2 (order by salary, bonus) "Dense Rank"

3 from employees;

Dense Rank

----------

2

SQL>

6-7) FIRST 함수

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

first나 last 함수는 행을 서열화 시켜서 첫 번째나 마지막 행을 추출한다.

【형식】

집합함수 KEEP (

DENSE_RANK FIRST ORDER BY

expr [DESC|ASC][NULL{FIRST|LAST}],...)

【예제】

SQL> select

2 min(salary) keep (dense_rank first order by salary) "Worst",

3 max(salary) keep (dense_rank last order by salary) "Best"

4 from employees

5 order by id;

Worst Best

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

220 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-8) FIRST_VALUE 함수

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

이 함수는 서열화된 값에서 첫 번째를 출력한다.

【형식】

FIRST_VALUE ( expr ) OVER ( analytic_절)

【예제】

SQL> select salary,first_value(name)

2 over (order by salary asc)

3 from (select * from employees

4 where dept_no = 20

5 order by salary);

SALARY FIRST_VALU

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

220 jijoe

240 jijoe

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-9) LAG 함수

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

이 함수는 analytic 함수로서,

self join하지 않고 하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】

LAG ( value_expr [,offset] [,default] )

OVER ([query_partition_clause] order_by_clause )

【예제】

SQL> select name,salary,LAG(salary,1,0)

2 OVER (ORDER BY salary) FROM employees;

NAME SALARY LAG(SALARY,1,0)OVER(ORDERBYSALARY)

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

jijoe 220 0

Joe 240 220

Cho 250 240

kim 250 250

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-10) LAST_VALUE 함수

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

이 함수는 서열화된 값에서 마지막 번째를 출력한다.

【형식】

LAST_VALUE ( expr ) OVER ( analytic_절)

【예제】

SQL> select salary,last_value(name)

2 over (order by salary asc)

3 from (select * from employees

4 where dept_no = 20

5 order by salary);

SALARY LAST_VALUE

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

220 jijoe

240 Joe

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-11) LEAD 함수

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

이 함수는 analytic 함수로서, self join하지 않고

하나의 테이블에서 동시에 한 행(row) 이상을 접근할 수 있도록 한다.

【형식】

LEAD ( value_expr [,offset] [,default] )

OVER ([query_partition_clause] order_by_clause )

【예제】

SQL> select name,salary,LEAD(salary,1,0)

2 OVER (ORDER BY salary) FROM employees;

NAME SALARY LEAD(SALARY,1,0)OVER(ORDERBYSALARY)

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

jijoe 220 240

Joe 240 250

Cho 250 250

kim 250 0

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-12) NTILE 함수

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

이 함수는 analytic 함수로서, 데이터를 주어진 bucket 수 expr로 분리한다.

【형식】

NTILE ( expr ) OVER ([query_partition_clause] order_by_clause )

【예제】

SQL> select name,salary,NTILE(3) OVER (ORDER BY salary DESC)

2 FROM employees;

NAME SALARY NTILE(3)OVER(ORDERBYSALARYDESC)

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

Cho 250 1

kim 250 1

Joe 240 2

jijoe 220 3

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-13) RATIO_TO_REPORT 함수

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

이 함수는 analytic 함수로서, 데이터 합에 대한 구성비를 계산한다.

【형식】

RATIO_TO_REPORT ( expr ) OVER ([query_partition_clause])

【예제】

SQL> select name,salary,RATIO_TO_REPORT(salary) OVER ()

2 FROM employees;

NAME SALARY RATIO_TO_REPORT(SALARY)OVER()

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

Cho 250 .260416667

Joe 240 .25

kim 250 .260416667

jijoe 220 .229166667

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

6-14) ROW_NUMBER 함수

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

이 함수는 analytic 함수로서, 각 행(row)에 unique 번호를 부여한다.

【형식】

ROW_NUMBER () OVER ([query_partition_clause] order_by_clause )

【예제】

SQL> SELECT ROW_NUMBER() OVER (ORDER BY salary DESC),name

2 FROM employees;

ROW_NUMBER()OVER(ORDERBYSALARYDESC) NAME

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

1 Cho

2 kim

3 Joe

4 jijoe

SQL> select * from employees;

ID DEPT_NO NAME SALARY BONUS

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

1101 10 Cho 250 125

1102 20 Joe 240 100

1103 10 kim 250 100

1104 20 jijoe 220 100

SQL>

7-1) REF 타입

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

테이블의 어떤 컬럼이 독립된 다른 객체 테이블을 참조하는 데이터 타입을 의미한다.

일반적으로 테이블을 만들때 사용하는 외부키(foreign-key)를 이용한 참조관계와 유사하다.

REF 타입의 컬럼 데이터를 읽을 때는 반드시 DEREF 함수를 사용한다.

REF 타입으로 정의된 컬럼에는 실제 데이터가 저장되는 것이 아니고 참조되는 객체가 존재하는 포인트정보만 가지고 있기 때문에 객체가 삭제 된다면, 해당 컬러은 정상적으로 검색되지 못한다.

이러한 현상을 REF의 Dangling 현상이고 한다.

이러한 dangling 현상을 방지하기 위해서는 삭제된 객체 정보를 참조하는 행의 컬럼 정보를 analyze 명령어를 이용하여 null 값으로 변경해 주어야 한다.

【예제】

SQL> connect jijoe/jijoe_password

connected

SQL> create type person_type as object(

2 first_name varchar2(10),

3 last_name varchar2(10),

4 phone varchar(12),

5 birthday varchar2(12));

6 /

Type created.

SQL> create type emp_type as object (

2 empno number,

3 emp person_type);

4 /

Type created.

SQL> create table emp2 of emp_type

2 oidindex emp_oid;

Table created.

SQL> insert into emp2 values(

2 emp_type(1000,person_type('junik','joe','123-1234','20-jul-04')));

1 row created.

SQL> create table dept(

2 empno number(4),

3 ename varchar2(15),

4 mgr REF emp_type SCOPE IS emp2);

Table created.

SQL> insert into dept

2 select empno, 'SCOTT', REF(e)

3 from emp2 e

4 where empno=1000;

1 row created.

【예제】

SQL> select ename,empno from dept;

ENAME EMPNO

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

SCOTT 1000

SQL> select mgr, DEREF(mgr) from dept;

MGR

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

DEREF(MGR)(EMPNO, EMP(FIRST_NAME, LAST_NAME, PHONE, BIRTHDAY))

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

0000220208DFA05B27A63701D9E034080020B588F4DFA05B27A63601D9E034080020B588F4

EMP_TYPE(1000, PERSON_TYPE('junik', 'joe', '123-1234', '20-jul-04'))

SQL>

【예제】

SQL> select empno,ename,mgr

2 from dept

3 where mgr is dangling;

no rows selected

SQL> analyze table dept validate REF update set dangling to NULL;

Table analyzed.

SQL>

8-1) ROWID 컬럼

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

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,

ROWID, ROWNUM등이 있다.

ROWID는 데이터베이스에서 컬럼이 위치한 장소이다.

【예제】

SQL> select rowid from test;

ROWID

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

AAAHbHAABAAAMXCAAA

SQL> select rowid from test

2 where rowidtochar(rowid) like '%AABAA%';

ROWID

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

AAAHbHAABAAAMXCAAA

SQL> select lengthb(rowidtonchar(rowid)), rowidtonchar(rowid)

2 from test;

LENGTHB(ROWIDTONCHAR(ROWID)) ROWIDTONCHAR(ROWID

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

36 AAAHbHAABAAAMXCAAA

SQL>

여기서 rowid의 의미는 다음과 같다.

AAAHbH AAB AAAMXC AAA 객체번호 테이블스페이스번호 블록번호 행번호

8-2) ROWNUM 컬럼

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

오라클에서 내부적으로 사용되는 컬럼을 pseudocolumn이라고 하며,

ROWID, ROWNUM등이 있다.

ROWNUM은 테이블에서 select 되어진 일련 번호임

【예제】

SQL> select rownum,ename from emp;

ROWNUM ENAME

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

1 CLARK

2 MILLER

3 JONES

4 ALLEN

5 MARTIN

6 CHAN

6 rows selected.

SQL> delete from emp where ename='JONES';

1 row deleted.

SQL> select rownum,ename from emp;

ROWNUM ENAME

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

1 CLARK

2 MILLER

3 ALLEN

4 MARTIN

5 CHAN

SQL>

ora_fun-xeon3402.chm

유효하지않음

,

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

MS949(EUC-KR)에서 UTF-8 변경시 DBMS 컬럼 사이즈 변경 작성

PL/SQL BASE64 Encode/Decode

crontab에서 plsql 실행

ORACLE 백업 펄스크립트

procedures 백업하기

DBMS_scheduler

오라클 잡 관리(Oracle job manage)

구글Map 주소로 좌표값얻기 PL/SQL

ORA-08002: 시퀀스 **.CURRVAL은 이 세션에서는 정의 되어 있지 않습니다

EXP-00056: ORACLE error

정규 표현식을 이용한 SQL 구문의 개선

오라클 정규표현식 예제

REGEXP_SUBSTR

Oracle REGEXP_REPLACE

9i EXP-00003

Memory Notification: Library Cache Object loaded into SGA

오라클 내장함수

공지사항

카테고리

태그목록

글 보관함

달력

링크

유효하지않음

LATEST FROM OUR BLOG

LATEST COMMENTS

BLOG VISITORS

티스토리툴바

« 2024/04 »
일	월	화	수	목	금	토
	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