oracle存储过程迁移到PostgreSQL之问题总结

相关文章推荐

不羁的四季豆 · 民政部发布“防灾减灾，从我做起”获奖征文名单· 3 月前 ·

唠叨的硬盘 · 乐山市市中区积极创建全国和谐社区建设示范区- ...· 1 年前 ·

酷酷的生姜 · 大风歌（汉朝开国皇帝刘邦创作诗作）_百度百科· 1 年前 ·

任性的数据线 · 深圳市科技创新委员会关于公布2021年第一批 ...· 1 年前 ·

想发财的镜子 · 申报目录｜全新广汽本田皓影 e:PHEV ...· 1 年前 ·

首先通过了解，在oracle中写的存储过程很多都是在包里面，然而PostgreSQL没有包的存在，我只能把每个包的存储过程通过脚本转化成PostgreSQL的函数；在PostgreSQL中的函数能满足oracle中存储过程的各种写法实现。

在处理过程中总结了一些不同之处的对比：

alter table tab_name add constraint pk_tab_name primary key(column_id) using index; alter table tab_name add constraint pk_tab_name primary key(column_id); create sequence SEQ_TAB_NAME
minvalue 1
maxvalue 9999999999999999
start with 1 increment by 1
cache 20; create sequence seq_tab_name
minvalue 1
maxvalue 9223372036854775807
start 1
increment 1
cache 20; Sequence语法及使用差异 oracle在存储过程或者函数等中调用另外的函数或存储过程赋值:comm_pkg.proc_cfg_validate(v_f_userid, v_f_tenantid, errcode, errmsg);或mTable := fun_gettablename(v_TenantID, 'bill_main'); 在pgsql函数中调用另外函数:
1.out返回数据的函数
out返回的数据名称与into到变量的名称相同时，不能直接into，可以取别名后再into
(1)可以这样简单粗暴的方式按照返回数据顺序进行赋值
SELECT * INTO errcode,errmsg FROM seeyon_zzyw."COMM_PKG.PROC_CFG_VALIDATE"(v_UserID::numeric, v_TenantID);
(2)可以把返回数据列出进行赋值
SELECT errcode as f_code,errmsg as f_msg INTO errcode,errmsg FROM seeyon_zzyw."COMM_PKG.PROC_CFG_VALIDATE"(v_UserID::numeric, v_TenantID);
2.不是out返回数据的函数，是直接return返回的函数，需要考虑返回空值
(1)select coalesce((SELECT fun_gettablename from seeyon_zzyw.fun_gettablename(0, 'bill_main')),'') into mBillTableName from dual;
(2)SELECT seeyon_zzyw.fun_comm_isoperation(v_F_tenantID) into mcount;--这样赋值，如果返回值是空会报错
(3)mcount = fun_comm_auth(V_F_Userid);--这样赋值，函数需要考虑是否为空 for cur_deleterole in (select r.f_Roleid,r.rolecode
from Sys_Role r
start with r.f_Roleid = V_F_ROLEID
connect by Prior r.f_Roleid = r.f_Parentid) loop
mid:= 'id=' || cur_deleterole.f_roleid || 'code='||cur_deleterole.rolecode;
dbms_output.put_line(mid);--控制台打印
end loop; for mrole,mcode in (WITH RECURSIVE a AS (
SELECT r.f_Roleid,r.f_rolecode
FROM Sys_Role r
WHERE r.f_Roleid = V_F_ROLEID
UNION ALL
SELECT d.f_Roleid,d.f_rolecode
FROM Sys_Role d
JOIN a ON a.f_Roleid = d.f_Parentid )
SELECT f_Roleid,f_rolecode FROM a) loop
mid:= 'id=' || mrole || 'code='||mcode;
raise info '%',mid;--控制台打印
end loop; 1.oracle中start with connect by 语句在pgsql中换成with recursive 语句；2.oracle中for的值是结果集，而pgsql中是分别的字段并且for这些字段不能加括号。 type up_drtemp_data is record(
f_a varchar(500),
f_b varchar(500),
f_c varchar(500),
f_d varchar(500),
f_e varchar(500),
f_f varchar(500),
f_g varchar(500),
f_h varchar(500),
f_i varchar(500),
f_j varchar(500),
f_k varchar(500),
f_l varchar(500),
f_m varchar(500),
f_n varchar(500)
);在函数或者包中直接声明使用type create type up_drtemp_data AS(
f_a varchar(500),
f_b varchar(500),
f_c varchar(500),
f_d varchar(500),
f_e varchar(500),
f_f varchar(500),
f_g varchar(500),
f_h varchar(500),
f_i varchar(500),
f_j varchar(500),
f_k varchar(500),
f_l varchar(500),
f_m varchar(500),
f_n varchar(500)
);在pgsql函数中要使用type需要另外创建，然后在函数中直接使用在update, insert语句可以给表取别名 (1)这种支持给更新表取别名： update base_ItemStd_284 t set (f_aircomid,f_paytypeid,f_std,f_useBegin,
f_useend) = (select trunc(f_aircomid),
trunc(f_paytypeid),f_std,f_useBegin,
f_useend from base_itemstd_tmp bit
where f_guid='b4e00167-7134-89f6-dd23-05fbe3bb97c1'
and t.f_itemstdid = bit.f_itemstdid)
where exists(select f_itemstdid from base_itemstd_tmp bit
where t.f_itemstdid = bit.f_itemstdid
and f_guid='b4e00167-7134-89f6-dd23-05fbe3bb97c1'); (2)这种方式就不支持(不支持的可以用表名)： update base_ItemStd_284 set (f_aircomid,f_paytypeid,f_std,f_useBegin,
f_useend) = (1,2,3,4,5)
where f_itemstdid=1 oracle中动态SQL可以直接拼接在执行函数或语句后面：open r_iplist for 'select count(*)
from base_handler a left outer join sys_user b
on a.f_userid = b.f_userid
and a.f_tenantid = '||trunc(r_tenantid)||'
and a.f_type not in (1, 2)' pg中也可以，但是推荐在执行前先拼接完整赋值给变量如msql，再去执行：sqlText:='select count(*)
from base_handler a left outer join sys_user b
on a.f_userid = b.f_userid
and a.f_tenantid = '||trunc(r_tenantid)||'
and a.f_type not in (1, 2)'; open r_iplist for execute sqlText; oracle中表连接 select u.f_Userid,
u.f_Usercode,
u.f_Username,
decode(nvl(r.f_Roleid, 0), 0, 0, 1) as F_IsChecked,
r.f_Roleid,
u.f_Isdelete
from Sys_User u,
(select * from Sys_Roleuser where F_ROLEID = '722') r
where u.f_Userid = r.f_Userid(+)
and u.f_Tenantid = 284
and u.f_State = 1
and u.f_Isdelete = 0
order by u.f_Tenantid, u.f_Userid; pgsql中表连接 select u.f_Userid,
u.f_Usercode,
u.f_Username,
decode(nvl(r.f_Roleid, 0), 0, 0, 1) as F_IsChecked,
r.f_Roleid,
u.f_Isdelete
from (select * from Sys_User where f_Tenantid=284
and f_State = 1
and f_isdelete = 0) u left outer join
(select * from Sys_Roleuser where F_ROLEID = '722') r
on u.f_Userid = r.f_Userid
--and u.f_Tenantid = 284
--and u.f_State = 1
--and u.f_isdelete = 0
order by u.f_Tenantid, u.f_Userid;