Oracle的存储过程

oracle存储过程

一 、存储过程说明

1）说明：

       1.存储过程是用于特定操作的pl/sql语句块

　　2.存储过程是预编译过的，经优化后存储在sql内存中，使用时无需再次编译，提高了使用效率；

　　3.存储过程的代码直接存放在数据库中，一般直接通过存储过程的名称调用，减少了网络流量，加快了系统执行效率；

2）存储过程与函数的区别：

　　1.一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

　　2.对于存储过程来说可以返回参数(output)，而函数只能返回值或者表对象。

　　3.存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

3）存储过程的优点：

　　1.执行速度更快 – 在数据库中保存的存储过程语句都是编译过的

　　2.允许模块化程序设计 ，程序的可移植性更强– 类似方法的复用（使用存储过程可以实现存储过程设计和编码工作的分开进行，只要将存储过程名、参数、返回信息等告诉编程人员即可）；

　　3.提高系统安全性 – 防止SQL注入 （执行存储过程的用户要具有一定的权限才能使用存储过程）

　　4.减少网络流通量 – 只要传输存储过程的名称（在大批数据查询时使用存储过程分页查询比其他方式的分页要快很多）

　　5.在同时进行逐主、从表间的数据维护及有效性验证时，使用存储过程更加方便，可以有效的利用SQL中的事务处理机制

　二、语法

         CREATE [OR REPLACE] PROCEDURE procedure_name
         [(parameter1[model] datatype1, parameter2 [model] datatype2..)]
         IS[AS]
         BEGIN
         PL/SQL;
         END [procedure_name];

说明：

　　1. parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型

　　2. 定义存储过程的参数时，只能指定数据类型，不能指定数据长度

　　3. IS/AS用于开始PL/SQL代码块

　　4. 创建存储过程时，既可以指定参数也可以不指定任何参数；

　　5. 存储过程参数：1）输入参数 IN   　IN用于接收调用环境的输入参数（创建存储过程时，输入参数的IN可以省略）

　　　　　　　　　　 2) 输出参数 OUT 　OUT用于将输出数据传递到调用环境

　　　　　　　　　　 3) 输入输出参数（IN OUT）其中IN用于接收调用环境的输入参数，OUT用于将输出数据传递到调用环境

2）删除存储过程

        DROP PROCEDURE procedure_name;

3)编译存储过程

        ALTER PROCEDURE procedure_name COMPILE

三、示例

示例一：无参无返

create or replace procedure p1
--or replace代表创建该存储过程时，若存储名存在，则替换原存储过程，重新创建
--无参数列表时，不需要写()
as
begin
  dbms_output.put_line('hello world');
end;

--执行存储过程方式1
set serveroutput on;
begin
  p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();

示例二：有参有返

create or replace procedure p2
(name in varchar2,age int,msg out varchar2)
--参数列表中，声明变量类型时切记不能写大小，只写类型名即可，例如参数列表中的name变量的声明
--参数列表中，输入参数用in表示，输出参数用out表示，不写时默认为输入参数。
------------输入参数不能携带值出去，输出参数不能携带值进来，当既想携带值进来，又想携带值出去，可以用in out
as
begin
  msg:='姓名'||name||',年龄'||age;
  --赋值时除了可以使用：=，还可以用into来实现
  --上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;
end;
--执行存储过程
set serveroutput on;
declare
  msg varchar2(100);
begin
  p2('张三',23,msg);
  dbms_output.put_line(msg);
end;

示例三：参数列表中有in out参数

(msg in out varchar2)
--当既想携带值进来，又想携带值出去，可以用in out
as
begin
  dbms_output.put_line(msg); --输出的为携带进来的值
  msg:='我是从存储过程中携带出来的值';
end;

--执行存储过程
set serveroutput on;
declare
  msg varchar2(100):='我是从携带进去的值';
begin
  p3(msg);
  dbms_output.put_line(msg);
end;

示例四：存储过程中定义参数

create or replace procedure p4
as
  --存储过程中定义的参数列表
  name varchar(50);
begin
  name := 'hello world';
  dbms_output.put_line(name);
end;
---执行存储过程
set serveroutput on;
execute p4();

 总结：1.创建存储过程的关键字为procedure。

　　    2.传参列表中的参数可以用in，out，in out修饰，参数类型一定不能写大小。列表中可以有多个输入输出参数。

　　    3.存储过程中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。

　　　 4.as可以用is替换。

　　    5.调用带输出参数的过程必须要声明变量来接收输出参数值。

　　    6.执行存储过程有两种方式，一种是使用execute，另一种是用begin和end包住。

　　    存储过程虽然有很多优点，但是它却不能使用return返回值。当需要使用return返回值时，我们可以使用函数。