云数据库 GAUSSDB-执行动态非查询语句:示例

时间:2024-01-23 20:09:25

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
--创建表
postgres=# CREATE TABLE sections_t1
(
   section       NUMBER(4) ,
   section_name  VARCHAR2(30),
   manager_id    NUMBER(6),
   place_id      NUMBER(4) 
)
DISTRIBUTE BY hash(manager_id);

--声明变量
postgres=# DECLARE 
   section       NUMBER(4) := 280; 
   section_name  VARCHAR2(30) := 'Info support'; 
   manager_id    NUMBER(6) := 103;
   place_id      NUMBER(4) := 1400;
   new_colname   VARCHAR2(10) := 'sec_name';
BEGIN 
--执行查询
    EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :4)' 
       USING section, section_name, manager_id,place_id; 
--执行查询(重复占位符)
    EXECUTE IMMEDIATE 'insert into sections_t1 values(:1, :2, :3, :1)' 
       USING section, section_name, manager_id; 
--执行ALTER语句(建议采用“||”拼接数据库对象构造DDL语句)
    EXECUTE IMMEDIATE 'alter table sections_t1 rename section_name to ' || new_colname;
END; 
/

--查询数据
postgres=# SELECT * FROM sections_t1;

--删除表
postgres=# DROP TABLE sections_t1;
support.huaweicloud.com/devg-v1-gaussdb/gaussdb_devg_0671.html