【037】U9手动更新菜单发布包
登录
侧边栏壁纸
博主昵称
秋驰雪隙

  • 累计撰写 60 篇文章
  • 累计收到 2 条评论
用友U9

【037】U9手动更新菜单发布包

秋驰雪隙
秋驰雪隙
2025-04-08 / 0 评论 / 10 阅读 / 正在检测是否收录...

使用存储过程生成要设置的SQL语句

此处以小辣椒为例子，新增一个【BOM子项替代关系】【BOM子项替代列表】的页面，然后还有对应的BE跟UI以及LIstUI的DLL，以及对应的菜单
if object_id('tempdb..#insertSQL') is not null  
    drop table #insertSQL
create table #insertSQL(seq int IDENTITY(1,1),sqlstr nvarchar(max))

insert into #insertSQL(sqlstr) values('-- 页面[BOM子项替代关系]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page]', 'Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page_Trl]', 'ID in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn_Trl]', 'ID IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part_Trl]', 'ID in (select ID from UBF_Assemble_Part where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ColumnPart]', 'PageColumn IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ParamBinding]', 'PageParameter IN (select ID from UBF_Assemble_PageParameter where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageParameter]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PartRelation]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';

insert into #insertSQL(sqlstr) values('-- 页面[BOM子项替代关系列表]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page]', 'Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page_Trl]', 'ID in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn_Trl]', 'ID IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part_Trl]', 'ID in (select ID from UBF_Assemble_Part where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ColumnPart]', 'PageColumn IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ParamBinding]', 'PageParameter IN (select ID from UBF_Assemble_PageParameter where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageParameter]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PartRelation]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';

insert into #insertSQL(sqlstr) values('-- DLL');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubUI.WebPart.dll''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubQryListUI.WebPart.dll''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubBE.dll''';

insert into #insertSQL(sqlstr) values('-- 菜单[客开]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu]', 'Code = ''a6dcc019-7877-4c5a-9a2f-e50653104337''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu_Trl]', 'ID IN (select ID from UBF_Assemble_Menu where Code = ''a6dcc019-7877-4c5a-9a2f-e50653104337'')';

insert into #insertSQL(sqlstr) values('-- 菜单[BOM子项替代关系]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu]', 'Code = ''25f8fca6-3ce5-425d-9956-1d108ebc84f3''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu_Trl]', 'ID IN (select ID from UBF_Assemble_Menu where Code = ''25f8fca6-3ce5-425d-9956-1d108ebc84f3'')';

select * from #insertSQL order by seq;

生成Insert语句的存储过程

/****** Object:  StoredProcedure [dbo].[proc_insert]    Script Date: 2023/9/21 13:04:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        qxq
-- Create date: 2023/9/21
-- Description:    生成单表的Insert语句
-- =============================================
CREATE OR ALTER proc [dbo].[Proc_Insert] (
    @tablename varchar(256), -- 表名
    @where varchar(max) -- 条件
)
as
begin
    set nocount on
    declare @sqlstr varchar(MAX)
    declare @sqlstr1 varchar(MAX)
    declare @sqlstr2 varchar(MAX)
    select @sqlstr='select ''INSERT '+@tablename
    select @sqlstr1=''
    select @sqlstr2=' ('
    select @sqlstr1= ' VALUES ( ''+'
    select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case 
    -- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
    when a.xtype =36 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
    when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
    when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
    when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
    when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
    when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
    when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
    when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
    when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
    when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
    -- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
    when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
    else '''NULL'''
    end as col,a.colid,a.name
    from syscolumns a
    where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 -- and a.xtype <>36
    AND COLUMNPROPERTY(a.id, a.name, 'IsIdentity') <> 1
    )t order by colid
    select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename + ' where '+@where
    -- print @sqlstr
    
    exec(@sqlstr)

    set nocount off
end
GO

执行SQL

在执行完成的结果集，选中sqlstr列，Ctrl+C复制到新的查询窗口执行即可
其中的主键ID可使用系统获取ID的存储过程替换

执行结果如下，选中sqlstr列，Ctrl+C复制出来即可
bd9f7273207549549b8c4951ae32aeb5.png

U9获取ID

declare    @SNIndex bigint
if object_id('InnerAllocSerials') is null
    exec    [dbo].[AllocSerials]
            @AllocCount = 20000,
            @StartSN = @SNIndex output

else
    exec    [dbo].[InnerAllocSerials]
            @AllocCount = 20000,
            @StartSN = @SNIndex output

手动卸载菜单发布

以小辣椒刚刚新增的为例子

删除页面

--删除页面[BOM子项替代关系]
DELETE FROM UBF_Assemble_PartRelation
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
DELETE FROM UBF_Assemble_PageParameter
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PageParameterID as
    (select ID from UBF_Assemble_PageParameter where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ParamBinding
WHERE PageParameter in (select ID from PageParameterID)
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PagePartID as
    (select ID from UBF_Assemble_Part where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_Part_Trl WHERE ID in (select ID from PagePartID)
;
DELETE FROM UBF_Assemble_Part
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PageColumnID as
    (select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ColumnPart
WHERE PageColumn in (select ID from PageColumnID)
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
    PageColumnID as
    (select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_PageColumn_Trl
WHERE ID in (select ID from PageColumnID)
;
DELETE FROM UBF_Assemble_PageColumn
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
DELETE FROM UBF_Assemble_Page_Trl
WHERE ID in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
DELETE FROM UBF_Assemble_Page
WHERE Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'
;

--删除页面[BOM子项替代关系列表]
DELETE FROM UBF_Assemble_PartRelation
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
DELETE FROM UBF_Assemble_PageParameter
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
    PageParameterID as
    (select ID from UBF_Assemble_PageParameter where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ParamBinding
WHERE PageParameter in (select ID from PageParameterID)
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
    PagePartID as
    (select ID from UBF_Assemble_Part where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_Part_Trl WHERE ID in (select ID from PagePartID)
;
DELETE FROM UBF_Assemble_Part
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
    PageColumnID as
    (select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ColumnPart
WHERE PageColumn in (select ID from PageColumnID)
;
with
    PageID as
    (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
    PageColumnID as
    (select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_PageColumn_Trl
WHERE ID in (select ID from PageColumnID)
;
DELETE FROM UBF_Assemble_PageColumn
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
DELETE FROM UBF_Assemble_Page_Trl
WHERE ID in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
DELETE FROM UBF_Assemble_Page
WHERE Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'
;

删除DLL

DELETE FROM UBF_Assemble_AppComponents
      WHERE Component = 'UFIDA.U9.SH.PubUI.WebPart.dll'
;
DELETE FROM UBF_Assemble_AppComponents
      WHERE Component = 'UFIDA.U9.SH.PubQryListUI.WebPart.dll'
;
DELETE FROM UBF_Assemble_AppComponents
      WHERE Component = 'UFIDA.U9.SH.PubBE.dll'
;

删除菜单

-- 删除菜单[客开]
delete from UBF_Assemble_Menu_Trl 
where ID in (select ID from UBF_Assemble_Menu where Code = 'a6dcc019-7877-4c5a-9a2f-e50653104337')
;
delete from UBF_Assemble_Menu
where Code = 'a6dcc019-7877-4c5a-9a2f-e50653104337'
;

-- 删除菜单[BOM子项替代关系]
delete from UBF_Assemble_Menu_Trl 
where ID in (select ID from UBF_Assemble_Menu where Code = '25f8fca6-3ce5-425d-9956-1d108ebc84f3')
;
delete from UBF_Assemble_Menu
where Code = '25f8fca6-3ce5-425d-9956-1d108ebc84f3'
;
0
暂无标签
版权属于： 秋驰雪隙
本文链接： https://blog.pee.ee/archives/u9037.html
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权

评论 (0)

取消