背景
在Oracle pro*c中在匿名块中支持数组变量参数绑定,让嵌套表与C数组变量之间能够轻松灵活传递。
LightDB在2024RP1版本里面,将支持ecpg匿名块数组变量绑定,全面对标Oracle。
案例如下:
- 创建函数
--新建常用数据类型的嵌套表类型
create or replace package hstype is
type hsvarslist is table of varchar2;
type hsvarclist is table of varchar;
type hsdoublist is table of numeric;
type hsflotlist is table of real;
type hsint2list is table of smallint;
type hsint4list is table of int;
type hsint8list is table of bigint;
end;
/
--新建函数覆盖常用数据类型数组,数组值包含需转义字符
create or replace function fhsarray(
p1 inout hstype.hsvarslist,
p2 inout hstype.hsvarclist,
p3 inout hstype.hsdoublist,
p4 inout hstype.hsflotlist,
p5 inout hstype.hsint2list,
p6 inout hstype.hsint4list,
p7 inout hstype.hsint8list,
id inout numeric) return int
as
begin
id := id + 1;
p1(id) := 'china';
p2(id) := 'hongkong';
p3(id) := 1.1;
p4(id) := 1.2;
p5(id) := 1;
p6(id) := 512;
p7(id) := 65535;
id := id + 1;
p1(id) := chr(34) || 'korea' || chr(34);
p2(id) := chr(47) || 'seoul' || chr(47);
p3(id) := 1.3;
p4(id) := 1.4;
p5(id) := 2;
p6(id) := 513;
p7(id) := 65536;
id := id + 1;
p1(id) := 'japan';
p2(id) := 'tokyo';
p3(id) := 1.4;
p4(id) := 1.5;
p5(id) := 3;
p6(id) := 514;
p7(id) := 65537;
id := id + 1;
p1(id) := 'russia';
p2(id) := 'mosco';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
id := id + 1;
p1(id) := 'america';
p2(id) := 'washington';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
id := id + 1;
p1(id) := 'canada';
p2(id) := 'ottawa';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
return 1;
end;
/
- 创建test01.pgc文件
#include <stdio.h>
#include <stdlib.h>
void
print_sqlca()
{
printf("==== sqlca ====\n");
printf("sqlcode: %ld\n", sqlca.sqlcode);
printf("sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
printf("sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
printf("sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
printf("sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
printf("sqlstate: %5s\n", sqlca.sqlstate);
printf("===============\n");
}
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
const char *target = "oracledb@192.168.226.100";
const char *user = "lightdb";
const char *passwd = "lightdb";
long pid = 0;
int id = 0;
int i = 0;
int ret = 0;
varchar vvcr1arr[3][100];
varchar vvcr2arr[3][100];
char vchr1arr[3][100];
char vchr2arr[3][100];
double vdoubarr[3];
float vflotarr[3];
short vint2arr[3];
int vint4arr[3];
long long int vint8arr[3];
EXEC SQL END DECLARE SECTION;
memset(vvcr1arr,0,sizeof(vvcr1arr));
memcpy(vvcr1arr[0].arr, "abc", 3);
vvcr1arr[0].len = 3;
memcpy(vvcr1arr[1].arr, "def", 3);
vvcr1arr[1].len = 3;
memcpy(vvcr1arr[2].arr, "ghi", 3);
vvcr1arr[2].len = 3;
memset(vvcr2arr,0,sizeof(vvcr2arr));
memcpy(vvcr2arr[0].arr, "qaz", 3);
vvcr2arr[0].len = 3;
memcpy(vvcr2arr[1].arr, "wsx", 3);
vvcr2arr[1].len = 3;
memcpy(vvcr2arr[2].arr, "edc", 3);
vvcr2arr[2].len = 3;
memset(vchr1arr,0,sizeof(char)* 3 * 100);
memcpy(vchr1arr[0], "abc", 3);
memcpy(vchr1arr[1], "def", 3);
memcpy(vchr1arr[2], "ghi", 3);
memset(vchr2arr,0,sizeof(char)* 3 * 100);
memcpy(vchr2arr[0], "qaz", 3);
memcpy(vchr2arr[1], "wsx", 3);
memcpy(vchr2arr[2], "edc", 3);
memset(vdoubarr,0,sizeof(double)*3);
memset(vflotarr,0,sizeof(float) *3);
memset(vint2arr,0,sizeof(short) *3);
memset(vint4arr,0,sizeof(int) *3);
memset(vint8arr,0,sizeof(long long int)*3);
EXEC SQL CONNECT TO :target USER :user USING :passwd;
EXEC SQL WHENEVER SQLERROR CALL print_sqlca();
EXEC SQL SELECT pg_backend_pid() INTO :pid;
printf("current pid=%ld\n", pid);
EXEC SQL
DO $$
BEGIN
:ret := fhsarray(:vvcr1arr,:vvcr2arr,:vdoubarr,:vflotarr,:vint2arr,:vint4arr,:vint8arr,:id);
END;
$$ LANGUAGE plorasql;
printf("id=%d,ret=%d\n", id,ret);
for (i = 0; i < 3; i++)
printf("index=%d,varchar2value=%s,varcharvalue=%s,doublevalue=%f,floatvalue=%f,int2value=%hd,int4value=%d,int8value=%lld\n", i,vvcr1arr[i].arr,vvcr2arr[i].arr,vdoubarr[i],vflotarr[i],vint2arr[i],vint4arr[i],vint8arr[i]);
EXEC SQL DISCONNECT;
}
- 编译运行test01.pgc文件
ecpg test01.pgc
gcc -g -o test01 -I/home/lightdb/stage/lightdb-x/include -L/home/lightdb/stage/lightdb-x/lib -lecpg ./test01.c
--运行test01
[lightdb@localhost test]$ ./test01
current pid=7916
id=6,ret=1
index=0,varchar2value=china,varcharvalue=hongkong,doublevalue=1.100000,floatvalue=1.200000,int2value=1,int4value=512,int8value=65535
index=1,varchar2value="korea",varcharvalue=/seoul/,doublevalue=1.300000,floatvalue=1.400000,int2value=2,int4value=513,int8value=65536
index=2,varchar2value=japan,varcharvalue=tokyo,doublevalue=1.400000,floatvalue=1.500000,int2value=3,int4value=514,int8value=65537
- 修改函数内容增加超限字段
--修改函数内容增加超限字段
create or replace function fhsarray(
p1 inout hstype.hsvarslist,
p2 inout hstype.hsvarclist,
p3 inout hstype.hsdoublist,
p4 inout hstype.hsflotlist,
p5 inout hstype.hsint2list,
p6 inout hstype.hsint4list,
p7 inout hstype.hsint8list,
id inout numeric) return int
as
begin
id := id + 1;
p1(id) := chr(34) || 'china' || chr(34);
p2(id) := chr(47) || 'hongkong' || chr(47);
p3(id) := 1.1;
p4(id) := 1.2;
p5(id) := 1;
p6(id) := 512;
p7(id) := 65535;
id := id + 1;
p1(id) := 'korea';
p2(id) := 'seoul-****************************************************************************************************';
p3(id) := 1.3;
p4(id) := 1.4;
p5(id) := 2;
p6(id) := 513;
p7(id) := 65536;
id := id + 1;
p1(id) := 'japan';
p2(id) := 'tokyo';
p3(id) := 1.4;
p4(id) := 1.5;
p5(id) := 3;
p6(id) := 514;
p7(id) := 65537;
id := id + 1;
p1(id) := 'russia';
p2(id) := 'mosco';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
id := id + 1;
p1(id) := 'america';
p2(id) := 'washington';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
id := id + 1;
p1(id) := 'canada';
p2(id) := 'ottawa';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
return 1;
end;
/
- 再次运行test01
--再次运行test01
[lightdb@localhost test]$ ./test01
current pid=8349
id=6,ret=1
index=0,varchar2value="china",varcharvalue=/hongkong/,doublevalue=1.100000,floatvalue=1.200000,int2value=1,int4value=512,int8value=65535
index=1,varchar2value=korea,varcharvalue=seoul-*********************************************************************************************,doublevalue=1.300000,floatvalue=1.400000,int2value=2,int4value=513,int8value=65536
index=2,varchar2value=japan,varcharvalue=tokyo,doublevalue=1.400000,floatvalue=1.500000,int2value=3,int4value=514,int8value=65537
- 新建存储过程覆盖常用数据类型数组,数组值包含需转义字符
create or replace procedure phsarray(
p1 inout hstype.hsvarslist,
p2 inout hstype.hsvarclist,
p3 inout hstype.hsdoublist,
p4 inout hstype.hsflotlist,
p5 inout hstype.hsint2list,
p6 inout hstype.hsint4list,
p7 inout hstype.hsint8list,
id inout numeric)
as
begin
id := id + 1;
p1(id) := chr(34) || 'china' || chr(34);
p2(id) := chr(47) || 'hongkong' || chr(47);
p3(id) := 1.1;
p4(id) := 1.2;
p5(id) := 1;
p6(id) := 512;
p7(id) := 65535;
id := id + 1;
p1(id) := 'korea';
p2(id) := 'seoul';
p3(id) := 1.3;
p4(id) := 1.4;
p5(id) := 2;
p6(id) := 513;
p7(id) := 65536;
id := id + 1;
p1(id) := chr(47) || 'japan' || chr(47);
p2(id) := chr(47) || 'tokyo' || chr(47);
p3(id) := 1.4;
p4(id) := 1.5;
p5(id) := 3;
p6(id) := 514;
p7(id) := 65537;
id := id + 1;
p1(id) := 'russia';
p2(id) := 'mosco';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
id := id + 1;
p1(id) := 'america';
p2(id) := 'washington';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
id := id + 1;
p1(id) := 'canada';
p2(id) := 'ottawa';
p3(id) := 1.5;
p4(id) := 1.6;
p5(id) := 4;
p6(id) := 515;
p7(id) := 65538;
end;
/
- 编辑test02.pgc
#include <stdio.h>
#include <stdlib.h>
void
print_sqlca()
{
printf("==== sqlca ====\n");
printf("sqlcode: %ld\n", sqlca.sqlcode);
printf("sqlerrm.sqlerrml: %d\n", sqlca.sqlerrm.sqlerrml);
printf("sqlerrm.sqlerrmc: %s\n", sqlca.sqlerrm.sqlerrmc);
printf("sqlerrd: %ld %ld %ld %ld %ld %ld\n", sqlca.sqlerrd[0],sqlca.sqlerrd[1],sqlca.sqlerrd[2],sqlca.sqlerrd[3],sqlca.sqlerrd[4],sqlca.sqlerrd[5]);
printf("sqlwarn: %d %d %d %d %d %d %d %d\n", sqlca.sqlwarn[0], sqlca.sqlwarn[1], sqlca.sqlwarn[2],sqlca.sqlwarn[3], sqlca.sqlwarn[4], sqlca.sqlwarn[5],sqlca.sqlwarn[6], sqlca.sqlwarn[7]);
printf("sqlstate: %5s\n", sqlca.sqlstate);
printf("===============\n");
}
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
const char *target = "oracledb@192.168.226.100";
const char *user = "lightdb";
const char *passwd = "lightdb";
long pid = 0;
int id = 0;
int i = 0;
int ret = 0;
varchar vvcr1arr[3][100];
varchar vvcr2arr[3][100];
char vchr1arr[3][100];
char vchr2arr[3][100];
double vdoubarr[3];
float vflotarr[3];
short vint2arr[3];
int vint4arr[3];
long long int vint8arr[3];
EXEC SQL END DECLARE SECTION;
memset(vvcr1arr,0,sizeof(vvcr1arr));
memcpy(vvcr1arr[0].arr, "abc", 3);
vvcr1arr[0].len = 3;
memcpy(vvcr1arr[1].arr, "def", 3);
vvcr1arr[1].len = 3;
memcpy(vvcr1arr[2].arr, "ghi", 3);
vvcr1arr[2].len = 3;
memset(vvcr2arr,0,sizeof(vvcr2arr));
memcpy(vvcr2arr[0].arr, "qaz", 3);
vvcr2arr[0].len = 3;
memcpy(vvcr2arr[1].arr, "wsx", 3);
vvcr2arr[1].len = 3;
memcpy(vvcr2arr[2].arr, "edc", 3);
vvcr2arr[2].len = 3;
memset(vchr1arr,0,sizeof(char)* 3 * 100);
memcpy(vchr1arr[0], "abc", 3);
memcpy(vchr1arr[1], "def", 3);
memcpy(vchr1arr[2], "ghi", 3);
memset(vchr2arr,0,sizeof(char)* 3 * 100);
memcpy(vchr2arr[0], "qaz", 3);
memcpy(vchr2arr[1], "wsx", 3);
memcpy(vchr2arr[2], "edc", 3);
memset(vdoubarr,0,sizeof(double)*3);
memset(vflotarr,0,sizeof(float) *3);
memset(vint2arr,0,sizeof(short) *3);
memset(vint4arr,0,sizeof(int) *3);
memset(vint8arr,0,sizeof(long long int)*3);
EXEC SQL CONNECT TO :target USER :user USING :passwd;
EXEC SQL WHENEVER SQLERROR CALL print_sqlca();
EXEC SQL SELECT pg_backend_pid() INTO :pid;
printf("current pid=%ld\n", pid);
EXEC SQL
DO $$
BEGIN
:id = 0;
phsarray(:vvcr1arr,:vvcr2arr,:vdoubarr,:vflotarr,:vint2arr,:vint4arr,:vint8arr,:id);
END;
$$ LANGUAGE plorasql;
printf("id=%d,ret=%d\n", id,ret);
for (i = 0; i < 3; i++)
printf("index=%d,varchar2value=%s,varcharvalue=%s,doublevalue=%f,floatvalue=%f,int2value=%hd,int4value=%d,int8value=%lld\n", i,vvcr1arr[i].arr,vvcr2arr[i].arr,vdoubarr[i],vflotarr[i],vint2arr[i],vint4arr[i],vint8arr[i]);
EXEC SQL DISCONNECT;
}
- 编译运行test02.pgc
--编译test02.pgc
ecpg test02.pgc
gcc -g -o test02 -I/home/lightdb/stage/lightdb-x/include -L/home/lightdb/stage/lightdb-x/lib -lecpg ./test02.c
--运行test02
[lightdb@localhost test]$ ./test02
current pid=8921
id=6,ret=0
index=0,varchar2value="china",varcharvalue=/hongkong/,doublevalue=1.100000,floatvalue=1.200000,int2value=1,int4value=512,int8value=65535
index=1,varchar2value=korea,varcharvalue=seoul,doublevalue=1.300000,floatvalue=1.400000,int2value=2,int4value=513,int8value=65536
index=2,varchar2value=/japan/,varcharvalue=/tokyo/,doublevalue=1.400000,floatvalue=1.500000,int2value=3,int4value=514,int8value=65537
通过以上的例子可以清晰的了解ecpg匿名块中函数存储过程数组变量出入参绑定的使用。
详细语法可参考LightDB官网查看: