Oracle SqlServer 比較

ORACLE * SQLSERVER 描述

1 ROWNUM ①; selectidentity(int,1,1)rownum,列名into#tempfrom; 表

select*from#tempwhererownum>10andrownum<=20

犧牲性能

② select ROW_NUMBER() OVER (ORDER BY 列名 desc) AS rowNum,* from 表名

ORDER BY 列名 desc

可以列出所有記錄，RowNUM不能用于條件

③ Select top N from 表

2 存儲過程里面定義函數; 不支持

3 TYPE work_table is TABLE of VARCHAR(6) INDEX BY BINARY_INTEGERDECLARE @ltb_mailplan;;Table(ColName VARCHAR(6) null)

4 lv2_startdateVARCHAR(20); DECLARE @lv2_startdateVARCHAR(20)

5 MOD; %

6 trunc(sysdate - to_date('2010/1/01','yyyy/mm/dd')); SELECT datediff( day,; '2010/02/01'，getdate() )

7 TO_CHAR(sysdate,'DY');;set language N'Korean'

select datename(weekday, getdate())

星期幾

8 select INSTR('sdsq','s',2) value from dualselect CHARINDEX('s','sdsq',2) value

9 TO_CHAR(sysdate,'DD'); DatePart(Getdate,'DD'）

10 select trunc(sysdate) value from dual

select to_char(sysdate,'yyyy-mm-dd') value from dual

select convert(char(10),getdate(),111) value

11 TO_CHAR(sysdate,'YYYY/MM/DD'); SELECTLTRIM(RTRIM(CONVERT(varchar(100), GETDATE(), 111))) 2010-1-21

12 TO_CHAR(sysdate,'HH24:MI:SS'); SELECTLTRIM(RTRIM(CONVERT(varchar(100), GETDATE(), 8))) 10:58:55

13 TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI:SS')CONVERT(varchar(100), GETDATE(), 20)

14 PRAGMA AUTONOMOUS_TRANSACTION;BEGIN TRANSACTION

15 ||; +

16 RAISE_APPLICATION_ERROR('-20050', lv2_msg)①

EXECsp_addmessage@msgnum=50006,@severity=14,

@msgtext='%sloginfailed!',@lang='us_english'

EXECsp_addmessage@msgnum=50006,@severity=14,

@msgtext='%1!登陸失敗!'

RAISERROR(50006, 14, 1,'Xing')

錯誤信息處理

17; ② RAISERROR('Xingaixin',16,1,'') 13000-50000 >50001個人定制信息

18 CURSOR lcur_mailplan (daid AvGomacSettings.DataAreaId%TYPE) IS 23:23

SELECT DISTINCT avMailplan FROM; avtimeperiod

WHERE DataAreaId = daid AND TRUNC(avTpdate) = TRUNC(sysdate);

DECLARE lcur_mailplan CURSOR FOR

SELECT DISTINCT avMailplan FROM; avtimeperiod

WHERE DataAreaId = @lv2_dataareaid AND CONVERT(varchar(10),avTpdate,120) =CONVERT(varchar(10),GetDate(),120);

13000-50000 >50001個人定制信息

19;FOR tp_rec IN lcur_mailplan (@lv2_dataareaid)

LOOP

@ltb_mailplan(@lbi_index) = tp_rec.AvMailplan;

@lbi_index = @lbi_index + 1;

END LOOP;

OPEN lcur_mailplan

FETCH; lcur_mailplan INTO @ltb_mailplan_Value

WHILE(@@FETCH_STATUS=0)

BEGIN

BEGIN

INSERT INTO @ltb_mailplan(ColName)

VALUES(@ltb_mailplan_Value)

END

FETCH; lcur_mailplan INTO @ltb_mailplan_Value

END

close lcur_mailplan

deallocate lcur_mailplan

20 LOOP ..;;;;END; LOOP; While 條件 BEGIN .. END

21 CHR(2); CHAR(2)

22 ELSIF; ELSE IF

23 SUBSTR; SUBSTRING

24 NLS_LOWER; selectlower('dAcB')

25 NLS_UPPER; selectupper('dAa')

26 IF 條件 Then ELSIF 條件 THEN ＥＮＤ IF; IF 條件 Begin END Else IF Begin ...END

27 TRUNC(SYSDATE); SELECT CONVERT(varchar(100), GETDATE(), 101); 2006-5-16

28 ISNULL(COLNAME,''); nvl(COLNAME,'')

29 TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS'));select datediff(ss,Convert(datetime,CONVERT(varchar(100), GETDATE(), 101)),getdate()); 從0點以來漸失秒數。

30; EXECUTE IMMEDIATE @lv2_updatesql USING @lv2_status, @an_code, @lv2_user, @ld_date, @ln_time,

@lv2_dataareaid, @lv2_salesid;