BEGIN

Description

BEGIN may be used to initiate an anonymous block or a single transaction.

An anonymous block is a structure that can dynamically create and execute stored procedure code instead of permanently storing code as a database object in the database.

Precautions

None

Syntax

• Enable an anonymous block.

  [DECLARE [declare_statements]] 
  BEGIN
  execution_statements
  END;
  /

• Start a transaction.

  BEGIN [ WORK | TRANSACTION ]
    [ 
      { 
         ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE | REPEATABLE READ }
         | { READ WRITE | READ ONLY }
        } [, ...] 
    ];

Parameters

• declare_statements

  Declares a variable, including its name and type, for example, sales_cnt int.

• execution_statements

  Specifies the statement to be executed in an anonymous block.

  Value range: DML operations (such as SELECT, INSERT, DELETE, and UPDATE) or registered functions in the system catalog.

• WORK | TRANSACTION

  Specifies the optional keyword in the BEGIN syntax, which is meaningless.

• ISOLATION LEVEL

  Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.

  

  The isolation level of a transaction cannot be reset after the first clause (INSERT, DELETE, UPDATE, FETCH, or COPY) for modifying data is executed in the transaction.

  Value range:

  • READ COMMITTED: Only committed data can be read. This is the default action.
  • READ UNCOMMITTED: Committed data is probably read. This isolation level is provided to handle CN breakdown emergencies. On this isolation level, you are advised to only read data to prevent inconsistency.
  • REPEATABLE READ: Only the data committed before transaction start is read. Uncommitted data or data committed in other concurrent transactions cannot be read.
  • SERIALIZABLE: Currently, this isolation level is not supported. Setting this isolation level is equivalent to REPEATABLE READ.
• READ WRITE | READ ONLY

  Specifies the transaction access mode (read/write or read only).

Examples

• Start a transaction.

  -- Create a table and insert data into the table.
  gaussdb=# CREATE TABLE tbl_test1(col1 int, col2 int);
  gaussdb=# INSERT INTO tbl_test1 VALUES (1,1), (2,2), (3,3);
  
  -- Start a transaction in default mode.
  gaussdb=# BEGIN;
  INSERT INTO tbl_test1 VALUES (4,4);
  END;
  
  -- Start a transaction with the separation level being REPEATABLE READ.
  gaussdb=# BEGIN ISOLATION LEVEL REPEATABLE READ; 
  SELECT * FROM tbl_test1; 
  END;
  
  -- Drop the table.
  gaussdb=# DROP TABLE tbl_test1;

• Use anonymous blocks.

  1 2 3 4 5

  -- Generate a string using anonymous blocks. gaussdb=# BEGIN dbe_output.print_line('Hello'); END; /

Helpful Links

START TRANSACTION