多表连接查询,更新存储过程代码,PLSQL代码库,德仔网

代码语言
代码分类
【PLSQL】多表连接查询,更新存储过程
作者:Dezai.CN / 发布于2011/7/22/ 945
<div>Oracel存储过程

<span style="color: rgb(128,0,0); font-weight: bold">1</span><span style="color: rgb(0,0,0)">．查询数据的存储过程
</span><span style="color: rgb(0,0,255)">PROCEDURE</span><span style="color: rgb(0,0,0)"> GetEvent(SCSWId </span><span style="color: rgb(128,128,128)">IN</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">VARCHAR2</span><span style="color: rgb(0,0,0)">, FromDate </span><span style="color: rgb(128,128,128)">IN</span><span style="color: rgb(0,0,0)"> DATE, ToDate </span><span style="color: rgb(128,128,128)">IN</span><span style="color: rgb(0,0,0)"> DATE, refOut OUT refcursor)
</span><span style="color: rgb(0,0,255)">IS</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">BEGIN</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">OPEN</span><span style="color: rgb(0,0,0)"> refOut </span><span style="color: rgb(0,0,255)">FOR</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> NOTES.NOTE_ID, 
NOTES.NOTE, 
SCSW_CALENDAR.DATE_TIME
</span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> SCSW_CALENDAR
</span><span style="color: rgb(128,128,128)">left</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">join</span><span style="color: rgb(0,0,0)"> NOTES </span><span style="color: rgb(0,0,255)">on</span><span style="color: rgb(0,0,0)"> SCSW_CALENDAR.NOTE_ID</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">notes.note_id
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> SCSW_CALENDAR.SCSW_ID </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> SCSWId
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> SCSW_CALENDAR.DATE_TIME </span><span style="color: rgb(128,128,128)">>=</span><span style="color: rgb(0,0,0)"> FromDate
</span><span style="color: rgb(128,128,128)">AND</span><span style="color: rgb(0,0,0)"> SCSW_CALENDAR.DATE_TIME </span><span style="color: rgb(128,128,128)"><</span><span style="color: rgb(0,0,0)"> ToDate
</span><span style="color: rgb(0,0,255)">order</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">by</span><span style="color: rgb(0,0,0)"> SCSW_CALENDAR.DATE_TIME;
</span><span style="color: rgb(0,0,255)">END</span><span style="color: rgb(0,0,0)"> GetEvent;

</span><span style="color: rgb(128,0,0); font-weight: bold">2</span><span style="color: rgb(0,0,0)">．更新数据的存储过程：
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> UpdateArticlesubmodel
(
ArticleSubID </span><span style="color: rgb(0,0,255)">number</span><span style="color: rgb(0,0,0)">,
ArticleTitle nvarchar2,
ArticleKeyWord nvarchar2,
ArticleContent CLOB,
CreatePerson nvarchar2,
ChangeDate date,
SetTop </span><span style="color: rgb(0,0,255)">number</span><span style="color: rgb(0,0,0)">,
ArticleSubStyleID </span><span style="color: rgb(0,0,255)">number</span><span style="color: rgb(0,0,0)">,
Checked </span><span style="color: rgb(0,0,255)">number</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">update</span><span style="color: rgb(0,0,0)"> "ArticleSubModel"
</span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> "ArticleTitle"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleTitle,
"ArticleKeyWord"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleKeyWord,
"ArticleContent"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleContent,
"CreatePerson"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">CreatePerson,
"CreateDate"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ChangeDate,
"SetTop"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">SetTop,
"ArticleSubStyleID"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleSubStyleID,
"Checked"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">Checked
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> "ArticleSubID"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleSubID;
</span><span style="color: rgb(0,0,255)">commit</span><span style="color: rgb(0,0,0)">;
Exception </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> others </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">rollback</span><span style="color: rgb(0,0,0)">;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> UpdateArticlesubmodel;
</span><span style="color: rgb(128,0,0); font-weight: bold">3</span><span style="color: rgb(0,0,0)">．删除数据的存储过程
</span><span style="color: rgb(0,0,255)">procedure</span><span style="color: rgb(0,0,0)"> DeleteArticlesubmodel
(
ArticleSubID </span><span style="color: rgb(0,0,255)">number</span><span style="color: rgb(0,0,0)">
)
</span><span style="color: rgb(0,0,255)">as</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">begin</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">delete</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> "ArticleSubAccessories"
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> "ArticleSubID"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleSubID;
</span><span style="color: rgb(0,0,255)">delete</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">from</span><span style="color: rgb(0,0,0)"> "ArticleSubModel"
</span><span style="color: rgb(0,0,255)">where</span><span style="color: rgb(0,0,0)"> "ArticleSubID"</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)">ArticleSubID;
</span><span style="color: rgb(0,0,255)">commit</span><span style="color: rgb(0,0,0)">;
Exception </span><span style="color: rgb(0,0,255)">when</span><span style="color: rgb(0,0,0)"> others </span><span style="color: rgb(0,0,255)">then</span><span style="color: rgb(0,0,0)">
</span><span style="color: rgb(0,0,255)">rollback</span><span style="color: rgb(0,0,0)">;
</span><span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,0)"> DeleteArticlesubmodel;</span></div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)