Luasql原始的oci8中对于存储过程只能支持无参数的存储过程的调用，现在我在里边加入一个stmt结构来支持带参数尤其是对cursor输出,的支持,需要做的就是实现一个statement的userdata来完成需要的工作bind_number, bind_string, bind_cursor：
用到的主要数据结构：
typedef union { int i; char *s; double d; OCIStmt *c; //add by alien} column_value; /*****************author:aliendate:2011-11-27an SQL statement******************/typedef struct { ub2 type; /* database type */ OCIBind *bind; /* define handle */ int direction; column_value val;} bind_data;typedef struct{ short closed; int conn; int numpars; bind_data *parvals; char *text; OCIStmt *stmthp; OCIError *errhp; /* !!! */} stmt_data;在connection metatable中增加prepare方法来返回stmt环境：
/************************************************************author:aliendate:2011-11-27create an SQL statement*************************************************************/static int create_statement (lua_State *L, int o, conn_data *conn, OCIStmt *stmthp, const char *text, int numparam){ env_data *env; stmt_data *stmt = (stmt_data *)lua_newuserdata(L, sizeof(stmt_data)); luasql_setmeta (L, LUASQL_STATEMENT_OCI8); /* fill in structure */ stmt->closed = 0; stmt->numpars = numparam; stmt->stmthp = stmthp; stmt->errhp = NULL; stmt->parvals = NULL; stmt->text = strdup (text); lua_pushvalue (L, o); stmt->conn = luaL_ref (L, LUA_REGISTRYINDEX); /* error handler */ lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env); env = lua_touserdata (L, -1); lua_pop (L, 1); ASSERT (L, OCIHandleAlloc((dvoid *) env->envhp, (dvoid **) &(stmt->errhp), (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0), conn->errhp); stmt->parvals = (bind_data *)malloc(sizeof(bind_data)*stmt->numpars); /* C array indices ranges from 0 to numcols-1 */ return 1;}/************************************************************author:aliendate:2011-11-27prapare an SQL statement*************************************************************/static int conn_prepare(lua_State* L){ env_data *env; conn_data *conn = getconnection (L); const char *statement = luaL_checkstring (L, 2); const int numparam = getparameternum(statement); OCIStmt *stmthp = NULL; /* get environment */ lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env); if (!lua_isuserdata (L, -1)) luaL_error(L,LUASQL_PREFIX"invalid environment in connection!"); env = (env_data *)lua_touserdata (L, -1); /* statement handle */ ASSERT (L, OCIHandleAlloc ((dvoid *)env->envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0), conn->errhp); ASSERT (L, OCIStmtPrepare (stmthp, conn->errhp, (text *)statement, (ub4) strlen(statement), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT), conn->errhp); return create_statement (L, 1, conn, stmthp, statement, numparam);}主要实现：
/************************************************************author:aliendate:2011-11-27dispose an SQL statement*************************************************************/static int stmt_close(lua_State* L){ int i, ret; conn_data *conn; stmt_data *stmt = (cur_data *)luaL_checkudata (L, 1, LUASQL_STATEMENT_OCI8); luaL_argcheck (L, stmt != NULL, 1, LUASQL_PREFIX"statement expected"); if (stmt->closed) { lua_pushboolean (L, 0); return 1; } /* Deallocate buffers. */ for (i = 0; i <= stmt->numpars; i++) { if( stmt->parvals[i].type == STRING ) free(stmt->parvals[i].val.s); /*else if( stmt->parvals[i].type == CURSOR ){ lua_rawgeti(L, LUA_REGISTRYINDEX, stmt->parvals[i].val.c); cur_close(L); }*/ } free(stmt->parvals); free(stmt->text); /* Nullify structure fields. */ stmt->closed = 1; if (stmt->stmthp) OCIHandleFree ((dvoid *)stmt->stmthp, OCI_HTYPE_STMT); if (stmt->errhp) OCIHandleFree ((dvoid *)stmt->errhp, OCI_HTYPE_ERROR); /* Decrement cursor counter on connection object */ lua_rawgeti (L, LUA_REGISTRYINDEX, stmt->conn); conn = lua_touserdata (L, -1); conn->cur_counter--; luaL_unref (L, LUA_REGISTRYINDEX, stmt->conn); lua_pushboolean (L, 1); return 1;}/************************************************************author:aliendate:2011-11-27bind integer to an SQL statement*************************************************************/static int stmt_bind_number(lua_State* L){ stmt_data *stmt = getstatement (L); const int pos = luaL_checknumber(L, 2) - 1; const int num = luaL_checknumber(L, 3); const int direction = luaL_checknumber(L, 4); stmt->parvals[pos].direction = direction; stmt->parvals[pos].type = NUMBER; stmt->parvals[pos].val.i = num; /*ASSERT (L, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos, &(stmt->parvals[pos].val.i), sizeof(stmt->parvals[pos].val.i), SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);*/ /*printf("pos: %d statement: %s bind_number: %d\n", pos, stmt->text, stmt->parvals[pos].val.i); error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &(stmt->parvals[pos].bind), stmt->errhp, (ub4)pos, &(num), sizeof(num), SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/ lua_pushboolean (L, 1); return 1;}/************************************************************author:aliendate:2011-11-27bind string to an SQL statement*************************************************************/static int stmt_bind_string(lua_State* L){ stmt_data *stmt = getstatement (L); const int pos = luaL_checknumber(L, 2) - 1; const char* text = luaL_checkstring(L, 3); const int direction = luaL_checknumber(L, 4); stmt->parvals[pos].direction = direction; stmt->parvals[pos].type = STRING; stmt->parvals[pos].val.s = (char*)calloc(strlen(text)+1, sizeof(char)); strcpy(stmt->parvals[pos].val.s, text); /*ASSERT (L, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos, &(stmt->parvals[pos].val.s), strlen(stmt->parvals[pos].val.s) + 1, SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);*/ /*printf("bind_string>> stmthp: %d\n", stmt->stmthp); error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &(stmt->parvals[pos-1].bind), stmt->errhp, (ub4)pos + 1, &(stmt->parvals[pos].val.s), strlen(stmt->parvals[pos].val.s) + 1, SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/ lua_pushboolean (L, 1); return 1;}/************************************************************author:aliendate:2011-11-27bind an cursor to an SQL statement*************************************************************/static int stmt_bind_cursor(lua_State* L){ env_data *env; conn_data *conn; stmt_data *stmt = getstatement (L); const int pos = luaL_checknumber(L, 2) - 1; const int direction = luaL_checknumber(L, 3); stmt->parvals[pos].type = CURSOR; stmt->parvals[pos].direction = direction; stmt->parvals[pos].val.c = NULL; lua_rawgeti (L, LUA_REGISTRYINDEX, stmt->conn); conn = lua_touserdata (L, -1); lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env); env = lua_touserdata (L, -1); lua_pop(L, 2); ASSERT(L, OCIHandleAlloc((dvoid*)env->envhp, (dvoid**)&(stmt->parvals[pos].val.c), OCI_HTYPE_STMT, 0, (dvoid**)0), stmt->errhp); /*ASSERT(L, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos, &(stmt->parvals[pos].val.i), sizeof(stmt->parvals[pos].val.i), SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);*/ /*printf("bind_cursor>> stmthp: %d cursor: %d\n", stmt->stmthp, stmt->parvals[pos].val.c); error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos + 1, &(stmt->parvals[pos].val.c), sizeof(stmt->parvals[pos].val.c), SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/ /*error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, &(stmt->parvals[i].val.c), sizeof(stmt->parvals[i].val.c), SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/ lua_pushboolean (L, 1); return 1;}/************************************************************author:aliendate:2011-11-27handle an SQL statement*************************************************************/static int data_handle(lua_State *L, stmt_data* stmt){ int top = lua_gettop(L), i, res = 0; env_data *env = NULL; conn_data *conn = NULL; cur_data *cur = NULL; OCIStmt* curstmt = NULL; //debug //printf("handle>> top: %d\n", top); for (i = 0; i < stmt->numpars; i++) { //printf("handle>> %d> io: %d type: %d res: %d\n", i, stmt->parvals[i].direction, stmt->parvals[i].type, res); if ( OUT == stmt->parvals[i].direction ) { if ( NUMBER == stmt->parvals[i].type ){ //printf("handle>> number: %u\n", stmt->parvals[i].val.i); lua_pushnumber( L, stmt->parvals[i].val.i ); res++; } if ( STRING == stmt->parvals[i].type ){ //printf("handle>> string: %s\n", stmt->parvals[i].val.s); lua_pushlstring( L, stmt->parvals[i].val.s, strlen(stmt->parvals[i].val.s) ); res++; } if ( CURSOR == stmt->parvals[i].type ){ curstmt = stmt->parvals[i].val.c; cur = (cur_data *)lua_newuserdata(L, sizeof(cur_data)); luasql_setmeta (L, LUASQL_CURSOR_OCI8); //printf("handle>> cursor: %d\n", stmt->parvals[i].val.c); lua_rawgeti (L, LUA_REGISTRYINDEX, stmt->conn); conn = lua_touserdata (L, -1); cur->conn = luaL_ref(L, LUA_REGISTRYINDEX); lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env); env = lua_touserdata (L, -1); lua_pop(L, 1); conn->cur_counter++; /* fill in structure */ cur->closed = 0; cur->numcols = 0; cur->colnames = LUA_NOREF; cur->coltypes = LUA_NOREF; cur->curr_tuple = 0; cur->stmthp = curstmt; cur->errhp = NULL; cur->cols = NULL; cur->text = strdup (stmt->text); //debug /*top = lua_gettop(L); printf("handle>> top: %d\n", top);*/ ASSERT (L, OCIHandleAlloc((dvoid *) env->envhp, (dvoid **) &(cur->errhp), (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0), conn->errhp); /* get number of columns */ ASSERT (L, OCIAttrGet ((dvoid *)curstmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&cur->numcols, (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT, cur->errhp), cur->errhp); //debug //printf("handle>> numcols: %d\n", cur->numcols ); cur->cols = (column_data *)malloc (sizeof(column_data) * cur->numcols); /* define output variables */ /* Oracle and Lua column indices ranges from 1 to numcols */ /* C array indices ranges from 0 to numcols-1 */ for (i = 1; i <= cur->numcols; i++) { int ret = alloc_column_buffer (L, cur, i); if (ret) return ret; } res++; } } } return res; }/************************************************************author:aliendate:2011-11-27execute an SQL statement*************************************************************/static int stmt_execute(lua_State* L){ int i = 0; env_data *env; conn_data *conn; stmt_data *stmt = getstatement (L); sword status; ub4 prefetch = 0; ub4 iters; ub4 mode; ub2 type; /* get environment */ lua_rawgeti(L, LUA_REGISTRYINDEX, stmt->conn); conn = (conn_data*)lua_touserdata(L, -1); lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env); if (!lua_isuserdata (L, -1)) luaL_error(L,LUASQL_PREFIX"invalid environment in connection!"); env = (env_data *)lua_touserdata (L, -1); lua_pop(L, 2); //debug //printf("execute>> text: %s\n", stmt->text); /* statement handle */ ASSERT (L, OCIAttrSet ((dvoid *)stmt->stmthp, (ub4)OCI_HTYPE_STMT, (dvoid *)&prefetch, (ub4)0, (ub4)OCI_ATTR_PREFETCH_ROWS, conn->errhp), conn->errhp); /* statement type */ ASSERT (L, OCIAttrGet ((dvoid *)stmt->stmthp, (ub4) OCI_HTYPE_STMT, (dvoid *)&type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, conn->errhp), conn->errhp); if (type == OCI_STMT_SELECT) iters = 0; else iters = 1; if (conn->auto_commit) mode = OCI_COMMIT_ON_SUCCESS; else mode = OCI_DEFAULT; // Bind data to SQLStatement for ( i = 0; i < stmt->numpars; i++) { //debug //printf("execute>> %d %s %d\n", i, stmt->parvals[0].val.s, stmt->numpars); switch(stmt->parvals[i].type){ case NUMBER: ASSERT (L, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, &(stmt->parvals[i].val.i), sizeof(stmt->parvals[i].val.i), SQLT_INT, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp); break; case STRING: ASSERT (L, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, stmt->parvals[i].val.s, strlen(stmt->parvals[i].val.s) + 1, SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp); break; case CURSOR: ASSERT (L, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, &(stmt->parvals[i].val.c), sizeof(stmt->parvals[i].val.c), SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp); break; default: break; } } status = OCIStmtExecute (conn->svchp, stmt->stmthp, conn->errhp, iters, (ub4)0, (CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, mode); //debug //printf("execute>> type: %d status: %d\n", type, status); if (status && (status != OCI_NO_DATA)) { //debug /*text errbuf[512]; sb4 errcode; (void)OCIErrorGet((dvoid *)conn->errhp,(ub4)1,NULL,&errcode, errbuf,(ub4)sizeof(errbuf),OCI_HTYPE_ERROR); printf("错误号：%d\n错误信息：%s\n",errcode,errbuf); */ OCIHandleFree ((dvoid *)stmt->stmthp, OCI_HTYPE_STMT); return checkerr (L, status, conn->errhp); } //get data if ( OCI_STMT_SELECT == type) { /* create cursor */ return create_cursor (L, 1, conn, stmt->stmthp, stmt->text); } else if ( OCI_STMT_BEGIN == type ){ /* handle the bind data */ return data_handle(L, stmt); } else { /* return number of rows */ int rows_affected; ASSERT (L, OCIAttrGet ((dvoid *)stmt->stmthp, (ub4)OCI_HTYPE_STMT, (dvoid *)&rows_affected, (ub4 *)0, (ub4)OCI_ATTR_ROW_COUNT, conn->errhp), conn->errhp); OCIHandleFree ((dvoid *)stmt->stmthp, OCI_HTYPE_STMT); lua_pushnumber (L, rows_affected); return 1; }}LUA接口导出:
#define LUASQL_STATEMENT_OCI8 "Oracle statement"struct luaL_reg connection_methods[] = { {"close", conn_close}, {"prepare", conn_prepare}, //add {"execute", conn_execute}, {"commit", conn_commit}, {"rollback", conn_rollback}, {"setautocommit", conn_setautocommit}, {NULL, NULL}, };struct luaL_reg statement_methods[] = { {"close", stmt_close}, {"bind_number", stmt_bind_number}, {"bind_string", stmt_bind_string}, {"bind_cursor", stmt_bind_cursor}, {"execute", stmt_execute}, {NULL, NULL} }; //add by alienluasql_createmeta (L, LUASQL_STATEMENT_OCI8, statement_methods);