匿名代码块介绍:
匿名代码块是在PostgreSQL 9.X版本引入的新功能【注:9.0以下不支持,可通过select version()脚本确认自己使用的数据库版本】,通过DO关键字指定执行代码块,其功能和函数类似,相当于轻量版的函数,但不能传参数和返回值。适合进行一些较为复杂的一次性查询,在日常中使用可以大大降低一些重复脚本执行的人力成本。
准备工作——创建数据表:
在开始使用匿名代码块之前,首先创建一张要操作的数据表作为函数中的操作对象。
create table mydb.mysc.learn01(
statistics_dt date -- 数据日期
,cust_id varchar(20) -- 客户号
,cust_name varchar(20) -- 客户名称
,aum_avg decimal(16,2) -- 资产月日均
,age int -- 年龄
,gender varchar(1) -- 性别标志:0男 1女
,PRIMARY KEY(statistics_dt,cust_id)
);-- DISTRIBUTED BY 语句适用于分布式部署的系统,单机版不支持
-- 赋权
ALTER TABLE mydb.mysc.learn01
OWNER to postgres;
-- 添加备注信息
comment on table mydb.mysc.learn01 is 'postgresql技能提升学习表01';
comment on column mydb.mysc.learn01.statistics_dt is '数据日期';
comment on column mydb.mysc.learn01.cust_id is '客户号';
comment on column mydb.mysc.learn01.cust_name is '客户名称';
comment on column mydb.mysc.learn01.aum_avg is '资产月日均';
comment on column mydb.mysc.learn01.age is '年龄';
comment on column mydb.mysc.learn01.gender is '性别标志:0男 1女';
使用匿名函数——插入一千条数据:
在以下脚本中通过一定的规则和随机数实现插入1000天记录的任务。
DO LANGUAGE 'plpgsql' -- 指定匿名块的语言,可省略默认为plpgsql
$BODY$
BEGIN
FOR counter IN 1..1000 LOOP -- 开始循环
INSERT INTO mydb.mysc.learn01
(statistics_dt -- 数据日期
,cust_id -- 客户号
,cust_name -- 客户名称
,aum_avg -- 资产月日均
,age -- 年龄
,gender -- 性别标志:0男 1女
)
VALUES(
date'2021-01-31' -- 数据日期
,RIGHT('000'||counter,4) -- 客户号
,'编号'||RIGHT('000'||counter,4) -- 客户名称
,CAST(RANDOM() * 10000 AS decimal(16,2)) -- 资产月日均:随机生成0-10000的数值
,CAST(RANDOM() * 89 AS INT) + 1 -- 年龄:随机生成1-90的整数
,CAST(CAST(RANDOM() * 2 AS INT)%2 AS CHAR) -- 性别:随机生成
);
END LOOP;
END
$BODY$;
在上述匿名函数执行结束后,查询验证数据,已经成功完成数据的插入,如图,