使用样本应用程序创建存储过程
本示例使用名为 MYPROC() 的存储过程。这个过程采用 5 个参数:帐户名称、选项、转帐金额、储蓄余额和支票余额。以下列表标识每个参数的用途:
帐户名称:用来标识帐户的输入参数。 选项:确定进行什么操作的输入参数。 以下有三个选项: 1:检查余额。 2:从储蓄帐户向支票帐户转帐。 3:从支票帐户向储蓄帐户转帐。 转帐金额:要在支票帐户和储蓄帐户之间转帐的金额的输入参数。 储蓄余额:返回储蓄帐户的余额的输出参数。 支票余额:返回支票帐户的余额的输出参数。
以下代码构建存储过程:
SQL_API_RC SQL_API_FN myProc(char * szName, int * nCmd, int * nAmount, int * nSaving, int * nChecking) { SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN rc; int nRetSize; SQLCHAR str1[]="select saving, checking from db2e.myaccount where name = ?"; SQLCHAR str2[]="update db2e.myaccount set saving=saving - ?, checking=checking + ? where name=?"; SQLCHAR str3[]="update db2e.myaccount set saving=saving + ?, checking=checking - ? where name=?"; //**************************************************************** //* Prepare connection and statement //**************************************************************** rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); //checkerror rc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc); //checkerror rc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_NTS); //checkerror rc = SQLConnect(hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS); //checkerror rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt); //checkerror //**************************************************************** //* Update account //**************************************************************** if ( *nCmd == 2 || *nCmd == 3 ){ if ( *nCmd == 2 ){ //Transfer from saving to checking rc = SQLPrepare(hstmt, str2, SQL_NTS); //checkerror } if ( *nCmd == 3 ){ //Transfer from checking to saving rc = SQLPrepare(hstmt, str3, SQL_NTS); //checkerror } rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)nAmount, 0, NULL ); //checkerror rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)nAmount, 0, NULL ); //checkerror rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, (SQLPOINTER)szName, 0, NULL ); //checkerror rc = SQLExecute(hstmt); //checkerror } //**************************************************************** //* Retrieve account balance //**************************************************************** rc = SQLPrepare(hstmt, str1, SQL_NTS); //checkerror rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, (SQLPOINTER)szName, 0, NULL); //checkerror rc = SQLExecute(hstmt); //checkerror if ( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO ) { while ( (rc = SQLFetch(hstmt) ) == SQL_SUCCESS ){ rc = SQLGetData( hstmt, (SQLSMALLINT)1, SQL_C_LONG, nSaving, sizeof(int) , &nRetSize ) ; //checkerror rc = SQLGetData( hstmt, (SQLSMALLINT)2, SQL_C_LONG, nChecking, sizeof(int) , &nRetSize ) ; //checkerror } } //**************************************************************** //* Clean up //**************************************************************** rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); SQLFreeHandle(SQL_HANDLE_ENV, henv); return (0); }
在 Win32 平台上,将存储过程构建成动态链接库(mydll.dll)后,将其复制至 /SQLLIB/function 目录。然后,注册存储过程。
- 打开 DB2 命令窗口。
- 使用以下命令连接至 MYSAMPLE 数据库:
DB2 CONNECT TO MYSAMPLE
- 使用名为 regscript.scr 的脚本注册存储过程以配置选项。下列代码用于此脚本:
CREATE PROCEDURE db2e.MYPROC (IN szName CHAR(16), IN nCmd INTEGER, IN nAmount INTEGER, OUT nSaving INTEGER, OUT nChecking INTEGER ) DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL NO DBINFO FENCED MODIFIES SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'mydll!myProc'@
要运行脚本,输入下列命令:
db2 -td@ -vf regscript.scr
现在配置存储过程 db2e.MYPROC。接着,使用“移动设备管理中心”创建预订。
相关概念
----------------------------------------------------------------------------------------------------------
请教db2嵌c的存储过程
我写了一个嵌c的存储过程sumuporg.sqc,将其预编译、绑定后生成sumuporg.c sumuporg.bnd。然后将sumuporg.c在microsoft vc下编译生成sumuporg.dll,将该dll拷贝到d:/sqllib/function下(我的db2的安装目录)。
预编译过程如下:
connect to smns_ins user db2admin using db2admin@
prep d:/share/sumuporg.sqc bindfile using d:/share/sumuporg.bnd@
bind d:/share/sumuporg.bnd@
connect reset@
然后create存储过程:
create procedure sumuporg(in org char(21),in table char(9),in date char(21))
external name 'sumuporg!sumuporg'
language c
FENCED
parameter style general@
在strore procedure builder中执行该存储过程,错误信息如下:
DB2ADMIN.SUMUPORG - 正在调用存储过程。
DB2ADMIN.SUMUPORG - 输入参数的值:
ORG = 04
TABLE = A2
DATE = 2003/0/1/00/0/0/0/00
[IBM][CLI Driver][DB2/NT] SQL1106N 已装入指定的 DLL "sumuporg" 模块,但是不能执行功能 "sumuporg"。
DB2ADMIN.SUMUPORG - 更改回滚。
DB2ADMIN.SUMUPORG - 调用存储过程完成。
sumuporg.sqc内容如下:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <conio.h>
#include "sql.h"
#include "sqlenv.h"
EXEC SQL INCLUDE SQLCA;
SQL_API_RC SQL_API_FN sumuporg(char *org,char *table,char *date)
{
EXEC SQL BEGIN DECLARE SECTION;
char inOrgCode[21];
char inTableCode[9];
char inDate[21];
char rowCode[10];
char columnCode[10];
char subOrgCode[21];
long lValue;
char sValue[31];
long lineCode;
EXEC SQL END DECLARE SECTION;
/*初始化*/
memset(inOrgCode,0,sizeof(inOrgCode));
memset(inTableCode,0,sizeof(inTableCode));
memset(inDate,0,sizeof(inDate));
strcpy(inOrgCode,org);
strcpy(inTableCode,table);
strcpy(inDate,date);
EXEC SQL DECLARE row CURSOR for
select FD_TARGET_CODE from tb_tem_target
where FD_TB_CODE=:inTableCode and FD_TARGET_KIND='H';
EXEC SQL DECLARE column CURSOR for
select FD_TARGET_CODE from tb_tem_target
where FD_TB_CODE=:inTableCode and FD_TARGET_KIND='L'
and FD_SUM='1';
EXEC SQL DECLARE org CURSOR for
select fd_code from tb_org
where fd_code like :inOrgCode||'__';
EXEC SQL OPEN row;
while(1){/*行指标*/
memset(rowCode,0,sizeof(rowCode));
EXEC SQL FETCH row INTO :rowCode;
if(sqlca.sqlcode==100){
EXEC SQL close row;
break;
}
else if(sqlca.sqlcode!=0){
EXEC SQL close row;
return(-1);
}
EXEC SQL OPEN column;
while(1){/*列指标*/
memset(columnCode,0,sizeof(columnCode));
EXEC SQL FETCH column INTO :columnCode;
if(sqlca.sqlcode==100){
EXEC SQL close column;
break;
}
else if(sqlca.sqlcode!=0){
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
lValue=0;
EXEC SQL OPEN org;
while(1){/*机构*/
memset(subOrgCode,0,sizeof(subOrgCode));
EXEC SQL FETCH org INTO :subOrgCode;
if(sqlca.sqlcode==100){
EXEC SQL close org;
break;
}
else if(sqlca.sqlcode!=0){
EXEC SQL close org;
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
memset(sValue,0,sizeof(sValue));
EXEC SQL select fd_value into :sValue from tb_datadic
where fd_date=:inDate and fd_orgcode=:subOrgCode
and fd_targetcode=:columnCode and fd_targetkindscode=:rowCode
and fd_tablecode=:inTableCode and fd_moneytype=1 and fd_flag='0';
if(sqlca.sqlcode!=0 && sqlca.sqlcode!=100){
EXEC SQL close org;
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
lValue+=atoi(sValue);
}/*end of 机构*/
/*如果数值不为0,则插入*/
if(lValue!=0){
lineCode=0;
EXEC SQL select fd_sortnum into :lineCode from tb_tem_target
where fd_tb_code=:inTableCode and fd_target_code=:rowCode;
memset(sValue,0,sizeof(sValue));
sprintf(sValue,"%d",lValue);
EXEC SQL insert into tb_datadic_tmp
values(:inDate,:inOrgCode,:columnCode,:rowCode,:lineCode,:inTableCode,:sValue,1,'2');
if(sqlca.sqlcode!=0){
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
}/*end of if(lValue!=0)*/
}/*end of 列指标*/
}/*end of 行指标*/
return(0);
}
请教各位,究竟是那个地方的问题?
预编译过程如下:
connect to smns_ins user db2admin using db2admin@
prep d:/share/sumuporg.sqc bindfile using d:/share/sumuporg.bnd@
bind d:/share/sumuporg.bnd@
connect reset@
然后create存储过程:
create procedure sumuporg(in org char(21),in table char(9),in date char(21))
external name 'sumuporg!sumuporg'
language c
FENCED
parameter style general@
在strore procedure builder中执行该存储过程,错误信息如下:
DB2ADMIN.SUMUPORG - 正在调用存储过程。
DB2ADMIN.SUMUPORG - 输入参数的值:
ORG = 04
TABLE = A2
DATE = 2003/0/1/00/0/0/0/00
[IBM][CLI Driver][DB2/NT] SQL1106N 已装入指定的 DLL "sumuporg" 模块,但是不能执行功能 "sumuporg"。
DB2ADMIN.SUMUPORG - 更改回滚。
DB2ADMIN.SUMUPORG - 调用存储过程完成。
sumuporg.sqc内容如下:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <conio.h>
#include "sql.h"
#include "sqlenv.h"
EXEC SQL INCLUDE SQLCA;
SQL_API_RC SQL_API_FN sumuporg(char *org,char *table,char *date)
{
EXEC SQL BEGIN DECLARE SECTION;
char inOrgCode[21];
char inTableCode[9];
char inDate[21];
char rowCode[10];
char columnCode[10];
char subOrgCode[21];
long lValue;
char sValue[31];
long lineCode;
EXEC SQL END DECLARE SECTION;
/*初始化*/
memset(inOrgCode,0,sizeof(inOrgCode));
memset(inTableCode,0,sizeof(inTableCode));
memset(inDate,0,sizeof(inDate));
strcpy(inOrgCode,org);
strcpy(inTableCode,table);
strcpy(inDate,date);
EXEC SQL DECLARE row CURSOR for
select FD_TARGET_CODE from tb_tem_target
where FD_TB_CODE=:inTableCode and FD_TARGET_KIND='H';
EXEC SQL DECLARE column CURSOR for
select FD_TARGET_CODE from tb_tem_target
where FD_TB_CODE=:inTableCode and FD_TARGET_KIND='L'
and FD_SUM='1';
EXEC SQL DECLARE org CURSOR for
select fd_code from tb_org
where fd_code like :inOrgCode||'__';
EXEC SQL OPEN row;
while(1){/*行指标*/
memset(rowCode,0,sizeof(rowCode));
EXEC SQL FETCH row INTO :rowCode;
if(sqlca.sqlcode==100){
EXEC SQL close row;
break;
}
else if(sqlca.sqlcode!=0){
EXEC SQL close row;
return(-1);
}
EXEC SQL OPEN column;
while(1){/*列指标*/
memset(columnCode,0,sizeof(columnCode));
EXEC SQL FETCH column INTO :columnCode;
if(sqlca.sqlcode==100){
EXEC SQL close column;
break;
}
else if(sqlca.sqlcode!=0){
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
lValue=0;
EXEC SQL OPEN org;
while(1){/*机构*/
memset(subOrgCode,0,sizeof(subOrgCode));
EXEC SQL FETCH org INTO :subOrgCode;
if(sqlca.sqlcode==100){
EXEC SQL close org;
break;
}
else if(sqlca.sqlcode!=0){
EXEC SQL close org;
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
memset(sValue,0,sizeof(sValue));
EXEC SQL select fd_value into :sValue from tb_datadic
where fd_date=:inDate and fd_orgcode=:subOrgCode
and fd_targetcode=:columnCode and fd_targetkindscode=:rowCode
and fd_tablecode=:inTableCode and fd_moneytype=1 and fd_flag='0';
if(sqlca.sqlcode!=0 && sqlca.sqlcode!=100){
EXEC SQL close org;
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
lValue+=atoi(sValue);
}/*end of 机构*/
/*如果数值不为0,则插入*/
if(lValue!=0){
lineCode=0;
EXEC SQL select fd_sortnum into :lineCode from tb_tem_target
where fd_tb_code=:inTableCode and fd_target_code=:rowCode;
memset(sValue,0,sizeof(sValue));
sprintf(sValue,"%d",lValue);
EXEC SQL insert into tb_datadic_tmp
values(:inDate,:inOrgCode,:columnCode,:rowCode,:lineCode,:inTableCode,:sValue,1,'2');
if(sqlca.sqlcode!=0){
EXEC SQL close column;
EXEC SQL close row;
return(-1);
}
}/*end of if(lValue!=0)*/
}/*end of 列指标*/
}/*end of 行指标*/
return(0);
}
请教各位,究竟是那个地方的问题?
-------------------------------------------------------------------
1.15 C存储过程参数注意
create procedure pr_clear_task_ctrl(
IN IN_BRANCH_CODE char(4),
IN IN_TRADEDATE char(8),
IN IN_TASK_ID char(2),
IN IN_SUB_TASK_ID char(4),
OUT OUT_SUCCESS_FLAG INTEGER )
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS(如果不是这样,sql 的sp将不能调用该用c写的存储过程,产生保护性错误)
NO DBINFO
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'pr_clear_task_ctrl!pr_clear_task_ctrl'@
create procedure pr_clear_task_ctrl(
IN IN_BRANCH_CODE char(4),
IN IN_TRADEDATE char(8),
IN IN_TASK_ID char(2),
IN IN_SUB_TASK_ID char(4),
OUT OUT_SUCCESS_FLAG INTEGER )
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS(如果不是这样,sql 的sp将不能调用该用c写的存储过程,产生保护性错误)
NO DBINFO
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'pr_clear_task_ctrl!pr_clear_task_ctrl'@