PRO*C编程动态SQL中SQLDA小试牛刀
最近准备ORACLE的作业，其中有一题要求模糊，不管怎么，我用了一个看似很麻烦的方法完成了这样一件事：输入一个表名，输出它的列名类型，然后输出所有的里面的值。
具体做法使用了动态SQL里面的最麻烦的方法，即使用SQLDA，网上的使用例子很少，所以我把自己写的贴上来，希望能给像我一样的PRO*C初学者一点启发，其中肯定有不足之处，还望高手能不吝赐教。
#include "sqlca.h"#include <stdio.h>#include <string.h>#include <stdlib.h>#include <sqlda.h>#include <sqlcpr.h>#include "oci.h"//定义列和绑定变量的最大个数#define MAX_ITEMS 40//定义列名的最大值#define MAX_VNAME_LEN 30#define MAX_INAME_LEN 30extern void sqlnul( unsigned short*, unsigned short*, int*);extern void sqlprc( unsigned int*, int*, int*);extern SQLDA *SQLSQLDAAlloc(dvoid *context, unsigned int max_vars,unsigned int max_name, unsigned int max_ind_name);int alloc_descriptor(int size,int max_vname_len,int max_iname_len);void set_bind_v();void set_select_v();void free_da();void sql_error(char *msg); EXEC SQL INCLUDE SQLCA; EXEC ORACLE OPTION (ORACA = YES); EXEC ORACLE OPTION (RELEASE_CURSOR = YES); EXEC SQL INCLUDE SQLDA;//变量定义区： EXEC SQL BEGIN DECLARE SECTION; VARCHAR oraCN; char sql_statement= "select * from "; char type_statement="select COLUMN_NAME,DATA_TYPE from all_tab_columns where table_name='"; char tablename=""; int i; EXEC SQL END DECLARE SECTION;SQLDA *bind_p; SQLDA *select_p; int main() { strcpy(oraCN.arr,"scott/Tiger@orcl"); oraCN.len = strlen(oraCN.arr); oraCN.arr='\0'; EXEC SQL CONNECT :oraCN; printf("\n \n\n"); EXEC SQL WHENEVER SQLERROR DO sql_error("<ERROR>"); printf("请输入表名:"); scanf("%s",tablename); strupr(tablename); strcat(sql_statement,tablename); strcat(type_statement,tablename); strcat(type_statement,"'"); alloc_descriptor(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN); EXEC SQL PREPARE S from :type_statement; EXEC SQL DECLARE C1 CURSOR FOR S; set_bind_v(); EXEC SQL OPEN C1 USING DESCRIPTOR bind_p; EXEC SQL DESCRIBE SELECT LIST for S INTO select_p; set_select_v(); printf("COLUMN_NAME\t\tDATA_TYPE\n"); printf("----------------------------------------------------------\n"); for(;;) { EXEC SQL WHENEVER NOT FOUND DO break; EXEC SQL FETCH C1 USING DESCRIPTOR select_p; for(i = 0;i<select_p->F;i++){printf("%s ",select_p->V);} printf("\n");}free_da();EXEC SQL CLOSE C1; printf("\n--------------------------------------------------------------------------------\n");alloc_descriptor(MAX_ITEMS,MAX_VNAME_LEN,MAX_INAME_LEN); EXEC SQL PREPARE S from :sql_statement; EXEC SQL DECLARE C CURSOR FOR S; set_bind_v(); EXEC SQL OPEN C USING DESCRIPTOR bind_p; EXEC SQL DESCRIBE SELECT LIST for S INTO select_p; set_select_v(); for(;;) { EXEC SQL WHENEVER NOT FOUND DO break; EXEC SQL FETCH C USING DESCRIPTOR select_p; for(i = 0;i<select_p->F;i++)printf("%s ",select_p->V); printf("\n");}free_da();EXEC SQL CLOSE C;EXEC SQL COMMIT WORK RELEASE; exit(0);}//描述符分配空间函数：int alloc_descriptor(int size,int max_vname_len,int max_iname_len){int i;if((bind_p=SQLSQLDAAlloc(0,size,max_vname_len,max_iname_len))==(SQLDA*)0){printf("can't allocate memory for bind_p.");return -1;}if((select_p=SQLSQLDAAlloc(0,size,max_vname_len,max_iname_len))==(SQLDA*)0){printf("can't allocate memory for select_p.");return -1;}select_p->N = MAX_ITEMS;for(i=0;i<MAX_ITEMS;i++){bind_p->I = (short*)malloc(sizeof(short));select_p->I = (short*)malloc(sizeof(short));bind_p->V = (char*)malloc(1);select_p->V = (char*)malloc(1);}return 0;}//绑定变量的设置：void set_bind_v(){int i;//char bind_var;EXEC SQL WHENEVER SQLERROR DO sql_error("<ERROR>");bind_p ->N = MAX_ITEMS;EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_p;if(bind_p->F<0){printf("Too Many bind varibles");return;}bind_p->N = bind_p->F;for(i=0;i<bind_p->N;i++){bind_p->T = 1;}}//释放内存SQLDA的函数：void free_da(){int i,j;for (i=0; i < MAX_ITEMS; i++) {if (select_p->V != (char *)NULL)free(select_p->V);free(select_p->I); }for (j=0; j < MAX_ITEMS; j++){if (bind_p->V != (char *)NULL)free(bind_p->V);free(bind_p->I);}SQLSQLDAFree(0,bind_p);SQLSQLDAFree(0,select_p);}//选择列处理void set_select_v(){int i,null_ok,precision,scale; EXEC SQL DESCRIBE SELECT LIST for S INTO select_p; if(select_p->F<0){printf("Too Many column varibles");return;}select_p->N = select_p->F;//对格式作处理for(i = 0;i<select_p->N;i++){sqlnul(&(select_p->T), &(select_p->T), &null_ok);//检查类型是否为空switch (select_p->T) { case 1://VARCHAR2 break; case 2://NUMBER sqlprc(&(select_p->L), &precision, &scale); if (precision == 0) precision = 40; select_p->L = precision + 2; break; case 8://LONG select_p->L = 240; break; case 11://ROWID select_p->L = 18; break; case 12://DATE select_p->L = 9; break; case 23://RAW break; case 24://LONGRAW select_p->L = 240; break;}select_p->V = (char *)realloc(select_p->V, select_p->L+1);select_p->V] ='\0';//加上终止符select_p->T = 1;//把所有类型转换为字符型 }}void sql_error(char *msg){ printf("\n%s %s\n", msg,(char *)sqlca.sqlerrm.sqlerrmc); EXEC SQL ROLLBACK RELEASE; exit(0);}
如果有人需要以此完成作业，请做出相应的修改，避免雷同哦http://www.agoit.com/images/smiles/icon_smile.gif http://www.agoit.com/images/smiles/icon_smile.gif http://www.agoit.com/images/smiles/icon_smile.gif
页:
[1]