Oracle和SQL對比語法總結

SQL資料庫                                                                                                                                                                                     Oracle資料庫

ISNULL(欄位名,替換值)  -------------------------------------------------------------          NVL(欄位名,替換值)

SUBSTRING(欄位名,起始位置,擷取長度) --------------------------------------        SUBSTR(欄位名,起始位置,擷取長度)

GETDATE() -----------------------------------------------------------------------------         SELECT SYSDATE FROM DUAL

YEAR(GETDATE())  -----------------------------------------------------------------           SELECT TO_CHAR(SYSDATE,'yyyy')  FROM DUAL

MONTH(GETDATE())  --------------------------------------------------------------           SELECT TO_CHAR(SYSDATE,'MM')  FROM DUAL

DAY(GETDATE())  ------------------------------------------------------------------            SELECT TO_CHAR(SYSDATE,'dd')  FROM DUAL

SELECT DATEPART(HOUR,GETDATE()) ----------------------------------          SELECT TO_CHAR(SYSDATE,'hh')  FROM DUAL

SELECT DATEPART(MINUTE,GETDATE())---------------------------------        SELECT TO_CHAR(SYSDATE,'mi')  FROM DUAL

SELECT DATEPART(SECOND,GETDATE()) ----------------------------          SELECT TO_CHAR(SYSDATE,'ss')  FROM DUAL

+ (連線符號) --------------------------------------------------------------------------          ||

'2013-03-29 11:40:28'-------------------------------------------------------------         TO_DATE('2013-03-29 11:40:28','yyyy/MM/dd hh:mi:ss')

 

UniqueIdentifier ----------------------------------------------------------------------------------------------   SYS_GUID()

 

select SYS_GUID() from dual;

create table TEST2
(
   UuID varchar2(200) default SYS_GUID(),
   UName varchar2(20)
);

insert into TEST2(UName) values('Hello');

insert into TEST2(UName) values('World');

select * from TEST2;