华为云用户手册

示例 支持在PL/SQL的存储过程内使用COMMIT/ROLLBACK。 gaussdb=# CREATE TABLE EXAMPLE1(COL1 INT); gaussdb=# CREATE OR REPLACE PROCEDURE TRANSACTION_EXAMPLE() AS BEGIN FOR i IN 0..20 LOOP INSERT INTO EXAMPLE1(COL1) VALUES (i); IF i % 2 = 0 THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END; / 支持含有EXCEPTION的存储过程使用COMMIT/ROLLBACK。 支持在存储过程的EXCEPTION语句内使用COMMIT/ROLLBACK。 支持DDL在COMMIT/ROLLBACK后的提交/回滚。 gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK() AS BEGIN DROP TABLE IF EXISTS TEST_COMMIT; CREATE TABLE TEST_COMMIT(A INT, B INT); INSERT INTO TEST_COMMIT SELECT 1, 1; COMMIT; CREATE TABLE TEST_ROLLBACK(A INT, B INT); RAISE EXCEPTION 'RAISE EXCEPTION AFTER COMMIT'; EXCEPTION WHEN OTHERS THEN INSERT INTO TEST_COMMIT SELECT 2, 2; ROLLBACK; END; / 支持在事务块里调用含有COMMIT/ROLLBACK的存储过程，即通过/BEGIN/START/END等开启控制的外部事务。 gaussdb=# BEGIN; CALL TEST_COMMIT_INSERT_EXCEPTION_ROLLBACK(); END; 支持多数PL/SQL的上下文和语句内调用COMMIT/ROLLBACK，包括常用的IF/FOR/CURSOR LOOP/WHILE。 gaussdb=# CREATE OR REPLACE PROCEDURE TEST_COMMIT2() IS BEGIN DROP TABLE IF EXISTS TEST_COMMIT; CREATE TABLE TEST_COMMIT(A INT); FOR I IN REVERSE 3..0 LOOP INSERT INTO TEST_COMMIT SELECT I; COMMIT; END LOOP; FOR I IN REVERSE 2..4 LOOP UPDATE TEST_COMMIT SET A=I; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN INSERT INTO TEST_COMMIT SELECT 4; COMMIT; END; / 支持存储过程内GUC参数的回滚提交。 gaussdb=# SHOW explain_perf_mode; gaussdb=# SHOW enable_force_vector_engine; gaussdb=# CREATE OR REPLACE PROCEDURE GUC_ROLLBACK() AS BEGIN SET enable_force_vector_engine = on; COMMIT; SET explain_perf_mode TO pretty; ROLLBACK; END; / gaussdb=# call GUC_ROLLBACK(); guc_rollback -------------- (1 row) gaussdb=# SHOW explain_perf_mode; explain_perf_mode ------------------- normal (1 row) gaussdb=# SHOW enable_force_vector_engine; enable_force_vector_engine ---------------------------- on (1 row) gaussdb=# SET enable_force_vector_engine = off;

使用限制 不支持调用的上下文环境： 不支持除PL/SQL的其他存储过程中调用COMMIT/ROLLBACK/SAVEPOINT，例如PLJAVA、PLPYTHON等。 不支持事务块中调用了SAVEPOINT后，调用含有COMMIT/ROLLBACK的存储过程。 不支持TRIGGER中调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。 不支持EXECUTE语句中调用COMMIT/ROLLBACK/SAVEPOINT语句。 不支持在CURSOR语句中打开一个含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。 不支持带有IMMUTABLE以及SHIPPABLE的存储过程调用COMMIT/ROLLBACK/SAVEPOINT，或调用带有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。 不支持SQL中调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程，除了SELECT PROC以及CALL PROC。 存储过程头带有GUC参数设置的不允许调用COMMIT/ROLLBACK/SAVEPOINT语句。 不支持CURSOR/EXECUTE语句，以及各类表达式内调用COMMIT/ROLLBACK/SAVEPOINT。 不支持存储过程返回值与表达式计算中调用含有COMMIT/ROLLBACK/SAVEPOINT的存储过程。 不支持存储过程中释放存储过程外部定义的保存点。 存储过程事务和其中的自治事务是两个独立的事务，不能互相使用对方事务中定义的保存点 不支持高级包通过DBE_SQL调用含有COMMIT/ROLLBACK/SAVEPOINT语句的存储过程。 不支持提交回滚的内容： 不支持存储过程内声明变量以及传入变量的提交/回滚。 不支持存储过程内必须重启生效的GUC参数的提交/回滚。

示例 gaussdb=# DECLARE v_num integer default NULL; BEGIN IF v_num IS NOT NULL THEN raise info 'v_num is NULL'; ELSE NULL; -- 不需要处理任何数据。 END IF; END; / ANONYMOUS BLOCK EXECUTE

示例 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 35 36 37 38 gaussdb=# CREATE OR REPLACE PROCEDURE proc_case_branch(pi_result in integer, pi_return out integer) AS BEGIN CASE pi_result WHEN 1 THEN pi_return := 111; WHEN 2 THEN pi_return := 222; WHEN 3 THEN pi_return := 333; WHEN 6 THEN pi_return := 444; WHEN 7 THEN pi_return := 555; WHEN 8 THEN pi_return := 666; WHEN 9 THEN pi_return := 777; WHEN 10 THEN pi_return := 888; ELSE pi_return := 999; END CASE; raise info 'pi_return : %',pi_return ; END; / CREATE PROCEDURE gaussdb=# CALL proc_case_branch(3,0); INFO: pi_return : 333 pi_return ----------- 333 (1 row) --删除存储过程 gaussdb=# DROP PROCEDURE proc_case_branch; DROP PROCEDURE

语法 创建函数时需要指定返回值SETOF datatype。 return_next_clause::= return_query_clause::= 对以上语法的解释如下： 当需要函数返回一个集合时，使用RETURN NEXT或者RETURN QUERY向结果集追加结果，然后继续执行函数的下一条语句。随着后续的RETURN NEXT或RETURN QUERY命令的执行，结果集中会有多个结果。函数执行完成后会一起返回所有结果。 RETURN NEXT可用于标量和复合数据类型。 RETURN QUERY有一种变体RETURN QUERY EXECUTE，后面还可以增加动态查询，通过USING向查询插入参数。

示例 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 35 36 37 38 gaussdb=# DROP TABLE t1; gaussdb=# CREATE TABLE t1(a int); gaussdb=# INSERT INTO t1 VALUES(1),(10); --RETURN NEXT gaussdb=# CREATE OR REPLACE FUNCTION fun_for_return_next() RETURNS SETOF t1 AS $$ DECLARE r t1%ROWTYPE; BEGIN FOR r IN select * from t1 LOOP RETURN NEXT r; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; gaussdb=# call fun_for_return_next(); a --- 1 10 (2 rows) -- RETURN QUERY gaussdb=# CREATE OR REPLACE FUNCTION fun_for_return_query() RETURNS SETOF t1 AS $$ DECLARE r t1%ROWTYPE; BEGIN RETURN QUERY select * from t1; END; $$ language plpgsql; gaussdb=# call fun_for_return_query(); a --- 1 10 (2 rows)

示例 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 --创建存储过程proc_add。 gaussdb=# CREATE OR REPLACE PROCEDURE proc_add ( param1 in INTEGER, param2 out INTEGER, param3 in INTEGER ) AS BEGIN param2:= param1 + param3; END; / gaussdb=# DECLARE input1 INTEGER:=1; input2 INTEGER:=2; statement VARCHAR2(200); param2 INTEGER; BEGIN --声明调用语句 statement := 'call proc_add(:col_1, :col_2, :col_3)'; --执行语句 EXECUTE IMMEDIATE statement USING IN input1, OUT param2, IN input2; dbe_output.print_line('result is: '||to_char(param2)); END; / result is: 3 ANONYMOUS BLOCK EXECUTE --删除存储过程。 gaussdb=# DROP PROCEDURE proc_add;

语法 语法请参见图1。 图1 call_procedure::= using_clause子句的语法参见图2。 图2 using_clause::= 对以上语法格式的解释如下： CALL procedure_name，调用存储过程。 [:placeholder1，:placeholder2，…]，存储过程参数占位符列表，占位符名不能使用带引号的数字、字符或字符串。占位符个数与参数个数相同。 USING [IN|OUT|IN OUT] bind_argument，用于指定存放传递给存储过程参数值的变量。bind_argument前的修饰符与对应参数的修饰符一致。 不支持调用带有占位符的重载函数或者存储过程。

示例 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 35 36 37 38 39 --创建表。 gaussdb=# CREATE TABLE sections_t1 ( section NUMBER(4) , section_name VARCHAR2(30), manager_id NUMBER(6), place_id NUMBER(4) ) DISTRIBUTE BY hash(manager_id); --声明变量。 gaussdb=# 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; / --查询数据。 gaussdb=# SELECT * FROM sections_t1; section | sec_name | manager_id | place_id ---------+--------------+------------+---------- 280 | Info support | 103 | 1400 280 | Info support | 103 | 280 (2 rows) --删除表。 gaussdb=# DROP TABLE sections_t1;

语法 语法请参见图1。 图1 noselect::= using_clause子句的语法参见图2。 图2 using_clause::= 对以上语法格式的解释如下： USING IN bind_argument用于指定存放传递给动态SQL值的变量，在dynamic_noselect_string中存在占位符时使用，即动态SQL语句执行时，bind_argument将替换相对应的占位符。要注意的是，bind_argument只能是值、变量或表达式，不能是表名、列名、数据类型等数据库对象。如果存储过程需要通过声明参数传递数据库对象来构造动态SQL语句（常见于执行DDL语句时），建议采用连接运算符“||”拼接dynamic_select_clause。另外，动态语句允许出现重复的占位符，相同占位符只能与唯一一个bind_argument按位置一一对应。当设置guc参数behavior_compat_options值为dynamic_sql_compat时，会按照占位符的顺序依次匹配USING子句bind_argument，重复的占位符不会再识别为同一个占位符（占位符名不能使用带引号的数字、字符或字符串）。

OPEN FOR 动态查询语句还可以使用OPEN FOR打开动态游标来执行。 语法参见图3。 图3 open_for::= 参数说明： cursor_name：要打开的游标名。 dynamic_string：动态查询语句。 USING value：在dynamic_string中存在占位符时使用。 游标的使用请参考游标。 示例 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 35 36 gaussdb=# DROP SCHEMA IF EXISTS hr CASCADE; gaussdb=# CREATE SCHEMA hr; gaussdb=# SET CURRENT_SCHEMA = hr; gaussdb=# CREATE TABLE staffs ( section_id NUMBER, first_name VARCHAR2, phone_number VARCHAR2, salary NUMBER ); gaussdb=# INSERT INTO staffs VALUES (30, 'mike', '13567829252', 5800); gaussdb=# INSERT INTO staffs VALUES (40, 'john', '17896354637', 4000); gaussdb=# DECLARE name VARCHAR2(20); phone_number VARCHAR2(20); salary NUMBER(8,2); sqlstr VARCHAR2(1024); TYPE app_ref_cur_type IS REF CURSOR; --定义游标类型。 my_cur app_ref_cur_type; --定义游标变量。 BEGIN sqlstr := 'select first_name,phone_number,salary from hr.staffs where section_id = :1'; OPEN my_cur FOR sqlstr USING '30'; --打开游标, using是可选的。 FETCH my_cur INTO name, phone_number, salary; --获取数据。 WHILE my_cur%FOUND LOOP dbe_output.print_line(name||'#'||phone_number||'#'||salary); FETCH my_cur INTO name, phone_number, salary; END LOOP; CLOSE my_cur; --关闭游标。 END; / mike#13567829252#5800.00 ANONYMOUS BLOCK EXECUTE

EXECUTE IMMEDIATE 语法图请参见图1。 图1 EXECUTE IMMEDIATE dynamic_select_clause::= using_clause子句的语法图参见图2。 图2 using_clause::= 对以上语法格式的解释如下： define_variable，用于指定存放单行查询结果的变量。 USING IN bind_argument，用于指定存放传递给动态SQL值的变量，即在dynamic_select_string中存在占位符时使用。 USING OUT bind_argument，用于指定存放动态SQL返回值的变量。 查询语句中，into和out不能同时存在； 占位符命名以“:”开始，后面可跟数字、字符或字符串（不能使用带引号的数字、字符或字符串），与USING子句的bind_argument一一对应； bind_argument只能是值、变量或表达式，不能是表名、列名、数据类型等数据库对象，即不支持使用bind_argument为动态SQL语句传递模式对象。如果存储过程需要通过声明参数传递数据库对象来构造动态SQL语句（常见于执行DDL语句时），建议采用连接运算符“||”拼接dynamic_select_clause； 动态PL/SQL块允许出现重复的占位符，即相同占位符只能与USING子句的一个bind_argument按位置对应。当设置guc参数behavior_compat_options值为dynamic_sql_compat时，会按照占位符的顺序依次匹配USING子句bind_argument，重复的占位符不会再识别为同一个占位符。 示例 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 gaussdb=# DROP SCHEMA IF EXISTS hr CASCADE; gaussdb=# CREATE SCHEMA hr; gaussdb=# SET CURRENT_SCHEMA = hr; gaussdb=# CREATE TABLE staffs ( staff_id NUMBER, first_name VARCHAR2, salary NUMBER ); gaussdb=# INSERT INTO staffs VALUES (200, 'mike', 5800); gaussdb=# INSERT INTO staffs VALUES (201, 'lily', 3000); gaussdb=# INSERT INTO staffs VALUES (202, 'john', 4400); --从动态语句检索值（INTO 子句）： gaussdb=# DECLARE staff_count VARCHAR2(20); BEGIN EXECUTE IMMEDIATE 'select count(*) from hr.staffs' INTO staff_count; dbe_output.print_line(staff_count); END; / 3 ANONYMOUS BLOCK EXECUTE --传递并检索值（INTO子句用在USING子句前）： gaussdb=# CREATE OR REPLACE PROCEDURE dynamic_proc AS staff_id NUMBER(6) := 200; first_name VARCHAR2(20); salary NUMBER(8,2); BEGIN EXECUTE IMMEDIATE 'select first_name, salary from hr.staffs where staff_id = :1' INTO first_name, salary USING IN staff_id; dbe_output.print_line(first_name || ' ' || salary); END; / CREATE PROCEDURE --调用存储过程。 gaussdb=# CALL dynamic_proc(); mike 5800.00 dynamic_proc -------------- (1 row) --删除存储过程。 gaussdb=# DROP PROCEDURE dynamic_proc; gaussdb=# DROP TABLE staffs;

示例 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 gaussdb=# DROP SCHEMA IF EXISTS hr CASCADE; gaussdb=# CREATE SCHEMA hr; gaussdb=# SET CURRENT_SCHEMA = hr; gaussdb=# CREATE TABLE staffs ( section_id NUMBER, first_name VARCHAR2, phone_number VARCHAR2, salary NUMBER ); gaussdb=# INSERT INTO staffs VALUES (30, 'mike', '13567829252', 5800); gaussdb=# INSERT INTO staffs VALUES (40, 'john', '17896354637', 4000); --创建存储过程proc_staffs gaussdb=# CREATE OR REPLACE PROCEDURE proc_staffs ( section NUMBER(6), salary_sum out NUMBER(8,2), staffs_count out INTEGER ) IS BEGIN SELECT sum(salary), count(*) INTO salary_sum, staffs_count FROM hr.staffs where section_id = section; END; / CREATE PROCEDURE --创建存储过程proc_return. gaussdb=# CREATE OR REPLACE PROCEDURE proc_return AS v_num NUMBER(8,2); v_sum INTEGER; BEGIN proc_staffs(30, v_sum, v_num); --调用语句 dbe_output.print_line(v_sum||'#'||v_num); RETURN; --返回语句 END; / --调用存储过程proc_return. gaussdb=# CALL proc_return(); 5800#1.00 proc_return ------------- (1 row) --清除存储过程 gaussdb=# DROP PROCEDURE proc_staffs; gaussdb=# DROP PROCEDURE proc_return; --创建函数func_return. gaussdb=# CREATE OR REPLACE FUNCTION func_return returns void language plpgsql AS $$ DECLARE v_num INTEGER := 1; BEGIN dbe_output.print_line(v_num); RETURN; --返回语句 END $$; CREATE FUNCTION -- 调用函数func_return gaussdb=# CALL func_return(); 1 func_return ------------- (1 row) -- 清除函数 gaussdb=# DROP FUNCTION func_return;

变量赋值示例 1 2 3 4 5 6 7 8 9 10 11 12 13 gaussdb=# DECLARE emp_id INTEGER := 7788;--赋值 BEGIN emp_id := 5;--赋值 DBE_OUTPUT.PRINT_LINE(emp_id); emp_id := 5*7784; DBE_OUTPUT.PRINT_LINE(emp_id); END; / --结果如下： 5 38920 ANONYMOUS BLOCK EXECUTE

示例 gaussdb=# DROP TABLE IF EXISTS customers; NOTICE: table "customers" does not exist, skipping DROP TABLE gaussdb=# CREATE TABLE customers(id int,name varchar); CREATE TABLE gaussdb=# INSERT INTO customers VALUES(1,'ab'); gaussdb=# DECLARE my_id integer; BEGIN select id into my_id from customers limit 1; -- 赋值 END; / ANONYMOUS BLOCK EXECUTE gaussdb=# DECLARE type id_list is varray(6) of customers.id%type; id_arr id_list; BEGIN select id bulk collect into id_arr from customers order by id DESC limit 20; -- 批量赋值 END; / ANONYMOUS BLOCK EXECUTE BULK COLLECT INTO 只支持批量赋值给数组或集合。数组类型合理使用LIMIT字段避免操作过量数据导致性能下降。 对于数组变量，小括号"()"将优先识别为下标，因此对于带括号的表达式，不支持写在数组变量后面。如对于select (1+3) into va(5)，不支持写为select into va(5) (1+3)或select into va[5] (1+3)。 BULK COLLECT INTO 只支持在ORA兼容性数据库下使用。

基本语句 在编写PL/SQL过程中，会定义一些变量，给变量赋值，调用其他存储过程等。介绍PL/SQL中的基本语句，包括定义变量、赋值语句、调用语句以及返回语句。 尽量不要在存储过程中调用包含密码的SQL语句，因为存储在数据库中的存储过程文本可能被其他有权限的用户看到导致密码信息被泄漏。如果存储过程中包含其他敏感信息也需要配置存储过程的访问权限，保证敏感信息不会泄漏。 定义变量 赋值语句 调用语句 父主题： 存储过程

示例 下面列举了基本的匿名块程序： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 --空语句块 gaussdb=# BEGIN NULL; END; / --将信息打印到控制台： gaussdb=# BEGIN dbe_output.print_line('hello world!'); END; / hello world! ANONYMOUS BLOCK EXECUTE --将变量内容打印到控制台： gaussdb=# DECLARE my_var VARCHAR2(30); BEGIN my_var :='world'; dbe_output.print_line('hello'||my_var); END; / helloworld ANONYMOUS BLOCK EXECUTE

结构 PL/SQL块中可以包含子块，子块可以位于PL/SQL中任何部分。PL/SQL块的结构如下： 声明部分：声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数。 DECLARE 不涉及变量声明时声明部分可以没有。 对匿名块来说，没有变量声明部分时，可以省去DECLARE关键字。 对存储过程来说，没有DECLARE， AS相当于DECLARE。即便没有变量声明的部分，关键字AS也必须保留。 执行部分：过程及SQL语句，程序的主要部分。必选。 BEGIN 执行异常部分：错误处理。可选。 EXCEPTION 结束。必选。 END; / 禁止在PL/SQL块中使用连续的Tab，连续的Tab可能会造成在使用gsql工具带“-r”参数执行PL/SQL块时出现异常。

数据类型转换 数据库中有些数据类型间允许进行隐式类型转换（例如赋值、函数调用的参数等）、有些数据类型间不允许进行隐式数据类型转换（例如int），可尝试使用 GaussDB 提供的类型转换函数，例如CAST进行数据类型强转。 GaussDB数据库 常见的隐式类型转换，请参见表1。 GaussDB支持的DATE的效限范围是：公元前4713年到公元294276年。 表1 隐式类型转换表 原始数据类型 目标数据类型 备注 CHAR VARCHAR2 - CHAR NUMBER 原数据必须由数字组成。 CHAR DATE 原数据不能超出合法日期范围。 CHAR RAW - CHAR CLOB - VARCHAR2 CHAR - VARCHAR2 NUMBER 原数据必须由数字组成。 VARCHAR2 DATE 原数据不能超出合法日期范围。 VARCHAR2 CLOB - NUMBER CHAR - NUMBER VARCHAR2 - DATE CHAR - DATE VARCHAR2 - RAW CHAR - RAW VARCHAR2 - CLOB CHAR - CLOB VARCHAR2 - CLOB NUMBER 原数据必须由数字组成。 INT4 CHAR - 父主题： 存储过程

场景二：常规数据倾斜巡检 在库中表个数少于1W的场景，直接使用倾斜视图查询当前库内所有表的数据倾斜情况。 1 SELECT * FROM pgxc_get_table_skewness ORDER BY totalsize DESC; 在库中表个数非常多（至少大于1W）的场景，因PGXC_GET_TABLE_SKEWNESS涉及全库查并计算非常全面的倾斜字段，所以可能会花费比较长的时间（小时级），建议参考PGXC_GET_TABLE_SKEWNESS视图定义，直接使用table_distribution()函数自定义输出，减少输出列进行计算优化，例如： 1 2 3 4 5 6 SELECT schemaname,tablename,max(dnsize) AS maxsize, min(dnsize) AS minsize FROM pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace INNER JOIN pg_catalog.table_distribution() s ON s.schemaname = n.nspname AND s.tablename = c.relname INNER JOIN pg_catalog.pgxc_class x ON c.oid = x.pcrelid AND x.pclocatortype = 'H' GROUP BY schemaname,tablename;

场景一：磁盘满后快速定位存储倾斜的表 首先，通过pg_stat_get_last_data_changed_time(oid)函数查询出近期发生过数据变更的表，鉴于表的最后修改时间只在进行IUD操作的CN记录，要查询库内1天(间隔可在函数中调整)内被修改的所有表，可以使用如下封装函数： 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE OR REPLACE FUNCTION get_last_changed_table(OUT schemaname text, OUT relname text) RETURNS setof record AS $$ DECLARE row_data record; row_name record; query_str text; query_str_nodes text; BEGIN query_str_nodes := 'SELECT node_name FROM pgxc_node where node_type = ''C'''; FOR row_name IN EXECUTE(query_str_nodes) LOOP query_str := 'EXECUTE DIRECT ON (' || row_name.node_name || ') ''SELECT b.nspname,a.relname FROM pg_class a INNER JOIN pg_namespace b on a.relnamespace = b.oid where pg_stat_get_last_data_changed_time(a.oid) BETWEEN current_timestamp - 1 AND current_timestamp;'''; FOR row_data IN EXECUTE(query_str) LOOP schemaname = row_data.nspname; relname = row_data.relname; return next; END LOOP; END LOOP; return; END; $$ LANGUAGE 'plpgsql'; 然后，通过table_distribution(schemaname text, tablename text)查询出表在各个DN占用的存储空间。 1 SELECT table_distribution(schemaname,relname) FROM get_last_changed_table();

查看表所在节点 用户在建表时可以指定表如何在节点之间分布或者复制，详情请参考•DISTRIBUTEBY，分布方式介绍可参阅选择分布方式。 用户在建表时也可设置“Node Group”来指定表所在的Group，详情请参考•TO{GROUPgroupname|...。 用户还可以通过以下命令查看表所在实例。 查询表所在的schema。 select t1.nspname,t2.relname from pg_namespace t1,pg_class t2 where t1.oid = t2.relnamespace and t2.relname = 'table1'; 上述命令中，“nspname”为schema的名称，“relname”为表、索引、视图等对象的名称，“oid”为行标识符，“relnamespace”为包含这个关系的名称空间的OID，“table1”为表名称。 查看表的relname和nodeoids。 select t1.relname,t2.nodeoids from pg_class t1, pgxc_class t2, pg_namespace t3 where t1.relfilenode = t2.pcrelid and t1.relnamespace=t3.oid and t1.relname = 'table1' and t3.nspname ='schema1'; 上述命令中，“nodeoids”为表分布的节点OID列表，“relfilenode”为这个关系在磁盘上的文件的名称，“pcrelid”为表的OID，“schema1”为1中查询出的该表所在schema。 根据查询到的表分布的节点，查询表所在实例。 select * from pgxc_node where oid in (nodeoids1, nodeoids2, nodeoids3); 上述命令中的“nodeoids1, nodeoids2, nodeoids3”为2中查询到的3个nodeoids，操作时以实际查询到的为准，各nodeoids间以“,”隔开。 父主题： 表设计最佳实践

选择数据类型 高效数据类型，主要包括以下三方面： 尽量使用执行效率比较高的数据类型 一般来说整型数据运算(包括=、＞、＜、≧、≦、≠等常规的比较运算，以及group by)的效率比字符串、浮点数要高。 尽量使用短字段的数据类型 长度较短的数据类型不仅可以减小数据文件的大小，提升I/O性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint。 使用一致的数据类型 表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销。 父主题： 表设计最佳实践

使用分区表 分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点： 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。 GaussDB支持的分区表为范围分区表，列表分区表，哈希分区表。 范围分区表：将数据基于范围映射到每一个分区。这个范围是由创建分区表时指定的分区键决定的。分区键经常采用日期，例如将销售数据按照月份进行分区。 列表分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。 哈希分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。 父主题： 表设计最佳实践

选择分布方式 复制表（Replication）方式将表中的全量数据在集群的每一个DN实例上保留一份。主要适用于记录集较小的表。这种存储方式的优点是每个DN上都有该表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment(每个plan segment都会起对应的线程)；缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。 哈希（Hash）表将表中某一个或几个字段进行hash运算后，生成对应的hash值，根据DN实例与哈希值的映射关系获得该元组的目标存储位置。对于Hash分布表，在读/写数据时可以利用各个节点的I/O资源，大大提升表的读/写速度。一般情况下大表定义为Hash表。 范围（Range）和列表（List）分布是由用户自定义的分布策略，根据分布列的取值落入满足一定范围或者具体值的对应目标DN，这两种分布方式便于用户灵活地进行数据管理，但对用户本身的数据抽象能力有一定的要求。 策略 描述 适用场景 Hash 表数据通过hash方式散列到集群中的所有DN实例上。 数据量较大的事实表。 Replication 集群中每一个DN实例上都有一份全量表数据。 小表、维度表。 Range 表数据对指定列按照范围进行映射，分布到对应DN。 用户需要自定义分布规则的场景。 List 表数据对指定列按照具体值进行映射，分布到对应DN。 用户需要自定义分布规则的场景。 如图1所示，复制表如图中的表T1，哈希表如图中的表T2。 图1 复制表和哈希表 在对复制表进行数据插入、修改、删除等操作时，如果用户使用声明为可下推（shippable或者immutable）的函数对不可下推的成分进行封装，则可能会导致复制表不同DN数据不一致。 使用带有窗口函数、rownum、limit子句、用户自定义函数等结果不稳定的语句对复制表进行数据插入或修改，可能会导致不同节点数据不完全相同。 父主题： 表设计最佳实践

美元引用的字符串常量 如果在字符串序列中包含有'(单引号)，那么应当将'(单引号)加倍为''(两个单引号)否则sql语句很可能无法执行。 如果字符串中包含很多单引号或者反斜杠，那么理解字符串的内容可能就会变得很苦涩，并且容易出错，因为单引号都要加倍。 为了让这种场合下的查询更具可读性，我们允许另外一种称作"美元符界定"的字符串常量书写办法。一个通过美元符界定声明的字符串常量由一个美元符号($)、零个或多个字符组成的"记号"、另一个美元符号、组成字符串常量的任意字符序列、一个美元符号、与前面相同的记号、一个美元符号组成的。 gaussdb=# SELECT $$it's an example$$; ?column? ----------------- it's an example (1 row) 父主题： 附录

扩展函数 下表列举了GaussDB中支持的扩展函数，不作为商用特性交付，仅供参考。 分类 函数名称 描述 触发器函数 pg_get_triggerdef(trigger_oid) 为触发器获取CREATE [ CONSTRAINT ] TRIGGER命令 pg_get_triggerdef(trigger_oid, pretty_bool) 为触发器获取CREATE [ CONSTRAINT ] TRIGGER命令 父主题： 附录

参数说明 expression 用于计算或插入结果表指定地点的常量或者表达式。 在一个出现在INSERT顶层的VALUES列表中，expression可以被DEFAULT替换以表示插入目的字段的缺省值。除此以外，当VALUES出现在其他场合的时候是不能使用DEFAULT的。 sort_expression 一个表示如何排序结果行的表达式或者整数常量。 ASC 指定按照升序排列。 DESC 指定按照降序排列。 operator 一个排序操作符。 count 返回的最大行数。 OFFSET start [ ROW | ROWS ] 声明返回的最大行数，而start声明开始返回行之前忽略的行数。 FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY FETCH子句限定返回查询结果从第一行开始的总行数，count的缺省值为1。

语法格式 1 2 3 4 5 VALUES {( expression [, ...] )} [, ...] [ ORDER BY { sort_expression [ ASC | DESC | USING operator ] } [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ];

语法格式 回收空间并更新统计信息，对关键字顺序无要求。 1 2 VACUUM [ ( { FULL | FREEZE | VERBOSE | {ANALYZE | ANALYSE }} [,...] ) ] [ table_name [ (column_name [, ...] ) ] [ PARTITION ( partition_name ) ] ]; 仅回收空间，不更新统计信息。 1 VACUUM [ FULL [COMPACT] ] [ FREEZE ] [ VERBOSE ] [ table_name [ PARTITION ( partition_name ) ] ]; 回收空间并更新统计信息，且对关键字顺序有要求。 1 2 VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] { ANALYZE | ANALYSE } [ VERBOSE ] [ table_name [ (column_name [, ...] ) ] [ PARTITION ( partition_name ) ] ];