创建存储过程
存储过程定义
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 PROCEDURE或CREATE ANY PROCEDURE的系统权限。该权限可由系统管理员授予。创建一个存储过程的基本语句如下:
CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
{AS|IS}
[说明部分]
BEGIN
可执行部分
[EXCEPTION
错误处理部分]
END [过程名];
其中:
可选关键字OR REPLACE 表示如果存储过程已经存在,则用新的存储过程覆盖,通常用于存储过程的重建。
参数部分用于定义多个参数(如果没有参数,就可以省略)。参数有三种形式:IN、OUT和IN 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 存储过程的高级应用,包括 Hibernate 调用 Oracle 存储过程和 Java 调用 Oracle 存储过程的方法。 Oracle 存储过程基础知识 Oracle 存储过程是 Oracle...
Oracle存储过程unwrap解密工具主要用于处理Oracle数据库中的加密存储过程。在Oracle数据库系统中,为了保护敏感代码或数据,开发人员有时会选择对存储过程进行加密。然而,当需要查看、调试或恢复这些加密的存储过程...
总结起来,"帆软报表Oracle存储过程解决storeParameter1参数试用插件"主要是针对在调用无参数Oracle存储过程时出现的异常问题提供的一种解决方案。通过安装并配置这个插件,用户可以顺利地在帆软报表中调用不包含...
以下是对“oracle存储过程解锁”这一主题的深入解析。 ### 标题:“oracle存储过程解锁” #### 解析: 在Oracle数据库中,存储过程是一种预先编译并存储在数据库中的SQL代码块,用于执行复杂的业务逻辑或数据处理...
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...
Oracle存储过程是数据库管理系统中的一种重要特性,它允许开发者编写一系列SQL语句和PL/SQL块,形成可重复使用的代码单元。这篇博客“oracle存储过程-帮助文档”可能提供了关于如何创建、调用和管理Oracle存储过程...
### Oracle存储过程、函数与DBLink详解 #### 一、Oracle存储过程简介 在Oracle数据库中,存储过程是一种预编译好的SQL代码集合,它可以接受输入参数、返回单个值或多个值,并能够执行复杂的数据库操作。存储过程...
以下是一个简单的示例,展示了如何调用一个不带参数的Oracle存储过程: ```java Session session = sessionFactory.openSession(); Transaction transaction = session.beginTransaction(); session....
本文将深入探讨如何在Spring Boot项目中整合MyBatis,实现调用Oracle存储过程并处理游标返回的数据。 首先,我们需要在Spring Boot项目中引入相关的依赖。在`pom.xml`文件中添加Oracle JDBC驱动(ojdbc66-oracle...
本篇文章将深入探讨如何在Oracle存储过程中使用临时表,包括会话级临时表和事务级临时表。 ### 会话级临时表 会话级临时表(Session-Level Temporary Tables)只在创建它的会话内可见,并且在会话结束时自动删除。...
Oracle存储过程是数据库管理系统Oracle中的一个关键特性,它允许开发者编写一组预编译的SQL和PL/SQL语句,以实现特定的业务逻辑或数据库操作。这篇教程将深入讲解Oracle存储过程的各个方面,帮助你从基础到高级全面...
本文将详细讲解如何在C#中使用自定义列表(List)作为参数调用Oracle存储过程,以及实现这一功能的关键技术和注意事项。 首先,我们需要了解Oracle数据库中的PL/SQL类型,例如VARCAR2、NUMBER等,它们对应于C#中的...
### Oracle存储过程批量提交知识点详解 在Oracle数据库中,存储过程是一种重要的数据库对象,它可以包含一系列SQL语句和控制流语句,用于实现复杂的业务逻辑处理。存储过程不仅可以提高应用程序性能,还可以确保...
标题中的“pb中执行oracle存储过程脚本”指的是在PowerBuilder(简称PB)环境中调用Oracle数据库的存储过程。PowerBuilder是一种可视化的开发工具,常用于构建数据驱动的应用程序。Oracle存储过程则是在Oracle数据库...
oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel
Oracle 存储过程调用 CallabledStatement 实用例子(IN OUT 传游标) 一、Oracle 存储过程简介 Oracle 存储过程是一种可以在 Oracle 数据库中存储和执行的程序单元。存储过程可以由多种语言编写,例如 PL/SQL、...
本篇将深入探讨如何在Oracle存储过程中创建并返回一个结果集,并结合Java代码展示如何在应用程序中使用这个结果集。 首先,我们需要理解`OUT`参数的概念。在Oracle存储过程中,`IN`参数用于传递数据到过程,`OUT`...
可以将SQL Server存储过程转为oracle存储过程的工具
Oracle存储过程常用技巧 Oracle存储过程是一种强大的数据库对象,它可以帮助开发者简化复杂的业务逻辑,并提高数据库的安全性和性能。在 Oracle 中,存储过程是一种特殊的 PL/SQL 程序,它可以接受输入参数,执行...
本话题将详细探讨如何在Oracle存储过程中调用外部的批处理脚本,如Windows系统的BAT文件,以实现数据库操作与系统命令的集成。 首先,`Oracle存储过程`是一种预编译的SQL和PL/SQL代码集合,可以被多次调用以执行...