synbase 中的十五

最新推荐文章于 2022-05-11 21:03:19 发布

原创最新推荐文章于 2022-05-11 21:03:19 发布 · 502 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #table #delay #null #object

本文档通过一系列SQL语句展示了如何创建、删除表，进行数据插入、更新操作，并使用事务来确保数据操作的一致性和隔离性。此外，还包括了存储过程的创建与调用示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if exists (select 1
            from sysobjects
            where id = object_id('tA2')
            and    type = 'U')
   drop table tA2
go

begin transaction tA2
LOCK TABLE tA2 IN SHARE MODE
commit transaction tA2

if exists (select 1
            from sysobjects
            where id = object_id('tB1')
            and    type = 'U')
   drop table tB1
go

/*==============================================================*/
/* Table: tA                                                                                                             */
/*==============================================================*/
create table tA2 (
     name                char(20)                        null,
     price                 int                            default 0 null,
   )
go
/*==============================================================*/
/* Table: tB                                                                                                            */
/*==============================================================*/
create table tB2 (
     name                    char(20)                        null,
     saleprice               int                            default 0 null,
   )
go
ALTER TABLE tA1 LOCK DATAROWS
ALTER TABLE tB1 LOCK DATAROWS
go
insert into tA2(name, price) values ('book',20)
insert into tA1(name, price) values ('book1',20)
insert into tB1(name, saleprice) values ('book',10)
insert into tB1(name, saleprice) values ('book1',10)

select * from tA2
select * from tB1

begin tran
insert into tA1(name, price) values ('book2',20)
waitfor delay '00:00:05'
insert into tB1(name, saleprice) values ('book2',10)
commit tran

begin tran
update tB set saleprice=saleprice+10000 where name='book'
update tA set price=price+20000 where name='book'
commit tran

----------- Test

begin tran
update tA2 set price=price+100 where name='book'
waitfor delay '00:00:05'
update tB2 set saleprice=saleprice+100 where name='book45'
commit tran

begin tran
insert into tB1(name, saleprice) values ('book3',10)
waitfor delay '00:00:05'
insert into tA1(name, price) values ('book3',20)

commit tran

drop procedure P1
select * from tA2

insert into tA2(name, price) values ('book44',20)

drop procedure P1

select * from tA2
select * from tB2

exec P1

create procedure P1
as
begin

begin transaction V1
insert into tA2(name, price) values ('book45',20)
exec P2
rollback transaction V1
end

drop procedure P2
create procedure P2
as
begin
begin transaction V2
insert into tB2(name, saleprice) values ('book45',20)
commit transaction V2
end