消息在Oracle中添加两次时未提示输入相同的商店代码。

huangapple go评论59阅读模式
英文:

Message is not prompting for same store code when added twice in Oracle

问题

我有以下存储过程,其中我正在检查唯一约束,即不能两次添加相同的 store_code。因此,一旦用户插入了带有名称 abcSTORE_CODE,然后再次尝试插入相同的 store_code 和相同的名称,他应该收到提示消息 记录已存在

我尝试过如下的存储过程,但它没有弹出消息。

PROCEDURE INSERT_INTO_RRSOC_MST
(    
  P_STORE_CODE IN NVARCHAR2,
  P_STATE IN NVARCHAR2,
  P_CITY IN NVARCHAR2,
  P_Indication IN NUMBER,
  TBLDATA OUT NVARCHAR2
) 

AS

V_RRSOC_ID NUMBER:=0;

BEGIN

  SELECT COUNT(RRSOC_ID) INTO V_RRSOC_ID FROM TBL_RRSOC_STORE_INFO 
  WHERE STORE_CODE = P_STORE_CODE AND isactive = 'Y';
  
  IF V_RRSOC_ID > 0 AND  P_Indication = 1 THEN
  
    UPDATE TBL_RRSOC_STORE_INFO 
    SET                                
      STATE = P_STATE,
      CITY = P_CITY,
    WHERE STORE_CODE = P_STORE_CODE;   
    COMMIT;  
    
  ELSE
  
    IF V_RRSOC_ID = 0 AND  P_Indication = 0 THEN
    
      INSERT INTO TBL_RRSOC_STORE_INFO      
      (
        STORE_CODE,
        STATE,     
        CITY                                      
      )
      VALUES
      (            
        P_STORE_CODE,
        P_STATE,
        P_CITY                                                
      ) 
      RETURNING RRSOC_ID INTO TBLDATA;
      COMMIT;                                         
      TBLDATA:='Record Saved Succesfully';  
      
    ELSE
    
      TBLDATA:='Record already exist';  
    
    END IF;
  END IF;
  EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  
END INSERT_INTO_RRSOC_MST;

注意:此存储过程看起来有一些语法错误,比如在UPDATE语句中有额外的逗号。您可能需要修复这些错误以使其正常运行。

英文:

I have stored procedure as below, where I am checking UNIQUE constraint that same store_code cannot be added twice. So once the user has inserted the STORE_CODE with name say abc. And again he is trying to insert the same store_code with same name. then he should get a prompt as Record already exist.

I have tried like below SP but it's not prompting the message.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

PROCEDURE INSERT_INTO_RRSOC_MST
  (    
    P_STORE_CODE IN NVARCHAR2,
    P_STATE IN NVARCHAR2,
    P_CITY IN NVARCHAR2,
    P_Indication IN NUMBER,
    TBLDATA OUT NVARCHAR2
  ) 
  
  AS
  
  V_RRSOC_ID NUMBER:=0;
  
  BEGIN
    
    SELECT COUNT(RRSOC_ID) INTO V_RRSOC_ID FROM TBL_RRSOC_STORE_INFO 
    WHERE STORE_CODE = P_STORE_CODE AND isactive = &#39;Y&#39;;
    
    IF V_RRSOC_ID &gt; 0 AND  P_Indication = 1 THEN
    
                              UPDATE TBL_RRSOC_STORE_INFO 
                              SET                                
                                  STATE = P_STATE,
                                  CITY = P_CITY,
                              WHERE STORE_CODE = P_STORE_CODE;   
  commit;  
  
    ELSE
    
     IF V_RRSOC_ID = 0 AND  P_Indication = 0 THEN
    
    INSERT INTO TBL_RRSOC_STORE_INFO      
                                   (
                                          STORE_CODE,
                                          STATE,     
                                          CITY                                      
                                          
                                   )
            
     VALUES
                                   (            
                                          P_STORE_CODE,
                                          P_STATE,
                                          P_CITY
                                            
                                   ) 
                                   
                                   RETURNING RRSOC_ID INTO TBLDATA;
commit;                                         
          TBLDATA:=&#39;Record Saved Succesfully&#39;;  
          
          ELSE
          
           TBLDATA:=&#39;Record already exist&#39;;  
    
          END IF;
  END IF;
        EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        
  END INSERT_INTO_RRSOC_MST;

<!-- end snippet -->

答案1

得分: 1

P_INDICATION 用于判断如何处理数据并且它可以取得哪些值。

在以下的代码中,根据不同的 v_rrsoc_id 值,检查了 p_indication 中的内容来决定要执行什么操作。

也许在存储过程内部不应该进行提交(commit),而应该让调用者来处理,如果一切正常的话。另外,在 when others 中回滚(rolling back)将会静默地忽略错误,你将不知道发生了什么问题。也许在这种情况下,你应该返回错误消息。

在第一个代码块中,P_INDICATION 可以取得值 0 或 1。值为 1 时表示记录已存在,值为 0 时表示需要插入新记录。

在第二个代码块中,P_INDICATION 仅可以取得值 0 或 1。值为 1 时表示要更新记录,值为 0 时表示要插入新记录。异常处理部分已经更新,现在包括了处理唯一索引冲突(DUP_VAL_ON_INDEX)的情况。

请注意,这些是根据提供的代码片段的理解,可能会有更多的上下文信息来解释 P_INDICATION 的用途和可能的取值。

英文:

It is kind of unclear what is P_INDICATION used for and which values it can get.

Code that follows distinguishes different v_rrsoc_id values and checks what's in p_indication to decide what to do.

Perhaps you shouldn't commit within a procedure, but let caller do that if everything is OK. Also, rolling back in when others will silently ignore errors and you won't know what went wrong. Maybe you should return error message in such a case.

PROCEDURE insert_into_rrsoc_mst (p_store_code  IN     NVARCHAR2,
                                 p_state       IN     NVARCHAR2,
                                 p_city        IN     NVARCHAR2,
                                 p_indication  IN     NUMBER,
                                 tbldata          OUT NVARCHAR2)
AS
   v_rrsoc_id  NUMBER := 0;
BEGIN
   SELECT COUNT (rrsoc_id)
     INTO v_rrsoc_id
     FROM tbl_rrsoc_store_info
    WHERE     store_code = p_store_code
          AND isactive = &#39;Y&#39;;

   IF v_rrsoc_id &gt; 0
   THEN
      IF p_indication &lt;&gt; 1
      THEN
         tbldata := &#39;Record already exist&#39;;
      ELSIF p_indication = 1
      THEN
         UPDATE tbl_rrsoc_store_info
            SET state = p_state, city = p_city
          WHERE store_code = p_store_code;
      END IF;
   ELSIF v_rrsoc_id = 0
   THEN
      IF p_indication = 0
      THEN
         INSERT INTO tbl_rrsoc_store_info (store_code, state, city)
              VALUES (p_store_code, p_state, p_city)
           RETURNING rrsoc_id
                INTO tbldata;
      END IF;
   END IF;

   tbldata := &#39;Record Saved Succesfully&#39;;
EXCEPTION
   WHEN OTHERS
   THEN
      tbldata := &#39;Error: &#39; || SQLERRM;
END insert_into_rrsoc_mst;

[EDIT]

Now that we found out what p_indication means, procedure can be significantly simplified:

PROCEDURE insert_into_rrsoc_mst (p_store_code  IN     NVARCHAR2,
                                 p_state       IN     NVARCHAR2,
                                 p_city        IN     NVARCHAR2,
                                 p_indication  IN     NUMBER,
                                 tbldata          OUT NVARCHAR2)
AS
BEGIN
   IF p_indication = 1
   THEN
      UPDATE tbl_rrsoc_store_info
         SET state = p_state, city = p_city
       WHERE store_code = p_store_code;

      tbldata := &#39;Record updated&#39;;
   ELSIF p_indication = 0
   THEN
      INSERT INTO tbl_rrsoc_store_info (store_code, state, city)
           VALUES (p_store_code, p_state, p_city);

      tbldata := &#39;Record inserted&#39;;
   END IF;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      tbldata := &#39;Record already exists&#39;;
END;

huangapple
  • 本文由 发表于 2023年6月26日 18:53:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/76556015.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定