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

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

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

 

Oracle	PostgreSql	说明
VARCHAR2	varchar
DATE	timestamp
SYSDATE	localtimestamp,now()
clob	text
blob	bytea
number	smallint/integer/bigint/numeric/real/double precision
不支持boolean，可通过0/1代替	支持 boolean
'==null	' != null
a'||null == 'a'	a'||null == null
trunc(时间)	date_trunc(text,time/timestamp/timestamptz)
to_char	to_char(待转换值，转换格式)
to_number	to_number(待转换值，转换格式)
to_date	to_date(待转换值，转换格式)
decode	case when或if判断
nvl	coalesce()
外连接(+)	left(right) join
goto语句	不支持
%FOUND	found	游标属性
%NOTFOUND	not found
%ISOPEN	不支持
%ROWCOUNT	不支持
	cursor名是全局的；所以用隐式声明定义cursor，或者保证所有的程序中cursor名唯一
COMMIT,ROLLBACK;SAVEPOINT	函数中不支持
DBMS_OUTPUT，DBMS_SQL，UTIL_FILE，UTIL_MAIL包	不支持
dual	不支持
不支持	::类型转换
	子查询必须指定别名
分页rownum	分页limit(limit必须用于 order by 之后)
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语法及使用差异
select seq_tab_name.nextval from dual;	select next_val(seq_tab_name);
wm_concat	arrary_agg （）、 string_agg( )、 xmlagg() 分别来处理数组，字符串和xml文档

下面是在处理存储过程移植过程中记录下的对比情况：

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 in循环cur不需要声明			pgsql 中需要进行声明才行，可以声明是游标或者记录集
to_date			带时分秒用这个to_timestamp
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这些字段不能加括号。
执行动态SQL赋值游标: open unbound_refcursor for mysql			open unbound_refcursor for execute mysql			重点关注，在pgsql中使用动态SQL拼接时，特别需要注意变量是否为null，如果是null需要自行转化一下，因为在pgsql中null与字符串拼接的结果是null。
RAISE_APPLICATION_ERROR(-20001, '该单据已使用，不能删除');			RAISE EXCEPTION '该单据已使用，不能删除' USING ERRCODE = -20001;
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需要另外创建，然后在函数中直接使用
创建自增系列ID方式：testseq_id_seq.nextval			nextval('testseq_id_seq')
open一个相同游标可以多次			只能open一次相同的游标
trunc函数传入数字是可以进行取整			trunc函数可以进行取整，但不支持时间操作，需要用date_trunc函数(需要传转换格式)
在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
打印动态SQL等语句dbms_output.put_line(sqlText)			pg中是raise info '%',sqlText;
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;
substr下标从0开始			substr下标从1开始
substring(index1,index2)			substring(字符串 from 开始索引 for 截取长度)
sysdate>date1+datenum			now()>(date1+(datenum || 'day')::interval)
to_date可以转化时分秒			to_date只能转化到年月日，需要时分秒使用to_timestamp
oracle中不同类型进行比较，赋值，where条件值，函数参数等等，会自动转化类型			pgsql中类型不会进行自动转化，所以要严格进行类型相同比较，赋值，函数参数等等
execute immediate sqlText			execute sqlText
GROUPING_ID分组统计函数			不支持GROUPING_ID函数，可以使用grouping函数替换
可以直接用rownum			pgsql中需要使用函数row_number() over() as rownum
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;
oracle支持这种天与时没有空格转化成时间to_date('2022-03-0412:11:41','YYYY-MM-DD hh24:mi:ss')			pgsql不支持这种天与时没有空格的to_timestamp('2022-03-0412:11:41','YYYY-MM-DD hh24:mi:ss')