从SQLSERVER上搞的别人写的一个查询,修改后代码如下:DECLARE v_PrjId nvarchar2(200);
v_Version int;
BEGIN
SELECT PrjId into v_PrjId FROM Bud_Task WHERE TaskId = 'C685D7B9-62DF-4BB5-B18D-0B053DF1C344';
SELECT Version INTO v_Version FROM Bud_Task WHERE TaskId = 'C685D7B9-62DF-4BB5-B18D-0B053DF1C344';
WITH cteTask AS
(
SELECT t.TaskId, t.OrderNumber, t.ParentId,
SUM(tr.ResourceQuantity * tr.ResourcePrice) AS Total --本节点资源配置金额
FROM Bud_Task t
LEFT JOIN Bud_TaskResource tr ON t.TaskId = tr.TaskID
WHERE t.PrjId = v_PrjId AND t.Version = v_Version
GROUP BY t.TaskId, t.OrderNumber, t.ParentId
), cteTask2 AS
(
SELECT t2.TaskId,t2.ParentId, t2.OrderNumber, t2.TaskCode,t2.TaskName, t2.Unit, t2.Quantity,
t2.StartDate, t2.EndDate,t2.Note,t2.Modified,
(SELECT SUM(cteTask.Total) FROM cteTask
WHERE cteTask.OrderNumber LIKE t2.OrderNumber || '%'
) AS Total , --小计(已对子节点进行汇总)
(SELECT COUNT(1) FROM Bud_Task t3
WHERE t3.ParentId = t2.TaskId
) AS SubCount -- 子节点的数量
FROM Bud_Task t2
JOIN cteTask ON t2.TaskId = cteTask.TaskId
WHERE t2.Version =v_Version
), cteType AS
(
SELECT ROW_NUMBER() OVER(ORDER BY cl.CodeId) AS CodeNo, cl.CodeId, cl.CodeName
FROM XPM_Basic_CodeList cl
INNER JOIN XPM_Basic_CodeType ct ON cl.TypeId = ct.TypeId
WHERE ct.SignCode = 'taskType' AND ct.IsValid = '1' AND cl.IsValid = '1'
)
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY cteTask2.OrderNumber) AS No, cteTask2.TaskId, cteTask2.ParentId,
cteTask2.OrderNumber, cteTask2.TaskCode,cteTask2.TaskName, cteTask2.Unit,
cteTask2.Quantity, cteTask2.StartDate, cteTask2.EndDate, cteTask2.Note,cteTask2.Modified,
NVL(cteTask2.Total, 0) AS Total, cteTask2.SubCount,
NVL(cteTask2.Total / NULLIF(cteTask2.Quantity, 0), 0) AS UnitPrice,
cteType.CodeNo,cteType.CodeName AS TypeName
FROM cteTask2
LEFT JOIN cteType ON NVL(cteTask2.OrderNumber,0) / 3 = cteType.CodeNo
) a
WHERE ParentId = 'C685D7B9-62DF-4BB5-B18D-0B053DF1C344'
ORDER BY No;
END;
测试运行后提示错误:
ORA-06550:第6行,第1列;
PLS-00428:在此SELECT语句中缺少INTO子句
求大神给我说说为什么错,非常感谢!Oracle
v_Version int;
BEGIN
SELECT PrjId into v_PrjId FROM Bud_Task WHERE TaskId = 'C685D7B9-62DF-4BB5-B18D-0B053DF1C344';
SELECT Version INTO v_Version FROM Bud_Task WHERE TaskId = 'C685D7B9-62DF-4BB5-B18D-0B053DF1C344';
WITH cteTask AS
(
SELECT t.TaskId, t.OrderNumber, t.ParentId,
SUM(tr.ResourceQuantity * tr.ResourcePrice) AS Total --本节点资源配置金额
FROM Bud_Task t
LEFT JOIN Bud_TaskResource tr ON t.TaskId = tr.TaskID
WHERE t.PrjId = v_PrjId AND t.Version = v_Version
GROUP BY t.TaskId, t.OrderNumber, t.ParentId
), cteTask2 AS
(
SELECT t2.TaskId,t2.ParentId, t2.OrderNumber, t2.TaskCode,t2.TaskName, t2.Unit, t2.Quantity,
t2.StartDate, t2.EndDate,t2.Note,t2.Modified,
(SELECT SUM(cteTask.Total) FROM cteTask
WHERE cteTask.OrderNumber LIKE t2.OrderNumber || '%'
) AS Total , --小计(已对子节点进行汇总)
(SELECT COUNT(1) FROM Bud_Task t3
WHERE t3.ParentId = t2.TaskId
) AS SubCount -- 子节点的数量
FROM Bud_Task t2
JOIN cteTask ON t2.TaskId = cteTask.TaskId
WHERE t2.Version =v_Version
), cteType AS
(
SELECT ROW_NUMBER() OVER(ORDER BY cl.CodeId) AS CodeNo, cl.CodeId, cl.CodeName
FROM XPM_Basic_CodeList cl
INNER JOIN XPM_Basic_CodeType ct ON cl.TypeId = ct.TypeId
WHERE ct.SignCode = 'taskType' AND ct.IsValid = '1' AND cl.IsValid = '1'
)
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY cteTask2.OrderNumber) AS No, cteTask2.TaskId, cteTask2.ParentId,
cteTask2.OrderNumber, cteTask2.TaskCode,cteTask2.TaskName, cteTask2.Unit,
cteTask2.Quantity, cteTask2.StartDate, cteTask2.EndDate, cteTask2.Note,cteTask2.Modified,
NVL(cteTask2.Total, 0) AS Total, cteTask2.SubCount,
NVL(cteTask2.Total / NULLIF(cteTask2.Quantity, 0), 0) AS UnitPrice,
cteType.CodeNo,cteType.CodeName AS TypeName
FROM cteTask2
LEFT JOIN cteType ON NVL(cteTask2.OrderNumber,0) / 3 = cteType.CodeNo
) a
WHERE ParentId = 'C685D7B9-62DF-4BB5-B18D-0B053DF1C344'
ORDER BY No;
END;
测试运行后提示错误:
ORA-06550:第6行,第1列;
PLS-00428:在此SELECT语句中缺少INTO子句
求大神给我说说为什么错,非常感谢!Oracle
,使用的时候应与INTO结合使用。要是想返回结果集的话,可以通过游标(REFCURSOR)或集合来说
实现。