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 存储过程里面定义函数 不支持
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#副标题#e#
错误信息处理
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 select lower('dAcB')
25 NLS_UPPER select upper('dAa')
26 IF 条件 Then ELSIF 条件 THEN END 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;
关键词标签:Oracle,Sqlserver
相关阅读
热门文章 Oracle中使用alter table来增加,删除,修改列oracle中使用SQL语句修改字段类型-oracle修使用低权限Oracle数据库账户得到管理员权限Oracle对user的访问控制
人气排行 ORACLE SQL 判断字符串是否为数字的语句Oracle中使用alter table来增加,删除,修改列的语法ORACLE和SQL语法区别归纳(1)oracle grant 授权语句如何加速Oracle大批量数据处理Oracle删除表的几种方法ORACLE修改IP地址后如何能够使用Oracle 10g创建表空间和用户并指定权限
查看所有0条评论>>