存儲過程之--MySQL與Oracle實現對比
1. 存儲過程簡介
我們常用的操作數據庫語言SQL語句在執行的時候需要要先編譯，然後執行，而存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後存儲在數據庫中，用戶通過指定存儲過程的名字並給定參數（如果該存儲過程帶有參數）來調用執行它。
一個存儲過程是一個可編程的函數，它在數據庫中創建並保存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程序或平台上執行相同的函數，或者封裝特定功能時，存儲過程是非常有用的。數據庫中的存儲過程可以看做是對編程中面向對象方法的模擬。它允許控制數據的訪問方式。
存儲過程通常有以下優點：
(1).存儲過程增強了SQL語言的功能和靈活性。存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成復雜的判斷和較復雜的運算。
(2).存儲過程允許標准組件是編程。存儲過程被創建後，可以在程序中被多次調用，而不必重新編寫該存儲過程的SQL語句。而且數據庫專業人員可以隨時對存儲過程進行修改，對應用程序源代碼毫無影響。
(3).存儲過程能實現較快的執行速度。如果某一操作包含大量的Transaction-SQL代碼或分別被多次執行，那麼存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運行一個存儲過程時查詢，優化器對其進行分析優化，並且給出最終被存儲在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運行時都要進行編譯和優化，速度相對要慢一些。
(4).存儲過程能過減少網絡流量。針對同一個數據庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織程存儲過程，那麼當在客戶計算機上調用該存儲過程時，網絡中傳送的只是該調用語句，從而大大增加了網絡流量並降低了網絡負載。
(5).存儲過程可被作為一種安全機制來充分利用。系統管理員通過執行某一存儲過程的權限進行限制，能夠實現對相應的數據的訪問權限的限制，避免了非授權用戶對數據的訪問，保證了數據的安全。
二、MySQL存儲過程
(1). 格式
MySQL存儲過程創建的格式：CREATE PROCEDURE過程名 ([過程參數[,...]])
[特性 ...]過程體
案例分析：
參數
MySQL存儲過程的參數用在存儲過程的定義，共有三種參數類型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 參數名數據類形...])
IN 輸入參數:表示該參數的值必須在調用存儲過程時指定，在存儲過程中修改該參數的值不能被返回，為默認值
OUT 輸出參數:該值可在存儲過程內部被改變，並可返回
INOUT 輸入輸出參數:調用時指定，並且可被改變和返回
1）不帶參數：
mysql>delimiter //
mysql> create procedure p1()
-> begin
-> declare v_ename varchar(10);
-> declare v_sal int;
-> select ename,sal into v_ename,v_sal from emp where empno=7788; -> select v_ename ;
-> select v_sal;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call p1();
+---------+
| v_ename |
+---------+
| SCOTT |
+---------+
1 row in set (0.00 sec)
+-------+
| v_sal |
+-------+
| 3000 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
2）in參數：
mysql> delimiter //
mysql> create procedure p2(in v_empno int)
-> begin
-> declare v_ename varchar(10);
-> declare v_sal int;
-> select ename,sal into v_ename,v_sal from emp where empno=v_empno; -> select v_ename ;
-> select v_sal;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
執行：
mysql> call p2(7788);
+---------+
| v_ename |
+---------+
| SCOTT |
+---------+
1 row in set (0.00 sec)
+-------+
| v_sal |
+-------+
| 3000 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3）out參數:
mysql> delimiter //
mysql> create procedure p3(in v_empno int,out v_sal int)
-> begin
-> declare v_ename varchar(10);
-> select ename,sal into v_ename,v_sal from emp where empno=v_empno; -> select v_ename ;
-> select v_sal;
-> end;
-> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
執行：
mysql> call p3(7788,@v_sal);
+---------+
| v_ename |
+---------+
| SCOTT |
+---------+
1 row in set (0.00 sec)
+-------+
| v_sal |
+-------+
| 3000 |
+-------+
1 row in set (0.00 sec)
4）存儲過程循環
while 循環
mysql> delimiter //
mysql> create procedure p_while()
begin
declare v_empno int;
declare v_count int;
set v_empno=7788;
set v_count=0;
while v_count < 10 do
insert into emp1 select * from emp where empno=v_empno;
set v_count=v_count + 1;
end while;
end;
//
mysql> delimiter ;
repeat 循環：
mysql> delimiter //
mysql> create procedure p_repeat()
begin
declare v_empno int;
declare v_count int;
set v_empno=7788;
set v_count=0;
repeat
insert into emp1 select * from emp where empno=v_empno;
set v_count=v_count + 1;
until v_count > 10
end repeat;
end;
//
mysql> delimiter ;
loop 循環：
mysql> delimiter //
mysql> create procedure p_loop()
begin
declare v_empno int;
declare v_count int;
set v_empno=7788;
set v_count=0;
loop_label:loop
insert into emp1 select * from emp where empno=v_empno;
set v_count=v_count + 1;
if v_count > 10 then
leave loop_label;
end if;
end loop;
end;
//
mysql> delimiter ;
三、Oracle 存儲過程
不帶參數：
SQL> create or replace procedure proc1 is
3 v_ename emp.ename%type;
4 v_sal emp.sal%type ;
5
6 begin
7 select ename,sal into v_ename,v_sal from emp where empno=&no;
8 dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
9 exception
10 when no_data_found then
11 dbms_output.put_line('you number is not crrect ,please input again !');
12
13 end;
14 /
Procedure created
執行：
SQL> exec proc1;
PL/SQL procedure successfully completed
SQL> set serverout on
SQL> /
Name is : SCOTT , Salary is : 3000
PL/SQL procedure successfully completed
帶參數的存儲過程：
參數定義中，IN、OUT和IN OUT代表參數的三種不同模式：
IN：當調用存儲過程時，該模式的形參接收對應實參的值，並且該是只讀的，即不能被修改。默認為IN。
OUT：該形參被認為只能寫，既只能為其賦值。在存儲過程中不能讀它的值。返回時，將該形參值傳給相應的實參。
IN OUT：都允許
in 參數：
SQL> create or replace procedure proc2 ( v_empno emp.empno%type) is
-- 如果不指明參數的模式，默認為in
2 v_ename emp.ename%type;
3 v_sal emp.sal%type;
4
5 begin
6 select ename,sal into v_ename,v_sal from emp where empno=v_empno;
7 dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
8 exception
9 when no_data_found then
10 dbms_output.put_line('you number is not crrect ,please input again !');
11
12 end;
13 /
Procedure created
執行：
SQL> exec proc2(7369);
Name is : SMITH , Salary is : 8000
PL/SQL procedure successfully completed
SQL> create or replace procedure proc2 ( v_empno in emp.empno%type) is
2 v_ename emp.ename%type;
3 v_sal emp.sal%type;
4
5 begin
6 select ename,sal into v_ename,v_sal from emp where empno=v_empno;
7 dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
8 exception
9 when no_data_found then
10 dbms_output.put_line('you number is not crrect ,please input again !');
11
12 end;
13 /
out 參數：
SQL> create or replace procedure proc2 ( v_empno in emp.empno%type, v_ename out emp.ename%type,v_sal out emp.sal%type)
2 is
3
4 begin
5 select ename,sal into v_ename,v_sal from emp where empno=v_empno;
6 dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
7 exception
8 when no_data_found then
9 dbms_output.put_line('you number is not crrect ,please input again !');
10
11 end;
12 /
Procedure created
執行：
SQL> var v_ename varchar2(10);
SQL> var v_sal number; //注意：對於number類型不能指明長度
SQL> exec proc2(7788,:v_ename,:v_sal );
Name is : SCOTT , Salary is : 3000
PL/SQL procedure successfully completed
v_ename
---------
SCOTT
v_sal
---------
3000
通過匿名塊執行：
SQL> var v_ename varchar2
SQL> var v_sal number
SQL> declare
2 v_empno emp.empno%type := 7788;
3 begin
4 proc2(v_empno ,:v_ename,:v_sal);
5 end;
6 /
Name is : SCOTT , Salary is : 3000
PL/SQL procedure successfully completed
v_ename
---------
SCOTT
v_sal
---------
3000
SQL> declare
2 v_empno emp.empno%type := 7836;
3 v_ename emp.ename%type ;
4 v_sal emp.sal%type;
5 begin
6 proc2(v_empno ,v_ename,v_sal);
7 end;
8 /
in out 參數：
SQL>
create or replace procedure proc3( v_empno in out emp.empno%type)
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
exception
when no_data_found then
dbms_output.put_line('you number is not crrect ,please input again !');
end;
/
執行：
SQL> var v_empno number;
SQL> exec :v_empno:=7788;
PL/SQL procedure successfully completed
v_empno
---------
7788
SQL> exec proc3(:v_empno);
Employees id is: 7788 , Name is : SCOTT , Salary is : 3000
PL/SQL procedure successfully completed
v_empno
---------
7788
SQL> declare
2 v_empno emp.empno%type;
3 begin
4 v_empno:=7788;
5 proc3(v_empno );
6 end;
7 /
Name is : SCOTT , Salary is : 3000
PL/SQL procedure successfully completed
SQL> create or replace procedure proc3( v_empno in out emp.empno%type)
2
3 is
4
5 v_ename emp.ename%type;
6 v_sal emp.sal%type;
7
8 begin
9 select empno,ename,sal into v_empno, v_ename,v_sal from emp where empno=v_empno;
10 dbms_output.put_line('Employee number is: '||v_empno||' , '||'Name is : '||v_ename||' , '||'Salary is : '||v_sal);
11 exception
12 when no_data_found then
13 dbms_output.put_line('you number is not crrect ,please input again !');
14
15 end;
16 /
Procedure created
執行：
SQL> declare
2 v_empno emp.empno%type;
3 begin
4 v_empno := 7788;
5 proc3(v_empno);
6 end;
7 /
Employee number is: 7788 , Name is : SCOTT , Salary is : 3000
PL/SQL procedure successfully completed
Oracle 存儲過程循環：
loop 基本循環：
SQL> declare
2 v_empno emp.empno%type := 7788;
3 v_count number :=0;
4
5 begin
6
7 loop
8 insert into emp1 select * from emp where empno=v_empno;
9 v_count := v_count + 1;
10 exit when v_count >= 10;
11 end loop;
12
13 end;
14 /
PL/SQL procedure successfully completed
for 循環：
SQL> declare
2
3 v_empno emp.empno%type;
4
5 begin
6 v_empno := 7788;
7
8 for i in 1..10 loop
9 insert into emp1 select * from emp where empno=v_empno;
10 end loop;
11
12 end;
13
14
15
16 /
PL/SQL procedure successfully completed
while 循環：
SQL> declare
2 v_empno emp.empno%type;
3 v_count number := 0;
4
5 begin
6 v_empno := 7788;
7
8 while v_count < 10 loop
9 insert into emp1 select * from emp where empno=v_empno;
10 v_count := v_count + 1;
11
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed
