数据库插入语句:4个字段,前2个是自动增长的,怎么避免后面2个字段的插入值不重复
insert into B_WZLB(ID,SerNO,Nam,DESCRIP ) values(concat('B',B_WZLB_Sequence.nextval),B_WZLB_SerNO_Sequence.nextval,:Nam,:DESCRIP)
insert into B_WZLB(ID,SerNO,Nam,DESCRIP ) values(concat('B',B_WZLB_Sequence.nextval),B_WZLB_SerNO_Sequence.nextval,:Nam,:DESCRIP)
(
@field1 varchar(50),
@field2 varchar(200) ,
@ID int,
@insertOrUpdate --小于0插入 大于0更新
)
as
begin
if(@insertOrUpdate<0)
begin
INSERT INTO TableName (field1,field2,ID )
values ( @field1 ,@field2,@ID);
end
else
begin
UPDATE TableName SET field1= @field1, field2 = @field2
where ID =@ID;
end
end
能不能写个oracle的存储过程,
CURSOR Insert_cur(D_Name VARCHAR2,D_DESCRIP VARCHAR2) IS
SELECT count(*) cnt
FROM B_WZLB t
WHERE Name=D_Name and DESCRIP= D_DESCRIP;
Insert_cur_row v_master_cur%ROWTYPE;
BEGIN
OPEN Insert_cur(Name,DESCRIP);
LOOP
FETCH Insert_cur
INTO Insert_cur_row;
EXIT WHEN Insert_cur%NOTFOUND;
IF Insert_cur_row.CNT > 0 THEN
p_code := 2; //表示重复记录!
ELSE
insert into B_WZLB(ID,SerNO,Nam,DESCRIP ) values(concat('B',B_WZLB_Sequence.nextval),B_WZLB_SerNO_Sequence.nextval,Name,DESCRIP);
p_code := 1; //表示插入成功!
END IF;
COMMIT;
END LOOP;
CLOSE Insert_cur;
EXCEPTION
WHEN OTHERS THEN
IF Insert_cur%ISOPEN THEN
CLOSE Insert_cur;
END IF;
ROLLBACK;
p_code := 0; //表示插入失败!
RETURN;
END p_data_insert;