Greenplum开发_greenplum for循环

GP开发规范

对象数管控

• 严格控制数据库对象的数量（包括表，视图，索引，函数等），控制在2-3万以内性能最佳，最好不要超过5万，超过10万以后GP性能下降明显;(以下规范全部基于OLAP)

1. 不允许建索引（包括普通索引、唯一索引、主键等）；
2. 临时表对象用完即时删除；
3. 尽量不要建分区表，分区表会导致对象数急剧上升，建分区表考虑以几个问题：表是否足够大（上亿）？目前性能瓶颈？查询条件是否满足分区条件？是否需要滚动历史数据？数据能否被均匀分拆；
4. 默认使用行存，列存会急剧增加对象数；

连接数管控

• GP建议连接数控制在200个左右，连接数超过250个以后，GP性能明显下降，根据人数限制每个开发连接数个数，目前建议开发用户4个连接数；每隔30分钟杀掉开发用户超过30分钟不活跃的连接；

1. 开发人员GP数据库连接数不超过4个；

建表管控

• 分布策略尽量使用分区分布，小的维度表可以使用复制表，大表禁止使用复制表；分区分布的分布键会影响后续查询效率，分布键没设置好，会导致数据倾斜或者查询时数据重分布；
• GP中不加””时，不区分大小写，GP在数据字典中默认存为小写，如果需要区分大小写或者表名中存在特殊字符，请加上””;
• GP中默认建Heap表（堆表），不能压缩，只能行存，适合数据量不大的表（维表等）；AO（Append Optimized 追加优化表），可以压缩，对批量插入做了优化，适合大表（事实表等）；
• GP中标识符长度不超过63，ORACLE中标识符长度不超过30；
• GP中char和varchar没有性能差异；

1. 建表时必须手动指定分布键，大表（事实表等）最好使用经常关联的字段作为分布键，小表（维表等）最好使用主键作为分布键；
2. 不允许使用默认分布键，尽可能不要使用随机分布策略；
3. 分布键原则上为1个字段，尽量不要超过3个字段；
4. 表名尽量使用数字、字母、下划线，不建议使用特殊字符；
5. 千万行级别以下的表建Heap表，千万行以上级别的表建oa表，固定zstd压缩，压缩等级3，
   WITH (APPENDONLY=true, COMPRESSTYPE=zstd, COMPRESSLEVEL=3)；
6. GP中标识符长度不超过63，ORACLE中标识符长度不超过30；
7. varchar类型替代char类型；

查询管控

• GP中通过配置的模式搜索路径对对象进行搜索，默认搜索 “$user”,public；
• GP中的除法运算需特别小心，select 1/2 的结果是0；

1. 引用其它schema下的对象时，需要追加schemaname，例如：tool.tmp;
2. 涉及到复合运算并且包含除法运算时，请在分子上添加1.0，例如：select 1/2 请改写成 select 1*1.0/2；*
3. 避免使用标量子查询，效率很低，并且容易造成表的死锁，需改成关联查询，例如：select a.id,(select b.name from tmp1 b where a.id = b.id) as name from tmp a 改成 select a.id,b.name from tmp a, tmp1 b where a.id = b.id ；
4. 一个sql中多表关联不要超过10张表；
5. sql语句全部小写；

简单的PSQL命令

psql常用参数

psql常用内部命令

语法

DDL

创建非分区表

--Oracle
CREATE TABLE test(
  id NUMBER,
  name VARCHAR2(40)
);
--GP
create table test(
  id int,
  name varchar(40)
) distributed by (id);
1
2
3
4
5
6
7
8
9
10

创建分区表

--Oracle
CREATE TABLE test(
  id NUMBER,
  name VARCHAR2(40),
  birth_day DATE
) PARTITION BY RANGE(birth_day)(
  PARTITION p20090101 VALUES LESS THAN (TO_DATE('20090102','yyyymmdd')),
  PARTITION p20090102 VALUES LESS THAN (TO_DATE('20090103','yyyymmdd')),
  PARTITION p20090103 VALUES LESS THAN (TO_DATE('20090104','yyyymmdd'))
);
--GP
create table test(id int,
  name varchar(40),
  birth_day date
) distributed by (id)
partition by range(birth_day)(
  partition p20090101 start(date '20010101') inclusive,
  partition p20090102 start(date '20090102') inclusive,
  partition p20090103 start(date '20090103') inclusive
  end (date '20090104') exclusive
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

分区表增加分区

--Oracle
ALTER TABLE test 
ADD PARTITION p20090104
  VALUES LESS THAN (TO_DATE('20090105','yyyymmdd'));
--GP
alter table test
add partition p20090104 
  start (date '2009-01-04') inclusive
  end (date '2009-01-05') exclusive;
1
2
3
4
5
6
7
8
9

分区表删除分区

--Oracle
ALTER TABLE test DROP PARTITION p20090104;
--GP
alter table test drop partition p20090104;
1
2
3
4

分区表截断分区

--Oracle
ALTER TABLE test TRUNCATE PARTITION p20090101;
--GP
alter table test truncate partition p20090101;
1
2
3
4

分区表分裂分区

--Oracle
ALTER TABLE test 
SPLIT PARTITION p200905_mon 
  AT (TO_DATE('20090515','yyyymmdd'))
  INTO (
    PARTITION p200905_01,
    PARTITION p200905_02
  );
--GP
alter table test 
split partition p200905_mon 
  at ('20090515') 
  into(
    partition p200905_01,
    partition p200905_02
  );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

删除表

--Oracle
DROP TABLE test;
--GP
drop table if exists test;
1
2
3
4

重命名表

--Oracle
rename a to b;
--GP
alter table a rename to b;
1
2
3
4

截断表

--Oracle
TRUNCATE TABLE test;
--GP
truncate table test;
1
2
3
4

创建索引

--Oracle
CREATE INDEX idx_test ON test(id);
--GP
create index idx_test on test(id);
1
2
3
4

创建主键

--Oracle
ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);
--GP
alter table test add constraint pk_test primary key (id);
1
2
3
4

删除主键

--Oracle
ALTER TABLE test DROP CONSTRAINT pk_test;
--GP
alter table test drop constraint if exists pk_test;
1
2
3
4

删除索引

--Oracle
DROP INDEX IND_TEST;
--GP
drop index if exists ind_test;
1
2
3
4

添加字段

--Oracle
ALTER TABLE test add (
  column1 number,
  column2 varchar2(100)
);
--GP
alter table test
  add column1 numeric,
  add column2 varchar(100);
1
2
3
4
5
6
7
8
9

修改字段

--Oracle
ALTER TABLE test modify (column1 VARCHAR2(100));
--GP
alter table test alter column column1 type varchar(100); 
alter table test alter column1 type varchar(100);
1
2
3
4
5

删除字段

--Oracle
ALTER TABLE test DROP COLUMN column1;
--GP
alter table test drop column if exists column1; 
alter table test drop if exists column1;
1
2
3
4
5

字段重命名

--Oracle
ALTER TABLE test RENAME COLUMN column2 TO column1;
--GP
alter table test rename column column2 to column1;
alter table test rename column2 to column1;
1
2
3
4
5

创建视图

--Oracle
CREATE VIEW v_test AS SELECT * FROM test;
--GP
create view v_test as select * from test;
1
2
3
4

DML

插入记录

--Oracle
INSERT INTO test VALUES (001,'SUNWG',TO_DATE('2009-01-01','YYYY-MM-DD'));
--GP
insert into test values (001,'SUNWG','2009-01-01');
1
2
3
4

删除记录

--Oracle
DELETE FROM test WHERE id = 001;
--GP
delete from test where id = 001;
1
2
3
4

关联更新记录

--Oracle
UPDATE test a SET a.column1 = (
  SELECT b.column1 
  FROM test1 b 
  WHERE a.id = b.id
);
--GP
update test a set 
  column1 = b.column1 
from test1 b 
where a.id = b.id;
1
2
3
4
5
6
7
8
9
10
11

查询表中前10条记录

--Oracle
SELECT * FROM test WHERE ROWNUM <= 10;
--GP
select * from test limit 10;
1
2
3
4

两表内关联

--Oracle
SELECT * FROM test1 a, test2 b WHERE a.id = b.id;
--GP
select * from test1 a inner join test2 b on (a.id = b.id);
1
2
3
4

两表左外关联

--Oracle
SELECT * FROM test1 a, test2 b WHERE a.id = b.id(+);
--GP
select * from test1 a left join test2 b on (a.id = b.id);
1
2
3
4

事务提交

--Oracle
INSERT INTO test VALUES (001,'SUNWG',TO_DATE('2009-01-01','YYYY-MM-DD')); 
COMMIT;
--GP
begin;
  insert into test values (001,'SUNWG','2009-01-01');
commit;
1
2
3
4
5
6
7

事务回滚

--Oracle
INSERT INTO test VALUES (001,'SUNWG',TO_DATE('2009-01-01','YYYY-MM-DD'));
ROLLBACK;
--GP
begin;
  insert into test values (001,'SUNWG','2009-01-01');
rollback;
1
2
3
4
5
6
7

数据类型转换

--Oracle
SELECT to_number('101') FROM dual;
--GP
select cast('101' as numeric); 
select '101' :: numeric;
1
2
3
4
5

除法运算

--Oracle
SELECT 1/2 FROM dual;
--GP
select 1.0/2
1
2
3
4

并集

--Oracle
SELECT 1 FROM dual
UNION
SELECT 2 FROM dual;
SELECT 1 FROM dual
UNION ALL
SELECT 2 FROM dual;
--GP
select 1 
union 
select 2;
select 1
union all
select 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

交集

--Oracle
SELECT 1 FROM dual 
INTERSECT 
SELECT 1 FROM dual;
--GP
select 1 
intersect
select 1;
select 1
intersect all
select 1;
1
2
3
4
5
6
7
8
9
10
11

差集

--Oracle
SELECT 1 FROM dual
MINUS 
SELECT 2 FROM dual;
--GP
select 1 
except
select 2;
select 1
except all
select 2;
1
2
3
4
5
6
7
8
9
10
11

子查询

--Oracle
SELECT * FROM (SELECT * FROM test);
--GP
select * from (select * from test) as a;
1
2
3
4

日期抽取

--Oracle
SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM dual;
--GP
select to_char(current_timestamp,'yyyy-mm-dd');
select extract(century from now()); --世纪
select extract(year from now()); --年
select extract(dow from now()); --星期几
select extract(dow from now()); --星期几
1
2
3
4
5
6
7
8

时间截取

--Oracle
SELECT TRUNC(SYSDATE,'hh') FROM dual;
--GP
select date_trunc('hour', now());
1
2
3
4

delete 剔重

--Oracle
DELETE FROM test a WHERE rowid < (
  SELECT MAX(rowid) 
  FROM test b
  WHERE a.id = b.id
);
--GP
delete from test a where ctid < (
  select max(ctid) 
  from test b
  where a.id = b.id
    and a.gp_segment_id = b.gp_segment_id
);
1
2
3
4
5
6
7
8
9
10
11
12
13

当前的系统时间

--Oracle
SELECT SYSDATE,CURRENT_TIMESTAMP FROM dual;
--GP
select current_timestamp,now(),clock_timestamp();
1
2
3
4

当前时间向前推一天

--Oracle
SELECT SYSDATE-1 FROM dual;
--GP
select current_timestamp - interval '1 day';
1
2
3
4

当前时间向前推一个月

--Oracle
SELECT add_months(SYSDATE,-1) FROM dual;
--GP
select current_timestamp - interval '1 month';
1
2
3
4

当前时间向前推一年

--Oracle
SELECT add_months(SYSDATE,-12) FROM dual;
--GP
select current_timestamp - interval '1 year';
1
2
3
4

当前时间向前推一小时

--Oracle
SELECT SYSDATE-1/24 FROM dual;
--GP
select current_timestamp - interval '1 hour';
1
2
3
4

当前时间向前推一分钟

--Oracle
SELECT SYSDATE-1/24/60 FROM dual;
--GP
select current_timestamp - interval '1 min';
1
2
3
4

当前时间向前推30秒

--Oracle
SELECT SYSDATE-1/24/60/60*30 FROM dual;
--GP
select current_timestamp - interval '30 second';
1
2
3
4

正则匹配

--Oracle
SELECT 1 FROM dual WHERE regexp_like ('123','[0-9]+');
--GP
select 1 where '123' ~ '[0-9]+';
1
2
3
4

正则截取

--Oracle
SELECT regexp_substr('你好123','[0-9]+') FROM dual;
--GP
select substring ('你好123' from '[0-9]+');
1
2
3
4

分区表查询指定分区

--Oracle
SELECT * FROM test PARTITION (p20090101);
--GP
select * from test_1_prt_p20090101;
1
2
3
4

字符串截取涉及非正数位置开始截取

--Oracle
SELECT SUBSTR('12345',-1,3) FROM dual;
--GP
select substr('12345',length('12345')-1+1,3);
select substr('12345',-1,3);
--等价于
select substr('12345',1,1);
1
2
3
4
5
6
7

指定子字符串第一次出现的位置

--Oracle
SELECT INSTR('123245','24',1,1) FROM dual
--GP
select strpos('123245','24');
select position ('24' in '123245');
1
2
3
4
5

指定月份间相差的月份

--Oracle
SELECT months_between(to_date('202108','yyyymm'),to_date('202107','yyyymm')) FROM dual;
--GP
select extract(month from age(to_date('202108','yyyymm'),to_date('202107','yyyymm')));
1
2
3
4

判空

--Oracle
SELECT NVL(NULL,1) FROM dual;
SELECT NVL2(NULL,1,2) FROM dual;
--GP
select coalesce (null::integer,1);
1
2
3
4
5

序号

--Oracle
SELECT ROWNUM,NAME FROM (
  SELECT 'ha' NAME FROM dual
  UNION ALL
  SELECT 'haha' FROM dual
);
--GP
select row_number() over(order by 1) xh,name from (
  select 'ha' name
  union all
  select 'haha'
) a;
1
2
3
4
5
6
7
8
9
10
11
12

to_char数字转换为字符

--Oracle
select to_char(123,'fm999');
--GP
select to_char(123,'fm999');
1
2
3
4

字符转换日期

--Oracle
SELECT to_date('20210827','yyyymmdd') FROM dual;
--GP
select to_date('20210827','yyyymmdd');
select to_timestamp('20210827','yyyymmdd');
select cast ('20210827' as date);
select cast ('20210827' as timestamp);
1
2
3
4
5
6
7

字符转换为数字

--Oracle
SELECT to_number('20210827') FROM dual;
--GP
select cast('20210827' as numeric);
1
2
3
4

字符串分割

--Oracle
SELECT 
  kind,
  listagg(subkind,';')WITHIN GROUP(ORDER BY subkind) newkind 
FROM (
  SELECT '水果' kind,'苹果' subkind FROM dual
  UNION ALL
  SELECT '水果','梨' FROM dual
  UNION ALL
  SELECT '水果','香蕉' FROM dual
  UNION ALL
  SELECT '动物','狗' FROM dual
  UNION ALL
  SELECT '动物','猫' FROM dual
) a
GROUP BY kind;
--GP
select 
  kind,
  string_agg(subkind,';' order by subkind) newkind 
from (
  select '水果' kind,'苹果' subkind
  union all
  select '水果','梨'
  union all
  select '水果','香蕉'
  union all
  select '动物','狗'
  union all
  select '动物','猫'
) a
group by kind;
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

递归查询

--Oracle
start with…connect by
--GP
with recursive
1
2
3
4

合并

--Oracle
DROP TABLE example;
CREATE TABLE example (
  example_id NUMBER,
  example_name VARCHAR2(200)
); 
INSERT INTO example VALUES (1,'样例1');
INSERT INTO example VALUES (1,'样例1');
COMMIT;
MERGE INTO example a
USING (
  SELECT 1 example_id, 'MERGE后的样例1' example_name FROM dual
  UNION 
  SELECT 2 example_id, 'MERGE后的样例2' example_name FROM dual
  UNION 
  SELECT 3 example_id, 'MERGE后的样例3' example_name FROM dual
) b
ON (a.example_id = b.example_id)
WHEN MATCHED THEN
  UPDATE SET
    a.example_name = b.example_name
  --限制当 example_id = 1 的时候才做更新
  WHERE 
    a.example_id = 1
WHEN NOT MATCHED THEN
  INSERT (a.example_id,a.example_name)
  VALUES (b.example_id,b.example_name);
COMMIT;
--GP
drop table example;
create table example (
  example_id numeric,
  example_name varchar(200)
);
insert into example values (1,'样例1');
insert into example values (2,'样例2');
begin;
  with tmp_a as (
    select 1 example_id, 'MERGE后的样例1' example_name
    union  
    select 2 example_id, 'MERGE后的样例2' example_name
    union 
    select 3 example_id, 'MERGE后的样例3' example_name
  )
  update example a 
    set example_name = b.example_name 
  from tmp_a b 
  where a.example_id = b.example_id
    and a.example_id = 1;
  with tmp_a as (
    select 1 example_id, 'MERGE后的样例1' example_name
    union 
    select 2 example_id, 'MERGE后的样例2' example_name
    union 
    select 3 example_id, 'MERGE后的样例3' example_name
  )
  insert into example
  select a.example_id,a.example_name 
  from tmp_a a
  where not exists (
    select 1 from example b 
    where a.example_id = b.example_id
  );
commit;
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

DCL

schema授权与回收

--Oracle
--无
--GP
grant usage,create on schema tool to tool; 
revoke usage,create on schema tool from tool;
1
2
3
4
5

表授权与回收查询、插入、更新

--Oracle
GRANT SELECT,INSERT,UPDATE ON test TO tool;
REVOKE SELECT,INSERT,UPDATE ON test FROM tool;
--GP
grant select,insert,update on table test to tool;
revoke select,insert,update on table test from tool;
1
2
3
4
5
6

表授权与回收所有用户访问

---Oracle
GRANT SELECT ON test TO public;
REVOKE SELECT ON test FROM public;
--GP
grant select on table test to public; 
revoke select on table test from public;
1
2
3
4
5
6

存储过程授权与回收

--Oracle
GRANT EXECUTE,DEBUG ON delete_example TO tool;
REVOKE EXECUTE,DEBUG ON delete_example FROM tool;
--GP
grant execute on function delete_example() to tool;
revoke execute on function delete_example() from tool;
1
2
3
4
5
6

PL/SQL和PL/PGSQL

匿名程序块

定义

--Oracle
DECLARE --可选
  --variables,cursors,user-defined exceptions
BEGIN --强制的（必须的）
  --一个或多个SQL语句
  --一个或多个PL/SQL语句
  EXCEPTION --可选
    --当错误发生是要进行的处理
END; --强制的（必须的）
/
--GP
do $$
declare            --可选
--variables,cursors,user-defined exceptions
begin              --强制的（必须的）
  --一个或多个SQL语句
  --一个或多个PL/PGSQL语句
  exception      --可选
    --当错误发生是要进行的处理
end$$;             --强制的（必须的） 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

举例

--Oracle
DECLARE
  var VARCHAR2(100) := 'hello world!';
BEGIN
  dbms_output.put_line(var);
END; 
/
--GP
do $$
declare
  var varchar(100) := 'hello world!';
begin              
  raise notice '%',var;
end$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

替代变量

定义

--Oracle(sqlplus)
define
--GP(psql)
\set
1
2
3
4

举例

--Oracle(sqlplus)
define a = 1
SELECT &a FROM dual;
SELECT &&a FROM dual;
--GP(psql)
\set a 1
select :a;
1
2
3
4
5
6
7

字符串分隔符

需要转义

--Oracle
SELECT 'What''s your name?' FROM dual;
--GP
select 'What''s your name?';
1
2
3
4

无需转义

--Oracle
–通过q'定义||为定界符
SELECT q'|What's your name?|' FROM dual;
–通过q'定义//为定界符
SELECT q'/What's your name?/' FROM dual;
–通过q'定义{}为定界符
SELECT q'{What's your name?}' FROM dual;
–通过q'定义[]为定界符
SELECT q'[What's your name?]' FROM dual;
--GP
select $$What's your name?$$;
select $sql$What's your name?$sql$;
1
2
3
4
5
6
7
8
9
10
11
12

%TYPE属性

1. 使用 %type 属性按照之前已经声明过的变量或者数据库中表的列来声明一个变量；
2. 当存储在一个变量中的值来自于数据库中的表时，最好使用 %type 声明这个变量；
3. 当存储在一个变量中的值来自于数据库中的表时，最好使用 %type 声明这个变量；

--Oracle
–-建表
CREATE TABLE example (
  example_id NUMBER,
  example_name VARCHAR2(200)
);
--插入数据
INSERT INTO example VALUES (1,'样例1');
INSERT INTO example VALUES (2,'样例2');
COMMIT;

--测试%TYPE
DECLARE
  --用%TYPE属性声明p_example_id 是与example表中example_id相同的数据类型
  p_example_id example.example_id%TYPE;
   --用%TYPE属性声明p_example_name 是与example表中example_name相同的数据类型
  p_example_name example.example_name%TYPE;
BEGIN
  --将从example表中读取的一行数据的两列分别写进p_example_id和p_example_name变量
  SELECT * 
  INTO p_example_id,p_example_name
  FROM example
  WHERE ROWNUM = 1;
  --在oracle输出区输出p_example_id的值
  dbms_output.put_line(p_example_id);
  --在oracle输出区输出p_example_name的值
  dbms_output.put_line(p_example_name);
  END;
/
--GP
--建表
create table example (
  example_id numeric,
  example_name VARCHAR(200)
) distributed by (example_id);
--插入数据
insert into example values (1,'样例1');
insert into example values (2,'样例2');
--测试%TYPE
do $$
declare
  --用%TYPE属性声明p_example_id 是与example表中example_id相同的数据类型
  p_example_id example.example_id%type;
  --用%TYPE属性声明p_example_name 是与example表中example_name相同的数据类型
  p_example_name example.example_name%type;
begin
  --将从example表中读取的一行数据的两列分别写进p_example_id和p_example_name变量
  select * 
  into p_example_id,p_example_name
  from example
  limit 1;
  --在gp输出区输出p_example_id的值
  raise notice '%',p_example_id;
  --在gp输出区输出p_example_name的值
  raise notice '%',p_example_name;
end$$;
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

%ROWTYPE属性

利用%ROWTYPE属性声明一个能够存储一个表或视图中一整行数据的记录（变量）；

--Oracle
DECLARE
  --声明记录类型的变量
  exp_record example%ROWTYPE;
BEGIN
  --将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中
  SELECT 
    example_id,
    example_name
  INTO exp_record
  FROM example
  WHERE ROWNUM = 1;
  --输出
  dbms_output.put_line(exp_record.example_id);
  dbms_output.put_line(exp_record.example_name);
END;
/
--GP
do $$
declare
  --声明记录类型的变量
  exp_record example%rowtype;
begin
  --将从example表中获取到的一行数据放进已经声明的记录类型的变量exp_record中
  select 
    example_id,
    example_name
  into exp_record
  from example
  limit 1;
  --输出
  raise notice '%',exp_record.example_id;
  raise notice '%',exp_record.example_name;
end$$;
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

IF语句

--Oracle
DECLARE
  score NUMBER;
BEGIN
  score := 100;
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good！');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good！');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible！');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much！');
  END IF;
  score := 80;
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good！');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good！');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible！');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much！');
  END IF;
  score := 65;
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good！');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good！');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible！');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much！');
  END IF;
  score := 50;
  IF score >= 90 AND score <= 100 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are very good！');
  ELSIF score >= 70 AND score < 90 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are good！');
  ELSIF score >= 60 AND score < 70 THEN
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible！');
  ELSE 
    dbms_output.put_line('Your score is ' || to_char(score) || ', you are terrible very much！');
  END IF;
END;
/
--GP
do $$
declare
  score numeric;
begin
  score := 100;
  if score >= 90 and score <= 100 then
    raise notice 'Your score is %, you are very good！',score;
  elsif score >= 70 and score < 90 then
    raise notice 'Your score is %, you are good！',score;
  elsif score >= 60 and score < 70 then
    raise notice 'Your score is %, you are terrible！',score;
  else 
    raise notice 'Your score is %, you are terrible very much！',score;
  end if;
  score := 80;
  if score >= 90 and score <= 100 then
    raise notice 'Your score is %, you are very good！',score;
  elsif score >= 70 and score < 90 then
    raise notice 'Your score is %, you are good！',score;
  elsif score >= 60 and score < 70 then
    raise notice 'Your score is %, you are terrible！',score;
  else 
    raise notice 'Your score is %, you are terrible very much！',score;
  end if;
  score := 65;
  if score >= 90 and score <= 100 then
    raise notice 'Your score is %, you are very good！',score;
  elsif score >= 70 and score < 90 then
    raise notice 'Your score is %, you are good！',score;
  elsif score >= 60 and score < 70 then
    raise notice 'Your score is %, you are terrible！',score;
  else 
    raise notice 'Your score is %, you are terrible very much！',score;
  end if;
  score := 50;
  if score >= 90 AND score <= 100 then
    raise notice 'Your score is %, you are very good！',score;
  elsif score >= 70 AND score < 90 then
    raise notice 'Your score is %, you are good！',score;
  elsif score >= 60 AND score < 70 then
    raise notice 'Your score is %, you are terrible！',score;
  else 
    raise notice 'Your score is %, you are terrible very much！',score;
  end if;
end$$;
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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

CASE表达式和CASE语句

CASE表达式定义

--Oracle
CASE selector
  WHEN 表达式1 THEN 结果1
  WHEN 表达式2 THEN 结果2
  ...
  WHEN 表达式n THEN 结果n
  [ELSE 结果n+1]
END;    --必须以END结尾
CASE 
  WHEN 搜索条件1 THEN 结果1
  WHEN 搜索条件2 THEN 结果2
  ...
  WHEN 搜索条件n THEN 结果n
  [ELSE 结果n+1]
END;    --必须以END结尾
--GP
case selector
  when 表达式1 then 结果1
  when 表达式2 then 结果2
  ...
  when 表达式n then 结果n
  [else 结果n+1]
end;    --必须以end结尾
case
  when 搜索条件1 then 结果1
  when 搜索条件2 then 结果2
  ...
  when 搜索条件n then 结果n
  [else 结果n+1]
end;    --必须以end结尾
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

CASE表达式举例

--Oracle
SELECT CASE 1 WHEN 2 THEN 1 ELSE 0 END FROM dual;
SELECT CASE WHEN 1 = 2 THEN 1 ELSE 0 END FROM dual;
--GP
select case 1 when 2 then 1 else 0 end;
select case when 1 = 2 then 1 else 0 end;
1
2
3
4
5
6

CASE语句定义

--Oracle
CASE selector
  WHEN 表达式1 THEN 
    statements1;
  WHEN 表达式2 THEN 
    statements2;
  ...
  WHEN 表达式n THEN 
    statementsn;
  [ELSE 
    statementsn+1]
END CASE;    --必须以END CASE;结尾
CASE 
  WHEN 搜索条件1 THEN 
    statements1;
  WHEN 搜索条件2 THEN 
    statements2;
  ...
  WHEN 搜索条件n THEN 
    statementsn;
  [ELSE 
    statementsn+1]
END CASE;    --必须以END CASE;结尾
--GP
case selector
  when 表达式1 then 
    statements1;
  when 表达式2 then 
    statements2;
  ...
  when 表达式n then 
    statementsn;
  [else 
    statementsn+1]
end case;    --必须以end case;结尾
case 
  when 搜索条件1 then 
    statements1;
  when 搜索条件2 then 
    statements2;
  ...
  when 搜索条件n then 
    statementsn;
  [else
    statementsn+1]
end case;    --必须以end case;结尾
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

CASE语句举例

--Oracle
BEGIN
  --第一种
  CASE 1 
    WHEN 2 THEN
      dbms_output.put_line('1 = 2');
    ELSE
      dbms_output.put_line('1 <> 2');
  END CASE;
  --第二种
  CASE  
    WHEN 1 = 2 THEN
      dbms_output.put_line('1 = 2');
    ELSE
      dbms_output.put_line('1 <> 2');
  END CASE; 
END;
/
--GP
do $$
begin
  --第一种
  case 1 
    when 2 then
      raise notice '1 = 2';
    else
      raise notice '1 <> 2';
  end case;
  --第二种
  case  
    when 1 = 2 then
      raise notice '1 = 2';
    else
      raise notice '1 <> 2';
  end case;
end$$;
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

LOOP循环

--Oracle
/*
  循环输出1到10
*/
DECLARE
  --定义一个数，初始值为1
  num NUMBER := 1;
BEGIN
  --循环开始
  LOOP
    --输出num
    dbms_output.put_line(num);
    --num+1
    num := num + 1;
    --循环退出条件，当num第一次出现比10大的时候退出
    EXIT WHEN num > 10;
  END LOOP;
END;
/
--GP
/*
  循环输出1到10
*/
do $$
declare
  --定义一个数，初始值为1
  num numeric := 1;
begin
  --循环开始
  loop
    --输出num
    raise notice '%',num;
    --num+1
    num := num + 1;
    --循环退出条件，当num第一次出现比10大的时候退出
    exit when num > 10;
  end loop;
end$$;
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

WHILE循环

--Oracle
/*
  循环输出1到10
*/
DECLARE
  --定义一个数，初始值为1
  num NUMBER := 1;
BEGIN
  --先判断退出循环条件，退出循环条件为 num <= 10
  WHILE num <= 10 
  LOOP
    --输出num
    dbms_output.put_line(num);  
    --num++
    num := num + 1;
  END LOOP;
END;
/
--GP
/*
  循环输出1到10
*/
do $$
declare
  --定义一个数，初始值为1
  num numeric := 1;
begin
  --先判断退出循环条件，退出循环条件为 num <= 10
  while num <= 10 
  loop
    --输出num
    raise notice '%',num;  
    --num++
    num := num + 1;
  end loop;
end$$;
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

FOR循环

--Oracle
/*
  循环输出1到10
*/
BEGIN
  FOR i IN 1..10 LOOP
    dbms_output.put_line(i);  
  END LOOP;
END;
/
--GP
/*
  循环输出1到10
*/
do $$
begin
  for i in 1..10 loop
    raise notice '%',i;  
  end loop;
end$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CONTINUE

--Oracle
/*
  循环输出1到4,6到10，不输出5
*/
BEGIN
  FOR i IN 1..10 LOOP
    CONTINUE WHEN i = 5;
    dbms_output.put_line(i);  
  END LOOP;
  FOR i IN 1..10 LOOP
    IF i = 5 THEN
      CONTINUE;
    END IF;
    dbms_output.put_line(i);  
  END LOOP;
END;
/
--GP
/*
  循环输出1到4,6到10，不输出5
*/
do $$
begin
  for i in 1..10 loop
    continue when i = 5;
    raise notice '%',i;  
  end loop;
  for i in 1..10 loop
    if i = 5 then
      continue;
    end if;
    raise notice '%',i;  
  END LOOP;
end$$;
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

SQL游标

普通游标

--Oracle
DECLARE
  --声明游标
  CURSOR exp_cursor IS 
    SELECT * FROM example;
  --声明基于游标的记录变量
  exp_cursor_record exp_cursor%ROWTYPE;
BEGIN
  --如果游标不是打开状态，则打开游标
  IF NOT exp_cursor%ISOPEN THEN
    OPEN exp_cursor;
  END IF;
  --循环获取游标的数据到基于游标的记录变量
  LOOP
    FETCH exp_cursor INTO exp_cursor_record;
    EXIT WHEN exp_cursor%NOTFOUND OR exp_cursor%NOTFOUND IS NULL;
    dbms_output.put_line(exp_cursor_record.example_id || '|' || exp_cursor_record.example_name);   
  END LOOP;
  CLOSE exp_cursor;
END;
/
--GP
do $$
declare
  --声明游标，游标在声明时默认打开
  exp_cursor cursor for select * from example;
  --声明基于表的记录变量
  exp_cursor_record example%rowtype;
begin
  open exp_cursor;
  --循环获取游标的数据到基于游标的记录变量
  loop
    fetch exp_cursor into exp_cursor_record;
    if found then
      raise notice '%|%',exp_cursor_record.example_id,exp_cursor_record.example_name;
    else
      exit;
    end if;
  end loop;
  close exp_cursor; 
end$$;
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

游标的FOR循环

--Oracle
BEGIN
  FOR i IN (
    SELECT * FROM example
  )
  LOOP
    dbms_output.put_line(i.example_id || '|' || i.example_name);
  END LOOP;
END;
/
--GP
do $$
declare
  i record;
begin
	for i in (select * from example) loop 
	  raise notice '%|%',i.example_id,i.example_name;
	end loop;
end$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

动态游标

--Oracle
CREATE TABLE example_202011 (
  example_id NUMBER,
  example_name VARCHAR2(200)
);
CREATE TABLE example_202010 (
  example_id NUMBER,
  example_name VARCHAR2(200)
);
INSERT INTO example_202011 VALUES (20201101,'样例20201101');
INSERT INTO example_202011 VALUES (20201102,'样例20201102');
INSERT INTO example_202010 VALUES (20201001,'样例20201001');
INSERT INTO example_202010 VALUES (20201002,'样例20201002');
COMMIT;
DECLARE
  --定义动态游标类型
  TYPE cursor_type IS REF CURSOR;
  --声明动态游标类型的变量
  v_cursor cursor_type;
  --当月月份变量   &thismonth表示替代变量
  thismonth VARCHAR2(10) := '&thismonth';
  --动态sql
  v_sql VARCHAR2(32767);
  --example_id变量用来接收游标中的example_id
  example_id NUMBER;
  --example_name变量用来接收游标中的example_name
  example_name VARCHAR2(200);
BEGIN
  --动态从指定月份的备份表中查询数据
  v_sql := q'[SELECT * FROM example_]' || thismonth;
  --打开动态游标并关联动态sql
  OPEN v_cursor FOR v_sql;
  --循环输出游标中的所有数据
  LOOP
    FETCH v_cursor INTO example_id,example_name;
    EXIT WHEN v_cursor%NOTFOUND OR v_cursor%NOTFOUND IS NULL;
    dbms_output.put_line(example_id || '|' || example_name);
  END LOOP;
  --关闭游标
  CLOSE v_cursor;
END;
/
--GP
create table example_202011 (
  example_id numeric,
  example_name varchar(200)
);
create table example_202010 (
  example_id numeric,
  example_name varchar(200)
);
insert into example_202011 values (20201101,'样例20201101');
insert into example_202011 values (20201102,'样例20201102');
insert into example_202010 values (20201001,'样例20201001');
insert into example_202010 values (20201002,'样例20201002');
do $$
declare
  --定义动态游标类型
  v_cursor refcursor;
  thismonth varchar(10) := '${thismonth}';
  --动态sql
  v_sql varchar(32767);
  --example_id变量用来接收游标中的example_id
  example_id numeric;
  --example_name变量用来接收游标中的example_name
  example_name varchar(200);
BEGIN
  --动态从指定月份的备份表中查询数据
  v_sql := 'select * from example_' || thismonth;
  --打开动态游标并关联动态sql
  open v_cursor for execute v_sql;
  --循环输出游标中的所有数据
  loop
    fetch v_cursor into example_id,example_name;
    exit when not found;
    raise notice '%|%',example_id,example_name;
  end loop;
  --关闭游标
  close v_cursor;
end$$;
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
73
74
75
76
77
78
79
80

异常处理

--Oracle
BEGIN
  dbms_output.put_line(1/0);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
/
--GP
do $$
begin
  raise notice '%',1/0;
  exception when others then 
    raise notice '%',sqlerrm;
end$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

存储过程和函数

1. ORACLE中存储过程和函数是分开的，但是在GP中，存储过程就是函数；
2. ORACLE中的函数和存储过程，在程序包中可以重载，GP的函数可以重载；
3. ORACLE的存储过程默认是以定义者权限执行，GP的函数默认是以调用者权限执行；
4. ORACLE存储过程中可以有多个事务，GP函数中，整个函数就是一个事务；
5. ORCLE中有自治事务，子事务的提交或回滚不会影响主事务的提交或回滚，GP中不存在自治事务，但部分可以通过begin exception end;块来实现自治事务；

--Oracle
/*
  --最简单的函数
  输入什么，就输出什么
*/
CREATE OR REPLACE FUNCTION print(
  str IN VARCHAR2
) RETURN VARCHAR2
AS 
BEGIN
  RETURN str;
END;
/
SELECT print('123') FROM dual;
CREATE OR REPLACE PROCEDURE delete_example
AS
BEGIN
  DELETE FROM example;
  COMMIT;
  EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
END;
/
CALL delete_example();
EXEC delete_example();
BEGIN
  delete_example();
END;
/
--GP
/*
  --最简单的函数
  输入什么，就输出什么
*/
create or replace function print(varchar) 
returns varchar
language plpgsql
as $function$
declare 
begin 
	return $1;
end;
$function$
;
select print('123');
do $$
begin
	perform print('123');
end$$;
create or replace function delete_example()
returns void
language plpgsql
as $procedure$
begin 
	delete from example;
	exception when others then
	  rollback;
end;
$procedure$
;
select delete_example();
do $$
begin
	perform delete_example();
end$$;
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

常用的数据字典表

ORACLE常用数据字典表

--显示用户下所有表和视图
SELECT * FROM cat;
--显示用户下的表
SELECT * FROM user_tables;
--显示用户下的视图
SELECT * FROM user_views;
--显示所有表
SELECT * FROM all_tables;
--显示多有视图
SELECT * FROM all_views;
--显示用户下的约束
SELECT * FROM user_constraints;
SELECT * FROM user_cons_columns;
--显示所有约束
SELECT * FROM all_constraints;
SELECT * FROM all_cons_columns;
--显示用户下的索引
SELECT * FROM user_indexes;
SELECT * FROM user_ind_columns;
--显示用户下的对象
SELECT * FROM user_objects;
--显示所有对象
SELECT * FROM all_objects;
--显示用户下的源代码
SELECT * FROM user_source;
--显示所有源代码
SELECT * FROM all_source;
--显示用户下的触发器
SELECT * FROM user_triggers;
--显示多有触发器
SELECT * FROM all_triggers;
--显示表中列的信息
SELECT * FROM user_tab_columns;
SELECT * FROM all_tab_columns;
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

GP查询字段信息

select
	b.nspname 模式名,
	a.relname 表名,
	c.attname 字段名,
	d.typname 字段类型,
	case
		when decode(d.typtype, 'd', d.typtypmod::varchar, c.atttypmod::varchar)::int4 = -1 then null
		when decode(d.typtype, 'd', d.typbasetype::varchar, c.atttypid::varchar)::oid in (1042, 1043) then decode(d.typtype, 'd', d.typtypmod::varchar, c.atttypmod::varchar)::int4 -4
		when decode(d.typtype, 'd', d.typbasetype::varchar, c.atttypid::varchar)::oid in (1560, 1562) then decode(d.typtype, 'd', d.typtypmod::varchar, c.atttypmod::varchar)::int4
		else null
	end 字符类型的最大精度,
	c.attnotnull 是否有非空约束,
	e.adsrc 默认值,
	f.description 注释
from
	pg_class a
inner join pg_namespace b
on
	(a.relnamespace = b.oid)
inner join pg_attribute c
on
	(a.oid = c.attrelid)
inner join pg_type d
on
	(c.atttypid = d.oid)
left join pg_attrdef e
on
	(a.oid = e.adrelid
		and c.attnum = e.adnum)
left join pg_description f 
on
	(a.oid = f.objoid
		and c.attnum = f.objsubid)
where
	c.attnum > 0
	--and a.relname = 'tmp'
	--and b.nspname = 'tool'
;
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

GP查询主键约束

with tmp_a as (
select
	b.nspname 模式名,
	a.relname 表名,
	c.attname 字段名,
	c.attnum 字段号,
	case
		when g.indkey is not null
		and g.indisunique = 'false' then 'true'
		else 'false'
	end::boolean 是否普通索引列,
	g.indisprimary 是否主键索引列,
	g.indisunique 是否唯一索引列
from
	pg_class a
inner join pg_namespace b
on
	(a.relnamespace = b.oid)
inner join pg_attribute c
on
	(a.oid = c.attrelid)
left join pg_index g 
on
	(a.oid = g.indrelid
		and c.attnum = any(g.indkey))
where
	c.attnum > 0
	--and a.relname = 'tmp'
	--and b.nspname = 'tool'
)
select
	模式名,
	表名,
	string_agg(字段名, ',' order by 字段号) 主键约束
from
	tmp_a
where
	是否主键索引列 = 'true'::boolean
group by
	模式名,
	表名;
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

GP查询建索引语句

select
	*
from
	pg_catalog.pg_indexes
	--where
	--tablename = 'tmp'
	--and schemaname = 'tool'
;
1
2
3
4
5
6
7
8

GP查询分布键

select
	a.oid,
	c.nspname 模式名,
	a.relname 表名,
	decode(b.policytype, 'p', '分区策略', 'r', '复制策略') 分布策略,
	pg_get_table_distributedby(a.oid) 分布键,
	a.reloptions 压缩类型
from
	pg_class a
inner join gp_distribution_policy b
on
	(a.oid = b.localoid)
inner join pg_namespace c 
on
	(a.relnamespace = c.oid)
	--where
	--	relname = 'tmp'
	--	and nspname = 'tool'
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

GP查询表大小

select relname,pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables;
1