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

运行系统 solaris 10

数据库 oracle 10g

sampleArray.pc内容

1 #include

2 #include

3 #include

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