批量从数据库是提取数据，并显示出来。

运行系统 solaris 10

数据库 oracle 10g

sampleArray.pc内容

 1 #include <stdio.h>
 2 #include <string.h>
 3 #include <sqlca.h>
 4 #define  USERNAME_LEN      20        /*用户名的长度*/
 5 #define  PASSWORD_LEN      20       /*密码长度*/
 6 #define  ARRAY_SIZE        100          /*数组大小*/
 7 exec sql begin declare section;
 8     varchar username[USERNAME_LEN];
 9     varchar password[PASSWORD_LEN];
10     int dept_number;
11     int select_rows;
12     int emp_number[ARRAY_SIZE];
13     varchar emp_name[ARRAY_SIZE][20];
14     varchar job[ARRAY_SIZE][15];
15     float salary[ARRAY_SIZE];
16 exec sql end declare section;
17
18 void sql_error(char *);    /*出错信息*/
19 void getConn(void);        /*数据连的连接*/
20
21 void main(void)
22 {
23     int n,i;
24     getConn();
25     printf("/nEnter dept number:");
26     scanf("%d",&dept_number);
27     exec sql whenever sqlerror do sql_error("select error...");
28     exec sql whenever not found continue;
29     exec sql select empno,ename,job,sal
30         into :emp_number,:emp_name,:job,:salary
31         from emp
32         where deptno = :dept_number order by empno;
33     n = sqlca.sqlerrd[2];      /*获取查询出来有多条记录*/
34     printf("count is %d /n",n);
35     for(i = 0 ; i < n ; i++)     /*循环打印出记录。*/
36     {
37         printf("%d /t %s/t/t %s/t/t %6.2f/n",emp_number[i],
38             emp_name[i].arr,job[i].arr,salary[i]);             /* 这里要注意得是，emp_name[i].arr 如果不这样写的话，会出core，因为他在解析成 .c文件时候，从数据里提取出来的值存放在 .arr里面。*/
39     }
40     exec sql commit work release;
41     exit(0);
42 }
43 void getConn(void)
44 {
45     strcpy(username.arr,"username");
46     username.len = strlen( username.arr );
47     strcpy(password.arr,"password");
48     password.len = strlen( password.arr );
49     exec sql whenever sqlerror do sql_error("Connected to oracle error.");
50     exec sql connect :username identified by :password;
51     printf("Connected to oracle user:%s/n",username.arr);
52 }
53 void sql_error(char *msg)
54 {
55     char err_msg[512];
56     int buf_size,msg_len;
57     exec sql whenever sqlerror continue;
58     printf("/n%s/n",msg);
59     buf_size = sizeof(err_msg);
60     sqlglm(err_msg,&buf_size,&msg_len);
61     printf("%.*s/n",msg_len,err_msg);
62     exec sql rollback work release;
63     exit(-1);
64 }

 

涉及到的表：emp表。

create table EMP
(
  EMPNO    NUMBER(8) not null,
  ENAME    VARCHAR2(20),
  JOB      VARCHAR2(9),
  MGR      NUMBER(4) default 100,
  HIREDATE DATE default sysdate,
  SAL      NUMBER(7,2) default 200,
  COMM     NUMBER(7,2) default 130,
  DEPTNO   NUMBER(2) default 1
)

 

运行结果：

XTN-test:$ sampleArray
Connected to oracle user:username

Enter dept number:2
count is 9
1        test1           job1             34.00
2        test2           jbb2            1234.00
3        xtn1            xtn              21.00
4        lhb             temp1            10.00
5        liaohb          temp1            10.00
6        a               b               200.00
8        test1           job1             34.00
9        xtn2            xtn              11.00
10       xtn3            xtn              11.00

 

makefile:

 1 include ${ORACLE_HOME}/rdbms/lib/env_rdbms.mk
 2 ORAINC=${ORACLE_HOME}/precomp/public
 3 ORALIB=${ORACLE_HOME}/lib
 4 ORAPUBLIC=${ORACLE_HOME}/rdbms/public
 5
 6 PROC = proc
 7 CC = gcc -ggdb3 -Wall
 8
 9 CFLAGS = -I $(ORAINC) -I $(ORAPUBLIC)

66 obj_Array = sampleArray.o
67 sampleArray:$(obj_Array)
68     $(CC) $(CFLAGS) $(obj_Array) -o sampleArray -L $(ORALIB) $(PROLDLIBS) -lm
69 sampleArray.c:sampleArray.pc
70     $(PROC) INCLUDE=$(ORAINC) INAME=sampleArray.pc ONAME=sampleArray.c
71
72 .PHONY:clean
73 clean:
76     rm -f sampleArray
77     rm -f *.c