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