oracle常用插入一条语句,Oracle：用一条 INSERT 语句批量插入多条记录

用一条 INSERT 语句批量插入多条记录，实例如下：

先建立这样一个表 T：

SQL> DESC T

Name                       Null?    Type

----------------------------------------- -------- ----------------------------

NUM                            NUMBER

BIN                            RAW(30)

程序代码：

typedef struct RAWTYPE {

unsigned short  len;

unsigned char buf[30];   // buf的尺寸必须与表T的BIN字段的尺寸一致。

} RAWTYPE;

EXEC SQL TYPE RAWTYPE IS VARRAW(30);   // 同样，VARRAW的尺寸也必须与表T的BIN字段的尺寸一致。

void fun(...) // 函数

{

...

int       wnum[3];

int       rnum[3];

RAWTYPE   wbin[3];

RAWTYPE   rbin[3];

memcpy(wbin[0].buf, "123", 3);

wbin[0].len = 3;

wnum[0] = 1;

memcpy(wbin[1].buf, "abcdef", 6);

wbin[1].len = 6;

wnum[1] = 2;

memcpy(wbin[2].buf, "中华人民共和国", 14);

wbin[2].len = 14;

wnum[2] = 3;

printf("\n--- write: ---\n");

for(n=0; n<3; n++)

{

printf("wnum[%d] = %d\n", n, wnum[n]);

printf("wbin[%d](len = %d): ", n, wbin[n].len);

for(i=0; i

printf("%02X ", wbin[n].buf[i]);

printf("\n");

}

EXEC SQL insert into T (NUM, BIN) values (:wnum, :wbin);

EXEC SQL COMMIT WORK;

printf("\n--- read: ---\n");

EXEC SQL select NUM, BIN INTO :rnum, :rbin from T;

for(n=0; n<3; n++)

{

printf("rnum[%d] = %d\n", n, rnum[n]);

printf("rbin[%d](len = %d): ", n, rbin[n].len);

for(i=0; i

printf("%02X ", rbin[n].buf[i]);

printf("\n");

}

...

}

运行结果：

--- write: ---

wnum[0] = 1

wbin[0](len = 3): 31 32 33

wnum[1] = 2

wbin[1](len = 6): 61 62 63 64 65 66

wnum[2] = 3

wbin[2](len = 14): E4 B8 AD E5 8D 8E E4 BA BA E6 B0 91 E5 85

--- read: ---

rnum[0] = 1

rbin[0](len = 3): 31 32 33

rnum[1] = 2

rbin[1](len = 6): 61 62 63 64 65 66

rnum[2] = 3

rbin[2](len = 14): E4 B8 AD E5 8D 8E E4 BA BA E6 B0 91 E5 85

再看看表T里的记录：

SQL> SELECT * FROM T;

NUM BIN

---------- ------------------------------------------------------------

1 313233

2 616263646566

3 E4B8ADE58D8EE4BABAE6B091E585