Java程序员的日常——存储过程知识普及_xing halo的博客-程序员宅基地

Java程序员的日常——存储过程知识普及

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

特点

1 提高性能

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

2 减少网络流量

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

3 提高安全性

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

4 事务处理机制

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

5 分离设计编码与使用

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

6 (缺点)不易移植和修改

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

语法结构

完整的过程结构如下:

create to replace procedure 过程名 as 
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end;

举例子:

-- 学生表student
create 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();

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

无参数的存储过程

定义

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();

仅有输入参数的过程

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')

仅有输出参数的过程

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)

有输入、输出的存储过程

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在等待资源连接超时

存储过程与函数的区别

在定义上

定义的名称这个就不说了,一个是FUNCTION,一个是PROCEDURE;

  • 存储过程的参数列表有输入参数、输出参数、输入输出参数
  • 函数的参数只有输入参数,最后会加上一个return返回值。

在返回值上

  • 存储过程的返回值,可以有多个
  • 函数的返回值只有一个

调用方式上

  • 存储过程的调用方式有: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会确认事务变化或回滚事务、结束事务、删除保存点、释放锁

参考

1 Oracle存储过程

posted @ 2016-09-10 11:20 xingoo 阅读( ...) 评论( ...) 编辑 收藏
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/xingoo_/article/details/86143189

智能推荐

spring boot Common Application properties-1_weijiasihai的博客-程序员宅基地

Common Application propertiesVarious properties can be specified inside yourapplication.propertiesfile, inside yourapplication.ymlfile, or as command line switches. This appendix provides a list...

apache在线升级yum_centos7下升级apache至最新版本_weixin_39851408的博客-程序员宅基地

第一步:找出默认软件库上的Apache版本为了查找Centos上软件库里的Apache版本,在命令行下输入以下指令:yum info httpd如果你的centos保持着默认的配置的话,你很可能会按到类似的结果:[[email protected] ~]# yum info httpdLoaded plugins: fastestmirrorepel/x86_64/metalink ...

如何做好一名合格的项目组长_李胜林的博客-程序员宅基地_项目组长需要做些什么

这里指向的项目组长,不局限于某个行业,但我的依据仍然是相对软件行业来说的,对每一个有追求的程序员来说,这也是他们必然会经历的一个阶段。简单介绍一下我,曾经带领过一些团队,虽然没有很丰富的管理经验,但是对项目组长职位还算比较熟悉,也正因为,最近换到一个新的公司,在熟悉的过程中也发现了这家公司在管理上存在的一些问题,虽然不是普遍存在,但也值得防范。很多刚上位的组长都认为,无外乎,组长的职责就是分配任务...

oracle数据库插入一条记录同时返回该记录的id值_绿源2008的博客-程序员宅基地_oracle每插入一条数据就输出记录数

/**  * 新增书籍类型  * @param bookType 书籍类型对象  * @return 书籍类型id  * @throws SQLException  */ public int addBookType(BookType bookType) throws SQLException{  Connection con = MyDBUtil.getConnect

java中equals和==之间的区别?clone方法的作用,及其为什么要使用clone方法?如何使用clone复制对象?以及深克隆浅克隆_清浅是我呀的博客-程序员宅基地

在选择是否原创时,我选择了原创,当然这些内容是我们高博的老师讲的,但是确实我一个字一个字打出来的,也算是我的理解吧。1.“==”在进行数据比较时, 如果数据是基本数据类型,比较的是数据的值;如果数据是引用类型,“==”比较的是对象的内存地址。equals方法,在比较数据是否相同时,只能比较引用类型,所在Object类类中,equals方法额实现,是比较内存直至,如果我们想要比较对象的值,必须重写e...

Linux IO子系统和文件系统读写流程_jasonLee_lijiaqi的博客-程序员宅基地

我们所有分析的,是基于2.6.32及其后的内核.我们在Linux上总是要保存数据,数据要么保存在文件系统里(如ext3),要么就保存在裸设备里。我们在使用这些数据的时候都是通过文件这个抽象来访问的,操作系统会把我们需要的数据提交给我们,而我们则无需和块设备打交道。从下图,我们可以清除的看到:I/O子系统是个层次很深的系统,数据请求从用户空间最终到达磁盘,经过了复杂的数据流动。对设驱开发人...

随便推点

Java处理UTF-8文件的BOM头部_快乐领读的博客-程序员宅基地_utf-8 bom头

Java处理UTF-8文件的BOM头部BOM——Byte Order Mark,就是字节序标记。基本概念在UCS 编码中有一个叫做”ZERO WIDTH NO-BREAK SPACE“的字符,它的编码是FEFF。而FFFE在UCS中是不存在的字符,所以不应该出现在实际传输中。UCS规范建议我们在传输字节流前,先传输 字符”ZERO WIDTH NO-BREAK SPACE“。如果接收者收到FEFF,就表明这个字节流是大字节序的;如果收到FFFE,就表明这个字节流是小字节序的。因此字符”ZERO

计算机应用技术目标地域分析100字,【计算机应用论文】威客模式计算机应用论文(共3100字)..._未来的一个可爱富婆的博客-程序员宅基地

一、威客模式在高职计算机应用能力课程教学的可行性分析(一)威客模式构建理论一体运用威客模式开展教学,以真实工作情境、具体实际的威客任务为出发点,能让学生在学中做、做中学,在学、做中掌握知识,练就技能,培养态度,陶冶情操,实现教学过程的“教、学、做”一体,提高学生的实践能力,最终达到理论与实践相结合的目的。(二)威客模式增强团队意识运用威客模式开展教学,将学生分成若干项目小组,威客任务的分析、设计、...

python回声程序echo 一行代码_Python Socket 编程示例 Echo Server_赵小王的博客-程序员宅基地

简评:我们已经从「Python Socket 编程概览」了解了 socket API 的概述以及客户端和服务器的通信方式,接下来让我们创建第一个客户端和服务器,我们将从一个简单的实现开始,服务器将简单地回显它接收到客户端的任何内容。本文将详细解释服务器部分的代码。下面是服务器代码保存到 echo-server.py文件:#!/usr/bin/env python3import socketHOST...

编译安装mysql与mysql error 解决之道_weixin_33804990的博客-程序员宅基地

2019独角兽企业重金招聘Python工程师标准>>> ...

java后台格式化时间以及前台vue格式化时间(时钟计数器)_那一抹阳光爱发呆的博客-程序员宅基地

1.后台格式化时间SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String currentDate = df.format(entity.getEffectDate());2.vue前台// 获取当前时间函数nowTimes() {let year = new Date().getFul...

UITextView 与 keyboard 处理_weixin_33841722的博客-程序员宅基地

2019独角兽企业重金招聘Python工程师标准>>> ...

推荐文章

热门文章

相关标签