如何建立存儲過程

A. 急求！怎麼建立oracle存儲過程(實例)

create or replace PROCEDURE st_receipt(p_sheetid varchar2(16))
AS
BEGIN
insert into receipt select * from receipt0 where sheetid=p_sheetid ; --將 receipt0表中數據插入 receipt
update receipt set Flag=100 wheresheetid=p_sheetid; --更新receipt表中Flag值

insert into ReceiptItem select * from ReceiptItem0 where sheetid=p_sheetid ;

delete from ReceiptItem0 where sheetid=p_sheetid ;--刪除ReceiptItem0表數據
delete from receipt0 where sheetid=p_sheetid ; --刪除 receipt0表數據
end ;

exec st_receipt('ys141128')

B. 講解Sybase存儲過程的建立和使用

存儲過程的特點

Sybase的存儲過程是集中存儲在sql Server中的預先定義且已經編譯好的事務 存儲租缺過程由SQL語句和流程式控制制語句組成 它的功能包括:接受參數;調用另一過程;返回一個狀態值給調用過程或批處理 指示調用成功或失敗;返回若干個參數值給調用過程或批處理 為調用者提供動態結果;在遠程SQL Server中運行等

存儲過程的性能特點如下:

·存儲過程是預編譯過的 這就意味著它與普通的SQL語句或批處理的SQL語句不同 當首次運行一個存儲過程時 SQL Server的查詢處理器對其進行分析 在排除了語法錯誤之後形成存儲在系統中的可執行方案 由於查詢處理的大部分工作已經完成 所以存儲過程執行速度很快

·存儲過程和待處理的數據都放在同一台運行SQL Server的計算機上 使用存儲過程查詢當地的數據 效率自然很高

·存儲過程一般多由Client端通過存儲過程的名字進行調用 即跨網傳送的只是存儲過程的名字及少量的參數(如果有的話) 而不是構成存儲過程的許多SQL語句 因此可以減少網路傳輸量 加快系統響應速度

·存儲過程還有著如同C語言子函數那樣的被調用和返回值弊搭辯的方便特性

所以 存儲過程大大增強了SQL語言的功能 效率和靈活性 掌握和應用好存儲過程 對進一步發揮Sybase資料庫系統的強大功能有著重要的意義

存儲過程的語法規則

建立存儲過程的語法規則為:

CREATEPROCere[owner ]procerename[;number] [[(]@parameter_namedatatype[=default][OUTput] [ @parameter_namedatatype[=default][OUTput]] [)]] [WITHREPILE] ASSQL_statements

使用存儲過程的語法規則為:

[EXECute][@return status=] [[[server ]database ]owner ]procerename[;number] [[@parameter_name=]value|[@parameter_name=]@varialbe[OUTput] [ [@parameter_name=]value|[@parameter_name=]@variable[OUTput] ]] [WITHREPILE]

[page]下面簡要介紹這兩個命令的常用選項以及建立和使用存儲過程的要點 關於選項的更為詳細的說明請參考有關手冊

·[[[server ]database ]owner ]procere_name:存儲過程的名字

·@parameter_name datatype[=default][OUTput]:形式參數(形參)的名稱 類型 df ault是賦予的預設值(可選) OUTput指定本參數為輸出參數(可選) 形參是存儲過程中的自變數 可以有多個 名字必須以@打頭 最長 個字元

·SQL_statements:定義存儲過程功能的SQL語句

·@return_status:接受存儲過程返回狀態值的變數

·[@parameter_name=]value:實際參數(實參) @parameter_name為實參的名稱(可選) 如果某個實參以@parameter_name=value提供 那麼隨後的實參也都要採用這一形式提供

·[@parameter_name=]@varialbe[OUTput]:將變數@varialbe中的值作為實參傳遞給形參@parameter_name(可選) 如果變數@varialbe是用來接受返回的參數值 則選項OUTput不可缺少

存儲過程的建立和使用 我們將通過幾個例子進行枝配介紹

假設有一個用下述語句生成的技能工資表RS LS GZ JiNeng:

create table RS_LS_GZ_JiNeng /*技能工資表*/

(GeRen_id char( ) /*個人代碼 */

RiQi *** alldatetime /*執行日期 */

YuanYin_id char( ) null /*變動原因代碼 */

JinE *** allmoney) /*技能工資金額 */

該表存儲著某單位員工多年來技能工資的歷史檔案

例 如果要查詢全體員工的技能工資變動歷史 則可先建立一個存儲過程p RsGz JiNeg All:

createprocerep_RsGz_JiNeng_Allas select* fromRS_LS_GZ_JiNeng orderbyGeRenid RiQi

然後用批處理語句調用存儲過程p_RsGz_JiNeng_All進行查詢:

execute p_RsGz_JiNeng_All

本例只顯示查詢到的數據 無輸入 輸出參量 是最簡單的一個存儲過程

例 如果要查詢某人技能工資的變動歷史 可建立另一個存儲過程p_RsGz_JiNeng:

createprocerep_RsGz_JiNeng@c_GeRenIdchar( ) as select*fromRS_LS_GZ_JiNeng whereGeRen_id=@c_GeRenId orderbyRiQi

之後用批處理語句調用存儲過程p_Rs_Gz_JiNeng進行查詢:

declare @GeRenId char( )

select @GeRenId= /*設要查詢員工的個人代碼為 */

execute p_RsGz_JeNeng @c_GeRenId=@GeRenId

存儲過程p_RsGz_JiNeng中定義了一個形參@c_GeRenId 是字元型變數 在調用該過程的批處理中 既可以用具體的值也可以用變數作為實參 用變數作實參(如本例)時 必須用del are語句加以說明 值得注意的是 在批處理的調用過程語句中 @c_GeRenId=@GeRenId中的@ c_GeRenId是存儲過程p_RsGz_JiNeng中的形參名 不是批處理中的變數 所以不能將它列入d eclare語句的變數單中

例 如果要計算當月工資 就必須從工資歷史中查出員工距離當前最近的一次技能工資變動的結果:

createprocerep_RsGz_JiNeng_Slt (@c_GeRenIdchar( ) @ *** _JinE *** allmoneyoutput) as select@ *** _JinE=JinE fromRS_LS_GZ_JiNeng whereRiQi=(selectmax(RiQi) fromRS_LS_GZ_JiNeng

where GeRenid=@c GeRenId)/*找出歷史記錄中距離當前最近的日期*/

調用存儲過程p_RsGz_JiNeng_Slt進行查詢:

declare@GeRenIdchar( ) @JinE *** allmoney select@GeRenid= /*設要查詢員工的個人代碼為 */ select@JinE= executep_RsGz_JiNeng_slt@c_GeRenId=@GeRenId @ *** _JinE=@JinEoutput

這里 變數 @JinE用來存儲過程形參@ *** _JinE傳回的金額 在調用過程語句中 @ *** _JiE = @JinE output中的output不可省略 否則 變數@JinE將得不到形參傳回的數值而始終為零(等於初值)

例 查到了個人代碼為 員工的技能工資就顯示其歷史紀錄 查不到則顯示一條出錯信息

createprocerep_RsGz_JiNeng_Rtn @c_GeRenIdchar( ) as declare@ErrCode *** allint select@ErrCode= ifexists(select*fromRS LS GZ JiNeng whereGeRenid=@c GeRenId) begin select* fromRS_LS_GZ_JiNeng whrerGeRen_id=@c_GeRenId orderbyRiQi return@ErrCodE end eslE begin select@ErrCode= return@ErrCodE end

調用存儲過程p_RsGz_JiNeng_Rtn:

declare@GeRenIdchar( ) @RtnCode *** allint select@GeRenId= select@RtnCode= execute@RtnCode=p_RsGz_JiNeng_Rtn@c_GeRenId=@GeRenId if@RtnCode= print Nothisone!

存儲過程p_RsGz_JiNeng_Rtn向調用者返回一個存儲在變數@ErrCode里的值 這個值被稱為狀態值 它向調用者反映存儲過程執行的成敗狀態 在本例中 如果查不到指定員工技能工資的任何記錄時 就認為 查無此人 返回出錯狀態值 否則 返回成功狀態值

調用過程的批處理語句使用變數@RtnCode存儲返回的狀態值 一旦檢出存儲過程p_RsG_ JiNeng_Rtn返回了錯誤標志(@RtnCode= ) 就顯示一條信息 No this one! [page]

小結

上述四個例子簡要介紹了存儲過程常用的幾種形式 從中我們已經可以領略到它的編程特色以及使用上的靈活性和方便性

C. sqlserver怎麼創建存儲過程

什麼是存儲過程？
q 存儲過程（procere）類似於C語言中的函數
q 用來執行管理任務或應用復雜的業務規則
q 存儲過程可以帶參數，也可以返回結果
q 存儲過程可以包含數據操縱語句、變數、邏輯 控制語句等

存儲過程的優點
(1)執行速度快。
存儲過程創建是就已經通過語法檢查和性能優化，在執行時無需每次編譯。
存儲在資料庫伺服器，性能高。
(2)允許模塊化設計。
只需創建存儲過程一次並將其存儲在資料庫中，以後即可在程序中調用該過程任意次。存儲過程可由在資料庫編程方面有專長的人員創建，並可獨立於程序源代碼而單獨修改 。
(3)提高系統安全性。
可將存儲過程作為用戶存取數據的管道。可以限制用戶對數據表的存取許可權，建立特定的存儲過程供用戶使用，完成對數據的訪問。
存儲過程的定義文本可以被加密，使用戶不能查看其內容。
(4)減少網路流量：
一個需要數百行Transact-SQL代碼的操作由一條執行過程代碼的單獨語句就可實現，而不需要在網路中發送數百行代碼。

存儲過程的分類
q 系統存儲過程
q 由系統定義，存放在master資料庫中
q 類似C語言中的系統函數
q 系統存儲過程的名稱都以「sp_」開頭或」xp_」開頭
q 用戶自定義存儲過程
q 由用戶在自己的資料庫中創建的存儲過程
q 類似C語言中的用戶自定義函數

常用的系統存儲過程

系統存儲過程

說明

sp_databases

列出伺服器上的所有資料庫。

sp_helpdb

報告有關指定資料庫或所有資料庫的信息

sp_renamedb

更改資料庫的名稱

sp_tables

返回當前環境下可查詢的對象的列表

sp_columns

回某個表列的信息

sp_help

查看某個表的所有信息

sp_helpconstraint

查看某個表的約束

sp_helpindex

查看某個表的索引

sp_stored_proceres

列出當前環境中的所有存儲過程。

sp_password

添加或修改登錄帳戶的密碼。

sp_helptext

顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發器或視圖的實際文本。

EXEC sp_databases /*列出當前系統中的資料庫*/
EXEC sp_renamedb 'Northwind','Northwind1' /*修改資料庫的名稱(單用戶訪問, 最簡單的辦法就是執行SQL語句時關掉企業管理器)*/
USE stuDB
GO
EXEC sp_tables /*當前資料庫中查詢的對象的列表*/
EXEC sp_columns stuInfo /*返回某個表列的信息*/
EXEC sp_help stuInfo /*查看錶stuInfo的信息*/
EXEC sp_helpconstraint stuInfo /*查看錶stuInfo的約束*/
EXEC sp_helpindex stuMarks /*查看錶stuMarks的索引*/
EXEC sp_helptext 'view_stuInfo_stuMarks' /*查看視圖的語句文本*/
EXEC sp_stored_proceres /*查看當前資料庫中的存儲過程*/

常用的擴展存儲過程
q 常用的擴展存儲過程：xp_cmdshell
q 可以執行DOS命令下的一些的操作
q 以文本行方式返回任何輸出
q 調用語法：
q EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
USE master
GO
EXEC xp_cmdshell 'mkdir d:/bank', NO_OUTPUT /*創建文件夾D:/bank*/
IF EXISTS(SELECT * FROM sysdatabases
WHERE name='bankDB')
DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
(
…
)
GO
EXEC xp_cmdshell 'dir D:/bank/' --查看文件 /*查看文件夾D:/bank*/

如何創建存儲過程？
q 定義存儲過程的語法
CREATE PROC[EDURE] 存儲過程名
@參數1 數據類型 = 默認值,
…… ,
@參數n 數據類型 OUTPUT
AS
SQL語句
GO
q 和C語言的函數一樣，參數可選
q 參數分為輸入參數、輸出參數
q 輸入參數允許有默認值

創建不帶參數的存儲過程
CREATE PROCEDURE proc_stu /* proc_stu為存儲過程的名稱*/
AS
DECLARE @writtenAvg float,@labAvg float /* 筆試平均分和機試平均分變數 */
SELECT @writtenAvg=AVG(writtenExam),
@labAvg=AVG(labExam) FROM stuMarks
print '筆試平均分：'+convert(varchar(5),@writtenAvg)
print '機試平均分：'+convert(varchar(5),@labAvg)
IF (@writtenAvg>70 AND @labAvg>70)
print '本班考試成績：優秀' /* 顯示考試成績的等級 */
ELSE
print '本班考試成績：較差'
print '--------------------------------------------------'
print ' 參加本次考試沒有通過的學員：'
SELECT stuName,stuInfo.stuNo,writtenExam,labExam /* 顯示未通過的學員 */
FROM stuInfo INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<60 OR labExam<60
GO

調用存儲過程
q EXECUTE（執行）語句用來調用存儲過程
q 調用的語法
EXEC 過程名 [參數]

創建帶參數的存儲過程
q 存儲過程的參數分兩種：
q 輸入參數
q 輸出參數
q 輸入參數：
用於向存儲過程傳入值，類似C語言的按值傳遞;
q 輸出參數：
用於在調用存儲過程後，
返回結果，類似C語言的
按引用傳遞;

帶輸入參數的存儲過程
問題：
修改上例：由於每次考試的難易程度不一樣，每次 筆試和機試的及格線可能隨時變化（不再是60分），這導致考試的評判結果也相應變化。

分析：
在述存儲過程添加2個輸入參數：
@writtenPass 筆試及格線
@labPass 機試及格線
CREATE PROCEDURE proc_stu
@writtenPass int = 60, /*輸入參數：筆試及格線*/
@labPass int = 60 /*輸入參數：機試及格線*/
AS
print '--------------------------------------------------'
print ' 參加本次考試沒有通過的學員：'
SELECT stuName,stuInfo.stuNo,writtenExam, /*查詢沒有通過考試的學員*/
labExam FROM stuInfo
INNER JOIN stuMarks ON
stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
q 調用帶參數的存儲過程
假定本次考試機試偏難，機試的及格線定為55分，筆試及格線定為60分
EXEC proc_stu 60,55
--或這樣調用：
EXEC proc_stu @labPass=55,@writtenPass=60

帶輸出參數的存儲過程
q 如果希望調用存儲過程後，返回一個或多個值，這時就需要使用輸出（OUTPUT）參數了
問題：
修改上例，返回未通過考試的學員人數。
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, /*輸出（返回）參數：表示沒有通過的人數*/
@writtenPass int=60, /*推薦將默認參數放在最後*/
@labPass int=60
AS
……
SELECT stuName,stuInfo.stuNo,writtenExam, /*統計並返回沒有通過考試的學員人數*/
labExam FROM stuInfo INNER JOIN stuMarks
ON stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
OR labExam<@labPass
SELECT @notpassSum=COUNT(stuNo)
FROM stuMarks WHERE writtenExam<@writtenPass
OR labExam<@labPass
GO
q 調用帶輸出參數的存儲過程
/*---調用存儲過程----*/
DECLARE @sum int /*調用時必須帶OUTPUT關鍵字 ，返回結果將存放在變數@sum中*/
EXEC proc_stu @sum OUTPUT ,64
print '--------------------------------------------------'
IF @sum>=3 /*後續語句引用返回結果*/
print '未通過人數：'+convert(varchar(5),@sum)+ '人,
超過60%,及格分數線還應下調'
ELSE
print '未通過人數：'+convert(varchar(5),@sum)+ '人,
已控制在60%以下，及格分數線適中'
GO
注意：調用時也必須跟隨關鍵字OUTPUT，否則SQL Server將視為輸入參數。

處理存儲過程中的錯誤
q 可以使用PRINT語句顯示錯誤信息，但這 些信息是臨時的，只能顯示給用戶
q RAISERROR 顯示用戶定義的錯誤信息時
q 可指定嚴重級別，
q 設置系統變數@@ERROR
q 記錄所發生的錯誤等
q RAISERROR語句的用法如下：
RAISERROR (msg_id | msg_str,severity,
state WITH option[,...n]])
• msg_id：在sysmessages系統表中指定用戶定義錯誤信息
• msg_str：用戶定義的特定信息，最長255個字元
• severity：定義嚴重性級別。用戶可使用的級別為0–18級
• state：表示錯誤的狀態，1至127之間的值
• option：指示是否將錯誤記錄到伺服器錯誤日誌中
RAISERROR 語句每個參數的詳細講解，可以查閱SQL幫助!
問題：
完善上例，當用戶調用存儲過程時，傳入的及格線參數不
在0～100之間時，將彈出錯誤警告，終止存儲過程的執行。
CREATE PROCEDURE proc_stu
@notpassSum int OUTPUT, --輸出參數
@writtenPass int=60, --默認參數放後
@labPass int=60 --默認參數放後
AS
IF (NOT @writtenPass BETWEEN 0 AND 100)
OR (NOT @labPass BETWEEN 0 AND 100)
/*引發系統錯誤，指定錯誤的嚴重級別16，調用狀態為1（默認）,並影響@@ERROR系統變數的值 */
BEGIN
RAISERROR (『及格線錯誤，請指定0－100之間的分
數，統計中斷退出',16,1)
RETURN ---立即返回，退出存儲過程
END
…..其他語句同上例，略
GO
/*---調用存儲過程，測試RAISERROR語句----*/
DECLARE @sum int, @t int
EXEC proc_stu @sum OUTPUT ,604 /*筆試及格線誤輸入604分*/
SET @t=@@ERROR
print '錯誤號：'+convert(varchar(5),@t )
IF @t<>0 /*如果執行了RAISERROR，系統全局@@ERROR將不等於0，表示出現錯誤*/
RETURN --退出批處理，後續語句不再執行
print '--------------------------------------------------'
IF @sum>=3
print '未通過人數：'+convert(varchar(5),@sum)+ '人,超過60%,及格分數線還應下調'
ELSE
print '未通過人數：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分數線適中'
GO

好，我們來做個總結：
• 存儲過程是一組預編譯的SQL語句，它可以包含數據操縱語句、變數、邏輯控制語句等
• 存儲過程允許帶參數，參數分為：
– 輸入參數
– 輸出參數
其中，輸入參數可以有默認值。
• 輸入參數：可以在調用時向存儲過程傳遞參數，此類參數可用來向存儲過程中傳入值
• 輸出參數從存儲過程中返回（輸出）值，後面跟隨OUTPUT關鍵字
• RAISERROR語句用來向用戶報告錯誤

D. 在SQL中存儲過程的一般語法是什麼

1、 創建語法

createproc|procerepro_name

[{@參數數據類型}[=默認值][output],

{@參數數據類型}[=默認值][output],

....

]

as

SQL_statements

2、 創建不帶參數存儲過程

--創建存儲過程

if(exists(select*fromsys.objectswherename='proc_get_student'))

dropprocproc_get_student

go

createprocproc_get_student

as

select*fromstudent;

--調用、執行存儲過程

execproc_get_student;

3、 修改存儲過程

--修改存儲過程

alterprocproc_get_student

as

select*fromstudent;

4、 帶參存儲過程

--帶參存儲過程

if(object_id('proc_find_stu','P')isnotnull)

dropprocproc_find_stu

go

createprocproc_find_stu(@startIdint,@endIdint)

as

select*fromstudentwhereidbetween@startIdand@endId

go

execproc_find_stu2,4;

5、 帶通配符參數存儲過程

--帶通配符參數存儲過程

if(object_id('proc_findStudentByName','P')isnotnull)

dropprocproc_findStudentByName

go

createprocproc_findStudentByName(@namevarchar(20)='%j%',@nextNamevarchar(20)='%')

as

select*fromstudentwherenamelike@nameandnamelike@nextName;

go

execproc_findStudentByName;execproc_findStudentByName'%o%','t%';

(4)如何建立存儲過程擴展閱讀：

SQL存儲過程優點：

1、重復使用。存儲過程可以重復使用，從而可以減少資料庫開發人員的工作量。

2、減少網路流量。存儲過程位於伺服器上，調用的時候只需要傳遞存儲過程的名稱以及參數就可以了，因此降低了網路傳輸的數據量。

3、安全性。參數化的存儲過程可以防止SQL注入式攻擊，而且可以將Grant、Deny以及Revoke許可權應用於存儲過程。

E. mysql如何創建存儲過程

1 用mysql客戶端登入
2 選擇資料庫
mysql>use test
3 查詢當前資料庫有哪些存儲過程
mysql>show procere status where Db='test'
4 創建一個簡單的存儲過程
mysql>create procere hi() select 'hello';
5 存儲過程創建完畢,看怎麼調用它
mysql>call hi();
顯示結果 mysql> call hi();
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)
6 一個簡單的儲存過程就成功了

F. 在SQL server資料庫中為什麼要建立存儲過程，怎樣建立存儲過程，望各位大俠指教一二

存儲過程的可以在性能高強的伺服器端運行；存儲過程的使用便於項目的公共開發和管理；存儲過程避免sql語句在網上的明碼傳遞，大大提高資料庫的安全。存儲過程可以在sql管理客戶端界面建立，修改，也可以利用語句建立的。

例子：

方式一:

打開sql伺服器管理工具客戶端：

代碼中【begin】和【end】之間寫你的代碼，做完後點擊「執行」；

方式二：

就是把右邊編輯窗的代碼通過客戶端軟體送到sql伺服器上執行

G. 如何創建MySQL存儲過程可以返回一個表類型的數據

如何創建MySQL存儲過程可以返回一個表類型的數據
首先需要知道「另一個存儲過程」的結果集的所有列的類型。
假設「另一個存儲過程」的名字是sp1，沒有參數，返回的結果集共3列，全部為int型，那麼「存儲過程」里添加一個與結果集列數相同的臨時表或表變數用於接收「另一個存儲過程」的結果集
如下
CREATE PROCEDURE sp2
AS
DECLARE @t table(a int,b int,c int)

INSERT INTO @t(a,b,c)
EXEC sp1

SELECT * FROM @t
使用SQLSERVER存儲過程可以很大的提高程序運行速度，簡化編程維護難度，現已得到廣泛應用。
創建存儲過程
和數據表一樣，在使用之前需要創建存儲過程，它的簡明語法是：

引用:
Create PROC 存儲過程名稱
[參數列表（多個以「,」分隔）]
AS
SQL 語句

例：

引用:
Create PROC upGetUserName
@intUserId INT,
@ostrUserName NVARCHAR(20) OUTPUT -- 要輸出的參數
AS
BEGIN
-- 將uName的值賦給 @ostrUserName 變數，即要輸出的參數
Select @ostrUserName=uName FROM uUser Where uId=@intUserId
END

其中 Create PROC 語句（完整語句為Create PROCEDURE）的意思就是告訴SQL SERVER，現在需要建立一個存儲過程，upGetUserName 就是存儲過程名稱，@intUserId 和 @ostrUserName 分別是該存儲過程的兩個參數，注意，在SQL SERVER中，所有用戶定義的變數都以「@」開頭，OUTPUT關鍵字表示這個參數是用來輸出的，AS之後就是存儲過程內容了。只要將以上代碼在「查詢分析器」里執行一次，SQL SERVER就會在當前資料庫中創建一個名為「upGetUserName」的存儲過程。你可以打開「企業管理器」，選擇當前操作的資料庫，然後在左邊的樹型列表中選擇「存儲過程」，此時就可以在右邊的列表中看到你剛剛創建的存儲過程了（如果沒有，刷新一下即可）。
二、存儲過程的調用

之前已經創建了一個名為「upGetUserName」的存儲過程，從字面理解該存儲過程的功能是用來取得某一個用戶的名稱。存儲過程建立好了，接下來就是要在應用程序里調用了，下面看一下在ASP程序里的調用。