pl / sql处理重复

debugcn 发表于 Dev

维克多·登布斯基（WiktorDębski）

您好，当表中存在其他工人具有相同的姓氏时，我必须执行插入新行并回滚的过程。

我在网上和这里签到，但找不到特定的信息。我必须使用异常dup_val_on_index

create or replace PROCEDURE insert_date(
   p_id IN WORKERS.ID%TYPE,
   p_PESEL IN WORKERS.PESEL%TYPE,
   p_name IN WORKERS.NAME%TYPE,
   p_surname IN WORKERS.SURNAME%TYPE,
   p_date_birth IN WORKERS.DATE_BIRTH%TYPE,
   p_salary IN WORKERS.SALARY%TYPE)
IS
BEGIN

INSERT INTO WORKERS VALUES (p_id, p_PESEL,p_name,p_surname, p_date_birth,p_salary);

COMMIT;
EXCEPTION
   when DUP_VAL_ON_INDEX then
   dbms_output.put_line('DUP_VAL_ON_INDEX exception.');
    ROLLBACK;
END;

BEGIN
insert_date(5,92060111111,'wikta','dss',TO_DATE('2003/07/10',    'yyyy/mm/dd'),100);
END;

萨加尔·琼（Sagar Joon）

您只需要检查表中是否已经存在SURNAME。如果不存在，则可以插入并提交该行。试试这个：

create PROCEDURE insert_date(
p_id IN WORKERS.ID%TYPE,
p_PESEL IN WORKERS.PESEL%TYPE,
p_name IN WORKERS.NAME%TYPE,
p_surname IN WORKERS.SURNAME%TYPE,
p_date_birth IN WORKERS.DATE_BIRTH%TYPE,
p_salary IN WORKERS.SALARY%TYPE)

IS 
  varTmp NUMBER:=0;
BEGIN
-- check here whether the surname already exist or not
SELECT decode((select max(t.d) from (SELECT 1 d FROM WORKERS WHERE SURNAME = p_surname) t),1, 2) INTO varTmp FROM dual;

-- insert
IF (varTmp <> 2) THEN
    INSERT INTO WORKERS VALUES (p_id, p_PESEL,p_name,p_surname, p_date_birth,p_salary);
 ELSE
    DBMS_OUTPUT.PUT_LINE('Row can not be inserted.');
END IF;
 COMMIT;
EXCEPTION
 when DUP_VAL_ON_INDEX then
 dbms_output.put_line('DUP_VAL_ON_INDEX exception.');
  ROLLBACK;
END;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。