轉自：http://www.java-cn.com/technology/tech/4310.html

本文闡述了怎么使用DBMS存儲過程。我闡述了使用存儲過程的基本的和高級特性，比如返回ResultSet。本文假設你對DBMS和JDBC已經非常熟悉，也假設你能夠毫無障礙地閱讀其它語言寫成的代碼（即不是Java的語言），但是，并不要求你有任何存儲過程的編程經歷。

存儲過程是指保存在數據庫并在數據庫端執行的程序。你可以使用特殊的語法在Java類中調用存儲過程。在調用時，存儲過程的名稱及指定的參數通過JDBC連接發送給DBMS，執行存儲過程并通過連接（如果有）返回結果。
使用存儲過程擁有和使用基于EJB或CORBA這樣的應用服務器一樣的好處。區別是存儲過程可以從很多流行的DBMS中免費使用，而應用服務器大都非常昂貴。這并不只是許可證費用的問題。使用應用服務器所需要花費的管理、編寫代碼的費用，以及客戶程序所增加的復雜性，都可以通過DBMS中的存儲過程所整個地替代。

你可以使用Java，Python，Perl或C編寫存儲過程，但是通常使用你的DBMS所指定的特定語言。Oracle使用PL/SQL，PostgreSQL使用pl/pgsql，DB2使用Procedural SQL。這些語言都非常相似。在它們之間移植存儲過程并不比在Sun的EJB規范不同實現版本之間移植Session Bean困難。并且，存儲過程是為嵌入SQL所設計，這使得它們比Java或C等語言更加友好地方式表達數據庫的機制。

因為存儲過程運行在DBMS自身，這可以幫助減少應用程序中的等待時間。不是在Java代碼中執行4個或5個SQL語句，而只需要在服務器端執行1個存儲過程。網絡上的數據往返次數的減少可以戲劇性地優化性能。

使用存儲過程

簡單的老的JDBC通過CallableStatement類支持存儲過程的調用。該類實際上是PreparedStatement的一個子類。假設我們有一個poets數據庫。數據庫中有一個設置詩人逝世年齡的存儲過程。下面是對老酒鬼Dylan Thomas（old soak Dylan Thomas，不指定是否有關典故、文化，請批評指正。譯注）進行調用的詳細代碼：

try{ 

int age = 39; 



String poetName = "dylan thomas"; 



CallableStatement proc = connection.prepareCall("{ call set_death_age(?, ?) }"); 



proc.setString(1, poetName); 



proc.setInt(2, age); 



cs.execute(); 



}catch (SQLException e){ // ....} 

傳給prepareCall方法的字串是存儲過程調用的書寫規范。它指定了存儲過程的名稱，？代表了你需要指定的參數。
和JDBC集成是存儲過程的一個很大的便利：為了從應用中調用存儲過程，不需要存根（stub）類或者配置文件，除了你的DBMS的JDBC驅動程序外什么也不需要。

當這段代碼執行時，數據庫的存儲過程就被調用。我們沒有去獲取結果，因為該存儲過程并不返回結果。執行成功或失敗將通過例外得知。失敗可能意味著調用存儲過程時的失敗（比如提供的一個參數的類型不正確），或者一個應用程序的失敗（比如拋出一個例外指示在poets數據庫中并不存在“Dylan Thomas”）

結合SQL操作與存儲過程

映射Java對象到SQL表中的行相當簡單，但是通常需要執行幾個SQL語句；可能是一個SELECT查找ID，然后一個INSERT插入指定ID的數據。在高度規格化（符合更高的范式，譯注）的數據庫模式中，可能需要多個表的更新，因此需要更多的語句。Java代碼會很快地膨脹，每一個語句的網絡開銷也迅速增加。
將這些SQL語句轉移到一個存儲過程中將大大簡化代碼，僅涉及一次網絡調用。所有關聯的SQL操作都可以在數據庫內部發生。并且，存儲過程語言，例如PL/SQL，允許使用SQL語法，這比Java代碼更加自然。下面是我們早期的存儲過程，使用Oracle的PL/SQL語言編寫：

create procedure set_death_age(poet VARCHAR2, poet_age NUMBER) 



poet_id NUMBER; 



begin SELECT id INTO poet_id FROM poets WHERE name = poet; 



INSERT INTO deaths (mort_id, age) VALUES (poet_id, poet_age); 



end set_death_age; 

很獨特？不。我打賭你一定期待看到一個poets表上的UPDATE。這也暗示了使用存儲過程實現是多么容易的一件事情。set_death_age幾乎可以肯定是一個很爛的實現。我們應該在poets表中添加一列來存儲逝世年齡。Java代碼中并不關心數據庫模式是怎么實現的，因為它僅調用存儲過程。我們以后可以改變數據庫模式以提高性能，但是我們不必修改我們代碼。

下面是調用上面存儲過程的Java代碼：

public static void setDeathAge(Poet dyingBard, int age) throws SQLException{ 



Connection con = null; 



CallableStatement proc = null; 



try { 



con = connectionPool.getConnection(); 



proc = con.prepareCall("{ call set_death_age(?, ?) }"); 



proc.setString(1, dyingBard.getName()); 



proc.setInt(2, age); 



proc.execute(); 



} 



finally { 



try { proc.close(); } 



catch (SQLException e) {} 



con.close(); 



} 



} 

為了確保可維護性，建議使?孟裾舛?庋??tatic方法。這也使得調用存儲過程的代碼集中在一個簡單的模版代碼中。如果你用到許多存儲過程，就會發現僅需要拷貝、粘貼就可以創建新的方法。因為代碼的模版化，甚至也可以通過腳本自動生產調用存儲過程的代碼。

Functions

存儲過程可以有返回值，所以CallableStatement類有類似getResultSet這樣的方法來獲取返回值。當存儲過程返回一個值時，你必須使用registerOutParameter方法告訴JDBC驅動器該值的SQL類型是什么。你也必須調整存儲過程調用來指示該過程返回一個值。

下面接著上面的例子。這次我們查詢Dylan Thomas逝世時的年齡。這次的存儲過程使用PostgreSQL的pl/pgsql：

create function snuffed_it_when (VARCHAR) returns integer 'declare 



poet_id NUMBER; 



poet_age NUMBER; 



begin 



--first get the id associated with the poet. 



SELECT id INTO poet_id FROM poets WHERE name = $1; 



--get and return the age. 



SELECT age INTO poet_age FROM deaths WHERE mort_id = poet_id; 



return age; 



end;' language 'pl/pgsql'; 

另外，注意pl/pgsql參數名通過Unix和DOS腳本的$n語法引用。同時，也注意嵌入的注釋，這是和Java代碼相比的另一個優越性。在Java中寫這樣的注釋當然是可以的，但是看起來很凌亂，并且和SQL語句脫節，必須嵌入到Java String中。

下面是調用這個存儲過程的Java代碼：

connection.setAutoCommit(false); 



CallableStatement proc = connection.prepareCall("{ ? = call snuffed_it_when(?) }"); 



proc.registerOutParameter(1, Types.INTEGER); 



proc.setString(2, poetName); 



cs.execute(); 



int age = proc.getInt(2); 

如果指定了錯誤的返回值類型會怎樣？那么，當調用存儲過程時將拋出一個RuntimeException，正如你在ResultSet操作中使用了一個錯誤的類型所碰到的一樣。

復雜的返回值

關于存儲過程的知識，很多人好像就熟悉我們所討論的這些。如果這是存儲過程的全部功能，那么存儲過程就不是其它遠程執行機制的替換方案了。存儲過程的功能比這強大得多。
當你執行一個SQL查詢時，DBMS創建一個叫做cursor（游標）的數據庫對象，用于在返回結果中迭代每一行。ResultSet是當前時間點的游標的一個表示。這就是為什么沒有緩存或者特定數據庫的支持，你只能在ResultSet中向前移動。

某些DBMS允許從存儲過程中返回游標的一個引用。JDBC并不支持這個功能，但是Oracle、PostgreSQL和DB2的JDBC驅動器都支持在ResultSet上打開到游標的指針（pointer）。

設想列出所有沒有活到退休年齡的詩人，下面是完成這個功能的存儲過程，返回一個打開的游標，同樣也使用PostgreSQL的pl/pgsql語言：

create procedure list_early_deaths () return refcursor as 'declare 



toesup refcursor; 



begin 



open toesup for SELECT poets.name, deaths.age FROM poets, deaths 

-- all entries in deaths are for poets. -- but the table might become generic. 



WHERE poets.id = deaths.mort_id AND deaths.age < 60; 



return toesup; 



end;' language 'plpgsql'; 

下面是調用該存儲過程的Java方法，將結果輸出到PrintWriter：

PrintWriter: 



static void sendEarlyDeaths(PrintWriter out){ 



Connection con = null; 



CallableStatement toesUp = null; 



try { 



con = ConnectionPool.getConnection(); 



// PostgreSQL needs a transaction to do this... con. 



setAutoCommit(false); // Setup the call. 



CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }"); 



toesUp.registerOutParameter(1, Types.OTHER); 



toesUp.execute(); 



ResultSet rs = (ResultSet) toesUp.getObject(1); 



while (rs.next()) { 



String name = rs.getString(1); 



int age = rs.getInt(2); 



out.println(name + " was " + age + " years old."); 



} 



rs.close(); 



} 



catch (SQLException e) { // We should protect these calls. toesUp.close(); con.close(); 



} 



} 

因為JDBC并不直接支持從存儲過程中返回游標，我們使用Types.OTHER來指示存儲過程的返回類型，然后調用getObject()方法并對返回值進行強制類型轉換。

這個調用存儲過程的Java方法是mapping的一個好例子。Mapping是對一個集上的操作進行抽象的方法。不是在這個過程上返回一個集，我們可以把操作傳送進去執行。本例中，操作就是把ResultSet打印到一個輸出流。這是一個值得舉例的很常用的例子，下面是調用同一個存儲過程的另外一個方法實現：

public class ProcessPoetDeaths{ 



public abstract void sendDeath(String name, int age); 



} 



static void mapEarlyDeaths(ProcessPoetDeaths mapper){ 



Connection con = null; 



CallableStatement toesUp = null; 



try { 



con = ConnectionPool.getConnection(); 



con.setAutoCommit(false); 



CallableStatement toesUp = connection.prepareCall("{ ? = call list_early_deaths () }"); 



toesUp.registerOutParameter(1, Types.OTHER); 



toesUp.execute(); 



ResultSet rs = (ResultSet) toesUp.getObject(1); 



while (rs.next()) { 



String name = rs.getString(1); 



int age = rs.getInt(2); 



mapper.sendDeath(name, age); 



} 



rs.close(); 



} catch (SQLException e) { // We should protect these calls. toesUp.close(); 



con.close(); 



} 



} 

這允許在ResultSet數據上執行任意的處理，而不需要改變或者復制獲取ResultSet的方法：

static void sendEarlyDeaths(final PrintWriter out){ 



ProcessPoetDeaths myMapper = new ProcessPoetDeaths() { 



public void sendDeath(String name, int age) { 



out.println(name + " was " + age + " years old."); 



} 



}; 



mapEarlyDeaths(myMapper); 



} 

這個方法使用ProcessPoetDeaths的一個匿名實例調用mapEarlyDeaths。該實例擁有sendDeath方法的一個實現，和我們上面的例子一樣的方式把結果寫入到輸出流。當然，這個技巧并不是存儲過程特有的，但是和存儲過程中返回的ResultSet結合使用，是一個非常強大的工具。

結論

存儲過程可以幫助你在代碼中分離邏輯，這基本上總是有益的。這個分離的好處有：
?6?1 快速創建應用，使用和應用一起改變和改善的數據庫模式。
?6?1 數據庫模式可以在以后改變而不影響Java對象，當我們完成應用后，可以重新設計更好的模式。
?6?1 存儲過程通過更好的SQL嵌入使得復雜的SQL更容易理解。
?6?1 編寫存儲過程比在Java中編寫嵌入的SQL擁有更好的工具－－大部分編輯器都提供語法高亮！
?6?1 存儲過程可以在任何SQL命令行中測試，這使得調試更加容易。

并不是所有的數據庫都支持存儲過程，但是存在許多很棒的實現，包括免費/開源的和非免費的，所以移植并不是一個問題。Oracle、PostgreSQL和DB2都有類似的存儲過程語言，并且有在線的社區很好地支持。
存儲過程工具很多，有像TOAD或TORA這樣的編輯器、調試器和IDE，提供了編寫、維護PL/SQL或pl/pgsql的強大的環境。

存儲過程確實增加了你的代碼的開銷，但是它們和大多數的應用服務器相比，開銷小得多。

create proc p_1
as
select * from cell
go

create proc p_2
    @name varchar(40),
    @remark varchar(40)
as
insert into cell(name,remark) values(@name,@remark)
go

import java.sql.*;

/**
 * jdbc中調用存儲過程
 * @author jiafang83
 *
 */
public class Procedure2 {

    public static void main(String[] args) throws Exception {
        // TODO Auto-generated method stub
        String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=fish";
        String user = "bm";
        String pwd = "bm";
        Connection conn = null;
        CallableStatement proc = null;//執行sql存儲過程的接口
        ResultSet rs = null;

//        調用存儲過程p_1:從Cell表中取出數據
        try{
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager.getConnection(url,user,pwd);
            proc = conn.prepareCall("{? = call p_1 ()}");
            proc.registerOutParameter(1, Types.REAL);
            rs = proc.executeQuery();//取得結果集
            while(rs.next()){
                System.out.println(rs.getString("name"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(rs!=null) rs.close();
            if(proc!=null)proc.close();
            if(conn!=null)conn.close();
        }
    }

}

import java.sql.*;

/**
 * jdbc中調用存儲過程
 * @author jiafang83
 *
 */
public class Procedure {

    public static void main(String[] args) throws Exception {
        // TODO Auto-generated method stub
        String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=fish";
        String user = "bm";
        String pwd = "bm";
        Connection conn = null;
        CallableStatement proc = null;//執行sql存儲過程的接口
       
//        調用存儲過程p_2:向Cell表中插入數據
        try{
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager.getConnection(url,user,pwd);
            proc = conn.prepareCall("{call p_2 (?,?)}");
            proc.setString(1, "測試");
            proc.setString(2, "測試");
            proc.execute();
            System.out.println("成功調用存儲過程，插入數據！");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            if(proc!=null)proc.close();
            if(conn!=null)conn.close();
        }
    }

    1、master數據庫
    master 數據庫記錄SQL Server系統的所有系統級別信息。它記錄所有的登錄帳戶和系統配置設置。它還記錄所有其它的數據庫，其中包括數據庫文件的位置。master數據庫記錄SQL Server的初始化信息，它始終有一個可用的最新master數據庫備份。
   
    2、model數據庫
    model 數據庫用作在系統上創建的所有數據庫的模板。當發出Create DATABASE語句時，新數據庫的第一部分通過復制model數據庫中的內容創建，剩余部分由空頁填充。由于SQL Server每次啟動時都要創建tempdb數據庫，model數據庫必須一直存在于SQL Server系統中。
   
    3、msdb數據庫
    msdb數據庫供SQL Server代理程序調度警報和作業以及記錄操作員時使用。

    4、Northwind數據庫
    Northwind數據庫包含一個名為Northwind Traders的虛構公司的銷售數據，該公司從事世界各地的特產食品進出口貿易。

    5、pubs數據庫
    pubs數據庫以一個圖書出版公司為模型，用于演示SQL Server數據庫中可用的許多選項。該數據庫及其中的表經常在文檔內容所介紹的示例中使用。

    6、tempdb數據庫
    tempdb 數據庫保存所有的臨時表和臨時存儲過程。它還滿足任何其它的臨時存儲要求，例如存儲SQL Server生成的工作表。tempdb數據庫是全局資源，所有連接到系統的用戶的臨時表和存儲過程都存儲在該數據庫中。tempdb數據庫在SQL Server每次啟動時都重新創建，因此該數據庫在系統啟動時總是干凈的。臨時表和存儲過程在連接斷開時自動除去，而且當系統關閉后將沒有任何連接處于活 動狀態，因此tempdb數據庫中沒有任何內容會從SQL Server的一個會話保存到另一個會話。  
    默認情況下，在SQL Server在運行時tempdb數據庫會根據需要自動增長。不過，與其它數據庫不同，每次啟動數據庫引擎時，它會重置為其初始大小。如果為tempdb 數據庫定義的大小較小，則每次重新啟動SQL Server時，將tempdb數據庫的大小自動增加到支持工作負荷所需的大小這一工作可能會成為系統處理負荷的一部分。為避免這種開銷，可以使用 Alter DATABASE增加tempdb數據庫的大小。

轉自：http://c21.cnblogs.com/archive/2006/05/08/393779.html

CREATE PROCEDURE
創建存儲過程，存儲過程是保存起來的可以接受和返回用戶提供的參數的 Transact-SQL 語句的集合。可以創建一個過程供永久使用，或在一個會話中臨時使用（局部臨時過程），或在所有會話中臨時使用（全局臨時過程）。也可以創建在 Microsoft SQL Server啟動時自動運行的存儲過程。

語法
CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

參數
procedure_name

    新存儲過程的名稱。過程名必須符合標識符規則，且對于數據庫及其所有者必須唯一。有關更多信息，請參見使用標識符。
    要創建局部臨時過程，可以在 procedure_name 前面加一個編號符 (#procedure_name)，要創建全局臨時過程，可以在 procedure_name 前面加兩個編號符 (##procedure_name)。完整的名稱（包括 # 或 ##）不能超過 128 個字符。指定過程所有者的名稱是可選的。

;number
    是可選的整數，用來對同名的過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。例如，名為 orders 的應用程序使用的過程可以命名為 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 語句將除去整個組。如果名稱中包含定界標識符，則數字不應包含在標識符中，只應在 procedure_name 前后使用適當的定界符。

@parameter

過程中的參數。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有 2.100 個參數。

使用 @ 符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其它過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其它數據庫對象的名稱。有關更多信息，請參見 EXECUTE。

data_type

參數的數據類型。所有數據類型（包括 text、ntext 和 image）均可以用作存儲過程的參數。不過，cursor 數據類型只能用于 OUTPUT 參數。如果指定的數據類型為 cursor，也必須同時指定 VARYING 和 OUTPUT 關鍵字。有關 SQL Server 提供的數據類型及其語法的更多信息，請參見數據類型。

說明  對于可以是 cursor 數據類型的輸出參數，沒有最大數目的限制。

VARYING

指定作為輸出參數支持的結果集（由存儲過程動態構造，內容可以變化）。僅適用于游標參數。

default

參數的默認值。如果定義了默認值，不必指定該參數的值即可執行過程。默認值必須是常量或 NULL。如果過程將對該參數使用 LIKE 關鍵字，那么默認值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT

表明參數是返回參數。該選項的值可以返回給 EXEC[UTE]。使用 OUTPUT 參數可將信息返回給調用過程。Text、ntext 和 image 參數可用作 OUTPUT 參數。使用 OUTPUT 關鍵字的輸出參數可以是游標占位符。

n

表示最多可以指定 2.100 個參數的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE 表明 SQL Server 不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執行計劃時，請使用 RECOMPILE 選項。

ENCRYPTION 表示 SQL Server 加密 syscomments 表中包含 CREATE PROCEDURE 語句文本的條目。使用 ENCRYPTION 可防止將過程作為 SQL Server 復制的一部分發布。

說明  在升級過程中，SQL Server 利用存儲在 syscomments 中的加密注釋來重新創建加密過程。

FOR REPLICATION

指定不能在訂閱服務器上執行為復制創建的存儲過程。.使用 FOR REPLICATION 選項創建的存儲過程可用作存儲過程篩選，且只能在復制過程中執行。本選項不能和 WITH RECOMPILE 選項一起使用。

AS

指定過程要執行的操作。

sql_statement

過程中要包含的任意數目和類型的 Transact-SQL 語句。但有一些限制。

n

是表示此過程可以包含多條 Transact-SQL 語句的占位符。

注釋
存儲過程的最大大小為 128 MB。

用戶定義的存儲過程只能在當前數據庫中創建（臨時過程除外，臨時過程總是在 tempdb 中創建）。在單個批處理中，CREATE PROCEDURE 語句不能與其它 Transact-SQL 語句組合使用。

默認情況下，參數可為空。如果傳遞 NULL 參數值并且該參數在 CREATE 或 ALTER TABLE 語句中使用，而該語句中引用的列又不允許使用 NULL，則 SQL Server 會產生一條錯誤信息。為了防止向不允許使用 NULL 的列傳遞 NULL 參數值，應向過程中添加編程邏輯或為該列使用默認值（使用 CREATE 或 ALTER TABLE 的 DEFAULT 關鍵字)。

建議在存儲過程的任何 CREATE TABLE 或 ALTER TABLE 語句中都為每列顯式指定 NULL 或 NOT NULL，例如在創建臨時表時。ANSI_DFLT_ON 和 ANSI_DFLT_OFF 選項控制 SQL Server 為列指派 NULL 或 NOT NULL 特性的方式（如果在 CREATE TABLE 或 ALTER TABLE 語句中沒有指定的話）。如果某個連接執行的存儲過程對這些選項的設置與創建該過程的連接的設置不同，則為第二個連接創建的表列可能會有不同的為空性，并且表現出不同的行為方式。如果為每個列顯式聲明了 NULL 或 NOT NULL，那么將對所有執行該存儲過程的連接使用相同的為空性創建臨時表。

在創建或更改存儲過程時，SQL Server 將保存 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 的設置。執行存儲過程時，將使用這些原始設置。因此，所有客戶端會話的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 設置在執行存儲過程時都將被忽略。在存儲過程中出現的 SET QUOTED_IDENTIFIER 和 SET ANSI_NULLS 語句不影響存儲過程的功能。

其它 SET 選項（例如 SET ARITHABORT、SET ANSI_WARNINGS 或 SET ANSI_PADDINGS）在創建或更改存儲過程時不保存。如果存儲過程的邏輯取決于特定的設置，應在過程開頭添加一條 SET 語句，以確保設置正確。從存儲過程中執行 SET 語句時，該設置只在存儲過程完成之前有效。之后，設置將恢復為調用存儲過程時的值。這使個別的客戶端可以設置所需的選項，而不會影響存儲過程的邏輯。

說明  SQL Server 是將空字符串解釋為單個空格還是解釋為真正的空字符串，由兼容級別設置控制。如果兼容級別小于或等于 65，SQL Server 就將空字符串解釋為單個空格。如果兼容級別等于 70，則 SQL Server 將空字符串解釋為空字符串。有關更多信息，請參見 sp_dbcmptlevel。

獲得有關存儲過程的信息
若要顯示用來創建過程的文本，請在過程所在的數據庫中執行 sp_helptext，并使用過程名作為參數。

說明  使用 ENCRYPTION 選項創建的存儲過程不能使用 sp_helptext 查看。

若要顯示有關過程引用的對象的報表，請使用 sp_depends。

若要為過程重命名，請使用 sp_rename。

引用對象
SQL Server 允許創建的存儲過程引用尚不存在的對象。在創建時，只進行語法檢查。執行時，如果高速緩存中尚無有效的計劃，則編譯存儲過程以生成執行計劃。只有在編譯過程中才解析存儲過程中引用的所有對象。因此，如果語法正確的存儲過程引用了不存在的對象，則仍可以成功創建，但在運行時將失敗，因為所引用的對象不存在。有關更多信息，請參見延遲名稱解析和編譯。

延遲名稱解析和兼容級別
SQL Server 允許 Transact-SQL 存儲過程在創建時引用不存在的表。這種能力稱為延遲名稱解析。不過，如果 Transact-SQL 存儲過程引用了該存儲過程中定義的表，而兼容級別設置（通過執行 sp_dbcmptlevel 來設置）為 65，則在創建時會發出警告信息。而如果在運行時所引用的表不存在，將返回錯誤信息。有關更多信息，請參見 sp_dbcmptlevel 和延遲名稱解析和編譯。

執行存儲過程
成功執行 CREATE PROCEDURE 語句后，過程名稱將存儲在 sysobjects 系統表中，而 CREATE PROCEDURE 語句的文本將存儲在 syscomments 中。第一次執行時，將編譯該過程以確定檢索數據的最佳訪問計劃。

使用 cursor 數據類型的參數
存儲過程只能將 cursor 數據類型用于 OUTPUT 參數。如果為某個參數指定了 cursor 數據類型，也必須指定 VARYING 和 OUTPUT 參數。如果為某個參數指定了 VARYING 關鍵字，則數據類型必須是 cursor，并且必須指定 OUTPUT 關鍵字。

說明  cursor 數據類型不能通過數據庫 API（例如 OLE DB、ODBC、ADO 和 DB-Library）綁定到應用程序變量上。因為必須先綁定 OUTPUT 參數，應用程序才可以執行存儲過程，所以帶有 cursor OUTPUT 參數的存儲過程不能通過數據庫 API 調用。只有將 cursor OUTPUT 變量賦值給 Transact-SQL 局部 cursor 變量時，才可以通過 Transact-SQL 批處理、存儲過程或觸發器調用這些過程。

Cursor 輸出參數
在執行過程時，以下規則適用于 cursor 輸出參數：

對于只進游標，游標的結果集中返回的行只是那些存儲過程執行結束時處于或超出游標位置的行，例如：
在過程中的名為 RS 的 100 行結果集上打開一個非滾動游標。

過程提取結果集 RS 的頭 5 行。

過程返回到其調用者。

返回到調用者的結果集 RS 由 RS 的第 6 到 100 行組成，調用者中的游標處于 RS 的第一行之前。
對于只進游標，如果存儲過程完成后，游標位于第一行的前面，則整個結果集將返回給調用批處理、存儲過程或觸發器。返回時，游標將位于第一行的前面。

對于只進游標，如果存儲過程完成后，游標的位置超出最后一行的結尾，則為調用批處理、存儲過程或觸發器返回空結果集。

說明  空結果集與空值不同。

對于可滾動游標，在存儲過程執行結束時，結果集中的所有行均會返回給調用批處理、存儲過程或觸發器。返回時，游標保留在過程中最后一次執行提取時的位置。

對于任意類型的游標，如果游標關閉，則將空值傳遞回調用批處理、存儲過程或觸發器。如果將游標指派給一個參數，但該游標從未打開過，也會出現這種情況。

說明  關閉狀態只有在返回時才有影響。例如，可以在過程中關閉游標，稍后再打開游標，然后將該游標的結果集返回給調用批處理、存儲過程或觸發器。

臨時存儲過程
SQL Server 支持兩種臨時過程：局部臨時過程和全局臨時過程。局部臨時過程只能由創建該過程的連接使用。全局臨時過程則可由所有連接使用。局部臨時過程在當前會話結束時自動除去。全局臨時過程在使用該過程的最后一個會話結束時除去。通常是在創建該過程的會話結束時。

臨時過程用 # 和 ## 命名，可以由任何用戶創建。創建過程后，局部過程的所有者是唯一可以使用該過程的用戶。執行局部臨時過程的權限不能授予其他用戶。如果創建了全局臨時過程，則所有用戶均可以訪問該過程，權限不能顯式廢除。只有在 tempdb 數據庫中具有顯式 CREATE PROCEDURE 權限的用戶，才可以在該數據庫中顯式創建臨時過程（不使用編號符命名）。可以授予或廢除這些過程中的權限。

說明  頻繁使用臨時存儲過程會在 tempdb 中的系統表上產生爭用，從而對性能產生負面影響。建議使用 sp_executesql 代替。sp_executesql 不在系統表中存儲數據，因此可以避免這一問題。

自動執行存儲過程
SQL Server 啟動時可以自動執行一個或多個存儲過程。這些存儲過程必須由系統管理員創建，并在 sysadmin 固定服務器角色下作為后臺過程執行。這些過程不能有任何輸入參數。

對啟動過程的數目沒有限制，但是要注意，每個啟動過程在執行時都會占用一個連接。如果必須在啟動時執行多個過程，但不需要并行執行，則可以指定一個過程作為啟動過程，讓該過程調用其它過程。這樣就只占用一個連接。

在啟動時恢復了最后一個數據庫后，即開始執行存儲過程。若要跳過這些存儲過程的執行，請將啟動參數指定為跟蹤標記 4022。如果以最低配置啟動 SQL Server（使用 -f 標記），則啟動存儲過程也不會執行。有關更多信息，請參見跟蹤標記。

若要創建啟動存儲過程，必須作為 sysadmin 固定服務器角色的成員登錄，并在 master 數據庫中創建存儲過程。

使用 sp_procoption 可以：

將現有存儲過程指定為啟動過程。

停止在 SQL Server 啟動時執行過程。

查看 SQL Server 啟動時執行的所有過程的列表。
存儲過程嵌套
存儲過程可以嵌套，即一個存儲過程可以調用另一個存儲過程。在被調用過程開始執行時，嵌套級將增加，在被調用過程執行結束后，嵌套級將減少。如果超出最大的嵌套級，會使整個調用過程鏈失敗。可用 @@NESTLEVEL 函數返回當前的嵌套級。

若要估計編譯后的存儲過程大小，請使用下列性能監視計數器。

性能監視器對象名 性能監視計數器名稱
SQLServer：緩沖區管理器 高速緩存大小（頁面數）
SQLServer：高速緩存管理器 高速緩存命中率
  高速緩存頁
  高速緩存對象計數*

* 各種分類的高速緩存對象均可以使用這些計數器，包括特殊 sql、準備 sql、過程、觸發器等。

有關更多信息，請參見 SQL Server:Buffer Manager 對象和 SQL Server:Cache Manager 對象。

sql_statement 限制
除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 之外（這兩個語句必須是批處理中僅有的語句），任何 SET 語句均可以在存儲過程內部指定。所選擇的 SET 選項在存儲過程執行過程中有效，之后恢復為原來的設置。

如果其他用戶要使用某個存儲過程，那么在該存儲過程內部，一些語句使用的對象名必須使用對象所有者的名稱限定。這些語句包括：

ALTER TABLE

CREATE INDEX

CREATE TABLE

所有 DBCC 語句

DROP TABLE

DROP INDEX

TRUNCATE TABLE

UPDATE STATISTICS
權限
CREATE PROCEDURE 的權限默認授予 sysadmin 固定服務器角色成員和 db_owner 和 db_ddladmin 固定數據庫角色成員。sysadmin 固定服務器角色成員和 db_owner 固定數據庫角色成員可以將 CREATE PROCEDURE 權限轉讓給其他用戶。執行存儲過程的權限授予過程的所有者，該所有者可以為其它數據庫用戶設置執行權限。

示例
A. 使用帶有復雜 SELECT 語句的簡單過程
下面的存儲過程從四個表的聯接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數。

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 存儲過程可以通過以下方法執行：

EXECUTE au_info_all
-- Or
EXEC au_info_all

如果該過程是批處理中的第一條語句，則可使用：

au_info_all

B. 使用帶有參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程接受與傳遞的參數精確匹配的值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'au_info' AND type = 'P')
   DROP PROCEDURE au_info
GO
USE pubs
GO
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 存儲過程可以通過以下方法執行：

EXECUTE au_info 'Dull', 'Ann'
-- Or
EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
-- Or
EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

如果該過程是批處理中的第一條語句，則可使用：

au_info 'Dull', 'Ann'
-- Or
au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'

C. 使用帶有通配符參數的簡單過程
下面的存儲過程從四個表的聯接中只返回指定的作者（提供了姓名）、出版的書籍以及出版社。該存儲過程對傳遞的參數進行模式匹配，如果沒有提供參數，則使用預設的默認值。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'au_info2' AND type = 'P')
   DROP PROCEDURE au_info2
GO
USE pubs
GO
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 存儲過程可以用多種組合執行。下面只列出了部分組合：

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%'

D. 使用 OUTPUT 參數
OUTPUT 參數允許外部過程、批處理或多條 Transact-SQL 語句訪問在過程執行期間設置的某個值。下面的示例創建一個存儲過程 (titles_sum)，并使用一個可選的輸入參數和一個輸出參數。

首先，創建過程：

USE pubs
GO
IF EXISTS(SELECT name FROM sysobjects
      WHERE name = 'titles_sum' AND type = 'P')
   DROP PROCEDURE titles_sum
GO
USE pubs
GO
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = '%', @@SUM money OUTPUT
AS
SELECT 'Title Name' = title
FROM titles
WHERE title LIKE @@TITLE
SELECT @@SUM = SUM(price)
FROM titles
WHERE title LIKE @@TITLE
GO

接下來，將該 OUTPUT 參數用于控制流語言。

說明  OUTPUT 變量必須在創建表和使用該變量時都進行定義。

參數名和變量名不一定要匹配，不過數據類型和參數位置必須匹配（除非使用 @@SUM = variable 形式）。

DECLARE @@TOTALCOST money
EXECUTE titles_sum 'The%', @@TOTALCOST OUTPUT
IF @@TOTALCOST < 200
BEGIN
   PRINT ' '
   PRINT 'All of these titles can be purchased for less than $200.'
END
ELSE
   SELECT 'The total cost of these titles is $'
         + RTRIM(CAST(@@TOTALCOST AS varchar(20)))

下面是結果集：

Title Name                                                              
------------------------------------------------------------------------
The Busy Executive's Database Guide
The Gourmet Microwave
The Psychology of Computer Cooking

(3 row(s) affected)

Warning, null value eliminated from aggregate.
 
All of these titles can be purchased for less than $200.

E. 使用 OUTPUT 游標參數
OUTPUT 游標參數用來將存儲過程的局部游標傳遞回調用批處理、存儲過程或觸發器。

首先，創建以下過程，在 titles 表上聲明并打開一個游標：

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titles

OPEN @titles_cursor
GO

接下來，執行一個批處理，聲明一個局部游標變量，執行上述過程以將游標賦值給局部變量，然后從該游標提取行。

USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

F. 使用 WITH RECOMPILE 選項
如果為過程提供的參數不是典型的參數，并且新的執行計劃不應高速緩存或存儲在內存中，WITH RECOMPILE 子句會很有幫助。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'titles_by_author' AND type = 'P')
   DROP PROCEDURE titles_by_author
GO
CREATE PROCEDURE titles_by_author @@LNAME_PATTERN varchar(30) = '%'
WITH RECOMPILE
AS
SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name',
   title AS Title
FROM authors a INNER JOIN titleauthor ta
   ON a.au_id = ta.au_id INNER JOIN titles t
   ON ta.title_id = t.title_id
WHERE au_lname LIKE @@LNAME_PATTERN
GO

G. 使用 WITH ENCRYPTION 選項
WITH ENCRYPTION 子句對用戶隱藏存儲過程的文本。下例創建加密過程，使用 sp_helptext 系統存儲過程獲取關于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關于該過程的信息。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'encrypt_this' AND type = 'P')
   DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO

EXEC sp_helptext encrypt_this

下面是結果集：

The object's comments have been encrypted.

接下來，選擇加密存儲過程內容的標識號和文本。

SELECT c.id, c.text
FROM syscomments c INNER JOIN sysobjects o
   ON c.id = o.id
WHERE o.name = 'encrypt_this'

下面是結果集：

說明  text 列的輸出顯示在單獨一行中。執行時，該信息將與 id 列信息出現在同一行中。

id         text                                                       
---------- ------------------------------------------------------------
1413580074 ?????????????????????????????????e??????????????????????????????????????????????????????????????????????????

(1 row(s) affected)

H. 創建用戶定義的系統存儲過程
下面的示例創建一個過程，顯示表名以 emp 開頭的所有表及其對應的索引。如果沒有指定參數，該過程將返回表名以 sys 開頭的所有表（及索引）。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'sp_showindexes' AND type = 'P')
   DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
   @@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
   i.name AS INDEX_NAME,
   indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
   ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO        
USE pubs
EXEC sp_showindexes 'emp%'
GO

下面是結果集：

TABLE_NAME       INDEX_NAME       INDEX_ID
---------------- ---------------- ----------------
employee         employee_ind     1
employee         PK_emp_id        2

(2 row(s) affected)

I. 使用延遲名稱解析
下面的示例顯示四個過程以及延遲名稱解析的各種可能使用方式。盡管引用的表或列在編譯時不存在，但每個存儲過程都可創建。

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'proc1' AND type = 'P')
   DROP PROCEDURE proc1
GO
-- Creating a procedure on a nonexistent table.
USE pubs
GO
CREATE PROCEDURE proc1
AS
   SELECT *
   FROM does_not_exist
GO 
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
   ON o.id = c.id
WHERE o.type = 'P' AND o.name = 'proc1'
GO
USE master
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'proc2' AND type = 'P')
   DROP PROCEDURE proc2
GO
-- Creating a procedure that attempts to retrieve information from a
-- nonexistent column in an existing table.
USE pubs
GO
CREATE PROCEDURE proc2
AS
   DECLARE @middle_init char(1)
   SET @middle_init = NULL
   SELECT au_id, middle_initial = @middle_init
   FROM authors
GO 
-- Here is the statement to actually see the text of the procedure.
SELECT o.id, c.text
FROM sysobjects o INNER JOIN syscomments c
   ON o.id = c.id
WHERE o.type = 'P' and o.name = 'proc2'

package net.jforum.sso;

import javax.servlet.http.Cookie;

import net.jforum.ControllerUtils;
import net.jforum.context.RequestContext;
import net.jforum.entities.UserSession;
import net.jforum.util.preferences.ConfigKeys;
import net.jforum.util.preferences.SystemGlobals;
import org.apache.log4j.Logger;
 
  public class CookieUserSSO implements SSO {     
    
    static final Logger logger = Logger.getLogger(CookieUserSSO.class.getName());     
    
    public String authenticateUser(RequestContext request) { 
         // myapp login cookie, contain logged username     
        Cookie myCookie = ControllerUtils.getCookie("jforumSSOCookieNameUser");     
             
        String username = null;          
        if (myCookie != null) username = myCookie.getValue();      
        System.out.println("cookie_name1="+myCookie.getName());   
        System.out.println("cookie value1="+myCookie.getValue());  
             
        if (myCookie == null || username.trim().equals("")) {     
            //JForumExecutionContext.setRedirect(SystemGlobals.getValue(ConfigKeys.SSO_REDIRECT));     
            return null; // no cookie found     
        }      
        System.out.println("cookie_name2="+myCookie.getName());  
        System.out.println("cookie value2="+myCookie.getValue());  
        return username; // jforum username     
    }     
    
    public boolean isSessionValid(UserSession userSession, RequestContext request) {   
  System.out.println("執行isSessionValid方法");
        Cookie SSOCookie = ControllerUtils.getCookie("jforumSSOCookieNameUser"); // myapp login cookie      
                       
        String remoteUser = null;     
             
        if (SSOCookie != null) remoteUser = SSOCookie.getValue(); //  jforum username     
    
        // user has since logged out     
        if(remoteUser == null &&      
                userSession.getUserId() != SystemGlobals.getIntValue(ConfigKeys.ANONYMOUS_USER_ID)) {     
            return false;     
        // user has since logged in     
        } else if(remoteUser != null &&      
                userSession.getUserId() == SystemGlobals.getIntValue(ConfigKeys.ANONYMOUS_USER_ID)) {     
            return false;     
        // user has changed user     
        } else if(remoteUser != null && !remoteUser.equals(userSession.getUsername())) {     
            return false;     
        }     
        return true; // myapp user and forum user the same     
    }     
}    

Cookie[] cookies = request.getCookies();
    if (cookies != null) {
     for (int i = 0; i < cookies.length; i++) {
      cookies[i].setValue(null);
      cookies[i].setMaxAge(0);
      response.addCookie(cookies[i]);
     }
    }