oracle - 在 Oracle 中的同一存储过程中更新查询
问题描述
我想UPDATE
在同一个存储过程中为同一个表编写逻辑。下面是我的SP。
PROCEDURE INSERT_PROJECT_MST
(
P_PROJECTNO IN NVARCHAR2,
P_CRNO IN NVARCHAR2,
P_APPNAME IN NVARCHAR2,
P_APPFUNCDESC IN NVARCHAR2,
P_AZUREFEATNO IN NVARCHAR2,
P_AZUREFEATDESC IN NVARCHAR2,
P_PROJMANAGER IN NVARCHAR2,
P_PROJLEAD IN NVARCHAR2,
P_REQBY IN NVARCHAR2,
P_BUSINESSCAT IN NVARCHAR2,
P_BUSINESSUSERCAT IN NVARCHAR2,
P_FEATUREID IN NVARCHAR2,
P_ISMGISRNO IN NVARCHAR2,
P_REPLICARR IN NVARCHAR2,
P_PRODRR IN NVARCHAR2,
P_USERSTORY IN NVARCHAR2,
P_ASSIGNEDBY IN NVARCHAR2,
P_ASSIGNEDTO IN NVARCHAR2,
P_CREATEDBY IN NVARCHAR2,
P_LASTUPDBY IN NVARCHAR2,
TBLDATA OUT NVARCHAR2
)
AS
V_PROJ_ID NUMBER:=0;
BEGIN
-- SELECT COUNT(MST_ID) INTO V_PROJ_ID FROM TBL_PROJECT_MST_INFO WHERE PROJECT_NO = P_PROJECTNO;
INSERT INTO TBL_PROJECT_MST_INFO
(
PROJECT_NO,
CR_NO,
APPLICATION_NAME,
APP_FUNC_DESC,
AZURE_FEATURE_NO,
AZURE_FEATURE_DESC,
PROJECT_MANAGER_NAME,
PROJECT_LEAD_NAME,
REQUESTED_BY,
BUSINESS_CATEGORY,
BUSINESS_USER_CATEGORY,
RATIONAL_FEATURE_ID,
ISMG_ISR_NO,
REPLICA_RR,
PROD_RR,
USER_STORY,
ASSIGNED_BY,
ASSIGNED_TO,
CREATED_BY,
LAST_UPDATED_BY,
CREATED_DATE,
LAST_UPDATED_DATE
)
VALUES
(
P_PROJECTNO,
P_CRNO,
P_APPNAME,
P_APPFUNCDESC,
P_AZUREFEATNO,
P_AZUREFEATDESC,
P_PROJMANAGER,
P_PROJLEAD,
P_REQBY,
P_BUSINESSCAT,
P_BUSINESSUSERCAT,
P_FEATUREID,
P_ISMGISRNO,
P_REPLICARR,
P_PRODRR,
P_USERSTORY,
UPPER(P_ASSIGNEDBY),
UPPER(P_ASSIGNEDTO),
P_CREATEDBY,
P_LASTUPDBY,
SYSDATE,
SYSDATE
)
RETURNING V_PROJ_ID INTO TBLDATA;
TBLDATA:='Record Saved Succesfully';
END INSERT_PROJECT_MST;
下面也是相同的表描述。
Name Null Type
---------------------- -------- --------------
ID NUMBER
MST_ID NOT NULL NUMBER
PROJECT_NO NVARCHAR2(100)
CR_NO NVARCHAR2(100)
APPLICATION_NAME NVARCHAR2(255)
APP_FUNC_DESC CLOB
AZURE_FEATURE_NO NVARCHAR2(155)
AZURE_FEATURE_DESC CLOB
PROJECT_MANAGER_NAME NVARCHAR2(100)
PROJECT_LEAD_NAME NVARCHAR2(100)
REQUESTED_BY NVARCHAR2(100)
BUSINESS_CATEGORY NVARCHAR2(100)
BUSINESS_USER_CATEGORY NVARCHAR2(100)
RATIONAL_FEATURE_ID NVARCHAR2(100)
ISMG_ISR_NO NVARCHAR2(100)
REPLICA_RR NVARCHAR2(100)
PROD_RR NVARCHAR2(100)
USER_STORY NVARCHAR2(500)
CREATED_BY NVARCHAR2(100)
CREATED_DATE DATE
ASSIGNED_TO NVARCHAR2(100)
ASSIGNED_BY NVARCHAR2(100)
IS_ACTIVE CHAR(1)
LAST_UPDATED_BY NVARCHAR2(100)
LAST_UPDATED_DATE DATE
COLUMN3 VARCHAR2(20)
COLUMN4 VARCHAR2(20)
COLUMN5 VARCHAR2(20)
注意 MST_ID
是我的唯一身份列,我将根据它更新表格。
解决方案
我不确定我明白你在问什么。目前,您的程序在表中插入一行;您插入的值是过程的 IN 参数。
您是否希望能够通过调用相同的过程并传递相同的参数来更新同一个表中的当前行?
如果是这样,MERGE可能是一个不错的选择。它也被称为upsert,因为它能够在同一个表中插入行(不存在)或更新行(如果存在)。
像这样的东西(简化;不想输入那么多列):
CREATE OR REPLACE PROCEDURE merge_project_mst (p_mst_id IN NUMBER,
p_projectno IN NVARCHAR2,
p_crno IN NVARCHAR2,
p_msg OUT NVARCHAR2)
IS
BEGIN
MERGE INTO tbl_project_mst_info t
USING (SELECT p_mst_id, p_projectno, p_crno FROM DUAL) x
ON (x.p_mst_id = t.mst_id)
WHEN MATCHED
THEN
UPDATE SET t.project_no = p_projectno, t.cr_no = p_crno
WHEN NOT MATCHED
THEN
INSERT (mst_id, project_no, cr_no)
VALUES (x.p_mst_id, x.p_projectno, x.p_crno);
p_msg := 'Merged ' || SQL%ROWCOUNT || ' row(s)';
END;
推荐阅读
- c++ - 如何在 DirectX 和 OpenGL 应用程序上覆盖 Chromium/Electron 浏览器主机?
- ruby-on-rails - Rails 5.1.4 fields_for 用于多个对象
- javascript - 使用javascript将图像添加到.ics文件?
- ios - Swift - 在视图控制器中使用两个单独的按钮添加文本字段和 UIImageView
- php - 如何用php替换字符串并上传
- sql-server - FULL OUTER JOIN 从外键表返回 NULL 值
- android - 自动 RecyclerView 滚动
- telepot - 即使给定条件,机器人也不会等待发送消息
- reactjs - mapDispatchToProps 反馈无法立即更新 mapStateToProps
- angular - 如何使用第三方api使用http客户端以角度绑定响应数据