MySql -> Oracle º¯È¯ MySQL
2008/09/19 14:32
http://blog.naver.com/jangfung/90035233898
ÀÌ Æ÷½ºÆ®¸¦ º¸³½°÷ ()
1. À妽º ÈùÆ® Á¦°ÅÇÑ´Ù.
2. NVL ÇÔ¼ö¸¦ IFNULL ÇÔ¼ö·Î º¯°æÇÑ´Ù.
3. SYSDATE ÇÔ¼ö¸¦ NOW() ÇÔ¼ö·Î º¯°æÇÑ´Ù.
4. TRUNC(SYSDATE)¸¦ CURDATE()·Î º¯°æÇÑ´Ù.
trunc(sysdate)¸¦ ÇÏ¸é ½Ã°£¸¸ À߸° ÀÏÀÚ°¡ ³ª¿Â´Ù.
MySQL¿¡¼ ³¯ÀÚ¸¸ ¹ÝȯÇØÁÖ´Â CURDATE()¸¦ ¾´´Ù.
CURDATE()¿Í ³¯Â¥ °ªÀ» ºñ±³Çϱâ À§Çؼ´Â ºñ±³³¯ÀÚ¸¦
DATE_FORMAT('20061020','%Y-%m-%d')·Î º¯È¯
5. Outerjoin º¯°æ
[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;
[MySQL]
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;
% FromÀý¿¡¼ Å×À̺í¸í Áß°£¿¡ , °¡ ¾ø´Ù´Â°Í°ú ON ±¸¹®ÀÌ FromÀý ¹Ù·Î µÚ¿¡ ¿À°Ô
ÇؾßÇϴ°Ϳ¡ ÁÖÀÇ
6. TO_CHAR()¸¦ DATE_FORMAT()À¸·Î º¯È¯ÇÑ´Ù.
TO_CHAR(sysdate,'YYYYMMDD')ÀÇ °æ¿ì
DATE_FORMAT(now(),'%Y%m%d')·Î º¯È¯
ÀÚ¹Ù½ºÅ©¸³Æ®ÀÇ ¿äÀÏÀº(0-6), MySQLÀÇ ¿äÀÏÀº(0-6), ORACLEÀÇ ¿äÀÏÀº(1,7)
±×·¯¹Ç·Î ÀÚ¹Ù½ºÅ©¸³Æ®¿Í ¿À¶óŬÀÇ ¿äÀÏÂ÷ÀÌ·Î TO_CHAR(SYSDATE-1, 'D')¸¦
±×´ë·Î º¯°æ ½ÃÅ°¸é ¾Æ·¡±¸¹®ÀÌÁö¸¸
=> DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%w')
MySQL¿¡¼´Â -1À» ÇÒ ÇÊ¿ä°¡ ¾øÀ¸¹Ç·Î
=> DATE_FORMAT(NOW(), '%w') ")
7. TO_DATE()¸¦ DATE_FORMAT()À¸·Î º¯È¯ÇÑ´Ù.
TO_DATE('20061020','YYYYMMDD')ÀÇ °æ¿ì
DATE_FORMAT('20061020','%Y%m%d')·Î º¯È¯
8. Where rownum <= 10 À» limit 10À¸·Î º¯È¯ (rownumÀº mysql¿¡ ¾ø¾î limit¸¦ »ç¿ë)
rownum >= 5 ¿Í rownum <= 10 À̸é limit 4, 5
ex) ¸®½ºÆ® ÆäÀÌ¡¿¡¼ 11¹ø°ºÎÅÍ 20¹ø°±îÁö limit 10, 10
óÀ½ºÎÅÍ 10°³ ........ LIMIT 10
5¹ø°ºÎÅÍ 10°³ ........LIMIT 4, 10
5¹ø°ºÎÅÍ ¸¶Áö¸·±îÁö .... LIMIT 4, -1
À̶§ ÁÖÀÇ ÇÒ Á¡Àº ù¹ø° ·¹ÄÚµåÀÇ ½ÃÀÛÀº ¡°0¡± ÀÔ´Ï´Ù.
Áï ¡°LIMIT 1, 3 - 2¹ø°ºÎÅÍ 3°³¡± ÀÇ °á°ú¸¦ Ãâ·ÂÇÏ°Ô µË´Ï´Ù.
9. Subquery¿¡¼ alias°¡ ¾ø´Ù¸é ºÙÀδÙ.
¿¹¸¦ µé¸é
select a, b from ( select a, b from ttt)
--> select t.a, t.b from ( select a, b from ttt) t
10. DELETE Å×À̺í¸í Äõ¸®¹®À» DELETE FROM Å×À̺í¸í Äõ¸®¹®À¸·Î ¹Ù²Ù¾îÁØ´Ù.
¿À¶óŬ¿¡¼´Â FROMÀ» »ý·«Çصµ µÇÁö¸¸ MySQL¿¡¼´Â ²À ºÙ¿©ÁØ´Ù.
11. Merge intoÀÇ °æ¿ì´Â Äõ¸®¹®À» ºÐ¸®ÇÏ¿© DAO´Ü¿¡¼ 󸮷ÎÁ÷À¸·Î º¯°æÇØÁØ´Ù.
º¯°æ¹æ¹ýÀº ¾Æ·¡ µÎ°¡ÁöÁß¿¡ Çϳª¸¦ ¼±ÅÃÇصµ µÇ°í ´Ù¸¥ ¹æ¹ýÀ» ½áµµ µÈ´Ù.
ÀÚ¼¼ÇÑ ¿¹Á¦´Â ÷ºÎ¹®¼ Âü°í
(1) Á¶°ÇÀÌ ¸Â´ÂÁö(MATCHED)¿¡ ´ëÇÑ SELECT¹®À» ½ÇÇà½ÃÄѼ °á°ú°ªÀÌ
ÀÖÀ¸¸é UPDATE¹®À» ½ÇÇà½ÃÄÑ ÁÖ°í °á°ú°ªÀÌ ¾øÀ¸¸é INSERT¹®À»
½ÇÇà½ÃÄÑÁØ´Ù.
% rs.next()·Î °á°ú°ªÀÌ ÀÖ´ÂÁö ÆÇ´Ü
(2) UPDATE¹®À» ½ÇÇàÇؼ UPDATE°¡ ÀϾ¸é ±×´ë·Î °¡°í UPDATE°¡
ÀϾÁö ¾ÊÀ¸¸é INSERT¹®À» ½ÇÇà½ÃŲ´Ù.
% ps.executeUpdate() > 0À¸·Î UPDATE ÆÇ´Ü
o ¿À¶óŬ MergeInto ±¸¹®
MERGE INTO SERVICE_LOG FSL1
USING
(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') AS DAYS, 'LS003' AS SERVICE_TYPE FROM DUAL ) FSL2
ON (FSL1.DAYS = FSL2.DAYS AND FSL1.SERVICE_TYPE = FSL2.SERVICE_TYPE)
WHEN MATCHED THEN
UPDATE SET FSL1.AGE_10_CNT = FSL1.AGE_10_CNT + 1
WHEN NOT MATCHED THEN
INSERT (FSL1.DAYS, FSL1.SERVICE_TYPE, FSL1.AGE_10_CNT)
VALUES (FSL2.DAYS, FSL2.SERVICE_TYPE, 1)
--------------------------------------------------------------------------------------
o MySQL ±¸¹®À¸·Î º¯È¯Ã³¸® 1
- DAO󸮿¡¼ Á¶°ÇÀÌ ¸Â´ÂÁö(MATCHED) ¾È¸Â´ÂÁö(NOT MATCHED) ºñ±³ÇÏ´Â SELECT Äõ¸®¹®À» ½ÇÇàÇÏ°í
±× °á°ú¿¡ µû¶ó¼ ¾÷µ¥ÀÌÆ®¿Í ÀμƮ 󸮸¦ ÇØÁØ´Ù. SELECT °á°ú°ªÀÌ ÀÖ´Ù¸é ¾÷µ¥ÀÌÆ®,
SELECT °á°ú°ªÀÌ ¾ø´Ù¸é ÀμƮ(rs.next()·Î °á°ú°ªÀÌ ÀÖ´ÂÁö ÆÇ´Ü)
if(getDAO().existServiceLog(conn, serviceType)) {
return getDAO().updateServiceLog(conn, memberInfo, serviceType);
}else{
return getDAO().insertServiceLog(conn, memberInfo, serviceType);
}
1) public boolean existServiceLog(Connection conn, String serviceType)
SELECT SERVICE_TYPE
FROM SERVICE_LOG USE INDEX(PRIMARY)
WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d')
2) public boolean updateServiceLog(Connection conn, Member memberInfo, String serviceType)
UPDATE SERVICE_LOG
SET AGE_10_CNT = AGE_10_CNT+1
WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d')
3) public boolean insertServiceLog(Connection conn, Member memberInfo, String serviceType)
INSERT SERVICE_LOG(DAYS, SERVICE_TYPE, AGE_10_CNT, AGE_20_CNT, AGE_30_CNT, AGE_40_CNT, AGE_50_CNT)
VALUES(DATE_FORMAT(NOW(), '%Y%m%d'), 'LS003', 1, 0, 0, 0, 0)
---------------------------------------------------------------------------------------------------------
o MySQL ±¸¹®À¸·Î º¯È¯Ã³¸® 2
- DAO󸮿¡¼ ¾÷µ¥ÀÌÆ®°¡ µÇ¾ú´ÂÁö¸¦ ºñ±³ÇÏ´Â ±¸¹®À» ½ÇÇàÇؼ ¾÷µ¥ÀÌÆ®°¡ ÀϾ¸é ±×´ë·Î ÁøÇàµÇ°í
¾÷µ¥ÀÌÆ® ÀϾÁö ¾ÊÀ¸¸é ÀμƮ¸¦ ½ÇÇàÇØ ÁØ´Ù(ps.executeUpdate() > 0À¸·Î UPDATE ÆÇ´Ü)
if(!getDAO.updateServiceLog(conn, memberInfo, serviceType)){
getDAO.insertServiceLog(conn, memberInfo, serviceType);
}
1) public boolean updateServiceLog(Connection conn, Member memberInfo, String serviceType)
UPDATE SERVICE_LOG
SET AGE_10_CNT = AGE_10_CNT+1
WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d')
À» ½ÇÇàÇؼ ps.executeUpdate() > 0;À» °á°ú·Î ¸®ÅÏÇÑ´Ù. ¾÷µ¥ÀÌÆ®°¡
ÀϾÁö ¾ÊÀ¸¸é false¸¦ ¹ÝȯÇÏ°í insert°¡ ÀϾÙ.
2) public boolean insertServiceLog(Connection conn, Member memberInfo, String serviceType)
INSERT SERVICE_LOG(DAYS, SERVICE_TYPE, AGE_10_CNT, AGE_20_CNT, AGE_30_CNT, AGE_40_CNT, AGE_50_CNT)
VALUES(DATE_FORMAT(NOW(), '%Y%m%d'), 'LS003', 1, 0, 0, 0, 0)
+ ÀÚ·áÇü º¯È¯
#####################################
# MySQL À» Oracle ·Î ÀÌÀüÇϱâ #
#####################################
ÀÛ¼ºÀÚ : ÀåÇüÈ(hhjang97@venus.uos.ac.kr)
ÀÛ¼ºÀÏ : 2005. 03. 08
¼öÁ¤ÀÏ :
¿øº» :
¼³¸í :
MySQLÀÇ ³»¿ëÀ» Oracle·Î ¿Å±â¸é¼ »ç¿ëÇÑ ³»¿ëÀ» Á¤¸®ÇÔ
################################# #################################
#################################
1. µ¥ÀÌÅÍ Å¸ÀÔÀ» ¹Ù²Û´Ù.
MySQL ¸Þ´º¾ó : 7.2 Column types Âü°í
int(11) => number(11)
varchar(30) => varchar2(30)
char(30) => char(30)
TINYINT => number(3) : 255
SMALLINT => number(5) : 65535
MEDIUMINT => number(8) : 16777215
INT => number(10): 4294967295
BIGINT => number(20): 18446744073709551615
FLOAT => : 1.175494351E-38 to 3.402823466E+38
DOUBLE => : 2.2250738585072014E-308 to 1.7976931348623157E+308
DECIMAL =>
DATE => : 'YYYY-MM-DD'
DATETIME => : '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP => number(11)
TIME => : 'HH:MM:SS'
YEAR => : 1901 to 2155
CHAR => CHAR
VARCHAR => VARCHAR2
TINYBLOB
TINYTEXT => CHAR(255)
TEXT => VARCHAR2(65535) : (2^16 - 1)
BLOB
MEDIUMBLOB
MEDIUMTEXT => VARCHAR2(16777215) : (2^24 - 1)
LONGBLOB
LONGTEXT => VARCHAR2(4294967295) : (2^32 - 1)
ENUM
SET
2. auto_increment ÀÎ °æ¿ì sequence °ªÀ» ¸¸µç´Ù.
- "Å×À̺íÀ̸§_seq" Çü½ÄÀ¸·Î ¸¸µç´Ù.
ÇÑ Å×ÀÌºí¿¡ ¿©·¯°³°¡ ÀÖÀ¸¸é column_seq ·Î Ãß°¡ÇÑ´Ù.
- user Å×À̺íÀÇ no ÀÎ °æ¿ì´Â user_seq °¡ µÈ´Ù.
CREATE sequence tablename_seq
INCREMENT BY 1 -- Áõ°¡Ä¡
START WITH 1 -- ½ÃÀÛ
MAXVALUE 9999999 -- Á¾Á¡
NOCACHE --
NOCYCLE;
¿¹)
create sequence SEQ_TEST increment by 1 start with 1
maxvalue 99999999 nocache nocycle;
insert into table_name (auto_inc_field) values(SEQ_TEST.NEXTVAL);
3. key °ª
"PRIMARY KEY (no)" ´Â ±×´ë·Î ¼³Á¤Çصµ µÈ´Ù.
4. NOT NULL °ú DEFAULT ÀÇ À§Ä¡¸¦ ¹Ù²Û´Ù.
¼·Î ¼ø¼°¡ ´Ù¸¥°Å °°´Ù.
- MySQL
NOT NULL default '',
- Oracle
DEFAULT '' NOT NULL ,
5. \\\' ³ª \\\" ·Î µÇ¾î Àִ°ÍÀ» '' ·Î ¹Ù²ã¾ß ÇÑ´Ù.
[Ãâó] MySql -> Oracle º¯È¯|ÀÛ¼ºÀÚ ÀådzÀÌ
|
|
|