Oracle带参数的自定义函数
Oracle自定义聚合函数，仿wm_concat，返回CLOB类型
Oracle⾃定义聚合函数，仿wm_concat，返回CLOB类型因为数据库的wm_concat()函数返回的是VARCHAR2，但是我有个聚合操作，聚合后的参数长度超过4000，所以我需要写⼀个类似于wm_concat的函数，返回的是CLOB类型我的报错sql如下：下⾯讲解我的操作步骤：先以sys⽤户授权：grant execute on dbms_lob to xxx;第⼀步：第⼆步：第三步：第四步：第五步：sql如下：第⼀步：CREATE OR REPLACE TYPE typ_concat_clob AS OBJECT(concat_str CLOB,splitstr CLOB,--ODCIAggregateInitialize做⼀些初始化操作STATIC FUNCTION ODCIAggregateInitialize(cs_ctx In Out typ_concat_clob) return number,--ODCIAggregateIterate是主要的处理逻辑所在，这⾥定义⼀个迭代操作member function ODCIAggregateIterate(self In Out typ_concat_clob,srcvalue in VARCHAR2) return number,-- ODCIAggregateMerge是⼀个合并函数，如果在使⽤时指定了partition enabled，就必须定义此函数，⽤来把并⾏处理的结果进⾏合并member function ODCIAggregateMerge(self In Out typ_concat_clob,ctx2 In Out typ_concat_clob) return number,--ODCIAggregateTerminate是⼀个终⽌函数，顾名思义，在这个函数中对结果做最后处理并返回member function ODCIAggregateTerminate(self In Out typ_concat_clob,returnValue Out CLOB,flags IN NUMBER) return number)第⼆步：CREATEOR REPLACE TYPE BODY typ_concat_clob IS STATIC FUNCTION ODCIAggregateInitialize ( cs_ctx IN OUT typ_concat_clob ) return NUMBER IS BEGIN cs_ctx := typ_concat_clob (to_clob( ',' ),to_clob( ',' ));RETURN ODCICONST.SUCCESS;END;member FUNCTION ODCIAggregateIterate ( self IN OUT typ_concat_clob, srcvalue IN VARCHAR2 ) return NUMBER IS BEGINdbms_lob.append (self.concat_str,to_clob( srcvalue ));dbms_lob.append ( self.concat_str, self.splitstr );RETURN ODCICONST.SUCCESS;END;member FUNCTION ODCIAggregateMerge ( self IN OUT typ_concat_clob, ctx2 IN OUT typ_concat_clob ) return NUMBER IS BEGINIFctx2 IS NOT NULL THENdbms_lob.append ( self.concat_str, ctx2.concat_str );END IF;RETURN ODCICONST.SUCCESS;END;member FUNCTION ODCIAggregateTerminate ( self IN OUT typ_concat_clob, returnValue OUT CLOB, flags IN NUMBER ) return NUMBER IS BEGINdbms_lob.trim( self.concat_str, dbms_lob.getlength ( self.concat_str ) - 1 );self.concat_str := dbms_lob.substr( self.concat_str, dbms_lob.getlength ( self.concat_str ) - 1, 2 );returnValue := self.concat_str;RETURN ODCICONST.SUCCESS;END;END;/第三步：CREATEOR REPLACE FUNCTION fun_concat_clob ( in_str VARCHAR2 ) RETURN CLOB AGGREGATE USING typ_concat_clob;/第四步：create table test_22 as(select fun_concat_clob('a') as id from dual使⽤和wm_concat⼀样，不同的是内部处理和返回值都是CLOB类型的。
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 function的编写和应用
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函数编写和应用的步骤。
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 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—自定义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自定义函数
oracle⾃定义函数函数和存储过程类似，可以简单的理解为⼀段可以执⾏某个活动/动作的⼦程序，可以作为⼀个系统对象被存储在数据库中，可以重复调⽤。
与存储过程不同的是，函数总是向调⽤者返回⼀个值，⽽存储过程不能有返回值。
CREATE[OR REPLACE]FUNCTION function_name[(argment [ { IN| IN OUT }] type,argment [ { IN | OUT | IN OUT } ] type]RETURN return_type{ IS|AS }<类型.变量的说明>BEGINFUNCTION_bodyEXCEPTION异常处理语句END;--IN:输⼊参数，向存储过程传递值，默认类型，可以不写--OUT:输出参数，⽤于返回结果。
--IN OUT：作为IN参数向存储过程传递值，同时作为OUT参数返回值。
--REPLACE：指明若已有同名的存储过程存在，那么将被替换成当前创建的版本。
--RETURN只能返回单个值，不能返回多个值。
--可以使⽤DEFAULT关键字为输⼊参数指定默认值。
--没有参数的函数create or replace function get_user return varchar2isv_user varchar2(50);beginselect 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 过程已成功完成。
SQL>print v_nameV_NAME------------------------------TEST⽅法三SQL>exec dbms_output.put_line('当前数据库⽤户是：'||get_user);当前数据库⽤户是：TESTPL/SQL 过程已成功完成--带有IN参数的函数create or replace function get_empname(v_id in number) return varchar2asv_name varchar2(50);beginselect name into v_name from employee where id = v_id;return v_name;exceptionwhen no_data_found thenraise_application_error(-20001, '你输⼊的ID⽆效！');end get_empname;函数的调⽤：把函数作为PL/SQL中的表达式调⽤例如：假如函数getID()可以返回值，可以这样调⽤：SELECT * FROM table WHERE id=getID()创建⼀个变量⽤于接收函数的返回值例如：VARIABLE id NUMBER;EXECUTE :id := getID();PRINT(id);调⽤函数的位置：l SELECT 语句的列表部分，eg: SELECT fun(..)….l WHERE 或 HAVING 字句 eg:WHERE id=fun(…)…l ORDER BY 、CONNECT BY、START WITH、GROUP BY 字句l INSERT 语句的VALUES部分 INSERT INTO table VALUES(..,fun(),..)l UPDATE 语句的SET部分 eg:UPDATE table SET name=fun(…)l PL/SQL块要让函数可以在SQL表达式中调⽤，必须满⾜以下条件：1. 应该是stored function2. 只接收IN参数3. 传⼊参数和返回值都必须是SQL⽀持的类型，不能是PL/SQL中的特殊类型（例如boolean）函数调⽤限制1、SQL语句中只能调⽤存储函数(服务器端)，⽽不能调⽤客户端的函数。
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数据库中的Function调用参数问题
2
ininttype INTEGER,
3
outcurlist OUT ref_cursor) RETURN INTEGER IS
4 BEGIN
5 OPEN outcurlist FOR
6
SELECT *
7
FROM tbuserlink
8
WHERE qidianuserid = inintuserid AND
9
intype = decode(ininttype, -1, intype, ininttype) AND
10
status = 0;
11
12 RETURN 0;
13 END;
那么调用这个Function的时候，就要定义4个参数，如下：
1 IDataParameter[] paramArray = new IDataParameter[]{
2
ado.GetReturnParameter(),
3
Hale Waihona Puke ado.GetParameter("inintuserid", DbType.Int32, userid),
4
ado.GetParameter("ininttype", DbType.Int32, typeid),
5
ado.GetParameter("outcurlist", DbType.Object, ParameterDirection.Output)
6
};
一点点心得，记下了！！！
最后，Function如果定义在Package 里面的话，调用的时候要加上package
this.AdoHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "pkg_ervotecount_month", paramArray); pkg_voteticket包名，包可以理解为一个类，里面放了很多一类的方法或存储过程，调用包中的方法：包名.方法名