在执行dml操作时,可以使用record来简化操作,如下:
SQL> create or replace procedure set_emp_info(rec_emp in emp%rowtype)
2 is
3 begin
4 insert into emp values rec_emp;
5 dbms_output.put_line('插入成功!');
6 exception
7 when dup_val_on_index
8 then
9 -- UPDATE emp SET ROW = rec_emp
10 -- WHERE empno=rec_emp.empno;
11 dbms_output.put_line('插入的记录与已有记录主键重复!');
12 end;
13 /
Procedure created.
执行DML操作,调用上面的存储过程:
SQL> declare
2 emp_rec emp%rowtype;
3 begin
4 select * into emp_rec from emp where empno=7369;
5 -- emp_rec.empno := 9999;
6 set_emp_info(emp_rec);
7 dbms_output.put_line('pl/sql execute successfully!');
8 end;
9 /
插入的记录与已有记录主键重复!
pl/sql execute successfully!
SQL> declare
2 emp_rec emp%rowtype;
3 begin
4 select * into emp_rec from emp where empno=7369;
5 emp_rec.empno := 9999;
6 set_emp_info(emp_rec);
7 dbms_output.put_line('pl/sql execute successfully!');
8 end;
9 /
插入成功!
pl/sql execute successfully!
SQL> select * from emp where empno=9999;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------- ---------- ---------- ----------
9999 SMITH CLERK 7902 19801217 00:00:00 800 20
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-710739/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-710739/