存儲(chǔ)過(guò)程實(shí)例(存儲(chǔ)過(guò)程實(shí)例 oracle)

存儲(chǔ)過(guò)程的實(shí)例

數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程
數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程的實(shí)質(zhì)就是部署在數(shù)據(jù)庫(kù)端的一組定義代碼以及SQL。將常用的或很復(fù)雜的工作，預(yù)先用SQL語(yǔ)句寫好并用一個(gè)指定的名稱存儲(chǔ)起來(lái)，那么以后要叫數(shù)據(jù)庫(kù)提供與已定義好的存儲(chǔ)過(guò)程的功能相同的服務(wù)時(shí)，只需調(diào)用execute，即可自動(dòng)完成命令。
利用SQL的語(yǔ)言可以編寫對(duì)于數(shù)據(jù)庫(kù)訪問(wèn)的存儲(chǔ)過(guò)程，其語(yǔ)法如下：
CREATE PROC[EDURE] procedure_name [;number]
[
{@parameter data_type} ][VARYING] [= default] [OUTPUT]
]
[,...n]
[WITH
{
RECOMPILE
| ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
[FOR REPLICATION]
AS
sql_statement [...n]
[ ]內(nèi)的內(nèi)容是可選項(xiàng)，而（）內(nèi)的內(nèi)容是必選項(xiàng)，
例：若用戶想建立一個(gè)刪除表tmp中的記錄的存儲(chǔ)過(guò)程Select_delete可寫為：
Create Proc lect_del As
Delete tmp
例：用戶想查詢tmp表中某年的數(shù)據(jù)的存儲(chǔ)過(guò)程
create proc lect_query @year int as
lect * from tmp where year=@year
在這里@year是存儲(chǔ)過(guò)程的參數(shù)
例：該存儲(chǔ)過(guò)程是從某結(jié)點(diǎn)n開(kāi)始找到最上層的父親結(jié)點(diǎn)，這種經(jīng)常用到的過(guò)程可以由存儲(chǔ)過(guò)程來(lái)?yè)?dān)當(dāng)，在網(wǎng)頁(yè)中重復(fù)使用達(dá)到共享。
空：表示該結(jié)點(diǎn)為頂層結(jié)點(diǎn)
fjdid(父結(jié)點(diǎn)編號(hào))
結(jié)點(diǎn)n 非空：表示該結(jié)點(diǎn)的父親結(jié)點(diǎn)號(hào)
dwmc（單位名稱） CREATEprocarch_dwmc@dwidoldint,@dwmcresultvarchar(100)outputasdeclare@stopintdeclare@resultvarchar(80)declare@dwmcvarchar(80)declare@dwidinttnocountont@stop=1t@dwmc=""lect@dwmc=dwmc,@dwid=convert(int,fjdid)fromjtdwwhereid=@dwidoldt@result=rtrim(@dwmc)if@dwid=0t@stop=0while(@stop=1)and(@dwid<>0)begint@dwidold=@dwidlect@dwmc=dwmc,@dwid=convert(int,fjdid)fromjtdwwhereid=@dwidoldif@@rowcount=0t@dwmc=""elt@result=@dwmc+@resultif(@dwid=0)or(@@rowcount=0)t@stop=0elcontinueendt@dwmcresult=rtrim(@result)使用execpro-name[pram1pram2.....]SQL Server中存儲(chǔ)過(guò)程
sql語(yǔ)句執(zhí)行的時(shí)候要先編譯，然后執(zhí)行。存儲(chǔ)過(guò)程就是編譯好了的一些sql語(yǔ)句。用的時(shí)候直接就可以用了。
在SQL Server的查詢分析器中，輸入以下代碼:
declare @tot_amt int
execute order_tot_amt 1,@tot_amt output
lect @tot_amt
以上代碼是執(zhí)行order_tot_amt這一存儲(chǔ)過(guò)程，以計(jì)算出訂單編號(hào)為1的訂單銷售金額，我們定義@tot_amt為輸出參數(shù)，用來(lái)承接我們所要的結(jié)果。
Oracle中的存儲(chǔ)過(guò)程
1.創(chuàng)建過(guò)程
與其它的數(shù)據(jù)庫(kù)系統(tǒng)一樣，Oracle的存儲(chǔ)過(guò)程是用PL/SQL語(yǔ)言編寫的能完成一定處理功能的存儲(chǔ)在數(shù)據(jù)庫(kù)字典中的程序。
語(yǔ)法:
create [or replace] procedure procedure_name
[ (argment [ { in| in out }] type,
argment [ { in | out | in out } ] type
{ is | as }
<類型.變量的說(shuō)明>
(注:不用 declare語(yǔ)句 )
Begin
<執(zhí)行部分>
exception
<可選的異常處理說(shuō)明>
end;
1.1 這里的IN表示向存儲(chǔ)過(guò)程傳遞參數(shù)，OUT表示從存儲(chǔ)過(guò)程返回參數(shù)。而IN OUT 表示傳遞參數(shù)和返回參數(shù)；
1.2 在存儲(chǔ)過(guò)程內(nèi)的參數(shù)只能指定參數(shù)類型；不能指定長(zhǎng)度；
1.3 在AS或IS 后聲明要用到的變量名稱和變量類型及長(zhǎng)度；
1.4 在AS或IS 后聲明變量不要加declare 語(yǔ)句。
2.使用過(guò)程
存儲(chǔ)過(guò)程建立完成后，只要通過(guò)授權(quán)，用戶就可以在SQLPLUS 、Oracle開(kāi)發(fā)工具或第三方開(kāi)發(fā)工具來(lái)調(diào)用運(yùn)行。Oracle 使用CALL 語(yǔ)句來(lái)實(shí)現(xiàn)對(duì)存儲(chǔ)過(guò)程的調(diào)用。
語(yǔ)法：
call procedure_name( parameter1, parameter2…);
3.開(kāi)發(fā)過(guò)程
如今的幾大數(shù)據(jù)庫(kù)廠商提供的編寫存儲(chǔ)過(guò)程的工具都沒(méi)有統(tǒng)一，雖然它們的編寫風(fēng)格有些相似，但由于沒(méi)有標(biāo)準(zhǔn)，所以各家的開(kāi)發(fā)調(diào)試過(guò)程也不一樣。下面編寫PL/SQL存儲(chǔ)過(guò)程、函數(shù)、包及觸發(fā)器的步驟如下：
3.1 編輯存儲(chǔ)過(guò)程源碼使用文字編輯處理軟件編輯存儲(chǔ)過(guò)程源碼，要用類似WORD文字處理軟件進(jìn)行編輯時(shí)，要將源碼存為文本格式。
3.2 對(duì)存儲(chǔ)過(guò)程程序進(jìn)行解釋在SQLPLUS或用調(diào)試工具將 存儲(chǔ)過(guò)程程序進(jìn)行解釋；
在SQL>下調(diào)試，可用start 或get 等Oracle命令來(lái)啟動(dòng)解釋。如：
SQL>start c:stat1.sql
如果使用調(diào)試工具，可直接編輯和點(diǎn)擊相應(yīng)的按鈕即可生成存儲(chǔ)過(guò)程。
3.3 調(diào)試源碼直到正確我們不能保證所寫的存儲(chǔ)過(guò)程達(dá)到一次就正確。所以這里的調(diào)試是每個(gè)程序員必須進(jìn)行的工作之一。在SQLPLUS下來(lái)調(diào)試主要用的方法是：
1．使用 SHOW ERROR命令來(lái)提示源碼的錯(cuò)誤位置；
2．使用 USER_ERRORS數(shù)據(jù)字典來(lái)查看各存儲(chǔ)過(guò)程的錯(cuò)誤位置。
3.4 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色如果調(diào)試正確的存儲(chǔ)過(guò)程沒(méi)有進(jìn)行授權(quán)，那就只有建立者本人才可以運(yùn)行。所以作為應(yīng)用系統(tǒng)的一部分的存儲(chǔ)過(guò)程也必須進(jìn)行授權(quán)才能達(dá)到要求。 在SQLPLUS下可以用GRANT命令來(lái)進(jìn)行存儲(chǔ)過(guò)程的運(yùn)行授權(quán)。
語(yǔ)法：
GRANT system_privilege | role TO ur | role | PUBLIC
[WITH ADMIN OPTION]
或
GRANT object_privilege | ALL column ON schema.object
TO ur | role | PUBLIC WITH GRANT OPTION
其中
system_privilege: 系統(tǒng)權(quán)限
role: 角色名
ur: 被授權(quán)的用戶名
object_privilege: 所授予的權(quán)限名字，可以是
ALTER
DELETE
EXECUTE
INDEX
INSERT
REFERENCES
SELECT
UPDATE
Column: 列名
schema: 模式名
object: 對(duì)象名
4.數(shù)據(jù)字典
USER_SOURCE 用戶的存儲(chǔ)過(guò)程、函數(shù)的源代碼字典
DBA_SOURCE 整個(gè)系統(tǒng)所有用戶的存儲(chǔ)過(guò)程、函數(shù)的源代碼字典
ALL_SOURCE 當(dāng)前用戶能使用的存儲(chǔ)過(guò)程(包括其她用戶授權(quán))、函數(shù)的源代碼字典
USER_ERRORS 用戶的存儲(chǔ)過(guò)程、函數(shù)的源代碼存在錯(cuò)誤的信息字典
臨時(shí)表
（針對(duì)SQL2000/2005）
可以創(chuàng)建本地和全局臨時(shí)表。本地臨時(shí)表僅在當(dāng)前會(huì)話中可見(jiàn)；全局臨時(shí)表在所有會(huì)話中都可見(jiàn)。
本地臨時(shí)表的名稱前面有一個(gè)編號(hào)符 (#table_name)，而全局臨時(shí)表的名稱前面有兩個(gè)編號(hào)符 (##table_name)。
SQL 語(yǔ)句使用 CREATE TABLE 語(yǔ)句中為 table_name 指定的名稱引用臨時(shí)表：
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)
如果本地臨時(shí)表由存儲(chǔ)過(guò)程創(chuàng)建或由多個(gè)用戶同時(shí)執(zhí)行的應(yīng)用程序創(chuàng)建，則 SQL Server 必須能夠區(qū)分由不同用戶創(chuàng)建的表。為此，SQL Server 在內(nèi)部為每個(gè)本地臨時(shí)表的表名追加一個(gè)數(shù)字后綴。存儲(chǔ)在 tempdb 數(shù)據(jù)庫(kù)的 sysobjects 表中的臨時(shí)表，其全名由 CREATE TABLE 語(yǔ)句中指定的表名和系統(tǒng)生成的數(shù)字后綴組成。為了允許追加后綴，為本地臨時(shí)表指定的表名 table_name 不能超過(guò) 116 個(gè)字符。
除非使用 DROP TABLE 語(yǔ)句顯式除去臨時(shí)表，否則臨時(shí)表將在退出其作用域時(shí)由系統(tǒng)自動(dòng)除去：
當(dāng)存儲(chǔ)過(guò)程完成時(shí)，將自動(dòng)除去在存儲(chǔ)過(guò)程中創(chuàng)建的本地臨時(shí)表。由創(chuàng)建表的存儲(chǔ)過(guò)程執(zhí)行的所有嵌套存儲(chǔ)過(guò)程都可以引用此表。但調(diào)用創(chuàng)建此表的存儲(chǔ)過(guò)程的進(jìn)程無(wú)法引用此表。
所有其它本地臨時(shí)表在當(dāng)前會(huì)話結(jié)束時(shí)自動(dòng)除去。
全局臨時(shí)表在創(chuàng)建此表的會(huì)話結(jié)束且其它任務(wù)停止對(duì)其引用時(shí)自動(dòng)除去。任務(wù)與表之間的關(guān)聯(lián)只在單個(gè) Transact-SQL 語(yǔ)句的生存周期內(nèi)保持。換言之，當(dāng)創(chuàng)建全局臨時(shí)表的會(huì)話結(jié)束時(shí)，最后一條引用此表的 Transact-SQL 語(yǔ)句完成后，將自動(dòng)除去此表。

簡(jiǎn)單sql存儲(chǔ)過(guò)程實(shí)例、儲(chǔ)過(guò)程實(shí)戰(zhàn)

實(shí)例1：只返回單一記錄集的存儲(chǔ)過(guò)程。
銀行存款表（bankMoney）的內(nèi)容如下
Id
urID
Sex
Money
001
Zhangsan
男
30
002
Wangwu
男
50
003
Zhangsan
男
40
要求1：查詢表bankMoney的內(nèi)容的存儲(chǔ)過(guò)程
create
procedure
sp_query_bankMoney
as
lect
*
from
bankMoney
go
exec
sp_query_bankMoney
注*
在使用過(guò)程中只需要把中的SQL語(yǔ)句替換為存儲(chǔ)過(guò)程名，就可以了很方便吧！
實(shí)例2（向存儲(chǔ)過(guò)程中傳遞參數(shù)）：
加入一筆記錄到表bankMoney，并查詢此表中urID=
Zhangsan的所有存款的總金額。
Create
proc
inrt_bank
@param1
char(10),@param2
varchar(20),@param3
varchar(20),@param4
int,@param5
int
output
with
encryption
---------加密
as
inrt
bankMoney
(id,urID,x,Money)
Values(@param1,@param2,@param3,
@param4)
lect
@param5=sum(Money)
from
bankMoney
where
urID='Zhangsan'
go
在SQL
Server查詢分析器中執(zhí)行該存儲(chǔ)過(guò)程的方法是：
declare
@total_price
int
exec
inrt_bank
'004','Zhangsan','男',100,@total_price
output
print
'總余額為'+convert(varchar,@total_price)
go
在這里再啰嗦一下存儲(chǔ)過(guò)程的3種傳回值（方便正在看這個(gè)例子的朋友不用再去查看語(yǔ)法內(nèi)容）:
1.以Return傳回整數(shù)
2.以output格式傳回參數(shù)
3.Recordt
傳回值的區(qū)別:
output和return都可在批次程式中用變量接收,而recordt則傳回到執(zhí)行批次的客戶端中。
實(shí)例3：使用帶有復(fù)雜
SELECT
語(yǔ)句的簡(jiǎn)單過(guò)程
　　下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中返回所有作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程不使用任何參數(shù)。
USE
pubs
IF
EXISTS
(SELECT
name
FROM
sysobjects
WHERE
name
=
'au_info_all'
AND
type
=
'P')
DROP
PROCEDURE
au_info_all
GO
CREATE
PROCEDURE
au_info_all
AS
SELECT
au_lname,
au_fname,
title,
pub_name
FROM
authors
a
INNER
JOIN
titleauthor
ta
ON
a.au_id
=
ta.au_id
INNER
JOIN
titles
t
ON
t.title_id
=
ta.title_id
INNER
JOIN
publishers
p
ON
t.pub_id
=
p.pub_id
GO
　　au_info_all
存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：
EXECUTE
au_info_all
實(shí)例4：使用帶有參數(shù)的簡(jiǎn)單過(guò)程
CREATE
PROCEDURE
au_info
@lastname
varchar(40),
@firstname
varchar(20)
AS
SELECT
au_lname,
au_fname,
title,
pub_name
FROM
authors
a
INNER
JOIN
titleauthor
ta
ON
a.au_id
=
ta.au_id
INNER
JOIN
titles
t
ON
t.title_id
=
ta.title_id
INNER
JOIN
publishers
p
ON
t.pub_id
=
p.pub_id
WHERE
au_fname
=
@firstname
AND
au_lname
=
@lastname
GO
　　au_info
存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：
EXECUTE
au_info
'Dull',
'Ann'
--
Or
EXECUTE
au_info
@lastname
=
'Dull',
@firstname
=
'Ann'
--
Or
EXECUTE
au_info
@firstname
=
'Ann',
@lastname
=
'Dull'
實(shí)例5：使用帶有通配符參數(shù)的簡(jiǎn)單過(guò)程
CREATE
PROCEDURE
au_info2
@lastname
varchar(30)
=
'D%',
@firstname
varchar(18)
=
'%'
AS
SELECT
au_lname,
au_fname,
title,
pub_name
FROM
authors
a
INNER
JOIN
titleauthor
ta
ON
a.au_id
=
ta.au_id
INNER
JOIN
titles
t
ON
t.title_id
=
ta.title_id
INNER
JOIN
publishers
p
ON
t.pub_id
=
p.pub_id
WHERE
au_fname
LIKE
@firstname
AND
au_lname
LIKE
@lastname
GO
　　au_info2
存儲(chǔ)過(guò)程可以用多種組合執(zhí)行。下面只列出了部分組合：
EXECUTE
au_info2
--
Or
EXECUTE
au_info2
'Wh%'
--
Or
EXECUTE
au_info2
@firstname
=
'A%'
--
Or
EXECUTE
au_info2
'[CK]ars[OE]n'
--
Or
EXECUTE
au_info2
'Hunter',
'Sheryl'
--
Or
EXECUTE
au_info2
'H%',
'S%'

oracle存儲(chǔ)過(guò)程實(shí)例 為什么 要INTO

你說(shuō)的是procedure或者function中的lect 列 into 變量 from 。。。這種結(jié)構(gòu)吧。
這就是plsql的語(yǔ)法規(guī)則。不難理解，你在程序中從一張表中獲取了某列或某些列的值，要想使用肯定要賦值給變量的，那么lect 。。into 就是為了賦值而定義的一種語(yǔ)法。

存儲(chǔ)過(guò)程和觸發(fā)器用在哪些方面?舉幾個(gè)實(shí)例?

存儲(chǔ)過(guò)程就相當(dāng)于子程序，比如批量處理SQL語(yǔ)句執(zhí)行或執(zhí)行SQL語(yǔ)言的編程。我用存儲(chǔ)過(guò)程用的最多的地方就是數(shù)據(jù)庫(kù)信息的分頁(yè)查詢。。

觸發(fā)器的例子除了銀行，最最常見(jiàn)的就是論壇啦。你回了別人一個(gè)帖子，數(shù)據(jù)庫(kù)里就會(huì)增加這個(gè)帖子的內(nèi)容，同時(shí)更新樓主的回帖數(shù)，同時(shí)還增加你的發(fā)帖數(shù)

如何編寫存儲(chǔ)過(guò)程

設(shè)計(jì)存儲(chǔ)過(guò)程
幾乎任何可寫成批處理的 Transact-SQL 代碼都可用于創(chuàng)建存儲(chǔ)過(guò)程。

存儲(chǔ)過(guò)程的設(shè)計(jì)規(guī)則
存儲(chǔ)過(guò)程的設(shè)計(jì)規(guī)則包括：

CREATE PROCEDURE 定義本身可包括除下列 CREATE 語(yǔ)句以外的任何數(shù)量和類型的 SQL 語(yǔ)句，存儲(chǔ)過(guò)程中的任意地方都不能使用下列語(yǔ)句： CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE

可在存儲(chǔ)過(guò)程中創(chuàng)建其它數(shù)據(jù)庫(kù)對(duì)象。可以引用在同一存儲(chǔ)過(guò)程中創(chuàng)建的對(duì)象，前提是在創(chuàng)建對(duì)象后再引用對(duì)象。

可以在存儲(chǔ)過(guò)程內(nèi)引用臨時(shí)表。

如果在存儲(chǔ)過(guò)程內(nèi)創(chuàng)建本地臨時(shí)表，則該臨時(shí)表僅為該存儲(chǔ)過(guò)程而存在；退出該存儲(chǔ)過(guò)程后，臨時(shí)表即會(huì)消失。

如果執(zhí)行調(diào)用其它存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程，那么被調(diào)用存儲(chǔ)過(guò)程可以訪問(wèn)由第一個(gè)存儲(chǔ)過(guò)程創(chuàng)建的、包括臨時(shí)表在內(nèi)的所有對(duì)象。

如果執(zhí)行在遠(yuǎn)程 Microsoft® SQL Server™ 2000 實(shí)例上進(jìn)行更改的遠(yuǎn)程存儲(chǔ)過(guò)程，則不能回滾這些更改。遠(yuǎn)程存儲(chǔ)過(guò)程不參與事務(wù)處理。

存儲(chǔ)過(guò)程中參數(shù)的最大數(shù)目為 2100。

存儲(chǔ)過(guò)程中局部變量的最大數(shù)目?jī)H受可用內(nèi)存的限制。

根據(jù)可用內(nèi)存的不同，存儲(chǔ)過(guò)程的最大大小可達(dá) 128 MB。
有關(guān)創(chuàng)建存儲(chǔ)過(guò)程的規(guī)則的更多信息，請(qǐng)參見(jiàn) CREATE PROCEDURE。

限定存儲(chǔ)過(guò)程內(nèi)的名稱
在存儲(chǔ)過(guò)程內(nèi)部，如果用于諸如 SELECT 或 INSERT 這樣的語(yǔ)句的對(duì)象名沒(méi)有限定用戶，那么用戶將默認(rèn)為該存儲(chǔ)過(guò)程的所有者。在存儲(chǔ)過(guò)程內(nèi)部，如果創(chuàng)建存儲(chǔ)過(guò)程的用戶沒(méi)有限定 SELECT、INSERT、UPDATE 或 DELETE 語(yǔ)句中引用的表名，那么通過(guò)該存儲(chǔ)過(guò)程對(duì)這些表進(jìn)行的訪問(wèn)將默認(rèn)地受到該過(guò)程的創(chuàng)建者權(quán)限的限制。

如果有其他用戶要使用存儲(chǔ)過(guò)程，則用于語(yǔ)句 ALTER TABLE、CREATE TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 和 DBCC 的對(duì)象名必須用該對(duì)象所有者的名稱限定。例如，Mary 擁有表 marytab，如果她希望其他用戶能夠執(zhí)行使用該表的存儲(chǔ)過(guò)程，必須在該表用于上述某一條語(yǔ)句時(shí)對(duì)其表名進(jìn)行限定。

此規(guī)則是必需的，因?yàn)檫\(yùn)行存儲(chǔ)過(guò)程時(shí)將解析對(duì)象的名稱。如果未限定 marytab，而 John 試圖執(zhí)行該過(guò)程，SQL Server 將查找 John 所擁有的名為 marytab 的表。

加密過(guò)程定義
如果要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程，并且希望確保其他用戶無(wú)法查看該過(guò)程的定義，那么可以使用 WITH ENCRYPTION 子句。這樣，過(guò)程定義將以不可讀的形式存儲(chǔ)。

存儲(chǔ)過(guò)程一旦加密其定義即無(wú)法解密，任何人（包括存儲(chǔ)過(guò)程的所有者或系統(tǒng)管理員）都將無(wú)法查看存儲(chǔ)過(guò)程定義。

SET 語(yǔ)句選項(xiàng)
當(dāng) ODBC 應(yīng)用程序與 SQL Server 連接時(shí)，服務(wù)器將自動(dòng)設(shè)置會(huì)話的下列選項(xiàng)：

SET QUOTED_IDENTIFIER ON

SET TEXTSIZE 2147483647

SET ANSI_DEFAULTS ON

SET CURSOR_CLOSE_ON_COMMIT OFF

SET IMPLICIT_TRANSACTIONS OFF
這些設(shè)置將提高 ODBC 應(yīng)用程序的可移植性。由于基于 DB-Library 的應(yīng)用程序通常不設(shè)置這些選項(xiàng)，所以應(yīng)在上述所列 SET 選項(xiàng)打開(kāi)和關(guān)閉的情況下都對(duì)存儲(chǔ)過(guò)程進(jìn)行測(cè)試。這樣可確保存儲(chǔ)過(guò)程始終能正確工作，而不管特定的連接在喚醒調(diào)用該存儲(chǔ)過(guò)程時(shí)可能設(shè)置的選項(xiàng)。需要特別設(shè)置其中一個(gè)選項(xiàng)的存儲(chǔ)過(guò)程，應(yīng)在開(kāi)始該存儲(chǔ)過(guò)程時(shí)發(fā)出一條 SET 語(yǔ)句。此 SET 語(yǔ)句將只對(duì)該存儲(chǔ)過(guò)程的執(zhí)行保持有效，當(dāng)該存儲(chǔ)過(guò)程結(jié)束時(shí)，將恢復(fù)原設(shè)置。

示例
A. 創(chuàng)建使用參數(shù)的存儲(chǔ)過(guò)程
下例創(chuàng)建一個(gè)在 pubs 數(shù)據(jù)庫(kù)中很有用的存儲(chǔ)過(guò)程。給出一個(gè)作者的姓和名，該存儲(chǔ)過(guò)程將顯示該作者的每本書(shū)的標(biāo)題和出版商。

CREATE PROC au_info @lastname varchar(40), @firstname varchar(20)
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO

將出現(xiàn)一條說(shuō)明該命令未返回任何數(shù)據(jù)也未返回任何行的消息，這表示已創(chuàng)建該存儲(chǔ)過(guò)程。

現(xiàn)在執(zhí)行 au_info 存儲(chǔ)過(guò)程：

EXECUTE au_info Ringer, Anne
GO

下面是結(jié)果集：

au_lname au_fname title pub_name
--------- --------- --------------------- ----------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Moon Books

(2 row(s) affected)

B. 創(chuàng)建使用參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程
下例創(chuàng)建一個(gè)存儲(chǔ)過(guò)程 pub_info2，該存儲(chǔ)過(guò)程顯示作為參數(shù)給出的出版商所出版的某本書(shū)的作者姓名。如果未提供出版商的名稱，該存儲(chǔ)過(guò)程將顯示由 Algodata Infosystems 出版的書(shū)籍的作者。

CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN titles t ON ta.title_id = t.title_id
JOIN publishers p ON t.pub_id = p.pub_id
WHERE @pubname = p.pub_name

執(zhí)行未指定參數(shù)的 pub_info2：

EXECUTE pub_info2
GO

下面是結(jié)果集：

au_lname au_fname pub_name
---------------- ---------------- --------------------
Green Marjorie Algodata Infosystems
Bennet Abraham Algodata Infosystems
O'Leary Michael Algodata Infosystems
MacFeather Stearns Algodata Infosystems
Straight Dean Algodata Infosystems
Carson Cheryl Algodata Infosystems
Dull Ann Algodata Infosystems
Hunter Sheryl Algodata Infosystems
Locksley Charlene Algodata Infosystems

(9 row(s) affected)

C. 執(zhí)行用顯式值替代參數(shù)默認(rèn)值的存儲(chǔ)過(guò)程
在下例中，存儲(chǔ)過(guò)程 showind2 的 @table 參數(shù)默認(rèn)值是 titles。

CREATE PROC showind2 @table varchar(30) = 'titles'
AS
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table

列標(biāo)題（例如，TABLE_NAME）可使結(jié)果更具可讀性。下面是該存儲(chǔ)過(guò)程顯示的 authors 表的情況：

EXECUTE showind2 authors
GO

TABLE_NAME INDEX_NAME INDEX_ID
---------- ---------- ----------
authors UPKCL_auidind 1
authors aunmind 2

(2 row(s) affected)

如果用戶未提供值，則 SQL Server 將使用默認(rèn)表 titles：

EXECUTE showind2
GO

下面是結(jié)果集：

TABLE_NAME INDEX_NAME INDEX_ID
---------- ---------- ----------
titles UPKCL_titleidind 1
titles titleind 2

(2 row(s) affected)

D. 使用參數(shù)默認(rèn)值 NULL 創(chuàng)建存儲(chǔ)過(guò)程
參數(shù)默認(rèn)值可以是 NULL 值。在這種情況下，如果未提供參數(shù)，則 SQL Server 將根據(jù)存儲(chǔ)過(guò)程的其它語(yǔ)句執(zhí)行存儲(chǔ)過(guò)程。不會(huì)顯示錯(cuò)誤信息。

過(guò)程定義還可指定當(dāng)不給出參數(shù)時(shí)要采取的其它某種措施。例如：

CREATE PROC showind3 @table varchar(30) = NULL
AS IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table

E. 使用包含通配符的參數(shù)默認(rèn)值創(chuàng)建存儲(chǔ)過(guò)程
如果存儲(chǔ)過(guò)程將參數(shù)用于 LIKE 關(guān)鍵字，那么默認(rèn)值可包括通配符（%、_、[] 和 [^]）。例如，可將 showind 修改為當(dāng)不提供參數(shù)時(shí)顯示有關(guān)系統(tǒng)表的信息：

CREATE PROC showind4 @table varchar(30) = 'sys%'
AS SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name LIKE @table

在存儲(chǔ)過(guò)程 au_info 的下列變化形式中，兩個(gè)參數(shù)都有帶通配符的默認(rèn)值：

CREATE PROC au_info2 @lastname varchar(30) = 'D%',
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname

如果執(zhí)行 au_info2 時(shí)不指定參數(shù)，將顯示姓以字母 D 開(kāi)頭的所有作者：

EXECUTE au_info2
GO

下面是結(jié)果集：

au_lname au_fname title pub_name
-------- -------- --------------------- -------------------
Dull Ann Secrets of Silicon Val Algodata Infosystems
del Castillo Innes Silicon Val Gastrono Binnet & Hardley
DeFrance Michel The Gourmet Microwave Binnet & Hardley

(3 row(s) affected)

下例在兩個(gè)參數(shù)的默認(rèn)值已定義的情況下，省略了第二個(gè)參數(shù)，因此可找到姓為 Ringer 的所有作者的書(shū)和出版商：

EXECUTE au_info2 Ringer
GO

au_lname au_fname title pub_name
--------- --------- ---------------------- ----------------
Ringer Anne The Gourmet Microwave Binnet & Hardley
Ringer Anne Is Anger the Enemy? New Moon Books
Ringer Albert Is Anger the Enemy? New Moon Books
Ringer Albert Life Without Fear New Moon Books

(4 row(s) affected)

Mysql存儲(chǔ)過(guò)程中游標(biāo)的用法實(shí)例

本文實(shí)例講述了Mysql存儲(chǔ)過(guò)程中游標(biāo)的用法。分享給大家供大家參考。具體如下：
1.
批量插入商戶路由關(guān)聯(lián)數(shù)據(jù)：
DELIMITER
$$
USE
`mmm_mac`$$
DROP
PROCEDURE
IF
EXISTS
`批量插入商戶路由關(guān)聯(lián)數(shù)據(jù)`$$
CREATE
DEFINER=`root`@`%`
PROCEDURE
`批量插入商戶路由關(guān)聯(lián)數(shù)據(jù)`()
BEGIN
DECLARE
v_partner_no
VARCHAR(32);
DECLARE
v_partner_id
INT(11);
DECLARE
v_sc_pid
INT(11);
DECLARE
v_mac_no
VARCHAR(32);
DECLARE
v_mac_addr
VARCHAR(32);
DECLARE
n_mac_no
BIGINT;
DECLARE
n_mac_addr
BIGINT;
DECLARE
n_mac_addr_str
VARCHAR(32);
DECLARE
done
INT;
#取得商戶數(shù)據(jù)
DECLARE
cur_partnerlist
CURSOR
FOR
SELECT
comp_id,
partner_no,
sc_pid
FROM
mmm_partner.anl_partner;
SET
n_mac_no
=
100000000;
SET
n_mac_addr
=
1000000000;
OPEN
cur_partnerlist;
REPEAT
FETCH
cur_partnerlist
INTO
v_partner_id,v_partner_no,v_sc_pid;
SET
v_mac_no
=
CONCAT('MAC',v_sc_pid,n_mac_no);
SET
n_mac_addr_str
=
CONCAT(SUBSTR(n_mac_addr,1,2),':',SUBSTR(n_mac_addr,3,2),':',SUBSTR(n_mac_addr,5,2),':',SUBSTR(n_mac_addr,7,2),':',SUBSTR(n_mac_addr,9,2));
SET
v_mac_addr
=
CONCAT('CC:',n_mac_addr_str);
SET
n_mac_no
=
n_mac_no
+
1;
SET
n_mac_addr
=
n_mac_addr
+
1;
#向t_machine_sc_config表中插入商戶關(guān)聯(lián)路由的數(shù)據(jù)
#inrt
into
t_machine_sc_config(mac_no,
partner_no,
partner_id,
sc_pid,
mac_addr,
comp_id,
is_lock)
values('MAC2016000000001','44060430603381',1,4403,'C8:87:18:AB:79:66',1,1);
INSERT
INTO
t_machine_sc_config(mac_no,
partner_no,
partner_id,
sc_pid,
mac_addr,
comp_id,
is_lock)
VALUES(v_mac_no,v_partner_no,v_partner_id,v_sc_pid,v_mac_addr,1,1);
UNTIL
0
END
REPEAT;
CLOSE
cur_partnerlist;
END$$
DELIMITER
;
2.
更新商戶表：
DELIMITER
$$
USE
`mmm_partner`$$
DROP
PROCEDURE
IF
EXISTS
`更新商戶表`$$
CREATE
DEFINER=`root`@`%`
PROCEDURE
`更新商戶表`()
BEGIN
DECLARE
v_partner_no
VARCHAR(32);
DECLARE
vpartner_no
VARCHAR(32);
DECLARE
v_partner_id
VARCHAR(32);
DECLARE
n
BIGINT;
DECLARE
partnerid_list
CURSOR
FOR
SELECT
comp_id
FROM
100msh_partner.anl_partner
WHERE
TRIM(partner_no)
=
'';
SET
vpartner_no
=
'2015415parno';
SET
n
=
10000000;
OPEN
partnerid_list;
REPEAT
FETCH
partnerid_list
INTO
v_partner_id;
SET
v_partner_no
=
CONCAT(vpartner_no,n);
SET
n
=
n
+
1;
UPDATE
mmm_partner.anl_partner
SET
partner_no
=
v_partner_no
WHERE
comp_id
=
v_partner_id;
UNTIL
0
END
REPEAT;
CLOSE
partnerid_list;
END$$
DELIMITER
;
希望本文所述對(duì)大家的mysql數(shù)據(jù)庫(kù)程序設(shè)計(jì)有所幫助。