原标题:Oracle Function(函数)解析
Oracle SQL 在执行过程中,函数的合理使用会大大提升执行效率。但函数的质量好坏严重影响SQL执行。比如说EBS的库龄,账龄报表,账龄天数的计算,函数使用不当。将是灾难;我曾经见过,20多个小时账龄报表硬是未运行完毕,造成系统宕机;
今天我们就说说函数。
一 定义
函数用于计算和返回一个结果值,把经常需要进行的计算写成函数,函数的调用是表达式的一部分。
函数与过程在创建的形式上有些相似,也是编译后放在内存中供用户使用。
函数必须有一个返回值,而过程没有做强制的规定。
RETURN在声明部分需要定义一个返回参数的类型,而在函数体中必须有一个RETURN语句。如果函数结束时还没有遇到返回语句,就会发生错误。
二 创建
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;
欢迎回来; 下期更精彩;返回搜狐,查看更多
责任编辑: