1、6.3 數據庫存儲過程了解存儲過程的概念掌握存儲過程創建、刪除的方法掌握存儲過程的執行方法掌握PostgreSQL的PL/SQL基本語法理解存儲過程的優缺點【本節的主要內容】一、什么是存儲過程存儲過程（Stored Procedure）是一種數據庫的對象；由一組能完成特定功能的SQL 語句集構成；是把經常會被重復使用的SQL語句邏輯塊封裝起來，經編譯后，存儲在數據庫服務器端；當被再次調用時，而不需要再次編譯；當客戶端連接到數據庫時，用戶通過指定存儲過程的名字并給出參數，數據庫就可以找到相應的存儲過程予以調用。二、創建存儲過程不同的數據庫系統創建存儲過程的語法存在差異；許多數據庫為創建存儲過程和

2、函數提供不同命令；如ORACLE、MySQL、SQL SERVER等數據庫，使用CREATE PRECEDURE命令創建存儲過程，使用CREATE FUNCTION命令創建函數。PostgreSQL使用CREATE FUNCTION命令創建存儲過程。三、創建存儲過程的語法CREATE OR REPLACE FUNCTION name ( argmode argname argtype DEFAULT | = default_expr , . ) RETURNS retype | RETURNS TABLE ( column_name column_type , . ) AS $ /$用于聲明存

3、儲過程的實際代碼的開始DECLARE - 聲明段BEGIN -函數體語句END;$ LANGUAGE lang_name; /$ 表明代碼的結束, LANGUAGE后面指明所用的編程語言（1）name：要創建的存儲過程名；（3）argmode：存儲過程參數的模式可以為IN、OUT或INOUT，缺省值是IN。（4）argname：形式參數的名字。（5）RETURNS：返回值；RETURNS TABLE：返回二維表（2）OR REPLACE ：覆蓋同名的存儲過程；四、創建存儲過程的示例創建一個名為countRecords()的存儲過程統計STUDENT表的記錄數。CREATE OR REPLACE

4、 FUNCTION countRecords () RETURNS integer AS $count$ declare count integer; BEGIN SELECT count(*) into count FROM STUDENT; RETURN count; END; $count$ LANGUAGE plpgsql;五、執行存儲過程如果程序員需要在查詢窗口執行存儲過程，語法形式如下： select 存儲過程名（參數）；或者： select * from 存儲過程名（參數）；例如： select countRecords ( );或者：select * from countRec

5、ords ( );五、執行存儲過程（續）如果程序員需要存儲過程調用其它存儲過程，語法形式如下：select into 自定義變量 from 存儲過程名（參數）；CREATE OR REPLACE FUNCTION testExec() returns integer AS $ declare rec integer; BEGIN select into rec countRecords(); /如果不關心countRecords()的返回值，則可用 PERFORM countRecords() 代替; return rec; END; $ LANGUAGE plpgsql;六、刪除存儲過程如果

6、程序員需要刪除存儲過程，語法形式如下：DROP FUNCTION IF EXISTS name ( argmode argname argtype , . ) CASCADE | RESTRICT 主要參數：（1）IF EXISTS：如果指定的存儲過程不存在，那么發出提示信息。（2）name ：現存的存儲過程名稱。（3）argmode：參數的模式：IN(缺省), OUT, INOUT, VARIADIC。請注意，實際并不注意OUT參數，因為判斷存儲過程的身份只需要輸入參數。（4）argname：參數的名字。請注意，實際上并不注意參數的名字，因為判斷函數的身份只需要輸入參數的數據類型。（5）ar

7、gtype：如果有的話，是存儲過程參數的類型。（6）CASCADE：級聯刪除依賴于存儲過程的對象(如觸發器)。（7）RESTRICT：如果有任何依賴對象存在，則拒絕刪除該函數；這個是缺省值。六、刪除存儲過程（續）例子：假如需要刪除前面定義的存儲過程 testExec()DROP FUNCTION IF EXISTS testExec() 七、PL/SQL基本語法1、聲明局部變量。變量聲明的語法如下： declare 變量名 變量類型；如果聲明變量為記錄類型，變量聲明格式為： variable_name RECORD;注：RECORD不是真正的數據類型，只是一個占位符。例如：declare co

8、unt intger； rec RECORD ；七、PL/SQL基本語法（續）2、條件語句 在PL/pgSQL中有以下三種形式的條件語句，與其他高級語言的條件語句意義相同。1). IF-THEN IF boolean-expression THEN statementsEND IF; 2). IF-THEN-ELSEIF boolean-expression THEN statementsELSE 3). IF-THEN-ELSIF-ELSEIF boolean-expression THEN statementsELSIF boolean-expression THEN statements

9、ELSIF boolean-expression THEN statementsELSE statementsEND IF; 七、PL/SQL基本語法（續）3、循環語句1). LOOP 語句LOOP statementsEND LOOP label ;2). EXIT EXIT label WHEN expression ;例如： LOOP count=count+1; EXIT WHEN count 100; END LOOP;3). CONTINUE CONTINUE label WHEN expression ;例如： LOOP count=count+1; EXIT WHEN coun

10、t 100; CONTINUE WHEN count 0 AND balance 0 LOOP -do something END LOOP;5). FORFOR name IN REVERSE expression . expression LOOP statementsEND LOOP;例如： FOR i IN 1.10 LOOP RAISE NOTICE i IS %, i; END LOOP; FOR i IN REVERSE 10.1 LOOP -do something END LOOP;七、PL/SQL基本語法（續）4、遍歷命令結果FOR record_or_row IN que

11、ry LOOP statementsEND LOOP ;FOR循環可以遍歷命令的結果并操作相應的數據，例如： declare rec RECORD ； FOR rec IN SELECT sid , sname FROM student LOOP raise notice %-,%-,rec.sid, rec.sname; END LOOP;八、存儲過程的優缺點1、使用存儲過程的優點 (1) 減少網絡通信量 (2) 執行速度更快 (3) 更強的適應性 (4) 降低了業務實現與應用程序的耦合 (5) 降低了開發的復雜性 (6) 保護數據庫元信息 (7) 增強了數據庫的安全性八、存儲過程的優缺點2、使用存儲過程的缺點 (1) SQL本身是一種結構化查詢語言，而存儲過程本質