忆达城市建设游戏网站,科技资讯网站开发,汕头网络推广seo方案,个人网站 商业oracle自定义函数
核心提示#xff1a;函数用于返回特定数据。执行时得找一个变量接收函数的返回值; 语法如下: create or replace function function_name ( argu1 [mode1]datatype1, argu2 [mode2] datatype2, … ) return datatype is begin end;
执行 var v1 varchar2(1…oracle自定义函数
核心提示函数用于返回特定数据。执行时得找一个变量接收函数的返回值; 语法如下: create or replace function function_name ( argu1 [mode1]datatype1, argu2 [mode2] datatype2, … ) return datatype is begin end;
执行 var v1 varchar2(100) exec :v1:function_na 函数用于返回特定数据。执行时得找一个变量接收函数的返回值;
/ oracle函数的语法结构 / 语法如下: 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 / oracle函数不带任何参数 / 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; /
/*------------------------------------------------------------------------------------------------------------- 函数的调用1直接调用类似于oracle预定义的函数那样 --------------------------------------------------------------------------------------------------------------**/
SQL select get_user from dual; GET_USER
SCOTT
/ oracle函数带in参数的 / create or replace function get_sal(empname in varchar2) return number is Result number; begin select sal into Result from emp where enameempname; return(Result); end get_sal; /
执行: SQL var sal number SQL exec :sal:get_sal(‘SCOTT’); 【说明调用函数一定要有一个变量来接收函数的返回值】 / oracle函数带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 enamee_name; return(Result); end get_info; /
执行: SQL var job varchar2(20) SQL var dname varchar2(20) SQL exec :dname:get_info(‘SCOTT’,:job) PL/SQL procedure successfully completed dname
3000 job
ANALYST / oracle函数带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; /
执行: SQL var result1 number; SQL var result2 number; SQL exec :result2:30 PL/SQL procedure successfully completed result2
30
SQL exec :result1:result(100,:result2) PL/SQL procedure successfully completed result1
3 result2
10 / 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_10 then return 1; elsif p_10 then return 0; else return -1; end if; end; / –这个函数只是可以知道自定义函数的定义和格式。其实没什么用途。 / eg: 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; / / eg: 一个输入参数一个输出参数 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_ID1; end Pro_Fun_test1_2; / / eg: 包的定义和使用入门 / 自定义函数的调用方法和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)–对包中定义的过程的实现 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; / eg: 调用方法一 / select get_user from dual; / eg: 调用方法二 / SQL var v_name varchar2(50) SQL exec :v_name:get_user; / eg: 带有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; / / eg: 函数调用限制 / 1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数
2、SQL只能调用带有输入参数,不能带有输出,输入输出函数
3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)
4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句 / eg: 查看函数院源代码 / oracle会将函数名及其源代码信息存放到数据字典中user_source
select text from user_source where name‘GET_EMPNAME’; / eg: 删除函数 / drop function get_empname; / eg: 判断任务过期时间 /
create or replace function GetUrgentState(m_TaskID varchar2,m_SendTime date,m_flag varchar2)return varchar2 IS myDate 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 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; /
CREATE OR REPLACE FUNCTION f (n INTEGER) RETURN INTEGER IS BEGIN IF n 0 THEN RETURN 1; ELSIF n 1 THEN RETURN n; END IF; END; /
创建函数输出员工号为7788 的员工的名称
create or replace function fun_show return varchar2 as v_ename emp.ename%type; – 这种定义变量的方式一定后面要加上对应的字段的名称 不要 v_ename emp%type 这样 我这样的玩过报错 begin select ename into v_ename from emp where empno 7788; return v_ename; exception when others then dbms_output.put_line(‘SQLCODE:’ || SQLCODE || ‘–SQLERRM:’ || SQLERRM); end fun_show;
删除函数 SQL SQL drop function fun_show; Function dropped
调用函数的方式很多 1、select fun_show() from dual; 这种方式就是在代码块中调用我们也可以单独调用 2、单独调用 declare str varchar2(2000); begin str : fun_show(); dbms_output.put_line(str); end; / SCOTT PL/SQL procedure successfully completed
这样调用比较常见
建立带有参数的函数
