Oracle的包(package)中的代码片段如下:
---处理菜单
tempsql := 'insert into T_CALMENUFILTERCONFIG(ACCTSYSTYPE,NOTSHOWSUBMENU,ISSUE,USERID,HSAGENCY) ';
tempsql :=tempsql||' select g.acctsystype,'''',1,t.userid,g.hsagency from t_cadataright t,t_acalmenurole g where t.roleid = g.roleid';
tempsql :=tempsql||' and t.roleid = '||proleid||' and g.acctsystype='||pacctsystype||' and exists (select 1 from t_causer us where us.type < 1 and t.userid = us.userid) ';
if pproxy = 1 then -- 代理
tempsql :=tempsql||' and g.hsagency='||phsagency||' and g.proxy=1';
execute immediate tempsql;
-- 处理隐藏菜单
tempsql :='update T_CALMENUFILTERCONFIG c set c.notshowsubmenu = (';
tempsql := tempsql||' select to_char(wm_concat(g.menuid)) from t_acalmenurole g where g.acctsystype ='||pacctsystype||' and g.hsagency='||phsagency||' and g.proxy=1 and g.roleid='||proleid||')';
tempsql := tempsql||' where c.acctsystype='||pacctsystype||' and c.hsagency='||phsagency||' and c.userid in (';
tempsql := tempsql||' select d.userid from t_cadataright d where d.roleid = '||proleid||')';
execute immediate tempsql;
else
tempsql :=tempsql||' and nvl(g.hsagency,0)=0 and nvl(g.proxy,0)=0';
execute immediate tempsql;
-- 处理隐藏菜单行转列到用户
tempsql :='update T_CALMENUFILTERCONFIG c set c.notshowsubmenu = (';
tempsql := tempsql||' select to_char(wm_concat(g.menuid)) from t_acalmenurole g where g.acctsystype ='||pacctsystype||' and nvl(g.hsagency,0)=0 and nvl(g.proxy,0)=0 and g.roleid='||proleid||')';
tempsql := tempsql||' where c.acctsystype='||pacctsystype||' and nvl(c.hsagency,0)=0 and c.userid in (';
tempsql := tempsql||' select d.userid from t_cadataright d where d.roleid = '||proleid||')';
execute immediate tempsql;
移植到PostgreSQL上的代码如下:
tempsql := 'insert into T_CALMENUFILTERCONFIG(ACCTSYSTYPE,NOTSHOWSUBMENU,ISSUE,USERID,HSAGENCY) ';
tempsql := tempsql || ' select g.acctsystype,'''',1,t.userid,g.hsagency from t_cadataright t,t_acalmenurole g where t.roleid = g.roleid';
tempsql := tempsql || ' and t.roleid = ' || proleid || ' and g.acctsystype=' || pacctsystype || ' and exists (select 1 from t_causer us where us.type < 1 and t.userid = us.userid) ';
if pproxy = 1 then
tempsql := tempsql || ' and g.hsagency=' || phsagency || ' and g.proxy=1';
execute immediate tempsql;
tempsql := 'update T_CALMENUFILTERCONFIG c set c.notshowsubmenu = (';
tempsql := tempsql || ' select to_char(string_agg(menuid,'','')) from t_acalmenurole g where g.acctsystype =' || pacctsystype || ' and g.hsagency=' || phsagency || ' and g.proxy=1 and g.roleid=' || proleid || ')';
tempsql := tempsql || ' where c.acctsystype=' || pacctsystype || ' and c.hsagency=' || phsagency || ' and c.userid in (';
tempsql := tempsql || ' select d.userid from t_cadataright d where d.roleid = ' || proleid || ')';
--raise notice 'tempsql is: %',tempsql;
execute immediate tempsql;
else
tempsql := tempsql || ' and nvl(g.hsagency,0)=0 and nvl(g.proxy,0)=0';
execute immediate tempsql;
tempsql := 'update T_CALMENUFILTERCONFIG c set c.notshowsubmenu = (';
tempsql := tempsql || ' select to_char(string_agg(g.menuid,'','')) from t_acalmenurole g where g.acctsystype =' || pacctsystype || ' and nvl(g.hsagency,0)=0 and nvl(g.proxy,0)=0 and g.roleid=' || proleid || ')';
tempsql := tempsql || ' where c.acctsystype=' || pacctsystype || ' and nvl(c.hsagency,0)=0 and c.userid in (';
tempsql := tempsql || ' select d.userid from t_cadataright d where d.roleid = ' || proleid || ')';
execute immediate tempsql;
注意:如果
string_agg(g.menuid,',')
里面的逗号使用单引号虽然编译能通过,可是在调用存储过程时报如下
错误:
ERROR: query "SELECT tempsql || ' select to_char(string_agg(menuid,',')) from t_acalmenurole g where g.acctsystype =' || pacctsystype || ' and g.hsagency=' || phsagency || ' and g.proxy=1 and g.roleid=' || proleid || ')'" returned 2 columns
CONTEXT: edb-spl function pkg_afsp_roletouser.handelrole(integer,integer,integer,integer) line 32 at assignment
line 34 of package body
SQL state: 42601
后来改为2个单引号即可(原因是这里的单引号需要转义,sql中的单引号'即为转义,相当于java中的反斜杠\),如上图所示。