Postsql 循环sql代码

Postsql语句编写

背景

1.hr.job存储所有工作岗位内容
2.hr.employee存储所有员工内容
3.hr.department存储所有部门内容
4，一个岗位对应多个员工，部门与上级部门一一对应，员工与上级员工一一对应。
5.需要循环更改，使用select嵌套方法可更改单条

目的

将hr.job中所有岗位内容的上级岗位字段赋值，被赋值对象为该岗位上所属人员对应的上级人员的所数岗位

代码编写

do $$
declare
    _record record;
    _department record;
    _employee record;
    _employee_up record;
begin
    for _record in select id from hr_job
loop
    select department_id into _department from hr_job where id = _record.id;
    select id,job_id,parent_id,name into _employee from hr_employee where job_id = _record.id and department_id = _department.department_id;
    select id,job_id,name into _employee_up from hr_employee where id = _employee.parent_id;
    update hr_job set parent_id = _employee_up.job_id where id = _record.id;
    RAISE NOTICE 'job_id: %', _record.id;
    RAISE NOTICE 'department_id: %', _department.department_id;
    RAISE NOTICE 'job_id: % name:%', _employee.job_id,_employee.name;
    RAISE NOTICE 'employee_up: % name:%', _employee_up.id,_employee_up.name;
    RAISE NOTICE 'employee_up_job_id: %', _employee_up.job_id;
    end loop;
end; $$
LANGUAGE plpgsql;

代码解释

    创建一个无需创建出来即可执行的function，效果等同 CREATE OR REPLACE FUNCTION
    do $$ 
    declare
        定义变量区域，通常为char或int,record为记录类型，它们没有预定义的结构。 它们在SELECT或者FOR命令中获取实际的行结构。
	    _record record;
    begin
        代码执行区域
        for _record in select id from hr_job
        loop
            循环语句执行，RAISE NOTICE效果等同print 
        end loop;
    end; $$
    明确数据库语言，可以不写
    LANGUAGE plpgsql;

扩展

Do $$ 等同于	
    create or replace function test(n integer)
    returns integer as $$
前者是后者的一个无命名无返回函数的简写

While 循环

while condition loop
    statement;
end loop;

For 循环

for loop_counter in [reverse] from..to [by exepression] loop
    statement;
end loop;

If 判断

if 判断 Then
    statement
end if;