oracle – 检查INSERT是否成功

CREATE OR REPLACE PROCEDURE potok_insert(
  p_jfplate IN potok.jfplate%TYPE,p_post IN potok.post%TYPE,p_jfchan IN potok.jfchan%TYPE,p_jfdatetime IN VARCHAR2 
  ) 
AS 
  t_jfdatetime TIMESTAMP:=TO_TIMESTAMP(p_jfdatetime,'DD.MM.YYYY HH24:MI:SS');
BEGIN
  INSERT INTO potok (jfplate,post,jfchan,jfdate_y,jfdate_m,jfdate_d,jftime,jfdatetime,dt_reg,ibd_arx)
      VALUES (RTRIM(p_jfplate),p_post,RTRIM(p_jfchan),EXTRACT(YEAR FROM t_jfdatetime),EXTRACT(MONTH FROM t_jfdatetime),EXTRACT(DAY FROM t_jfdatetime),LPAD(EXTRACT(HOUR FROM t_jfdatetime),2,'0')||':'||
        LPAD(EXTRACT(MINUTE FROM t_jfdatetime),'0')||':'||
        LPAD(EXTRACT(SECOND FROM t_jfdatetime),'0'),CAST(t_jfdatetime AS DATE),SYSDATE,1);  
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END potok_insert;

一些触发器和约束可以应用于表,它们可以
INSERT.
我如何检查程序正文 – 如果INSERT成功与否？

当然我可以在头部和程序结束时调用count(),但这不是那么优雅的解决方案.

解决方法

CREATE SEQUENCE seq_emp;

set serveroutput on

DECLARE
 x emp.empno%TYPE;
BEGIN
  INSERT INTO emp
  (empno,ename)
  VALUES
  (seq_emp.NEXTVAL,'Morgan')
  RETURNING empno
  INTO x;

  dbms_output.put_line(x);
END;
/

DECLARE
 r rowid;
BEGIN
  INSERT INTO emp
  (empno,'Morgan')
  RETURNING rowid
  INTO r;

  dbms_output.put_line(r);
END;
/

DECLARE
 x emp.empno%TYPE;
 r rowid;
BEGIN
  INSERT INTO emp
  (empno,'Morgan')
  RETURNING rowid,empno
  INTO r,x;

  dbms_output.put_line(r); 
  dbms_output.put_line(x);
END;

取自此链接：

http://www.psoug.org/reference/insert.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!