我需要在ORACLE里插入三张关联的表.其中两张表是用自增长列关联的,要求每次插入时,会在原来的基础上进行自增长.语句如下,但是出现了编译错误.如下.
create or replace procedure createdata
(
v_status number,v_policy_no varchar2,v_batch_no varchar2,v_order_no varchar2 --@statusÑ¡0,1,2ÈýÖÖ
)
as
begin
declare mm number;
select t_renewal.nexval 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,21-12ÔÂ-10-04.25.11.578000 ÏÂÎç, 40.00,100003,test123, 21-12ÔÂ-10 04.25.11.609000 ÏÂÎç,v_status, test123, v_order_no);
insert into t_policy (id ,policy_name,policy_no,main_product_id)
values(nextval,45, 12ÆÚ-»ù±¾ÒâÍâÏÕ60Ôª ,v_policy_no,nextval);
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,21-12ÔÂ-10 04.25.11.703000 ÏÂÎç,te4st123,test123,tes5t123,22,t_policy.id,5);
end
end
create or replace procedure createdata
(
v_status number,v_policy_no varchar2,v_batch_no varchar2,v_order_no varchar2 --@statusÑ¡0,1,2ÈýÖÖ
)
as
begin
declare mm number;
select t_renewal.nexval 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,21-12ÔÂ-10-04.25.11.578000 ÏÂÎç, 40.00,100003,test123, 21-12ÔÂ-10 04.25.11.609000 ÏÂÎç,v_status, test123, v_order_no);
insert into t_policy (id ,policy_name,policy_no,main_product_id)
values(nextval,45, 12ÆÚ-»ù±¾ÒâÍâÏÕ60Ôª ,v_policy_no,nextval);
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,21-12ÔÂ-10 04.25.11.703000 ÏÂÎç,te4st123,test123,tes5t123,22,t_policy.id,5);
end
end
create or replace procedure createdata(v_status number,
v_policy_no varchar2,
v_batch_no varchar2,
v_order_no varchar2 --@status??0,1,2èy??
) as ---begin -------地方放错了
--declare---没有declare,这个只有触发器中才有
mm number;
begin
select t_renewal.nexval 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', -------这个变量没有定义,还是字符吗,如果是字符加上引号
'21 - 12 ? ? -10 - 04.25.11.578000 ? ? ? ?', ---字符没有引号
40.00,
100003,
'test123', ----没加引号
'21 - 12 ? ? -10 04.25.11.609000 ? ? ? ?', ----没加引号
v_status,
'test123', ----没加引号
v_order_no); insert into t_policy
(id, policy_name, policy_no, main_product_id)
values
(t_renewal.nextval, --------未指定序列名字
45,
'12 ? ú - ? ù± ? òaía ? ? 60 ? a', ---没加引号
v_policy_no,
t_renewal.nextval, --------未指定序列名字
); 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,
'21 - 12 ? ? -10 04.25.11.703000 ? ? ? ?',
'te4st123', --没加引号
'test123', --没加引号
'tes5t123', --没加引号
22,
t_policy.id,
5);
end
create or replace procedure createdata
(
v_status number,v_policy_no varchar2,v_batch_no varchar2,v_order_no varchar2 --@status??0,1,2èy??
)
as
mm number; --变量申明写在as后begin 前
begin
select t_renewal.nexval into mm from dual;
--我觉得你这里的序列名称t_renewal和你表t_renewal 两个名称相同,我怀疑你创建表或者序列的时候能成功吗?
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,21-12??-10-04.25.11.578000 ????, 40.00,100003,test123, 21-12??-10 04.25.11.609000 ????,v_status, test123, v_order_no);
insert into t_policy (id ,policy_name,policy_no,main_product_id)
values(nextval,45, 12?ú-?ù±?òaía??60?a ,v_policy_no,nextval);
--你这里的nextval是什么?是t_renewal序列,吗?如果是 改成t_renewal.nexval
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,21-12??-10 04.25.11.703000 ????,te4st123,test123,tes5t123,22,t_policy.id,5);
--end 多了end
end ; --少了分号结束
values(nextval,45, 12?ú-?ù±?òaía??60?a ,v_policy_no,nextval);
--你这里的nextval是什么?是t_renewal序列,吗?如果是 改成t_renewal.nexval
-----------------------------------------------
这句的两个nextval是t_policy表的自增长列
'12 ? ú - ? ù± ? òaía ? ? 60 ? a', ---没加引号
---------------------------
这个是日期型,需要加引号吗?
select t_renewal.nexval into mm from dual;
--我觉得你这里的序列名称t_renewal和你表t_renewal 两个名称相同,我怀疑你创建表或者序列的时候能成功吗?
--------------------------------------
这句我其实不是很理解,我要做的就是t_renewal表和t_biz_activity表用自增长列关联起来,先声明一个
mm做全局变量.然后关联.