Блог

Главная - Вопросы по программированию - Обновить запрос в той же хранимой процедуре в Oracle

Обновить запрос в той же хранимой процедуре в Oracle

#oracle #stored-procedures #insert-update

#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 — это мой уникальный идентификационный столбец, на основе которого я буду обновлять таблицу.

Ответ №1:

Я не уверен, что понял, о чем вы спрашиваете. В настоящее время ваша процедура вставляет строку в таблицу; значения, которые вы вставляете, являются параметрами процедуры В параметрах.

Вы хотели бы иметь возможность обновлять текущие строки в той же таблице, вызывая ту же процедуру и передавая те же параметры?

Если это так, СЛИЯНИЕ может быть хорошим выбором. Он также называется 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;

Комментарии:

1. нет проблем с записью столбцов, но будет ли он обновляться, если записи существуют, и вставляться, когда нет?

2. при вставке INSERT (mst_id, project_no, cr_no) mst_id мой уникальный столбец. он будет вставлен автоматически

3. 1 -й комментарий: попробуйте, и вы увидите. 2 -й комментарий: если вы хотите иметь возможность обновлять существующие строки, вам нужно будет передать MST_ID в качестве параметра. Как еще вы узнаете, какие строки обновлять?

4. Я хочу вернуть сообщение об успешной вставке или обновлении.. как я его добавлю? пожалуйста, предложите

5. Включите параметр OUT; Я изменил пример кода, посмотрите.