target
掌握什么是存儲(chǔ)過程
掌握存儲(chǔ)過程的作用
掌握存儲(chǔ)過程的創(chuàng)建
掌握存儲(chǔ)過程的修改與刪除
1. 什么是存儲(chǔ)過程
存儲(chǔ)過程在數(shù)據(jù)庫開發(fā)中使用比較頻繁，它有著普通SQL語句不可替代的作用。
1.1 認(rèn)識(shí)存儲(chǔ)過程
存儲(chǔ)過程可以通俗地理解為是存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中的封裝了一段或多段SQL語句的PL/SQL代碼塊。在數(shù)據(jù)庫中有一些是系統(tǒng)默認(rèn)的存儲(chǔ)過程，那么可以直接通過存儲(chǔ)過程的名稱進(jìn)行調(diào)用。另外，存儲(chǔ)過程還可以在編程語言中調(diào)用，如Java、C#等編程語言。
1.2 存儲(chǔ)過程的作用
-
簡化復(fù)雜的操作。
存儲(chǔ)過程可以把需要執(zhí)行的多條SQL語句封裝到一個(gè)獨(dú)立單元中，用戶只需調(diào)用這個(gè)單元就能達(dá)到目的。這樣就實(shí)現(xiàn)了一人編寫多人調(diào)用，同時(shí)縮短了平均開發(fā)周期，為公司節(jié)省了成本。
-
增加數(shù)據(jù)獨(dú)立性。
與視圖的效果類似，利用存儲(chǔ)過程可以把數(shù)據(jù)庫基礎(chǔ)數(shù)據(jù)和程序 （或用戶）隔離開來，當(dāng)基礎(chǔ)數(shù)據(jù)的結(jié)構(gòu)發(fā)生變化時(shí)，可以修改存儲(chǔ)過程，這樣對程序來說基礎(chǔ)數(shù)據(jù)的變化是不可見的，也就不需要修改程序代碼了。
-
提高安全性
使用存儲(chǔ)過程有效地降低了錯(cuò)誤出現(xiàn)的幾率。如果不使用存儲(chǔ)過程要想實(shí)現(xiàn)某項(xiàng)操作可能需要執(zhí)行多條單獨(dú)的SQL語句，而過多的執(zhí)行步驟很可能造成更高的出錯(cuò)幾率。不僅如此，實(shí)際工作中開發(fā)人員的水平參差不齊，由高水平的人編寫存儲(chǔ)過程，水平較低的人員直接調(diào)用，這樣就能避免很多不必要的錯(cuò)誤發(fā)生。此外，存儲(chǔ)
過程也可以進(jìn)行權(quán)限設(shè)置。 提高性能。完成一項(xiàng)復(fù)雜的功能可能需要多條SQL語句，同時(shí)SQL每次執(zhí)行都需要編譯， 而存儲(chǔ)過程可以包含多條SQL語句，而且創(chuàng)建完成后只需要編譯一次，以后就可以直接調(diào)用，從這方面來看存儲(chǔ)過程可以提高性能。如果程序語言要實(shí)現(xiàn)某項(xiàng)比較復(fù)雜的功能， 它會(huì)多次連接數(shù)據(jù)庫，在使用存儲(chǔ)過程的情況下，程序只需連接一次就能達(dá)到目的。
1.3 存儲(chǔ)過程的語法
CREATE ( OR REPLACE ] PROCEDURE [ schema. ] procedure_name
[parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
| { OUT | IN OUT } ( NOCOPY ] datatype
] [,...]
{ is | as}
body;
其中各項(xiàng)參數(shù)介紹如下：
OR REPLACE：表示如果指定的過程已經(jīng)存在，則覆蓋同名的存儲(chǔ)過程。
schema：表示該存儲(chǔ)過程的所屬機(jī)構(gòu)。
procedure_name:創(chuàng)建存儲(chǔ)過程的名稱。
parameter_name：表示存儲(chǔ)過程中的參數(shù)名稱。
[IN] datatype [ { := I DEFAULT } expression ]：整個(gè)這段語法表示傳入?yún)?shù)的數(shù)據(jù)類型以及默認(rèn)值。其中，datatype項(xiàng)表示參數(shù)的數(shù)據(jù)類型，[{ := I DEFAULT } expression ] 項(xiàng)表示參數(shù)的默認(rèn)值的寫法。
{ OUT | IN OUT } [ NOCOPY ] datatype：表示存儲(chǔ)過程的參數(shù)類型，不過和上面介紹 的IN有所區(qū)別。其中，OUT表示輸出參數(shù)，IN OUT表示既可輸入也可輸出的參數(shù)， datatype依舊表示參數(shù)類型。
{ IS | AS }：連接詞。
BODY：表示函數(shù)體，是存儲(chǔ)過程的具體操作部分，通常在begin...end中。
注意：
存儲(chǔ)過程的參數(shù)默認(rèn)類型是IN型的，也就是說是傳入型的。
創(chuàng)建存儲(chǔ)過程需要有CREATE PROCEDURE權(quán)限。
2. 在SQL *Plus中創(chuàng)建存儲(chǔ)過程
2.1 hello world存儲(chǔ)過程
create procedure helloworld as
begin
dbms_output.put_line('Hello Wolrd');
end;
/
執(zhí)行效果：
① serveroutput設(shè)置
要想讓dbms_output.put_line成功輸出，需要把serveroutput選項(xiàng)設(shè)置為ON狀態(tài)。默認(rèn)情況是OFF狀態(tài)的?？梢詧?zhí)行如下語句查看：
show serveroutput
打開輸出設(shè)置：
set serveroutput on
② 執(zhí)行存儲(chǔ)過程
begin
helloworld;
end;
/
輸出：
Hello Wolrd
PL/SQL 過程已成功完成。
還可以通過關(guān)鍵詞exec執(zhí)行存儲(chǔ)過程：
exec helloworld;
2.2 查看存儲(chǔ)過程
存儲(chǔ)過程一旦被創(chuàng)建就會(huì)存儲(chǔ)到數(shù)據(jù)庫服務(wù)器上，Oracle允許開發(fā)人員查看已經(jīng)存在的存儲(chǔ)過程腳本，這可以到視圖USER_SOURCE里査看。
??：査看存儲(chǔ)過程helloworld的腳本
SELECT * FROM USER_SOURCE WHERE NAME = 'HELLOWORLD' ORDER BY LINE;
注意：過程名需要全部大寫。
2.3 顯示存儲(chǔ)過程的錯(cuò)誤
語法：
show errors procedure produce_name;
??：故意寫錯(cuò)一個(gè)存儲(chǔ)過程，然后查看錯(cuò)誤
① 創(chuàng)建存儲(chǔ)過程：
create procedure test_err as
begin
dbms_output.put_lin('Hello Wolrd');
end;
/
輸出：
警告: 創(chuàng)建的過程帶有編譯錯(cuò)誤。
② 查看存儲(chǔ)過程的錯(cuò)誤細(xì)節(jié)：
show errors procedure test_err;
輸出：
PROCEDURE TEST_ERR 出現(xiàn)錯(cuò)誤:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PL/SQL: Statement ignored
3/13 PLS-00302: 必須聲明 'PUT_LIN' 組件
2.4 無參存儲(chǔ)過程
無參存儲(chǔ)過程就是創(chuàng)建的存儲(chǔ)過程不帶任何參數(shù)，通常這種存儲(chǔ)過程用做數(shù)據(jù)轉(zhuǎn)換的幾率 比較大。
??：把表PRODUCTINFO中價(jià)格最低的3件產(chǎn)品的description字段設(shè)置成“促銷商品”，實(shí)現(xiàn)步驟如下：
(1) 將PRODUCTINFO中產(chǎn)品價(jià)格最低的3件產(chǎn)品査詢出來。
(2) 把價(jià)格最低的3件產(chǎn)品description字段加上"促銷商品”字樣。
首先為PRODUCTINFO表添加一列description：
alter table productinfo add description varchar2(100);
創(chuàng)建存儲(chǔ)過程的腳本如下：
create procedure product_update_prc as
begin
update productinfo set description = '促銷商品'
where productid in
(
select productid from (select * from productinfo order by productprice)
where rownum < 4
);
commit;
end;
/
此時(shí)的過程還沒有執(zhí)行，只是編譯通過了，執(zhí)行后數(shù)據(jù)才會(huì)修改：
exec product_update_prc;
2.5 有參存儲(chǔ)過程
存儲(chǔ)過程允許帶有參數(shù)，參數(shù)的使用將增加存儲(chǔ)過程的靈活性，給數(shù)據(jù)庫編程帶來極大的方便。
存儲(chǔ)過程中如果使用了參數(shù)，在執(zhí)行存儲(chǔ)過程時(shí)必須為其指定參數(shù)。參數(shù)可以是常量、變量、表達(dá)式等。
過程有輸入、輸出、輸入輸出三種參數(shù)。其中，輸入?yún)?shù) 是默認(rèn)的參數(shù)，也叫IN類型的參數(shù)。
??：根據(jù)輸入的部門名稱，從表emp中搜索出該部門員工的信息，并將其打印到屏幕
create procedure emp_intype_prc(parm_deptname in varchar2) as
cur_deptno emp.deptno%type; --存放部門名稱
cur_emp emp%rowtype; --存放emp表中的行記錄
begin
--根據(jù)部門名字查出部門編號(hào)
select deptno into cur_deptno
from dept where dname=parm_deptname;
if sql%found then
dbms_output.put_line(parm_deptname || ':');
end if;
--從游標(biāo)里面取值
for my_emp_rec in
(
select * from emp where deptno = cur_deptno
)
loop
dbms_output.put_line('姓名：' || my_emp_rec.ename || ' 工作：' || my_emp_rec.job || ' 薪資：' || my_emp_rec.sal);
end loop;
exception
when no_data_found then
dbms_output.put_line('沒有數(shù)據(jù)');
when too_many_rows then
dbms_output.put_line('數(shù)據(jù)過多');
end;
/
執(zhí)行：
SQL> exec emp_intype_prc('ACCOUNTING');
ACCOUNTING:
姓名：CLARK 工作：MANAGER 薪資：2450
姓名：KING 工作：PRESIDENT 薪資：5000
姓名：MILLER 工作：CLERK 薪資：1300
PL/SQL 過程已成功完成。
??：輸出emp表的記錄數(shù)
CREATE or replace PROCEDURE prc_empcount (empcount out number) as
BEGIN
Select count(*) into empcount From emp;
END;
/
執(zhí)行：
SQL> variable count number;
SQL> exec prc_empcount(:count);
SQL> print count;
COUNT
----------
14
3. 修改存儲(chǔ)過程
修改存儲(chǔ)過程內(nèi)容要利用replace關(guān)鍵詞。
在創(chuàng)建過程中需要開發(fā)人員自行帶上or replace關(guān)鍵詞，從而完成過程的修改，也就是覆蓋。
4. 刪除存儲(chǔ)過程
語法：
drop procedure procedure_name;
??：刪除 prc_empcount
drop procedure prc_empcount;
習(xí)題
一、填空題
- 存儲(chǔ)過程參數(shù)分為（ ）、（ ）和（ ）3種類型。
- SQL *Plus使用（ ）命令來執(zhí)行存儲(chǔ)過程。
- SQL*Plus使用（ ）命令來顯示錯(cuò)誤信息。
二、選擇題
OUT類型的參數(shù)以使用下哪項(xiàng)填充？（ ）
A. 常量 B. 變量
C. 初始化后的變最 D. 函數(shù)有輸入?yún)?shù)的存儲(chǔ)過程調(diào)用時(shí)能否不帶參數(shù)？（ ）
A.不能 B,能
三、簡答題
為什么使用存儲(chǔ)過程？
存儲(chǔ)過程和語句塊有什么區(qū)別？
