目前还不清楚P_INDICATION用于什么以及它可以获得哪些值。
下面的代码区分不同的v_rrsoc_id值，并检查p_indication中的内容以决定要做什么。
也许你不应该在一个过程中提交，但是如果一切正常的话，让调用者去做。此外，在when others中回滚将忽略错误，您将不知道发生了什么问题。在这种情况下，您可能需要返回错误消息。

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
   THEN
      IF p_indication <> 1
      THEN
         tbldata := 'Record already exist';
      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 := 'Record Saved Succesfully';
EXCEPTION
   WHEN OTHERS
   THEN
      tbldata := 'Error: ' || SQLERRM;
END insert_into_rrsoc_mst;

[编辑]

现在我们知道了p_indication的含义，程序可以大大简化：

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 := 'Record updated';
   ELSIF p_indication = 0
   THEN
      INSERT INTO tbl_rrsoc_store_info (store_code, state, city)
           VALUES (p_store_code, p_state, p_city);

      tbldata := 'Record inserted';
   END IF;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX
   THEN
      tbldata := 'Record already exists';
END;