oracle 自定义函数
oracle 自定义函数
oracle自定义函数是最重要的函数之一，下面就为您介绍oracle自定义函数的使用，实现返回表类型，希望对您能够有所帮助。
oracle中的函数可以返回表类型。
但是，这个表类型实际上是集合类型（与数组类似）这个类型不能直接作为 from 的宾语。
从oracle 9i 开始，提供了一个叫做“管道化表函数”的概念，来解决这个问题。
这种类型的函数，必须返回一个集合类型，且标明 pipelined。
这个函数不能返回具体变量，必须以一个空 return 返回。
这个函数中，通过 pipe row () 语句来送出要返回的表中的每一行。
调用这个函数的时候，通过 table() 关键字把管道流仿真为一个数据集
以下是一个十分简单的实例：
create table tb1(k number, v varchar2(10));
insert into tb1(k, v) values(100,'aaa');
insert into tb1(k, v) values(200,'bbb');
insert into tb1(k, v) values(200,'ccc');
select * from tb1;
create type row_type1 as object(k number, v varchar2(10));
create type table_type1 as table of row_type1;
create or replace function fun1 return table_type1 pipelined as
v row_type1;
begin
formyrow in (select k, v from tb1) loop
v := row_type1(myrow.k, myrow.v);
pipe row (v);
end loop;
return;
end;
select * from table(fun1);。
oracle自定义字符串分割函数
oracle⾃定义字符串分割函数思路:oracle没有提供类似java中split的函数来将字符串分割成数组,需要开发者⾃⼰搞定.⾸先定⼀个数组类型,再定义⼀个函数或存储过程,处理字符串分割操作,⼀个具体的实例如下:--⾃定义数组类型:CREATE OR REPLACETYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);/--⾃定义字符串分割函数,返回分组后的数组CREATE OR REPLACE FUNCTION f_strsplit (STRING VARCHAR2, substring VARCHAR2)RETURN varchar2varrayISlen INTEGER := LENGTH (substring);lastpos INTEGER := 1 - len;pos INTEGER;num INTEGER;i INTEGER := 1;ret varchar2varray := varchar2varray (NULL);v_str VARCHAR2 (32767);/**⾃定义split函数,将指定的字符串按指定的标志符分割成字符数组*/BEGINLOOPpos := INSTR (STRING, substring, lastpos + len);IF pos > 0THEN --foundnum := pos - (lastpos + len);ELSE --not foundnum := LENGTH (STRING) + 1 - (lastpos + len);END IF;IF i > STTHENret.EXTEND;END IF;v_str := SUBSTR (STRING, lastpos + len, num);--DBMS_OUTPUT.put_line (v_str);ret(i) := v_str;EXIT WHEN pos = 0;lastpos := pos;i := i + 1;END LOOP;RETURN ret;END;/--调⽤⽅式:SELECT *FROM TABLE (CAST (f_strsplit ('ABC$DEFGH$IJKLMN$OPQRST', '$') AS varchar2varray ) )将输出:column_value--------------ABCDEFGHIJKLMNOPQRST也可以采⽤如下⽅式得到数组中的值FOR cur_str IN(SELECT *FROM TABLE(CAST(f_strsplit ('ABC$DEFGH$IJKLMN$OPQRST', '$') AS varchar2varray )))LOOPDBMS_OUTPUT.put_line (cur_str.column_value);--其他赋值操作END LOOP;转载⾃：。
函数索引的使用细节——自定义函数的索引化
Explained.
SQL> select * from table(dbms_xplan.display(format=>'advanced'));
Index created
//收集统计量
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
//生成执行计划
// *Action: If the function is deterministic, mark it DETERMINISTIC. If it
// is not deterministic (it depends on package state, database state,
8 return(Result);
9 end f_test_nc;
10 /
Function created
使用dbms_random函数，必然结果是不确定性的。
SQL> select f_test_nc('df') from dual;
|* 2 | INDEX RANGE SCAN | IDX_T_FOBJNAME | 4 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
ORACLE函数记录，自定义日历周获取周数
ORACLE函数记录，⾃定义⽇历周获取周数函数：⾃定义⽇历周获取周数功能说明：计算某⽇期是⼀年的第⼏周，⽀持⾃定义⽇历周，将任意周内星期设为⼀周起始⽇。
参数说明：currentDate为⽬标⽇期，weekStandard为⾃定义⼀周开端。
注：可将任意周⽇、周⼀、周⼆...设为⼀周开头，参数取值范围为1~7，周⼀⽤1表⽰，周⼆⽤2表⽰，以此类推。
具体sql如下：CREATE OR REPLACE function GetWeekNum(currentDate IN date,weekStandard IN varchar2) return varchar2asfirstDayWeekNo NUMBER; --本年1号周⼏（以⽇历为基础，周⼀为第⼀天）fisrtWeekDayCount NUMBER; --本年第⼀周天数(以weekNo为标准)dayCount NUMBER; --总天数weekCount NUMBER; --总周数beginif currentDate is null or weekStandard is null or weekStandard < 1 or weekStandard > 7thenreturn null;end if;firstDayWeekNo := to_char(trunc(currentDate,'y'),'d','NLS_DATE_LANGUAGE=AMERICAN')-1;if firstDayWeekNo = 0thenfirstDayWeekNo := 7;end if;if weekStandard = firstDayWeekNothenif(currentDate = trunc(currentDate,'y'))thenfisrtWeekDayCount := 1;elsefisrtWeekDayCount := 7;end if;elsif weekStandard < firstDayWeekNothenfisrtWeekDayCount := 7-(firstDayWeekNo-weekStandard);elsif weekStandard > firstDayWeekNothenfisrtWeekDayCount := (weekStandard - firstDayWeekNo);end if;dayCount := to_char(currentDate,'ddd');weekCount := ceil((dayCount-fisrtWeekDayCount)/7)+1;return to_char(currentDate,'YYYY')||to_char(weekCount,'FM00');end;。
Oracle自定义聚合函数-分析函数
自定义聚合函数，分析函数--from GTA Aaron最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如：select exp(sum(ln(field_name))) from table_name;不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。
很幸运Oracle 允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。
首先介绍聚合函数接口：用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface 来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。
通过实现ODCIAggregate rountines来创建自定义的聚合函数。
可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。
在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后，就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。
每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数，这些函数定义了任何一个聚合函数内部需要实现的操作：1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。
初始化的聚合环境(aggregation context)会以对象实例(object type instance)传回给oracle.static function ODCIAggregateInitialize(var IN OUT agg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self 为当前聚合函数的指针,用来与前面的计算结果进行关联。
Oracle带参数的自定义函数
query_sql := 'select count(1) from ' || table_name;
execute immediate query_sql into t_count;
return t_count;
end;
end getRecordCount;
/
(2) 函数调用
set serverout on
Oracle带参数的自定义函数
Oracle带参数的自定义函数
(1) 函数编写
create or replace function getRecordCount(table_name varchar2) return number as
begin
declare t_count number;
query_sql varchar2(200);
end;
/
执行结果：
The record count of table emp is 14
oracle带参数的自定义函数 oracle带参数的自定义函数 (1)函数编写 create or replace function getrecordcount(table_name varchar2) return number as begin declare t_count number; query_sql varchar2(200); begin query_sql := 'select count(1) from ' || table_name; execute immediate query_sql into t_count; return t_count; end; end getrecordcount; / (2)函数调用 set serverout on declare table_name varchar2(50); begin table_name := 'dept'; dbms_output.put_line('the record count of table ' || table_name || ' is ' || getrecordcount(table_name)); end; / 执行结果: the record count of table dept is 4 pl/sql procedure successfully completed. 换个表名试试: declare table_name varchar2(50); begin table_name := 'emp'; dbms_output.put_line('the record count of table ' || table_name || ' is ' || getrecordcount(table_name)); end; / 执行结果: the record count of table emp is 14 下载文档原格式( word原格式 ,共1页)
Oracle—自定义function语法（转载）
Oracle—⾃定义function语法（转载）⽤户⾃定义函数是存储在数据库中的代码块，可以把值返回到调⽤程序。
函数的参数有3种类型：（1）in参数类型：表⽰输⼊给函数的参数，该参数只能⽤于传值，不能被赋值。
（2）out参数类型：表⽰参数在函数中被赋值，可以传给函数调⽤程序，该参数只能⽤于赋值，不能⽤于传值。
（3）in out参数类型：表⽰参数既可以传值，也可以被赋值。
1.函数的创建语法格式：Sql代码1. create [or replace] function functionName2. (3. parameterName1 mode1 dataType1,4. parameterName2 mode2 dataType2,5. ...6. )7. return returnDataType8. is/as9. begin10. function_body11. return expression12. end functionName; -- 结束函数的声明，也可以直接写end不加函数名。
13. --其中mode1、mode2表⽰参数类型，dataType表⽰参数的数据类型。
returnDataType表⽰返回值类型。
⽰例1：Sql代码1. create or replace function explainParameter -- 定义⼀个名为explainParameter的函数2. (3. inParam in char, -- 定义该参数类型为in参数类型，只能⽤于赋值4. outParam out char, -- out参数类型，只能⽤于传值5. inAndOutParam in out char -- in out参数类型，既能赋值，⼜能传值6. )7. return char -- 表⽰函数的返回类型为char类型8. as -- 表⽰函数体部分9. returnChar char; -- 声明零时变量，这是可有可⽆的，这⾥声明返回变量为char类型的returnChar变量。
Oracle创建自定义函数--解析JSON字符串
Oracle创建⾃定义函数--解析JSON字符串按照下⾯步骤在oracle 执⾏sql----------创建⾃定义函数sql—1.创建⼀个type，返回table类型CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2(4000);—2.创建⼀个分割的函数，主要对json进⾏按照固定割串分割CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)RETURN ty_str_splitISj INT := 0;i INT := 1;len INT := 0;len1 INT := 0;str VARCHAR2 (4000);str_split ty_str_split := ty_str_split ();BEGINlen := LENGTH (p_str);len1 := LENGTH (p_delimiter);WHILE j < lenLOOPj := INSTR (p_str, p_delimiter, i);IF j = 0THENj := len;str := SUBSTR (p_str, i);str_split.EXTEND;str_split (str_split.COUNT) := str;IF i >= lenTHENEXIT;END IF;ELSEstr := SUBSTR (p_str, i, j - i);i := j + len1;str_split.EXTEND;str_split (str_split.COUNT) := str;END IF;END LOOP;RETURN str_split;END fn_split;—3.再创建⼀个函数，解析json对像{}CREATE OR REPLACE FUNCTION parsejson(p_jsonstr varchar2, p_key varchar2)RETURN VARCHAR2 ISrtnVal VARCHAR2(1000);i NUMBER(2);jsonkey VARCHAR2(500);jsonvalue VARCHAR2(1000);json VARCHAR2(3000);BEGINIF p_jsonstr IS NOT NULL THENjson := REPLACE(p_jsonstr, '{', '');json := REPLACE(json, '}', '');json := replace(json, '"', '');FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOPIF temprow.column_value IS NOT NULL THENi := 0;jsonkey := '';jsonvalue := '';FOR tem2 IN (SELECT *FROM TABLE(fn_split(temprow.column_value, ':'))) LOOPIF i = 0 THENjsonkey := tem2.column_value;END IF;IF i = 1 THENjsonvalue := tem2.column_value;END IF;i := i + 1;END LOOP;IF (jsonkey = p_key) THENrtnVal := jsonvalue;END if;END IF;END LOOP;END IF;RETURN rtnVal;END parsejson;—4 解析JSON数组[{},{}]CREATE OR REPLACEFUNCTION parsejsontwo(p_jsonstr varchar2, p_key varchar2)RETURN VARCHAR2 ISrtnVal VARCHAR2(4000);i NUMBER(2);jsonkey VARCHAR2(500);jsonvalue VARCHAR2(1000);json VARCHAR2(4000);json2 VARCHAR2(4000);BEGINIF p_jsonstr IS NOT NULL THENjson := REPLACE(p_jsonstr, '[', '');json := REPLACE(json, ']', '');FOR temprow IN (SELECT * FROM TABLE(fn_split(json, ','))) LOOPIF temprow.column_value IS NOT NULL THENjson2 := REPLACE(temprow.column_value, '{', '');json2 := REPLACE(json2, '}', '');json2 := replace(json2, '"', '');FOR temprow2 IN (SELECT * FROM TABLE(fn_split(json2, ','))) LOOPi:=0;jsonkey:= '';jsonvalue:= '';FOR tmpe2 IN (SELECT * FROM TABLE(fn_split(temprow2.column_value, ':'))) LOOPIF i = 0 THENjsonkey := tmpe2.column_value;END IF;IF i = 1 THENjsonvalue := tmpe2.column_value;END IF;i := i + 1;END LOOP;IF (jsonkey = p_key) THENIF rtnVal IS NOT NULL THENrtnVal :=rtnVal||','||jsonvalue;ELSErtnVal :=jsonvalue;END IF;END if;END LOOP;END IF;END LOOP;END IF;RETURN rtnVal;END parsejsontwo;使⽤：SELECT parsejsontwo('[{"applyno":792,"extend1":"2019-WT-280","customerpart":"xxx","contactman":"茆XX","projectname":"XXXXX","projecttype":"XXX样"},{"applyno":793,"extend1":"2019-WT-281","customerpart":"江苏XXX集团有限公司","cont。
oracle自定义函数
oracle⾃定义函数函数 函数与存储过程相似，也是数据库中存储的已命名PL-SQL程序块。
函数的主要特征是它必须有⼀个返回值。
通过return来指定函数的返回类型。
在函数的任何地⽅可以通过return expression语句从函数返回，返回类型必须和声明的返回类型⼀致。
语法： create[or replace]function function_name [(parameter_list)] return datatype {is/as} [local_declarations] begin executable_statements; [exception exception_handlers;] end;说明： function_name：函数名称。
parameter_list：函数列表，可选。
return ⾃居：指定函数的返回类型，不能指定⼤⼩。
local_declarations：局部变量声明，可选。
executable_statements：要执⾏的PL-SQL语句。
exception_handlers：异常处理，可选。
or repalce：是否覆盖，可选。
例：根据员⼯编号获得员⼯薪⽔。
--根据no查询salCREATE OR REPLACE FUNCTION fun_getSal(eno NUMBER)RETURN NUMBER--返回类型ASv_sal emp.sal%TYPE;BEGINSELECT sal INTO v_sal FROM emp WHERE empno = eno;RETURN v_sal;EXCEPTIONWHEN OTHERS THENraise_application_error(-20012,'该雇员不存在');END fun_getSal;调⽤函数：--调⽤函数，⾃定义异常处理DECLAREv_sal NUMBER;emp_20012 EXCEPTION;PRAGMA EXCEPTION_INIT(emp_20012,-20012);BEGINv_sal:=fun_getsal(7788);dbms_output.put_line('sal:'||v_sal);EXCEPTIONWHEN emp_20012 THENdbms_output.put_line('该雇员不存在');END;注意： 1、函数参数和返回类型只声明参数，不指定⼤⼩。
oracle返回表类型自定义函数
Oracle是一种常用的关系型数据库管理系统，它提供了丰富的函数和特性来支持开发人员创建有效的数据库应用程序。
其中，返回表类型的自定义函数是一种非常有用的功能，可以帮助开发人员简化复杂的查询，并方便地使用查询结果进行进一步的处理和分析。
在Oracle中，表类型是一种用户定义的复杂数据类型，可以包含多个字段，并且可以在查询中作为一种临时的数据结构来使用。
通过使用返回表类型的自定义函数，开发人员可以将复杂的查询逻辑封装到函数中，并且可以方便地在应用程序中调用这些函数来获取所需的数据。
下面我们将介绍如何在Oracle中创建和使用返回表类型的自定义函数。
1. 创建表类型在创建返回表类型的自定义函数之前，首先需要创建相应的表类型。
可以使用以下语句来创建一个包含多个字段的表类型:```sqlCREATE OR REPLACE TYPE person_type AS OBJECT (id NUMBER,name VARCHAR2(100),age NUMBER);```上面的语句创建了一个名为`person_type`的表类型，其中包含三个字段：`id`、`name`和`age`。
2. 创建自定义函数一旦表类型创建完成，接下来可以创建返回该表类型的自定义函数。
以下是一个简单的例子，演示了如何创建一个返回`person_type`表类型的自定义函数:```sqlCREATE OR REPLACE FUNCTION get_person_info RETURN person_typeISp person_type;BEGINSELECT person_type(id, name, age) INTO pFROM person_tableWHERE id = 1;RETURN p;END;```在上面的例子中，我们创建了一个名为`get_person_info`的自定义函数，该函数返回`person_type`表类型。
ORACLE 自定义函数
Oracle 自定义函数Sql代码1. --没有参数的函数2. create or replace function get_user return varchar2 is3. v_user varchar2(50);4. begin5. select username into v_user from user_users;6. return v_user;7. end get_user;8.9. --测试10. 方法一11. select get_user from dual;12.13. 方法二14. SQL> var v_name varchar2(50)15. SQL> exec :v_name:=get_user;16.17. PL/SQL 过程已成功完成。
18.19. SQL> print v_name20.21. V_NAME22. ------------------------------23. TEST24.25. 方法三26. SQL> exec dbms_output.put_line('当前数据库用户是：'||get_user);27. 当前数据库用户是：TEST28.29. PL/SQL 过程已成功完成。
--没有参数的函数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 过程已成功完成。