代码之家 › 专栏 › 技术社区 › hud

Oracle中同一存储过程中的Uodate查询

insert-update stored-procedures oracle

hud · 技术社区 · 4 年前

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

请帮忙

0 回复 | 直到 4 年前

Littlefoot 4 年前

我不确定我是否理解你的要求。当前,您的过程在表中插入一行;您插入的值是过程的参数。

您希望能够通过调用相同的过程并传递相同的参数来更新同一表中的当前行吗?

如果是的话, 合并因为它能够在同一个表中插入行(不存在的行)或更新行(如果存在的话)。

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;