GP开发文档

psql -d etl -E -U tool -W -h 10.75.41.115 -p 5432

以tool用户登录到10.75.41.115主机上5432端口下的etl数据库，并且回显\d对应的sql语句

psql -d etl -U tool -W -h 10.75.41.115 -p 5432 -f xxxx.psql

以tool用户登录到10.75.41.115主机上5432端口下的etl数据库执行xxxx.psql脚本

-d

指定数据库名

-E

回显由\d和其他反斜杠命令生成的实际查询

-U

指定用户名

-W

强制提示输入密码

-h

主机名

-p

端口

-f

执行sql脚本

\connect etl tool;

以tool用户切换到etl数据库

\q

退出psql客户端

\df

查看所有的函数

\df tool.*

查看tool下的函数

\dn

查看所有的schemas

\dt tool.*

查看schema为tool下的所有的表

\d

展示表的信息

\d+

比前面的\d 多了size和despription

\dv gp_toolkit.gp_resq*

查看资源组的视图

\du+

查看用户信息

\set

设置替代变量

\!

执行外部命令

操作

ORACLE

GP

创建非分区表

CREATE TABLE test(

id NUMBER,

name VARCHAR2(40)

);

create table test(

id int,

name varchar(40)

) distributed by (id);

创建分区表

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'))

);

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);

分区表增加分区

ALTER TABLE test ADD PARTITION p20090104 VALUES LESS THAN (TO_DATE('20090105','yyyymmdd'));

alter table test

add partition p20090104 start (date '2009-01-04') inclusive

end (date '2009-01-05')

exclusive;

分区表删除分区

ALTER TABLE test DROP PARTITION p20090104;

alter table test drop partition p20090104;

分区表截断分区

ALTER TABLE test TRUNCATE PARTITION p20090101;

alter table test

truncate

partition p20090101;

分区表分裂分区

ALTER TABLE test SPLIT PARTITION p200905_mon AT (TO_DATE('20090515','yyyymmdd'))

INTO (PARTITION p200905_01,PARTITION p200905_02);

alter table test split partition p200905_mon at ('20090515')

into

(partition p200905_01,

partition p200905_02);

删除表

DROP TABLE test;

drop table if exists test;

重命名表

rename a to b;

alter table a rename to b;

截断表

TRUNCATE TABLE test;

truncate table test;

创建索引

CREATE INDEX idx_test ON test(id);

create index idx_test on test(id);

创建主键

ALTER TABLE test ADD CONSTRAINT pk_test PRIMARY KEY (id);

alter table test add constraint pk_test primary key (id);

删除主键

ALTER TABLE test DROP CONSTRAINT pk_test;

alter table test drop constraint if exists pk_test;

删除索引

DROP INDEX IND_TEST;

drop index if exists ind_test;

添加字段

ALTER TABLE test add (

column1 number,

column2 varchar2(100)

);

alter table test

add column1 numeric,

add column2 varchar(10);

修改字段

ALTER TABLE test modify (column1 VARCHAR2(100));

alter table test alter column column1 type varchar(100);

alter table test alter column1 type varchar(100);

删除字段

ALTER TABLE test DROP COLUMN column1;

alter table test drop column if exists column1;

alter table test drop if exists column1;

字段重命名

ALTER TABLE test RENAME COLUMN column2 TO column1;

alter table test rename column column2 to column1;

alter table test rename column2 to column1;

创建视图

CREATE VIEW v_test AS SELECT * FROM test;

create view v_test as

select

*

from

test;

操作

ORACLE

GP

插入记录

INSERT INTO test VALUES (001,'SUNWG',TO_DATE('2009-01-01','YYYY-MM-DD'));

Insert into test values (001,'SUNWG','2009-01-01');

删除记录

DELETE FROM test WHERE id = 001;

delete from test where id = 001;

关联更新记录

UPDATE test a SET a.column1 = (SELECT b.column1 FROM test1 b WHERE a.id = b.id);

update test a set column1 = b.column1 from test1 b where a.id = b.id;

查询表中前10条记录

SELECT * FROM test WHERE ROWNUM < 10;

select * from test limit 10;

两表内关联

SELECT * FROM test1 a, test2 b WHERE a.id = b.id;

select * from test1 a inner join test2 b on (a.id = b.id);

两表左外关联

SELECT * FROM test1 a, test2 b WHERE a.id = b.id(+);

select * from test1 a left join test2 b on (a.id = b.id);

事务提交

INSERT INTO test VALUES (001,'SUNWG',TO_DATE('2009-01-01','YYYY-MM-DD'));

COMMIT;

begin;

insert into test values (001,'SUNWG','2009-01-01');

commit;

事务回滚

INSERT INTO test VALUES (001,'SUNWG',TO_DATE('2009-01-01','YYYY-MM-DD'));

ROLLBACK;

begin;

insert into test values (001,'SUNWG','2009-01-01');

rollback;

数据类型转换

SELECT to_number('101') FROM dual;

select cast('101' as numeric);

select '101' :: numeric;

除法运算

SELECT 1/2 FROM dual;

select 1.0/2;

并集

SELECT 1 FROM dual

UNION

SELECT 2 FROM dual;

SELECT 1 FROM dual

UNION ALL

SELECT 2 FROM dual;

select 1

union

select 2;

select 1

union all

select 2;

交集

SELECT 1 FROM dual

INTERSECT

SELECT 1 FROM dual;

select 1

intersect

select 1;

select 1

intersect all

select 1;

差集

SELECT 1 FROM dual

MINUS

SELECT 2 FROM dual;

select 1

except

select 2;

select 1

except all

select 2;

子查询

SELECT * FROM (SELECT * FROM test);

select * from (select * from test) as a;

日期抽取

SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM dual;

select extract(century from now()); --世纪

select extract(year from now()); --年

select extract(dow from now()); --星期几

select extract(quarter from now()); --季度

时间截取

SELECT TRUNC(SYSDATE,'hh') FROM dual;

select date_trunc('hour', now());

null和’’

SELECT '123'||'' FROM dual; --'123'

SELECT '123'||NULL FROM dual;--'123'

select '123'||''; --'123'

select '123'||null; --null

delete 剔重

DELETE FROM test a WHERE rowid < (

SEELCT MAX(rowid) from test b

WHERE a.id = b.id

);

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

);

当前的系统时间

SELECT SYSDATE,current_timestamp FROM dual;

select current_timestamp,now();

当前时间向前推一天

SELECT SYSDATE-1 FROM dual;

select current_timestamp - interval '1 day';

当前时间向前推一个月

SELECT add_months(SYSDATE,-1) FROM dual;

select current_timestamp - interval '1 month';

当前时间向前推一年

SELECT add_months(SYSDATE,-12) FROM dual;

select current_timestamp - interval '1 year';

当前时间向前推一小时

SELECT SYSDATE-1/24 FROM dual;

select current_timestamp - interval '1 hour';

当前时间向前推一分钟

SELECT SYSDATE-1/24/60 FROM dual;

select current_timestamp - interval '1 min';

当前时间向前推30秒

SELECT SYSDATE-1/24/60/60*30 FROM dual;

select current_timestamp - interval '60 second';

正则匹配

SELECT 1 FROM dual WHERE regexp_like ('123','[0-9]+');

select 1 where '123' ~ '[0-9]+';

正则截取

SELECT regexp_substr('你好123','[0-9]+') FROM dual;

select substring ('你好123' from '[0-9]+');

分区表查询指定分区

SELECT * FROM test PARTITION (p20090101);

select * from test_1_prt_p20090101;

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

SELECT SUBSTR('12345',-1,3) FROM dual;

select substr('12345',-1,3);

--等价于

select substr('12345',1,1);

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

SELECT INSTR('123245','24',1,1) FROM dual;

select strpos('123245','24');

select position ('24' in '123245');

指定日期间相差的月份

SELECT months_between(to_date('202108','yyyymm'),to_date('202107','yyyymm')) FROM dual;

select extract(month from age(to_date('202108','yyyymm'),to_date('202107','yyyymm')));

判空

SELECT NVL(NULL,1) FROM dual;

SELECT NVL2(NULL,1,2) FROM dual;

select coalesce (null::integer,1);

序号

SELECT ROWNUM,NAME FROM (

SELECT 'ha' NAME FROM dual

UNION ALL

SELECT 'haha' FROM dual

);

select row_number() over(order by 1) xh,name from (

select 'ha' name

union all

select 'haha'

) a;

to_char数字转换为字符

SELECT to_char(123) FROM dual;

select to_char(123,'fm999');

字符转换日期

SELECT to_date('20210827','yyyymmdd') FROM dual;

select to_date('20210827','yyyymmdd');

select to_timestamp('20210827','yyyymmdd');

select cast ('20210827' as date);

select cast ('20210827' as timestamp);

字符转换为数字

SELECT to_number('20210827') FROM dual;

select cast('20210827' as numeric);

字符串分割

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;

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;

递归查询

start with…connect by

with recursive

合并

DROP TABLE example;

CREATE TABLE example (

example_id NUMBER,

example_name VARCHAR2(200)

);

INSERT INTO example VALUES (1,'样例1');

INSERT INTO example VALUES (2,'样例2');

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;

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;

操作

ORACLE

GP

schema授权

无

grant usage,create on schema tool to tool;

表授权查询、插入、更新

GRANT SELECT,INSERT,UPDATE ON test TO tool;

grant select,insert,update on table test to tool;

表授权所有用户访问

GRANT SELECT ON test TO public;

grant select on table test to public;

存储过程授权

GRANT EXECUTE,DEBUG ON delete_example TO tool;

grant execute on function delete_example() to tool;

数据库

定义

举例

ORACLE

DECLARE --可选

--variables,cursors,user-defined exceptions

BEGIN --强制的（必须的）

--一个或多个SQL语句

--一个或多个PL/SQL语句

EXCEPTION --可选

--当错误发生是要进行的处理

END; --强制的（必须的）

/

DECLARE

var VARCHAR2(100) := 'hello world!';

BEGIN

dbms_output.put_line(var);

END;

/

GP

do $$

declare --可选

--variables,cursors,user-defined exceptions

begin --强制的（必须的）

--一个或多个SQL语句

--一个或多个PL/PGSQL语句

exception --可选

--当错误发生是要进行的处理

end$$; --强制的（必须的）

do $$

begin

raise notice 'hello world!';

end$$;

数据库

定义

举例

ORACLE(SQL PLUS)

define

define a = 1

SELECT &a FROM dual;

SELECT &&a FROM dual;

GP(PSQL)

\set

\set a 1

select :a;

数据库

定义

描述

举例

ORACLE

‘’

单引号，字符串中有单引号，需加’转义

SELECT 'What''s your name?' FROM dual;

GP

‘’

单引号，字符串中有单引号，需加’转义

select 'What''s your name?';

ORACLE

q'

q’可以自定义定界符，允许嵌套

--通过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?$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

类型

样式

举例

ORACLE

CASE表达式

CASE selector

WHEN 表达式1 THEN 结果1

WHEN 表达式2 THEN 结果2

...

WHEN 表达式n THEN 结果n

[ELSE 结果n+1]

END; --必须以END结尾

SELECT CASE 1 WHEN 2 THEN 1 ELSE 0 END FROM dual;

GP

CASE表达式

case selector

when 表达式1 then 结果1

when 表达式2 then 结果2

...

when 表达式n then 结果n

[else 结果n+1]

end; --必须以end结尾

select case 1 when 2 then 1 else 0 end;

ORACLE

CASE表达式

CASE

WHEN 搜索条件1 THEN 结果1

WHEN 搜索条件2 THEN 结果2

...

WHEN 搜索条件n THEN 结果n

[ELSE 结果n+1]

END; --必须以END结尾

SELECT CASE WHEN 1 = 2 THEN 1 ELSE 0 END FROM dual;

GP

CASE表达式

case

when 搜索条件1 then 结果1

when 搜索条件2 then 结果2

...

when 搜索条件n then 结果n

[else 结果n+1]

end; --必须以end结尾

select case when 1 = 2 then 1 else 0 end;

ORACLE

CASE语句

CASE selector

WHEN 表达式1 THEN

statements1;

WHEN 表达式2 THEN

statements2;

...

WHEN 表达式n THEN

statementsn;

[ELSE

statementsn+1]

END CASE; --必须以END CASE;结尾

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;

/

ORACLE

CASE语句

CASE

WHEN 搜索条件1 THEN

statements1;

WHEN 搜索条件2 THEN

statements2;

...

WHEN 搜索条件n THEN

statementsn;

[ELSE

statementsn+1]

END CASE; --必须以END CASE;结尾

GP

CASE语句

case selector

when 表达式1 then

statements1;

when 表达式2 then

statements2;

...

when 表达式n then

statementsn;

[else

statementsn+1]

end case; --必须以end case;结尾

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$$;

GP

CASE语句

case

when 搜索条件1 then

statements1;

when 搜索条件2 then

statements2;

...

when 搜索条件n then

statementsn;

[else

statementsn+1]

end case; --必须以end case;结尾

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

举例

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$$;

数据库

类型

举例

ORACLE

函数

/*

--最简单的函数

输入什么，就输出什么

*/

CREATE OR REPLACE FUNCTION print(

str IN VARCHAR2

) RETURN VARCHAR2

AS

BEGIN

RETURN str;

END;

/

SELECT print('123') FROM dual;

ORACLE

存储过程

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$$;

--显示用户下所有表和视图

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;

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'

;

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

模式名,

表名;

select

*

from

pg_catalog.pg_indexes

--where

--tablename = 'tmp'

--and schemaname = 'tool'

;

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'

;

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