在 plsql 块中动态传递Contract_id

Question

在这里，我正在更新一个特定的contract_id。 我需要一次更新所有contract_ids，
即在 plsql 块中动态传递Contract_id。

DECLARE
VAR1   NUMBER := 0;
CURSOR C1 (P_CONTRACT_ID IN NUMBER)
IS
SELECT GO.CONTRACT_ID,
GO.COLUMN46,
GO.COLUMN1,
GO.ORG_ID
FROM XYZ GO
WHERE GO.CONTRACT_ID = P_CONTRACT_ID AND GO.COLUMN46 IS NULL;
BEGIN
BEGIN
SELECT NVL (MAX (TO_NUMBER (COLUMN46)), 0)
INTO VAR1
FROM XYZ
WHERE CONTRACT_ID = 525215;
EXCEPTION
WHEN OTHERS
THEN
VAR1 := 0;
END;
FOR F1 IN C1 (525215)
LOOP
VAR1 := VAR1 + 1;
BEGIN
UPDATE XYZ
SET COLUMN46 = VAR1
WHERE     CONTRACT_ID = F1.CONTRACT_ID
AND COLUMN46 IS NULL
AND COLUMN1 = F1.COLUMN1;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

Answer 1

更改光标并删除位置条件：

CURSOR C1
IS
SELECT GO.CONTRACT_ID,
GO.COLUMN46,
GO.COLUMN1,
GO.ORG_ID
FROM XYZ GO
WHERE GO.COLUMN46 IS NULL;

您可能也想在 id-select 时将其删除：

SELECT NVL (MAX (TO_NUMBER (COLUMN46)), 0)
INTO VAR1
FROM XYZ
--WHERE CONTRACT_ID = 525215
;

您还应该阅读有关生成 ID 的序列和触发器的内容;-( 如何在甲骨文上使用AUTO_INCREMENT创建id？