Oracle Procedure存储过程简介

在Oracle中，可以在数据库中定义子程序，这种程序块称为存储过程（Procedure）。它存放在数据字典中，可以在不同用户和应用程序之间共享，可实现程序的优化和重用。使用存储过程的优点是：

（1）    过程在服务器端运行，执行速度快；

（2）    过程执行一次后代码就驻留在高速缓冲存储器中，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行即可，可以提高系统性能。

（3）   确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。

（4）   自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，可以自动完成一些需要预先执行的任务。

 

1、SQL命令创建Procedure：

语法格式：

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name

[(parameter parameter_mode data_type,…n)]

IS|AS

BEGIN

       Sql_statement

END procedure_name

 

说明：

（1）    procedure_name:过程名，必须符合标示符规则。关键字REPLACE表示在创建过程时，如果已存在同名的过程，则重新创建。如果使用CREATE关键字，则需将原有的过程删除后才能创建。

（2）    schema：指定过程所属的用户方案；

（3）    parameter：过程的参数。创建过程时，可以声明一个或多个参数，执行过程时应提供相对应的参数。Parameter_mode是参数的类型，过程参数和函数参数一样，也有3种类型，分别为IN、OUT和INOUT。

①    IN：表示参数是输入给过程的。

②    OUT：表示参数在过程中被赋值，可以传给过程体的外部。

③    INOUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值；

（4）    sql_statement：代表过程体包含的PL/SQL语句。

2、调用存储过程

直接输入存储过程的名字就可以执行一个已定义的存储过程。

语法格式：       [EXECUTE]procedure_name[(parameter,…n)]

 

以下举例说明：

 

如要经常执行插入，Oracle每次都要进行编译，并判断语法正确性，因此执行速度可想而知，
--所以我们要创建一个过程来实现
CREATE OR REPLACE PROCEDURE AddNewUser
(
n_id   user_info.id%TYPE,
n_name user_info.name%TYPE,
n_pwd user_info.pwd%TYPE,
n_address user_info.address%TYPE
)
AS
BEGIN
--向表中插入数据
INSERT INTO user_info(id,name,pwd,address)
VALUES(n_id,n_name,n_pwd,n_address);
END AddNewUser;
/

 

--下面我们利用PL/SQL匿名块调用该过程
DECLARE
--描述新用户的变量
v_id   user_info.id%TYPE   := 'u002';
v_name user_info.name%TYPE := 'wish';
v_pwd user_info.pwd%TYPE := 'history';
v_add user_info.address%TYPE := 'shanghai';
BEGIN
--调用过程，添加wish用户到数据库
AddNewUser(v_id,v_name,v_pwd,v_add);
DBMS_OUTPUT.PUT_LINE('用户 ' || v_name || ' 已经成功插入');
END;
/
--或者可以利用EXEC()直接插入
EXEC AddNewUser('u003','jian','jian','beijing');
--或
EXECUTE AddNewUser('u004','zhang','zhang','beijing');