Oracle 自定义函数

一、函数

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

语法:


  create [or replace] function function_name

  [(parameter_list)]

  return datatype

  {is/as}

  [local_declarations]

  begin

    executable_statements;

  [exception_handlers;]

  end;

说明:

function_name:函数名称。

parameter_list:函数列表,可选。

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

local_declarations:局部变量声明,可选。

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

exception_handlers:异常处理,可选。

or repalce:是否覆盖,可选。

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


    create or replace function function1(para1 in number, para2 in number)   
    return number   
    as   
    begin  
      if para1 > para2 then  
          return para1;  
      else  
          return para2;   
      end if;  
    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代码

    --没有参数的函数  
    create or replace function get_user return varchar2 is  
      v_user varchar2(50);  
    begin  
      select username into v_user from user_users;  
      return v_user;  
    end get_user;  

–测试

方法一

    select get_user from dual;  

方法二

    SQL> var v_name varchar2(50)  
    SQL> exec :v_name:=get_user; 
    PL/SQL 过程已成功完成。   
    SQL> print v_name  
      
    V_NAME  
    ------------------------------  
    TEST  

方法三

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

Sql代码

    --带有IN参数的函数  
    create or replace function get_empname(v_id in number) return varchar2 as  
      v_name varchar2(50);  
    begin  
      select name into v_name from employee where id = v_id;  
       return v_name;  
    exception  
      when no_data_found then  
        raise_application_error(-20001, '你输入的ID无效!');  
    end get_empname;  

附:

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

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

select text from user_source where name='GET_EMPNAME';

删除函数

drop function get_empname;
### Oracle 数据库中自定义函数的创建与使用 在 Oracle 数据库中,自定义函数是一种非常强大的工具,可以用来封装逻辑、提高代码复用性以及解决跨数据库兼容性问题。以下是关于如何创建和使用自定义函数的详细说明。 #### 创建存储函数 创建存储函数的基本语法如下: ```sql CREATE [OR REPLACE] FUNCTION 函数名 (参数列表) RETURN 函数值类型 AS -- 定义局部变量 BEGIN -- 函数体逻辑 RETURN 返回值; END; ``` - `CREATE OR REPLACE`:如果函数已经存在,则替换它。 - 参数列表:可以包含零个或多个参数,每个参数都有名称、模式(IN、OUT 或 IN OUT)和数据类型。 - `RETURN`:指定函数返回的数据类型。 #### 示例 1:无参函数 以下是一个简单的无参函数示例,用于返回当前日期[^3]: ```sql CREATE OR REPLACE FUNCTION get_current_date RETURN DATE AS BEGIN RETURN SYSDATE; END; ``` 调用该函数的方式如下: ```sql SELECT get_current_date FROM DUAL; ``` #### 示例 2:带参函数 以下是一个带参函数示例,用于统计某个部门的员工人数[^4]: ```sql CREATE OR REPLACE FUNCTION depA2(dep VARCHAR2) RETURN NUMBER AS result NUMBER; BEGIN SELECT COUNT(e.deptno) INTO result FROM SCOTT.dept d LEFT JOIN SCOTT.emp e ON d.deptno = e.deptno WHERE d.dname = dep; RETURN result; END; ``` 调用该函数时,可以传递部门名称作为参数: ```sql SELECT depA2('SALES') FROM DUAL; ``` #### 示例 3:外部函数 Oracle 支持通过外部函数调用 C/C++ 编写的动态链接库。例如,假设有一个名为 `mylib.so` 的动态链接库,其中包含一个函数 `add_numbers`,可以通过以下方式注册并调用它[^2]: ```sql CREATE LIBRARY mylib AS '/path/to/mylib.so'; CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER) RETURN NUMBER AS EXTERNAL NAME 'add_numbers' LIBRARY mylib LANGUAGE C PARAMETERS (a BY VALUE, b BY VALUE); ``` 调用此函数的方式如下: ```sql SELECT add_numbers(5, 3) FROM DUAL; ``` #### 注意事项 - 自定义函数必须包含 `RETURN` 子句,用于返回函数值[^3]。 - 如果函数需要处理复杂逻辑,可以结合 PL/SQL 块实现。 - 在编写函数时,应考虑性能优化,避免不必要的查询或循环操作。 ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值