PL/SQL存储过程使用手册
上一篇 / 下一篇 2007-12-10 15:43:18 / 个人分类：SQL
存储过程使用手册软件测试网(k7A0L]&A0KI R
一、存储过程概述
1. 存储过程定义
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值，不同数据库存储过程的写法是不一样的。（简单点说存储过程就是由一组SQL语句和可选控制流语句的预编译集合。）
2. 存储过程的优点
使用存储过程有以下的优点： 软件测试网%UM J6h B s
1、存储过程大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
2、可保证数据的安全性和完整性。
1）通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
2）通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
3、在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。
4、可以降低网络的通信量。
5、使体现企业规则的运算程序放入数据库服务器中，以便：
1）集中控制。
2）当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。
二、ORACLE的存储过程使用方法
1. ORACLE创建存储过程的基本语法：
1）基本格式：
create [or replace] procedure 存储过程名
参数1 [in|out|in out] 数据类型
[,参数2 [in|out|in out] 数据类型]......
{is|as} pl/sql语句
begin
end 存储过程名
%q D8z2tLe+] @L t02）SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
S*?o r"M1F?0例子：
7sy X%d hcWj!X E0BEGIN
#{wH,|3Nj#I0SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;软件测试网eY0W~0Dd
EXCEPTION软件测试网 W B,Z_PM@ZZ
WHEN NO_DATA_FOUND THEN软件测试网+b8}b(V8x_(H ~0z5W+cp
xxxx;软件测试网-a7~b`hr/p&yC
END;
VKja P$b"c+G0...
GJ)E+P;o@g;j)m V J03）IF 判断
IF V_TEST=1 THEN
-] p7h3N1o!od N0BEGIN 软件测试网'p q9{RM/r9v}
XXXX
^/? Fl L;Z^a0END;软件测试网(~ W/^v+@Z$Lu
END IF;软件测试网&K7ke3wt
4）while 循环
WHILE V_TEST=1 LOOP
m^U&j KN#m4}0BEGIN
(k:MvpSI^ F9j0XXXX
mW U+L7JVB]0END;软件测试网Vp uG Z,T3_L vL
END LOOP;
)~u ['SQ ? A05）变量赋值
V_TEST := 123;软件测试网9N)F6P,ffCl0I
6）用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
7）带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;软件测试网3J1O*q[$P {/flij(V)q
2. 建立存储过程
1. 通过命令行，直接在sql窗口创建一个存储过程。
1）打开一个sql窗口，如图1
$l4Y/X` p.TL0图1
2）在sql窗口输入命令行并按f8运行，一个存储过程即可创建成功，如图2、图3软件测试网,Qe,|M9F!h4d8l+Q
（建立一个表tt1有a、b、c三个属性，创建一个存储过程，将a列字符串的大写字符串和小写字符串分别插入到b列、c列）
a)wUVC(D5\0图2
图3
2. 在sql窗口创建一个表后，通过左侧菜单创建。
1）在sql窗口创建一个表。如图4
CV&u Vw)bTG m0（创建一个表tt2）
$H C x\L5~]0图4
2）右击左侧菜单procedures，选择new，如图5
;J Q$z acB9H0图5
弹出如下窗口：软件测试网-Z?*U UZ&?G | l Ex u
图6
填写要创建的存储过程名和参数（Name:p_test2,Parameters: i in varchar2），点击ok。软件测试网FAHD'C3fXz }-Zj
3）在弹出的窗口内填写要进行编译的sql语句，并F8，一个存储过程即创建成功。如图7
r4@9{'Y4c&eZ\"X0（创建了一个功能跟p_test1相同的存储过程p_test2）软件测试网"^` U7r*j}
图7
3. 运行存储过程
1）选择存储过程名点击右键，选择test。如图8
x XO^y gD Y s A^0图8
2）输入需要输入的数值，按F8快捷键即可运行已有存储过程。如图9软件测试网 KS K,w_$^
图9
3）可通过查询表状态查看存储过程运行结果。如图10
:rWhe4s b0图10
4. 调用存储过程
1. CALL命令
CALL procedure_name[(parameter,...n)]
调用p_test1，i=‘sTEst’如图11
图11
运行结果查询如图12
图12
2. 编写PL/SQL语句执行软件测试网[Q7XcQ%UB u1h T
DECLARE软件测试网 [\kD&n/l~
...软件测试网 B]6N&Q%V
BEGIN
-{([+X8Sn0procedure_name[(parameter,...n)]软件测试网X,gxI R7s2]5K
END;
oo!@2v _0或软件测试网3V'SO!I0f:g
BEGIN
l!R;g^8[8D"B1QcDN0procedure_name[(parameter,...n)]
9W4_zn^7n0END;
:E;|FaH9kRx0调用p_test1，i=‘Test’如图13
图13
运行结果查询如图14
图14
调用p_test1，i=‘another Test’如图15
图15
运行结果查询如图16
图16
5. 修改存储过程
修改存储过程和修改视图一样,虽然也有ALTER PROCEDURE语句,但是它是用于重新编译或验证现有过程的.
如果要修改过程定义,仍然要使用 CREAE 或 REPLACE 命令,语法格式一样.
6. 删除存储过程
1. 通过命令行删除存储过程。
DROP PROCEDURE[schema.]procedure_name;
删除存储过程p_test2,如图17
导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报
TAG:
