Sybase , Oracle 함수 비교

ㅇ Tunning Guide
   - DBO만 소유자

   - 커서와 View 지양
   - View내 '*' 존재시 원 Table 변경시 View Recompile 실시

   - SP 지향
   - SP 작성시 소유자 명시 : CREATE PROC DBO.SP명
   - SP 작성시 DB명 명시 : USE DB명
   - SP 작명시 SP외의 접두어 사용
   - SP는 'SET NOCOUNT ON'으로 시작

   - [Sybase] CREATE Tbl보다 SELECT INTO 구문 지향

   - SELECT는 필요한 값만 명시
   - COUNT(ColNm)보다 COUNT(*) 사용

   - DELETE시 FROM 붙이기(MySQL) : DELETE FROM table_name WHERE ...

   - UPDATE시 Index Column은 지양
   - JOIN에 의한 UPDATE 지양

   - WHERE절은 INDEX 또는 INDEX Hint 사용
   - 부정문은 긍정문으로 수정
   -  <, >, = 사용시 가능한 '=' 포함
   - Column 변환 금지
   - EXISTS()로 대체가능한 COUNT(*)는 EXISTS() 사용
   - LIKE 지양 : 코드 찾기로 유도
   - MIN과 MAX 함께 사용 지양
   - SUBSTRING  지양 : Table 설계시 분해로 해소

   - TRANSACTION에서 사용하는 Table은 타 Program과 비교하여 일관된 순서 적용
   - TRANSACTION은 가급적 짧게 관리

ㅇ Archive 정리(query tool 사용)
USE PUBS                                                 -- PUBS DB 사용
BACKUP LOG PUBS WITH TRUNCATE_ONLY  -- PUBS DB 로그 백업후 삭제
GO
SP_HELPDB PUBS                                      -- PUBS DB 정보 확인(Size 조정전)
DBCC SHRINKFILE(PUBS_LOG,#)                 -- 줄일 File Size #(단위: MB) 입력
SP_HELPDB PUBS                                      -- PUBS DB 정보 확인(Size 조정후)
※ 관련파일 : MSSQL > Data > xxx_LOG.xxx

ㅇ Sybase Attribute Value
max memory : Max Memory, 1M = 512page
number of locks : 동시 Locking 개수
precedure cache size : SP Cache Size, 1M = 512page
shared memory starting address : DB Start시 할당 된 메모리 할당여부 결정, 1 = 할당영역 확보
statement cache size : Statement cache 사용할 영역
max online engines : OS CPU 개수에 의존적인 DB사용 최대 엔진수
number of engines at startup : DB Start시 Start할 엔진 수

ㅇ 기타
※ Oracle : Ora, Sybase : Syb
ABS()                                                                  : 절대값
AS                                                                       : [MS, Syb]Alias
AS                                                                       : [Ora]Column Alias 만 가능
AVG()                                                                  : 평균값
ALTER TABLE TblNm ADD ColNm ColType(#)            : TblNm에 ColNm Add
BACKUP DATABASE DB명 TO DISK = '디스크:\경로\백업파일명'  : DB Backup
BEGIN TRAN                                                         : Transaction 시작
BEGIN TRANSACTION...COMMIT TRANSACTION       : Transaction 처리

CASE .. WHEN .. THEN .. ELSE .. END                     : [Syb,MS] 조건 분기
CEIL()                                                                  : [Ora] 올림
CEILING()                                                             : [Syb] 올림
FLOOR()                                                               : [Syb] 버림
CHR()                                                                  : [Ora] Data를 Character로 변환
CHAR()                                                                : [Syb] Data를 Character로 변환
COMMIT                                                              : Transation 처리 및 저장
CONVERT(INT,VarNm);                                          : [Syb] VarNm을 Int화
CONVERT(VARCHAR(10),GETDATE(),101)               : [Syb] mm/dd/yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),102)               : [Syb] yyyy.mm.dd 로 변경
CONVERT(VARCHAR(10),GETDATE(),103)               : [Syb] dd/mm/yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),104)               : [Syb] dd.mm.yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),105)               : [Syb] dd-mm-yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),106)               : [Syb] dd mm yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),107)               : [Syb] mm dd, yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),108)               : [Syb] hh:mi:ss 로 변경
CONVERT(VARCHAR(10),GETDATE(),109)       : [Syb] mm dd yyyy h:mi:ss:??PM/AM로 변경
CONVERT(VARCHAR(10),GETDATE(),110)               : [Syb] mm-dd-yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),111)               : [Syb] yyyy/mm/dd 로 변경
CONVERT(VARCHAR(10),GETDATE(),112)               : [Syb] yyyymmdd 로 변경
CONVERT(VARCHAR(10),GETDATE(),113)               : [Syb] yy mm yyyy hh:mm:ss:???로 변경
CONVERT(VARCHAR(10),GETDATE(),114)               : [Syb] hh:mm:ss:??? 로 변경
CREATE TABLE TblNm ( Col1 ColType(#) NOT NULL, ..) : TblNm Create
CREATE TABLE TblNm ( Col1 ColType(#) default 값, ..)  : [Syb] TblNm Create, Defualt 값 부여
DATALENGTH()                                                      : [Syb] 문자열 길이
DATEADD(DAY, ±일, GETDATE())                            : [Syb] 금일 ±일 더하기
DATEADD(MONTH, ±월, GETDATE())                       : [Syb] 금월 ±월 더하기
DATEADD(YEAR, ±년, GETDATE())                          : [Syb] 금년 ±년 더하기
DATEDIFF()                                                           : [Syb] Date간 차이
DECLARE @@VarNm char(10)                                 : [Syb] Grobal 변수 선언
DECLARE @VarNm char(10)                                   : [Syb] Local 변수 선언
DECODE()                                                             : [Ora] Case문 대체
DUMP DATABASE DB_Nm FROM "/PathNM/FileNm.dmp"  : [Syb] DB Backup
exec sp_rename 'TblNm.ColNm','NewColNm','column' : [Syb] ColNm수정
EXEC sp_helpdb DBMS_Nm                                     : [Syb] DBMS size, owner 정보
EXEC sp_primarykey TblNm, Col1, Col2                     : TblNm에 Col1, Col2를 PK로 지정
exec sp명 parameter값                                            : [Syb] Sp 구동
EXISTS (SELECT Col FROM Tbl Where Tbl.Col = Tbl2.Col : [Syb] 복수개의 SELECT 값 비교
FROM Tbl1 = Tbl2(+)                                             : [Ora] Left Outer Join
FROM Tbl1 *= Tbl2                                               : [Syb] Left Outer Join
GETDATE()                                                            : [Syb,MS] 현 시간
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_MNG_ATTD_REC_I]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                                                                            : Object 존재유무 확인
INSERT INTO TblNm VALUES('V1','V2')                     : V1, V2를 TblNm에 추가
INSERT INTO TblNM SELECT ...                               : [Syb] 조회결과 INSERT
ISNULL()                                                               : [Syb] Null을 타 문자로 치환
isql -U사용자ID -P패스워드 -i입력파일 -o출력파일      : [Syb]쿼리 결과 파일로 만들기
LEN()                                                                    : [Syb] 문자열 길이
LENGTH()                                                              : [Ora] 문자열 길이
LOAD DATABASE DB_Nm FROM "/PathNm/FileNm.dum"  : [Syb]에서 DB Restore
LOWER()                                                               : 소문자로 변환
LOWNUM                                                               : [Ora] 행의 갯수 제한시 사용
LTRIM()                                                                : [Syb] Left 공란 제거
MAX()                                                                   : 최대값
MIN()                                                                    : 최소값
NVL()                                                                   : [Ora]에서 Null을 타 문자로 치환
print "%1!", @변수명                                                : [Syb] SP의 변수 출력하기
RAND()                                                                 : [Syb]에서 난수 구하기
REPLACE()                                                           : [Ora]에서 문자치환
ROLLBACK                                                            : Transaction 처리 취소
RTRIM()                                                                : [Syb] Right 공란 제거
SELECT * FROM #TmpTblNm                                   : [Syb] 임시 테이블 사용
SELECT * FROM TblNm < FlNm.sql > FlNm2.out         : [Syb] FlNm.sql 결과 → FlNn2.out
SELECT db_name()                                                : [Syb]현재 Login DB명 Display
SELECT CONVERT(CHAR(10), GETDATE(), 126)        : Date Type  → Char(10) Type
SELECT CONVERT(CHAR(10), GETDATE(), 112)        : Date Type  → Char(10) Type
SELECT * INTO NewTblNm FROM OldTblNm               : [Syb] CREATE Tbl, INSERT Tbl
SELECT ROWNUM = IDENTITY(9)                              : [Syb] Oracle Rownum 따라하기
 , ColNm
INTO #T
FROM TblNm
SET IDENTITY_INSERT TblNm ON                              : Identity Column 수정 가능
SET ROWCOUNT #                                                  : [Syb] #건만큼 반환
sp_cacheconfig "default data cache"                        : [Syb] 할당된 Default Data Cache 조회
sp_configure "number of user connections",100          : [Syb] 동시 접속자 100명
sp_configure "lock scheme",0,"datarows                   : [Syb] Row Level Locking
sp_help                                                                 : [Syb] 현재 Login Table 조회
sp_help Tbl_Nm                                                      : [Syb] Tbl_Nm 정보 조회
sp_helpdb DB_Nm                                                   : [Syb] DB_Nm 정보조회
sp_lock                                                                 : [Syb]Locking정보 Display
sp_who                                                                 : [Syb] 접속된 Process
STR()                                                                    : [Syb] Numeric을 Char로 변환
STUFF()                                                                : [Syb] 문자치환
SUBSTR()                                                              : [Ora] Substring
SUBSTRING()                                                         : [Syb] Substring
SUM()                                                                   : 합계
SYSDATE                                                              : [Ora] 현 시간
TO_CHAR()                                                            : [Ora] Data을 Char로 변환
TO_DATE()                                                             : [Ora] Data을 Date로 변환
TO_NUMBER()                                                        : [Ora] Data을 Numeric로 변환
TOP 1                                                                    : [Syb] 첫 Row Return
TRUNCATE TABLE TblNm                                         : TblNm의 모든 데이터 삭제
UNION                                                                    : SELECT 결과 APPEND - 교집합
UNION ALL                                                             : SELECT 결과 APPEND
UPDATE TblNm SET Col1='xxx', Col2='yyy'                 : TblNm의 Col1, Col2 Update
UPDATE STATISTICS TblNM                                     : TblNm 통계 갱신
UPDATE Tbl1                                                          : [Syb] Join Update
Set Col1 = Col
2FROM Tbl1 INNER JOIN Tbl
2ON Tbl1.Key1 = Tbl2.Key2
UPPER()                                                                : 대문자로 변환
USE DB_Nm                                                           : DB_nm으로 이동
CHR()                                                                  : [Ora] Data를 Character로 변환
CHAR()                                                                : [Syb] Data를 Character로 변환
COMMIT                                                              : Transation 처리 및 저장
CONVERT(INT,VarNm);                                          : [Syb] VarNm을 Int화
CONVERT(VARCHAR(10),GETDATE(),101)               : [Syb] mm/dd/yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),102)               : [Syb] yyyy.mm.dd 로 변경
CONVERT(VARCHAR(10),GETDATE(),103)               : [Syb] dd/mm/yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),104)               : [Syb] dd.mm.yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),105)               : [Syb] dd-mm-yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),106)               : [Syb] dd mm yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),107)               : [Syb] mm dd, yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),108)               : [Syb] hh:mi:ss 로 변경
CONVERT(VARCHAR(10),GETDATE(),109)       : [Syb] mm dd yyyy h:mi:ss:??PM/AM로 변경
CONVERT(VARCHAR(10),GETDATE(),110)               : [Syb] mm-dd-yyyy 로 변경
CONVERT(VARCHAR(10),GETDATE(),111)               : [Syb] yyyy/mm/dd 로 변경
CONVERT(VARCHAR(10),GETDATE(),112)               : [Syb] yyyymmdd 로 변경
CONVERT(VARCHAR(10),GETDATE(),113)               : [Syb] yy mm yyyy hh:mm:ss:???로 변경
CONVERT(VARCHAR(10),GETDATE(),114)               : [Syb] hh:mm:ss:??? 로 변경
CREATE TABLE TblNm ( Col1 ColType(#) NOT NULL, ..) : TblNm Create
CREATE TABLE TblNm ( Col1 ColType(#) default 값, ..)  : [Syb] TblNm Create, Defualt 값 부여
DATALENGTH()                                                      : [Syb] 문자열 길이
DATEADD(DAY, ±일, GETDATE())                            : [Syb] 금일 ±일 더하기
DATEADD(MONTH, ±월, GETDATE())                       : [Syb] 금월 ±월 더하기
DATEADD(YEAR, ±년, GETDATE())                          : [Syb] 금년 ±년 더하기
DATEDIFF()                                                           : [Syb] Date간 차이
DECLARE @@VarNm char(10)                                 : [Syb] Grobal 변수 선언
DECLARE @VarNm char(10)                                   : [Syb] Local 변수 선언
DECODE()                                                             : [Ora] Case문 대체
DUMP DATABASE DB_Nm FROM "/PathNM/FileNm.dmp"  : [Syb] DB Backup
exec sp_rename 'TblNm.ColNm','NewColNm','column' : [Syb] ColNm수정
EXEC sp_helpdb DBMS_Nm                                     : [Syb] DBMS size, owner 정보
EXEC sp_primarykey TblNm, Col1, Col2                     : TblNm에 Col1, Col2를 PK로 지정
exec sp명 parameter값                                            : [Syb] Sp 구동
EXISTS (SELECT Col FROM Tbl Where Tbl.Col = Tbl2.Col : [Syb] 복수개의 SELECT 값 비교
FROM Tbl1 = Tbl2(+)                                             : [Ora] Left Outer Join
FROM Tbl1 *= Tbl2                                               : [Syb] Left Outer Join
GETDATE()                                                            : [Syb,MS] 현 시간
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[sp_MNG_ATTD_REC_I]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
                                                                            : Object 존재유무 확인
INSERT INTO TblNm VALUES('V1','V2')                     : V1, V2를 TblNm에 추가
INSERT INTO TblNM SELECT ...                               : [Syb] 조회결과 INSERT
ISNULL()                                                               : [Syb] Null을 타 문자로 치환
isql -U사용자ID -P패스워드 -i입력파일 -o출력파일      : [Syb]쿼리 결과 파일로 만들기
LEN()                                                                    : [Syb] 문자열 길이
LENGTH()                                                              : [Ora] 문자열 길이
LOAD DATABASE DB_Nm FROM "/PathNm/FileNm.dum"  : [Syb]에서 DB Restore
LOWER()                                                               : 소문자로 변환
LOWNUM                                                               : [Ora] 행의 갯수 제한시 사용
LTRIM()                                                                : [Syb] Left 공란 제거
MAX()                                                                   : 최대값
MIN()                                                                    : 최소값
NVL()                                                                   : [Ora]에서 Null을 타 문자로 치환
print "%1!", @변수명                                                : [Syb] SP의 변수 출력하기
RAND()                                                                 : [Syb]에서 난수 구하기
REPLACE()                                                           : [Ora]에서 문자치환
ROLLBACK                                                            : Transaction 처리 취소
RTRIM()                                                                : [Syb] Right 공란 제거
SELECT * FROM #TmpTblNm                                   : [Syb] 임시 테이블 사용
SELECT * FROM TblNm < FlNm.sql > FlNm2.out         : [Syb] FlNm.sql 결과 → FlNn2.out
SELECT db_name()                                                : [Syb]현재 Login DB명 Display
SELECT CONVERT(CHAR(10), GETDATE(), 126)        : Date Type  → Char(10) Type
SELECT CONVERT(CHAR(10), GETDATE(), 112)        : Date Type  → Char(10) Type
SELECT * INTO NewTblNm FROM OldTblNm               : [Syb] CREATE Tbl, INSERT Tbl
SELECT ROWNUM = IDENTITY(9)                              : [Syb] Oracle Rownum 따라하기
 , ColNm
INTO #T
FROM TblNm
SET IDENTITY_INSERT TblNm ON                              : Identity Column 수정 가능
SET ROWCOUNT #                                                  : [Syb] #건만큼 반환
sp_cacheconfig "default data cache"                        : [Syb] 할당된 Default Data Cache 조회
sp_configure "number of user connections",100          : [Syb] 동시 접속자 100명
sp_configure "lock scheme",0,"datarows                   : [Syb] Row Level Locking
sp_help                                                                 : [Syb] 현재 Login Table 조회
sp_help Tbl_Nm                                                      : [Syb] Tbl_Nm 정보 조회
sp_helpdb DB_Nm                                                   : [Syb] DB_Nm 정보조회
sp_lock                                                                 : [Syb]Locking정보 Display
sp_who                                                                 : [Syb] 접속된 Process
STR()                                                                    : [Syb] Numeric을 Char로 변환
STUFF()                                                                : [Syb] 문자치환
SUBSTR()                                                              : [Ora] Substring
SUBSTRING()                                                         : [Syb] Substring
SUM()                                                                   : 합계
SYSDATE                                                              : [Ora] 현 시간
TO_CHAR()                                                            : [Ora] Data을 Char로 변환
TO_DATE()                                                             : [Ora] Data을 Date로 변환
TO_NUMBER()                                                        : [Ora] Data을 Numeric로 변환
TOP 1                                                                    : [Syb] 첫 Row Return
TRUNCATE TABLE TblNm                                         : TblNm의 모든 데이터 삭제
UNION                                                                    : SELECT 결과 APPEND - 교집합
UNION ALL                                                             : SELECT 결과 APPEND
UPDATE TblNm SET Col1='xxx', Col2='yyy'                 : TblNm의 Col1, Col2 Update
UPDATE STATISTICS TblNM                                     : TblNm 통계 갱신
UPDATE Tbl1                                                          : [Syb] Join Update
Set Col1 = Col
2FROM Tbl1 INNER JOIN Tbl
2ON Tbl1.Key1 = Tbl2.Key2
UPPER()                                                                : 대문자로 변환
USE DB_Nm                                                           : DB_nm으로 이동


출처: http://pmguda.com/178