Oracle Function（函数）解析

Oracle Function（函数）解析

Oracle SQL 在执行过程中，函数的合理使用会大大提升执行效率。但函数的质量好坏严重影响SQL执行。比如说EBS的库龄，账龄报表，账龄天数的计算，函数使用不当。将是灾难；我曾经见过，20多个小时账龄报表硬是未运行完毕，造成系统宕机；

今天我们就说说函数。

一  定义

函数用于计算和返回一个结果值，把经常需要进行的计算写成函数,函数的调用是表达式的一部分。

函数与过程在创建的形式上有些相似，也是编译后放在内存中供用户使用。

函数必须有一个返回值，而过程没有做强制的规定。

二 创建

CREATE OR REPLACE FUNCTION first_fn RETURN VARCHAR2

--不需要长度

IS

RESULT VARCHAR2(100); --declare，长度定义。

BEGIN

NULL;

RETURN(RESULT);

END first_fn;

三 调用

1  调用方法一

DECLARE

var_str VARCHAR(100);

BEGIN

var_str := first_fn();

dbms_output.put_line(var_str);

2  调用方法二

SELECT first_fn()

FROM dual;

四  项目应用

带输入参数

CREATE OR REPLACE FUNCTION sf_pub_strminusint(p_beg IN VARCHAR2,

p_end IN VARCHAR2) RETURN INT

/**

AUTH

FUNC  两个随机号相减得数

**/

AS

v_ret  INT;

vs_geb VARCHAR2(30);

vs_str VARCHAR2(30);

vn_beg INT;

vs_dne VARCHAR2(30);

vn_end INT;

BEGIN

--反转

SELECT REVERSE(p_beg)

INTO vs_geb

FROM dual;

--正则

SELECT regexp_substr(vs_geb,

'[0-9]+',

1,

1)

INTO vs_str

FROM dual;

--取得随机码

SELECT to_number(REVERSE(vs_str))

INTO vn_beg

FROM dual;

--止号

SELECT REVERSE(p_end)

INTO vs_dne

FROM dual;

--正则

SELECT regexp_substr(vs_dne,

'[0-9]+',

1,

1)

INTO vs_str

FROM dual;

--取得随机码

SELECT to_number(REVERSE(vs_str))

INTO vn_end

FROM dual;

v_ret := vn_end - vn_beg + 1;

RETURN v_ret;

END sf_pub_strminusint;

以对象返回数据列表

CREATE OR REPLACE FUNCTION fn_test(pid IN NUMBER) RETURN tabtest IS

RESULT tabtest = tabtest();

userid NUMBER(12);

vname  VARCHAR(200);

orgid  NUMBER(12);

TYPE tab IS TABLE OF NUMBER;

tab_test1 tab;

***

create or replace type ctest as object

(

cUser  number(12),  --不可注释

cName  varchar(200),

cOrg  number(12)

)

create or replace type tabTEST is table of ctest

测试

select  from table(cast(fn_test({0})as tabTEST))

***/

BEGIN

SELECT id BULK COLLECT

INTO tab_test1

FROM tuser

WHERE id = pid;

RESULT = tabtest();

FOR i IN 1 .. tab_test1.count

LOOP

SELECT id,

NAME,

belongorg

INTO userid,

vname,

orgid

FROM tuser

WHERE id = tab_test1(i);

result.extend;

RESULT(result.count) = ctest(NULL,

NULL,

NULL);

RESULT(result.count).cuser = userid;

RESULT(result.count).cname = vname;

RESULT(result.count).corg = orgid;

END LOOP;

RETURN(RESULT);

END fn_test;

欢迎回来； 下期更精彩；