github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/prepare/prepare_transaction.sql (about)

     1  
     2  -- test Implicit Transaction execute prepare
     3  drop table if exists user;
     4  CREATE TABLE user
     5  (
     6   id BIGINT(20) NOT NULL COMMENT '主键ID',
     7   age INT(11) NULL DEFAULT NULL COMMENT '年龄',
     8   PRIMARY KEY (id)
     9  );
    10  INSERT INTO user (id, age) VALUES
    11  (1, 18),
    12  (2, 20),
    13  (3, 28),
    14  (4, 21),
    15  (5, 24);
    16  
    17  
    18  set autocommit=0;
    19  
    20  set @id=6;
    21  set @age=6;
    22  prepare s1 from 'insert into user values (?,?)';
    23  execute s1 using @id,@age;
    24  deallocate prepare s1;
    25  commit;
    26  set autocommit=1;
    27  select * from user;
    28  
    29  set autocommit=0;
    30  
    31  set @id=6;
    32  prepare s2 from 'delete from user where id=?';
    33  execute s2 using @id;
    34  deallocate prepare s2;
    35  commit;
    36  set autocommit=1;
    37  select * from user;
    38  
    39  set autocommit=0;
    40  
    41  set @id=5;
    42  set @age=100;
    43  prepare s3 from 'update user set age=? where id=?';
    44  execute s3 using @age, @id;
    45  deallocate prepare s3;
    46  commit;
    47  set autocommit=1;
    48  select * from user;
    49  
    50  set autocommit=0;
    51  
    52  set @id=3;
    53  prepare s4 from 'select * from user where id>?';
    54  execute s4 using @id;
    55  deallocate prepare s4;
    56  prepare s5 from 'select * from user where id<?';
    57  execute s5 using @id;
    58  deallocate prepare s5;
    59  commit;
    60  set autocommit=1;
    61  
    62  set autocommit=0;
    63  prepare s6 from 'create table test_user(a int)';
    64  execute s6;
    65  deallocate prepare s6;
    66  commit;
    67  set autocommit=1;
    68  show tables like 'test_user';
    69  
    70  set autocommit=0;
    71  prepare s7 from 'drop table test_user';
    72  execute s7;
    73  deallocate prepare s7;
    74  commit;
    75  set autocommit=1;
    76  show tables like 'test_user';
    77  
    78  drop table user;
    79  
    80  
    81  
    82  -- test explicit transaction execute prepare
    83  drop table if exists user;
    84  CREATE TABLE user
    85  (
    86   id BIGINT(20) NOT NULL COMMENT '主键ID',
    87   age INT(11) NULL DEFAULT NULL COMMENT '年龄',
    88   PRIMARY KEY (id)
    89  );
    90  INSERT INTO user (id, age) VALUES
    91  (1, 18),
    92  (2, 20),
    93  (3, 28),
    94  (4, 21),
    95  (5, 24);
    96  
    97  set @id=6;
    98  set @age=6;
    99  begin;
   100  prepare s1 from 'insert into user values (?,?)';
   101  execute s1 using @id,@age;
   102  deallocate prepare s1;
   103  commit;
   104  select * from user;
   105  
   106  set @id=6;
   107  begin;
   108  prepare s2 from 'delete from user where id=?';
   109  execute s2 using @id;
   110  deallocate prepare s2;
   111  commit;
   112  select * from user;
   113  
   114  set @id=5;
   115  set @age=100;
   116  begin;
   117  prepare s3 from 'update user set age=? where id=?';
   118  execute s3 using @age, @id;
   119  deallocate prepare s3;
   120  commit;
   121  select * from user;
   122  
   123  set @id=3;
   124  begin;
   125  prepare s4 from 'select * from user where id>?';
   126  execute s4 using @id;
   127  deallocate prepare s4;
   128  prepare s5 from 'select * from user where id<?';
   129  execute s5 using @id;
   130  deallocate prepare s5;
   131  commit;
   132  
   133  begin;
   134  prepare s6 from 'create table test_user(a int)';
   135  execute s6;
   136  deallocate prepare s6;
   137  commit;
   138  show tables like 'test_user';
   139  
   140  
   141  begin;
   142  prepare s7 from 'drop table test_user';
   143  execute s7;
   144  deallocate prepare s7;
   145  commit;
   146  show tables like 'test_user';
   147