`
zhuqinglin
  • 浏览: 27750 次
  • 性别: Icon_minigender_1
  • 来自: 郑州
社区版块
存档分类
最新评论

oracle存储过程

 
阅读更多


创建存储过程

存储过程定义

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

其中: procedure_name是存储过程的名字,parameter用于指定参数,model用于指定参数模式,datatype用于指定参数类型, IS [AS]用于开始PL/SQL代码块。

注:当定义存储过程的参数时,只能指定数据类型,不能指定数据长度

 


创建存储过程,需要有CREATE PROCEDURECREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下:
CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
{AS|IS}
[说明部分]
BEGIN
可执行部分
[EXCEPTION
错误处理部分]
END [过程名];
其中:
可选关键字OR REPLACE 表示如果存储过程已经存在,则用新的存储过程覆盖,通常用于存储过程的重建。
参数部分用于定义多个参数(如果没有参数,就可以省略)。参数有三种形式:INOUTIN OUT。如果没有指明参数的形式,则默认为IN
关键字AS也可以写成IS,后跟过程的说明部分,可以在此定义过程的局部变量。
编写存储过程可以使用任何文本编辑器或直接在SQL*Plus环境下进行,编写好的存储过程必须要在SQL*Plus环境下进行编译,生成编译代码,原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在Oracle环境下进行调用了。

1)建立存储过程时,既可以指定存储过程的参数,也可以不提供任何参数。

2)存储过程的参数主要有三种类型:输入参数(IN)、输出参数(OUT)、输入输出参数(IN OUT),其中IN用于接收调用环境的输入参数,OUT用于将输出数据传递到调用环境,IN OUT不仅要接收数据,而且要输出数据到调用环境。

3)在建立存储过程时,输入参数的IN可以省略。

存储过程的维护:

1)删除存储过程

DROP PROCEDURE procedure_name;

2)编译存储过程

ALTER PROCEDURE procedure_name COMPILE;

3)与存储过程相关的几个查询

--查看无效的存储过程

SELECT   object_name 

FROM       USER_OBJECTS 

WHERE   STATUS='INVALID' 

AND   OBJECT_TYPE='PROCEDURE' 

--查看存储过程的代码

SELECT   TEXT 

FROM       USER_SOURCE 

WHERE    NAME= procedure_name

其中: procedure_name是存储过程的名字

 

什么是存储过程?

 

存储过程是指用于特定操作的PL/SQL块,是由流控制和SQL语句书写的过程。

存储过程经编译和SQL优化后存储在数据库服务器中,使用时只要调用即可。在Oracle数据库中,若干个有联系的存储过程,可以组合在一起构成包。

 

存储过程的优点?

存储过程是预编译的,即创建时编译,并且经优化后存储于SQL内存中,使用时无需再次编译,提高了工作效率;调用存储过程可以大大减少同数据库的交互次数。

· 降低网络通信量,因为存储过程执行的时候,只需要call存储过程名,不需要传递大量的SQL语句。

存储过程的代码直接存放于数据库中,一般由客户端直接通过存储过程的名字进行调用,减少了网络流量,加快了系统执行速度,例如在进行百万以上的大批量数据查询时,使用存储过程分页要比其他方式分页快得多; 

· 使用存储过程可以减少SQL注入式攻击,提高了系统的安全性,执行存储过程的用户要具有一定的权限才能使用存储过程,没有数据操作权限的用户只能在其控制下间接地存取数据;

· 有利于复用。

存储过程的缺点?

· 移植性非常差,如果在oracle上写的存储过程,移植到mysql需要修改。

· 代码可读性差,实现一个简单的逻辑,代码会非常长。

存储过程的用途?

· 造测试数据:可以使用存储过程,往表里造几百万条数据。

· 数据同步:两个表之间按照一定的业务逻辑进行数据同步。

· 数据挖掘。

存储过程注意事项?

· 数据量大的时候(10+),一定要做压力测试,有些存储过程在大数据量的情况下才会出现问题。

· 如果插入或者更新的次数比较多,为了提高效率,可以执行一万次,再commit一次。

· 如果先插入记录,没有commit,再对这条记录进行更新,会引起死锁。如果先后对同一笔记录进行更新,又没有commit,也会引起死锁。因为后一条语句会等待前一条语句提交。如果出现这种情况,则需要一条条commit

· 不要忘记在存储过程里写commit

如何写存储过程?

 

Sql代码  

1 --创建或者更新存储过程update_user_p  

2 create or replace procedure update_user_p(param1 in varchar2) is  

3   v_taskName VARCHAR2(20); --定义变量,Oracle类型。  

4   v_i        number(12);  

5   --User_Advisor_Log表的结果集赋给cur  

6   CURSOR cur IS  

7     SELECT * FROM User_Advisor_Log;  

8   --sql开始标记,以上是定义变量,以下才写程序  

9 begin  

10   DBMS_OUTPUT.PUT_LINE(param1);  

11   v_i := 0;  

12   DBMS_OUTPUT.PUT_LINE('start!');  

13   --遍历结果集  

14   for cur_result in cur LOOP  

15     

16     begin  

17       v_taskName := cur_result.TASK_NAME; --将结果集赋给变量v_creator,一个语句结束需要分号结尾。  

18       

19       --if语句开始  

20       if v_taskName > 0 then  

21         begin  

22           NULL--NULL 语句表明什么事都不做,这句不能删去,因为PL/SQL体中至少需要有一句;  

23         end;  

24       end if;  

25       

26       --while循环  

27       while v_taskName > 0 LOOP  

28         begin  

29           NULL;  

30         end;  

31       end LOOP;  

32       

33       --建议每循环一万次提交一下  

34       v_i := v_i + 1;  

35       if mod(v_i, 10000) = 0 then  

36         commit;  

37       end if;  

38       

39       --有异常输出,或者在这里回滚  

40     exception  

41       when others then  

42         DBMS_OUTPUT.PUT_LINE('update_user_p has error!');  

43     end;  

44   end LOOP; --循环结束  

45   commit;  

46   DBMS_OUTPUT.PUT_LINE('end and commit!');  

47 end update_user_p;  

 

一个简单的造数据存储过程

Sql代码  

48 --往表里造40万数据。  

49 create or replace procedure vas_create_acookie_data_p is  

50   v_i number(12);  

51   

52 begin  

53   v_i := 0;  

54   while v_i < 400000 LOOP  

55     begin  

56       insert into TableName (GMT_CREATED,  

57          CREATOR,  

58          GMT_MODIFIED,  

59          MODIFIER,  

60          MEMBER_ID)  

61       values  

62         (sysdate, 'sys', sysdate, 'sys', v_i);  

63       v_i := v_i + 1;  

64       

65     end;  

66   end LOOP;  

67   commit;  

68 end vas_create_acookie_data_p;  

 

 

如何执行存储过程?

执行存储过程:call update_user_p('this is param')。在output 里可以看见DBMS_OUTPUT.PUT_LINE的输出。

 

如何调试存储过程?

 

plsql里编辑存储过程,点击执行,系统会告诉你,错误的行数和原因。并能显示代码结构。

另外可以使用DBMS_OUTPUT.PUT_LINE打印异常,注意打印异常时,输出上下文(如错误的taskName)。 

 

 

<!--EndFragment-->
分享到:
评论

相关推荐

    oracle存储过程学习经典入门

    本文将从 Oracle 存储过程的基础知识开始,逐步深入到 Oracle 存储过程的高级应用,包括 Hibernate 调用 Oracle 存储过程和 Java 调用 Oracle 存储过程的方法。 Oracle 存储过程基础知识 Oracle 存储过程是 Oracle...

    oracle存储过程unwrap解密工具.zip

    Oracle存储过程unwrap解密工具主要用于处理Oracle数据库中的加密存储过程。在Oracle数据库系统中,为了保护敏感代码或数据,开发人员有时会选择对存储过程进行加密。然而,当需要查看、调试或恢复这些加密的存储过程...

    帆软报表Oracle存储过程解决storeParameter1参数试用插件

    总结起来,"帆软报表Oracle存储过程解决storeParameter1参数试用插件"主要是针对在调用无参数Oracle存储过程时出现的异常问题提供的一种解决方案。通过安装并配置这个插件,用户可以顺利地在帆软报表中调用不包含...

    oracle存储过程解锁

    以下是对“oracle存储过程解锁”这一主题的深入解析。 ### 标题:“oracle存储过程解锁” #### 解析: 在Oracle数据库中,存储过程是一种预先编译并存储在数据库中的SQL代码块,用于执行复杂的业务逻辑或数据处理...

    Python使用cx_Oracle调用Oracle存储过程的方法示例

    本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...

    oracle存储过程-帮助文档

    Oracle存储过程是数据库管理系统中的一种重要特性,它允许开发者编写一系列SQL语句和PL/SQL块,形成可重复使用的代码单元。这篇博客“oracle存储过程-帮助文档”可能提供了关于如何创建、调用和管理Oracle存储过程...

    oracle 存储过程 函数 dblink

    ### Oracle存储过程、函数与DBLink详解 #### 一、Oracle存储过程简介 在Oracle数据库中,存储过程是一种预编译好的SQL代码集合,它可以接受输入参数、返回单个值或多个值,并能够执行复杂的数据库操作。存储过程...

    hibernate query调用oracle存储过程

    以下是一个简单的示例,展示了如何调用一个不带参数的Oracle存储过程: ```java Session session = sessionFactory.openSession(); Transaction transaction = session.beginTransaction(); session....

    springboot整合mybatis调用oracle存储过程

    本文将深入探讨如何在Spring Boot项目中整合MyBatis,实现调用Oracle存储过程并处理游标返回的数据。 首先,我们需要在Spring Boot项目中引入相关的依赖。在`pom.xml`文件中添加Oracle JDBC驱动(ojdbc66-oracle...

    Oracle存储过程中使用临时表

    本篇文章将深入探讨如何在Oracle存储过程中使用临时表,包括会话级临时表和事务级临时表。 ### 会话级临时表 会话级临时表(Session-Level Temporary Tables)只在创建它的会话内可见,并且在会话结束时自动删除。...

    ORACLE存储过程最全教程

    Oracle存储过程是数据库管理系统Oracle中的一个关键特性,它允许开发者编写一组预编译的SQL和PL/SQL语句,以实现特定的业务逻辑或数据库操作。这篇教程将深入讲解Oracle存储过程的各个方面,帮助你从基础到高级全面...

    C# 传入自定义列表List 到Oracle存储过程

    本文将详细讲解如何在C#中使用自定义列表(List)作为参数调用Oracle存储过程,以及实现这一功能的关键技术和注意事项。 首先,我们需要了解Oracle数据库中的PL/SQL类型,例如VARCAR2、NUMBER等,它们对应于C#中的...

    oracle 存储过程批量提交

    ### Oracle存储过程批量提交知识点详解 在Oracle数据库中,存储过程是一种重要的数据库对象,它可以包含一系列SQL语句和控制流语句,用于实现复杂的业务逻辑处理。存储过程不仅可以提高应用程序性能,还可以确保...

    pb中执行oracle存储过程脚本

    标题中的“pb中执行oracle存储过程脚本”指的是在PowerBuilder(简称PB)环境中调用Oracle数据库的存储过程。PowerBuilder是一种可视化的开发工具,常用于构建数据驱动的应用程序。Oracle存储过程则是在Oracle数据库...

    oracle 存储过程导出excel

    oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel

    用callabledStatement调用oracle存储过程实用例子(IN OUT 传游标)

    Oracle 存储过程调用 CallabledStatement 实用例子(IN OUT 传游标) 一、Oracle 存储过程简介 Oracle 存储过程是一种可以在 Oracle 数据库中存储和执行的程序单元。存储过程可以由多种语言编写,例如 PL/SQL、...

    Oracle存储过程返回结果集

    本篇将深入探讨如何在Oracle存储过程中创建并返回一个结果集,并结合Java代码展示如何在应用程序中使用这个结果集。 首先,我们需要理解`OUT`参数的概念。在Oracle存储过程中,`IN`参数用于传递数据到过程,`OUT`...

    SQLServer存储过程转为oracle存储过程的工具

    可以将SQL Server存储过程转为oracle存储过程的工具

    oracle存储过程常用技巧

    Oracle存储过程常用技巧 Oracle存储过程是一种强大的数据库对象,它可以帮助开发者简化复杂的业务逻辑,并提高数据库的安全性和性能。在 Oracle 中,存储过程是一种特殊的 PL/SQL 程序,它可以接受输入参数,执行...

    Oracle存储过程调用bat批处理脚本程序

    本话题将详细探讨如何在Oracle存储过程中调用外部的批处理脚本,如Windows系统的BAT文件,以实现数据库操作与系统命令的集成。 首先,`Oracle存储过程`是一种预编译的SQL和PL/SQL代码集合,可以被多次调用以执行...

Global site tag (gtag.js) - Google Analytics