java面试问题：oracle存储过程（procedure)和函数(function)的区别

- 日理万妓 2022-05-13 19:05 228阅读 0赞

**oracle存储过程（procedure)和函数(function)的区别**

<table> 
 <thead> 
  <tr> 
   <th>存储过程</th> 
   <th>函数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>用于在数据库中完成特定的操作或者任务（如插入、删除等）</td> 
   <td>用于特定的数据（如选择）</td> 
  </tr> 
  <tr> 
   <td>程序头部声明用procedure</td> 
   <td>程序头部声明用function</td> 
  </tr> 
  <tr> 
   <td>程序头部声明时不需描述返回类型</td> 
   <td>程序头部声明时要描述返回类型，而且PL/SQL块中至少要包含一个有效的return语句</td> 
  </tr> 
  <tr> 
   <td>可以使用in/out/in out 三种模式的参数</td> 
   <td>可以使用in/out/in out 三种模式的参数</td> 
  </tr> 
  <tr> 
   <td>可作为一个独立的PL/SQL语句来执行</td> 
   <td>不能独立执行，必须作为表达式的一部分调用</td> 
  </tr> 
  <tr> 
   <td>可以通过out/in out返回0个或多个值</td> 
   <td>通过return语句返回一个值，且该值要与声明部分一致，也可以是通过out类型的参数带出的变量</td> 
  </tr> 
  <tr> 
   <td>SQL语句（DML或SELECT）中不可调用存储过程</td> 
   <td>SQL语句（DML或SELECT）中可以调用存储过程</td> 
  </tr> 
 </tbody> 
</table>

定义：

*  存储过程（Stored Procedure ）是一组为了完成特定功能的SQL  
    语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
    
    存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。
    
    存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。
    
    在Oracle 中，若干个有联系的过程可以组合在一起构成程序包。

优 点：

*  1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
    
    2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
    
    3.存储过程可以重复使用,可减少数据库开发人员的工作量。
    
    4.安全性高,可设定只有某用户才具有对指定存储过程的使用权。

存储过程的优缺点

*  优点：减少网络交互的成本。  
    缺点：  
    1.不可移植性，每种数据库的内部编程语法都不太相同，当你的系统需要兼容多种数据库时，最好不要用存储过程。
    
    2.学习成本高，DBA一般都擅长写存储过程，但并不是每个程序员都能写好存储过程，除非你的团队有较多的开发人员熟悉写存储过程，否则后期系统维护会产生问题。  
    3.业务逻辑多处存在，采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理，这种架构会增加一些系统维护和调试成本。  
    4.存储过程和常用应用程序语言不一样，它支持的函数及语法有可能不能满足需求，有些逻辑就只能通过应用程序处理。  
    5.如果存储过程中有复杂运算的话，会增加一些数据库服务端的处理成本，对于集中式数据库可能会导致系统可扩展性问题。
    
    6.为了提高性能，数据库会把存储过程代码编译成中间运行代码(类似于Java的class文件)，所以更像静态语言。当存储过程引用的对像(表、视图等等)结构改变后，存储过程需要重新编译才能生效，在24\*7高并发应用场景，一般都是在线变更结构的，所以在变更的瞬间要同时编译存储过程，这可能会导致数据库瞬间压力上升引起故障。

区别：

*  函数：只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。执行的本质都一样。  
    函数限制比较多，比如不能用临时表，只能用表变量还有一些函数都不可用等等。而存储过程的限制相对就比较少。  
    1.一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。  
    2.对于存储过程来说可以返回参数，而函数只能返回值或者表对象。  
    3.存储过程一般是作为一个独立的部分来执行（EXEC执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。  
    4.当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。 Procedure cache中保存的是执行计划 (execution plan) ，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。