oracel里有自自增长列吗?SQL里有.
我有A表,ID为number,C表ID为number,B表有ID和ID2(都为number),现在要求写一个存储过程,先插入A表记录,而后插入B表,插入C表,现在要求插入后,A表和C表关联(A.ID=C.ID),B表和C表关联(B.ID=C.ID2,其中C.ID2由B.ID决定)
我有A表,ID为number,C表ID为number,B表有ID和ID2(都为number),现在要求写一个存储过程,先插入A表记录,而后插入B表,插入C表,现在要求插入后,A表和C表关联(A.ID=C.ID),B表和C表关联(B.ID=C.ID2,其中C.ID2由B.ID决定)
create sequence a_id start with 1 increment by 1
create sequence b_id start with 1 increment by 1
create sequence c_id start with 1 increment by 1
插入id 列时 用 insert into a (id) values (a_id.nextval);
b,c 表雷同
对于 b.id=c.id2 而且 c.id2 数据有 b.id决定 可以jianl主外键关系
alter table c add contraint f_key foreign key (id2) reference b(id)
但你可以建一个序列 模拟自增长
这样无法实现a.id=c.id关联,C表的数据是由a表的ID来决定的.
即:insert into c vaules(c.id2=b.id)
create sequence b_id1 start with 1 increment by 1 max value 9999999;
create sequence b_id2 start with 1 increment by 1 max value 9999999;create or replace procedure insertInfo
is
begin
insert into a values (a_id.nextval,...);
insert into b values (b_id1.nextval,b_id2.nextval,...);
insert into c values (a_id.nextval-1,b_id2.nextval-1,...);
end;
create Sequence emp_sequence INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMAXVALUE ;
create Sequence emp_sequence2 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMAXVALUE ;
create Sequence emp_sequence3 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOMAXVALUE;create or replace procedure createdata
(
v_status varchar2,v_policy_no varchar2,v_batch_no varchar2,v_order_no varchar2 --@status选0,1,2三种
)
as
mm number;
-- nn number;
begin
-- select emp_sequence.nextval into mm from dual;
insert into t_renewal (id,batch_no,branch,create_time,payment_amt,
period_no,region_code,renewal_time,status,teller,order_no)
values (mm,v_batch_no,'Branch_003',TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00','YYYY-MM-D HH24:MI:SS.FF TZH:TZM'),40.00,100003,'test123',
TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00', 'YYYY-MM-D HH24:MI:SS.FF TZH:TZM'),
v_status, 'test123', v_order_no);
insert into t_policy (id ,policy_name,policy_no,main_product_id)
values(emp_sequence2.nextval,'12期-基本意外险60元',v_policy_no,emp_sequence3.nextval);--ora-00289错误在emp_sequence3
-- select max(t_policy.id) into nn from dual ;insert into t_biz_activity (id,sync_status,trans_time,trans_no,trans_type,unit_code,partner_id,policy_id,person_id)
values (mm,'2',TO_TIMESTAMP_TZ('2009-3-9 17:51:23.23 -05:00', 'YYYY-MM-D HH24:MI:SS.FF TZH:TZM'),'te4st123','test123','tes5t123',22,t_policy.id,5);--t_policy.id出现ora-00984错误
end;