Oracle和SQL Server存储过程的不同

一．    多表连接查询,更新存储过程
Sql存储过程
ALTER PROCEDURE [ dbo ] . [ GetEvent ]
    @SCSWId nvarchar （ 20 ） = null ,
    @ToDate DATETIME ,
    @FromDate DATETIME
AS
    SELECT NOTES.NOTE_ID,
        NOTES.NOTE,
        SCSW_CALENDAR.DATE_TIME   
    FROM SCSW_CALENDAR
        LEFT OUTER JOIN NOTES ON SCSW_CALENDAR.NOTE_ID = notes.note_id
    WHERE SCSW_CALENDAR.SCSW_ID = SCSWId
    ORDER BY Patient.PatientId

Oracel存储过程

1 ．查询数据的存储过程
PROCEDURE GetEvent(SCSWId IN VARCHAR2 , FromDate IN DATE, ToDate IN DATE, refOut OUT refcursor)
IS
BEGIN
     OPEN refOut FOR
     select NOTES.NOTE_ID,
NOTES.NOTE,
SCSW_CALENDAR.DATE_TIME
     from SCSW_CALENDAR
     left join NOTES on SCSW_CALENDAR.NOTE_ID = notes.note_id
     where SCSW_CALENDAR.SCSW_ID = SCSWId
     AND SCSW_CALENDAR.DATE_TIME >= FromDate
     AND SCSW_CALENDAR.DATE_TIME < ToDate
     order by SCSW_CALENDAR.DATE_TIME;
END GetEvent;

2 ．更新数据的存储过程：
procedure UpdateArticlesubmodel
(
ArticleSubID number ,
ArticleTitle nvarchar2,
ArticleKeyWord nvarchar2,
ArticleContent CLOB,
CreatePerson nvarchar2,
ChangeDate date,
SetTop number ,
ArticleSubStyleID number ,
Checked number
)
as
begin
     update "ArticleSubModel"
     set "ArticleTitle" = ArticleTitle,
         "ArticleKeyWord" = ArticleKeyWord,
         "ArticleContent" = ArticleContent,
         "CreatePerson" = CreatePerson,
         "CreateDate" = ChangeDate,
         "SetTop" = SetTop,
         "ArticleSubStyleID" = ArticleSubStyleID,
         "Checked" = Checked
      where "ArticleSubID" = ArticleSubID;
      commit ;
      Exception when others then
      rollback ;
end UpdateArticlesubmodel;
3 ．删除数据的存储过程
procedure DeleteArticlesubmodel
(
ArticleSubID number
)
as
begin
     delete from "ArticleSubAccessories"
     where "ArticleSubID" = ArticleSubID;
     delete from "ArticleSubModel"
     where "ArticleSubID" = ArticleSubID;
     commit ;
     Exception when others then
     rollback ;
end DeleteArticlesubmodel;