分类目录

链接

2012年五月
« 4月   6月 »
 123456
78910111213
14151617181920
21222324252627
28293031  

近期文章

热门标签

博主推荐

现在位置:    首页 > Oracle > 正文
Oracle自定义函数
Oracle 暂无评论 阅读(2,669)

函数用于返回特定数据。执行时得找一个变量接收函数的返回值;

语法如下: 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 number 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;

 

elsif 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';

 

删除函数

 

drop function get_empname;

 

判断任务过期时间:

 

create or replace function GetUrgentState(m_TaskID varchar2,

 

m_SendTime date,

 

m_flag varchar2)

 

return varchar2 IS

 

myDate date;

 

ExpireTime date;

 

strsql varchar2(200);

 

begin

 

myDate := m_SendTime;

 

strsql := 'select max(EXPIRETIME) from t_wf_supervise where TASKID =''' ||

 

m_TaskID || '''';

 

execute immediate strsql

 

into ExpireTime;

 

--没有到期时间 就是正常状态

 

if ExpireTime is null then

 

if m_flag = 'String' then

 

return '正常';

 

end if;

 

if m_flag = 'Img' then

 

return 'cb_execute.gif';

 

end if;

 

end if;

 

--未发送任务,就是判断当前时间

 

if m_SendTime is null then

 

myDate := sysdate;

 

end if;

 

if ExpireTime < myDate then

 

if m_flag = 'String' then

 

return '超期';

 

end if;

 

if m_flag = 'Img' then

 

return 'cb_limit.gif';

 

end if;

 

end if;

 

--小于3天的任务预警

 

if ExpireTime - myDate < 3 then

 

if m_flag = 'String' then

 

return '预警';

 

end if;

 

if m_flag = 'Img' then

 

return 'cb_warning.gif';

 

end if;

 

else

 

if m_flag = 'String' then

 

return '正常';

 

end if;

 

if m_flag = 'Img' then

 

return 'cb_execute.gif';

 

end if;

 

end if;

 

end;

 

查询其它表数据:

 

create or replace function GetPreNode(m_PreTaskID varchar2) return varchar2 IS

 

nodename varchar2(50);

 

strsql varchar2(200);

 

begin

 

if m_PreTaskID is null then

 

return '';

 

end if;

 

strsql := 'select max(nodename) from t_Wf_Tasklist where TaskID =''' ||

 

m_PreTaskID|| '''';

 

execute immediate strsql

 

into nodename;

 

return nodename;

 

end;

 

格式化标题输出:

 

create or replace function FormatTitle(m_title varchar2,

 

m_length number,

 

m_FillChar varchar2) return varchar2 IS

 

begin

 

if lengthb(m_title) > m_length*2 then

 

return substr(m_title, 0,m_length) || m_FillChar;

 

else

 

return m_title;

 

end if;

 

end;

本文版权归数据库之家所有,转载引用请完整注明以下信息:
本文作者:Bruce
本文地址:Oracle自定义函数 | 数据库之家

发表评论

留言无头像?