LightDB支持使用嵌套表元素作为插入数据源

machete701

已于 2024-04-08 10:39:00 修改

阅读量166

点赞数 4

文章标签：数据库 postgresql oracle

于 2024-03-14 10:50:02 首次发布

本文链接：https://blog.csdn.net/u012796067/article/details/136701528

版权

本文介绍了LightDB24.1版本开始支持的嵌套表元素插入功能，通过示例展示了如何基于表类型和全局type创建嵌套表，并使用其元素进行数据插入。这与OraclePL/SQL的集合类型概念相似，允许用户像操作数组一样处理数据。

摘要由CSDN通过智能技术生成

LightDB中使用嵌套表元素执行插入案例

为了更好地兼容oracle语法，从24.1版本开始，LightDB支持使用嵌套表元素作为数据源执行insert语句，即用嵌套表的一个元素作为整体插入到表中。文字表述还是较为抽象，下面用2个示例来演示该功能，第一个是基于表类型来创建嵌套表，并使用嵌套表元素作为数据源执行insert：

drop table if exists t;
create table t(a int,b float,c number);
insert into t values(1,10,100),(2,20,200);
select * from t;

lightdb@oracle=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
(2 rows)


create or replace procedure p1 is 
  TYPE array_table IS TABLE OF t%rowtype;
  a_table array_table := array_table();
BEGIN

  SELECT t.* BULK COLLECT INTO a_table FROM t;
  execute immediate 'truncate table t';

  FOR i IN a_table.first..a_table.last LOOP
	a_table(i).a := a_table(i).a * 100;
	a_table(i).b := a_table(i).b * 100;
	a_table(i).c := a_table(i).c * 100;
    INSERT INTO t VALUES a_table(i); --支持使用嵌套表元素作为数据源插入
  END LOOP;

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/

begin
  p1();
end;
/

select * from t;
  a  |  b   |   c   
-----+------+-------
 100 | 1000 | 10000
 200 | 2000 | 20000
(2 rows)

第二个示例是基于全局type创建嵌套表，并使用嵌套表元素作为数据源执行insert：

drop table if exists t;
create table t(a int,b float,c number);
insert into t values(1,10,100),(2,20,200);
select * from t;

lightdb@oracle=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
(2 rows)

create type type1 as (a int,b float,c number);
create or replace procedure p1 is 
  TYPE array_table IS TABLE OF type1;	--基于type创建嵌套表
  a_table array_table := array_table();
BEGIN
  execute immediate 'truncate table t';
  a_table.extend();
  a_table.extend;
  a_table(1).a := 111;
  a_table(1).b := 111.222;
  a_table(1).c := 222.222;
  INSERT INTO t VALUES a_table(1);
  
  a_table(2).a := 100;
  a_table(2).b := 100.202;
  a_table(2).c := 222.123456789;
  INSERT INTO t VALUES a_table(2);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/

Oracle Collection文档

Oracle的官网(PL/SQL Collections and Records (oracle.com))上，对于集合类型有着较为详细的描述。虽然Oracle官网并没有给出使用嵌套表元素作为数据源执行插入语句的直接案例，但有使用record作为数据源执行插入的案例。而上文所述的嵌套表实际就是一个数组容器，内含多条record记录，所以使用嵌套表元素执行插入和使用record执行插入，本质上是一样的。

此外，在“Restrictions on Record Inserts/Updates”章节, 官网有如下描述：“If the VALUES clause of an INSERT statement contains a record variable, no other variable or value is allowed in the clause.” 而这正是使用嵌套表元素，或者说使用集合元素作为一个整体执行插入的语法描述。