6.2-數(shù)據(jù)庫存儲過程

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

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

3、儲過程的實際代碼的開始DECLARE - 聲明段BEGIN -函數(shù)體語句END;$ LANGUAGE lang_name; /$ 表明代碼的結(jié)束, LANGUAGE后面指明所用的編程語言（1）name：要創(chuàng)建的存儲過程名；（3）argmode：存儲過程參數(shù)的模式可以為IN、OUT或INOUT，缺省值是IN。（4）argname：形式參數(shù)的名字。（5）RETURNS：返回值；RETURNS TABLE：返回二維表（2）OR REPLACE ：覆蓋同名的存儲過程；四、創(chuàng)建存儲過程的示例創(chuàng)建一個名為countRecords()的存儲過程統(tǒng)計STUDENT表的記錄數(shù)。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;五、執(zhí)行存儲過程如果程序員需要在查詢窗口執(zhí)行存儲過程，語法形式如下： select 存儲過程名（參數(shù)）；或者： select * from 存儲過程名（參數(shù)）；例如： select countRecords ( );或者：select * from countRec

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

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

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

8、unt intger； rec RECORD ；七、PL/SQL基本語法（續(xù)）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基本語法（續(xù)）3、循環(huán)語句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基本語法（續(xù)）4、遍歷命令結(jié)果FOR record_or_row IN que

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