求算阶乘PLSQLJAVA⽅法
SQL> CREATE OR REPLACE FUNCTION F_SUM_MULTI(P_IN IN NUMBER) RETURN NUMBER AS
2 V_RESULT_MULTI NUMBER DEFAULT 1;
3 V_RESULT NUMBER DEFAULT 0;
4 BEGIN
5 FOR I IN 1..P_IN LOOP
6 V_RESULT_MULTI := V_RESULT_MULTI * I;
7 V_RESULT := V_RESULT + V_RESULT_MULTI;
8 END LOOP;
9 RETURN V_RESULT;
10 END;
11 /
函数已创建。
SQL> SELECT F_SUM_MULTI(5) FROM DUAL;
F_SUM_MULTI(5)
--------------
153
代码很简单，功能也已经实现了。但是，问题并不想我想的这么简单。
SQL> SELECT F_SUM_MULTI(100) FROM DUAL;
F_SUM_MULTI(100)
----------------
~
奇怪，输出结果为什么会是~呢？莫⾮是超过了NUMBER能表⽰的最⼤的范围？
SQL> SELECT F_SUM_MULTI(83) FROM DUAL;
F_SUM_MULTI(83)
---------------
3.994E+124
SQL> SELECT F_SUM_MULTI(84) FROM DUAL;
F_SUM_MULTI(84)
---------------
~
果然是超过了NUMBER能表⽰的最⼤的范围。NUMBER类型能表达的最⼤值是
9.9999999999999999999999999999999999*10E125。以前还从没有碰到过超出最⼤处理范围的情况，也⼀直没有想到过会碰到超出最⼤精度。看来阶乘不愧是结果增长最迅速的操作。
由于超过了Oracle能处理的最⼤值，Oracle已经很难处理这个问题了。莫⾮已经没有办法来处理这个问题了？
上⽂以及提到，由于计算的数值的⼤⼩以及超过了Oracle所能表⽰的最⼤范围，因此，Oracle中已经⽆法进⾏计算了。
⾸先考虑的是能否通过外部过程来实现。利⽤C或程序来计算，并将最终结果通过字符串的⽅式返回给Oracle。
这篇⽂章给出通过外部C过程的⽅式来实现。本例是模仿Tom的EXPERT ONE ON ONE ORACLE中C外部过程例⼦进⾏编写的。OCI程序的连接、初始化，以及数据传递部分和Tom的例⼦很相似，只是在需要的地⽅进⾏了⼀些⼩的修改。
整个程序代码如下，multi_sum.c：
#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <string.h>
#include <time.h>
#include <errno.h>
#include <ctype.h>
#include <oci.h>
#define INI_FILE_NAME "/tmp/multi_sum.ini"
typedef struct ociStruct
{
OCIExtProcContext * ctx;
OCIEnv * envhp;
OCISvcCtx * svchp;
OCIError * errhp;
ub1 debugf_flag;
char debugf_path[255];
char debugf_filename[50];
} ocihStruct;
void _debugf(ocihStruct * ocih, char * fmt, ...)
{
va_list ap;
OCIFileObject * fp;
time_t theTime=time(NULL);
char msg[8192];
ub4 bytes;
if (OCIFileOpen(ocih->envhp, ocih->errhp, &fp, ocih->debugf_filename, ocih->debugf_path,
OCI_FILE_WRITE_ONLY, OCI_FILE_APPEND|OCI_FILE_CREATE, OCI_FILE_TEXT) != OCI_SUCCESS) return;
strftime(msg, sizeof(msg), "%Y%m%d%H%M%S GMT", gmtime(&theTime));
OCIFileWrite(ocih->envhp, ocih->errhp, fp, msg, strlen(msg), &bytes);
va_start(ap, fmt);
vsprintf(msg, fmt, ap);
va_end(ap);
strcat(msg, "n");
OCIFileWrite(ocih->envhp, ocih->errhp, fp, msg, strlen(msg), &bytes);
OCIFileClose(ocih->envhp, ocih->errhp, fp);
}
void _debugf(ocihStruct * ocih, char * fmt, ...);
#define debugf if((ocih!=NULL) && (ocih->debugf_flag)) _debugf
static int raise_application_error(ocihStruct * ocih, int errCode, char * errMsg, ...)
{
char msg[8192];
va_list ap;
va_start(ap, errMsg);
vsprintf(msg, errMsg, ap);
va_end(ap);
debugf(ocih, "raise application error(%d, %s)", errCode, msg);
if(OCIExtProcRaiseExcpWithMsg(ocih->ctx, errCode, msg, 0) == OCIEXTPROC_ERROR)
{
debugf(ocih, "Unable to raise exception");
}
return -1;
}
static char * lastOciError(ocihStruct * ocih)
{
sb4 errcode;
char * errbuf=(char *)OCIExtProcAllocCallMemory(ocih->ctx, 256);
strcpy(errbuf, "unable to retrieve messagen");
OCIErrorGet(ocih->errhp, 1, NULL, &errcode, errbuf, 255, OCI_HTYPE_ERROR);
errbuf[strlen(errbuf) - 1] = 0;
return errbuf;
}
static ocihStruct * init(OCIExtProcContext * ctx)
{
ub1 false = 0;
ocihStruct *ocih = NULL;
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIError *errhp;
ub4 key = 1;
if (OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp) != OCI_SUCCESS)
{
OCIExtProcRaiseExcpWithMsg(ctx, 20000, "failed to get OCI Connection", 0);
return NULL;
}
if (OCIContextGetValue(envhp, errhp, (ub1 *)&key, sizeof(key), (dvoid **)&ocih) != OCI_SUCCESS) {
OCIExtProcRaiseExcpWithMsg(ctx, 20000, "failed to get OCI Context", 0);
return NULL;
}
if (ocih == NULL)
{
if (OCIMemoryAlloc(envhp, errhp, (dvoid **)&ocih, OCI_DURATION_PROCESS, sizeof(ocihStruct), OCI_MEMORY_CLEARED) != OCI_SUCCESS)
{
OCIExtProcRaiseExcpWithMsg(ctx, 20000, "failed to get OCI Memory", 0);
return NULL;
return NULL;
}
ocih->ctx = ctx;
ocih->envhp = envhp;
ocih->svchp= svchp;
ocih->errhp= errhp;
if (OCIContextSetValue(envhp, errhp, OCI_DURATION_SESSION, (ub1 *)&key, sizeof(key), ocih) != OCI_SUCCESS)
{
raise_application_error(ocih, 20000, "%s", lastOciError(ocih));
return NULL;
}
if ((OCIExtractInit(envhp, errhp) != OCI_SUCCESS) ||
(OCIExtractSetNumKeys(envhp, errhp, 3) != OCI_SUCCESS) ||
(OCIExtractSetKey(envhp, errhp, "debugf", OCI_EXTRACT_TYPE_BOOLEAN, 0, &false, NULL, NULL) != OCI_SUCCESS) || (OCIExtractSetKey(envhp, errhp, "debugf_filename", OCI_EXTRACT_TYPE_STRING, 0, "extproc.log", NULL, NULL)
!= OCI_SUCCESS) ||
(OCIExtractSetKey(envhp, errhp, "debugf_path", OCI_EXTRACT_TYPE_STRING, 0, "", NULL, NULL) != OCI_SUCCESS) || (OCIExtractFromFile(envhp, errhp, 0, INI_FILE_NAME) != OCI_SUCCESS) ||
(OCIExtractToBool(envhp, errhp, "debugf", 0, &ocih->debugf_flag) != OCI_SUCCESS) ||
(OCIExtractToStr(envhp, errhp, "debugf_filename", 0, ocih->debugf_filename, sizeof(ocih->debugf_filename))
!= OCI_SUCCESS) ||
(OCIExtractToStr(envhp, errhp, "debugf_path", 0, ocih->debugf_path, sizeof(ocih->debugf_path))
!= OCI_SUCCESS) ||
(OCIExtractTerm(envhp, errhp) != OCI_SUCCESS))
{
raise_application_error(ocih, 20000, "%s", lastOciError(ocih));
return NULL;
}
}
else
{
ocih->ctx = ctx;
ocih->envhp =envhp;
ocih->svchp = svchp;
ocih->errhp = errhp;
}
if (OCIFileInit(ocih->envhp, ocih->errhp) != OCI_SUCCESS)
{
raise_application_error(ocih, 20000, "%s", lastOciError(ocih));
return NULL;
}
return ocih;
java replace方法}
static void term(ocihStruct * ocih)
{
OCIFileTerm(ocih->envhp, ocih->errhp);
}
#define ERROR_OCI_ERROR 20001
#define ERROR_STR_TOO_SMALL 20002
char * multi_sum (OCIExtProcContext * ctx, int p_number, short p_inumber_i, short * return_i, int * return_l) {
char * return_value;
ocihStruct * ocih;
double result=0, result_mul=1;
int n=100, i;
char res[4000];
if ((ocih=init(ctx)) == NULL)
return NULL;
if (p_inumber_i == OCI_IND_NOTNULL)
{
n = p_number;
}
for(i=1;i<=n;i++)
{
result_mul*=i;
result+=result_mul;
}
sprintf(res, "%lf", result);
debugf(ocih, "Enter return String");
return_value = (char *)OCIExtProcAllocCallMemory(ctx, strlen(res) + 1);
if (return_value == NULL)
{
raise_application_error(ocih, ERROR_OCI_ERROR, "%s", "no memory");
}
else
{
*return_i = OCI_IND_NULL;
strcpy(return_value, res);
*return_l = strlen(return_value);
*return_i = OCI_IND_NOTNULL;
}
term(ocih);
return return_value;
}
然后，利⽤MAKE⽂件，将其编译成.so⽂件，makefile⽂件内容如下：
MAKEFILE= $(ORACLE_HOME)/rdbms/demo/demo_rdbms.mk
INCLUDE= -I$(ORACLE_HOME)/rdbms/demo
-I$(ORACLE_HOME)/rdbms/public
-I$(ORACLE_HOME)/plsql/public
-I$(ORACLE_HOME)/network/public
TGTDLL= multi_sum.so
OBJS= multi_sum.o
all:$(TGTDLL)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论