DB2 C/C++ 外部函数举例

字数 3682 阅读 2962 评论 4 赞 0

打算将 DB2 数据库的一些常用应用以举例的形式写出来，方便开发人员参考。比较忙，慢慢往这里搬吧^-^
关键字： DB2 C/C++ 外部函数

这里是一个简单的外部标量函数 (external scalar function)

1. C 源代码
cd $DBHOME/sqllib/samples/c
=> cat salary.c

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqludf.h>

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN newsalary(SQLUDF_CHAR       *in_job,
                          SQLUDF_DOUBLE     *in_salary,
                          SQLUDF_DOUBLE     *out_newSalary,
                          SQLUDF_SMALLINT   *in_jobNullInd,
                          SQLUDF_SMALLINT   *in_salaryNullInd,
                          SQLUDF_SMALLINT   *out_newSalaryNullInd,
                          SQLUDF_TRAIL_ARGS)
{
if (-1 == *in_jobNullInd || -1 == *in_salaryNullInd)
{
    *out_newSalaryNullInd = -1;
}
else
{
    if (strncmp(in_job, "mgr", 3) == 0)
    {
      *out_newSalary = *in_salary * 1.20;
    }
    else if (strncmp(in_job, "sales", 5) == 0)
    {
      *out_newSalary = *in_salary * 1.10;
    }
    else
    {
      *out_newSalary = *in_salary * 1.05;
    }

    *out_newSalaryNullInd = 0;
}
}

2. 编译
   => bldrtn salary
   => ls salary*
      salary salary.c salary.o
3. 拷贝到默认目录
   => cp salary $HOME/sqllib/function

4. 注册
     CREATE or replace FUNCTION newsalary (CHAR(10), DOUBLE)
     RETURNS DOUBLE
     EXTERNAL NAME 'salary!newsalary'
     LANGUAGE C
     PARAMETER STYLE SQL
     DETERMINISTIC
     NO SQL
     NO EXTERNAL ACTION;

5. 测试
CREATE TABLE employinfo
(
job char(10) not null,
salary double not null
);
db2 => insert into employinfo values('mgr', 30000),('sales', 50000);
DB20000I The SQL command completed successfully.
db2 => select * from employinfo;
JOB        SALARY
---------- ------------------------
mgr          +3.00000000000000E+004
sales        +5.00000000000000E+004
2 record(s) selected.
db2 => select job, newsalary(job, salary) from employinfo;
JOB        2
---------- ------------------------
mgr          +3.60000000000000E+004
sales        +5.50000000000000E+004
2 record(s) selected.

数据库源代码

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！