【Oracle系列】- 存储过程(Stored Procedure)

这篇具有很好参考价值的文章主要介绍了【Oracle系列】- 存储过程(Stored Procedure)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

【Oracle系列】- 存储过程(Stored Procedure)

一、什么是存储过程

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,存储过程在数据库中,经过第一次编译后再次调用不需要二次编译。用户通过指定的存储过程名称应传给参数来调用完成。

存储过程就是解决特定问题,有先后顺序的一组SQL语句集合。

在Oracle数据库中存储过程是Procedure。

二、存储过程优缺点

  • 存储过程优点
    1. 效率高

      存储过程编译一次后,就会存到数据库,每次调用时都直接执行。而普通的SQL语句在代码中,都要先分析编译才会执行。所以想对而言存储过程效率更高。

    2. 降低网络流量

      存储过程编译好会放在数据库,我们在远程调用时,不会传输大量的字符串类型的SQL语句。

    3. 复用性高

      存储过程往往是针对一个特定的功能编写的,当再需要完成这个特定的功能时,可以再次调用该存储过程。

    4. 可维护性高

      当功能要求发生小的变化时,修改之前的存储过程比较容易,花费精力少。

    5. 安全性高

      完成某个特定功能的存储过程一般只有特定的用户可以使用,具有使用身份限制,更安全。

  • 存储过程缺点
    1. 增加数据库负荷: 存储过程编译存储在数据库服务端增加了数据库的负荷。
    2. 存储过程编辑编译困难:因存储过程编写一般在DBMS中完成,相对于代码编辑器,调试编写都不那么友好。
    3. 可移植性差:不同的数据库有独特的编写方法,不同达到一次编译多个数据库同时使用。

三、存储过程创建示例

3.1 创建语法
create or replace procedure schema.过程名(变量名 in|out 数据类型) 
is | as -- 此处 is 和 as 等同
   声明语句段;
begin
   执行语句段;
exception 
   异常处理语句段;
end 过程名;
3.2 创建存储过程步骤

Oracle存储过程大体分为这么几个部分:

  1. 创建语句:create or replace procedure 存储过程名

    如果没有or replace语句,则仅仅是新建一个存储过程。如果系统存在该存储过程,则会报错。Create or replace procedure 如果系统中没有此存储过程就新建一个,如果系统中有此存储过程则把原来删除掉,重新创建一个存储过程。

  2. 存储过程名定义:包括存储过程名和参数列表

    参数名和参数类型。参数名不能重复, 参数传递方式:IN, OUT, IN OUT

    IN 表示输入参数,按值传递方式。

    OUT 表示输出参数,可以理解为按引用传递方式。可以作为存储过程的输出结果,供外部调用者使用。

    IN OUT 即可作输入参数,也可作输出参数。

    • 参数的数据类型只需要指明类型名,不需要指明参数类型
    • 参数类型的宽度由外部调用者决定
    • 过程可以有参数,也可以没有参数
  3. 变量声明块

    紧跟着的as (is )关键字,可以理解为pl/sql的declare关键字,用于声明变量;

    变量声明块用于声明该存储过程需要用到的变量,它的作用域为该存储过程。另外这里声明的变量必须指定宽度。遵循PL/SQL的变量声明规范。

  4. 过程语句块

    从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。

  5. 异常处理块

    关键字为exception ,为处理语句产生的异常。该部分为可选

  6. 结束块

    由end关键字结果

3.3 创建一个简单无参存储过程
create or replace procedure pro_noparameters
is
  v_depte_id integer:= '';
begin
   select dept_id into v_depte_id  from sys_dept where dept_id = 100;
   dbms_output.put_line('v_depte_id:' || v_depte_id);
end;
3.4 创建一个有入参的存储过程
create or replace procedure pro_parameters(
   in_deptid in number
)
as
  v_dept_name varchar2(30):='';
begin
   select DEPT_NAME into v_dept_name  from sys_dept where dept_id= in_deptid;
   dbms_output.put_line('v_dept_name:' || v_dept_name);
end pro_parameters;
3.5 创建一个存储过程并返回结果集
create or replace procedure pro_showdata(data out sys_refcursor)
as
 begin
    open data for  select * from sys_dept;
 end pro_showdata;
3.6 存储过程中SELECT INFO

查出来一个结果集合赋值给一个集合变量。

  • 语法
SELECT field BULK COLLECT INTO var_conllect FROM table where whereColStatement;
  • 参数说明:
序号 参数 说明
1 field 要查询的字段,可以是一个或多个(要保证和后面的集合变量要向对应)
2 var_collect 集合变量(联合数组等),用来存放查到的结果
3 table 表名,要查询的表
4 whereColStatement 后面过滤条件语句
3.7 存储过程中FETCH INTO

从一个集合中抓取一部分数据赋值给一个集合变量

  • 语法
FETCH cur1 BULK COLLECT INTO var_collect [LIMIT rows]
  • 参数说明
序号 参数 说明
1 cur1 数据集合
2 var_collect 数据集合
3 [LIMIT rows] 可有可无,限制每次抓取的数据量。不写的话,默认每次一条数据
3.8 存储过程中RETURNING

BULK COLLECT除了与SELECT,FETCH进行批量绑定之外,还可以与INSERT,DELETE,UPDATE语句结合使用,可以返回这些DML语句执行后所影响的记录内容(某些字段)

  • 语法:
DMLStatement
       RETURNING field BULK COLLECT INTO var_field;
  • 参数说明:
序号 参数 说明
1 DMLStatement 是一个DML语句
2 field 是这个表的某个字段,当然也可以写多个逗号隔开(field1,field2, field3)
3 var_field 一个类型为该字段类型的集合,多个的话用逗号隔开,如:(var_field1, var_field2, var_field3)
3.9 存储过程中FORALL
  • 语法:
FORALL index IN bounds [SAVE EXCEPTIONS]     
     sqlStatement;
  • 参数说明:
序号 参数 说明
1 index 执行过程游标
2 bounds 游标边界,形式是start…end
3 [SAVE EXCEPTIONS] 可选值,(后面详细介绍)
4 sqlStatement 一个DML语句,这里有且仅有一个sql语句
3.10 存储过程中SAVE EXCEPTIONS

在执行DML时,遇到异常,导致某个语句或整个事务回滚。如果我们写FORALL语句时没有用SAVE EXCEPTIONS语句,那么DML语句会在执行到一半的时候停下来;如果我们的FORALL语句后使用了SAVE EXCEPTIONS语句,当在执行过程中如果遇到异常,数据处理会继续向下进行,发生的异常信息会保存到SQL%BULK_EXCEPTONS的游标属性中,该游标属性是个记录集合,每条记录有两个字段分别是:ERROR_INDEX和ERROR_CODE

ERROR_INDEX:该字段会存储发生异常的FORALL语句的迭代编号

ERROR_CODE:存储对应异常的,oracle错误代码

SQL%BULK_EXCEPTONS这个异常信息总是存储着最近一次执行的FORALL语句可能发生的异常。

而这个异常记录集合异常的个数则由它的COUNT属性表示,即:SQL%BULK_EXCEPTONS.COUNT,SQL%BULK_EXCEPTIONS有效的下标索引范围在1到%BULK_EXCEPTIONS.COUNT之间。

3.11 存储过程中 INDICES OF

在Oracle数据库10g之前有一个重要的限制,该数据库从IN范围子句中的第一行到最后一行,依次读取集合的内容,如果在该范围内遇到一个未定义的行,Oracle数据库将引发ORA-22160异常事件:ORA-22160: element at index [N] does not exist。针对这一问题,Oracle后续又提供了两个新语句:INDICES OF 和 VALUES OF

  • 语法:
FORALL i INDICES OF collection [SAVE EXCEPTIONS]

       sqlStatement;
3.12 存储过程中VALUES OF

VALUES OF适用情况:绑定数组可以是稀疏数组,也可以不是,但我只想使用该数组中元素的一个子集。VALUES OF选项可以指定FORALL语句中循环计数器的值来自于指定集合中元素的值。但是,VALUES OF在使用时有一些限制:

如果VALUES OF子句中所使用的集合是联合数组,则必须使用PLS_INTEGER和BINARY_INTEGER进行索引,VALUES OF 子句中所使用的元素必须是PLS_INTEGER或BINARY_INTEGER;

当VALUES OF 子句所引用的集合为空,则FORALL语句会导致异常;

  • 语法:
FORALL i IN VALUES OF collection [SAVE EXCEPTIONS]
       sqlStatement;

四、总结

Oracle存储过程相对于Sql Service 和 Mysql比较复杂,难度较大。在解决实际问题需要根据场景选择响应的存储过程方案。文章来源地址https://www.toymoban.com/news/detail-464144.html

到了这里,关于【Oracle系列】- 存储过程(Stored Procedure)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • oracle存储过程(基础语法)

            存储过程(Stored Procedure )是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过

    2023年04月08日
    浏览(44)
  • oracle古法unwrap手艺(oracle存储过程解码)

    首先oracle官方是不支持解包的,见 Doc ID 376303.1 但是需求来了。我就寄希望于民间大神的工具。很顺利,找到了几个,甚至还有网页版,以为是个easy money。 但是,我点背,总是能遇到精彩的情况。数据库环境约是10年前从9i升级到11g的。没错这个wrap的存储过程是9i下生成的。

    2024年01月24日
    浏览(50)
  • Oracle存储过程~封神之作

    Oracle 存储过程是 Oracle 数据库中的一种数据处理对象,它可以在数据库中定义一组预定义的 SQL 语句,用于完成特定的数据库操作。存储过程可以被授权的用户调用,并且可以执行多个语句,这些语句可以被视为一个单独的操作,也可以被视为一系列的操作。 使用存储过程可

    2024年02月03日
    浏览(46)
  • Java调用Oracle存储过程

    0、序言 使用Java实现存储过程的步骤: 1、创建数据表、存储过程 2、引入依赖包、配置数据源 3、Java代码实现“存储过程调用” 1、创建数据表、存储过程 (1)创建数据表 (2)创建存储过程(Oracle 存储过程与游标sys_refcursor的结合使用) Oracle 存储过程与游标的相关知识点

    2023年04月08日
    浏览(54)
  • Oracle中 Procedure sql%notfound

    sql%notfound 是一个布尔值,与最接近的sql语句(update、insert、delete、select)发生交互,当最近的一条sql语句没有涉及到任何行的时候,返回true,否则返回false。 这样的语句在实际应用中,是非常有用的。例如要update一行数据时,如果没有找到,就可以作相应操作。 当update em

    2024年02月05日
    浏览(41)
  • Oracle数据库创建存储过程

    下面是一个迁移数据库数据的存储过程: 存储过程中用到的 while 循环逻辑:  

    2024年02月11日
    浏览(42)
  • oracle 之存储过程 begin ...... ; ...... end

    点击查看代码

    2024年02月08日
    浏览(51)
  • oracle 自定义存储过程(非常简单明了)

    1.举一个简单的例子 定义存储过程 easyProcedure 入参为 两个数 出参为 他们的和 使用存储过程接着往下看 2.举一个复杂的例子(例子比较复杂,但很实用) ①创建 TYPE 类型 atrr_type ②将 TYPE 类型 atrr_type 定义为表, 用做接收返回值 ③定义存储过程 入参为 objt 出参为 一张表 ④

    2024年02月09日
    浏览(36)
  • Oracle SQL存储过程能够返回表吗

    在Oracle存储过程中,我们可以使用游标来返回表的数据。游标是一种类似于指针的数据类型,可以用来遍历和操作结果集。以下是一个示例的Oracle存储过程,通过游标返回表数据: SQL Copy 在这个存储过程中,我们声明了一个游标变量employee_cursor,并将SELECT语句的结果集赋值给

    2024年02月13日
    浏览(44)
  • oracle中建立job定期运行存储过程 参数

     Plsql developer dbms schedual job里面编辑也可以 手动方式: 1 首先查看 SQL show parameter job NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes                  integer     10 如果值是 0 需要修改(如

    2024年02月07日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包