Oracle 自定义函数

tangshan 贡献于2012-08-14

作者 USER  创建于2011-04-13 12:20:00   修改者USER  修改于2011-04-13 12:21:00字数3844

文档摘要:函数用于返回特定数据。执行时得找一个变量接收函数的返回值;
关键词:

Oracle自定义函数 2010年01月18日 星期一 下午 01:50 函数用于返回特定数据。执行时得找一个变量接收函数的返回值; 语法如下:     create or replace function function_name ( argu1 [mode1] datatype1, argu2 [mode2] datatype2, ........ ) return datatype is begin end; 执行 var v1 varchar2(100)       exec :v1:=function_name =============================================================================== 不带任何参数 =============================================================================== create or replace function get_user return varchar2 is Result varchar2(50); begin select username into Result from user_users; return(Result); end get_user; 执行: =============================================================================== 带in参数的 =============================================================================== create or replace function get_sal(empname in varchar2) return number is Result number; begin select sal into Result from emp where ename=empname; return(Result); end get_sal; 执行: SQL> var sal number SQL> exec :sal:=get_sal('scott'); =============================================================================== 带out参数的函数 =============================================================================== create or replace function get_info(e_name varchar2,job out varchar2) return numbe r is Result number; begin select sal,job into Result,job from emp where ename=e_name; return(Result); end get_info; 执行: SQL> var job varchar2(20) SQL> var dname varchar2(20) SQL> exec :dname:=get_info('SCOTT',:job) =============================================================================== 带in out参数的函数 =============================================================================== create or replace function result(num1 number,num2 in out number) return number is v_result number(6); v_remainder number; begin v_result :=num1/num2; v_remainder :=mod(num1,num2); num2 :=v_remainder; return(v_result); Exception     when zero_divide then        raise_application_error(-20000,'不能除0'); end result; 执行: var result1 number;       var result2 number;       exec :result2:=30       exec :result1:=result(100,:result2) eg: 1 、一个最简单的自定义函数Fun_test1的定义。 create or replace function Fun_test1(p_1 number)--Fun_test1是函数名,有一个输入参数p_1,是number型的。返回值也是number型的 return number IS begin if p_1>0 then return 1; el sif p_1=0 then return 0; else return -1; end if; end; --这个函数只是可以知道自定义函数的定义和格式。其实没什么用途。 2、Fun_test1自定义函数的调用的存储过程Pro_Fun_test1_1示例: create or replace procedure Pro_Fun_test1_1( p1_in in number, p2_out out number ) AS begin p2_out:=Fun_test1(p1_in); end Pro_Fun_test1_1; --一个输入参数,一个输出参数 3、Fun_test1自定义函数的调用的存储过程Pro_Fun_test1_2示例: create or replace procedure Pro_Fun_test1_2( p1_in in number, p2_out out number ) AS t_1 number; begin select Fun_test1(p1_in)+100 INTO p2_out from bill_org where org_ID=1; end Pro_Fun_test1_2; --自定义函数的调用方法和Oracle的其它内部函数是一样的。 二、包的定义和使用入门 包一般是过程和函数的集合,对过程和函数进行更好的封装,一般不针对字段。 包的构成包括包头和包体。 1、包头的定义: 包头仅仅只是对包中的方法进行说明,而没有实现 语法: create or replace package myPackage_1       is        procedure syaHello(vname varchar2);--申明了该包中的一个过程       end; 2、包体的定义: 包体是对包头中定义的过程、函数的具体实现。 create or replace package body myPackage_1 is procedure syaHello(vname varchar2)--对包中定义的过程的实现 is begin dbms_output.put_line('Hello '||vname); end; end; 要注意的是: create or replace package后面的名称必须和create or replace package body后面的名称一致, 如果将create or replace package body后面的名称改为,'MYPACKAGE' 否则将会出现诸如下面的错误: 必须说明标识符 'MYPACKAGE' 3、调用包用的自定义方法: create or replace procedure Pro_test_package( p1_in string ) AS begin myPackage_1.syaHello(p1_in); end Pro_test_package; eg2: --没有参数的函数 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; return v_user; --测试 方法一 select get_user from dual; 方法二 SQL> var v_name varchar2(50) SQL> exec :v_name:=get_user; --带有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';

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档