oracle 自定义函数 方法 基本例子

oracle中的函数可以返回表类型。但是，这个表类型实际上是集合类型（与数组类似）这个类型不能直接作为 from 的宾语。

函数用于返回特定数据。执行时得找一个变量接收函数的返回值; 语法如下: 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

　　函数用于返回特定数据。执行时得找一个变量接收函数的返回值;
　　语法如下: 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;

--没有参数的函数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 过程已成功完成。

　　执行:
　　带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');

-带有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;

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