我正在使用VB6和ADO从XP客户端在数据库服务器上使用Oracle。在一项事务中,我将一条记录插入到一个父表中,该表具有一个触发器和序列以创建唯一的recordid,然后将该recordid用于与子表的关系,以对子表进行可变数量的插入。为了提高性能,这是从我的客户端应用程序通过一个execute命令发送的。例如(简化示例):
declare Recordid int;
begin
insert into ParentTable (_field list_) Values (_data list_);
Select ParentTableSequence.currVal into Recordid from dual;
insert into ChildTable (RecordID, _field list_) Values (Recordid, _data list_);
insert into ChildTable (RecordID, _field list_) Values (Recordid, _data list_);
... multiple, variable number of additional ChildTable inserts
commit;
end;
一切正常。我的问题是:我还需要将为插入创建的Recordid返回给客户端。在SQL Server上,我可以在提交后向Scope_Identity()添加类似选择的内容,以将记录集返回给具有唯一ID的客户端。
但是,如何为Oracle做类似的事情(不必是记录集,我只需要那个长整数值)?我已经基于搜索'net的结果尝试了很多方法,但是没有找到解决方案。
最佳答案
这两行可以压缩为一条语句:
-- insert into ParentTable (field list) Values (data list);
-- Select ParentTableSequence.currVal into Recordid from dual;
insert into ParentTable (field list) Values (data list)
returning ParentTable.ID into Recordid;
如果要将ID传递回调用程序,则需要将程序定义为存储过程或函数,分别将Recordid返回为OUT参数或RETURN值。
编辑
MarkL评论:
这更像是Oracle PL / SQL
问题比什么都重要,我
相信。
我承认我对ADO一无所知,所以我不知道下面的示例是否适用于您的情况。它涉及构建一些基础结构,该基础结构允许我们将值的数组传递到过程中。下面的示例创建一个新部门,提升一个现有员工来管理它,并分配两名新员工。
SQL> create or replace type new_emp_t as object
2 (ename varchar2(10)
3 , sal number (7,2)
4 , job varchar2(10));
5 /
Type created.
SQL>
SQL> create or replace type new_emp_nt as table of new_emp_t;
2 /
Type created.
SQL>
SQL> create or replace procedure pop_new_dept
2 (p_dname in dept.dname%type
3 , p_loc in dept.loc%type
4 , p_mgr in emp.empno%type
5 , p_staff in new_emp_nt
6 , p_deptno out dept.deptno%type)
7 is
8 l_deptno dept.deptno%type;
9 begin
10 insert into dept
11 (dname, loc)
12 values
13 (p_dname, p_loc)
14 returning deptno into l_deptno;
15 update emp
16 set deptno = l_deptno
17 , job = 'MANAGER'
18 , mgr = 7839
19 where empno = p_mgr;
20 forall i in p_staff.first()..p_staff.last()
21 insert into emp
22 (ename
23 , sal
24 , job
25 , hiredate
26 , mgr
27 , deptno)
28 values
29 (p_staff(i).ename
30 , p_staff(i).sal
31 , p_staff(i).job
32 , sysdate
33 , p_mgr
34 , l_deptno);
35 p_deptno := l_deptno;
36 end pop_new_dept;
37 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL>
SQL> declare
2 dept_staff new_emp_nt;
3 new_dept dept.deptno%type;
4 begin
5 dept_staff := new_emp_nt(new_emp_t('MARKL', 4200, 'DEVELOPER')
6 , new_emp_t('APC', 2300, 'DEVELOPER'));
7 pop_new_dept('IT', 'BRNO', 7844, dept_staff, new_dept);
8 dbms_output.put_line('New DEPTNO = '||new_dept);
9 end;
10 /
New DEPTNO = 70
PL/SQL procedure successfully completed.
SQL>
DEPT和EMP的主键都通过触发器分配。
FORALL
语法是插入记录的一种非常有效的方式(它也适用于UPDATE
和DELETE
)。可以将其写为FUNCTION
来返回新的DEPTNO,但通常认为在插入,更新或删除时使用PROCEDURE
是更好的做法。那将是我的首选方法,但我承认这并不符合所有人的口味。
编辑2
关于性能,使用
FORALL
的批量操作肯定比少数单个插入要好。在SQL中,设置操作总是比逐条记录更可取。但是,如果我们每次仅处理少量记录,则可能很难注意到差异。就内存而言,构建PL / SQL集合(您将其视为SQL Server中的临时表)可能会非常昂贵。如果有许多用户在运行该代码,则尤其如此,因为它来自会话级别的内存分配,而不是共享全局区域。当我们处理大量记录时,最好使用大块填充数组,也许使用带
BULK COLLECT
子句的LIMIT
语法。Oracle在线文档集非常不错。 《 PL / SQL开发人员指南》中有关于集合的整章。 Find out more。
关于oracle - 在Oracle数据库上获取插入的recordid,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/1324761/