程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 存儲過程之一—建立簡單的存儲過程，存儲過程建立

存儲過程之一—建立簡單的存儲過程，存儲過程建立

編輯：MySQL綜合教程

存儲過程之一—建立簡單的存儲過程，存儲過程建立

一、存儲過程

　　存儲過程（Stored Procedure）是在大型數據庫系統中，一組為了完成特定功能的SQL語句集，經編譯後存儲在數據庫中，用戶
通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。而我們常用的操作數據庫語言SQL語句在執行的時
候需要要先編譯，然後執行，所以執行的效率沒有存儲過程高。

　　存儲過程優點如下：

重復使用。存儲過程可以重復使用，從而可以減少數據庫開發人員的工作量。
提高性能。存儲過程在創建的時候在進行了編譯，將來使用的時候不再重新翻譯。一般的SQL語句每執行一次就需要編譯一次，所以使用存儲過程提高了效率。
減少網絡流量。存儲過程位於服務器上，調用的時候只需要傳遞存儲過程的名稱以及參數就可以了，因此降低了網絡傳輸的數據量。
安全性。參數化的存儲過程可以防止SQL注入式攻擊，而且可以將Grant、Deny以及Revoke權限應用於存儲過程。

　　存儲過程簡單語法：

CREATE PROCEDURE 存儲過程名稱(
　　輸入輸出類型變量名稱類型,

　　　　輸入輸出類型變量名稱類型

)
BEGIN
　　-- 聲明，語句要完成的操作，增刪改查。。。
END

二、實例

　　例子中的存儲過程均使用mysql作為例子。
　　表結構如下：

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

　　1、只帶IN（輸入參數）的存儲過程

　　表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值.

DROP PROCEDURE IF EXISTS proc_person_findById;

-- 創建存儲過程
CREATE PROCEDURE proc_person_findById(
    in n int
)
BEGIN
     SELECT * FROM person where id=n;
END

-- 定義變量
SET @n=2;
-- 調用存儲過程
CALL proc_person_findById(@n);

調用結果如下：

　　2、只帶OUT（輸出參數）的存儲過程

　　該值可在存儲過程內部被改變，並可返回。

DROP PROCEDURE IF EXISTS proc_person_getCount

-- 創建存儲過程
CREATE PROCEDURE proc_person_getCount(
    out n int(11)
)
BEGIN 
     SELECT COUNT(*) INTO n FROM person ;
END 

-- 調用存儲過程
CALL proc_person_getCount(@n);
SELECT @n as '總數';

調用結果如下：

　　3、帶IN（輸入參數）和OUT（輸出參數）的

　　調用時指定，並且可被改變和返回

DROP PROCEDURE IF EXISTS proc_person_findInfoById; 

-- 創建存儲過程
CREATE PROCEDURE proc_person_findInfoById(
    IN n INT(11), 
    OUT pusername VARCHAR(255), 
    OUT page INT(11)
)
BEGIN 
     SELECT username, age INTO pusername, page FROM person WHERE id=n;
END

-- 定義變量
SET @id=2;
-- 調用存儲過程
CALL proc_person_findInfoById(@id,@username, @age);
SELECT @username as '用戶名', @age '年齡';

調用結果如下：

　　4、帶INOUT（輸入輸出）參數的存儲過程

-- 輸入輸出
DROP PROCEDURE IF EXISTS proc_person_get_age;

-- 創建存儲過程
CREATE PROCEDURE proc_person_get_age(
    INOUT n INT(11)
)
BEGIN 
     SELECT age INTO N FROM person WHERE id=n;
END

SET @id = 1;
CALL proc_person_get_age(@id); 
SELECT @id;

調用結果如下：

　　5、關於輸入輸出參數

IN為輸入，定義參數時，可以不加，不加則默認為輸入參數。
OUT為輸出，定義參數時，必須加上。
INOUT為輸入和輸出，必須加上。表示該參數可以輸入也可在處理後存放結果進行輸出。

怎在sql創建一條插入數據的存儲過程

create or replace PROCEDURE test_insert -定義名稱
(in_insert in number
in_insert2 in number, -定義輸入值
on_code out number) -定義輸出值
is
begin -開始實施
insert into table_name (列名1,列名2，列名3)
values (in_insert,in_insert2) --操作語句執行插入表 end
test_insert ;

SQL Server中有創建存儲過程，誰可以簡單介紹一下這個存儲過程？

存儲過程就是作為可執行對象存放在數據庫中的一個或多個SQL命令。
定義總是很抽象。存儲過程其實就是能完成一定操作的一組SQL語句，只不過這組語句是放在數據庫中的(這裡我們只談SQL Server)。如果我們通過創建存儲過程以及在ASP中調用存儲過程，就可以避免將SQL語句同ASP代碼混雜在一起。這樣做的好處至少有三個：
第一、大大提高效率。存儲過程本身的執行速度非常快，而且，調用存儲過程可以大大減少同數據庫的交互次數。
第二、提高安全性。假如將SQL語句混合在ASP代碼中，一旦代碼失密，同時也就意味著庫結構失密。
第三、有利於SQL語句的重用。

在ASP中，一般通過command對象調用存儲過程，根據不同情況，本文也介紹其它調用方法。為了方便說明，根據存儲過程的輸入輸出，作以下簡單分類：
1. 只返回單一記錄集的存儲過程
假設有以下存儲過程(本文的目的不在於講述T-SQL語法，所以存儲過程只給出代碼，不作說明)：

/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
select * from dbo.[userinfo]
end
go

以上存儲過程取得userinfo表中的所有記錄，返回一個記錄集。通過command對象調用該存儲過程的ASP代碼如下:

'**通過Command對象調用存儲過程**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr是數據庫連接字串
MyComm.CommandText = "getUserList" '指定存儲過程名
MyComm.CommandType = 4 '表明這是一個存儲過程
MyComm.Prepared = true '要求將SQL命令先行編譯
Set MyRst = MyComm.Execute
Set MyComm = Nothing

存儲過程取得的記錄集賦給MyRst，接下來，可以對MyRst進行操作。
在以上代碼中，CommandType屬性表明請求的類型，取值及說明如下：
-1 表明CommandText參數的類型無法確定

1 表明CommandText是一般的命令類型
2 表明CommandText參數是一個存在的表名稱
4 表明CommandText參數是一個存儲過程的名稱

還可以通過Connection對象或Recordset對象調用存儲過程，方法分別如下：

'**通過Connection對象調用存儲過程**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
......余下全文>>