Oracle的自定义函数浅析

youngfan007

　　用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。调用时如同系统函数一样，如max(value)函数，其中，value被称为参数。函数参数有3种类型。
　　IN 参数类型：表示输入给函数的参数。
　　OUT 参数类型：表示参数在函数中被赋值，可以传给函数调用程序。
　　IN OUT参数类型：表示参数既可以传值也可以被赋值。
　　１、语法格式：
　　SQL语法方式创建的语法格式为：

CREATE OR REPLACE FUNCTION function_name         /*函数名称*/

(

Parameter_name1,mode1 datatype1,　　　　　　　　　　　 /*参数定义部分*/

Parameter_name2,mode2 datatype2,

Parameter_name3,mode3 datatype3

…

)

RETURN return_datatype　　　　　　　　　　　　　　　　/*定义返回值类型*/

IS/AS

BEGIN

       Function_body　　　　　　　　　　　　　　　　　 /*函数体部分*/

      RETURN scalar_expression                        /*返回语句*/

END function_name;

　　说明：
　　function_name:：用户定义的函数名。函数名必须符合标示符的定义规则，对其所有者来说，该名在数据库中是唯一的。
　　parameter：用户定义的参数。用户可以定义一个或多个参数。
　　mode：参数类型。
　　datatype：用户定义参数的数据类型。
　　return_type:：用户返回值的数据类型。
　　函数返回scalar_expression表达式的值，function_body函数体由pl/sql语句构成。
　　２、示例
　　函数代码：

create or replace function T01001_count

return number

is

count_T01001 number;

begin

select count(*) into count_T01001 from T01001;

return(count_T01001);

end T01001_count;                  --记得一定要打分号

调用：

declare

i number;

begin

i:=T01001_count();

dbms_output.put_line(to_char(i));

end;　　　　　　　　　　　　　　　　 --记得一定要打分号

　　注意：
　　(1)    如果函数没有参数，那么函数名后不应该要括号；
　　(2)    创建函数的时候end后面一定要记得写函数名
　　--没有参数的函数
　　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 过程已成功完成。
　　--没有参数的函数
　　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>　　return v_name;
　　exception
　　when no_data_found then
　　raise_application_error(-20001, '你输入的ID无效！');
　　end get_empname;
　　--带有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>　　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语句
　　5.项目实际例子（获得用户姓名的函数）:
FUNCTION f_hfsc_get_user_name　　(
　　V_USERID INTEGER
　　)
　　RETURN VARCHAR
　　AS
　　V_USERNAME VARCHAR(100);
　　V_CNTSMALLINT;
　　BEGIN

　　SELECT NVL(SUM(1),0) INTO V_CNT FROM DUAL WHERE EXISTS(SELECT * FROM T_MK_SYS_USER WHERE>　　IF V_CNT0 THEN

　　SELECT F_CAPTION INTO V_USERNAME FROM T_MK_SYS_USER WHERE>　　ELSE

　　SELECT PERSONAL_CAPTION INTO V_USERNAME FROM T_SY_WT_PERSONAL_USER WHERE>　　END IF;
　　RETURN V_USERNAME ;
　　END;