`

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

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

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

分享到:
评论
1 楼 Java_zhou 2011-08-05  

相关推荐

    14.Oracle自定义函数1

    在 Oracle 数据库中,自定义函数是一种非常重要的特性,允许用户根据需求创建自己的功能模块,以扩展数据库的内置功能。本文将详细讲解如何创建、调用和管理自定义函数,以及讨论它们在实际应用中的优缺点。 一、...

    oracle自定义函数总结

    本文将详细介绍Oracle自定义函数的定义、使用方法以及一些高级特性。 #### 二、基本语法与定义 1. **定义函数的基本语法**: ```sql CREATE [OR REPLACE] FUNCTION function_name (参数列表) RETURN 返回类型 ...

    oracle 自定义函数 解析文件以“|”分割字符串

    oracle 自定义函数 解析文件以“|”分割字符串

    oracle自定义日期函数

    Oracle 自定义日期函数 Oracle 日期处理函数是数据库管理系统中非常重要的一部分,最近看到有人在做 T-SQL 与 PL/SQL 的转化,问了些有关时间函数的处理。在 PL/SQL 中,Oracle 提供了其他的方法来处理日期函数,...

    Oracle自定义加密解密函数:数据安全合规·数据脱敏·加密存储高效解决方案

    3、兼容性与灵活性: 适用于各种 Oracle 数据库环境,无论是本地部署还是云数据库,均可无缝集成,满足不同场景需求。 4、详尽注释文档: 提供了完整的函数代码及详细的注释说明,便于用户快速理解与应用,同时降低...

    Oracle函数返回表

    Oracle自定义函数返回一张表。主要用于生成一张等时间间隔的表数据。

    oracle自定义函数实现金额转换为大写

    oracle数据库,使用自定义函数,实现了金额的大写转换。sql语句中,直接调用此函数即可得到金额大写。

    Oracle10g自定义聚合函数(字符串拼接)

    * 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100... * Oracle11g Release2版本引入了LISTAGG 函数,使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序

    Oracle自定义聚合函数-分析函数

    Oracle 自定义聚合函数-分析函数 Oracle 自定义聚合函数是一种强大且灵活的功能,允许用户根据自己的需求创建自定义的聚合函数。聚合函数是一种特殊的函数,它可以对一组数据进行处理和计算,并返回一个结果。 ...

    Oracle统一社会信用代码自定义效验函数

    在IT行业中,数据库管理系统(DBMS)如...总结来说,这个Oracle自定义函数是针对统一社会信用代码的效验工具,遵循国家的效验规则,确保在数据库中存储的USCC信息的准确无误,这对于在中国进行合规操作的企业至关重要。

    hibernate 调用oracle函数

    1. **创建Oracle函数**:在Oracle数据库中,我们首先需要创建一个自定义函数。例如,我们可以创建一个计算两个数字之和的函数`ADD_NUMBERS`: ```sql CREATE OR REPLACE FUNCTION ADD_NUMBERS(p_num1 NUMBER, p_...

    Oracle 自定义函数

    包含自定义身份证函数,小写转大写等等。自己总结了很长时间,以后会继续更新的。

    手机号安全脱敏工具方案:Oracle自定义函数实现中间四位替换为4位星号

    1. 高效性:此函数针对Oracle数据库环境进行了优化,能够快速处理大量数据,显著提升数据处理效率。 2. 安全性:通过将手机号码的中间四位替换为星号(*),有效防止了敏感信息泄露,满足GDPR等国际隐私法规的要求。 3...

    ORACLE CRC32函数

    ### ORACLE CRC32函数详解 #### 一、概述 在Oracle数据库中,`CRC32`函数是一种非常实用的功能,主要用于将字符类型的数据转换为一个唯一的数字类型,这一过程通常被称为散列(Hash)。通过该函数,可以方便地生成...

    Oracle自定义脱敏函数的代码详解

    Oracle自定义脱敏函数是数据库管理中用于保护敏感数据的一种常见方法,尤其是在处理用户个人信息时。本文将深入解析Oracle自定义脱敏函数的实现细节,以确保在数据共享、备份或分析时不泄露关键信息。 首先,我们来...

    Oracle存储过程、自定义函数、动态建表存储过程等例子

    2. **Oracle自定义函数**: 自定义函数与存储过程类似,但它们通常用于返回单个值。函数可以在查询中直接使用,比如在SELECT语句中,增强查询的灵活性。比如,你可以编写一个计算折扣的函数,根据客户等级返回不同...

Global site tag (gtag.js) - Google Analytics