Oracle SqlServer 比較
ORACLE * SQLSERVER 描述
ROWNUM ① select identity(int) rownum列名 into #temp from 表
select * from #temp where rownum> and rownum<=
犧牲性能
② select ROW_NUMBER() OVER (ORDER BY 列名 desc) AS rowNum* from 表名
ORDER BY 列名 desc
可以列出所有記錄RowNUM不能用於條件
③ Select top N from 表
存儲過程裡面定義函數 不支持
TYPE work_table is TABLE of VARCHAR() INDEX BY BINARY_INTEGER; DECLARE @ltb_mailplan Table(ColName VARCHAR() null)
lv_startdate VARCHAR() DECLARE @lv_startdate VARCHAR()
MOD %
trunc(sysdate to_date(//yyyy/mm/dd)) SELECT datediff( day //getdate() )
TO_CHAR(sysdateDY) set language NKorean
select datename(weekday getdate())
星期幾
select INSTR(sdsqs) value from dual select CHARINDEX(ssdsq) value
TO_CHAR(sysdateDD) DatePart(GetdateDD）
select trunc(sysdate) value from dual
select to_char(sysdateyyyymmdd) value from dual
select convert(char()getdate()) value
TO_CHAR(sysdateYYYY/MM/DD) SELECT LTRIM(RTRIM(CONVERT(varchar() GETDATE() )))
TO_CHAR(sysdateHH:MI:SS) SELECT LTRIM(RTRIM(CONVERT(varchar() GETDATE() ))) ::
TO_CHAR(sysdateYYYY/MM/DD HH:MI:SS) CONVERT(varchar() GETDATE() )
PRAGMA AUTONOMOUS_TRANSACTION; BEGIN TRANSACTION
|| +
RAISE_APPLICATION_ERROR( lv_msg); ①
EXEC sp_addmessage @msgnum = @severity =
@msgtext = %s login failed!@lang = us_english
EXEC sp_addmessage @msgnum = @severity =
@msgtext = %!登陸失敗!
RAISERROR( Xing)
錯誤信息處理
② RAISERROR(Xingaixin) >個人定制信息
CURSOR lcur_mailplan (daid AvGomacSettingsDataAreaId%TYPE) IS :
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 = @lv_dataareaid AND CONVERT(varchar()avTpdate) =CONVERT(varchar()GetDate());
>個人定制信息
FOR tp_rec IN lcur_mailplan (@lv_dataareaid)
LOOP
@ltb_mailplan(@lbi_index) = tp_recAvMailplan;
@lbi_index = @lbi_index + ;
END LOOP;
OPEN lcur_mailplan
FETCH lcur_mailplan INTO @ltb_mailplan_Value
WHILE(@@FETCH_STATUS=)
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
LOOP END LOOP While 條件 BEGIN END
CHR() CHAR()
ELSIF ELSE IF
SUBSTR SUBSTRING
NLS_LOWER select lower(dAcB)
NLS_UPPER select upper(dAa)
IF 條件 Then ELSIF 條件 THEN ＥＮＤ IF IF 條件 Begin END Else IF Begin END
TRUNC(SYSDATE) SELECT CONVERT(varchar() GETDATE() )
ISNULL(COLNAME) nvl(COLNAME)
TO_NUMBER(TO_CHAR(SYSDATE SSSSS)); select datediff(ssConvert(datetimeCONVERT(varchar() GETDATE() ))getdate()) 從點以來漸失秒數
EXECUTE IMMEDIATE @lv_updatesql USING @lv_status @an_code @lv_user @ld_date @ln_time
@lv_dataareaid @lv_salesid;
From:http://tw.wingwit.com/Article/program/Oracle/201311/17008.html
