간혹 발생하는 소유관련 문제...
관련 정보 중 가장 도움이 되었던 예제이다.

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=2368

테이블 소유자 확인 및 변경
 
- SQL Server 2005의 경우
 
SQL Server 2005에서는 소유자가 스키마를 소유하고 스키마가 오브젝트를 소유합니다.
> 기호의 벌어진 쪽이 좁혀진 쪽을 소유한다는 의미로 사용했을 때, 다음과 같이 표현할 수 있습니다.
소유자 > 스키마 > 오브젝트
 
따라서 소유자를 삭제할 경우에는 삭제할 소유자가 소유한 스키마의 소유권을 새로운 소유자에게 이전한 후 기존 소유자를 삭제하면 됩니다.
다음의 쿼리를 한 줄씩 실행해보시면 소유자 확인 및 이전, 삭제의 방법을 알게 되시리라 생각합니다.
반드시 충분한 테스트를 해보시고 실전에 사용하시기 바랍니다. ^^;;;
 
/*************************************************************/
-- 사용자와 관련 오브젝트 목록 확인 및 사용자 삭제 - SQL Server 2005
/*************************************************************/
USE master;
GO
 
/*************************************************************/
/**-- 테스트를 위한 사용자, 스키마, 테이블 생성----------------*/
 
-- 테스트 데이터 베이스 생성
CREATE DATABASE AccTestDB;
 
-- 로그인 생성
CREATE LOGIN AccountTestA WITH PASSWORD = 'test1';
CREATE LOGIN AccountTestB WITH PASSWORD = 'test2';
GO
 
-- 사용자 생성
USE AccTestDB;
GO
CREATE USER AccountTestA FOR LOGIN AccountTestA WITH DEFAULT_SCHEMA = AccSchema;
CREATE USER AccountTestB FOR LOGIN AccountTestB WITH DEFAULT_SCHEMA = AccSchema;
GO
 
-- 테이블 생성 권한을 위한 역할 추가
EXEC sp_addrolemember 'db_owner', 'AccountTestA';
EXEC sp_addrolemember 'db_owner', 'AccountTestB';
GO
 
-- 스키마 생성
CREATE SCHEMA AccSchema AUTHORIZATION AccountTestA;
 
-- 사용자 생성 확인
SELECT * FROM sys.sysusers;
SELECT * FROM sys.schemas;
 
-- SQL Server 2005에서는 소유자.테이블이 아닌 스키마.테이블로 테이블을 생성
EXECUTE AS LOGIN = 'AccountTestA';
GO
CREATE TABLE AccSchema.AccTable (Col1 int);
GO
 
/*************************************************************/
/**-- AccountTestA가 소유자인 오브젝트 목록 조회-------------*/
SELECT u.name as UserName, s.name as SchemaName, o.Type as ObjectType, o.name as ObjectName
FROM sys.sysusers u
                           JOIN sys.schemas s ON u.uid = s.principal_id
                           JOIN sys.objects o ON s.schema_id=o.schema_id
WHERE u.name = 'AccountTestA'
 
/*************************************************************/
/**-- SQL Server 2005에서는 소유자가 스키마를 소유하고
                           스키마가 오브젝트를 소유하기 때문에
        소유자를 삭제할 경우에는 오브젝트를 삭제하지 않고
                           삭제할 소유자가 소유한 스키마의 소유권을 이전한 후,
                           소유자를 삭제한다. --*/
 
-- 소유자삭제
DROP USER AccountTestA
/*
메시지15138, 수준16, 상태1, 줄1
데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다.
*/
 
-- AccountTestA 소유자인 테이블 목록 조회
SELECT u.name as UserName, s.name as SchemaName
FROM sys.sysusers u JOIN sys.schemas s ON u.uid = s.principal_id
WHERE u.name = 'AccountTestA'
 
-- 소유권 이전
ALTER AUTHORIZATION ON SCHEMA::AccSchema TO AccountTestB;
GO
 
-- 삭제하려는 소유자를 가진 테이블 삭제 후, 사용자 삭제
DROP USER AccountTestA;
GO
SELECT * FROM sys.sysusers WHERE name = 'AccountTestA';
GO
/*
메시지916, 수준14, 상태1, 줄1
현재 보안 컨텍스트로는 서버 보안 주체 "AccountTestA"이(가) 데이터베이스 "AccTestDB"에 액세스 할 수 없습니다.
 
*/
 
/*************************************************************/
-- 데이터베이스 삭제
REVERT;
 
USE master;
DROP DATABASE AccTestDB;
 
-- 로그인 삭제
DROP LOGIN AccountTestA;
DROP LOGIN AccountTestB;
 
 
 
- SQL Server 2000의 경우
 
/*************************************************************/
-- 사용자와 관련 오브젝트 목록 확인- SQL Server 2000
/*************************************************************/
USE master;
GO
 
/*************************************************************/
/**-- 테스트를 위한 사용자 및 테이블 생성----------------------*/
 
-- 테스트 데이터베이스 생성
CREATE DATABASE AccTestDB
GO
 
-- 로그인 생성
EXEC sp_addlogin 'AccountTest', 'test', 'AccTestDB'
 
-- 사용자 생성
USE AccTestDB
GO
EXEC sp_adduser 'AccountTest'
GO
 
-- 테이블 생성 권한을 위한 역할 추가
EXEC sp_addrolemember 'db_owner', 'AccountTest'
 
-- 사용자 생성 확인
SELECT * FROM sysusers WHERE name = 'AccountTest'
 
-- 실행 사용자를 AccountTest로 변경
SETUSER 'AccountTest'
 
-- 관련 테이블 생성
CREATE TABLE AccTable (Col1 int)
 
 
/*************************************************************/
/**-- AccountTest가 소유자인 오브젝트 목록 조회---------------*/
SELECT o.xType as ObjectType, u.name as UserName, o.name as ObjectName
FROM sysusers u JOIN sysobjects o ON u.uid=o.uid
WHERE u.name = 'AccountTest'
 
 
/*************************************************************/
/**-- 소유자 삭제 테스트-------------------------------------*/
-- 소유자 삭제
EXEC sp_dropuser 'AccountTest'
/*
서버: 메시지15183, 수준16, 상태1, 프로시저sp_MScheck_uid_owns_anything, 줄17
데이터베이스의 개체를 소유하고 있으므로 사용자를 삭제할 수 없습니다.
*/
 
-- AccountTest가 소유자인 테이블 목록 조회
SELECT u.name, o.name FROM sysusers u JOIN sysobjects o ON u.uid=o.uid
WHERE u.name = 'AccountTest' and o.xType='U'
 
-- 새 로그인 생성
SETUSER
EXEC sp_addlogin 'AccountTest2';
 
-- 새 사용자 생성
EXEC sp_adduser 'AccountTest2';
 
-- 소유자 이전
EXEC sp_changeobjectowner 'AccountTest.AccTable' , 'AccountTest2';
/*
주의: 개체 이름부분을 변경하면 스크립트나 저장프로시저를 손상시킬 수 있습니다.
*/
 
-- 소유자 삭제 재시도
EXEC sp_dropuser 'AccountTest'
 
-- 로그인 삭제
EXEC sp_droplogin 'AccountTest'
 
-- 확인
SELECT * FROM master.dbo.SYSXLOGINS
SELECT * FROM SYSUSERS
 
/**--테스트 완료------------------------------------------------------------------------*/
-- 데이터베이스 삭제
USE master
DROP DATABASE AccTestDB
 
-- 로그인 삭제
EXEC sp_droplogin 'AccountTest2'
 
-- 확인
SELECT * FROM master.dbo.SYSXLOGINS
SELECT * FROM SYSUSERS

tempdb의 사용량이 많은 경우 mdf, ldf와 물리적으로 다른 위치(별도의 Disk)로 tempdb를 이동하면 성능에 도움이 된다.
혹은 tempdb를 크게 사용해야 하는 경우 별도의 Disk로 이동해서 작업을 해야만 하는 경우가 존재할 수 있다.

이동 방법은 나중에 찾아보려할 때 사라지면 곤란하므로 sqlleader.com 등에서 소개한 것을 퍼왔다.


1. tempdb 정보 확인

SELECT name, physical_name AS Current_Location, state_desc                
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

2. 위치 변경

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\DBdata\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'F:\DBdata\templog.ldf');
GO

성공일 경우 Message
 시스템 카탈로그에서 파일 "tempdev"이(가) 수정되었습니다.
 새 경로는 다음에 데이터베이스가 시작될 때 사용됩니다.
 시스템 카탈로그에서 파일 "templog"이(가) 수정되었습니다.
 새 경로는 다음에 데이터베이스가 시작될 때 사용됩니다.

3. SQL Server를 중지한 후 다시 시작합니다.

4. 이동된 경로 확인

SELECT name, physical_name AS Current_Location, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

5. 기존의 tempdb 파일들을 삭제.
tempdb는 SQL Server가 재시작할 때마다 다시 만들어지므로 기존의 tempdb 파일들은 삭제한다.

SQL Server 2000 :  http://www.microsoft.com/korea/sqlserver/2005/howtobuy/pricing/default.mspx
SQL Server 2005 : http://www.microsoft.com/korea/sqlserver/2005/howtobuy/default.mspx

SQL Server는 현재 두 가지 라이선스 모델을 통해 사용할 수 있습니다.

프로세서 라이선스 (PL) : SQL 서버를 실행하는 컴퓨터 내의 각 CPU에 대해 라이선스가 하나씩 있어야 합니다. 이 라이선스는 클라이언트 장치를 무제한으로 액세스할 수 있습니다.

서버/사용자 단위 클라이언트 액세스 라이선스(CAL) :   Microsoft 서버 제품을 실행하는 컴퓨터에 대한 라이선스가 필요하며, 각 클라이언트 장치에 대한 클라이언트 액세스 라이선스(CAL)도 필요합니다. CAL의 설정 횟수는 서버 라이선스와 서버 소프트웨어에 포함되어 있습니다.

 ** 듀얼코어든 쿼드코어든 옥토퍼스든 물리적인 씨퓨에 따라서 각 1개씩 사면된다.
  -> 그렇다면... 코어가 많으면 유리한가??

 현재 600만원이 넘는다. (2008-06-26)
 Standard Edition : $5,999 (2008년 3월 26일)
 
 지인으로 부터 들은 이야기...
 CPU 4개, 메모리 4G 넘어가면 Enterprise Edition을 구입해야 한다고 함.
 Standard Edtion의 경우 CPU 당 600만원 정도라고 함.

만일 현재 Server를 운영중인데 어떤 것이 설치되어 있는지 알고 싶다면...
아래의 Query로 정보를 알아본다.

SELECT
 SERVERPROPERTY('BuildClrVersion') BuildClrVersion
 , SERVERPROPERTY('Collation') Collation
 , SERVERPROPERTY('CollationID') CollationID
 , SERVERPROPERTY('ComparisonStyle') ComparisonStyle
 , SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ComputerNamePhysicalNetBIOS
 , SERVERPROPERTY('Edition') Edition
 , SERVERPROPERTY('EditionID') EditionID
 , SERVERPROPERTY('EngineEdition') EngineEdition
 , SERVERPROPERTY('InstanceName') InstanceName
 , SERVERPROPERTY('IsClustered') IsClustered
 , SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled
 , SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly
 , SERVERPROPERTY('IsSingleUser') IsSingleUser
 , SERVERPROPERTY('LCID') LCID
 , SERVERPROPERTY('LicenseType') LicenseType
 , SERVERPROPERTY('MachineName') MachineName
 , SERVERPROPERTY('NumLicenses') NumLicenses
 , SERVERPROPERTY('ProcessID') ProcessID
 , SERVERPROPERTY('ProductVersion') ProductVersion
 , SERVERPROPERTY('ProductLevel') ProductLevel
 , SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime
 , SERVERPROPERTY('ResourceVersion') ResourceVersion
 , SERVERPROPERTY('ServerName') ServerName
 , SERVERPROPERTY('SqlCharSet') SqlCharSet
 , SERVERPROPERTY('SqlCharSetName') SqlCharSetName
 , SERVERPROPERTY('SqlSortOrder') SqlSortOrder
 , SERVERPROPERTY('SqlSortOrderName') SqlSortOrderName
 , SERVERPROPERTY('FilestreamShareName') FilestreamShareName
 , SERVERPROPERTY('FilestreamConfiguredLevel') FilestreamConfiguredLevel
 , SERVERPROPERTY('FilestreamEffectiveLevel') FilestreamEffectiveLevel

알아볼 Server가 여러개이고, Linked Server로 연결되어 있다면...
OPENQUERY 를 사용해서 UNION 으로 보는 것이 편하다.

ex)
SELECT * FROM OPENQUERY(ServerName1, 'SELECT SERVERPROPERTY(''Edition'') Edition')
UNION ALL
SELECT * FROM OPENQUERY(ServerName2, 'SELECT SERVERPROPERTY(''Edition'') Edition');

 

http://cafe.naver.com/sqlmvp/517

MSSQL 2005에서 가능한 것 같다.

CREATE TABLE taddy  (번호 int not null, name varchar(24));

INSERT taddy
SELECT 1,'박찬호' UNION ALL
SELECT 2,'홍길동' UNION ALL
SELECT 3,'김연아' UNION ALL
SELECT 4,'유재석' UNION ALL
SELECT 5,'강호동';

CREATE TABLE taddy_id (번호 int identity(6,1) not null, name varchar(24));

ALTER TABLE taddy SWITCH TO taddy_id;

drop table taddy;

exec sp_rename 'taddy_id','taddy';

SELECT * FROM taddy;

switch 라는 기능 신기하다...

Oracle은 Stored Procedure 에서 SELECT로 Record Set을 받아서 사용하려면 아래처럼 Package를 사용해야 한다.


CREATE OR REPLACE PACKAGE PKG_RECORDSET IS
 TYPE RECORDSET IS REF CURSOR;

 PROCEDURE up_Pkg_RecordSet
    (
        nParam NUMBER
    );

END PKG_RECORDSET;
/

CREATE OR REPLACE PACKAGE BODY PKG_RECORDSET IS
 PROCEDURE up_Pkg_RecordSet
    (
        nParam NUMBER
    )
 IS
 BEGIN
         OPEN pRECORDSET FOR
  SELECT Columns1, Columns2
  FROM Tables1
  WHERE Columns3 = nParam;
 END;
END PKG_RECORDSET;
/

입문자를 위한 사이트...
아래 3개만 다 봐도... 되지 않을까...

http://www.sqlleader.com/

http://technet.microsoft.com/ko-kr/sqlserver/default.aspx

http://www.sqlservercentral.com/scripts/

http://www.sqler.com

출처 : http://tong.nate.com/love_i/42009569

---- 링크가 사라지면 곤란...

Oracle 내부함수

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
+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
+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))
--------------------------------------------------------------------------
<ROWSET>
  <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>Cho</name>
<name>Joe</name>
<name>kim</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="NAME">Cho</column>
<column name="ID">1101</column>
<column name="S
 
<column name="NAME">Joe</column>
<column name="ID">1102</column>
<column name="S
 
<column name="NAME">kim</column>
<column name="ID">1103</column>
<column name="S
 
<column name="NAME">jijoe</column>
<column name="ID">1104</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>Cho</name>
<bonus>125</bonus>
 
<name>Joe</name>
<bonus>100</bonus>
 
<name>kim</name>
<bonus>100</bonus>
 
<name>jijoe</name>
<bonus>100</bonus>
 
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>
  <ID>1101</ID>
  <NAME>Cho</NAME>
  <BONUS>125</BONUS>
</emp>
 
<emp>
  <ID>1102</ID>
  <NAME>Joe</NAME>
  <BONUS>100</BONUS>
</emp>
 
<emp>
  <ID>1103</ID>
  <NAME>kim</NAME>
  <BONUS>100</BONUS>
</emp>
 
<emp>
  <ID>1104</ID>
  <NAME>jijoe</NAME>
  <BONUS>100</BONUS>
 
</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
                               236.666667
                               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
       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
                                250
                                240
                                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
                                250
                                220
                                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
                               236.666667
                               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
       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>

어제... 자주 가는 사이트 중의 하나인 SQLLeader.com에 SSIS에 대해서 문의를 했었다.
나와 비슷하게 느끼는 사람이 생각보다 있는 듯 했다.

그 중 읽어볼 만한 좋은 글을 퍼왔다. (혹시 site 폐쇠되면 ... ㅠㅠ)
link : http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=2951

-- 본인의 질문
안녕하세요.
SQL Leader.com 에서 눈팅하는 회원입니다.

질문이 아니라 업무 성향이라고 할까 처리 방법이라고 말해야 할까.. 좀 단어선택이 어렵네요.
뭐.. 하여간 그런 것들에 대한 이야기를 해보고 싶어서 글을 올립니다.
토론이라면 토론이고... 그냥 다른 분들은 처리를 어떤 방식으로 주로 하시는지 궁금하고요..
이런 류의 것이 대부분 그렇듯 상황에 따라서 적절한 방법이 있을 뿐, 절대적인 정답이 존재하기 어려운 것이기도 하고... (아.. 개인적으로 그렇게 생각한다는 것이지 제 말이 꼭 맞다는 건 아닙니다.)

SQL Leader.com에서 기술자료 올라오는 걸 읽는 것이 참 좋아서 자주 들리게 되는데...
SSIS 부분의 기술자료가 압도적으로 많은 것을 보게 됩니다.
저의 경우는 SSIS를 가능한한 사용하지 않고 OPENQUERY나 혹은 linked server를 사용해서 어떻게 해서든 Stored procedure를 사용해서 그 안에서 문제를 해결하려고 합니다. 제가 코드 보기가 더 쉽다는 이유이기도 하고... 아직 SSIS를 사용해야만 하는 상황을 만나지 못해서 일 수 도 있습니다.

다 읽어본 것은 아니지만, SSIS를 사용하지 않고 위에 제가 언급한 방법으로 충분히 해결 가능한 것들이라고 느껴지는 예가 대다수 인듯 합니다.
하지만, 기술자료가 이렇게나 많이 올라오는 것을 보면 SSIS가 무척이나 유용한 것 같은데...

SSIS의 매력을 알고 싶습니다.

p.s. 사실 이런건 토론게시판 같은데 올리고 싶은데... 그런 게시판이 여기는 없네요...
----------------------------------------------------------------------------

-- 한대성님의 글

어찌보면 SSIS의 기능을 하나 더 익히는 것보다 더 중요한 부분일 수 있습니다.
나름대로 사이트에 게시물을 하나하나 추가하곤 하는데, 전체적인 부분이 아닌, SSIS를 가지고 작업을 하면서 익힌 새로운 방법이나 지식들을 단편적으로 올린 것이기에 더욱 더 막연하게 보일 수도 있겠네요.

개인적인 경험을 바탕으로 설명드리겠습니다. 제가 했었던 업무나, 프로젝트 등입니다.

처음에 저는 옥션에서 DataWarehouse를 개발하고 관리하는 DBA로 SSIS의 이전 버전인 DTS를 접했습니다.
지금도 많은 자료에서 ETL(Extract, Transformation, Loading) 툴이라 하면 Data Warehouse에서 데이터를 추출하는 데 이용한 툴로 소개해 놓은 곳이 많지요.
운영 데이터, 여러 형태의 데이터 서버들에서 데이터를 읽어들인 후, 정재하고 DW Database에 데이터를 적재하는 부분에 저장 프로시저 대신 DTS 패키지들을 이용하였습니다. 그 다음엔 OLAP(Analysis Service) Cube를 Processing하고 Web Report를 Generating하고 특정 사용자들에게 추출한 보고서를 메일로 보내는 등 여러 작업들을 수행하였습니다.
데이터만 뽑는다면, 특히 동일 박스(서버)에서 데이터를 뽑아서 다른 DB에 적재하는 역할만 한다면 굳이 DTS, SSIS를 쓸 필요는 없겠지요.
제가 했었던 부분은 여러 서버들에 있는 데이터를 모으는 것이었고, OPENQUERY, Linked Server로 하기에는 부하 및 문제가 많았지요.
가장 간단한 문제로, SQL Server 2000에서 Linked Server를 이용할 때 NOLOCK 못 쓴다는 것. 그리고 Oracle 데이터를 Linked Server로 읽어올 때 간혹 부하가 걸리는 문제 등.
이러한 부분에 대해서는 DTS나 SSIS가 월등히 우수합니다.
그런 다음 OLAP Cube를 Processing하는 작업을 수행해야 하는데, 고맙게도 DTS 내에 이러한 기능이 있어서 이를 이용하였습니다.
그러고선 Report를 Generating하는 프로그램을 VB로 만들었는데, 이것 또한 실행토록 할려니깐 DTS의 프로그램 실행 작업 개체를 이용하니깐 편하더라구요.
또한 메일 보내는 기능도 있어서 이를 이용하게 되었습니다.

패키지가 100개가 넘고 200개가 넘으니깐 관리를 해야 겠더라구요. 1번 패키지가 끝나고 2번 패키지 수행, 2번 패키지 끝나고 3, 4번 패키지 수행 형태로.
이것 또한 패키지의 자식 패키지를 호출하는 기능을 사용하니깐 편하더라구요.

이리저리 해서 한 5년 동안 SQL 2000의 DTS를 아주 재미있게 접하고 사용했었습니다.

이후에 회사를 나와서 지금의 전 직장인 에이디컨설팅에서 여러 프로젝트를 하는데 많은 도움이 되었습니다.
그 중에서 몇 개 말씀드리자면.
금융권에서 데이터를 처리하는 배치 작업을 프로그램과 저장 프로시저로 처리하는 데, 작업의 특성 상 텍스트 파일로 만들로 (데이터를 만다고 하지요.) 이를 보내고 또 결과를 받아서 이를 반영하는 작업이 많았습니다.
처리 로직도 상당히 복잡하긴 해서 저장 프로시저 행 수가 2000~3000행을 넘는 것이 대부분이었구요. 작업이 돌면 몇 시간씩 돌고 에러가 나면 어디서 나는지 확인하기 어려웠습니다.
이러한 작업을 SSIS로 바꾸면서 이벤트 처리기를 이용해서 각 단계별 수행 상태를 기록하도록 하여 모니터링을 구현하였고, 순차적인 저장 프로시저에 비해 시각적인 면에서 우수하며, 병렬로 작업을 처리할 수 있는 SSIS 패키지가 유용하였습니다.
파일을 생성하고 검증하고 에러가 나는 경우에 이에 대한 처리를 하고 관리자에게 통보하고, 생성한 파일을 FTP로 전송하는 등 모든 필요한 기능을 SSIS로 구현할 수 있었지요.

다른 경우는 제조부분에서의 작업인데, 여러 서버 간에 데이터 동기화 및 처리 작업을 수행하는데, Linked Server로 처리하는 것에는 여러 문제가 있었습니다. 이 경우에도 SSIS를 이용해서 패키지를 만들고 Looping 기능을 이용해서 여러 서버에 적용하도록 구현해서 서버의 대수가 늘어나더라도 별 작업없이 쉽게 처리할 수 있도록 구현되었지요.

이 외에도 여러 서버의 성능 정보 파일을 읽어온 후, DB에 적재하고 이를 처리하고,  Raw 파일을 압축해서 다른 곳에 보관하도록 하는 기능 등에도 이용했었구요.
가장 최근에는 외부에서 특정 폴더에 데이터 파일을 떨궈(^^) 주면, SSIS 패키지가 이를 감시하고 있다가(WMI 감시자를 이용해서) 생성되면 이를 여러 곳에 있는 DB에 반영합니다. 반영하다가 에러가 나는 데이터는 별도의 폴더로 이동시키고 담당자에게 메일 보냅니다.
다른 여러 기능들도 있는데, 대략 여기까지만 정리하고..

그럼 SSIS가 항상 좋으냐.. 10개 고객사를 가는 경우 3~4군데 정도는 SSIS를 쓰지말고 그냥 BCP나 Bulk Insert, Linked Server 또는 단순히 저장 프로시저를 이용하라고 말씀드리고 그렇게 변경합니다.
SSIS의 장점을 꼽으로면 저 개인적으로는 SQL Query 기능 + Programing 기능을 최우선으로 생각합니다.
SQL 2005 부터는 CLR 기능을 이용해서 프로그램 기능을 쿼리에서 쉽게 사용할 수 있긴 하지만, 기본적으로 Ad-Hoc 쿼리 또는 저장 프로시저는 DB의 영역에서 처리를 해야 합니다.
간단히 예를 들면, 테이블에 있는 데이터를 이용해서 B라는 파일 서버에 파일로 생성하고 싶습니다. 이를 저장 프로시저로 구현한다면, BCP 명령을 이용해서 파일을 생성하고 이를 복사해야 합니다. 파일 복사는 xp_cmdshell 명령을 이용해서 해도 되겠지만, SQL 2005에서는 기본값으로 비활성화이지요. 갑갑해 집니다.
만약 이를 프로그램(VB.net이나 C#등)으로 만든다면 후딱 만들겠지요.
SSIS에서는 쿼리 뿐만 아니라 스크립트 작업 등도 구현할 수 있기에 구현 가능한 범위가 넓어졌다고 생각합니다.

또 다른 장점은 하나의 패키지에서 할 수 있는 작업이 다양하다라는 것입니다.
예를 들어, 웹 로그 데이터를 처리하는 작업을 생각해봅니다.
 a. 웹 서버에서 로그 파일이 생성되었는지를 확인하고 이를 DB에서 읽을 수 있는 위치로 복사합니다.
 b. 복사한 파일을 DB 테이블에 넣습니다. 넣다가 에러도 날 것이고 필터링도 해야 할 것이고.
 c. 데이터를 정재합니다. 거를 것 거르고, 넣을 것 넣고.
 d. 처리한 파일은 삭제하거나 압축해서 다른 곳으로 보냅니다.
 e. 데이터 처리가 끝나면 이를 집계해서 보고서를 생성하거나 결과물을 메일로 보냅니다.
이러한 기능을 구현해야 한다고 할 때, 쿼리가 할 수 있는 부분은 간단히 b, c 정도이고, a, d, e 단계를 구현하기 위해서는 별도의 프로그램이나 쉘 스크립트 등을 만들어 구현해야 합니다.
SSIS로 이를 구현한다면
 a. File 시스템 작업으로 구현
 b. 데이터 흐름 작업을 이용해서 정재, 변환을 거쳐 대상 테이블에 적재
 c. SQL 실행 작업을 이용해서 데이터 처리
 d. 프로세스 실행 작업이나 파일 시스템  작업으로 처리
 e. 메일 보내기 작업으로 구현

SSIS가 SQL Server에 포함된 기능이라고 해서 DB의 기능에만 국한해서 생각할 필요는 없습니다.

간단히 한 마디만 덧붙이자면..
우리가 쓰는 프로그램들을 VB6.0 으로도 만들 수 있고 C++로도 만들 수 있으며 C#, Java 등으로도 다 구현할 수 있듯이 SSIS로만 되는 것은 하나도 없습니다.
단지 SSIS를 이용하면 몸이 더 편할 수 있다라는 것이지요.
여러 업무들이 있을 것입니다. SSIS의 여러 기능들을 보고선 이를 구현하다가 편하다고 생각되거나 누가 편하다고 떠들면 고려해보면 되는 것이지요.

써 놓고 보니 아무 영양가도 없는 글인 것 같아 다시 읽어보기 민망하네요. 그냥 마치렵니다...^^

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