我有一些执行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成功与否？
解决方法
您可以使用RETURNING子句返回刚刚创建的行的rowid,如下所示：
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;
取自此链接：
