'IT/SQL SERVER' 카테고리의 글 목록 (16 Page)

IT/SQL SERVER

MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수 2011.02.28
MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수 2011.02.28
특정 키워드를 포함하는 모든 프로시져 보기 2011.02.28
TABLE 별 데이터 건수 및 사이즈 확인 2011.02.28
DB Size Check Script 2011.02.28

MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수

쓸만한게없네 2011. 2. 28. 10:02

2011. 2. 28. 10:02

http://www.sqler.com/270809

안녕하세요?

지난 번에 이어 두 번째 Function 비교 시간입니다.

또다른 9가지 나갑니다. ^^.

11. 문자열 변환

구분	MSSQL	Oracle
함수	REPLACE	REPLACE
용례	SELECT REPLACE('1234567','123','321')	SELECT REPLACE('1234567','123','321') FROM DUAL
결과	3214567	3214567

è MSSQL의 STUFF라는 함수가 있는데요. 이것은 엑셀의 REPLACE와 같습니다.

SELECT STUFF('13579',2,3,'222')

--> 결과

12229

12. 음절의 첫 글자만 대문자로 변환(Pascal Case)

구분	MSSQL	Oracle
함수	지원하지 않음	INITCAP
용례	-	SELECT INITCAP('we are the world') FROM DUAL;
결과	-	We Are The World

11. 문자열에서 특정 문자만 쏙쏙 골라서 다른 문자로 변환, 정확하게 이야기하면 지정한 문자와 맞으면 특정 문자로 처리함, 또는 특정 지정문자가 없는 경우 해당 문자를 삭제함.

구분	MSSQL	Oracle
함수	지원하지 않음	TRANSLATE
용례	-	SELECT TRANSLATE('NothingToUse','o','!') FROM DUAL; SELECT TRANSLATE('BFG123', '12345BCDEFG', '123XXXXXXXX') FROM DUAL;
결과	-	N!thingT!Use XXX123

è 아래 SAMPLE을 참조하세요.

create table translate_test

(

mem_id varchar(20)

)

;

INSERT INTO translate_test (mem_id) values ('ABCD1234');

INSERT INTO translate_test (mem_id) values ('abcdef');

INSERT INTO translate_test (mem_id) values ('585472');

COMMIT;

select mem_id, translate(mem_id, '0123456789' || mem_id, '0123456789')

from translate_test;

-- 결과

ABCD1234 1234

abcdef

585472 585472

14. 나열한 인수 중 가장 큰 값 / 작은 값을 반환

구분	MSSQL	Oracle
함수	지원하지 않음	GREATEST / LEAST
용례	-	SELECT GREATEST('Z9', '나의','콜','A341', '999','123') FROM DUAL; SELECT LEAST('Z9', '나의','콜','A341', '999','123') FROM DUAL;
결과	-	콜 / 123

è 비교하는 것은, 문자순위(A보다는 Z가 크다. 한글은 자모순서에 의함), 숫자순위(0보다는 9가 크가), 글자수단위(같은 문자로 시작하더라도 결국 글자수가 많은 것이 크다.)

15. 길이 가져오기, 또는 BYTE 단위 길이 가져오기

구분	MSSQL	Oracle
함수	LEN, DATALENGTH	LENGTH
용례	SELECT LEN('NothingToUse') SELECT DATALENGTH('NothingToUse') SELECT LEN('지원하지 않음') SELECT DATALENGTH('지원하지 않음')	SELECT LENGTH('NothingToUse') FROM DUAL; SELECT LENGTH('지원하지 않음') FROM DUAL;
결과	12 / 12 / 7 / 13	12 / 7

è 위에서 보시면 알겠지만, BYTE 단위로 길이를 확인하시려면 LEN 이 아닌 DATALENGTH 를 사용하셔야 합니다.

16. NULL 일 경우 대체값 표시.

구분	MSSQL	Oracle
함수	ISNULL	NVL
용례	SELECT ISNULL(QTY1,100) FROM A_TEMP	SELECT NVL(QTY1,100) FROM A_TEMP;
결과	100 (값이 NULL일 경우)	100 (값이 NULL 일 경우)

17. 숫자형을 문자형으로 변환

구분	MSSQL	Oracle
함수	STR, CONVERT, CAST	TO_CHAR
용례	SELECT 123 + 456 SELECT STR(123) + STR(456) SELECT STR(123,3,0) + STR(456,3,0)	SELECT TO_CHAR(123) \|\| TO_CHAR(456) FROM DUAL; SELECT 123 \|\| 456 FROM DUAL;
결과	579 123 456 123456	123456 123456

è MSSQL의 STR는 기본 자릿수가 10자리입니다.

è Oracle에서는 위에서 보시다시피 숫자형태를 Concat하더라도 자동적으로 문자로 나옵니다.

è 물론 MSSQL에서는 CONVERT나 CAST를 훨씬 더 많이 사용합니다.

SELECT CONVERT(VARCHAR(3),123) + CONVERT(VARCHAR(3),456)

--> 결과

123456

18. 문자형을 숫자형으로 변환

구분	MSSQL	Oracle
함수	CONVERT, CAST	TO_NUMBER
용례	SELECT '123' + '456' SELECT CONVERT(INT,'123') + CONVERT(INT,'456') SELECT CAST('123' AS INT) + CAST('456' AS INT)	SELECT TO_NUMBER('123') + TO_NUMBER('456') FROM DUAL;
결과	123456 / 579 / 579	579

19. 조건처리 구문

구분	MSSQL	Oracle
함수	CASE	CASE (DECODE)

è MSSQL CASE문은 여러 개의 조건 중 맞는 결과를 표시하는 것이고, Oracle의 DECODE는 참/거짓에 따라 결과를 표시합니다.

1) MSSQL CASE SAMPLE

-- 1. 테이블생 성

CREATE TABLE CASE_STUDY

(PKEY INT PRIMARY KEY,

DATA1 VARCHAR(20),

DATA2 VARCHAR(30)

);

-- 2. 자료 입력

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '가');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '나');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '다');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '라');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '마');

-- 3. 데이터 출력

SELECT

PKEY,

CASE

WHEN PKEY = 1 THEN DATA1

WHEN PKEY = 2 THEN DATA2

WHEN PKEY = 3 THEN DATA1

ELSE DATA2

END AS DATA

FROM

CASE_STUDY

;

-- 4. 결과

1 A -- 1일 때 DATA1값

2 나 -- 2일 때 DATA2값

3 C -- 3일 때 DATA1값

4 라 -- 아니면 DATA2값

5 마 -- 아니면 DATA2값

2) 같은 자료를 Oracle DECODE를 사용할 경우

-- 1. 테이블생 성

CREATE TABLE CASE_STUDY

(

PKEY NUMBER(9),

DATA1 VARCHAR2(20),

DATA2 VARCHAR2(30)

);

-- 2. 자료 입력

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (1, 'A', '가');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (2, 'B', '나');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (3, 'C', '다');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (4, 'D', '라');

INSERT INTO CASE_STUDY (PKEY, DATA1, DATA2) VALUES (5, 'E', '마');

-- 3. 데이터 출력

SELECT

PKEY,

DECODE(PKEY, 1, DATA1,

DECODE(PKEY, 2, DATA2,

DECODE(PKEY, 3, DATA1,

DATA2)

)

) AS DATA

FROM

CASE_STUDY

-- 또는

SELECT

PKEY,

DECODE(PKEY, 1, DATA1,

2, DATA2,

3, DATA1,

DATA2) AS DATA

FROM

CASE_STUDY

-- 4. 결과

1 A -- 1일 때 DATA1값

2 나 -- 2일 때 DATA2값

3 C -- 3일 때 DATA1값

4 라 -- 아니면 DATA2값

5 마 -- 아니면 DATA2값

è Oracle 8.1.7부터는 MSSQL과 거의 동일한 CASE문을 제공합니다.

위 구문을 MSSQL 구문과 동일하게 하셔도 결과는 같습니다.

오늘은 여기까지이고요, 조만간 또 3탄 올리겠습니다.

이상 [쓸만한게없네]였습니다.

저작자표시 비영리

MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수

쓸만한게없네 2011. 2. 28. 10:01

2011. 2. 28. 10:01

http://www.sqler.com/269782

안녕하세요?

MSSQL과 Oracle 함수를 비교하신 분들이 많은데요. 의외로 예제를 실어 놓은 곳이 거의 없더군요. 하여 준비해 보았습니다.

하지만, 한 번에 너무 많은 것을 올려놓으면 보기 힘들기에…

오늘은 그 첫번째 시간으로 문자열 중 10가지만 적어봅니다.

1. 문자 식에서 가장 왼쪽 문자의 ASCII 코드 값 반환 (숫자로 표시됨)

구분	MSSQL	Oracle
함수	ASCII	ASCII
용례	SELECT ASCII('A')	SELECT ASCII('A') FROM DUAL
결과	65	65

è MSSQL이든 ORacle이든 ‘’ 안에 여러 개의 문자가 있어도 가장 좌측 값만 반환합니다.

즉, SELECT ASCII(‘A’) 나 SELECT ASCII(‘ABCDEFG’) 나 결과는 같습니다.

2. 문자 합치기

구분	MSSQL	Oracle
함수	+	CONCAT 또는 \|\|
용례	SELECT '동해물과' + '백두산이'	1. SELECT '동해물과 ' \|\| '백두산이' FROM DUAL 2. SELECT CONCAT('동해물과 ','백두산이') FROM DUAL
결과	동해물과 백두산이	동해물과 백두산이

3. ASCII 코드를 문자로 변환하기

구분	MSSQL	Oracle
함수	CHAR	CHR
용례	SELECT CHAR(67)	SELECT CHR(67) FROM DUAL
결과	C	C

è 참고로 9번은 TAB, 10은 LF(Line Feed), 13번은 CR(Carriage Return) 입니다.

4. 좌측에서 몇 번째에 해당 문자가 있는지 알려주기

구분	MSSQL	Oracle
함수	CHARINDEX	INSTR
용례	SELECT CHARINDEX('마이', '고마해라. 마이 무우따 아이가?')	SELECT INSTR('고마해라. 마이 무우따 아이가?','마이' ) FROM DUAL
결과	7	7

è MSSQL과 Oracle의 함수사용 순서가 다릅니다.

è MSSQL에는 패턴찾기에 PATINDEX를 많이 사용합니다. 위와 같은 결과를 얻으려면

SELECT PATINDEX('%마이%', '고마해라. 마이 무우따 아이가?')

처럼 사용하면 됩니다.(와일드 카드 사용 가능)

5. 대문자 변환, 소문자 변환

구분	MSSQL	Oracle
함수	UPPER / LOWER	UPPER / LOWER
용례	SELECT UPPER('aBcDeF'), LOWER('aBcDeF')	SELECT UPPER('aBcDeF'), LOWER('aBcDeF') FROM DUAL
결과	ABCDEF abcdef	ABCDEF abcdef

6. 좌측공간을 특정 문자로 채워주기

구분	MSSQL	Oracle
함수	지원하지 않음(없음)	LPAD
용례	-	SELECT LPAD('13579',10, '0') FROM DUAL
결과	-	0000013579

è MSSQL에선 없는 함수이기 때문에 아래와 같이 사용자함수를 만들어서 사용하기도 합니다.

-- 1. FUNCTION 만들기

CREATE FUNCTION dbo.UFN_LPAD

(

@INPUT VARCHAR(8000),

@COUNT AS INT,

@FILLCHAR AS CHAR(1)=' '

)

RETURNS varchar(200)

BEGIN

RETURN

CASE

WHEN LEN(@INPUT) >= @COUNT THEN LEFT(@INPUT, @COUNT)

ELSE

LEFT(REPLICATE(@FILLCHAR, @COUNT), @COUNT-LEN(@INPUT)) + @INPUT

END

-- 2. SAMPLE

SELECT dbo.UFN_LPAD('12',10,'0') AS PR_KEY

--> 결과

0000000012

7. 우측공간을 특정 문자로 채워주기

구분	MSSQL	Oracle
함수	지원하지않음(없음)	RPAD
용례	-	SELECT RPAD('13579',10, '0') FROM DUAL
결과	-	1357900000

è MSSQL에선 없는 함수이기 때문에 UFN_LPAD처럼 함수를 만들어서 씁니다.

다만, REPLICATE라는 함수가 있는데, 이것은 특정문자를 연속적으로 채워 줄 뿐, RPAD와는 조금 다릅니다.

-- 사용례

SELECT REPLICATE('0',10)

--> 결과

0000000000

또한, SPACE라는 함수는 공백만 채워줍니다.

-- 사용례

SELECT '나의' + SPACE(10) + '것'

--> 결과

나의 것

8. 좌/우 공백 없애주기

구분	MSSQL	Oracle
함수	LTRIM / RTRIM	LTRIM / RTRIM
용례	SELECT LTRIM(' 아버지'), RTRIM('어머니 ')	SELECT LTRIM(' 아버지'), RTRIM('어머니 ') FROM DUAL
결과	아버지 어머니 à (공백제거됨)	아버지 어머니 à (공백제거됨)

9. 문자의 음성표현을 가지는 문자열을 반환. 국내에서는 흔히 사용하지 않음(한글 동작 안함)

b, f, p, v = 1

c, g, j, k, q, s, x, z = 2

l = 4

m, n = 5

r = 6

구분	MSSQL	Oracle
함수	SOUNDEX	SOUNDEX
용례	SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');	SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') FROM DUAL
결과	S252 S200	S530 S530

è 어라? 예제에는 같은 것으로 되어 있는데, 실제 결과는 다르네요. -_- 이것 참…

10. 전체 문자열에서 지정한 길이만큼의 문자열 반환

구분	MSSQL	Oracle
함수	SUBSTRING	SUBSTR
용례	SELECT SUBSTRING('1234567890',4,5)	SELECT SUBSTR('1234567890',4,5) FROM DUAL
결과	45678	45678

è 4번째 자리에서 시작해서 다섯 글자를 가져옵니다.

오늘은 여기까지이고요, 조만간 2탄 올리겠습니다.

이상 [쓸만한게없네]였습니다.

저작자표시 비영리

특정 키워드를 포함하는 모든 프로시져 보기

쓸만한게없네 2011. 2. 28. 09:50

2011. 2. 28. 09:50

http://www.sqler.com/318666

많이들 알고 계시겠지만, 프로시져에서 특정 문자열을 포함하는 것들만 가져오는 명령어입니다.

1. Schema 에서 가져오는 방법

SELECT ROUTINE_NAME

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='PROCEDURE'

AND ROUTINE_DEFINITION LIKE '%키워드%'

ORDER BY ROUTINE_NAME;

2. Comments 에서 가져오는 방법

SELECT DISTINCT A.NAME

FROM dbo.SysObjects A JOIN dbo.SysComments B ON A.ID = B.ID

WHERE A.TYPE = 'P' AND B.TEXT LIKE '%키워드%'

ORDER BY A.NAME;

à DISTINCT 를 한 이유는, 해당 Procedure 안에 같은 단어가 여러 번 나올경우,

결과가 여러 번 나오기 때문입니다.

참고로 1.번 방법은 VARCHAR(4000) 이상의 크기를 가진 Procedure는 찾아내지 못합니다.

저작자표시 비영리

TABLE 별 데이터 건수 및 사이즈 확인

쓸만한게없네 2011. 2. 28. 09:49

2011. 2. 28. 09:49

SELECT Schema_name(tbl.schema_id) AS [Schema],

tbl.name,

Coalesce((SELECT pr.name

FROM sys.database_principals pr WITH (nolock)

WHERE pr.principal_id = tbl.principal_id),

Schema_name(tbl.schema_id)) AS [Owner],

tbl.max_column_id_used AS [Columns],

CAST(CASE idx.index_id

WHEN 1 THEN 1

ELSE 0

END AS BIT) AS

[HasClusIdx],

Coalesce((SELECT SUM (spart.ROWS)

FROM sys.partitions spart WITH (nolock)

WHERE spart.object_id = tbl.object_id

AND spart.index_id < 2), 0) AS [RowCount],

Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE

WHEN a.TYPE <> 1 THEN a.used_pages

WHEN p.index_id < 2 THEN a.data_pages

ELSE 0

END)

FROM sys.indexes AS i WITH (nolock)

JOIN sys.partitions AS p WITH (nolock)

ON p.object_id = i.object_id

AND p.index_id = i.index_id

JOIN sys.allocation_units AS a WITH (nolock)

ON a.container_id = p.partition_id

WHERE i.object_id = tbl.object_id), 0.0) / 1024 AS [IndexMB],

Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE

WHEN a.TYPE <> 1 THEN a.used_pages

WHEN p.index_id < 2 THEN a.data_pages

ELSE 0

END)

FROM sys.indexes AS i WITH (nolock)

JOIN sys.partitions AS p WITH (nolock)

ON p.object_id = i.object_id

AND p.index_id = i.index_id

JOIN sys.allocation_units AS a WITH (nolock)

ON a.container_id = p.partition_id

WHERE i.object_id = tbl.object_id), 0.0) / 1024 AS [DataMB],

tbl.create_date,

tbl.modify_date

FROM sys.tables AS tbl WITH (nolock)

INNER JOIN sys.indexes AS idx WITH (nolock)

ON ( idx.object_id = tbl.object_id

AND idx.index_id < 2 )

INNER JOIN MASTER.dbo.spt_values v WITH (nolock)

ON ( v.NUMBER = 1

AND v.TYPE = 'E' )

--WHERE tbl.Name like '%tablename%'

ORDER BY 8 DESC

저작자표시 비영리

DB Size Check Script

쓸만한게없네 2011. 2. 28. 09:48

2011. 2. 28. 09:48

declare @dbname

sysname

set @dbname = null

if @dbname

is not null and @dbname

not in (select name from

master.dbo.sysdatabases)

begin

raiserror('You''re just one step away

from the results. please verify the database name is correct and try again. www.sqlServer-Training.com', 16,1)

end

set nocount on

if exists (select * from sysobjects where name = '#sizeinfo' and type = 'u')

drop table #sizeinfo

create table #sizeinfo

(

db_name varchar(100) not null primary key clustered,

total dec (7, 1),

data dec (7, 1),

data_used dec (7, 1),

[data (%)] dec (7, 1),

data_free dec (7, 1),

[data_free (%)]

dec (7, 1),

log dec (7, 1),

log_used dec (7, 1),

[log (%)] dec (7, 1),

log_free dec (7, 1),

[log_free (%)]

dec (7, 1),

status dec (7, 1)

)

set nocount on

insert

#sizeinfo ( db_name, log, [log (%)] , status

) exec ('dbcc sqlperf(logspace)

with no_infomsgs')

print '' print ''

if @dbname

is null

declare dbname cursor for select name from master.dbo.sysdatabases where

not status

& 32 = 32

and not status & 512

= 512 order

by name asc

else if @dbname is not null

begin

delete from

#sizeinfo where db_name <>

@dbname

declare dbname cursor for select name from master.dbo.sysdatabases where

not status

& 32 = 32

and not status & 512

= 512 and

name =

@dbname

end

open

dbname

fetch next from dbname

into @dbname

while @@fetch_status = 0

begin

----- adding .0 at the end of interger to avoid divide by zero error

exec ( ' use [' + @dbname

+ '] declare @total dec(7,1),

@data dec (7, 1),

@data_used dec (7, 1),

@data_percent dec (7, 1),

@data_free dec (7, 1),

@data_free_percent dec (7, 1),

@log dec (7, 1),

@log_used dec (7, 1),

@log_used_percent dec (7, 1),

@log_free dec (7, 1),

@log_free_percent dec (7, 1)

set @total = (select sum(convert(dec(15),size)) from sysfiles) * 8192.0 /1048576.0

set @data = (select sum(size) from sysfiles where (status & 64 = 0))* 8192.0 / 1048576.0

set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes

where indid in (0, 1, 255)) * 8192.0 / 1048576.0

set

@data_percent = (@data_used * 100.0 / @data)

set @data_free = (@data - @data_used)

set @data_free_percent = (@data_free * 100.0 / @data

)

set @log = (select log from #sizeinfo where db_name = '''+@dbname+''')

set @log_used_percent = (select [log (%)] from #sizeinfo where db_name ='''+@dbname+''')

set @log_used = @log * @log_used_percent / 100.0

set @log_free = @log - @log_used

set @log_free_percent =@log_free * 100.0 / @log

update #sizeinfo set total = @total,

data = @data ,

data_used = @data_used,

[data (%)] = @data_percent,

data_free = @data_free,

[data_free (%)] = @data_free_percent,

log_used = @log_used,

log_free = @log_free,

[log_free (%)] = @log_free_percent

where db_name = '''+@dbname+'''' )

fetch next from dbname

into @dbname

end

dbname

deallocate

dbname

if ((select count(*) from #sizeinfo

) <> 1)

select @@servername as

'ServerName',db_name, total, data, data_used, [data (%)], data_free, [data_free (%)],

log,

log_used, [log (%)], log_free,

[log_free (%)]

from #sizeinfo order by db_name asc

else

select @@servername as

'ServerName',db_name, total, data, data_used, [data (%)], data_free, [data_free (%)],

log,

log_used, [log (%)], log_free,

[log_free (%)]

from #sizeinfo

drop table #sizeinfo

저작자표시 비영리

PREV 이전 1 ···13 14 15 16 17 NEXT 다음

곰탱이의 개인블로그

IT/SQL SERVER

MSSQL과 Oracle 함수 비교 2탄 - 9가지 함수

MSSQL과 Oracle 함수 비교 - 첫번째 10가지 함수

특정 키워드를 포함하는 모든 프로시져 보기

TABLE 별 데이터 건수 및 사이즈 확인

DB Size Check Script

+ Recent posts

티스토리툴바