PROC提取資料

PROC提取資料[@more@]

1.建立fetch_data.pc檔案,內容如下:

#include "sqlca.h"
#include
#include
#include
#include
#include
#include
#include
#include "oci.h"

//定義列和繫結變數的最大個數
#define MAX_ITEMS 40

//定義列名的最大值
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30

extern 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);
char *strtoupper(char *string);

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[30];
char sql_statement[256]= "select * from ";
char type_statement[256]="select COLUMN_NAME,DATA_TYPE from all_tab_columns where table_name='";
char tablename[20];
int i;
EXEC SQL END DECLARE SECTION;

SQLDA *bind_p;
SQLDA *select_p;

int main()
{
strcpy(oraCN.arr,"test/test@test");
oraCN.len = strlen(oraCN.arr);
oraCN.arr[oraCN.len]='';
EXEC SQL CONNECT :oraCN;
printf("n [OK Connected!] nn");

EXEC SQL WHENEVER SQLERROR DO sql_error("");
printf("請輸入表名:");
scanf("%s",tablename);
strcat(sql_statement,strtoupper(tablename));
strcat(type_statement,strtoupper(tablename));
strcat(type_statement,"'");
//測試輸出
printf("%sn",sql_statement);
printf("%sn",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_NAMEttDATA_TYPEn");
printf("----------------------------------------------------------n");
for(;;)
{ EXEC SQL WHENEVER NOT FOUND DO break;

EXEC SQL FETCH C1 USING DESCRIPTOR select_p;
for(i = 0;iF;i++){
printf("%s ",select_p->V[i]);
}
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;iF;i++)
printf("%s ",select_p->V[i]);
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{
bind_p->I[i] = (short*)malloc(sizeof(short));
select_p->I[i] = (short*)malloc(sizeof(short));
bind_p->V[i] = (char*)malloc(1);
select_p->V[i] = (char*)malloc(1);
}
return 0;
}
//繫結變數的設定：
void set_bind_v()
{
int i;
//char bind_var[64];
EXEC SQL WHENEVER SQLERROR DO sql_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;iN;i++)
{

bind_p->T[i] = 1;
}
}
//釋放記憶體SQLDA的函式：
void free_da()
{
int i,j;
for (i=0; i < MAX_ITEMS; i++)
{
if (select_p->V[i] != (char *)NULL)
free(select_p->V[i]);
free(select_p->I[i]);
}
for (j=0; j < MAX_ITEMS; j++)
{
if (bind_p->V[j] != (char *)NULL)
free(bind_p->V[j]);
free(bind_p->I[j]);
}
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;iN;i++)
{
sqlnul(&(select_p->T[i]), &(select_p->T[i]), &null_ok);//檢查型別是否為空
switch (select_p->T[i])
{
case 1://VARCHAR2
break;
case 2://NUMBER
sqlprc(&(select_p->L[i]), &precision, &scale);
if (precision == 0)
precision = 40;
select_p->L[i] = precision + 2;
break;
case 8://LONG
select_p->L[i] = 240;
break;
case 11://ROWID
select_p->L[i] = 18;
break;
case 12://DATE
select_p->L[i] = 9;
break;
case 23://RAW
break;
case 24://LONGRAW
select_p->L[i] = 240;
break;
}
select_p->V[i] = (char *)realloc(select_p->V[i], select_p->L[i]+1);
select_p->V[i][select_p->L[i]] ='';//加上終止符
select_p->T[i] = 1;//把所有型別轉換為字元型
}
}
void sql_error(char *msg)
{
printf("n%s %sn", msg,(char *)sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK RELEASE;
exit(0);
}

char *
strtoupper(char *string)
{
int i;

for(i = 0; i <= strlen(string); i++){
string[i] = toupper(string[i]);
}
return string;
}

2.編譯fet_data.pc檔案

proc parse=none iname=fetch_data.pc
sqlcheck=semantics

cc -o fetch_data -qcpluscmt -I.
-I${ORACLE_HOME}/precomp/public
-I${ORACLE_HOME}/rdbms/public
-I${ORACLE_HOME}/rdbms/demo
-I${ORACLE_HOME}/plsql/public
-I${ORACLE_HOME}/network/public
-L${ORACLE_HOME}/lib32 -lclntsh `cat ${ORACLE_HOME}/lib32/sysliblist` -lm -lpthread
fetch_data.c

3.執行可以執行檔案fet_data