ORACLE TYPE
的Java实现,
而是在Java类上方定义一个PL / SQL包装器包,并在TYPE实现中使用PL / SQL函数。Create or Replace AND RESOLVE Java Source Named "JAggrFun" As
import java.math.BigDecimal;
class JAggrFun {
/*
Supporting Java class for dummy aggregate function
Implemented methods:
initiate - dummy
iterate - dummy
terminate - return "Hello World"
merge (is not required)
*/
final static BigDecimal SUCCESS = new BigDecimal(0);
final static BigDecimal ERROR = new BigDecimal(1);
static public BigDecimal ODCIInitialize(BigDecimal[] sctx) {
return SUCCESS;
}
static public BigDecimal ODCIIterate(BigDecimal ctx, String str) {
return SUCCESS;
}
static public BigDecimal ODCITerminate(BigDecimal ctx, String[] str) {
str[0] = "Hello World";
return SUCCESS;
}
}
/
create or replace
PACKAGE JAggrFunPackage authid current_user AS
FUNCTION ODCIInitialize(
ctx OUT NOCOPY NUMBER
) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JAggrFun.ODCIInitialize(
java.math.BigDecimal[]) return java.math.BigDecimal';
FUNCTION ODCIIterate(
ctx IN NUMBER,
str VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JAggrFun.ODCIIterate(
java.math.BigDecimal,
java.lang.String) return java.math.BigDecimal';
FUNCTION ODCITerminate(
ctx IN NUMBER,
str OUT VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'JAggrFun.ODCITerminate(
java.math.BigDecimal,
java.lang.String[]) return java.math.BigDecimal';
END JAggrFunPackage;
/
类型
CREATE OR REPLACE
TYPE JAggrFunPackageType authid current_user AS OBJECT
(
jctx NUMBER, -- stored context; not used in dummy implementation
STATIC FUNCTION
ODCIAggregateInitialize(sctx IN OUT NOCOPY JAggrFunPackageType )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateIterate(self IN OUT NOCOPY JAggrFunPackageType,
VALUE IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateTerminate(self IN JAggrFunPackageType,
returnValue OUT NOCOPY VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION
ODCIAggregateMerge(self IN OUT NOCOPY JAggrFunPackageType,
ctx IN JAggrFunPackageType)
RETURN NUMBER
);
/
Aggregate Function
CREATE OR REPLACE
FUNCTION JAggr(input VARCHAR2 )
RETURN VARCHAR2
AGGREGATE USING JAggrFunPackageType;
/
类型定义
create or replace
type body JAggrFunPackageType is
static function ODCIAggregateInitialize(sctx IN OUT NOCOPY JAggrFunPackageType)
return number
is
begin
sctx := JAggrFunPackageType( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT NOCOPY JAggrFunPackageType,
value IN varchar2 )
return number
is
status NUMBER;
begin
if self.jctx is null then
status := JAggrFunPackage.ODCIInitialize(self.jctx);
if (status <> ODCIConst.Success) then
return status;
end if;
end if;
status := JAggrFunPackage.ODCIIterate(jctx,value);
return status;
end;
member function ODCIAggregateTerminate(self IN JAggrFunPackageType,
returnValue OUT NOCOPY VARCHAR2,
flags IN number)
return number
is
begin
return JAggrFunPackage.ODCITerminate(jctx, returnValue);
end;
member function ODCIAggregateMerge(self IN OUT NOCOPY JAggrFunPackageType,
ctx IN JAggrFunPackageType)
return number
is
begin
return ODCIConst.Success;
end;
end;
/
测试
select JAggr(dummy) from dual;
JAGGR(DUMMY)
-------------
Hello World