插入到包含200列的表中

Question

我们的Oracle数据库中有一个表，有200列，我们必须插入其中，我们有一个C程序，它在该表中插入请求，它使用Pro*C调用Oracle数据库中的存储程序来完成这项工作。

目前，我们使用TLV格式｛Tag-Length-Value｝序列化所有字段，并有一个带有Varchar2参数的函数(字段的序列化列表(。例如：

008603701212345678901201100611111104800622222220000633333320100644444401201420200321164712

更易于阅读：

0086 037 012 123456789012 011 006 111111 048 006 222222 200 006 333333 201 006 444444 012 014 20200321164712

这意味着：

String Len:86
Field037 with len 12:123456789012
Field011 with len 6:111111
Field048 with len 6:222222
Field200 with len 6:333333
Field201 with len 6:444444
Field012 with len 14:20200321164712

这些字段中的每一个都映射到表中的一个字段，存储的程序解析这个巨大的字符串并将它们填充到rowtype中，最后将行插入表中。

多年来，它一直运行良好，但当我们准备好接受更多的请求(从而进行更多的插入(时，我们的DBA表示，我们正在使用大量CPU来对TLV进行反序列化。所以我们必须改变我们的插入方法。

我目前在C中创建了一个结构，并映射其中的所有字段，然后调用：

typedef struct 
{ 
char  field37[12+1];
char  field11[6+1];
char  field48[6+1];
char  field200[6+1];
char  field201[6+1];
char  field12[14+1];
} NewRecord; 
NewRecord newRecord;
TlvToStruct(sTlv, &newRecord);//Mapper Function
EXEC SQL INSERT INTO MY_TABLE(FIELD37, FIELD11, FIELD200, FIELD201, FIELD12) VALUES(:newRecord);
EXEC SQL COMMIT WORK RELEASE;

这种方法目前运行良好，但我的问题是：我应该继续开发并将所有200个字段添加到这个结构中并使用这种方法吗？还是最好使用PLSQL调用(可能开发并使用另一个插入函数(而不是这个SQL插入？

我目前知道PLSQL的好处，但这里有一些问题：

1- Pro*C does not support PLSQL Records

[Oracle文档]1

2- It is not so reasonable to have a function with 200 parameters!
3- We use Types in plsql but never used them in Pro*C(actually I tried once and I failed long ago)

我试着在这里描述这个问题，如果不清楚，请询问

感谢

==============================================

编辑：

这是我们以前的Get_Tag和Put_Tag函数：

FUNCTION GET_TAG(P_TAG_NAME IN VARCHAR2, P_TLV_STRG IN VARCHAR2, P_TAG_LEN OUT NOCOPY PLS_INTEGER, P_TAG_VALUE OUT NOCOPY VARCHAR2)
RETURN PLS_INTEGER IS
V_COUNTER_LOOP                          PLS_INTEGER := 1;
V_TLV_STRG                              VARCHAR2(4096) := SUBSTR(P_TLV_STRG, 5);
BEGIN
P_TAG_VALUE   := NULL;
P_TAG_LEN     := 0;
WHILE V_COUNTER_LOOP < LENGTH(V_TLV_STRG) LOOP
IF SUBSTR(V_TLV_STRG, V_COUNTER_LOOP, 3) = P_TAG_NAME THEN
P_TAG_LEN     := TO_NUMBER(SUBSTR(V_TLV_STRG, V_COUNTER_LOOP + 3, 3));
P_TAG_VALUE   := SUBSTR(V_TLV_STRG, V_COUNTER_LOOP + 6, P_TAG_LEN);
RETURN (DECLARATION_CST.OK);
END IF;
V_COUNTER_LOOP   := V_COUNTER_LOOP + 6 + TO_NUMBER(SUBSTR(V_TLV_STRG, V_COUNTER_LOOP + 3, 3));
END LOOP;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END GET_TAG;

=================================================

FUNCTION PUT_TAG(P_TAG_NAME IN VARCHAR2, P_TAG_VALUE IN VARCHAR2, P_TLV_STRG IN OUT NOCOPY VARCHAR2)
RETURN PLS_INTEGER IS
V_COUNTER_LOOP                          PLS_INTEGER := 0;
TMP_VARCHAR                             VARCHAR2(4096);
BEGIN
P_TLV_STRG       := SUBSTR(P_TLV_STRG, 5);
V_COUNTER_LOOP   := 1;
WHILE V_COUNTER_LOOP < LENGTH(P_TLV_STRG) LOOP
IF SUBSTR(P_TLV_STRG, V_COUNTER_LOOP, 3) = SUBSTR(P_TAG_NAME, 1, 3) THEN
TMP_VARCHAR   :=
SUBSTR(P_TLV_STRG, 1, V_COUNTER_LOOP - 1)
|| SUBSTR(P_TAG_NAME, 1, 3)
|| TO_CHAR(NVL(LENGTH(P_TAG_VALUE), 0), 'FM000')
|| P_TAG_VALUE
|| SUBSTR(P_TLV_STRG, V_COUNTER_LOOP + 6 + TO_NUMBER(SUBSTR(P_TLV_STRG, V_COUNTER_LOOP + 3, 3)));
P_TLV_STRG   := TO_CHAR(LENGTH(TMP_VARCHAR), 'FM0000') || TMP_VARCHAR;
RETURN (DECLARATION_CST.OK);
END IF;
V_COUNTER_LOOP   := V_COUNTER_LOOP + 6 + TO_NUMBER(SUBSTR(P_TLV_STRG, V_COUNTER_LOOP + 3, 3));
END LOOP;
P_TLV_STRG       :=
P_TLV_STRG
|| SUBSTR(P_TAG_NAME, 1, 3)
|| TO_CHAR(NVL(LENGTH(P_TAG_VALUE), 0), 'FM000')
|| P_TAG_VALUE;
P_TLV_STRG       := TO_CHAR(LENGTH(P_TLV_STRG), 'FM0000') || P_TLV_STRG;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END PUT_TAG;

Answer 1

您需要首先在数据库中创建映射表，该映射表映射到字段及其长度(在我的示例中，我使用CTE作为映射表：YOUR_MAPPING_TABLE(。

Oracle设置：

SQL> CREATE TABLE TEST1234 (
2      FIELD001   VARCHAR2(4000),
3      FIELD002   VARCHAR2(4000),
4      FIELD003   VARCHAR2(4000),
5      FIELD004   VARCHAR2(4000)
6  );
Table created.

解决方案查询：

SQL> INSERT INTO TEST1234
2  WITH YOUR_MAPPING_TABLE (FIELD_ID, LEN)
3  AS (
4  SELECT 'FIELD001', 4 FROM DUAL UNION ALL
5  SELECT 'FIELD002', 3 FROM DUAL UNION ALL
6  SELECT 'FIELD003', 3 FROM DUAL UNION ALL
7  SELECT 'FIELD004', 12 FROM DUAL
8  )
9  SELECT * FROM
10      ( SELECT M.FIELD_ID, -- Your string will go in following substring
11               SUBSTR('0086037012123456789012', M.STARTPOS + 1, M.TOTALLEN) AS FIELDVALUE 
12          FROM ( SELECT FIELD_ID,
13                        SUM(LEN) OVER(ORDER BY FIELD_ID ) - LEN AS STARTPOS,
14                        LEN   AS TOTALLEN
15                   FROM YOUR_MAPPING_TABLE
16               ) M
17      ) PIVOT (
18          MIN ( FIELDVALUE )
19          FOR FIELD_ID IN ( 'FIELD001', 'FIELD002', 'FIELD003', 'FIELD004' )
20      );
1 row created.

测试结果

SQL> SELECT * FROM TEST1234;
FIELD001   | FIELD002   | FIELD003   | FIELD004
---------- | ---------- | ---------- | -------------
0086       | 037        | 012        | 123456789012
SQL>

您可以根据自己的逻辑在查询中传递大字符串。