我可以运行此过程,
CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_MST_ERC_UPD
(
p_HEAD_MARK IN MST_ERC_UPD.HEAD_MARK%TYPE,
p_PROJECT_NAME IN MST_ERC_UPD.PROJECT_NAME%TYPE,
p_COLI_NUM IN MST_ERC_UPD.COLI_NUM%TYPE,
p_ONSITE_UPD_QTY IN MST_ERC_UPD.ONSITE_UPD_QTY%TYPE,
p_PREP_UPD_QTY IN MST_ERC_UPD.PREP_UPD_QTY%TYPE,
p_ERECT_UPD_QTY IN MST_ERC_UPD.ERECT_UPD_QTY%TYPE,
p_QC_UPD_QTY IN MST_ERC_UPD.QC_UPD_QTY%TYPE
)
IS
BEGIN
UPDATE MST_ERC_UPD
SET
ONSITE_UPD_QTY = p_ONSITE_UPD_QTY,
PREP_UPD_QTY = p_PREP_UPD_QTY,
ERECT_UPD_QTY = p_ERECT_UPD_QTY,
QC_UPD_QTY = p_QC_UPD_QTY
WHERE
HEAD_MARK = p_HEAD_MARK AND
PROJECT_NAME = p_PROJECT_NAME AND
COLI_NUM = p_COLI_NUM;
COMMIT;
END SP_MST_ERC_UPD;
这只是更新。我试图实现的是,当具有 3 个变量HEAD_MARK、PROJECT_NAME COLI_NUM恰好存在于表中时,它应该更新值,否则它应该插入一个带有提供值的新行。如何添加 IF、ELSE 以便根据这两个条件执行操作?
多谢
Oracle 支持语句merge
。您可以使用 merge
来避免多个 insert
、 update
和 delete
语句。
请参考以下网址:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606