時(shí)間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(0)
Oracle SqlServer 比較
ORACLE * SQLSERVER 描述
1 ROWNUM ①? select?? identity(int,1,1)?? rownum,列名?? into?? #temp?? from? 表
select?? *?? from?? #temp?? where?? rownum>10?? and?? rownum<=20
犧牲性能
?、?select ROW_NUMBER() OVER (ORDER BY 列名 desc) AS rowNum,* from 表名
ORDER BY 列名 desc
可以列出所有記錄，RowNUM不能用于條件
?、?Select top N from 表
2 存儲(chǔ)過程里面定義函數(shù)? 不支持
3 TYPE work_table is TABLE of VARCHAR(6) INDEX BY BINARY_INTEGER;? DECLARE @ltb_mailplan???? Table(ColName VARCHAR(6) null)
4 lv2_startdate?? VARCHAR(20)? DECLARE @lv2_startdate?? VARCHAR(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 dual?? select 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')? SELECT?? LTRIM(RTRIM(CONVERT(varchar(100), GETDATE(), 111))) 2010-1-21
12 TO_CHAR(sysdate,'HH24:MI:SS')? SELECT?? LTRIM(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);? ①
EXEC?? sp_addmessage?? @msgnum?? =?? 50006,?? @severity?? =?? 14,
@msgtext?? =?? '%s?? login?? failed!',@lang?? =?? 'us_english'
EXEC?? sp_addmessage?? @msgnum?? =?? 50006,?? @severity?? =?? 14,
@msgtext?? =?? '%1!登陸失敗!'
RAISERROR(50006, 14, 1,'Xing')
#p#副標(biāo)題#e#
錯(cuò)誤信息處理
17? ② RAISERROR('Xingaixin',16,1,'') 13000-50000 >50001個(gè)人定制信息
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個(gè)人定制信息
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? select?? lower('dAcB')
25 NLS_UPPER? select?? upper('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點(diǎn)以來漸失秒數(shù)。
30? EXECUTE IMMEDIATE @lv2_updatesql USING @lv2_status, @an_code, @lv2_user, @ld_date, @ln_time,
@lv2_dataareaid, @lv2_salesid;
關(guān)鍵詞標(biāo)簽：Oracle,Sqlserver
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實(shí)例的控制文件
為UNIX服務(wù)器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法