Oracle和Mssql預存程序區別

預存程序 

 一． 多表串連查詢,更新預存程序 

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;