oracle 自定义函数入门.doc

oracle 自定义函数入门博客分类： oracle用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。调用时如同系统函数一样，如max(value)函数，其中，value被称为参数。函数参数有3种类型。IN参数类型：表示输入给函数的参数。OUT参数类型：表示参数在函数中被赋值，可以传给函数调用程序。IN OUT参数类型：表示参数既可以传值也可以被赋值。、语法格式：SQL语法方式创建的语法格式为：CREATEORREPLACEFUNCTIONfunction_name/*函数名称*/(Parameter_name1,mode1 datatype1,/*参数定义部分*/Parameter_name2,mode2 datatype2,Parameter_name3,mode3 datatype3)RETURNreturn_datatype/*定义返回值类型*/IS/ASBEGIN Function_body/*函数体部分*/RETURNscalar_expression/*返回语句*/ENDfunction_name;说明：function_name:：用户定义的函数名。函数名必须符合标示符的定义规则，对其所有者来说，该名在数据库中是唯一的。parameter：用户定义的参数。用户可以定义一个或多个参数。mode：参数类型。datatype：用户定义参数的数据类型。return_type:：用户返回值的数据类型。函数返回scalar_expression表达式的值，function_body函数体由pl/sql语句构成。、示例函数代码：createorreplacefunctionT01001_countreturnnumberiscount_T01001number;beginselectcount(*)intocount_T01001fromT01001;return(count_T01001);endT01001_count;-记得一定要打分号调用：declareinumber;begini:=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_nameV_NAME-TEST方法三SQL exec dbms_output.put_line(当前数据库用户是：|get_user);当前数据库用户是：TESTPL/SQL 过程已成功完成。-没有参数的函数create or replace function get_user return varchar2 isv_user varchar2(50);beginselect 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_nameV_NAME-TEST方法三SQL exec dbms_output.put_line(当前数据库用户是：|get_user);当前数据库用户是：TESTPL/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;-带有IN参数的函数create or replace function get_empname(v_id in number) return varchar2 asv_name varchar2(50);beginselect name into v_name from employee where id = v_id;return v_name;exceptionwhen no_data_found thenraise_application_error(-20001, 你输入的ID无效！);end get_empname;附：函数调用限制1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数2、SQL只能调用带有输入参数,不能带有输出,输入输出函数3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语