oracle函数相关，持续更新

最新推荐文章于 2021-04-10 12:23:14 发布

royalan丶

最新推荐文章于 2021-04-10 12:23:14 发布

阅读量241

点赞数 1

分类专栏： oracle自定义函数

oracle自定义函数专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

语法如下:

create or replace function function_name ( argu1 [mode1] datatype1, argu2 [mode2] datatype2, ........ )

return datatype

begin

end;

2.根据参数类型：

1）不带任何参数：

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;

2）带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;

3)带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;

4）带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;

3.简单示例

1）一个最简单的自定义函数Fun_test1的定义。

create or replace function Fun_test1(p_1 number)--Fun_test1是函数名，有一个输入参数p_1，是number型的。返回值也是number型的

return number

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

)

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

)

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

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;

转载自：http://blog.itpub.net/20784402/viewspace-1116719/