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 function的编写和应用在Oracle数据库中，可以使用PL/SQL语言编写函数。

函数是一段可重用的代码块，它可以接受输入参数，并返回一个值。

函数可以应用于各种场景，例如计算、数据转换和复杂查询等。

以下是编写和应用Oracle函数的一般步骤：1. 创建函数：使用CREATE FUNCTION语句创建函数。

指定函数的名称、参数和返回类型。

函数体由BEGIN和END关键字包围。

例如，下面是一个简单的函数，用于计算两个数值的和：```sqlCREATE FUNCTION sum_numbers(a NUMBER, b NUMBER) RETURN NUMBERISresult NUMBER;BEGINresult := a + b;RETURN result;END;```2. 存储函数：使用该CREATE FUNCTION语句创建函数时，它将被存储在数据库中以供后续使用。

可以通过使用替代CREATE OR REPLACE FUNCTION语句来修改已存在的函数。

例如，可以使用以下语句修改上面的函数：```sqlCREATE OR REPLACE FUNCTION sum_numbers(a NUMBER, b NUMBER)RETURN NUMBERISresult NUMBER;BEGINresult := a + b;RETURN result;END;```3. 调用函数：使用SELECT语句调用函数。

将函数名和参数传递给SELECT语句，可以将函数的返回值嵌入到查询结果中。

例如，可以使用以下SELECT语句调用上述函数并查看结果：```sqlSELECT sum_numbers(10, 5) FROM dual;```在调用函数时，也可以将其作为表达式的一部分使用，例如：```sqlSELECT sum_numbers(10, 5) * 2 FROM dual;```以上是基本的Oracle函数编写和应用的步骤。

oracle12c自定义字符串拼接聚合函数Oracle12c自定义字符串拼接聚合函数是一种非常有用的功能，可以将多个字符串拼接成一个字符串。

这种聚合函数可以在SQL查询中使用，使得查询结果更加符合需求。

在本文中，我们将讨论如何创建和使用Oracle 12c自定义字符串拼接聚合函数。

首先，我们需要创建一个存储过程来实现这个聚合函数。

以下是一个示例存储过程：CREATE OR REPLACE FUNCTION string_agg (input VARCHAR2, delimiter VARCHAR2)RETURN VARCHAR2 AGGREGATE USING string_agg_type;CREATE OR REPLACE TYPE string_agg_type AS OBJECT (total VARCHAR2(4000),STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER,MEMBER FUNCTION ODCIAggregateIterate(self IN OUTstring_agg_type, value IN VARCHAR2) RETURN NUMBER,MEMBER FUNCTION ODCIAggregateTerminate(self INstring_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,MEMBER FUNCTION ODCIAggregateMerge(self IN OUTstring_agg_type, ctx2 IN string_agg_type) RETURN NUMBER);CREATE OR REPLACE TYPE BODY string_agg_type ISSTATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT string_agg_type) RETURN NUMBER ISBEGINsctx := string_agg_type(NULL, NULL);RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateIterate(self IN OUTstring_agg_type, value IN VARCHAR2) RETURN NUMBER ISBEGINIF self.total IS NULL THENself.total := value;ELSEself.total := self.total || delimiter || value;END IF;RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateTerminate(self INstring_agg_type, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER ISBEGINreturnValue := self.total;RETURN ODCIConst.Success;END;MEMBER FUNCTION ODCIAggregateMerge(self IN OUTstring_agg_type, ctx2 IN string_agg_type) RETURN NUMBER IS BEGINIF ctx2.total IS NOT NULL THENself.total := self.total || delimiter || ctx2.total;END IF;RETURN ODCIConst.Success;END;END;在这个示例存储过程中，我们创建了一个名为string_agg的函数，它将输入的字符串input和分隔符delimiter作为参数，并返回一个拼接好的字符串。

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 asv row_type1;beginformyrow in (select k, v from tb1) loopv := row_type1(myrow.k, myrow.v);pipe row (v);end loop;return;end;select * from table(fun1);。

oracle 19c 创建函数摘要：1.Oracle 19c 简介2.创建函数的步骤3.创建函数的示例4.函数的调用与测试正文：【1.Oracle 19c 简介】Oracle 19c 是Oracle 公司的一款关系型数据库管理系统，该版本在性能、安全和易用性等方面都有显著的提升。

Oracle 19c 提供了丰富的功能，如数据库自动化管理、机器学习、大数据处理等，为企业级应用提供了强大的支持。

【2.创建函数的步骤】在Oracle 19c 中创建函数，需要遵循以下步骤：1) 登录到Oracle 数据库，使用SQL*Plus 或者其他SQL 客户端工具。

2) 创建一个新的表空间，用于存储函数的元数据。

例如：```sqlCREATE TABLESPACE function_spaceDATAFILE "function_space.dbf" SIZE 10MAUTOEXTEND ON NEXT 10M;```3) 创建一个新的函数，可以使用CREATE FUNCTION 语句。

例如，创建一个名为`GET_USER_INFO`的函数，用于查询用户信息：```sqlCREATE FUNCTION GET_USER_INFO (p_user_id IN er_id%TYPE)RETURNS er_info%ROWTYPEASBEGINSELECT * FROM users WHERE user_id = p_user_id;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE NO_DATA_FOUND;END;/```4) 编译函数，使用ALTER FUNCTION 语句。

例如：```sqlALTER FUNCTION GET_USER_INFOCOMPLETE;```5) 测试函数，使用SELECT 语句或者在应用程序中调用。

例如，查询用户ID 为1 的用户信息：```sqlSELECT * FROM GET_USER_INFO(1);```【3.创建函数的示例】除了上述的`GET_USER_INFO`函数示例，还可以创建其他类型的函数，如存储过程、触发器等。

Oracle⾃定义脱敏函数对于信息安全有要求的，在数据下发和同步过程中需要对含有⽤户⾝份信息的敏感字段脱敏，包括⽤户姓名、证件号、地址等等，下⾯是⾃定义函数的代码CREATE OR REPLACE FUNCTION F_GET_SENSITIVE(IN_STR VARCHAR, IN_TYPE NUMBER)RETURN VARCHAR2 ISV_STR_LENGTH NUMBER;V_NAME VARCHAR2(1000);V_N NUMBER;V_HID VARCHAR2(200);V_SQL VARCHAR2(200);V_NUM_FLAG NUMBER;/****N_TYPE 脱敏字段类型1 ：名称11:地址2 ：证件3 ：银⾏账号4 ：联系电话5 ：接⼊号码***/BEGINV_STR_LENGTH := LENGTH(IN_STR);V_N := 0;IF V_STR_LENGTH=0 THENRETURN(NULL);END IF;/**********名称和地址脱敏规则**********/IF IN_TYPE = 1 OR IN_TYPE=11 THENIF V_STR_LENGTH = 2 OR V_STR_LENGTH = 3 THENV_NAME := REGEXP_REPLACE(IN_STR, '(.)', '*', 2, 1);ELSIF V_STR_LENGTH < 2 THENV_NAME :=IN_STR;ELSEWHILE V_N < V_STR_LENGTH / 2 LOOPV_N := V_N + 1;V_HID := V_HID || '*';END LOOP;V_NAME := SUBSTR(IN_STR, 0, V_STR_LENGTH / 2) || V_HID;END IF;RETURN(V_NAME);END IF;/**********证件脱敏规则**********/IF IN_TYPE = 2 THENIF V_STR_LENGTH = 15 THENV_NAME := SUBSTR(IN_STR, 0, 6) || '******' || SUBSTR(IN_STR, -3, 3);ELSIF V_STR_LENGTH = 18 THENV_NAME := SUBSTR(IN_STR, 0, 6) || '********' || SUBSTR(IN_STR, -4, 4);ELSEWHILE V_N < V_STR_LENGTH / 3 LOOPV_N := V_N + 1;V_HID := V_HID || '*';END LOOP;V_NAME := SUBSTR(IN_STR, 0, V_STR_LENGTH / 3) || V_HID ||SUBSTR(IN_STR, -V_STR_LENGTH / 3, V_STR_LENGTH / 3);END IF;RETURN(V_NAME);END IF;/**********银⾏账号脱敏规则**********/IF IN_TYPE = 3 THENIF V_STR_LENGTH > 15 THENV_NAME := SUBSTR(IN_STR, 0, 4) || '********' || SUBSTR(IN_STR, -4, 4);ELSEV_NAME :=IN_STR;END IF;RETURN(V_NAME);END IF;/**********联系电话脱敏规则**********/IF IN_TYPE = 4 THENV_NAME := SUBSTR(IN_STR, 0, V_STR_LENGTH - 4) || '****';RETURN(V_NAME);END IF;/**********接⼊号码脱敏规则**********/IF IN_TYPE = 5 THENV_SQL := 'SELECT COUNT(1) FROM DUAL WHERE LENGTH(''' || IN_STR ||''') = LENGTH(REGEXP_REPLACE(''' || IN_STR || ''', ''[^0-9]''))';EXECUTE IMMEDIATE V_SQLINTO V_NUM_FLAG;IF V_NUM_FLAG = 1 AND (V_STR_LENGTH = 7 OR V_STR_LENGTH = 8) THEN V_NAME := SUBSTR(IN_STR, 0, 2) || '****' || SUBSTR(IN_STR, -2, 2);ELSIF V_NUM_FLAG = 1 AND V_STR_LENGTH = 11 THENV_NAME := SUBSTR(IN_STR, 0, 3) || '*****' || SUBSTR(IN_STR, -3, 3);ELSEV_NAME := IN_STR;END IF;RETURN(V_NAME);END IF;RETURN(IN_STR);EXCEPTIONWHEN OTHERS THEN-- DBMS_OUTPUT.PUT_LINE('1'||V_SQL);V_NAME := '-1';RETURN V_NAME;END F_GET_SENSITIVE;。

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 执⾏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中，表类型是一种用户定义的复杂数据类型，可以包含多个字段，并且可以在查询中作为一种临时的数据结构来使用。

通过使用返回表类型的自定义函数，开发人员可以将复杂的查询逻辑封装到函数中，并且可以方便地在应用程序中调用这些函数来获取所需的数据。

下面我们将介绍如何在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`表类型。