Java-Oracle存储过程知识小题

Java-Oracle存储过程知识小题

存储过程是保存可以接受或返回用户提供参数的SQL语句集合。在日常的使用中，经常会遇到复杂的业务逻辑和对数据库的操作，使用存储过程可以进行封装。可以在数据库中定义子程序，然后把子程序存储在数据库服务器，之后通过名称调用。

一、特点：

1. 提高性能

存储过程是预先编译过，进行优化后，存储在SQL的内存中，使用的时候不需要重新编译，提高工作效率。

2. 减少网络流量

存储过程的代码直接存储在数据库中，用户通过名称进行调用，减小网络流量，加快执行速度。如：百万以上的数据查询，存储过程分页要比其他方式的分页快得多

3. 提高安全性

存储过程可以减少SQL注入攻击，提高系统的安全性。执行的过程也受到用户的身份权限控制，因此没有数据操作权限的用户只能在权限控制下间接的存储数据。

4. 事务处理机制

在同时进行主从表以及夺标的数据维护和有效性验证时，存储过程比较方便，可以有效地利用SQL的事务处理机制。

5. 分离设计编码与使用

使用存储过程，可以实现存储过程设计和编码工作分开进行，只要存储过程名、参数、及返回信息告诉编码人员即可。

6. （缺点）不易移植和修改

使用存储过程封装业务逻辑将限制应用程序的可移植性；另外，如果更改参数或者返回的数据以及类型，需要修改相关的代码，比较繁琐。

二、语法结构

完整的过程结构如下：

create to replace procedure 过程名 as

声明语句段;begin

执行语句段;

exception

异常处理语句段;end;

举例子：

-- 学生表studentcreate table student(

    sno number(6),

    sname varchar2(25),

    pno number(6) primary key

);

-- 存储过程create or replace procedure stu_proc as

p_name varchar2(25);begin

    select sname into p_name from student where sno=1;

    dbms_output.put_line(p_name);end;

-- 调用存储过程call stu_proc();

三、关于参数的类型，存储过程大致提供下面几种

1. 无参数的存储过程

定义

create or replace procedure stu_proc as pname varchar2(25);begin

    select sname into pname from student where sno=1;

    dbms_output.put_line(pname);end;

使用方法为：call stu_proc();

2. 仅有输入参数的过程

create or replace procedure stu_proc1(pno in student.sno%type) as pname varchar2(25);begin

    select sname into pname from student where sno=pno;

    dbms_output.put_line(pname);end;

使用方法为:call stu_proc1('001')

3. 仅有输出参数的过程

create or replace procedure stu_proc2(pname out student.sname%type) as begin

    select sname into pname from student where sno=1;

    dbms_output.put_line(pname);end;

这种存储过程不能直接用call调用，需要在oracle函数调用。使用方法为：call stu_proc2(name)

4. 有输入、输出的存储过程

create or replace procedure stu_proc3(pname out student.sname%type,pname out student.sname%type) as begin

    select sname into pname from student where sno=pno;

    dbms_output.put_line(pname);end;

使用方法为:call stu_proc3(name,'001')

四、存储过程的异常处理

为了提高存储过程的健壮性，避免运行错误，建立存储过程时，应该包含异常处理的部分。异常包括预定义异常、非预定义异常和自定义异常。

·预定义异常：PL\SQL提供的系统异常

·费预定义异常：用于处理与预定义异常无关的Oracle错误

·自定义异常：处理Oracle错误之外的一些异常

使用方法：

create or replace procedure stu_proc6(pno in student.sno%type,pname out student.sname%type)

    is

    begin

        select sname into pname from student where sno=pno;

    EXCEPTION

        when NO_DATA_FOUND then

        RAISE_APPLICATION_ERROR

            (-20011,'ERROR:不存在!');

end;

常用的异常处理：

命名的系统异常

产生原因

ACCESS_INTO_NULL

定义对象

CASE_NOT_FOUND

CASE中未包含相应的WHEN,并且没有设置集合元素的初始化

COLLECTION_IS_NULL

集合元素未初始化

CURSER_ALREADY_OPEN

游标已经打开

DUP_VAL_ON_INDEX

唯一索引对应的列上有重复的值

INVALID_CURSOR

在不合法的游标上进行操作

INVALID_NUMBER

内嵌的SQL语句不能讲字符穿换成数字

NO_DATA_FOUND

使用select into 未返回行，或者应用索引表未初始化的

TOO_MANY_ROWS

执行select into，结果集超过一行

ZERO_DIVIDE

除数为0

SUBSCRIPT_BEYOND_COUNT

元素下表超过嵌套表或VARRAY的最大值

SUBSCRIPT_OUTSIDE_LIMIT

使用嵌套类或VARRAY时，将下表指定为负数

VALUE_ERROR

赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED

PL\SQL应用连接到oracle时，提供了 不正确的用户名密码

NOT_LOGGED_ON

PL\SQL应用程序在没有连接oracle数据的情况下访问数据

PROGRAM_ERROR

PL\SQL内部问题，可能需要重装数据字典

ROWTYPE_MISMATCH

主游标变量与PLSQL游标变量的返回类型不兼容

SELF_IS_NULL

使用对象类型时，在null对象上调用对象方法

STORAGE_ERROR

运行PL\SQL时，超出内存空间

SYS_INVALIDE_ID

无效的ROWID字符串

TIMEOUT_ON_RESOURCE

Oracle在等待资源连接超时

五、存储过程与函数的区别

1. 在定义上

定义的名称这个就不说了，一个是FUNCTION，一个是PROCEDURE；

· 存储过程的参数列表有输入参数、输出参数、输入输出参数

· 函数的参数只有输入参数，最后会加上一个return返回值。

2. 在返回值上

· 存储过程的返回值，可以有多个

· 函数的返回值只有一个

3. 调用方式上

· 存储过程的调用方式有：exec、execute、语句块调用

· 函数的调用方式有：可以在函数块中、也可以直接在sql中使用，比如：

create or replace function add_three_numbers

(

    a NUMBER:=0,b NUMBER:=0,c NUMBER:=0

)

return number is

begin

return a+b+c;

end;

select add_three_numbers(1,2,3) from dual;

六、事务处理

1. 事务用于确保数据的一致性，要么全部确认，要不全部取消。

2. 档执行事务操作的时候，Oracle会作用在表上加锁，防止其他的用户改变表。同时也会在被作用的行上加行锁，以防止其他事务在相应行上执行DML操作。

3. 执行事务提交或者事务回滚时，Oracle会确认事务变化或回滚事务、结束事务、删除保存点、释放锁。

七、参考

Oracle存储过程

http://wenku.baidu.com/view/e56d8071be1e650e52ea99a4.html?from=search