英文:
Message is not prompting for same store code when added twice in Oracle
问题
我有以下存储过程,其中我正在检查唯一约束,即不能两次添加相同的 store_code
。因此,一旦用户插入了带有名称 abc
的 STORE_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 = '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;
<!-- 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 = '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;
[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 := '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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论