PostgreSQL存储过程示例

CREATE OR REPLACE FUNCTION f_insert_app_op_assign(oldid int8, newid int8) RETURNS "pg_catalog"."void" AS 
$BODY$
DECLARE c_record record; num int8;
BEGIN
  for c_record in (SELECT id, app_ver_id, op_id, role_id, is_inner FROM app_op_assign where op_id in(SELECT id from app_op_security where id = oldid) and role_id not in(3,4)) loop
    DELETE from app_op_assign where role_id = c_record.role_id and op_id = newid;
    INSERT INTO app_op_assign(id,app_ver_id,op_id,role_id, is_inner)
    VALUES (c_record.id + 10001,c_record.app_ver_id,newid,c_record.role_id,c_record.is_inner);
  END loop;
END; 
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
select f_insert_app_op_assign(7872525861819131578,6098882599055920104); 

另一个案例：

DECLARE c_record record; c_temp record; exesql VARCHAR; c_code VARCHAR;

BEGIN
    UPDATE sys_department SET full_ids = '',full_codes = '',full_names = '';
    exesql := 'select id,pid,n  from sys_department_r where n!=0 order by n desc';
FOR c_record IN EXECUTE exesql loop SELECT
    ID :: VARCHAR,
    code,
    NAME INTO c_temp
FROM
    sys_department
WHERE
    ID = c_record.pid;
IF c_temp.code = ''
OR c_temp.code IS NULL THEN
    UPDATE sys_department
SET full_ids = concat (full_ids, '/', c_temp. ID),
 full_names = concat (full_names, '/', c_temp. NAME)
WHERE
    ID = c_record. ID;
ELSE
    UPDATE sys_department
SET full_ids = concat (full_ids, '/', c_temp. ID),
 full_codes = concat (full_codes, '/', c_temp.code),
 full_names = concat (full_names, '/', c_temp. NAME)
WHERE
    ID = c_record. ID;
END
IF;
END loop;
END;