ALTER PROC [dbo].[proc_pingbiao_pw_HZ]
@biaoduanguid VARCHAR(50) ,
@PFDTYPE VARCHAR(50)
AS
DECLARE @sql VARCHAR(3000)
DECLARE @Pw_Name VARCHAR(500)
DECLARE @Pw_Guid VARCHAR(500)
DECLARE @Pw_Type1 VARCHAR(500)
DECLARE @Pw_Type2 VARCHAR(500)
DECLARE @Pw_Type3 VARCHAR(500)
declare @IsNeedSecondEnvelope nvarchar(500)
declare @PBBFMark nvarchar(500)
declare @Field varchar(300)
declare @Xh varchar(50)
declare @FeiBiaoJD int
declare @PwType VARCHAR(500)
if(@PFDTYPE='1')
set @Field='Econ_PingFenMethod'
else if(@PFDTYPE='2')
set @Field='Tech_PingFenMethod'
else if(@PFDTYPE='3')
set @Field='ZongHePFMethod'
else if(@PFDTYPE='9')
set @Field='QT_PingFenMethod' SET @Pw_Type1 = NULL
SET @Pw_Type2 = NULL
SET @Pw_Type3 = NULL IF ( @PFDTYPE = '1' )
BEGIN
SET @Pw_Type1 = '1'
SET @Pw_Type3 = '3'
End
ELSE
IF ( @PFDTYPE = '2' )
BEGIN
SET @Pw_Type2 = '2'
SET @Pw_Type3 = '3'
End
ELSE
--IF ( @PFDTYPE = '3' )
BEGIN
SET @Pw_Type1 = '1'
SET @Pw_Type2 = '2'
SET @Pw_Type3 = '3'
END PRINT @Pw_Type1
PRINT @Pw_Type2
PRINT @Pw_Type3--判断是否是双信封
SELECT @PBBFMark=PingBiaoBF FROM PingBiao_BiaoDuan where biaoduanguid=@biaoduanguid
SELECT @IsNeedSecondEnvelope=IsNeedSecondEnvelope FROM PingBiao_Eval_PingBiaoBF WHERE MARK=@PBBFMark
CREATE TABLE #tmp
(
danweiguid VARCHAR(50) ,
danweiname VARCHAR(500) ,
hz VARCHAR(500)
)
--
--insert into #tmp
--values(1,'1')
----Edit By Brb 2011-09-09
--if (@PFDTYPE='11') --初步评审
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1' order by Row_ID
-- END
-- else if (@PFDTYPE='1' or @PFDTYPE='3' or @PFDTYPE='9') --商务标评审
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1' and
-- not (IsFeiBiao ='1' and FeiBiaoJD='3') order by Row_ID
-- END
-- else if (@PFDTYPE='2') --技术标评审
-- BEGIN
-- --判断是否是双信封
-- IF(@IsNeedSecondEnvelope='1;')
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsTechSucceed='1' order by Row_ID
-- END
-- ELSE
-- begin
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1' and
-- not (IsFeiBiao ='1' and FeiBiaoJD='3') order by Row_ID
-- end
-- END
-- ELSE
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1'
-- AND ISNULL(IsFeiBiao, '0') = '0'
-- END
--Edit By BRB 2012-08-07 修改成根据废标节点配置动态取单位
if(@PFDTYPE='1')
begin
select @Xh=xh from PingBiao_BuZhouBack where pbbf=@PBBFMark and charindex('1',pfdvalue)!=0 and charindex('11',pfdvalue)=0
end
else
begin
select @Xh=xh from PingBiao_BuZhouBack where pbbf=@PBBFMark and charindex(''+ @PFDTYPE +'',pfdvalue)!=0
end
@biaoduanguid VARCHAR(50) ,
@PFDTYPE VARCHAR(50)
AS
DECLARE @sql VARCHAR(3000)
DECLARE @Pw_Name VARCHAR(500)
DECLARE @Pw_Guid VARCHAR(500)
DECLARE @Pw_Type1 VARCHAR(500)
DECLARE @Pw_Type2 VARCHAR(500)
DECLARE @Pw_Type3 VARCHAR(500)
declare @IsNeedSecondEnvelope nvarchar(500)
declare @PBBFMark nvarchar(500)
declare @Field varchar(300)
declare @Xh varchar(50)
declare @FeiBiaoJD int
declare @PwType VARCHAR(500)
if(@PFDTYPE='1')
set @Field='Econ_PingFenMethod'
else if(@PFDTYPE='2')
set @Field='Tech_PingFenMethod'
else if(@PFDTYPE='3')
set @Field='ZongHePFMethod'
else if(@PFDTYPE='9')
set @Field='QT_PingFenMethod' SET @Pw_Type1 = NULL
SET @Pw_Type2 = NULL
SET @Pw_Type3 = NULL IF ( @PFDTYPE = '1' )
BEGIN
SET @Pw_Type1 = '1'
SET @Pw_Type3 = '3'
End
ELSE
IF ( @PFDTYPE = '2' )
BEGIN
SET @Pw_Type2 = '2'
SET @Pw_Type3 = '3'
End
ELSE
--IF ( @PFDTYPE = '3' )
BEGIN
SET @Pw_Type1 = '1'
SET @Pw_Type2 = '2'
SET @Pw_Type3 = '3'
END PRINT @Pw_Type1
PRINT @Pw_Type2
PRINT @Pw_Type3--判断是否是双信封
SELECT @PBBFMark=PingBiaoBF FROM PingBiao_BiaoDuan where biaoduanguid=@biaoduanguid
SELECT @IsNeedSecondEnvelope=IsNeedSecondEnvelope FROM PingBiao_Eval_PingBiaoBF WHERE MARK=@PBBFMark
CREATE TABLE #tmp
(
danweiguid VARCHAR(50) ,
danweiname VARCHAR(500) ,
hz VARCHAR(500)
)
--
--insert into #tmp
--values(1,'1')
----Edit By Brb 2011-09-09
--if (@PFDTYPE='11') --初步评审
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1' order by Row_ID
-- END
-- else if (@PFDTYPE='1' or @PFDTYPE='3' or @PFDTYPE='9') --商务标评审
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1' and
-- not (IsFeiBiao ='1' and FeiBiaoJD='3') order by Row_ID
-- END
-- else if (@PFDTYPE='2') --技术标评审
-- BEGIN
-- --判断是否是双信封
-- IF(@IsNeedSecondEnvelope='1;')
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsTechSucceed='1' order by Row_ID
-- END
-- ELSE
-- begin
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1' and
-- not (IsFeiBiao ='1' and FeiBiaoJD='3') order by Row_ID
-- end
-- END
-- ELSE
-- BEGIN
-- INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- and IsCanYuPb='1'
-- AND ISNULL(IsFeiBiao, '0') = '0'
-- END
--Edit By BRB 2012-08-07 修改成根据废标节点配置动态取单位
if(@PFDTYPE='1')
begin
select @Xh=xh from PingBiao_BuZhouBack where pbbf=@PBBFMark and charindex('1',pfdvalue)!=0 and charindex('11',pfdvalue)=0
end
else
begin
select @Xh=xh from PingBiao_BuZhouBack where pbbf=@PBBFMark and charindex(''+ @PFDTYPE +'',pfdvalue)!=0
end
begin
set @FeiBiaoJD=cast(@Xh as int)-1
INSERT INTO #tmp
SELECT danweiguid ,
danweiname ,
''
FROM PingBiao_KaiBiaoTouBiao
WHERE biaoduanguid = @biaoduanguid
and IsCanYuPb='1' and (isnull(FeiBiaoJD,0)=0 or FeiBiaoJD >@FeiBiaoJD ) order by Row_ID
end
else
begin
--INSERT INTO #tmp
-- SELECT danweiguid ,
-- danweiname ,
-- ''
-- FROM PingBiao_KaiBiaoTouBiao
-- WHERE biaoduanguid = @biaoduanguid
-- AND IsCanYuPb='1' order by Row_ID
if (@PFDTYPE='11') --初步评审
BEGIN
INSERT INTO #tmp
SELECT danweiguid ,
danweiname ,
''
FROM PingBiao_KaiBiaoTouBiao
WHERE biaoduanguid = @biaoduanguid
and IsCanYuPb='1' order by Row_ID
END
else if (@PFDTYPE='1' or @PFDTYPE='3' or @PFDTYPE='9') --商务标评审
BEGIN
INSERT INTO #tmp
SELECT danweiguid ,
danweiname ,
''
FROM PingBiao_KaiBiaoTouBiao
WHERE biaoduanguid = @biaoduanguid
and IsCanYuPb='1' and
not (IsFeiBiao ='1' and FeiBiaoJD='3') order by Row_ID
END
else if (@PFDTYPE='2') --技术标评审
BEGIN
--判断是否是双信封
IF(@IsNeedSecondEnvelope='1;')
BEGIN
INSERT INTO #tmp
SELECT danweiguid ,
danweiname ,
''
FROM PingBiao_KaiBiaoTouBiao
WHERE biaoduanguid = @biaoduanguid
and IsTechSucceed='1' order by Row_ID
END
ELSE
begin
INSERT INTO #tmp
SELECT danweiguid ,
danweiname ,
''
FROM PingBiao_KaiBiaoTouBiao
WHERE biaoduanguid = @biaoduanguid
and IsCanYuPb='1' and
not (IsFeiBiao ='1' and FeiBiaoJD='3') order by Row_ID
end
END
ELSE
BEGIN
INSERT INTO #tmp
SELECT danweiguid ,
danweiname ,
''
FROM PingBiao_KaiBiaoTouBiao
WHERE biaoduanguid = @biaoduanguid
and IsCanYuPb='1'
AND ISNULL(IsFeiBiao, '0') = '0'
END
end
DECLARE cr_cursor CURSOR --1.定义游标
FOR
SELECT Pw_Name ,
Pw_Guid
FROM PingBiao_PW_LoginInfo
WHERE BiaoDuanGuid = @biaoduanguid
AND ( Pw_Type = @Pw_Type1
OR Pw_Type = @Pw_Type2
OR Pw_Type = @Pw_Type3
)
FETCH FROM cr_cursor INTO @Pw_Name, @Pw_Guid --3.提取游标 WHILE @@fetch_status = 0
BEGIN
SET @sql = 'alter table #tmp add [' + @Pw_Name + '] varchar(50)'
EXEC(@sql) IF ( @PFDTYPE = 5
OR @PFDTYPE = 15
OR @PFDTYPE = 11
OR @PFDTYPE = 4
OR @PFDTYPE = 7
OR @PFDTYPE = 6
OR @PFDTYPE = 8
)
BEGIN
--符合性
SET @sql = 'update #tmp set [' + @Pw_Name
+ ']=
(select case pingweipass when 0 then''不通过'' else ''通过'' end from PingBiao_TechLastMark where biaoduanguid='''
+ @biaoduanguid
+ ''' and Danweiguid=#tmp.Danweiguid and pingweiguid='''
+ @Pw_Guid + ''' and PingFenType=''' + @PFDTYPE
+ ''') '
EXEC(@sql)
END
ELSE
BEGIN
--直接打分,清单打分
SET @sql = 'update #tmp set [' + @Pw_Name
+ ']=
(select case '+@Field+' when ''1'' then cast(PingWeiMarkResult as varchar)
when ''2'' then case PingWeiMarkPass when ''1'' then ''通过'' else ''不通过'' end
else case PingWeiMarkPass when ''1'' then cast(PingWeiMarkResult as varchar)+''(通过)'' else cast(PingWeiMarkResult as varchar)+''(不通过)'' end end
from PingBiao_TechLastMark a inner join PingBiao_Parameter b on (a.BiaoDuanGuid=b.BiaoDuanGuid) where a.biaoduanguid='''
+ @biaoduanguid
+ ''' and a.Danweiguid=#tmp.Danweiguid and a.pingweiguid='''
+ @Pw_Guid + ''' and a.PingFenType=''' + @PFDTYPE
+ ''') '
EXEC(@sql)
END
--print @danweiguid --执行打印操作 FETCH NEXT FROM cr_cursor INTO @Pw_Name, @Pw_Guid --3.提取游标
END CLOSE cr_cursor --4.关闭游标 DEALLOCATE cr_cursor --5.释放游标--计算汇总
IF ( @PFDTYPE = 4 )
BEGIN
UPDATE #tmp
SET hz = ( SELECT TOP 1
( CASE isxspspass
WHEN 0 THEN '不通过'
ELSE '通过'
END )
FROM PingBiao_KaiBiaoTouBiao
WHERE BiaoDuanGuid = @BiaoDuanGuid
AND danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 5 )
BEGIN
UPDATE #tmp
SET hz = ( SELECT TOP 1
( CASE IsZiGeYSPass
WHEN 0 THEN '不通过'
ELSE '通过'
END )
FROM PingBiao_KaiBiaoTouBiao
WHERE BiaoDuanGuid = @BiaoDuanGuid
AND danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 6 )
BEGIN
UPDATE #tmp
SET hz = ( SELECT TOP 1
( CASE IsXYXPSPass
WHEN 0 THEN '不通过'
ELSE '通过'
END )
FROM PingBiao_KaiBiaoTouBiao
WHERE BiaoDuanGuid = @BiaoDuanGuid
AND danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 7 )
BEGIN
UPDATE #tmp
SET hz = ( SELECT TOP 1
( CASE IsTechPass
WHEN 0 THEN '不通过'
ELSE '通过'
END )
FROM PingBiao_KaiBiaoTouBiao
WHERE BiaoDuanGuid = @BiaoDuanGuid
AND danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 8 )
BEGIN
UPDATE #tmp
SET hz = ( SELECT TOP 1
( CASE IsYouXiaoXingPass
WHEN 0 THEN '不通过'
ELSE '通过'
END )
FROM PingBiao_KaiBiaoTouBiao
WHERE BiaoDuanGuid = @BiaoDuanGuid
AND danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 11 )
BEGIN
UPDATE #tmp
SET hz = ( SELECT TOP 1
( CASE IsChuBuPass
WHEN 0 THEN '不通过'
ELSE '通过'
END )
FROM PingBiao_KaiBiaoTouBiao
WHERE BiaoDuanGuid = @BiaoDuanGuid
AND danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 2 )--技术
BEGIN
UPDATE #tmp
SET hz = ( SELECT case b.Tech_PingFenMethod when '1' then cast(TechMark as varchar) when '2' then
case a.TechPass when '1' then '通过' else '不通过' end
else case a.TechPass when '1' then cast(TechMark as varchar)+'(通过)' else cast(TechMark as varchar)+'(不通过)' end end
FROM PingBiao_Eval_LastMark a inner join PingBiao_Parameter b on(a.BiaoDuanGuid=b.BiaoDuanGuid)
WHERE a.BiaoDuanGuid = @BiaoDuanGuid
AND a.danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 1 )--经济
BEGIN
UPDATE #tmp
SET hz = ( SELECT case b.Econ_PingFenMethod when '1' then cast(EconMark as varchar) when '2' then
case a.EconPass when '1' then '通过' else '不通过' end
else case a.EconPass when '1' then cast(EconMark as varchar)+'(通过)' else cast(EconMark as varchar)+'(不通过)' end end
FROM PingBiao_Eval_LastMark a inner join PingBiao_Parameter b on(a.BiaoDuanGuid=b.BiaoDuanGuid)
WHERE a.BiaoDuanGuid = @BiaoDuanGuid
AND a.danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 3 )--综合
BEGIN
UPDATE #tmp
SET hz = ( SELECT case ZongHePFMethod when '1' then cast(ZongHeMark as varchar) when '2' then
case ZongHePass when '1' then '通过' else '不通过' end
else case ZongHePass when '1' then cast(ZongHeMark as varchar)+'(通过)' else cast(ZongHeMark as varchar)+'(不通过)' end end
FROM PingBiao_Eval_LastMark a inner join PingBiao_Parameter b on(a.BiaoDuanGuid=b.BiaoDuanGuid)
WHERE a.BiaoDuanGuid = @BiaoDuanGuid
AND a.danweiguid = #tmp.danweiguid
)
END
ELSE
IF ( @PFDTYPE = 9 )--其他评分汇总
BEGIN
UPDATE #tmp
SET hz = ( SELECT case b.QT_PingFenMethod when '1' then cast(OtherMark as varchar) when '2' then
case a.OtherPass when '1' then '通过' else '不通过' end
else case a.OtherPass when '1' then cast(OtherMark as varchar)+'(通过)' else cast(OtherMark as varchar)+'(不通过)' end end
FROM PingBiao_Eval_LastMark a inner join PingBiao_Parameter b on(a.BiaoDuanGuid=b.BiaoDuanGuid)
WHERE a.BiaoDuanGuid = @BiaoDuanGuid
AND a.danweiguid = #tmp.danweiguid
)
END
SELECT *
FROM #tmp
DROP TABLE #tmp
Field ||
' when N''1'' then cast(PingWeiMarkResult as varchar(50))
when N''2'' then case PingWeiMarkPass when N''1'' then ''通过'' else ''不通过'' end
else case PingWeiMarkPass when N''1'' then cast(PingWeiMarkResult as varchar(50))||''(通过)'' else cast(PingWeiMarkResult as varchar(50))||''(不通过)'' end end
from PingBiao_TechLastMark a inner join PingBiao_Parameter b on (a.BiaoDuanGuid=b.BiaoDuanGuid) where a.biaoduanguid=''' ||
parBiaoDuanGuid ||
''' and a.Danweiguid=TEMP_PWMARK_HZ.Danweiguid and a.pingweiguid=''' ||
Pw_Guid || ''' and a.PingFenType=''' || PFDTYPE || ''') ';
dbms_output.put_line(sql1);
execute immediate sql1;调用的时候这里报错,ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ,未记录到sql1
把你想要执行的语句贴出来,跟输出的dbms_output.put_line(sql1); 对比一下看看哪有问题;sql1定义的长度尽量产点就好,Varchar2最大应该是4000,弄两三千不怕不够长;
varchar2, 在定义表时 最大长度是4000.
在plsql中, 最大长度是32767。