Oracle 自定义函数

深藏阁楼爱情的钟 2021-06-26 16:06 637阅读 0赞

一、函数

函数与存储过程相似，也是数据库中存储的已命名PL-SQL程序块。函数的主要特征是它必须有一个返回值。通过return来指定函数的返回类型。在函数的任何地方可以通过return expression语句从函数返回，返回类型必须和声明的返回类型一致。

语法：

  1. 　　create [or replace] function function_name
  3. 　　[(parameter_list)]
  5. 　　return datatype
  7. 　　{
  8.    is/as}
  10. 　　[local_declarations]
  12. 　　begin
  14. 　　　　executable_statements;
  16. 　　[exception_handlers;]
  18. 　　end;

说明：

function_name：函数名称。

parameter_list：函数列表，可选。

return datatype：指定函数的返回类型，不能指定大小。

local_declarations：局部变量声明，可选。

executable_statements：要执行的PL-SQL语句。

exception_handlers：异常处理，可选。

or repalce：是否覆盖，可选。

简单的例子: 读入两个值, 返回比较大的值

  1. create or replace function function1(para1 in number, para2 in number)   
  2.     return number   
  3.     as   
  4.     begin  
  5.       if para1 > para2 then  
  6.           return para1;  
  7.       else  
  8.           return para2;   
  9.       end if;  
  10.     end function1;

二、函数和存储过程的优点：

1、共同使用的代码可以只需要被编写一次，而被需要该代码的任何应用程序调用（.net,c++,java，也可以使DLL库）。

2、这种几种编写、几种维护更新、大家共享的方法，简化了应用程序的开发维护，提高了效率和性能。

3、这种模块化的方法使得一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分，进行分别编写，因此程序的结构更加清晰，简单，也容易实现。

4、可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。

5、节省内存空间。它们以一种压缩的形式被存储在外存中，当被调用时才被放入内存进行处理。而且多个用户在调用同一个存储过程或函数时，只需要加载一次即可。

6、提高数据的安全性和完整性。通过把一些对数据的操作方到存储过程或函数中，就可以通过是否授予用户有执行该语句的权限，来限制某些用户对数据库进行这些操作。

三、函数和存储过程的区别：

1、存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。

2、存储过程声明用procedure，函数用function。

3、存储过程不需要返回类型，函数必须要返回类型。

4、存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分。

5、存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值。

6、sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。

四、适用场合：

1、如果需要返回多个值和不返回值，就使用存储过程；如果只需要返回一个值，就使用函数。

2、存储过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以再SQL内部调用函数来完成复杂的计算问题，但不能调用存储过程。

Oracle之自定义函数

数据库中函数包含四个部分：声明、返回值、函数体和异常处理。

Sql代码

  1. --没有参数的函数 
  2.     create or replace function get_user return varchar2 is  
  3.       v_user varchar2(50);  
  4.     begin  
  5.       select username into v_user from user_users;  
  6.       return v_user;  
  7.     end get_user;

–测试

方法一

  1. select get_user from dual;

方法二

  1. SQL> var v_name varchar2(50)  
  2.     SQL> exec :v_name:=get_user; 
  3.     PL/SQL 过程已成功完成。
  5. SQL> print v_name  
  7.     V_NAME  
  8.     ------------------------------ 
  9.     TEST

方法三

  1. SQL> exec dbms_output.put_line('当前数据库用户是：'||get_user);  
  2.     当前数据库用户是：TEST  
  3.     PL/SQL 过程已成功完成。

Sql代码

  1. --带有IN参数的函数 
  2.     create or replace function get_empname(v_id in number) return varchar2 as  
  3.       v_name varchar2(50);  
  4.     begin  
  5.       select name into v_name from employee where id = v_id;  
  6.        return v_name;  
  7.     exception  
  8.       when no_data_found then  
  9.         raise_application_error(-20001, '你输入的ID无效！');  
  10.     end get_empname;

附：

函数调用限制
1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数
2、SQL只能调用带有输入参数,不能带有输出,输入输出函数
3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)
4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句

查看函数院源代码
oracle会将函数名及其源代码信息存放到数据字典中user_source

  1. select text from user_source where name='GET_EMPNAME';

删除函数

  1. drop function get_empname;

发表评论

表情：
评论列表 （有 0 条评论，637人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Oracle自定义函数错误处理案例

    在Oracle数据库中，编写自定义函数时可能会遇到各种错误。这里我们将通过一个实际案例来演示如何进行错误处理。 **案例：创建一个自定义函数，计算两个日期之间的天数** `

    怼烎@怼烎@/ 0 赞/ 67 阅读

    相关 Oracle 自定义分隔函数

    今天上班时，产品经理反馈客户有一批存量数据中，biz\_type 存在多个业务键号使用逗号分隔，现在需要将该字段调整为基于逗号分隔的多行记录。 Oracle 自定义分隔函数：

    电玩女神电玩女神/ 0 赞/ 112 阅读

    相关 Oracle 自定义函数

    一、函数 函数与存储过程相似，也是数据库中存储的已命名PL-SQL程序块。函数的主要特征是它必须有一个返回值。通过`return`来指定函数的返回类型。在函数的任何地方可

    深藏阁楼爱情的钟深藏阁楼爱情的钟/ 0 赞/ 638 阅读