雨林: db

레이블이 db인 게시물을 표시합니다. 모든 게시물 표시

2009년 10월 22일 목요일

oracle meta取得　SQL

/* テーブル定義情報を取得 */

SELECT

SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name

,USER AS schema_name

,tbls.TABLE_NAME AS table_name

,comments.TABLE_TYPE AS table_type

,comments.COMMENTS AS table_comment

FROM

USER_TABLES tbls

LEFT OUTER JOIN USER_TAB_COMMENTS comments

tbls.TABLE_NAME = comments.TABLE_NAME

ORDER BY

tbls.TABLE_NAME

/* カラム定義情報を取得 */

SELECT

SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name

,USER AS schema_name

,cols.TABLE_NAME AS table_name

,cols.COLUMN_NAME AS column_name

,cols.COLUMN_ID AS ordinal_position

/* PKか否かの判別フラグ */

,(CASE

WHEN pk_cols.CONSTRAINT_NAME IS NULL THEN

ELSE

END) AS is_primary_key

/* PK制約名 */

,pk_cols.CONSTRAINT_NAME AS pk_constraint_name

/* PK制約カラム位置 */

,pk_cols.KEY_ORDINAL AS pk_key_ordinal

/* UQか否かの判別フラグ */

,(CASE

WHEN uq_cols.CONSTRAINT_NAME IS NULL THEN

ELSE

END) AS is_unique_key

/* UQ制約名 */

,uq_cols.CONSTRAINT_NAME AS uq_constraint_name

/* UQ制約カラム位置 */

,uq_cols.KEY_ORDINAL AS uq_key_ordinal

,cols.DATA_TYPE AS column_data_type

,cols.DATA_DEFAULT AS column_default

,cols.NULLABLE AS is_nullable

,cols.DATA_LENGTH AS max_length

,cols.DATA_PRECISION AS precision

,cols.DATA_SCALE AS scale

,comments.COMMENTS AS column_comment

FROM

USER_TABLES tbls

INNER JOIN USER_TAB_COLUMNS cols

cols.TABLE_NAME = tbls.TABLE_NAME

LEFT OUTER JOIN USER_COL_COMMENTS comments

comments.TABLE_NAME = cols.TABLE_NAME

AND comments.COLUMN_NAME = cols.COLUMN_NAME

/* PK情報を結合 */

LEFT OUTER JOIN (

SELECT

cons_inner.CONSTRAINT_NAME AS constraint_name

,cons_columns_inner.POSITION AS key_ordinal

,tbls_inner.TABLE_NAME AS table_name

,cols_inner.COLUMN_NAME AS col_name

,cols_inner.COLUMN_ID AS col_id

FROM

USER_TABLES tbls_inner

INNER JOIN USER_TAB_COLUMNS cols_inner

cols_inner.TABLE_NAME = tbls_inner.TABLE_NAME

INNER JOIN USER_CONSTRAINTS cons_inner

cons_inner.OWNER = USER

AND cons_inner.CONSTRAINT_TYPE = 'P'

AND cons_inner.TABLE_NAME = tbls_inner.TABLE_NAME

INNER JOIN USER_CONS_COLUMNS cons_columns_inner

cons_columns_inner.OWNER = cons_inner.OWNER

AND cons_columns_inner.CONSTRAINT_NAME = cons_inner.CONSTRAINT_NAME

AND cons_columns_inner.TABLE_NAME = cons_inner.TABLE_NAME

AND cons_columns_inner.COLUMN_NAME = cols_inner.COLUMN_NAME

) pk_cols

pk_cols.TABLE_NAME = cols.TABLE_NAME

AND pk_cols.COL_NAME = cols.COLUMN_NAME

AND pk_cols.COL_ID = cols.COLUMN_ID

/* UQ情報を結合 */

LEFT OUTER JOIN (

SELECT

cons_inner.CONSTRAINT_NAME AS constraint_name

,cons_columns_inner.POSITION AS key_ordinal

,tbls_inner.TABLE_NAME AS table_name

,cols_inner.COLUMN_NAME AS col_name

,cols_inner.COLUMN_ID AS col_id

FROM

USER_TABLES tbls_inner

INNER JOIN USER_TAB_COLUMNS cols_inner

cols_inner.TABLE_NAME = tbls_inner.TABLE_NAME

INNER JOIN USER_CONSTRAINTS cons_inner

cons_inner.OWNER = USER

AND cons_inner.CONSTRAINT_TYPE = 'U'

AND cons_inner.TABLE_NAME = tbls_inner.TABLE_NAME

INNER JOIN USER_CONS_COLUMNS cons_columns_inner

cons_columns_inner.OWNER = cons_inner.OWNER

AND cons_columns_inner.CONSTRAINT_NAME = cons_inner.CONSTRAINT_NAME

AND cons_columns_inner.TABLE_NAME = cons_inner.TABLE_NAME

AND cons_columns_inner.COLUMN_NAME = cols_inner.COLUMN_NAME

) uq_cols

uq_cols.TABLE_NAME = cols.TABLE_NAME

AND uq_cols.COL_NAME = cols.COLUMN_NAME

AND uq_cols.COL_ID = cols.COLUMN_ID

ORDER BY

table_name, ordinal_position

/* ビュー定義を取得 */

SELECT

SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name

,USER AS schema_name

,views.VIEW_NAME AS view_name

,views.TEXT AS definition

FROM

USER_VIEWS views

ORDER BY

views.VIEW_NAME

2009년 10월 21일 수요일

# 재귀적 CME SQL 사용례 (in MSSQL)

WITH CODE_MST_CTE (NO, CODE, CODE_NAME, CODE_GROUP, DEPTH, SORT_ORDER, BINARY_SORT)

AS(SELECT

NO,

CODE,

CODE_NAME,

CODE_GROUP,

DEPTH,

SORT_ORDER,

CAST(SORT_ORDER AS VARBINARY(16))

FROM

CODE_MST A

WHERE

DEPTH = 1 AND

CODE = ?

UNION ALL

SELECT

B.NO,

B.CODE,

B.CODE_NAME,

B.CODE_GROUP,

B.DEPTH,

B.SORT_ORDER,

CAST(BINARY_SORT + CAST(B.SORT_ORDER AS BINARY(4)) AS VARBINARY(16))

FROM

CODE_MST B INNER JOIN CODE_MST_CTE A ON A.NO = B.CODE_GROUP

)

SELECT

D.NO,

B.CODE_NAME,

C.CODE_NAME,

D.CODE_NAME,

E.VALNER_GRADE,

E.VALNER_YN,

D.DEPTH,

D.SORT_ORDER,

A.BINARY_SORT

FROM

CODE_MST_CTE A

INNER JOIN CODE_MST B

ON B.CODE_GROUP = A.NO AND B.DEPTH = 2

INNER JOIN CODE_MST C

ON C.CODE_GROUP = B.NO AND C.DEPTH = 3

INNER JOIN CODE_MST D

ON D.CODE_GROUP = C.NO

LEFT OUTER JOIN PCC_JUDG_ITEM E

ON D.NO = E.JUDG_GROUP_CODE

WHERE

B.NO = ?

AND

C.NO = ?

ORDER BY

A.BINARY_SORT

OPTION (MAXRECURSION 4);

2009년 10월 20일 화요일

Transact-SQL Enhancements

SQL Server 2005에서는 .NET 런타임의 호스팅을 통해서 VB.NET이나 C#과 같은 언어로 저장 프로시저, 함수 등의 쿼리 개체 작성이 가능합니다. 이러한 구현은 기존 T-SQL 언어의 부족한 부분을 지원할 수 있으나 궁극적으로 관계 데이터 처리 언어인 T-SQL를 대체하는 것은 아닙니다. 특히, SQL Server 2005 T-SQL은 많은 향상 기능을 제공하고 있습니다. 어떠한 기능들이 추가되고 향상되었는지 살펴보도록 하겠습니다.

TOP 연산자

그 동안 TOP 연산자에 대해서 아쉬운 점들이 많으셨죠? 특히 웹 어플리케이션에서 페이지를 처리하는 부분 등에 있어서 동적으로 변하는 결과 집합의 수를 처리하기 위해서는 서버측에서 동적 쿼리 형식을 사용해야 하는 등의 어려움이 있었습니다. 이제는 보다 쉽고 편리하게 그러한 형식의 쿼리를 구현할 수 있습니다. 물론 이것은 익히 잘 알고 있는 성능 관련 문제 들을 배제하고 순수하게 구문 그 자체로서 비교하는 것입니다.

■ SQL Server 2005에서 TOP 연산자의 향상된 기능은
ㆍ TOP 구문에서 변수나 서브쿼리 같은 수식 지원
ㆍ INSERT, UPDATE 그리고 DELETE 구문 지원

이제 몇 가지 따라 하기를 통해서 향상된 기능을 경험해 보도록 하겠습니다.

[따라하기] TOP 연산자 향상 기능
1. 변수를 사용한 경우

USE AdventureWorks

DECLARE @n AS smallint
SET @n = 5

SELECT TOP(@n) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

2. 서브쿼리를 사용한 경우

SELECT TOP(SELECT COUNT(*)/DATEDIFF(month, MIN(OrderDate),
MAX(OrderDate))
FROM Purchasing.PurchaseOrderHeader) *
FROM Purchasing.PurchaseOrderHeader
ORDER BY OrderDate DESC

TABLESAMPLE 절

앞서 살펴 본 TOP 연산자가 결과 집합을 제한하는 방법이라면, SQL Server 2005에 새로 도입된 TABLESAMPLE 은 SELECT 결과 집합을 샘플링으로 선택된 행 집합으로 제한하고자 할 경우에 사용될 수 있습니다. 즉 모든 데이터를 처리할 필요가 없거나, 정확한 결과가 아닌 대략적인 결과를 필요로 하는 경우입니다. 예를 들어, 주문 상세에서 샘플링을 통한 결과 집합에 대해서 대략적인 평균 주문 수량을 보고자 하는 경우 등입니다.

[따라하기] TABLESAMPLE 절

USE AdventureWorks

-- 아래는 전체 집합에 대한 평균 주문 수량을 구합니다.
SELECT AVG(OrderQty) FROM Purchasing.PurchaseOrderDetail

-- 아래는 30퍼센트의 샘플링 집합에 대한 평균 주문 수량을 구합니다.
SELECT AVG(OrderQty) FROM Purchasing.PurchaseOrderDetail
TABLESAMPLE (30 PERCENT)

CTE (Common Table Expressions)

SQL-99에 정의된 CTE는 파생 테이블(Derived Table, 혹은 일반적으로 인-라인 뷰라고 부르는)의 일종으로 생각할 수 있습니다. 그러나 파생 테이블은 외부 쿼리에서 한 번 이상 참조할 수가 없습니다. 이를 위해서 뷰를 만들 수도 있지만 이는 쿼리 개체를 만들고 데이터베이스에 저장하기를 요구합니다. CTE는 이와 같은 두 가지 어려움을 해소할 수 있는 방법을 제공합니다. 즉 파생 테이블처럼 쿼리에 직접 테이블의 표현 식을 작성하면서도 뷰처럼 한 번 이상 참조가 가능한 것입니다. CTE의 또 다른 형식은 바로 재귀적인(Recursive) 호출 기능입 니다. 이제 관계 형 데이터 모델의 순환 관계를 처리할 수 있는 구현 방법을 제공하는 것입니다.

■ CTE의 두 가지 형식은
ㆍ 비재귀적 CTE 는 파생 테이블 형식으로 테이블 표현을 정의하고 이를 외부 쿼리에서 한 번 이상
참조할 경우에 유용하게 적용될 수 있습니다.
ㆍ 재귀적 CTE는 순환 관계 모델을 처리할 경우 기본적으로 사용됩니다.

우선, 비재귀적 CTE를 살펴보시죠.
다음은 비재귀적 CTE의 대략적인 구문 형식과 따라 하기입니다.

[따라하기] 비재귀적 CTE
1. 구문 형식

WITH NonRecursiveCTE(<column_aliases>)
AS
(
<CTE_쿼리_정의>
)
SELECT * FROM NonRecursiveCTE ;

2. 예제

USE AdventureWorks ;

WITH YearlyOrders(orderyear, numorders)
AS
(
SELECT YEAR(OrderDate), COUNT(*)
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate)
)
SELECT
CurYear.orderyear
, CurYear.numorders
, PrevYear.numorders AS prev
FROM
YearlyOrders AS CurYear
LEFT OUTER JOIN YearlyOrders AS PrevYear
ON CurYear.orderyear = PrevYear.orderyear + 1
ORDER BY orderyear ;

[참고]
CTE가 한 일괄 처리(Batch)의 일부분으로 실행되는 경우, 이전 구문들은 ; (세미콜론)으로 구별되어야 합니다.

다음으로 재귀적 CTE에 대한 구문 형식과 예제입니다.

■ 아래의 재귀적 CTE 구문 형식에서
ㆍ 본문에는 UNION ALL 연산자로 구분된 두 개의 (멤버)쿼리를 포함한다.
ㆍ 앵커 멤버는, SQL Server가 단 한번 호출한다.
ㆍ 재귀 멤버는, 앵커 멤버를 시작으로 이전 단계에서 반환된 결과 집합을 표현하는 CTE 이름을
참조한다.
ㆍ SQL Server는 쿼리가 빈 결과 집합을 반환할 때까지 재귀 멤버를 반복 호출한다.

[따라하기] 재귀적 CTE
1. 구문 형식

WITH RecursiveCTE(<column_aliases>)
AS
(
-- 앵커 멤버(Anchor Member)
SELECT ...
FROM <some_table(s)>
...
UNION ALL

-- 재귀 멤버(Recursive Member)

-- RecursiveCTE 를 참조
SELECT ...
FROM <some_table(s)>
JOIN RecursiveCTE
...
)
SELECT ...
FROM RecursiveCTE ;

2. 예제

USE AdventureWorks ;

-- 회사의 조직 구성도를 보여주기 위한 CTE 예제
WITH EmpsCTE(EmployeeID, ManagerID, FirstName, LastName, Title, EmpLevel,
SortKey)
AS
(
-- 앵커 멤버
SELECT
EmployeeID, ManagerID, FirstName, LastName, em.Title
, 0, CAST(EmployeeID AS VARBINARY(900)) AS SortKey
FROM HumanResources.Employee em INNER JOIN Person.Contact ct
ON em.ContactID = ct.ContactID
WHERE ManagerID IS NULL

UNION ALL

-- 재귀 멤버
SELECT
E.EmployeeID, E.ManagerID, ct.FirstName, ct.LastName, E.Title
, ct.EmpLevel + 1, CAST(SortKey + CAST(E.EmployeeID AS BINARY(4)) AS
VARBINARY(900))
FROM HumanResources.Employee AS E
INNER JOIN EmpsCTE AS ct
ON E.ManagerID = ct.EmployeeID
)
SELECT EmpLevel
, REPLICATE‘( ‘| , EmpLevel) + FirstName +‘ ‘ + LastName
, Title
FROM EmpsCTE

큰 값 데이터 형식

SQL Server 2000까지 한 칼럼의 대용량 데이터 처리를 위해서 text, ntext 그리고 image 데이터 형식을 사용했었습니다만, 이러한 데이터 형식 사용을 사용한 개발 작업이 결코 쉽지가 않았습니다.

■ 기존 대용량 데이터 형식의 한계점
ㆍ 변수나 출력 파라미터로 선언할 수 없다.
ㆍ SUBSTRING, LEFT 와 같은 일반적인 문자열 함수를 사용할 수 없는 경우가 대부분이다.
ㆍ 특별한 형식의 작업을 위해서는 TEXTPTR, WRITETEXT 그리고 UPDATETEXT 와 같은 별도의
명령을 사용해서 약간의 절차적 프로그램을 작성해야 한다.

SQL Server 2005의 도입된 큰 값 데이터 형식(MAX 지시어)은 이러한 기존 한계점들을 해결함으로써 대용량 데이터 형식을 일반 데이터 형식과 동일하게 프로그램하고 손 쉽게다룰 수 있도록 지원합니다.

varchar, nvarchar 그리고 varbinary 형식의 선언 시의 MAX 지정자를 사용함으로써 최대 2GB 까지 저장할 수 있습니다.

[따라하기] 큰 값 데이터 형식
컬럼의 자료형이 큰 값 데이터 형식이어도 데이터 입력시 데이터를 큰 값 데이터 형식으로 바꾸어 주어야 합니다 use tempdb
go
create table a (id int identity,a varchar(max))
insert a values(replicate‘( A’,9000))
select * from a
select len(a) from a

insert a values(replicate(cast‘( A’as varchar(max)),9000))
select len(a) from a
큰 값 데이터 형식을 사용한 경우, 그 값의 일부를 변경하거나 새로운 값을 입력하는 등의 부분 데이터 처리가 필요할 수 있습니다. SQL Server 2005에서 MAX 지정자와 함께 도입된 .WRITE(expression, @offset, @length) 절은 이전보다 훨씬 편리한 방법으로 그와 같이 데이터 처리에 사용될 수 있습니다. 간단한 따라 하기를 통해 그 편리함을 경험해 보시죠.

[따라하기] .WRITE( ) 절 사용

-- big 문자열 부분을 funny 라는 문자열을 대체
UPDATE dbo.EmployeeNote
SET Notes.Write‘( funny’, CHARINDEX‘( big’, Notes)-1, 3)
WHERE EmployeeID = 2

SELECT * FROM dbo.EmployeeNote

[참고]
SQL Server 2005에서 새롭게 도입한 또 다른 데이터 형식이 바로 xml 입니다. 변수나 파라미터 뿐만 아니라 테이블 칼럼의 데이터 형식으로 선언하고 xml 데이터를 원시 수준에서 저장할 수 있습니다. 또한 XQuery 언어와 관련 메서드들을 사용해서 xml 데이터에 대한 검색 및 수정 작업이 가능합니다. 이에 대한 xml 관련 장에서 살펴보겠습니다.

T-SQL 오류 처리

T-SQL 개발자들의 또 다른 고충 중의 하나가 바로 사용자 정의 트랜잭션 내에서의 오류 처리 루틴의 작성이었습니다. SQL Server 2000까지는 일종의 인-라인 오류 처리를 해야만 했습니다. 각각의 INSERT, UPDATE, 그리고 DELETE 구문마다 매번 @@ERROR 전역 변수 를 사용해서 오류 검사를 하고 오류 발생 시 ROLLBACK 과 RETURN을 수행하도록 처리하 거나 혹은 GOTO 문을 사용해서 오류 처리 루틴으로 분기하는 등의 코드 형식을 사용했습 니다. 그 결과 코드의 길이가 방대해지고, 트랜잭션 코드 개발과 관리가 어려워졌으면 무엇 보다 잘못된 오류 처리로 인해서 데이터 무결성 문제가 빌생할 수도 있었습니다.

현재 대부분의 고급 언어들(예를 들어, C#, C++나 자바 등)은 예외 처리(Exception Handling)라는 개념으로 보다 향상된 구조적 오류 처리 루틴을 제공합니다. SQL Server 2005에서도 이와 유사한 구조적 오류 처리 방식을 도입했습니다. 바로 C#, VB.NET 등의 언어에서 지원하는 것과 유사한 TRY...CATCH 구문입니다.

[구문] TRY…CATCH

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
{ sql_statement | statement_block }
END CATCH
[;]

TRY 블록 내에서 오류가 발생하는 경우, 이를 CATCH 블록 내에서 처리할 수가 있습니다. 또한 CATCH 블록 내에서, 오류 처리에 필요한 (이전에는 사용할 수 없었던) 다양한 정보 들을 관련된 함수를 통해서 얻을 수가 있습니다.

■ 오류 처리 관련 함수들
ㆍ ERROR_NUMBER( ) 는 오류 번호를 반환합니다.
ㆍ ERROR_SEVERITY( ) 는 오류 심각도 수준을 반환합니다.
ㆍ ERROR_STATE( ) 는 오류 상태 번호를 반환합니다.
ㆍ ERROR_PROCEDURE( ) 는 오류가 발생한 저장 프로시저 또는 트리거의 이름을 반환합니다.
ㆍ ERROR_LINE( ) 은 오류가 발생한 루틴 내의 줄 번호를 반환합니다.
ㆍ ERROR_MESSAGE( ) 는 전체 오류 메시지를 반환합니다. 이 메시지에는 매개 변수에 제공된
값을 포함합니다.

[따라하기] TRY…CATCH

BEGIN TRY
-- ErrorNumber = 547번, FK 관련 참조 무결성 오류 발생
DELETE Production.Product
WHERE ProductID = 1
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER( ) AS ErrorNumber,
ERROR_SEVERITY( ) AS ErrorSeverity,
ERROR_STATE( ) AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE( ) AS ErrorLine,
ERROR_MESSAGE( ) AS ErrorMessage
END CATCH

위 구문은, 사용자 정의 트랜잭션을 포함하고 있지 않으므로, 간단한 형식입니다. 트랜잭션을 포함한다면, COMMIT/ROLLBACK 등에 대한 보다 안전하고 세밀한 처리가 요구될 것입니다.

DDL 트리거

SQL Server 2000에서는 INSTEAD-OF 트리거 방식이 추가되기는 했지만, 여전히 DML 구문에 한정된 작업이었습니다. 많은 DBA 들이 오랜 전부터 DDL 명령에 대한 트리거 기능을 요구해 왔습니다. 주로 감사(Auditing) 기능이나 사용자 실수(혹은 의도적인)에 의한 스키마 변경 등의 이벤트를 감시하고 추적하기 위한 용도였습니다.

SQL Server 2005에서 이러한 요구 사항을 만족할 수 있도록 DDL 트리거를 지원합니다. 두가지 수준에서 이벤트에 대한 트리거를 생성할 수 있습니다. 하나의 데이터베이스 수준에서 발생하는 이벤트에 대한 트리거(예를 들어, CREATE/ALTER/DROP TABLE, VIEW, USER 등), 나머지 하나는 서버 수준에서 발생하는 이벤트(예를 들어, CREATE/ALTER/DROP LOGIN, CERTIFICATE 등에 대한 트리거입니다.

구문 또한 기존의 DML 구문과 유사합니다. 다만 데이터베이스 수준일 경우에 “ON DATABASE”절을 지정하거나 서버 수준일 경우“ON ALL SERVER”절을 지정하는 것이 중요한 차이점입니다.

또한, 트리거 내에서 참조하는 inserted, deleted 테이블은 생성되지 않습니다. 대신에, EVENTDATA() 라는 함수 호출을 통해서 이벤트에 관련된 정보 들(예를 들어, 이벤트를 발생한 SPID, 발생 시간, 관련 구문 등)을 액세스할 수 있습니다. 특히 EVENTDATA()가 반환하는 데이터 형식이 XML 데이터이므로 전체 결과를 XML 변수 등에 저장하고 처리할 수도 있지만, SQL Server 2005에서부터 지원되는 XQuery 언어의 검색 기능을 활용해서 특정 값을 처리하도록 설계할 수도 있습니다.

[따라하기] 데이터베이스 수준의 DDL 트리거

-- 트리거 관련 데이터를 저장할 테이블
CREATE TABLE DDL_Log (
ID int IDENTITY(1, 1)
PRIMARY KEY
, TrData xml
)
GO

-- 테스트용으로 DROP, ALTER를 수행 할 테이블
CREATE TABLE trTestT1 ( a int )
GO
-- DDL 트리거
CREATE TRIGGER tr_Test1
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
DECLARE @trData XML
-- EVENTDATA( ) 함수 호출 결과를 XML로 저장하거나, XQuery로 검색
SET @trData = EVENTDATA( )

-- DDL 트리거는 ROLLBACK 처리가 가능하다
ROLLBACK

-- Log 기록
SET NOCOUNT ON
INSERT INTO DDL_Log (TrData) VALUES (@trData)
GO

/*
DDL 트리거 호출 테스트
*/

-- 1) DROP TABLE 이벤트는 직접 수행
DROP TABLE trTestT1
SELECT * FROM DDL_Log
GO

-- 2) ALTER TABLE 이벤트는 TRY ... CATCH 내에서 수행해 봄
BEGIN TRY
ALTER TABLE trTestT1 ADD b int
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER( ), ERROR_MESSAGE( )
END CATCH
GO
SELECT * FROM DDL_Log

예제에서 수행된 결과로 DDL_Log에 기록된 데이터를 보면, EVENTDATA( ) 함수의 출력 결과를 확인할 수 있습니다.

[따라하기] 데이터베이스 수준의 DDL 트리거

<EVENT_INSTANCE>
<EventType>DROP_TABLE</EventType>
<PostTime>2005-11-03T20:19:01.590</PostTime>
<SPID>52</SPID>
<ServerName>YUKON</ServerName>
<LoginName>YUKON\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>tempdb</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>trTestT1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS=”ON” ANSI_NULL_DEFAULT=”ON”
ANSI_PADDING=”ON”QUOTED_IDENTIFIER=”ON”ENCRYPTED=”FALSE”/>
<CommandText>DROP TABLE trTestT1
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>

다음은 서버 수준의 DDL 트리거 예제입니다.

[따라하기] 서버 수준의 DDL 트리거

-- CREATE LOGIN 에 대한 DDL 트리거
CREATE TRIGGER tr_Test2
ON ALL SERVER
FOR CREATE_LOGIN
AS
SET NOCOUNT ON

-- XQuery 의 value( ) 메서드를 사용, 특정 요소 값을 검색하고 비교
IF (EVENTDATA( ).value‘( (/EVENT_INSTANCE/LoginType)[1]’,‘ varchar(100)’)
=‘ SQL Login’)
BEGIN
ROLLBACK
PRINT‘ SQL 표준 사용자는 생성할 수 없습니다!’
END
ELSE
BEGIN
PRINT‘ Windows 사용자는 생성 가능합니다!’
END
GO
-- SQL 표준 사용자 생성 테스트 => 실패
CREATE LOGIN SqlUser WITH PASSWORD =‘ p@ssw0rd’
GO

-- Windows 표준 사용자 생성 테스트
CREATE LOGIN [yukonfan\Guest] FROM WINDOWS
GO
DROP LOGIN [yukonfan\Guest]
GO

SQL Server 2005에서는 이벤트 그룹이라는 개념으로, 관련된 이벤트에 대한 그룹 단위의 트리거 처리를 지원합니다. 예를 들어, CREATE/ALTER/DROP TABLE 개별 이벤트를 지정하는 대신, DDL_TABLE_EVENTS 라는 이벤트 그룹을 지정할 수도 있습니다.

다음은 트리거에 관련된 메타데이터 검색이 필요할 때 접근할 수 있는 카탈로그 뷰들입니다.

[따라하기] 트리거 관련 카탈로그 뷰

SELECT * FROM sys.triggers
SELECT * FROM sys.server_triggers
SELECT * FROM sys.server_trigger_events

DDL과 DML 트리거는 동기적으로 작동합니다. 즉, 트리거의 코드가 완료될 때까지 트리거를 발생시킨 작업으로 제어권을 넘기지 않는다는 것입니다.

SQL Server 2005에서는 비 동기적인 이벤트 처리를 지원하기 위한 새로운 방법으로 이벤트 알림을 도입했습니다. 여러 개의 서로 다른 어플리케이션에서 이벤트가 발생할 때 알림을 주도록 가입을 하기 되면, 해당 이벤트가 발생했을 때, 가입된 모든 어플리케이션들이 자신들의 동작이 종료될 때까지 기다릴 필요 없이 바로 그 이벤트에 대한 코드를 실행할 수 있습니다.

이벤트 알림(Event Notifications)

이벤트 알림은 앞서 소개한 DDL 트리거의 한계점을 해결할 수 있는 또 다른 접근 방법을 제공합니다. 서버나 데이터베이스에서 DDL 이벤트가 발생 시 이를 비 동기적으로 처리할 수 있으며, 이벤트 알림을 원격 서버에서 처리할 수도 있으므로, 확장성과 유연성을 제공됩니다. 또한 이벤트 알림은 기존의 SQL 프로필러나 SQLTrace를 통해서나 추적할 수 있었던 SQL Trace 이벤트에 대해서도 알림을 발생하고 처리할 수 있습니다.

어떻게 이벤트에 대한 비동기적이 처리가 가능할까요? 그것은 역시 SQL Server 2005에 새로 도입된 서비스 브로커(Service Broker) 서비스의 기능입니다. DDL 혹은 SQL Trace 이벤트가 발생하면 연관된 서비스 브로커 큐에 저장되고 이를 비 동기적으로 처리하는 것입니다. 서비스 브로커 서비스는 데이터베이스 엔진에서 신뢰할 수 있는 비동기적 메시징 플랫폼을 제공하는 새로운 기술입니다.

■ DDL 트리거나 이벤트 알림의 차이점 요약
ㆍ 트리거는 그 발생 원인이 되는 트랜잭션의 범위 내에서 동기적으로 실행되므로 필요 한 경우
ROLLBACK 이 가능합니다. 반면에 이벤트 알림은, 트랜잭션 범위 내에서 실행되지 않으므로
ROLLBACK 이 안되며, 서비스 브로커 서비스를 통해서 비 동기적으로 처리됩니다.
ㆍ 서버나 데이터베이스의 DDL 이벤트뿐만 아니라 SQL trace 이벤트에도 응답할 수 있습니다.
ㆍ 이벤트 알림은 원격 서버에서도 처리될 수 있습니다.

따라서, 이벤트 알림에 대한 기술은 서비스 브로커 서비스에 대한 이해를 요구합니다. 자세한 내용은 서비스 브로커 서비스 소개를 통해서 살펴봅니다.

이벤트 알림은 CREATE EVENT NOTIFICATION 구문을 사용해서 정의합니다. 이 때, 관련된 큐(Queue) 와 브로커 서비스에 대한 지정이 필요하므로 미리 정의되어 있어야 합니다.

[따라하기] 이벤트 알림

USE AdventureWorks

-- 이벤트 정보를 기록할 테이블
CREATE TABLE dbo.evtAudit (
evtData xml NULL
)

-- 이벤트 처리용 프로시저 생성
CREATE PROC dbo.up_Audit
AS
DECLARE @evtData varbinary(max)

-- 관련된 큐에서 첫 번째 메시지를 추출
-- RECEIVE 구문의앞에; 이와야한다
; RECEIVE TOP (1) @evtData = message_body
FROM AdventureWorks.dbo.AuditQueue

IF CAST(@evtData AS xml) IS NOT NULL
BEGIN
INSERT INTO dbo.evtAudit (evtData)
VALUES (CAST(@evtData AS xml))
END
GO

-- 관련 큐 생성
CREATE QUEUE dbo.AuditQueue
WITH STATUS = ON
, ACTIVATION (
-- 여기서 프로시저를 연계한다
PROCEDURE_NAME = AdventureWorks.dbo.up_Audit
, MAX_QUEUE_READERS = 1
, EXECUTE AS SELF
)
ON [DEFAULT]
GO

-- 서비스 브로커 서비스 생성
CREATE SERVICE dbo.AuditService ON QUEUE dbo.AuditQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- 이벤트 알림 생성, 관련 서비스 브로커 연동
CREATE EVENT NOTIFICATION en_Audit
ON SERVER
FOR CREATE_DATABASE, DROP_DATABASE
TO SERVICE‘ AuditService’,‘ current database’
GO

-- 테스트
CREATE DATABASE TestDB1
DROP DATABASE TestDB1
GO

SELECT * FROM dbo.evtAudit
GO

위 스크립트를 수행하면, evtAudit 테이블에서 2건의 이벤트에 대해 저장된 XML 데이터를 확인할 수 있습니다. 이 XML 데이터는 앞서 DDL 이벤트에서 제공되는 것과 유사한 요소로 구성되어 있습니다.

다음은 이벤트 알림과 관련된 메타데이터 검색을 위한 카탈로그 뷰들입니다.

[따라하기] 이벤트 알림 관련 카탈로그 뷰

SELECT * FROM sys.event_notifications
SELECT * FROM sys.server_event_notifications

SELECT * FROM sys.events
SELECT * FROM sys.server_events
SELECT * FROM sys.trace_events
SELECT * FROM sys.event_notification_event_types

스냅숏 격리(Snapshot Isolation)

SQL Server 2000에서는 READ COMMITTED 격리 수준을 사용하는 경우 데이터 검색 작업과 변경 작업이 동시에 같은 리소스(기본적으로 행 단위 잠금)에 발생할 때, 그 두 작업 간의 차단(Blocking)이 발생했습니다. 이는 데이터의 일관성을 보장하기 위해서이지만 과도한 차단은 서버의 안정적인 서비스의 장애 요소입니다. 많은 사용자들이 이 문제를 해결하기 위해서 SELECT 작업 시의 READ UNCOMMITTED(혹은 NOLOCK) 격리 수준이나 참고(Hint)를 사용해서 처리했습니다. 그러나 이 접근 방법은 Dirty Read(현재 변경 중인 데이터 읽기)를 발생시킴으로 인해서 중요한 업무에 있어서 데이터 일관성에 문제를 일으킬 수 있다는 것입니다.

SQL Server 2005에서는 기존의 차단 문제를 피하면서 Dirty Read와 같은 데이터 일관성 문제도 지킬 수 있는 또 다른 옵션으로 스냅숏 격리 수준(Snapshot Isolation Level)이라는 것을 지원합니다. (여기서 데이터 일관성 문제를 지킨다는 것에는 약간의 견해 차이가 있을 수 있습니다.)

스냅숏 격리 수준은, 데이터 검색 시의 변경 중인 데이터(Dirty)를 읽도록 허용하는 것이 아니라 변경되기 이전의 정보를 읽을 수 있는 기반 구조를 제공하는 것입니다. 즉 행이 변경될 때 그 행의 이전 버전을 별도로 저장해 둔 뒤에 해당 행의 검색 시의 제공을 하는 것입니다. 바로 이러한 기반 구조를 SQL Server 2005에서 행 버전 관리(Row Versioning)라고 합니다. 행 버전 관리를 위해 사용되는 데이터는 바로 tempdb 입니다. 행 버전 관리는 SQL Server 2005에서 아주 중요한 기반 구조로 제공되며 따라서 tempdb는 이전과 비교할 수 없을 정도로 중요한 시스템 데이터베이스로 자리잡게 될 것으로 예상됩니다. 행 버전 관리에 대해서 도 별도로 살펴봅니다.

■ 스냅숏 격리는 두 가지 방법으로 보다 상세한 제어가 가능합니다
ㆍ READ_COMMITTED_SNAPSHOT 데이터베이스 옵션
READ COMMITTED 격리 수준에서 Dirty Read와 Non-Repeatable Read를 피하기 위해서 잠금을
사용하는 대신, 행 버전 관리를 사용하도록 합니다.

ㆍ ALLOW_SNAPSHOT_ISOLATION 데이터베이스 옵션
이 옵션을 설정한 뒤, SET TRANSACTION ISOLATION LEVEL SNAPSHOT 세션 옵션을 설정하면
해당 세션에서 스냅숏 격리 수준이 적용됩니다.

순위 함수들

SQL Server 2005에 새로 도입된 아주 반가운 기능 중의 하나가 바로 순위 함수입니다. 관계형 데이터베이스에서 복잡한 형식의 결과 집합을 처리하는 경우 해당 결과를 위한 쿼리의 형식과 원본 데이터 집합에서 순번(Sequence Number) 혹은 행 번호(Row Number)라고 불리는 열이 필요한 경우가 많이 있습니다. 또는 순위에 해당하는 열이 반드시 있어야만 원하는 결과 집합을 산출하는 쿼리를 작성할 수 있는 경우도 있습니다.

그러나 SQL Server 2000에서는 순번 혹은 순위 값을 처리하기 위한 기능으로 IDENTITY 속성을 지정한 임시 테이블 혹은 테이블 변수를 사용하거나 IDENTITY( ) 함수를 SELECT ... INTO 문과 함께 적용하는 방법, 심지어는 상관 하위 쿼리 등을 사용해서 순번 혹은 순위 값을 가진 중간 결과 집합을 만들고 이를 이용해서 원하는 쿼리 형식을 만들어야만 했습니다. 이러한 방법은 쿼리 작성을 힘들게 할 뿐만 아니라 그 성능 또한 문제를 일으키는 것이 일반적이었습니다.

SQL Server 2005에서는 ROW_NUMBER( ), RANK( ), DENSE_RANK( ), 그리고 NTILE( )라는 4가지 함수를 통해서 아주 쉽게 결과 집합을 구성할 수 있도록 지원합니다. (쿼리 개발자들에게는 정말 반가운 소식입니다!)

순번이나 순위를 결정하기 위해서는, 행 집합이 특정 순서로 정렬이 되거나 혹은 특정 파티션 단위로 정렬되어 있어야 합니다. 이를 위해서 각 함수에는 OVER ([<partition_by_clausegt;] <order_by_clause>) 구문을 제공합니다.

아마도 자세한 설명이 필요 없을 것이라 판단 됩니다.

아래 몇 가지 예문 들을 통해서, SQL Server 2000에서 기본적으로 사용되던 방식과 SQL Server 2005에 도입된 기능을 비교 분석해 보시면 바로 이해가 되실 겁니다.

[따라하기] 순위 함수
1. ROW_NUMBER 예제

USE AdventureWorks -- 1) SQL Server 2000에서 상관 하위 쿼리를 사용한 방법 SELECT -- ProductID/LocationID 별 순번 생성을 위한 하위쿼리 내의 COUNT 작업 -- 아래에서, COUNT(*)를 사용하는 경우, 조건에서 < , <= 를 사용하는 경우 등에 -- 따라서 결과에는 차이가 발생할 수 있습니다. (SELECT COUNT(ProductID) FROM Production.ProductInventory p2 WHERE p2.ProductID < p1.ProductID OR (p2.ProductID = p1.ProductID AND p2.LocationID <= p1.LocationID) ) AS SeqNo , ProductID , LocationID , Quantity FROM Production.ProductInventory p1 ORDER BY ProductID, LocationID -- 2) SQL Server 2005에서 ROW_NUMBER( )를 사용한 방법 SELECT ROW_NUMBER() OVER (ORDER BY ProductID, LocationID) AS SeqNo , ProductID , LocationID , Quantity
FROM Production.ProductInventory
GO
-- 3) ROW_NUMBER()의 결과를 이용한, 일종의 페이징 처리
SELECT *
FROM (SELECT
ROW_NUMBER( ) OVER (ORDER BY ProductID, LocationID) AS SeqNo
, ProductID
, LocationID
, Quantity
FROM Production.ProductInventory) p1
WHERE p1.SeqNo BETWEEN 90 AND 100

마지막 3번의 경우, 웹 어플리케이션 개발자 분들이 아주 절실히 원하던 방법이죠. 여기서 앞서 소개한 TOP 구문의 향상 기능을 병행한다면 이전 보다 훨씬 좋은 쿼리 형식을 만들 수 있습니다. 물론 대용량 페이징 처리에서는 성능 문제를 추가로 고려해야 합니다.

RANK( ) 와 DENSE_RANK( ) 함수는 말 그 대로 결과 집합에 순위 형식의 열을 사용할 수 있도록 제공됩니다. DENSE_RANK( ) 함수는 복권 추천 결과와 같습니다. 1등이 여러 명 있다고 2, 3등이 없는 건 아니죠?

2. RANK( ), DENSE_RANK( ) 예제 SELECT
RANK() OVER (ORDER BY ProductID) AS Rank
, DENSE_RANK() OVER (ORDER BY ProductID) AS DenseRank , *
FROM Production.ProductInventory
GO마지막 4번째 NTILE( ) 함수는 지정된 정렬(혹은 파티션) 조건에 따라서 결과 집합을 지정된 N개의 버킷(Bucket)으로 나눈 결과를 제공합니다. 예를 들어, 한 클래스의 학생 수가 35명일 때 이를 4개의 그룹으로 나누고자 한다면, 한 그룹에 몇 몇씩 포함되면 될까요?

3. NTILE( ) 예제 SELECT
NTILE(3) OVER (ORDER BY Name) AS NBucket
, *
FROM Production.Product
WHERE ProductID <= 316
GO위 결과 집합은 5개의 행으로 구성됩니다. 이를 3개의 버킷으로 나누고자 합니다. 5를 3으로 나누어보시죠? 몫은 1, 나머지는 2가 됩니다. 즉, 전체 3개의 버킷에 1 행씩 포함되고 처음 2 버킷에 추가로 1행씩 포합됩니다.

새로운 관계 연산자들: PIVOT, UNPIVOT, 그리고 APPLY

혹시 Microsoft Access를 사용 해 본 적이 있습니까? 그렇다면 Jet Database 엔진에서 제공되는 TRANSFORM 구문에 대해서도 알고 계실 겁니다. 흔히 교차 집계 보고서(CROSSTAB) 또는 PIVOT 테이블이라고 부르는 결과 집합을 만들 수 있는 명령이죠. 한국형 분석 보고서의 대표적인 사례이지만 안타깝게도 SQL Server 2000 버전까지는 같은 기능의 명령이 지원되지 않았던 관계로 데이터베이스 개발자를 힘들게 만드는 또 다른 어려움 중의 하나였습니다.

SQL Server 2000에서는 이를 해결하기 위한 전통적인 방법으로, 주로 CASE, GROUP, 그리고 집계 함수 등을 활용했습니다. 이제 SQL Server 2005에서는 새로 도입된 PIVOT, UNPIVOT 연산자의 도움으로 이러한 결과 집합의 구성을 보다 쉽고 편리하고 만들 수 있습니다.

PIVOT 연산자는 행 집합을 칼럼으로 변환하는데(행->열) 사용될 수 있으며, 반대로 UNPIVOT 을 열을 행으로 변환하는데 사용될 수 있습니다.

다음 따라 하기 예제를 통해서 그 사용 법을 살펴보시죠.

[따라하기] PIVOT 과 UNPIVOT 연산자 사용 예

-- 고객별 / 년도별, SubTotal의 합을 보여주는 교차 집계 보고서 생성 SELECT CustomerID, [2001], [2002], [2003] FROM (SELECT YEAR(OrderDate) SalesYear, CustomerID, SubTotal FROM Sales.SalesOrderHeader WHERE OrderDate >=‘ 20010101’AND OrderDate <‘ 20040101’ AND CustomerID <= 10 ) Sales PIVOT (SUM(SubTotal) FOR SalesYear IN ([2001], [2002], [2003])) SalesPivot ORDER BY CustomerID GO -- 다음은 UNPIVOT 예제를 보여주기 위한 샘플 테이블을 생성 SELECT CustomerID, [2001], [2002], [2003] INTO dbo.SalesPivot FROM (SELECT YEAR(OrderDate) SalesYear, CustomerID, SubTotal FROM Sales.SalesOrderHeader WHERE OrderDate >=‘ 20010101’AND OrderDate <‘ 20040101’ AND CustomerID <= 10 ) Sales PIVOT (SUM(SubTotal) FOR SalesYear IN ([2001], [2002], [2003])) PSales GO -- SalesPivot 을 사용, 년도별 집계 정보를 행으로 변환 SELECT CustomerID, SalesUnPivot.SalesYear, SalesUnPivot.SubTotal FROM dbo.SalesPivot UNPIVOT (SubTotal FOR SalesYear IN ([2001], [2002], [2003])) SalesUnPivot ORDER BY CustomerID GO

이번엔 APPLY이 연산자를 살펴보시죠.

SQL Server 2000에서는 테이블 값 함수 형식을 제공했습니다. 함수가 결과 집합을 반환하도록 구성하고 이를 FROM 절 지정해서 함수를 마치 뷰처럼 사용할 있는 인터페이스를 제공했습니다. 즉, T-SQL FROM 절에 지정할 수 있는 대략 5가지 종류의 테이블 원본(Table Source) 중의 하나였습니다. SQL Server 2005에서는 FROM절에 지정된 테이블 원본과 APPLY 연산자를 활용 그 테이블 원본에서 반환되는 각 행에 대해서 테이블 값 함수를 반복 호출하고 다시 함수가 반환하는 결과 집합을 외부 테이블 원본과 결합해서 열 목록에서 사용할 수 있도록 지원합니다. 간단히 정리하면, 특정 테이블 원본의 행 집합에 대해서 테이블 값 함수를 반복 호출하고 그 결과를 조인할 수 있는 쿼리 형식을 제공하는 것입니다.

APPLY는 조인 방식의 따라 다시 두 가지로 나누어집니다. CROSS APPLY 와 OUTER APPLY 입니다. CROSS APPLY는 일종의 내부 조인(INNER JOIN)을 결과를 가집니다. 즉 함수가 결과 집합을 반환하지 않는 경우, 조인 결과에 포함되지 않습니다. 그렇다면 OUTER APPLY는 짐작이 되시겠죠? 네, 외부 조인(OUTER JOIN)의 결과 형식입니다. 함수가 결과 집합을 반환하지 않더라도 외부 테이블의 해당 행은 반환이 되며, 결과가 없는 열 값은 NULL로 표현됩니다.

[따라하기] PIVOT 과 UNPIVOT 연산자 사용 예 -- 예제용 테이블 값 함수
-- 고객ID를 파라미터 값으로, 해당 고객의 주문 정보 n 건을 반환
CREATE FUNCTION uf_CustOrders(
@custid int
, @n AS int
) RETURNS TABLE
AS
RETURN
SELECT TOP (@n) * FROM Sales.SalesOrderHeader
WHERE CustomerID = @custid
ORDER BY OrderDate DESC, SalesOrderID DESC
GO
-- CROSS APPLY, Customer테이블의 각 고객 별로 함수 호출, 조인된 결과 집합을 반환
-- 참고. ID=13 고객의 결과는 반환되지 않는다. SELECT c.CustomerID, c.AccountNumber, o.*
FROM Sales.Customer AS c
CROSS APPLY uf_custorders (c.CustomerID, 3) As o
WHERE c.CustomerID BETWEEN 13 AND 15
ORDER BY c.CustomerID, o.OrderDate DESC, o.SalesOrderID DESC
GO
-- OUTER APPLY, ID=13 고객도 반환된다.
SELECT c.CustomerID, c.AccountNumber, o.*
FROM Sales.Customer AS c
OUTER APPLY uf_custorders (c.CustomerID, 3) As o
WHERE c.CustomerID BETWEEN 13 AND 15
ORDER BY c.CustomerID, o.OrderDate DESC, o.SalesOrderID DESC
GO

OUTPUT 절

DELETE, INSERT, 혹은 UPDATE와 같은 DML 구문을 실행한 후에 그 반영된 결과 집합을 다시 재 사용해야 할 상황이 자주 발생합니다. 기존에 다시 그 결과 집합을 SELECT 하거나 혹은 UPDATE 구문의 경우 SET @변수 = 열 = 값 이라는 특수한 구문 형식을 빌어서 그 결과를 처리하기도 했습니다.

SQL Server 2005의 OUTPUT 절은 DELETE, INSERT, UPDATE 구문에서 변경된 행 집합을 저장해 두고 이를 inserted 와 deleted 라는 일종의 가상 테이블(그 내용은 이전 트리거와 동일)을 통해서 재 사용할 수 있도록 구문으로 제공합니다.

[따라하기] OUTPUT 절

-- SELECT ... INTO 구문으로 예제용 테이블 생성 SELECT TOP 50 SalesOrderID, SalesOrderDetailID, OrderQty INTO dbo.SalesEx FROM Sales.SalesOrderDetail GO -- DELETE 구문과 OUTPUT 절 예제 DELETE dbo.SalesEx OUTPUT deleted.* WHERE SalesOrderID = 43659 GO -- UPDATE 구문과 OUTPUT 절 예제 UPDATE dbo.SalesEx SET OrderQty = OrderQty * 100 OUTPUT deleted.*, inserted.* WHERE SalesOrderID = 43660 GO /* OUTPUT 절의 결과 집합을 테이블 변수에 저장하는 경우 주의. 테이블 변수를 사용하므로 아래 구문은 모두 동시에(배치 단위)에 실행함. */ -- 테이블 변수 선언 DECLARE @SalesExR TABLE ( SalesOrderID in , SalesOrderDetailID int , OrderQty smallint ) -- UPDATE 구문을 실행한 후, 변경 이전의 이미지를 반환하고 저장 UPDATE dbo.SalesEx SET OrderQty = OrderQty + 100 OUTPUT deleted.* -- 열의 정의는 일치해야 한다 INTO @SalesExR WHERE SalesOrderID = 43660 SELECT * FROM @SalesExR GO

BULK 행 집합 공급자(BULK Rowset Provider)

대용량 데이터의 대량 입력 시의 BCP.EXE 유틸리티나 BULK INSERT 명령을 사용합니다. 이 경우, 해당 결과 집합을 SELECT 문으로 재 가공하는 작업이 힘들었습니다. SQL Server 2005에서는 OPENROWSET 함수가 BULK 행 집합 공급자를 지원하도록 확장되었습니다.

BULK 행 집합 공급자와 OPENROWSET 함수를 활용, 파일을 읽어서 파일의 내용을 행 집합으로 반환할 수가 있습니다. BCP.EXE 유틸리티와 거의 동일한 옵션들을 지원하며 또한 이전과 동일한 서식 파일이나 혹은 XML 서식 파일을 사용할 수도 있습니다. (이전의 서식 파일을 써 보신 분들이라면 이것이 얼마나 힘든 작업인지 아실 겁니다! XML 서식 파일의 지원은 정말 반가운 소식입니다!)

뿐만 아니라 SQL Server 2005에서 새로 도입된 큰 값 데이터 형식, 즉 MAX 지시어로 정의된 열에 대해서 파일 내의 저장된 대용량의 문자 혹은 바이너리 형식의 데이터를 하나의 행과 하나의 열로 입력할 수가 있습니다. 정말 편리해졌죠!

이를 위해 세 가지 옵션이 제공됩니다. SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB 입니다.

SINGLE_CLOB와 SINGLE_NCLOB는 각각 varchar(max)와 nvarchar(max) 열에 입력을 할 경우 사용될 수 있습니다. 물론 xml 데이터 형식의 칼럼에 대해서 xml 파일의 내용을 저장하는데 사용될 수 있습니다. 특히, SINGLE_NCLOB 옵션의 경우 해당 파일이 유니코드 형식이어야 합니다. SINGLE_BLOB은 varbinary(max) 열에 파일의 내용을 입력하고자 할 경우입니다.

[따라하기] OPNEROWSET 함수와 BULK 행 집합 제공자

-- 예제용 테이블 CREATE TABLE dbo.BULK_Ex ( id int IDENTITY(1, 1) NOT NULL PRIMARY KEY , SetupLog varchar(max) ) GO -- 아래에서는, 임의의 text 형식 파일을 예제로 사용하면 됩니다. INSERT INTO BULK_Ex (SetupLog) SELECT b.SetupLog FROM OPENROWSET(BULK N’C:\Windows\setup.log’, SINGLE_CLOB) AS b(SetupLog) SELECT * FROM dbo.BULK_Ex GO

새로운 선언적 참조 무결성 동작

선언전 참조 무결성(DRI, Declarative Referential Integrity) 동작은 부모 값이 수정 및 삭제되는 경우의 외래 키(참조 키, FK)로 선언 된 자식 값의 동작을 정의 합니다. 이는 외래 키의 선언 시의 ON DELETE/ON UPDATE 절을 통해서 지정하게 됩니다. SQL Server 2000 버전까지는 두 가지 동작을 지원했습니다. 첫 번째는“NO ACTION”이라는 동작으로, 바로 부모값의 수정 및 삭제를 허용하지 않는 것입니다. 두 번째가“CASCADE”동작(SQL Server에서는“연계 참조 무결성”이라고 소개합니다)으로 부모 값이 수정되는 경우 자식 값도 동일 한 값으로 수정됨을 정의합니다. 문제는 바로 DELETE 작업에 대한“CASCADE”동작입니다. 부모 값이 삭제되는 경우 자식 값만 삭제되는 것이 아니라, 자식 값을 가진 행 전체가 삭제된다는 것이었습니다.

SQL Server 2005에서는 이제 두 가지 새로운 DRI 동작을 지원합니다. 바로 SET NULL 과 SET DEFAULT 입니다. 즉, 자식 값을 NUL 값이나 DEFAULT 제약 조건의 값으로 적용함을 의미합니다. 따라서 SET NULL의 경우의 FK로 선언된 열에 NULL 허용이 되어야 하며, SET DEFALUT 의 경우 해당 열의 DEFAULT 제약 조건이 선언되어 있거나 NULL 허용이어야 합니다. 이 두 가지 동작의 추가로 인해서 참조 무결성 자동화의 어려운 부분을 해소하는데 큰 도움이 되리라 생각됩니다.

[따라하기] SET NULL / SET DEFAULT 연계 참조 무결성 동작

USE tempdb -- 부모 테이블의 정의 CREATE TABLE dbo.Products ( ProductID int NOT NULL PRIMARY KEY , ProductName varchar(20) ) -- 자식 테이블-1, SET NULL을 적용한 경우 CREATE TABLE dbo.Orders1 ( OrderID int NOT NULL PRIMARY KEY , ProductID int NULL FOREIGN KEY REFERENCES dbo.Products(ProductID) ON DELETE SET NULL ) -- 자식 테이블-2, SET DEFAULT를 적용한 경우 CREATE TABLE dbo.Orders2 ( OrderID int NOT NULL PRIMARY KEY , ProductID int NULL DEFAULT (0) -- DEFAULT가 없으면 NULL로 적용 FOREIGN KEY REFERENCES dbo.Products(ProductID) ON DELETE SET DEFAULT ) GO -- 임의 데이터 입력 INSERT dbo.Products SELECT 0,‘ 보류’ INSERT dbo.Products SELECT 1,‘ XBOX’ INSERT dbo.Products SELECT 2,‘ NOTEBOOK’ INSERT dbo.Orders1 SELECT 100, 1 INSERT dbo.Orders2 SELECT 100, 1 GO -- 부모 값을 삭제한 후의 자식 테이블의 결과를 확인 DELETE dbo.Products WHERE ProductID = 1 SELECT * FROM dbo.Orders1 SELECT * FROM dbo.Orders2

메타데이터 뷰와 동적 관리 뷰(Dynamic Management Views)

SQL Server 2000에서 메타데이터 검색을 위해서 주로 시스템 저장 프로시저, 시스템 함수, 혹은 INFORMATION_SCHEMA 뷰를 사용했습니다. 이러한 방법으로도 원하는 메타데이터 접근이 어려운 경우엔 직접 관련된 시스템 테이블을 검색했습니다.

INFORMATION_SCHEMA 뷰의 경우엔 표준을 만족하기 위한 인터페이스므로 여전히 지원됩니다만, SQL Server 2005에서는 메타데이터 검색을 위한 새로운 인터페이스를 제공합니다. 바로 카탈로그 뷰(Catalog View)입니다. 카탈로그 뷰는 시스템 메타데이터 검색을 위한 일관되고 쉬운 인터페이스와 더불어 기존의 문제가 되었던 메타데이터의 대한 보안 기능을 위한 새로운 인터페이스입니다. 무엇보다 SQL Server 2005의 모든 기능에 대한 메타데이터 접근 방법입니다. 다양한 범주에 따라서 많은 카탈로그 뷰들이 제공되며 이들은 모두 sys 스키마 내에 정의됩니다.

하위 버전 호환성을 위해서 기존의 시스템 테이블들은 같은 이름과 같은 데이터를 반환하도록 지원됩니다만, 호환성 뷰(Compatibility View)라는 이름으로 뷰 집합으로서 제공됩니다. 이 책에서 각각의 주제 영역별로 관련된 카탈로그 뷰들이 소개될 것입니다.

동적 관리 뷰(DMV, Dynamic Management View)는 SQL Server 2005에서 새롭게 제공되는 또 다른 형식의 뷰입니다. 동적 관리 뷰는 SQL Server 2005 시스템의 현재 상태를 나타냅니다. SQL Server의 내부 메모리 구조나 상태에 대한 실시간 이미지 정보를 포함해서, I/O, 인덱스, 쿼리 플랜 캐시, 전체 텍스트 인덱스, 서비스 브로커, 트랜잭션 등 다양한 정보를 제공합니다 이를 이용해서 서비스 상태를 위한 실시간 모니터링이나 튜닝 및 최적화에 사용될 수 있습니다. 특히 SQL Server 2005에서는 이전 버전과 비교할 수 없을 정도로 많은 분량의 정보를 제공하고 있으며, SQL Server Management Studio(SSMS)에서는 사용자가 중요한 상태 정보들을 보다 쉽게 확인할 수 있도록 관련된 보고서 메뉴를 제공합니다. 동적 관리 뷰 또한 sys 스키마 내에서 정의되며 dm_ 로 시작하는 이름 규칙과 내부 데이터의 범주를 나타내는 이름으로 연결됩니다. 예를 들어, 이전의 sp_who 과 같은 결과를 보고자 할 경우 sys.dm_ exec_sessions를 검색합니다. SQL Server 2005에서는 특히 시스템의 유지 관리를 책임지고 있는 사용자게 있어서 이 동적 관리 뷰의 기능이 중요하게 다루어 질 것입니다.

기타

지금까지 소개한 기능들 이외에도 SQL Server 2005에서 새로 도입되거나 향상된 기능들은 많이 있습니다. 지면 상 모두 다루지는 못하지만 독자 여러분 스스로 반드시 살펴보시기를 권장합니다.

■ T-SQL 추가 향상 기능들
ㆍ ALTER INDEX 구문 추가
ㆍ 문-단위 다시 컴파일(재컴파일, Recompile) 기능 향상
ㆍ 새로운 서버 구성 옵션 제공
ㆍ EXCEPT 와 INTERSECT 연산자 추가
ㆍ SET SHOWPLAN_XML 과 SET STATISTICS XML 세션 옵션 추가

등 입니다.

* 출처 : DB 가이드 넷(http://www.dbguide.net/sql2005_dev_guide/sql2005_dev_guide_03.jsp)

2009년 10월 13일 화요일

HiRDB sql

5.コメント
　/* コメント */はありますが、-- の1行コメントがありません。

6.LIKE検索の文字列連結
　なんと、LIKE検索時に、LIKE '%' || '文字列' || '%'のような
　文字列連結をするとエラーになるようです。
　よって、CONDITIONが"CONTAINS","STARTS","ENDS","NOT_CONTAINS"
　のSQLは、HiRDBではエラーになると思われます・・・

　対策としては、下記の２パターンがあると思います。
　(1)buyerItemCd_CONTAINS,buyerItemCd_STARTS,buyerItemCd_ENDS,
　　 buyerItemCd_NOT_CONTAINSを作成し、%をつけた文字列を設定。
　　 SQLファイルとしては、現状の'%'|| || '%'を削除。
　(2)複数入力ありのSQLと同様に、ジョンギルユーティリティで、
　　 検索条件SQLは作成してしまう。
　　 /*$conditionSql*/

　いずれにせよLIKE検索は、%と_のESCAPE対応が必要なので、どちらかの
　対応は必要になります。
　どうせ複数入力ありの対応をやるなら(2)でも良いかなぁと思っています。
　ユーザのロールや所属に合わせた範囲指定などだけをSQLファイルに
　指定してもらうイメージです。

【現状のSQLファイル】
/*IF buyerItemCd != null && buyerItemCd_CONDITION == "CONTAINS" */AND BUYER_ITEM_CD LIKE '%' || /*buyerItemCd*/ ||
'%'/*END*/
/*IF buyerItemCd != null && buyerItemCd_CONDITION == "STARTS" */AND BUYER_ITEM_CD LIKE '%' || /*buyerItemCd*//*END*/
/*IF buyerItemCd != null && buyerItemCd_CONDITION == "ENDS" */AND BUYER_ITEM_CD LIKE /*buyerItemCd*/ || '%'/*END*/
/*IF buyerItemCd != null && buyerItemCd_CONDITION == "NOT_CONTAINS" */AND BUYER_ITEM_CD NOT LIKE '%' || /*buyerItemCd*/
|| '%'/*END*/

　ちなみに、ESCAPE処理を検証したところ、下記の感じでよさそうです。
　LIKE '文字列' ESCAPE '|'とした場合の文字列の組み立て形。
　(1)画面入力された文字の|を||に置換
　(2)画面入力された文字の%を|%に置換
　(3)画面入力された文字の_を|_に置換
　(4)画面入力された文字の％を|％に置換
　(5)画面入力された文字の＿を|＿に置換
　(6)CONTAINS、STARTS、ENDS、NOT_CONTAINSの条件に合わせて、%を前後に追加
　
　例：画面の検索条件 テスト|１２３４５→ '%テスト||１２３４５%'

2009년 10월 6일 화요일

HirDB vs Oracle sql

これまでのHiRDB対応でOracleのSQLと異なっているために、
つまづいた点を下記に纏めました。
少しずつノウハウの蓄積が必要な感じです。

1.シーケンス値の設定
[Oracle]
　シーケンス名.NEXTVAL
　forcast_id_seq.NEXTVAL
[HiRDB]
　NEXT VALUE FOR シーケンス名
　NEXT VALUE FOR forcast_id_seq

※HiRDBでは、単純なSELECT文ではシーケンス値の取得はできない。
　INSERT INTO SELECTやUPDATEの中でのみ利用可能。

2.SELECT文でのNULL取得
[Oracle]
　SELECT NULL AS TEST1 FROM TEST
[HiRDB]
　SELECT CAST(NULL AS VARCHAR) AS TEST1 FROM TEST

※HiRDBでは、OracleのようにはNULLを取得できない。
　型を指定してCASTが必要。

3.SQL ExecuterにおけるSQL実行
[HiRDB]
　改行コードが取り除かれて次行のSQLと連結されるため、先頭行に
　FROMを記述した場合に、SQLの構文エラーとなる。

　SELECT *
　FROM TEST;
　を実行とすると、SELECT *FROM TESTとなり、構文エラー。

4.総件数の取得について
　総ヒット件数返却機能というのがあるそうです。
　http://www.hitachi.co.jp/Prod/comp/soft1/manual/pc/d635640/W3560167.HTM
通常，総ヒット件数とヒットした行の値を求める場合，
　二つのSQL文を実行しますが，　総ヒット件数返却機能を
　使用すると，総ヒット件数を求めるSQLと，ヒットした行の
　値を求めるSQLとを，一つのSQLに統合できます。
　これによって，二つのSQL文を実行するための検索時間が，
　一つのSQL文を実行する検索時間とほぼ等しくなります。

　必ずORDER BY等はつくので、一覧取得のSQLにCOUNT(*) OVER()を
　加えて、1回で取得した方がよさそうです。

　※ちなみに、Oracleも同じSQLが実行できます
4.17.2　使用例
在庫表（ZAIKO）から単価（TANKA）が5000円以上の商品の総数と，その商品名（SNAME）を求め，数量（ZSURYO）でソートする場合の例を次に示します。
総ヒット件数返却機能を使用しない場合
SELECT COUNT(*) FROM ZAIKO WHERE TANKA>=5000
SELECT SNAME    FROM ZAIKO WHERE TANKA>=5000 ORDER BY ZSURYO
[説明]
総ヒット件数返却機能を使用しない場合は，二つのSQL文が必要となります。
総ヒット件数返却機能を使用する場合
SELECT COUNT(*) OVER(), SNAME 
                FROM ZAIKO WHERE TANKA>=5000 ORDER BY ZSURYO
[説明]
二つのSQL文の下線部が同じなので，総ヒット件数返却機能を使用することで一つのSQL文に統合し，初回取り出し時に総ヒット件数を取得できます。

2009년 10월 1일 목요일

java-DB(HiRDB or Oracle) 型対応

http://www.hitachi.co.jp/Prod/comp/soft1/manual/common/hirdb.htm

表 2‑1　アプリ-DB型対応表

項番	Java	COBOL（HiRDB接続）	HiRDB	Oracle
1	Integer	L1 基本項目名 PICTURE S9(9) COMPUTATIONAL.	INTEGER	NUMBER
2	Short	L1 基本項目名 PICTURE S9(4) COMPUTATIONAL.	SMALLINT	NUMBER
3	BigDecimal	L1 基本項目名 PICTURE S9(p-s)[V9(s)] COMPUTATIONAL-3. ここで、1<=p<=18， 0<=s<=p p=sの場合，SV9(s) s=0の場合，[V9(s)]を省略	DECIMAL	NUMBER
4	Double	L1 基本項目名 COMPUTATIONAL-2.	FLOAT	NUMBER
5	String	L1 基本項目名 PICTURE X(n). ここで、1<=n<=30000	MCHAR	CHAR
6	String	L2 集団項目名. L3 基本項目名1 PICTURE S9(4) COMPUTATIONAL. L3 基本項目名2 PICTURE X(n). ここで、1<=n<=32000	MVARCHAR	VARCHAR2
7	Date	L1 基本項目名 PICTURE X(10).	DATE	使用しない
8	Time	L1 基本項目名 PICTURE X(8).	TIME	使用しない
9	Timestamp	L1 基本項目名 PICTURE X(22).	TIMESTAMP	TIMESTAMP
10	byte[]	L2 集団項目名 [USAGE [IS]] SQL TYPE IS BLOB(n{K\|M\|G}). 単位省略時： 1<=n<=2147483647 単位がK： 1<=n<=2097152 単位がM： 1<=n<=2048 単位がG： 1<=n<=2	BLOB BINARY	BLOB
11	String	同上	BLOB BINARY	BLOB
12	String	使用しない	使用しない	CLOB
13	String	HIRDBのMVARCHAR型に従う	FREEWORD	使用しない
14	byte[]	HIRDBのBLOB型に従う	SGMLTEXT	使用しない
15	String	同上	SGMLTEXT	使用しない

2009년 10월 22일 목요일

oracle meta取得 SQL

2009년 10월 21일 수요일

# 재귀적 CME SQL 사용례 (in MSSQL)

2009년 10월 20일 화요일

재귀적 CTE

Transact-SQL Enhancements

2009년 10월 13일 화요일

HiRDB sql

2009년 10월 6일 화요일

HirDB vs Oracle sql

2009년 10월 1일 목요일

java-DB(HiRDB or Oracle) 型対応

oracle meta取得　SQL